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>

About these ads

3 Comments »

  1. Thanks for the explanation – I have 2 ? though

    1) If a database has the db_unrecoverable_scn_tracking=false, is it then at all possible to check for unrecoverable ops in the database?
    How will I know if anyone has made these kind of unrec.ops?

    2) If i have an unrecoverable tablespace – I then make an rman backup of it (which may take ie 1 hr) – and in that hour I get more unrec. ops – how can I be sure to make a backup I can use for restore (not missing any rows)
    apart for setting force_logging = YES of course – or making a Cold backup

    regards
    Mette

    Comment by Mette Juel — June 27, 2013 @ 6:36 pm

    • Hello Mette,

      Thanks for stopping by.

      As this blog post mentions in the starting: All unrecoverable operations will leave their trail in redo logs. So, to get to know about every unrecoverable operations, you will need to dump redo/archivelogs to know which operations were done with nologging option.
      From v$datafile.unrecoverable_time, you can know only latest time when nologging operation was done. Based on this information, you should perform an incremental backup which will backup all the blocks that have changed since last successful backup. Rolling forward a restored db copy using incremental backups would avoid errors that you will encounter instead in case you used archivelogs/redologs for recovery.

      Saurabh Manroy

      Comment by Saurabh Manroy — June 28, 2013 @ 11:31 am

  2. Hello Again

    at 1)
    If db_unrecoverable_scn_tracking=false is there a way to detect unrec. ops in v$views or will the date still be set?

    at 2) I still dont get it :-)
    If I have unrec. ops during a long runing backup (ie lvl 0) then the recover process will need the lvl 0 + archivelog files to get a full recovery not matter what point in time I choose (?) And if unrec. ops were made during backup the I would need archive logs – and then the archive logs would be incomplete – and the resulting recovery would not be correct (block corruptions) Is that correct? So is the only way to ensure a complete restoreable database (in case of lots og unrec. ops ) a Cold backup?

    Comment by Mette Juel — June 28, 2013 @ 9:27 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: