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 S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

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 directories must exist on unix machine for above command to work

SQL> grant read,write on directory dump_dir to arjun; —user exporting needs write priv and user importing needs read priv.

Grant succeeded.

SQL> grant read,write on directory log_dir to scott;

Grant succeeded.

ESTIMATE SIZE OF EXPORT

expdp arjun/arjun logfile=log_dir:full1.log estimate_only=y ……no parameter dump_dir allowed when using estimate_only=Y

Does not export…only estimates size of export dump.

SCHEMA EXPORT

expdp arjun/arjun schemas=arjun dumpfile=dump_dir:schema1.dmp logfile=log_dir:full1.log parallel=2 filesize=2G

Dump_dir and log_dir are the directories created above.

FULL DATABASE EXPORT

expdp system/temp full=y dumpfile=dump_dir:full.dmp logfile=log_dir:full3.log parallel=2 filesize=2G

IMPORTING USING DATAPUMP

To import into another schema, example – from arjun to scott.

impdp arjun/arjun remap_schema=arjun:scott dumpfile=dump_dir:schema.dmp logfile=log_dir:full4.log

Import into same schema name…i.e…export taken from arjun and import into arjun.

impdp system/temp schemas=arjun dumpfile=dump_dir:schema.dmp logfile=log_dir:scott1.log
The dumpfile should point to the dump_dir:*.dmp file.

FULL DATABASE IMPORT:

impdp system/temp dumpfile=dump_dir:full.dmp logfile=log_dir:full10.log

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 string /opt/oracle/, /opt/oracle/

SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_file_management string AUTO

PRIMARY SITE:

If a file is created in any other area other than /opt/oracle ( which is in the db_file_name_convert path)

EX: SQL> create tablespace arjun datafile ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’ size 10m;

Tablespace created.

SQL> alter system switch logfile;

Since the db_file_name_convert on the standby site only handles files created in /opt/oracle area, the datafile cannot be added to the standby site….

Alert log in standby site…

Media Recovery Log /opt/oracle/dgtest9i/arch/arch235.log
File #7 added to control file as ‘UNNAMED00007′. Originally created as:
‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
Recovery was unable to create the file as:
‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
MRP0: Background Media Recovery terminated with error 1274
ORACLE AUTOMATICALLY STOPS MEDIA RECOVERY -
Sun Mar 5 18:49:06 2006
Errors in file /opt/oracle/admin/dgtest9i/bdump/dgtest9i_mrp0_30121.trc:
ORA-01274: cannot add datafile ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’ – file could not be created
ORA-01119: error in creating database file ‘/u01/ORACLE/dgtest9i_bkp/arjun.dbf’
ORA-27040: skgfrcre: create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown…………..Automatically shuts down the recovery process in this case.

Check whether logs have been applied….

SQL> SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
237

select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
234

Now check the v$datafile view in standby database…..

SQL> select name from v$datafile;

NAME
——————————————————————————–
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/product9204/dbs/UNNAMED00007 – This is the new file.

To rename the file as required…

In standby database….

alter system set standby_file_management=manual;

SQL> alter database create datafile ‘/opt/oracle/product9204/dbs/UNNAMED00007′ as ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’ ;
Database altered.

SQL> select name from v$datafile;

NAME
——————————————————————————–
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/arjun1.dbf

alter system set standby_file_management=AUTO;

alter database recover managed standby database disconnect;

Now check the alertlog again….and notice recovery has started on the standby site.

ALTER SYSTEM SET standby_file_management=’MANUAL’ SCOPE=BOTH;
Sun Mar 5 18:56:23 2006
alter database create datafile ‘/opt/oracle/product9204/dbs/UNNAMED00007′
as ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’
Sun Mar 5 18:56:23 2006
Starting control autobackup
Control autobackup failed with following error :
ORA-00236: snapshot operation disallowed: mounted controlfile is a backup
Completed: alter database create datafile ‘/opt/oracle/produc
Sun Mar 5 18:57:01 2006
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH;
Sun Mar 5 18:57:08 2006
Alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=11
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 235
Datafile 1: ‘/opt/oracle/oradata/dgtest9i/system01.dbf’
Starting datafile 2 recovery in thread 1 sequence 235
Datafile 2: ‘/opt/oracle/oradata/dgtest9i/undotbs01.dbf’
Starting datafile 3 recovery in thread 1 sequence 235
Datafile 3: ‘/opt/oracle/oradata/dgtest9i/users01.dbf’
Starting datafile 4 recovery in thread 1 sequence 235
Datafile 4: ‘/opt/oracle/oradata/dgtest9i/arjun1.dbf’
Media Recovery Log /opt/oracle/dgtest9i/arch/arch235.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch236.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch237.log
Media Recovery Waiting for thread 1 seq# 238 (in transit)

Now check logs applied…..

SQL> SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
237
select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
237

Therefore it is a good idea to check the alert_log on a regular basis to make sure managed recovery has not crashed, just log shipping taking place is not enough as logs will continue to get shipped irrespective of whether recovery is in progress or not.

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 issued to convert it to ENABLE

sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

Now check again…

SQL>NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_state_2
ENABLE…………………………………PROCEED .

SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

If any file/files are in RECOVER status, DO NOT PROCEED with the switchover…

SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
MOUNTED………This is the correct response ..PROCEED with switchover.

If the STANDBY database has been opened in READ ONLY mode, you will receive the following message…

SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
READ ONLY……………….DO NOT PROCEED with switchover.

To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…

On PRIMARY site
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
14

Now on STANDBY site.
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
14

SAFE TO PROCEED.

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, incremental backups hardly took minutes to complete as opposed to many hours.

To enable block change tracking – this can be run with the database open ( as sysdba ).

alter database enable block change tracking using file ‘/hqlinux01db05/ORACLE/test/block_change.dbf’;

Database altered.

select filename,status from v$block_change_tracking;

FILENAME
——————————————————————————–
STATUS
———-
/hqlinux01db05/ORACLE/test/block_change.dbf
ENABLED

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 in db_file_name_convert has only 550 mb free.

Now create a new tablespace for the testing purpose..

SQL> create tablespace dropme datafile ‘/opt/oracle/oradata/dgtest9i/dropme.dbf’ size 200m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

[oracle@itlinuxdevblade07 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 22G 1.8G 93% /opt
[oracle@itlinuxdevblade07 dgtest9i]$

On STANDBY

[oracle@itlinuxdevblade08 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 350M 99% /opt
[oracle@itlinuxdevblade08 dgtest9i]$

Space available reduced from 550 mb to 350 mb

The logs were succesfully applied on STANDBY disk as there was enough space.

Now add a sum of more than the amount available on the standby disk by RESIZING the file on PRIMARY to the tablespace.

PRIMARY site..

SQL> Alter database datafile ‘/opt/oracle/oradata/dgtest9i/dropme.dbf’ RESIZE 1024M;
Database altered.

SQL> SQL>
SQL> alter system switch logfile;
System altered.
Go to STANDBY…

[oracle@itlinuxdevblade08 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 350M 99% /opt
[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt
total 801848
-rw-r—– 1 oracle dba 209723392 May 10 14:23 dropme.dbf
-rw-r—– 1 oracle dba 7544832 May 10 14:23 control01.ctl
[oracle@itlinuxdevblade08 dgtest9i]$

The file has not been resized….as there was not enough space…

Now check the logs for errors..

On standby …alert log shows media recovery failed as the datafile could not be resized and media recovery stops

The standby database cannot be opened now as the files need recovery..

Media Recovery Log /opt/oracle/dgtest9i/arch/arch59.log
MRP0: Background Media Recovery terminated with error 1237
Wed May 10 14:23:04 2006
Errors in file /opt/oracle/admin/dgtest9i/bdump/dgtest9i_mrp0_10198.trc:
ORA-01237: cannot extend datafile 3
ORA-01110: data file 3: ‘/opt/oracle/dgtest9i/dropme.dbf’
ORA-19502: write error on file “/opt/oracle/dgtest9i/dropme.dbf”, blockno 71297 (blocksize=8192)
ORA-27072: skgfdisp: I/O error
Linux-x86_64 Error: 2: No such file or directory
Additional information: 71296
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown

STEPS to solve this problem.

1. Shutdown both databases.

2. Create a new db_file_name_convert path as the file in question has to be moved to different disks where space exists.

*.db_file_name_convert=’/opt/oracle/dgtest9i/’,'/opt/oracle/oradata/dgtest9i/’,'/tmp/dgtest9i/’,'/tmp/dgtest9i/’…both machines.

3. Copy dropme.dbf to /tmp/dgtest9i on both machines from original area.

4. Startup mount;..PRIMARY DATABASE

5. alter database rename file ‘/opt/oracle/oradata/dgtest9i/dropmedbf’ to ‘/tmp/dgtest9i/dropme.dbf’;

6. alter database open;

7. alter database create standby controlfile as ‘/tmp/control01.ctl’ ….primary site.

8. ftp the standby controlfile to /opt/oracle/dgtest9i on standby….controlfile area in standby spfile.

9.startup nomount;…….STANDBY site.

SQL> show parameter convert;

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

SQL> alter database mount standby database;

Database altered.

SQL> select name from v$datafile;

/opt/oracle/dgtest9i/system01.dbf
/opt/oracle/dgtest9i/undotbs01.dbf
/tmp/dgtest9i/dropme.dbf

Primary site : Switch a few logfiles….

Now start managed recovery on standby.

SQL>recover managed standby database disconnect;

Alert log below…

Completed: ALTER DATABASE RECOVER managed standby database d
Wed May 10 14:54:30 2006
RFS: Possible network disconnect with primary database
Wed May 10 14:54:38 2006
Fetching gap sequence for thread 1, gap sequence 59-63
Trying FAL server: DGTEST9I_BLADE07
Wed May 10 14:54:41 2006
RFS: Successfully opened standby logfile 4: ‘/opt/oracle/dgtest9i/st01.log’
Wed May 10 14:54:56 2006
Media Recovery Log /opt/oracle/dgtest9i/arch/arch59.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch60.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch61.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch62.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch63.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch64.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch65.log
Media Recovery Waiting for thread 1 seq# 66 (in transit)

Go to /tmp/dgtest9i on STANDBY and make sure file is resized to 1 gb..

[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt
total 1049612
-rw-r—– 1 oracle dba 1073750016 May 10 14:55 dropme.dbf
[oracle@itlinuxdevblade08 dgtest9i]$ pwd

SUCCESS !

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               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       11212

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       15996

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       20246

After the delete operation is completed, we now run a ROLLBACK command to undo the
delete operation.

While the rollback is in operation, running the same query shows that the USED_UBLK
column now instead keeps decreasing in value until the query returns 'no rows selected'. 

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3389

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3376

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        2409

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        1344

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         775

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         399

SQL> /

no rows selected

At this point we can confirm that the user SH would have completed the rollback operation.

SQL> rollback;

Rollback complete.

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      25.76
       685       1586 Table Scan                 6333      24498      25.85

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 -

1. nohup gzip -dc </u02/oradata/export/arjun.dmp.gz > import_pipe &

Immediately enter next command -

2. nohup imp \’/ as sysdba\’ parfile=/u02/oradata/export/imp.par

Check the logs for output of import.

Recreate online redo logs and standby logs

In a DATAGUARD ENVIRONMENT, If online redologs are resized, the standby logs also need to be resized to match the online redo logs in the Primary database.

STEPS TO ACHIEVE THIS -

1. Drop and recreate online redologs on the PRIMARY site.

2. On primary site, create the standby controlfile -

alter database create standby controlfile as ‘/tmp/standby01.ctl’;

3. Copy the standby control file to standby site…

4. Cancel recovery, shutdown immediate the standby database.

5. Startup and mount the standby database -using the new standby controlfile.

5. Defer recovery until standby logs are created..…do not start managed recovery.

6. Add the standby logs with same size as Primary online redologs.

7. The online logs on the standby side will be created at switchover time so no need to do anything now.

8. Start recovery on standby site..

To recreate ONLY the STANDBY logs only…follow from point 2 above.

Identify active transactions in undo and rollback segments

col o format a10
col u format a10
select osuser o, username u, sid,
segment_name s, substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
And substr(sa.sql_text,1,200) is not null
order by 3;

col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select username, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

Resumable space allocation.

RESUMABLE SPACE ALLOCATION AND AFTER SUSPEND TRIGGER

A resumable statement allows you to :

1. Suspend large operations instead of receiving an error.

2. Allows you to fix the problem while the operation is suspended, rather than start from scratch.

A session remains suspended for the following reasons

1. Out of space
2. Max extents reached.
3. Space quota exceeded on tablespace.

Pre-requisites:

1. INIT.ORA prameter : RESUMBABLE_TIMEOUT=3600( 1 HOUR) , default is 7200 seconds or 2 hours…This parameter can be changed dynamically with an ‘alter system set resumable_timeout=3600′

2. Grant RESUMABLE system privilege to schema owner/user….

3. User has to issue ‘ alter session enable resumable’ before running the transaction.

EXAMPLE…

Table test in arjun schema….

SQL> create table test (col_1 number(10),col_2 date);

Table created.

Create a script called loop.sql which is created in this manner…..

vi loop.sql in unix .

declare
x number;
begin
x:= 0;
for i in 1 .. 5000
loop
insert into test
values
(x,sysdate);
x := x+1;
commit;
end loop;
end;
/
save the file as loop.sql

Now set the Resumable_timeout parameter to 0…

SQL> alter system set resumable_timeout=0 scope=both;

System altered.

SQL> @loop
declare
*
ERROR at line 1:
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
ORA-06512: at line 7

Now issue command as sysdba.

Sql> alter system set resumable_timeout=3600 scope=both;

Conn arjun/arjun

@loop

Session hangs after sometime….but no error message is thrown( see below the use of after suspend trigger to throw an error message when the session hangs)

From another session….

SQL> select count(*) from arjun.test;

COUNT(*)
———-
98900

SQL> /

COUNT(*)
———-
98900

SQL> /

COUNT(*)
———-
98900

Although space has run out tablespace arjun, the first session does not throw an error but allows you to increase space in the tablespace from another session.

The first session will wait for 1 hour – the time set by you in the resumable_timeout=3600 parameter…If you do not take corrective action, ie add space within that period, an error is thrown and transaction rolled back.

SQL> select sum(bytes/1048576) from dba_free_space where tablespace_name=’ARJUN’;

SUM(BYTES/1048576)
——————
.9375

Add space to tablespace..

SQL> Alter database datafile ‘/u03/ORACLE/test9/arjun.dbf’ resize 5m;

Database altered.

As soon as you add space the table begins to get populated again…

SQL> select count(*) from arjun.test;

COUNT(*)
———-
119097

SQL> /

COUNT(*)
———-
136680

SQL> /

COUNT(*)
———-
145413

##################################################################

USAGE OF AFTER SUSPEND TRIGGER IN RELATION TO RESUMABLE SPACE ALLOCATION:

Another very useful feature is the usage of the AFTER SUSPEND trigger which results in an error message appearing in the session, rather than the session hanging( in which case the DBA or USER will never know there is an error unless he checks from another session.)

This helps the DBA to take corrective action once the error message is thrown.

Once corrective action is taken , the original session can be restarted.

Firstly create the trigger…As SYS user.

CREATE OR REPLACE TRIGGER ARJUN_RESUMABLE
AFTER SUSPEND ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(60);
END;
/

This creates a trigger which sets the RESUMABLE_TIMEOUT to 60 seconds or 1 minute if there is a situation where a session is stuck. This is the time allowed to take corrective action before oracle aborts the transaction.

Begin inserting into the TEST table…

@loop

Hangs after sometime….

To check if a session is hanging and the error message—

From another session just select error_msg from the dba_resumable view….

SQL> select error_msg from dba_resumable;

ERROR_MSG
——————————————————————————–
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN

SQL> select count(*) from arjun.test;

COUNT(*)
———-
353926

Now the original session that was running the insert hangs for 1 minute and then throws the error….

declare
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
ORA-06512: at line 7

Take corrective action and then rerun the insert …

@loop…

SQL> select count(*) from arjun.test;………notice the session has resumed and count increases….

COUNT(*)
———-
416321

SQL> /

COUNT(*)
———-
421232

SQL> /

COUNT(*)
———-
425740

Locked Sessions and Locked Objects

PROMPT Blocked and Blocker Sessions

col blocker_sid format 99999999999
col blocked_sid format 99999999999
col min_blocked format 99999999999
col request format 9999999
select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock blocked
where
blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1;

prompt blocked objects from V$LOCK and SYS.OBJ$

set lines 132
col BLOCKED_OBJ format a35 trunc
select /*+ ORDERED */
l.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||'.'||o.NAME blocked_obj
from (select *
from v$lock
where type='TM'
and sid in (select sid
from v$lock
where block!=0)) l
, sys.obj$ o
, sys.user$ u
where o.obj# = l.ID1
and o.OWNER# = u.user#
/

prompt blocked sessions from V$LOCK

select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock blocked
where blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1
/

prompt blokers session details from V$SESSION

set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc
SELECT sid
, serial#
, username
, osuser
, machine
FROM v$session
WHERE sid IN (select sid
from v$lock
where block != 0
and type = 'TX')
/

Flashback query

FLASHBACK QUERY

FLASHBACK QUERY feature allows you to perform queries on the database as of a certain time or user-specified SCN.

FLASHBACK VERSIONS QUERY feature allows you to use the VERSIONS clause to retrieve all of the versions of the rows that exist between two points in time, or two SCN’s.

YOU REQUIRE THE SELECT ANY TRANSACTION PRIVILEGE TO BE ABLE TO ISSUE A QUERY AGAINST FLASHBACK_TRANSACTION_QUERY.

SQL> create table test(salary number(10));

Table created.

SQL> insert into test values(1000);

1 row created.

SQL> select * from test;

SALARY
———-
1000

SQL> update test set salary=2000;

1 row updated.

SQL> commit;

Commit complete.

SQL> update test set salary=4000;

1 row updated.

SQL> commit;

Commit complete.

SQL> update test set salary=5000;

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from test versions between scn minvalue and maxvalue;

SALARY
———-
5000
4000
2000

Using FLASHBACK VERSIONS query

select salary from test versions between timestamp
TO_TIMESTAMP( ‘2005-09-12 14:38:00′,’YYYY-MM-DD HH24:MI:SS’) and
TO_TIMESTAMP( ‘2005-09-12 14:43:00′,’YYYY-MM-DD HH24:MI:SS’);

SALARY
———-
5000
4000
2000

Select to_char(versions_starttime,’DD-MON HH:MI’) “START DATE”,
to_char (versions_endtime,’DD-MON HH:MI’) “END DATE”,VERSIONS_XID,VERSIONS_OPERATION,empno FROM VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

START DATE END DATE VERSIONS_XID V SALARY
———— ———— —————- – ———-
12-SEP 02:40 0700190004010000 U 5000
12-SEP 02:40 12-SEP 02:40 0A00290001020000 U 4000
12-SEP 02:40 12-SEP 02:40 07000C0005010000 I 2000

Using FLASHBACK TRANSACTION query:

The flashback transaction query helps to get the actual query run:

SQL> Select table_name,operation, undo_sql from flashback_transaction_query Where XID=’06002C00F7060000′;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–
TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘1000′ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TEST
INSERT
delete from “ARJUN”.”TEST” where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–

To narrow down to a point-in-time…

Select table_name,operation ,undo_sql
From flashback_transaction_query where start_timestamp >= to_timestamp ( ‘2005-09-12 14:38:00′,’YYYY-MM-DD HH24:MI:SS’)
AND TABLE_OWNER=’ARJUN’;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–
TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘4000′ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘2000′ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

Please remember that the FLASHBACK VERSIONS QUERY cannot be used to query:

1. EXTERNAL TABLES
2. TEMPORARY TABLES
3. FIXED TABLES
4. VIEWS

IT also cannot span DDL commands, ie…alter table etc..

Flashback table

FLASHBACK TABLE

Only FLASHBACK DATABASE requires flashback to be ‘ON’ since only FLASHBACK DATABASE uses flashback logs in the flash_recovery_area.

All other forms of FLASHBACK use the recycle bin and undo_tablespace.

Important info:

You cannot ‘flashback table to before drop’ a table which has been created in the SYSTEM tablespace.

The table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace and that tablespace must be locally managed.

When you drop a table, the objects are temporarily placed in a ‘recycle bin’ and still belong to the owner.

The space used by recycle bin objects is never reclaimed unless there is space pressure.

The space associated with the dropped object is not immediately reclaimable although it appears in the DBA_FREE_SPACE view.

Query the dba_recyclebin view as SYS or just recyclebin as the user for information about the recycle bin.

Flashback drop allows you to recover a dropped table.

Example.

Connect arjun/arjun

Create table tempp (col_1 number(10)) tablespace users;

Insert into tempp values (10);

1 row created.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI2Gp/QiZsPgQAw5yUImEA==$0 TABLE 2005-09-12:10:49:16

SQL> flashback table tempp to before drop;

Flashback complete.

SQL> select * from tempp;

COL_1
———-
10

In case the table is created in the system tablespace :

SQL> show user

USER is “SYS”…..Default tablespace SYSTEM

SQL> create table arjun1 (col_1 number(10));

Table created.

SQL> drop table arjun1;

Table dropped.

SQL> select * from dba_recyclebin;

no rows selected

SQL> flashback table arjun1 to before drop;
flashback table arjun1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Permanently drop without recycle bin
Drop table test purge;

QUERYING DROPPED TABLES

Dropped tables can be queried from the recycle bin. No DML or DDL operations are allowed on the table.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI9AwvFRdf7gQAw5yUIsGA==$0 TABLE 2005-09-12:13:15:22

While querying the recycle bin, make sure the system generated table name is enclosed in double quotes.

SQL> select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0;
select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0′;
select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0′
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from “BIN$AI9AwvFRdf7gQAw5yUIsGA==$0″;

COL_1
———-
10

You cannot run any DML or DDL on dropped tables ….

SQL> Insert into tempp values(20);
Insert into tempp values(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist

FLASHBACK TABLE TO A TIME IN THE PAST.

Firstly enable row movement for table test..

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

TIME : 08:00:00

SQL> select * from test;

SALARY
———-
5000

TIME :08:00:01

SQL> update test set salary =6000;

1 row updated.

SQL> select * from test;

SALARY
———-
6000

SQL> commit;

Commit complete.

Now flashback table to time 08:00:00

SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( ‘2005-09-13 08:00:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> SELECT * FROM TEST;

SALARY
———-
5000

Identify database idle sessions

set linesize 140
col username format a15
col idle format a15
col program format a30

PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT

select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

Identify database SID based on OS Process ID

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

Identify OS process ID based on database SID

col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;

MANUAL INSTALLATION OF DB CONTROL

To manually install and administer a database using DBCONTROL -

cd $ORACLEHOME/bin
oraofsap(DATABASE)@tmpu023:pwd
/u01/app/oracle/product/10.2/bin
oraofsap(DATABASE)@tmpu023:

oraofsap(DATABASE)@tmpu023:./emca -config dbcontrol db -repos create

STARTED EMCA at Oct 23, 2008 10:23:59 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: ofsam
Listener port number: 1521
Password for SYS user: ——– ENTER SYS PASSWORD
Password for DBSNMP user:——ENTER DBSNMP PASSWORD
Password for SYSMAN user: – no user exists so just give any password.
Email address for notifications (optional): Enter
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME …………… /u01/app/oracle/product/10.2

Database hostname ……………. tmpu023.bankwest.com
Listener port number ……………. 1521
Database SID ……………. ofsam
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
Oct 23, 2008 10:26:36 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2/cfgtoollogs/emca/ofsam/emca_2008-10-23_10-23-58-AM.log.
Oct 23, 2008 10:26:40 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …

Oct 23, 2008 10:30:52 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Oct 23, 2008 10:31:07 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Oct 23, 2008 10:32:04 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Oct 23, 2008 10:32:04 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Oct 23, 2008 10:33:48 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 23, 2008 10:33:48 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://tmpu023.bankwest.com:1158/em <<<<<<<<<<< – use the I.P address also -https://10.254.203.177:1158/em
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 23, 2008 10:33:48 AM
oraofsap(DATABASE)@tmpu023:

List all tablespaces with free space < 10%

set pagesize 300
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'

ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
set echo off 

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

Monitor the Flashback area space usage

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE; 

SELECT * FROM V$RECOVERY_FILE_DEST; 

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name format a40

select name, round(space_limit/1048576),round(space_used/1048576)
from  v$RECOVERY_FILE_DEST;

Identify ‘hot’ objects when faced with ‘Cache Buffers Chains Latch’ wait event

select
   count(*)    child_count,
   sum(gets)   sum_gets,
   sum(misses) sum_misses,
   sum(sleeps) sum_sleeps
from
   v$latch_children
where
   name = 'cache buffers chains';

select
   p1 "File #".
   p2 "Block #"
from
   v$session_wait
where
   event ='cache buffer chains';

select
   owner,
   segment_name,
   segment_type
from
   dba_extents
where
   file_id = &P1
and
 &P2 between block_id and block_id + blocks -1;

col object_name format a20
col owner format a10
col subobject_name format a20

select distinct o.owner, o.object_name, o.subobject_name, o.object_type
from dba_objects o, v$bh b
where o.data_object_id=b.objd and b.objd < power(2,22) and status != 'free'
  and b.file#=&P1
  and b.block#=&P2
;

FLASHBACK DATABASE

FLASHBACK DATABASE

Flashback database allows you to rollback database to a time in the past.

Useful if you have :

1. Dropped user
2. Truncated table
3. Batch job:Partial changes.

PRE-REQUISITE: DATABASE MUST BE IN ARCHIVELOG MODE AND YOU MUST SET FLASHBACK ON( BY DEFAULT IT IS OFF)

FLASHBACK LOGS ARE CREATED IN YOUR FLASH_RECOVERY_AREA SPECIFIED BY THE INIT.ORA PARAMETER – DB_RECOVERY_FILE_DEST

To set FLASHBACK ON in your 10g database.

shutdown immediate;

startup mount exclusive;….Must be mounted exclusive.

alter database flashback on;

alter database open;

To Flashback database…

Flashback database can be issued with 3 different conditions:

1. TO_TIME
2. TO SCN
3. TO SEQUENCE( LOG ARCHIVE SEQ)

FLASHBACK TO_TIME

PRESENT TIME IS 2009-06-29 07:40:00

drop user arjun cascade;

user dropped.

shutdown immediate;

startup mount;

flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:38:00′,’YYYY-MM-DD HH24:MI:SS’);

alter database open resetlogs;

select username from dba_users;

SQL> select username from dba_users;

USERNAME
——————————
SYS
SYSTEM
DBSNMP
ARJUN
OUTLN

After this is done you cannot flashback the database to a time before the original flashback…

Shutdown immediate;

Startup mount;

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’);
flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’)
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

But you can flashback the tablespace to a time after the original flashback time of 07:38:00

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:45:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

alter database open resetlogs;

FOR FLASHBACK TO SCN….

Alter database mount;

Flashback database to scn=12355;

To get current_scn: select current_scn from v$database;

TO FLASHBACK TO SEQUENCE NUMBER…

Alter database mount;

Flashback database to sequence=223 thread=1;

Monitor space used in ASM Disk Groups

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

ARCHIVE AREA FULL – CONNECT INTERNAL UNTIL FREED

When a database is running in ARCHIVELOG mode, if the area which stores the archivelogs on disk gets full, the database comes to a halt and can be accessed only as a sysdba.

The error message when you try to connect as a normal user is ‘ CONNECT INTERNAL UNTIL FREED’

If the following parameter is in use – DB_RECOVERY_FILE_DEST as the archivelog destination , then the size of the parameter DB_RECOVERY_FILE_DEST_SIZE can be increased dynamically and then the database begins to function again -

Ex

SQL> show parameter db_recovery_file_dest;

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /opt/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 1000M

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
——————————————————————————–
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
/opt/oracle/flash_recovery_area

1048576000 1048260608 0 163

There are a couple of possible options.

1) Increase the parameter db_recovery_file_dest_size, which is dynamic.

SQL> alter system set db_recovery_file_dest_size=XG; (larger amount) – Example 2G

2) Stop using the db_recovery_file_dest by unsetting the parameter.
( This assumes you never really wanted to use this option )

Recovery from a backup taken before a RESETLOGS

Simulate loss of redo log files 

[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log

Check alert log 

Errors in file /u02/ORACLE/opsdba/bdump/opsdba_arc1_29898.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ORACLE/opsdba/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Jun 28 12:50:59 2006
ARC1: Failed to archive thread 1 sequence 14 (0)

RECOVER DATABASE UNTIL LAST LOG SEQUENCE (13) 

RMAN> run {
2> set until logseq=14 thread=1;  >>>> Note add one to the max applied log sequence
3> restore database;
4> recover database;
5> }


executing command: SET until clause

Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 28-JUN-06

Starting recover at 28-JUN-06
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_05hmonkt
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_05hmonkt tag=TAG20060628T123748
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:05
Finished recover at 28-JUN-06

RMAN> exit
Recovery Manager complete.

[oracle@itlinuxdevblade08 opsdba]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 12:57:34 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> alter database open resetlogs;
Database altered.

SQL> conn scott/tigerConnected.
SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1271227

RECOVERY COMPLETE !

GENERATE SOME MORE ARCHIVE LOGS 

SQL> exec load_data;

PL/SQL procedure successfully completed.

SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1678207

NOTE: NO BACKUP HAS BEEN TAKEN AFTER RESETLOGS - AGAIN SIMULATE FAILURE

[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log

[oracle@itlinuxdevblade08 opsdba]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:11:35 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  2020512 bytes
Variable Size             171969376 bytes
Database Buffers          146800640 bytes
Redo Buffers                6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/ORACLE/opsdba/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN> list incarnation of database;  ; 

NOTE - incarnation has not changed after resetlogs

new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
185729689 185729700 OPSDBA   1480400868       PARENT  525876     23-JUN-06
185729689 185729690 OPSDBA   1480400868       PARENT  654443     27-JUN-06
185729689 185817798 OPSDBA   1480400868       CURRENT 685999     28-JUN-06

RECOVER AGAIN - NOTICE HOW ALL ARCHIVE LOG FILES ARE APPLIED –

BOTH BEFORE RESETLOGS AS WELL AS AFTER RESETLOGS

Note:After RESETLOGS log sequence 8 is the last log sequence applied -
so we recover until sequence 9


RMAN> run {
2> set until sequence=9 thread =1;
3> restore database;
4> recover database;
5> }


executing command: SET until clause

Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 28-JUN-06

Starting recover at 28-JUN-06
using channel ORA_DISK_1

starting media recovery

NOTE: Applying archived log files BEFORE resetlogs

archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594244455.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log

NOTE: Applying archived log files AFTER resetlogs

archive log thread 1 sequence 1 is already on disk as file /u02/ORACLE/opsdba/arch/arch.1.1.594305864.log
archive log thread 1 sequence 2 is already on disk as file /u02/ORACLE/opsdba/arch/arch.2.1.594305864.log
archive log thread 1 sequence 3 is already on disk as file /u02/ORACLE/opsdba/arch/arch.3.1.594305864.log
archive log thread 1 sequence 4 is already on disk as file /u02/ORACLE/opsdba/arch/arch.4.1.594305864.log
archive log thread 1 sequence 5 is already on disk as file /u02/ORACLE/opsdba/arch/arch.5.1.594305864.log
archive log thread 1 sequence 6 is already on disk as file /u02/ORACLE/opsdba/arch/arch.6.1.594305864.log
archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594305864.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594305864.log
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
archive log filename=/u02/ORACLE/opsdba/arch/arch.1.1.594305864.log thread=1 sequence=1
archive log filename=/u02/ORACLE/opsdba/arch/arch.2.1.594305864.log thread=1 sequence=2
archive log filename=/u02/ORACLE/opsdba/arch/arch.3.1.594305864.log thread=1 sequence=3
archive log filename=/u02/ORACLE/opsdba/arch/arch.4.1.594305864.log thread=1 sequence=4
archive log filename=/u02/ORACLE/opsdba/arch/arch.5.1.594305864.log thread=1 sequence=5
archive log filename=/u02/ORACLE/opsdba/arch/arch.6.1.594305864.log thread=1 sequence=6
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594305864.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594305864.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:09
Finished recover at 28-JUN-06

RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit

ALL DATA RECOVERED UNTIL POINT OF FAILURE 

[oracle@itlinuxdevblade08 opsdba]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:34:51 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1678207

Using FLASHBACK after a Data Guard Failover

Original Data Guard Environment

HQLINUX05 - PRIMARY
ITLINUX03 - STANDBY.

Perform FAILOVER TO ITLINUX03 (now the new Primary)

Next in ITLINUX03 .....

Step 1: On the new primary, run the following query to determine at what SCN it became
the new primary.

bozo:/u01/ORACLE/bozo> sql

SQL*Plus: Release 10.1.0.2.0 - Production on Mon May 9 14:48:57 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
385053

Step2: Bring the old primary database to the mount state and flashback to SCN retrieved in Step 1.

Go to original primary machine (HQLINUX05)…

SQL>
 shutdown immediate;
 startup mount;
 flashback database to scn 387774;

Flashback complete.

Step3:
SQL> alter database flashback off;  - since flashback logs are no more valid.

Step4: 

On the old primary(hqlinux05), create a standby controlfile

SQL>alter database create standby controlfile as '/tmp/standby1.ctl';

Step 5:

SQL>Shutdown immediate;

Copy the standby control file to the control_files location in the init.ora(hqlinux05)
which is the original primary.

rename the standby controlfile standby1.ctl to match the names of the control files
as defined in the control_files parameter in the init.ora.

Step 6:

SQL> Startup mount;

SQL> Alter database flashback on;

On the new primary(itlinux03) enable log shipping…

SQL> alter system set log_archive_dest_state_2=ENABLE  scope=both;

On the new standby/old primary start managed recovery - (hqlinux05)

SQL> Alter database recover managed standby database disconnect;

RMAN Block Recovery

From Oracle 9i onwards you can use RMAN to recover only blocks while database is up and
running.

This could possibly save hours and hours of recovery time as a full database restore
 is not necessary.

Error reported by user pointing to block corruption.

 POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted
(file # 48, block # 142713)
ORA-01110: data file 48: '/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf

Check first if the there is only one(few) blocks corrupted or most of the blocks are
corrupted.

macsl:/opt/oracle/admin/macsl/bdump>

Issue command below at UNIX prompt.

dbv file=/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf BLOCKSIZE=8192
LOGFILE=test.log

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBV-00200: Block, dba 201469305, already marked corrupted

macsl:/opt/oracle/admin/macsl/bdump> vi test.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 21107
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 2550111754 (1040.2550111754) 

You can get the list of corrupted blocks from  v$database_block_corruption

SQL> Select * from v$database_block_corruption;

You will get block number corrupt.
Ex: block 142713.

After that LOGIN TO RMAN.

macsl:/opt/oracle/admin/macsl/bdump> rman target / catalog rman10/rman10@rman10p
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 4 14:33:26 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: MACSL (DBID=1125502194)
connected to recovery catalog database

RMAN> BLOCKRECOVER DATAFILE 48 BLOCK 142713;;

Starting blockrecover at 04-OCT-06
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=119 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00048
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.1.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
piece handle=MACSL.20061004.7379.1.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.2.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 2
piece handle=MACSL.20061004.7379.2.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:04:01

starting media recovery
media recovery complete, elapsed time: 00:00:46

Finished blockrecover at 04-OCT-06

Additional information:

V$database_block_corruption is the view to check the list of corrupted blocks.

If you have multiple block list as corrupt, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;

Daily DBA Checklist

• Ensure that previous night's backup is complete and there are no RMAN errors in the backup logs.

• Ensure that any exports which are part of the backup are complete and the dump files
   compressed.

• Check the alert log for any ORA- errors - also for messages like 'Checkpoint not complete etc'.

• Ensure that the cron job for truncating, saving and renaming alert logs is working - verify the
  same.

• Ensure that the archive redo log files are compressed and have been deleted. Only files for
 current and previous day should be present.

• All tablespaces should be less than 95% full - run the coalesce command on all tablespaces to
  reduce fragmentation. Ensure that space in the TEMP tablespace is released and is 100% free
  at the beginning of the day.

• Enough contiguous free space is available in all tablespaces for objects to extend if required.

• Backup the control file to trace so that every day we have a outline of the files and their
  locations for each database.

• No objects are within 5 extents of the MAXEXTENTS storage parameter.

• All core dumps are deleted from the $CDUMP area.

• All *.trc files are deleted from the $UDUMP area.

• Check the machine for any disks 100% full or nearing that value. If a disk has filled up use
  the 'find' command to determine files which have been recently created/modified . Ensure that all
  *.dmp files are in their proper locations and large *.dmp files have been compressed.

• Truncate the listener.log file in the $ORACLE_HOME/network/log location if the listener log has
  increased to a size > than 500 MB. Ensure the space is released, otherwise 'reload' listener.

• Run the 'recently created/modified objects' report to ensure that no unauthorised object
  creation/modification is taking place.

• Ensure that there are no DBMS_JOBS with the status of failed or broken. Also last refresh times
  of all running jobs should be current.

• Check to ensure that no objects exist in the database with the status 'INVALID'

Managing space in the PERFSTAT tablespace

You can create the following procedure in your perfstat schema if you want to delete
the old statistics and rebuild the objects to free up the space in PERFSTAT tablespace.

create or replace procedure perfstat_freespace is
 cursor c1 is
        SELECT table_name from user_tables ;
 cursor c2 is
        SELECT index_name from user_indexes ;
 l_str varchar2(200) ;
begin
  delete from stats$snapshot where snap_time < sysdate - 10 ;
  commit ;
  for i in c1 loop
     l_str := 'alter table '||i.table_name||' move ' ;
     execute immediate l_str ;
  end loop ;
  for i in c2 loop
     l_str := 'alter index '||i.index_name||' rebuild ' ;
     execute immediate l_str ;
  end loop ;
end ;

You can submit a job to execute the procedure every 10 day to delete the old statistics :

 declare
  jobno number;
 begin
   dbms_job.submit(:jobno, '  perfstat_freespace ;',
          trunc(sysdate+1) + 6/24, 'sysdate + 1');
   commit ;
 end ;

10g Data Guard Broker Fast Start Failover

FAST START FAILOVER IS DISABLED BY DEFAULT

Configuration
  Name:                streams2.hq.emirates.com
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED  Databases:
    streams2 - Primary database
    blade08  - Physical standby database

Current status for "streams2.hq.emirates.com":
SUCCESS

PREPARE TO ENABLE TO FAST START FAILOVER

DGMGRL> EDIT DATABASE 'streams2' SET PROPERTY FastStartFailoverTarget = 'blade08';
Property "faststartfailovertarget" updated
DGMGRL> EDIT DATABASE 'blade08' SET PROPERTY FastStartFailoverTarget = 'streams2';
Property "faststartfailovertarget" updated

WE SET THE THRESHOLD FOR FAILOVER TO 60 SECONDS.
OBSERVER WILL MONITOR FOR NO RESPONSE > 60 SECONDS

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold =60;
Property "faststartfailoverthreshold" updated

FROM ANOTHER SESSION START OBSERVER - IT WILL RUN IN THE FOREGROUND 

streams2:/opt/oracle/product10gr2/db/network/admin>dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> start observerObserver started

ENABLE THE FAST START FAILOVER 

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

CONFIRM THE CONFIGURATION IF FAST START FAILOVER IS ENABLED 

DGMGRL> show configuration;

Configuration
  Name:                streams2.hq.emirates.com
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    streams2 - Primary database
    blade08  - Physical standby database
             - Fast-Start Failover target

Current status for "streams2.hq.emirates.com":
SUCCESS

SIMULATE A FAILURE BY SHUTTING DOWN CURRENT PRIMARY DATABASE - STANDBY

standby:/opt/oracle/product10gr2/db/network/admin>sql

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.

AFTER ONE MINUTE HAS LAPSED, YOU WILL SEE THAT THE OBSERVER HAS INITIATED A FAILOVER
12:51:28.78  Tuesday, May 15, 2007
Initiating fast-start failover to database "streams2"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "streams2"
12:51:40.35  Tuesday, May 15, 2007

CONFIRM THAT THE NEW PRIMARY IS NOW STREAMS2

DGMGRL> show configuration;

Configuration
  Name:                streams2.hq.emirates.com
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    streams2 - Primary database
    blade08  - Physical standby database (disabled)
             - Fast-Start Failover target

Current status for "streams2.hq.emirates.com":
Warning: ORA-16608: one or more databases have warnings

USING FLASHBACK WE CAN REINITIATE THE CURRENT STANDBY - BLADE08 - NO NEED TO RECREATE FROM SCRATCH

Using FLASHBACK to Reinstate a Standby after a FAILOVER

PERFORM A FAILOVER USING DATA GUARD BROKER

DGMGRL> failover to 'standby_blade07';
Performing failover NOW, please wait...
Failover succeeded, new primary is "standby_blade07"

On the NEW PRIMARY issue this command to obtain the SCN to FLASHBACK the old primary(new standby) to

SQL> conn / as sysdba
Connected.

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
2147132

FLASHBACK THE OLD PRIMARY AND CONVERT IT TO A PHYSICAL STANDBY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  574619648 bytes
Fixed Size                  2022408 bytes
Variable Size             327156728 bytes
Database Buffers          239075328 bytes
Redo Buffers                6365184 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO SCN 2147132;
Flashback complete.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL>

USING DATA GUARD BROKER REINSTATE THE NEW STANDBY DATABASE INTO THE DATA GUARD CONFIGURATION

DGMGRL> reinstate database 'streams2';Reinstating database "streams2", please wait...
Operation requires shutdown of instance "streams2" on database "streams2"
Shutting down instance "streams2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "streams2" on database "streams2"
Starting instance "streams2"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "streams2" ...
Reinstatement of database "streams2" succeeded

DGMGRL> show configuration;
Configuration
  Name:                streams2.hq.emirates.com
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    streams2        - Physical standby database
    standby_blade07 - Primary database
Current status for "streams2.hq.emirates.com":
SUCCESS

RMAN – Clear Saved Configuration

To clear the saved configuration in rman – example the configuration for Channel 1

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

starting full resync of recovery catalog
full resync complete
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/ora_export/backup/fin/%U’ MAXPIECESIZE 16 G;
old RMAN configuration parameters are successfully deleted
starting full resync of recovery catalog
full resync complete

Unix – remove file with unseen characters

This works on AIX – Please test on other systems.

If a file name is vague and can’t be removed.

Ex – a file name has some hidden characters which prevents it’s removal as the name is not recognized by the rm command.

ls -lai sqlnet.log
19462 -rw-r—– 1 oraofsap dba 7741 Jun 4 13:14 sqlnet.log

look for number against file and rm number .

find . -inum 19462 -exec rm {} \;

Using the 10g Resource Manager to manage INACTIVE sessions

CREATE THE PLAN

 begin
dbms_resource_manager.create_pending_area();
end;
/

 begin
dbms_resource_manager.create_plan( plan => 'TEST_PLAN', comment => 'Resource plan/method for Idle
time kill sessions');
end;
/

CREATE THE CONSUMER GROUPS

begin
dbms_resource_manager.create_consumer_group( consumer_group => 'LONG_RUNNING',
comment =>'Privileged Users');
dbms_resource_manager.create_consumer_group( consumer_group => 'SHORT_RUNNING',
comment => 'Under Privileged Users');
end;
/

CREATE DIRECTIVES FOR THE PLAN

Consumer Group LONG_RUNNING will have idle time set to 5 MINUTES (300 seconds)

Consumer Group SHORT_RUNNING will have idle time set to 1 Minute (60 seconds)

Users falling in the Consumer Group OTHER_GROUPS will have idle time set to 1 Hour

begin
 dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
'LONG_RUNNING', comment => 'Limit idle time to 5 minutes', max_idle_time => 300);
dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
'SHORT_RUNNING', comment => 'Limit idle time to 1 minute', max_idle_time => 60);
dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
 'OTHER_GROUPS', comment => 'Limit idle time to 1 Hour', max_idle_time => 3600);
 end;
 /

VALIDATE AND SUBMIT THE PLAN

begin
dbms_resource_manager.validate_pending_area();
end;
/

begin
dbms_resource_manager.submit_pending_area();
end;
/

ALLOW OR DISALLOW CONSUMER GROUP SWITCHING

begin
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SYSTEM',
consumer_group => 'LONG_RUNNING', grant_option => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SH',
consumer_group => 'SHORT_RUNNING', grant_option => FALSE);
end;
/

ASSIGN DATABASE USERS A DEFAULT INITIAL CONSUMER GROUP AT CONNECTION TIME

User SYSTEM is made member of the group LONG_RUNNING

User SH is made member of the group SHORT_RUNNING

begin
dbms_resource_manager.create_pending_area();
end;
/

begin
dbms_resource_manager.set_initial_consumer_group( user => 'SYSTEM',
consumer_group => 'LONG_RUNNING');
dbms_resource_manager.set_initial_consumer_group( user => 'SH',
consumer_group => 'SHORT_RUNNING');
end;
 /

VALIDATE AND SUBMIT THE LAST ACTIONS MADE TO THE PLAN

 begin
dbms_resource_manager.validate_pending_area();
end;
/

begin
dbms_resource_manager.submit_pending_area();
end;
/

MAKE THE NEW PLAN THE ACTIVE ONE FOR THE DATABASE

SQL> alter system set resource_manager_plan=’TEST_PLAN’;

TEST

This query will show the sessions that are killed when the idle time thresholds set for the plan are crossed.

 SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';

NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
-------------------------------- ---------------------- --------------------
SHORT_RUNNING                                         0                    0

Initially this will show no sessions are killed

Connect as user SH from another session

apex:/u01/oracle/scripts> sqlplus sh/SH

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 10:26:54 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

After ONE Minute has elapsed from the first session run the same query again

SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';

NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
-------------------------------- ---------------------- --------------------
SHORT_RUNNING                                         0                    1

It now shows that one session has been killed

Confirm that the session has actually been killed

SQL> quit
ERROR:
ORA-02396: exceeded maximum idle time, please connect again

Export and Import schema statistics

Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

SCHEMA NAME – TEST

Create the table to store the statistics-
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema stats – will be stored in the ‘STATS_TABLE’
EXEC DBMS_STATS.export_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats
EXEC DBMS_STATS.drop_stat_table(‘TEST’,'STATS_TABLE’);

11g RMAN REPAIR ADVISOR

USING RMAN REPAIR ADVISOR:

Export ORACLE_SID=test1

sqlplus “sys as sysdba”

Shutdown immediate.

Go to c:\app\bb16872\test1 and rename SYSTEM01.DBF to SYSTEM01.old

Next try to startup database…will fail with error that SYSTEM01.DBF is missing.

Y:\>rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Wed Apr 30 10:51:03 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST1 (DBID=1030433487, not open)

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08 System datafile 1: ‘C:\APP\BB16872\T
EST1\SYSTEM01.DBF’ is missing

RMAN> list failure detail;
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened

RMAN>

RMAN> advise failure;
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file C:\APP\BB16872\TEST1\SYSTEM01.DBF was unintentionally renamed or
moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;

RMAN> repair failure noprompt;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;

executing repair script

Starting restore at 30-APR-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\BB16872\TEST1\SYSTEM01.DB
F
channel ORA_DISK_1: reading from backup piece C:\APP\BB16872\BACKUP\1_TEST1_6533
95264
channel ORA_DISK_1: piece handle=C:\APP\BB16872\BACKUP\1_TEST1_653395264 tag=TAG
20080430T104104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 30-APR-08

Starting recover at 30-APR-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 30-APR-08
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened.

In case of loss of controlfile….
Follow same steps above –remove controlfile and then try to startup.

RMAN> repair failure noprompt;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_3514023340.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘C:\APP\BB16872\TEST1\CONTROL03.CTL’;
sql ‘alter database mount’;
executing repair script

Starting restore at 30-APR-08
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=C:\APP\BB16872\TEST1\CONTROL01.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL02.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL03.CTL
Finished restore at 30-APR-08

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
database opened

RMAN> exit

Recovery Manager complete.

Y:\>sqlplus “sys as sysdba”

SQL*Plus: Release 11.1.0.6.0 – Production on Wed Apr 30 11:17:04 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_Data_Files;
FILE_NAME
——————————————————————————–

C:\APP\BB16872\TEST1\SYSTEM01.DBF
C:\APP\BB16872\TEST1\SYSAUX01.DBF
C:\APP\BB16872\TEST1\UNDOTBS01.DBF
C:\APP\BB16872\TEST1\USERS01.DBF

SQL>

Querying the Alert Log using External Tables

Run this PL/SQL block as SYS. This will create the Directory, grant the required privileges on the Directory and then create the External Table in the SYSTEM schema.

DECLARE
  BDumploc  VARCHAR2(200);
  ORASID       VARCHAR2(16);
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
   SELECT value
  INTO BDumploc
  FROM v$parameter
  WHERE name='background_dump_dest';
  -- create the directory for the bdump dir
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
    BDumploc||'''';
  -- grant the necessary privileges
  EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
  -- get the SID
  SELECT instance_name INTO ORASID FROM v$instance;
  -- create the external table
  EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXT
    (TEXT VARCHAR2(255)
    ) ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY BDUMP_DIR
     ACCESS PARAMETERS
     (records delimited by newline
      nobadfile
      nologfile
     )
     LOCATION (''alert_'||ORASID||'.log'')
    )
    REJECT LIMIT UNLIMITED'
  ;
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/

Create the function which will handle dates in the alert log

CREATE OR REPLACE FUNCTION system.get_alert_log_date( text IN VARCHAR2 )
  RETURN DATE
IS
  InvalidDate  EXCEPTION;
  PRAGMA EXCEPTION_INIT(InvalidDate, -1846);
BEGIN
  RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY'
    ,'NLS_DATE_LANGUAGE=AMERICAN');
EXCEPTION
  WHEN InvalidDate THEN RETURN NULL;
END;
/

Create the View to query the External Table

CREATE OR REPLACE VIEW system.read_alert_log as
SELECT
      LAST_VALUE(low_row_num IGNORE NULLS)
         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW) start_row
      ,LAST_VALUE(alert_date  IGNORE NULLS)
         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW) alert_date
      ,alert_text
FROM (SELECT ROWNUM row_num
            ,NVL2(system.get_alert_log_date(text),ROWNUM,NULL) low_row_num
            ,system.get_alert_log_date(text) alert_date
            ,text alert_text
      FROM system.alert_log_ext
     )
;

Query the External Table – this will list all alert log entries for the past day which have the string ‘ORA-’

SELECT alert_text
FROM system.read_alert_log
WHERE start_row IN (SELECT start_row
FROM system.read_alert_log
WHERE REGEXP_LIKE(alert_text,'ORA-')
)
AND alert_date > TRUNC(SYSDATE)-1
/

Determing the optimal UNDO tablespace size

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]“,
       SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]“,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      “NEEDED UNDO SIZE [MByte]“
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/

set feedback off
set heading off
set lines 132
declare
  cursor get_undo_stat is
         select d.undo_size/(1024*1024) "C1",
                substr(e.value,1,25)    "C2",
                (to_number(e.value) * to_number(f.value) *
g.undo_block_per_sec) / (1024*1024) "C3",
                round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec)))             "C4"
           from (select sum(a.bytes) undo_size
                   from v$datafile      a,
                        v$tablespace    b,
                        dba_tablespaces c
                  where c.contents = 'UNDO'
                    and c.status = 'ONLINE'
                    and b.name = c.tablespace_name
                    and a.ts# = b.ts#)  d,
                v$parameter e,
                v$parameter f,
                (select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat)  g
          where e.name = 'undo_retention'
            and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) ||
'To optimize UNDO you have two choices :');
dbms_output.put_line('==================================================
==' || chr(10));
  for rec1 in get_undo_stat loop
      dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
      dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' || ltrim(TO_CHAR(rec1.c2/60,'999999'))
 || ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
      dbms_output.put_line(chr(10));
      dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||
' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
      dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' ||
TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
  end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/

select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;

11g – using the PIVOT and UNPIVOT commands

11g has a very good new feature for SQL*PLUS called PIVOT and UNPIVOT - it is very useful for
creating cross tabular reports and aggregates.

In earlier Oracle versions this would have needed a lot of coding using the DECODE function ....

Objective - find the count of employees in each department broken down by the job performed.

 SQL>
  select * from
    (select deptno,job from emp
    )
PIVOT
    (
    count(*)
  for job in ('SALESMAN','CLERK','MANAGER','ANALYST')
     )
;

SQL>
    DEPTNO 'SALESMAN'    'CLERK'  'MANAGER'  'ANALYST'
---------- ---------- ---------- ---------- ----------
        30          4          1          1          0
        20          0          2          1          2
        10          0          1          1          0

This operation can be reversed using the UNPIVOT command

SQL>
 select * from pivot_emp
  UNPIVOT
  (employee_count for job in ("'SALESMAN'","'CLERK'","'MANAGER'","'ANALYST'")
    );

    DEPTNO JOB        EMPLOYEE_COUNT
---------- ---------- --------------
        30 'SALESMAN'              4
        30 'CLERK'                 1
        30 'MANAGER'               1
        30 'ANALYST'               0
        20 'SALESMAN'              0
        20 'CLERK'                 2
        20 'MANAGER'               1
        20 'ANALYST'               2
        10 'SALESMAN'              0
        10 'CLERK'                 1
        10 'MANAGER'               1
        10 'ANALYST'               0
12 rows selected.

Objective - find count which year were most employees hired in

SQL> select empno,hiredate from emp;

     EMPNO HIREDATE
---------- ---------
      7369 17-DEC-80
      7499 20-FEB-81
      7521 22-FEB-81
      7566 02-APR-81
      7654 28-SEP-81
      7698 01-MAY-81
      7782 09-JUN-81
      7788 19-APR-87
      7839 17-NOV-81
      7844 08-SEP-81
      7876 23-MAY-87
      7900 03-DEC-81
      7902 03-DEC-81
      7934 23-JAN-82
14 rows selected.

SQL>
  select count(*), extract (year from hiredate) year_hired from emp
 group by  extract (year from hiredate);

  COUNT(*) YEAR_HIRED
---------- ----------
         1       1982
         2       1987
         1       1980
        10       1981

11g Standby Database creation without a backup

11g New Feature – Standby Database creation from active database without having to take a backup ….

Note the command output – APEXDG is created by copying the datafiles directly from an open database APEX without having to restore the backup from tape

RMAN> run {

allocate channel c1 type disk;

allocate auxiliary channel c2 type disk;

duplicate target database for standby from active database

dorecover

nofilenamecheck;

}

10g optimized incremental backups to disk

 By using a combination of Oracle 10g Block Change Tracking and RMAN Optimised Incremental Backups we can significantly reduce backup durations and more importantly restore and recover time.

The optimised incremental backup will ensure that subsequent incremental backups after a full backup only contain blocks changed since the last backup and will also apply archive logs to the backup on disk so that the backupset on disk can be used to recover the database without applying any archivelog files.

Also, since the backup on disk is actually the image of the database with all recent changes applied to it, we can issue a SWITCH DATABASE TO COPY command in the event of a database crash and bring up the database directly from the backup on disk in a few minutes without having to restore any file from tape.

Note the first run of the RMAN backup command and compare the same with the second run …….. the backup of the database includes an internal recovery operation as well.

 RMAN> run {

allocate channel c1 device type disk format ‘/u02/oradata/backup/bkp.%u’;

recover copy of database with tag ‘LEVEL0_BKP’;

backup incremental level 1 for recover of copy with tag ‘LEVEL0_BKP’ database;

}

  using target database control file instead of recovery catalog

 

allocated channel: c1

channel c1: sid=147 devtype=DISK

Starting recover at 11-MAR-09

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

no copy of datafile 5 found to recover

no copy of datafile 6 found to recover

no copy of datafile 8 found to recover

Finished recover at 11-MAR-09

Starting backup at 11-MAR-09

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u02/oradata/rcatd/system01.dbf

input datafile fno=00003 name=/u02/oradata/rcatd/sysaux01.dbf

input datafile fno=00006 name=/u02/oradata/rcatd/rman10d01.dbf

input datafile fno=00002 name=/u02/oradata/rcatd/undotbs01.dbf

input datafile fno=00004 name=/u02/oradata/rcatd/users01.dbf

input datafile fno=00005 name=/u02/oradata/rcatd/rman9d01.dbf

input datafile fno=00008 name=/u02/oradata/rcatd/patrol01.dbf

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.09k9l940 tag=TAG20090311T124136 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0ak9l941 tag=TAG20090311T124136 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:02

Finished backup at 11-MAR-09

released channel: c1

 RMAN> run {

allocate channel c1 device type disk format ‘/u02/oradata/backup/bkp.%u’;

recover copy of database with tag ‘LEVEL0_BKP’;

backup incremental level 1 for recover of copy with tag ‘LEVEL0_BKP’ database;

}

  using target database control file instead of recovery catalog

 

channel c1: sid=141 devtype=DISK

Starting recover at 11-MAR-09

channel c1: starting incremental datafile backupset restore

channel c1: specifying datafile copies to recover

recovering datafile copy fno=00001 name=/u02/oradata/backup/bkp.01k9l8o8

recovering datafile copy fno=00002 name=/u02/oradata/backup/bkp.04k9l8s7

recovering datafile copy fno=00003 name=/u02/oradata/backup/bkp.02k9l8q0

recovering datafile copy fno=00004 name=/u02/oradata/backup/bkp.05k9l8sm

recovering datafile copy fno=00005 name=/u02/oradata/backup/bkp.06k9l8st

recovering datafile copy fno=00006 name=/u02/oradata/backup/bkp.03k9l8re

recovering datafile copy fno=00008 name=/u02/oradata/backup/bkp.07k9l8t1

channel c1: reading from backup piece /u02/oradata/backup/bkp.09k9l940

channel c1: restored backup piece 1

piece handle=/u02/oradata/backup/bkp.09k9l940 tag=TAG20090311T124136

channel c1: restore complete, elapsed time: 00:00:02

Finished recover at 11-MAR-09

Starting backup at 11-MAR-09

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u02/oradata/rcatd/system01.dbf

input datafile fno=00003 name=/u02/oradata/rcatd/sysaux01.dbf

input datafile fno=00006 name=/u02/oradata/rcatd/rman10d01.dbf

input datafile fno=00002 name=/u02/oradata/rcatd/undotbs01.dbf

input datafile fno=00004 name=/u02/oradata/rcatd/users01.dbf

input datafile fno=00005 name=/u02/oradata/rcatd/rman9d01.dbf

input datafile fno=00008 name=/u02/oradata/rcatd/patrol01.dbf

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0bk9lak7 tag=TAG20090311T130719 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:07

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0ck9lake tag=TAG20090311T130719 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 11-MAR-09

released channel: c1

LOB’s – some Performance Tuning considerations

• If LOB data is less than 4000 bytes, then it can be stored in line or on the same database page as the rest of the row data. LOB data can be stored in line only when the block size is large enough to accommodate it.
• LOB data that is stored out of line, on database pages that are separate from the row data, is accessed (read and written) by Oracle in CHUNK size pieces where CHUNK is specified in the LOB storage clause (see Section 11.2 for more information about the CHUNK option). CHUNK must be an integer multiple of DB_BLOCK_SIZE and defaults to DB_BLOCK_SIZE if not specified. Generally, it is more efficient for Oracle to access LOB data in large chunks, up to 32 KB. However, when LOB data is updated, it may be versioned (for read consistency) and logged both to the rollback segments and the redo log in CHUNK size pieces. If updates to LOB data are frequent then it may be more efficient space wise to manipulate smaller chunks of LOB data, especially when the granularity of the update is much less than 32 KB.

 

• If the same BLOB is to be accessed frequently, set the table LOB CACHE parameter to TRUE

 

• Use the CACHE option on interMedia column objects containing BLOBs if the same BLOB data is to be accessed frequently. The CACHE option puts the data into the database buffer and makes it accessible for subsequent read operations. If you specify CACHE, then LOGGING is used; you cannot have CACHE and NOLOGGING.

 

• Use the NOCACHE option (the default) if BLOB data is to be read only once or infrequently, or if you have too much BLOB data to cache, or if you are reading lots of images but none more frequently than others.

 

• Set the CHUNK option to the number of blocks of interMedia column objects containing BLOB data that are to be accessed at one time. That is, the number of blocks that are to be read or written using the object.readFromSource or object.writeToSource interMedia audio and video object methods or call, OCILobRead( ), OCILobWrite( ), DBMS_LOB.READ( ), or DBMS_LOB.WRITE( ) during one access of the BLOB value. Note that the default value for the CHUNK option is 1 Oracle block and does not vary across systems. If only 1 block of BLOB data is accessed at a time, set the CHUNK option to the size of 1 block. For example, if the database block size is 2K, then set the CHUNK option to 2K.

 

• Set the CHUNK option to the next largest integer multiple of database block size that is slightly larger than the audio, image, or video data size being inserted. Specifying a slightly larger CHUNK option allows for some variation in the actual sizes of the multimedia data and ensures that the benefit is realized. For large-sized media data, a general rule is to set the CHUNK option as large as possible. The maximum is 32K in Oracle9i. For example, if the database block size is 2K or 4K or 8K and the image data is mostly 21K in size, set the CHUNK option to 24K.

 

• If you explicitly specify the storage characteristics for the interMedia column object containing a BLOB, make sure that the INITIAL and NEXT parameters for the BLOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK value of 8K, make sure that the INITIAL and NEXT parameters are at least 8K, preferably higher (for example, at least 16K).

 

• If the interMedia column object containing a BLOB is small (that is, less than 4000 bytes), then storing the BLOB data out of line will decrease performance. However, storing the BLOB in the row increases the size of the row. This has a detrimental impact on performance if you are doing a lot of base table processing, such as full table scans, multiple row accesses (range scans), or doing many UPDATE or SELECT statements to columns other than the interMedia column objects containing BLOBs. If you do not expect the BLOB data to be less than 4000 bytes, that is, if all BLOBs are big, then the default is the best choice because:

               The LOB data is automatically moved out of line once it gets bigger than 4000 bytes.
               Performance can be better if the BLOB data is small (less than 4000 bytes including control information) and     is stored inline because the LOB locator and the BLOB data can be retrieved in the same buffer, thus reducing I/O     operations.

Query the RMAN catalog to check backup status

This script will query the RMAN catalog and report on the backup status of every database in the catalog.

 

set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
 

Some top causes for unplanned outages

I have quickly thought about some of the events which had caused unplanned downtime at my past organisation. Idea is to share the same and ensure our current monitoring infrastructure is taking into account aspects discussed below – better to learn from other’s experiences than to experience it yourself.

 

• Listener log file grew > 2GB in a Red Hat environment. This prevented user sessions from connecting even though the listener was running. Not sure if there is an OS file limit in AIX, but if logging is enabled, a good practice will be to truncate the log on a regular basis.

• Controlfile was multiplexed over three mount points and one of the mount points got 100% full. Error thrown by the application as well as alert log is ORA-0600 with a lot of arguments. Will not specifically indicate a disk space issue when looked up on Metalink.

• Temporary Tablespaces created using large size for the tempfiles. At creation time, Oracle will not check if sufficient disk space is available or not. Problem will manifest itself when some operation like a sort or index build will start using temporary tablespace disk space and disk space is not adequate.

• MAXEXTENTS value reached for tables and indexes. Application outage occurred when new tables and indexes were created and developer hard coded a very small value for MAXEXTENTS instead of leaving it to the default value which in UNLIMITED.

• Disk holding archive log suddenly got filled due to a spurt in redo activity caused by a month end or year end batch job. Good practice is to have an automated RMAN job which will backup and delete archive log files if a certain disk space usage threshold is crossed.

• Mount point holding Oracle software binaries got full. This will prevent any SQL*PLUS connections directly to the host machine. Since the trace file and core dump directories are usually on the same mount point as the ORACLE_HOME, we should ensure that effective daily housekeeping jobs are in place to not only clean up old trace files but also to rename the alert log on a daily basis so that the alert log only has entries for one day. The alert log is normally visited when a problem occurs and having the alert log have entries for the past 6 months or take a long time for the file to open using vi because it has grown very large is not a good practice.

• Procedures or Packages got INVALID because some DDL activity was done on another object with dependencies. A good practice is to ensure that no INVALID objects exist in the database and to run a job which polls the database for any objects that have got invalidated. This will also serve from an auditing viewpoint as it may identify if anyone is doing an unauthorised modifications to the database.

• Data Guard log shipping terminated because of some activity on the Primary database like password change for SYS, datafile added or resized on the Primary database, but the corresponding mount point on the standby machine did not have adequate space to accommodate the resize. Best practice is to ensure that the disk space allocation and mount point mapping is the same on both Primary as well as Standby machines.

• Public database links are used by the application and the password of the user account used in the database link was changed as part of a monthly password change. Best practice is to never change the password for the user account that is used to connect via the database link.

• Limit for parameters SESSIONS or PROCESSES which are defined in the init,ora was crossed. The only way to fix this is to kill sessions at the OS level or change the parameters and restart the database. A good practice would be to have an alerting mechanism which warns you if the threshold set is being reached. That way we can be proactive and kill any idle or inactive sessions so that this upper limit is not reached.

• Planned outage for a data movement exercise was extended because Import of data failed because of lack of tablespace free space in target database.Consequently the whole exercise had to be repeated. Best practice is to use the RESUMABLE parameter while doing such operations so that they can be resumed from the point of failure and also to write a Database Event Trigger which can alert the DBA in case such an event happens.

10g Wait Event Classes and Histograms

Oracle 10g has simplified one important aspect of tuning which is the wait event
interface by broadly classifying wait events into 8-10 major classes
 
This will give us a good overview of which area is contributing the most to a performance
problem and enable us to concentrate our efforts in those areas.
 
While the same information is very well displayed in Oracle 10g EM, it is a good idea to
know the SQL being executed in the background.
 
 
SQL> col wait_class format a30
SQL> select  wait_class,
  2     sum(total_waits), sum(time_waited)
  3  from v$session_wait_class
  4  where wait_class !='Idle'
  5  group by wait_class
  6  order by 3;
 
WAIT_CLASS                     SUM(TOTAL_WAITS) SUM(TIME_WAITED)
------------------------------ ---------------- ----------------
Network                                     199                0
Application                                  32                6
Commit                                      239               70
Configuration                               209              271
Other                                      1120             1035
User I/O                                   8185             5201
Concurrency                                 646             5865
System I/O                               422189           416403
 
8 rows selected.
 
 
To find out which individual events are contributing to the wait class "System I/O" we
can drill down using the next query
 
SQL> col event format a30
SQL> select event, total_waits, time_waited
  2  from v$system_event e, v$event_name n
  3  where n.event_id = e.event_id
  4  and n.wait_class = (select wait_class from v$session_wait_class
  5   where wait_class !='Idle'
  6   group by wait_class having
  7  sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
  8  where wait_class !='Idle'
  9  group by (wait_class)))
 10  order by 3;
 
EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
log file single write                   24          95
Log archive I/O                        458         109
control file sequential read        239678        2503
log file sequential read               424        6677
db file parallel write               79129       86110
log file parallel write              44111       93207
control file parallel write         142355      228264
 
7 rows selected.
 
 
So now we see that the wait event accounting for most I/O waits is the event
related to writing to the control file which is 'control file parallel write'
 
We can go one step further and see the breakdown of the time that has been spent
by sessions waiting on this event by using Histograms.
 
These histograms will organise the wait duration into buckets and will tell us
things like if the wait duration happened often, but for a very short duration,
or happened occasionally, but when it happened the wait time was of a longer
duration.
 
The indicator of a problem is when high wait_count numbers are falling into buckets
located at the far end of the histogram - in this case we see the highest wait_counts
are concentrated at the top end of the histogram.
 
 
SQL> select wait_time_milli bucket, wait_count
  2   from v$event_histogram
  3   where event =
  4   'control file parallel write';
 
    BUCKET WAIT_COUNT
---------- ----------
         1          0
         2      10097
         4      37621
         8      34497
        16      28743
        32      17543
        64       8000
       128       4065
       256       1528
       512        548
      1024         35
      2048          3
      4096          2
      8192          1
 
 

How to manually submit a 10g SQL Tuning Advisor task

Purpose:

Use the following procedure to generate the 10g SQL Tuning Advisor reports and recommendations from the command line when  Database Control or Grid Control GUI  is not available.
CREATE A SQL TUNING TASK

 DECLARE
  my_task_name   VARCHAR2 (30);
  my_sqltext     CLOB;
 BEGIN
  my_sqltext :=  ‘INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,
            ASSIGNMENT_NUMBER, LOCATION_CODE, PAYROLL_ACTION_ID,PAY_DATE) (SELECT
            PAA.ASSIGNMENT_ID, PAA.FULL_NAME, PAA.ASSIGNMENT_NUMBER,
            PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_DATE FROM
             PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE PPS WHERE
            PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID = PPS.PERSON_ID AND
            PPS.ACTUAL_TERMINATION_DATE IS NULL)’;  >>>>> SQL TO BE TUNED GOES HERE
   my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
      bind_list     => sql_binds (anydata.convertnumber (9)),
          user_name     => ‘BWINT’,
         scope         => ‘COMPREHENSIVE’,
         time_limit    => 60,
          task_name     => ‘CHECK_PAYROLL_RUN’,
         description   => ‘Testing Slow Running Online Payslip Run’
      );
   END;
  /

PL/SQL procedure successfully completed.

EXECUTE THE TUNING TASK

 BEGIN
dbms_sqltune.execute_tuning_task (task_name =>   2  ‘CHECK_PAYROLL_RUN’);
 end;
 /

PL/SQL procedure successfully completed.

VERIFY THE STATUS OF THE TASK

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name =’CHECK_PAYROLL_RUN’;

STATUS
———–
COMPLETED

RUN THE SQL TUNING ADVISOR REPORT

SQL> set pagesize 120
SQL> set pagesize 5000
SQL> set linesize 120
SQL> set long 50000
SQL> set longchunksize 500000

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’) FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’)                           
—————————————————————————————————-
GENERAL INFORMATION SECTION                                                    
——————————————————————————-
Tuning Task Name                  : CHECK_PAYROLL_RUN                          
Tuning Task Owner                 : SYS                                        
Scope                             : COMPREHENSIVE                              
Time Limit(seconds)               : 60                                         
Completion Status                 : COMPLETED                                  
Started at                        : 05/09/2008 10:34:44                        
Completed at                      : 05/09/2008 10:35:54                        
Number of Index Findings          : 1                                          
Number of SQL Restructure Findings: 1                                          
Number of Errors                  : 1                                          
                                                                               
——————————————————————————-
Schema Name: BWINT                                                             
SQL ID     : 35mw20gycm0q5                                                     
SQL Text   : INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,  
                      ASSIGNMENT_NUMBER, LOCATION_CODE,                        
             PAYROLL_ACTION_ID,PAY_DATE) (SELECT                               
                      PAA.ASSIGNMENT_ID, PAA.FULL_NAME,                        
             PAA.ASSIGNMENT_NUMBER,                                            
                      PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_ 
             DATE FROM                                                         
                      PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE    
             PPS WHERE                                                         
                      PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID =          
             PPS.PERSON_ID AND                                                 
                      PPS.ACTUAL_TERMINATION_DATE IS NULL)                     
                                                                               
——————————————————————————-
FINDINGS SECTION (2 findings)                                                  
——————————————————————————-
                                                                               
1- Index Finding (see explain plans section below)                             
————————————————–                             
  The execution plan of this statement can be improved by creating one or more 
  indices.                                                                     
                                                                               
  Recommendation (estimated benefit: 100%)                                     
  —————————————-                                     
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0001 on HR.PER_ALL_ASSIGNMENTS_F(‘ASSIGNMENT_ID’)
    ;                                                                          
                                                                               
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0002 on HR.PER_ALL_PEOPLE_F(‘PERSON_ID’);        
                                                                               
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0003 on HR.PAY_PAYROLL_ACTIONS(‘DATE_EARNED’,'PAY
    ROLL_ID’);                                                                 
                                                                               
  Rationale                                                                    
  ———                                                                    
    Creating the recommended indices significantly improves the execution plan 
    of this statement. However, it might be preferable to run “Access Advisor” 
    using a representative SQL workload as opposed to a single statement. This 
    will allow to get comprehensive index recommendations which takes into     
    account index maintenance overhead and additional space consumption.       
                                                                               
2- Restructure SQL finding (see plan 1 in explain plans section)               
—————————————————————-               
  An expensive cartesian product operation was found at line ID 17 of the      
  execution plan.                                                              
                                                                               
  Recommendation                                                               
  ————–                                                               
  – Consider removing the disconnected table or view from this statement or    
    add a join condition which refers to it.                                   
                                                                               
  Rationale                                                                    
  ———                                                                    
    A cartesian product should be avoided whenever possible because it is an   
    expensive operation and might produce a large amount of data.

11g Passwords can be case sensitive

In 11g – Passwords are case sensitive but this can be controlled by an init.ora parameter -

SQL> show parameter SEC_CASE_SENSITIVE_LOGON ;

NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE

SQL> create user arjun identified by ARJUN; – Password in Upper Case.

User created.

SQL> grant create session to arjun;

Grant succeeded.

SQL> conn arjun/arjun; – Tried to connect with lower case password – FAILS.
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> exit
apex:/u01/oracle> sqlplus “sys as sysdba”

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn arjun/ARJUN – CONNECTS –
Connected.
SQL>exit

Sqlplus “sys as sysdba”

 drop user arjun cascade;

Now alter the parameter to FALSE – so case sensitive passwords are not required.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> create user arjun identified by ARJUN;

User created.

SQL> grant create session to arjun;

Grant succeeded.

SQL> conn arjun/arjun; – Connects with lower case password although password at time of creation was in UPPER case.

11g ACTIVE STANDBY

Active Standby :

PRIMARY DB- APEX

STANDBY DB- APEXDG

Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:

1. Stop the managed recovery process on STANDBY database :
In APEXDG ( Standby database )

SQL > alter database recover managed standby database cancel;
Database altered.

2. Open the APEXDG – standby database as read-only:
SQL > alter database open read only;
Database altered.

3. Restart the managed recovery process ON STANDBY DATABASE :

SQL > alter database recover managed standby database using current logfile disconnect;

Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.

4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:

In PRIMARY database –
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;
no rows selected

On STANDBY
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;
no rows selected

In APEX database – PRIMARY
SQL > create table arjun (col1 number);

5. After a few seconds, check the existence of the table in the standby database:
SQL > select table_name from dba_tables where table_name = ‘ARJUN’;

TABLE_NAME
——————————
ARJUN

The table will be present !!

The standby database is open in read-only mode, but it is still applying the logs from the primary database.
This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.

6. To confirm the application of redo logs on the primary database, first switch the log file:
alter system switch logfile;

7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:

$ adrci
show alert -tail –f

kcrrvslf: active RFS archival for log 6 thread 1 sequence 15694
RFS[7]: Successfully opened standby log 5: ‘/u02/oradata/apexdg/standby02.log’
Media Recovery Log /u02/oradata/apexdg/arch/APEXDG/archivelog/2008_12_05/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15695 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u02/oradata/apexdg/standby02.log

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database.
With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database.
Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably.
And because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile.