RMAN Incremental Backup SCN's

For all the archived logs, RMAN/Oracle manage the start and end SCN numbers. By having a quick view of these numbers, one can easily find out if anything is missing there which can jeopardize the recovery process.

Unfortunately the same doesn't apply to database backupsets. When we take RMAN incremental backup of database, it also does has the start and end SCN associated with it but that information is not easily visible to user for a quick look. When we take RMAN L1 backup, it actually backup all the blocks with SCN number greater than the end SCN of last L1 or L0 (depending of differential vs cumulative backups). This information is not directly available to users, in other words, one can't just have a look on the backupsets and determine if the continuity has been maintained or if something is missing/deleted in between available L1 backups.

Here is the output of RMAN archive log backup showing the START (Low) and END (Next) SCN of each available archive log:

 RMAN> list backup of archivelog all;  
 using target database control file instead of recovery catalog  
 List of Backup Sets  
 ===================  
 BS Key Size Device Type Elapsed Time Completion Time  
 ------- ---------- ----------- ------------ -------------------  
 690 9.17M DISK 00:00:06 02/23/2015 09:03:33  
 BP Key: 18910 Status: AVAILABLE Compressed: YES Tag: TAG20150223T081600  
 Piece Name: /media/rmanbackup/backup0gpvvu6v_1_1  
 List of Archived Logs in backup set 18902  
 Thrd Seq Low SCN Low Time Next SCN Next Time  
 ---- ------- ---------- ------------------- ---------- ---------  
 1 62083 362640841 02/23/2015 05:00:00 362643077 02/23/2015 06:00:00  
 1 62084 362643077 02/23/2015 06:00:00 362646450 02/23/2015 07:00:00  
 1 62085 362646450 02/23/2015 07:00:00 362651834 02/23/2015 08:00:03  
 1 62086 362651834 02/23/2015 08:00:03 362654121 02/23/2015 08:15:58  

And here is the L1 backup of database:

 RMAN> list backup of database;  
 List of Backup Sets  
 ===================  
 BS Key Type LV Size    Device Type Elapsed Time Completion Time  
 ------- ---- -- ---------- ----------- ------------ -------------------  
 694  Incr 1 12.4G   DISK    00:20:20   02/23/2015 08:23:32  
 BP Key: 18912  Status: AVAILABLE Compressed: YES Tag: TAG20150223T091530  
 Piece Name: /u02/PROD/backupset/2015_02_23/o1_mf_nnnd1_tgph2lw_.bkp  
 List of Datafiles in backup set 18904  
 File LV Type <b>Ckp SCN</b>  <b>Ckp Time</b>      Name  
 ---- -- ---- ---------- ------------------- ----  
 1  1 Incr 300407179 02/23/2015 08:43:13 +ORADATA/prod/system_01.dbf  
 2  1 Incr 300407179 02/23/2015 08:43:13 +ORADATA/prod/undo_01.dbf  
 3  1 Incr 300407179 02/23/2015 08:43:13 +ORADATA/prod/sysaux_01.dbf  
 4  1 Incr 300407179 02/23/2015 08:43:13 +ORADATA/prod/prod_data_01.dbf  
 5  1 Incr 300407179 02/23/2015 08:43:13 +ORADATA/prod/prod_index_01.dbf  

We do have Checkpoint SCN which is kind of start SCN of the backupset.

Oracle does store the start and end SCN of database backupsets as well and we can see that while doing the recovery in debug mode. Here are the steps to see that:

1) Before invoking rman, set the following environment variable to have the date in certain format:
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

2) Invoke RMAN and set the debug ON

 $rman trace=recovery.trc log=recovery.log  
 RMAN> connect target /  
 RMAN> set echo on  
 RMAN> debug on  
 RMAN> recover database;  
 RMAN> debug off  

3) Now open the recovery.trc file and find the string "bsKey=", there should be a section like this in debug file:

 DBGRCVMAN: DUMPING RECOVERY CONTAINER  
 DBGRCVMAN: Incremental Backup Set  
 DBGRCVMAN:  <b>bsKey=694</b> bsRecid=694 bsStamp=868787809 setStamp=868586419 setCount=2283 site_key=0  
 DBGRCVMAN:  bsLevel=1 bsType=I pieceCount=1  
 DBGRCVMAN:  multi_section=N  
 DBGRCVMAN:  key=82 recid=82 stamp=868787809  
 DBGRCVMAN:  compTime=23-FEB-15  
 DBGRCVMAN:  blocks=64000 blockSize=8192  
 DBGRCVMAN:  <b>fromSCN=300407179</b> <b>toSCN=300409375</b> toTime=23-FEB-15 level=1 section_size=0  
 DBGRCVMAN:  rlgSCN=1 rlgTime=22-MAY-13 dbincKey=  
 DBGRCVMAN:  afzSCN=0  
 DBGRCVMAN:  dfNumber=1 creationSCN=10 pluginSCN=0 foreignDbid=0 pluggedRonly=0  
 DBGRCVMAN:  cfType=B  
 DBGRCVMAN:  keep_options=0 keep_until=NULL  
 DBGRCVMAN:    fetchCursor1RecoveryAction real filter rejected - trying next  
 DBGRCVMAN:    fetchCursor1RecoveryAction seekNext  
 DBGRCVMAN:    fetchCursor1RecoveryAction rcvRecCursor1_c record  

Here "bsKey=694" is the backupset key and "fromSCN" & "toSCN" fields shows the START & END SCN contained in that backupset.

So far, this information is only available while doing actual recovery with debug mode. I wish if Oracle can somehow give this information directly in the "LIST BACKUP" command for ease of use.

I know there are many other ways to check the validity of the backups but this information would also help a lot and take less time to check the continuity of the backsets.



Thanks
Daljit Singh