Oracle – Using v$ or rc Views for RMAN Restore Database Preview

disaster recoveryoracleoracle-11g-r2rman

Setup:

We take an Incremental level 0 backup weekly using:

RMAN> backup as compressed backupset incremental level 0 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

We take an Incremental level 1 backup nightly (except on the night that we take the incremental level 0 backup) using:

RMAN> backup as compressed backupset incremental level 1 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

In RMAN, I know that I can report on when recovery will start and which SCN will need to be restored in order to clear the datafile fuzziness to bring the database to a consistent state using the following:

RMAN> restore database preview;

Media recovery start SCN is 49928711194
Recovery must be done beyond SCN 49928711196 to clear datafile fuzziness

This tells me that if I issue a restore database; i'll need to recover SCN's 49928711194 to 49928711196 before I can open the database.

From here, I know that I can query which archivelog backups are available and which SCNs these archivelog backups contain using: 

SELECT sequence#, first_change#, next_change#, id2, handle from (
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711194 between first_change# and next_change#
UNION
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711196 between first_change# and next_change#) archivelog
  JOIN v$backup_piece p
    ON archivelog.id2=p.set_count;

SEQUENCE#   FIRST_CHANGE#   NEXT_CHANGE#    ID2     HANDLE
22030       49923438913     49928711521     7940    L:\ORACLE\BACKUPS\ARCH_2_7940_1

My question is: is there a way to query a v$ view or rc view to obtain the start SCN and beyond SCN that RMAN> restore database preview; provides in order to clear the datafile fuzziness for a given backupset?

Best Answer

V$BACKUP_DATAFILE

V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.

Media recovery start SCN is 7962109213
Recovery must be done beyond SCN 7962156379 to clear datafile fuzziness 
Finished restore at 28-JUN-2017 09:27:17

And what I am interested in is ABSOLUTE_FUZZY_CHANGE# and CHECKPOINT_CHANGE# column of that view.

CHECKPOINT_CHANGE#: All changes up to the checkpoint change number are included in this backup

ABSOLUTE_FUZZY_CHANGE#: Highest change number in this backup

I used the following query to get the same result as given by RMAN restore preview command.

SQL>   select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
                        (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
                        where  trunc(completion_time) = to_date('JUN-28-2017','MON-DD-YYYY')
                        and file# <> 0
                        order by completion_time desc
                        ); 

     FUZZ#    CHKPNT#
---------- ----------
7962156379 7962109213
Related Question