Oracle Database Blog: Experiments & Learnings

January 18, 2012

Unrecoverable SCN

Filed under: Oracle RDBMS — Tags: , , — Saurabh Manroy @ 6:11 pm

No-logging operations leave their trail in Redologs and Controlfile. In redologs, the change record is marked with special OPCODE (19.2), with a line following the OPCODE suggesting that change record is due to Direct Path Load. This can be seen by dumping the redolog file. This information in redologs is used by Oracle during media recovery to check for objects impacted by nologging operations. This is covered in depth here by Oracle Expert Riyaj.

In controlfile, information about nologging operation is updated using a field called Unrecoverable SCN. This can be easily seen from UNRECOVERABLE_CHANGE# column in v$DATAFILE which records the highest SCN from last nologging operation performed. Another option is to dump controlfile at level 3 and search tracefile for word ‘unrecoverable’.

This unrecoverable SCN field in controlfile is used by RMAN to provide output for ‘REPORT UNRECOVERABLE’ command. While this is handy,but updating small piece of information in Controlfile requires the session to take CF Enqueue and if multiple Controlfiles are configured, wait events for CF parallel write can be easily seen. Such controlfile enqueue situation gets amplified and can become cause of slowdown in a large Direct Path Load operation.

Prior to 11.2, Event 10359 is available to disable such updates to Controlfile. 11.2.0.2 formally introduced a parameter DB_UNRECOVERABLE_SCN_TRACKING to disable updating the controlfile with unrecoverable SCN from nologging operations. However 11.2.0.2 patchset suffers from bug 12360160 which makes the parameter change ineffective untill next database restart. This bug has been fixed in 11.2.0.3.

Here is a bit of proof to the theory above from 11.2.0.3:

db_unrecoverable_scn_tracking = TRUE

PROD> show parameter scn
NAME                                     TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unrecoverable_scn_tracking          boolean        TRUE

SQL> create table test_nolog tablespace test_uscn NOLOGGING as select * from DBA_SOURCE;
Table created.
-- From 10046 Trace

Event waited on                          Times   Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O                     7     0.00       0.00
direct path write                          208     0.00       0.03
direct path read                            11     0.00       0.00
direct path sync                             1    13.96      13.96
control file sequential read                 9     0.00       0.00
control file parallel write                  4     0.00       0.00

SQL> select name,file#,unrecoverable_change# from v$datafile where file#=6;
NAME                                       FILE#    UNRECOVERABLE_CHANGE#
---------------------------------------- ---------- ---------------------
/u01/app/oracle/oradata/test_uscn.dbf        6         4045395

-- Controlfile Dump

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_30579.trc

-- From Tracefile:

DATA FILE #6:
 name #1: /u01/app/oracle/oradata/test_uscn.dbf
creation size=25600 block size=8192 status=0xe head=1 tail=1 dup=1
 tablespace 11, index=7 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.003dba53 01/18/2012 16:54:31
 Checkpoint cnt:2 scn: 0x0000.003db52c 01/18/2012 16:44:03

-- Connect to RMAN

RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File  Type of Backup Required       Name
----  -----------------------     -----------------------------------
6           full or incremental    /u01/app/oracle/oradata/test_uscn.dbf

-- Now backup datafile.

RMAN> backup datafile 6;
Starting backup at 18-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/test_uscn.dbf
channel ORA_DISK_1: starting piece 1 at 18-JAN-12
channel ORA_DISK_1: finished piece 1 at 18-JAN-12
piece handle=/u01/app/oracle/oradata/backup/1kn13a8t_1_1 tag=TAG20120118T170221 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 18-JAN-12
RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
RMAN>

db_unrecoverable_scn_tracking = FALSE

SQL> alter system set db_unrecoverable_scn_tracking=false;
System altered.

-- switch on 10046 trace

SQL> create table test_noscn tablespace test_uscn NOLOGGING as select * from DBA_SOURCE;
Table created.

From 10046 trace (only relevant events shown):

Event waited on                           Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 direct path write                         208     0.00        0.03
 direct path read                           15     0.00        0.00
 direct path sync                            1    13.70       13.70
-- No Controlfile updates/Waits

SQL> select name,file#,unrecoverable_change# from v$datafile where file#=6;
NAME                                        FILE#     UNRECOVERABLE_CHANGE#
---------------------------------------- ---------- ---------------------
/u01/app/oracle/oradata/test_uscn.dbf        6            4045395 <<<-- Keeeps the old SCN

-- Lets check from RMAN:

RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
RMAN>

Advertisements

Blog at WordPress.com.