June « 2009 « Oracle DBA – Tips and Techniques

Script – Temporary tablespace usage

– Listing of temp segments.–

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

– Temp segment usage per session.–

SELECT

Continue reading Script – Temporary tablespace usage

10g Datapump – An Introduction

In 10g you can continue to use the earlier exp and imp versions to export/import data.

However if you export data using exp, then the import has to be with imp utility.

You cannot export using the old exp utility and import using impdp

The 2 new utilites in 10g are EXPDP and IMPDP.

EXPORT USING DATAPUMP (EXPDP).

Important pre-requisites:

Two directories need to be created in the database with the path pointing to an existing physical path on the Database Server: Ex HQLINUX5.

By default dumps and logs created in $ORACLE_HOME/rdbms/log area.

To avoid using the disk on which the ORACLE_HOME resides…

1. CREATE DIRECTORIES on server: example /u01/ORACLE/bozo/datapump and /u02/ORACLE/bozo/pumplogs

2. CREATE DIRECTORIES IN DATABASE.

Sql> create directory dump_dir as ‘/u02/ORACLE/bozo/datapump’; ………All dumps are sent to this area.

Sql> create directory log_dir as ‘/u02/ORACLE/bozo/pumplogs’; ………All logs are sent to this area.

Above

Continue reading 10g Datapump – An Introduction

Renaming datafile on standby database

DATAGUARD TROUBLE SHOOTING -

It is possible that a datafile is added on primary in some other area other than paths covered by the db_file_name_convert parameter which ensures the file is added on the standby database as well.

This documents the real life scenario if a datafile is added to an wrong area on the primary database which is not in the db_file_name_convert path in the standby init.ora and how to recover the standby database in this case.

STANDBY SITE

SQL> show parameter convert

NAME TYPE VALUE
———————————— ———– ——————————
db_file_name_convert string /opt/oracle/, /opt/oracle/
log_file_name_convert

Continue reading Renaming datafile on standby database

Data Guard switchover checklist

In some establishments, Dataguard switchovers are manual -

Please perform these pre-requisite checks before undertaking a switchover to primary.

1. ON STANDBY SITE:

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY…………….PROCEED.

If you receive a reply like the one below, then do not proceed- you are most probably firing the sql command in the primary site:

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY…………….do not proceed if this message is received …in this case you are most probably on the primary site..

Again on STANDBY SITE:

SQL> select name,value from v$parameter where name in (‘log_archive_dest_1′,’log_archive_dest_state_1′, ‘log_archive_dest_2′,’log_archive_dest_state_2′);

NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_1
LOCATION=/opt/oracle/opsdb9i/arch

log_archive_dest_2
SERVICE=opsdb9i_blade07 lgwr sync affirm nodelay……….Make sure lgwr and not arch is mentioned here, otherwise new primary database will not open after switchover ( if the protection_mode is Maximum availability).

log_archive_dest_state_1
ENABLE

log_archive_dest_state_2
DEFER …………..DO NOT PROCEED.

While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alert_log , but make sure it is set to ENABLE before starting a switchover.

This command can be

Continue reading Data Guard switchover checklist

Enable block change tracking

BLOCK CHANGE TRACKING

From version 10.2 onwards, Oracle have provided a new tool which is very useful to reduce the time of RMAN incremental backups.

Prior to 10.2, all incremental backups had to read every single block in the database, and if the block has changed, it was backed up. This meant the RMAN backup job took nearly as long as a normal full backup because every block had to be read regardless.

To avoid this, Oracle introduced the BLOCK CHANGE TRACKING file – if this was enabled, then a file called the block change tracking file kept information of all changes to blocks since the last backup. This file was read instead of all the blocks in the database to arrive at changed blocks and then these blocks were backed up.

This reduced the backup time considerably – sometimes, especially in the case of Data Warehouse type databases, since changes happened infrequently,

Continue reading Enable block change tracking

Resize standby datafile if disk runs out of space on standby site.

PURPOSE: TO AVOID RECREATION OF STANDBY DATABASE IN CASE FILE IS NOT RESIZED ON STANDBY :

ITLINUXDEVBLADE07-PRIMARY

Database is DGTEST9i

[oracle@itlinuxdevblade07 dgtest9i]$ df -h .

Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 21G 2.0G 92% /opt

[oracle@itlinuxdevblade07 dgtest9i]$

2 gb freespace on disk on PRIMARY.

ITLINUXDEVBLADE08

[oracle@itlinuxdevblade08 oradata]$ df -h .

Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 550M 98% /opt

[oracle@itlinuxdevblade08 oradata]$

The corresponding disk on the standby site as

Continue reading Resize standby datafile if disk runs out of space on standby site.

How to monitor the progress of an UNDO operation

We can monitor the progress of an undo operation by running the query shown below:

select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
, from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr;

From the first session connected as SH we issue a DELETE statement

SQL> conn sh/sh
Connected.

SQL> delete sales;

While the delete operation is in progress, we can monitor the usage of undo blocks from
another session

As user SYS we issue the SQL statement shown above and we see that the USED_UBLK
column value keeps increasing as the delete statement progresses and more undo blocks
are generated.

SQL> /

USERNAME COMMAND USED_UBLK
————— ——————– ———-
SH sqlplus@devu007 (TN 11070

SQL> /

USERNAME COMMAND

Continue reading How to monitor the progress of an UNDO operation

Monitor long running operations using v$session_longops

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;

Note: the same query can be used to monitor RMAN backup status

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0
AND SOFAR != TOTALWORK
;

SID SERIAL# OPNAME SOFAR TOTALWORK COMPLETE
———- ———- ——————– ———- ———- ———-
604 13371 Table Scan 6311 24498

Continue reading Monitor long running operations using v$session_longops

Monitor Data Guard Log Shipping

Note: This query needs to be run on the Primary database.

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
);

DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
——– ———— ———— ———– ———— ——-
CPSPRD PRDU009N1 11213 11213 30-JUN/08:48 0

Export and Import using unix pipes and compression

EXPORT AND IMPORT USING UNIX PIPES.

Sometimes, the space on disk may not be enough to hold a full export dump if uncompressed.

EXAMPLE – export schema ARJUN from PROD database and import into DEV database.

To avoid space running out, unix pipes and compression can be used.

EXPORT IN PROD DATABASE

cd /u02/oradata/export

CREATE UNIX PIPE IN THIS AREA – WHERE THE EXPORT DUMP WILL BE WRITTEN TO.

mknod pipe p

CREATE PAR FILE -

parfile is arjun.par

vi arjun.par

buffer=2097152
recordlength=65535
consistent=y
owner=arjun
log=/u02/oradata/export/arjun.log
file=/u02/oradata/export/pipe

Now export schema ARJUN.

1. nohup gzip -c </u02/oradata/export/pipe > /u02/oradata/export/arjun.dmp.gz &

Immediately enter next command -

2. nohup exp \’/ as sysdba\’ parfile=/u02/oradata/export/arjun.par &

Export of ARJUN schema completes – compressed dump arjun.dmp.gz created.

ftp or copy the dump file arjun.dmp.gz to the DEV database box.

IMPORT IN DEV DATABASE – – Presume same directory structure exists on DEV box.

Create UNIX PIPE in area where dump is copied to in DEV box.

cd /u02/oradata/export

mknod import_pipe p

Create import parfile – called imp.par

vi imp.par

fromuser=arjun
touser=arjun
commit=y
buffer=2097152
ignore=y
file=/u02/oradata/export/import_pipe
log=/u02/oradata/export//imp_arjun.log

Enter commands

Continue reading Export and Import using unix pipes and compression

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration