RMAN Infatuation?

Lately, I am becoming infatuated with RMAN again.

Have you ever run “restore database preview”?

Are you curious about how the recovery SCN are determined?

Media recovery start SCN is 1515046
Recovery must be done beyond SCN 1515051 to clear datafile fuzziness

If you are, then I will demonstrate this for you.

RMAN LEVEL0 backup and restore database preview summary:

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
366     B  A  A DISK        07-APR-2014 14:11:32 1       1       YES        ARCHIVELOG
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:35 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
371     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
372     B  0  A DISK        07-APR-2014 14:11:52 1       1       YES        LEVEL0
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
374     B  F  A DISK        07-APR-2014 14:11:58 1       1       NO         TAG20140407T141156

RMAN> restore database preview summary;

Starting restore at 07-APR-2014 14:13:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=107 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=108 device type=DISK

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
371     B  0  A DISK        07-APR-2014 14:11:38 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:39 1       1       YES        LEVEL0
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
Media recovery start SCN is 1524017
Recovery must be done beyond SCN 1524025 to clear datafile fuzziness
Finished restore at 07-APR-2014 14:13:05

RMAN>

Query database to determine recovery SCN:

ARROW:(SYS@db01):PRIMARY> r
  1  SELECT
  2    MIN(checkpoint_change#) start_scn,
  3    GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn
  4  FROM v$backup_datafile
  5  WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0)
  6*

 START_SCN BEYOND_SCN
---------- ----------
   1524017    1524025

ARROW:(SYS@db01):PRIMARY>

RMAN LEVEL0 and LEVEL1 backup and restore database preview summary:

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
366     B  A  A DISK        07-APR-2014 14:11:32 1       1       YES        ARCHIVELOG
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:35 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
371     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
372     B  0  A DISK        07-APR-2014 14:11:52 1       1       YES        LEVEL0
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
374     B  F  A DISK        07-APR-2014 14:11:58 1       1       NO         TAG20140407T141156
375     B  A  A DISK        07-APR-2014 14:14:37 1       1       YES        ARCHIVELOG
376     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
377     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
378     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
379     B  1  A DISK        07-APR-2014 14:14:42 1       1       YES        LEVEL1
380     B  1  A DISK        07-APR-2014 14:14:42 1       1       YES        LEVEL1
381     B  1  A DISK        07-APR-2014 14:14:45 1       1       YES        LEVEL1
382     B  A  A DISK        07-APR-2014 14:14:47 1       1       YES        ARCHIVELOG
383     B  F  A DISK        07-APR-2014 14:14:51 1       1       NO         TAG20140407T141448

RMAN> restore database preview summary;

Starting restore at 07-APR-2014 14:15:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=107 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=108 device type=DISK

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
371     B  0  A DISK        07-APR-2014 14:11:38 1       1       YES        LEVEL0
376     B  1  A DISK        07-APR-2014 14:14:39 1       1       YES        LEVEL1
370     B  0  A DISK        07-APR-2014 14:11:39 1       1       YES        LEVEL0
377     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
378     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
368     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
380     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
379     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
382     B  A  A DISK        07-APR-2014 14:14:47 1       1       YES        ARCHIVELOG
Media recovery start SCN is 1524335
Recovery must be done beyond SCN 1524339 to clear datafile fuzziness
Finished restore at 07-APR-2014 14:16:00

RMAN>

Query database to determine recovery SCN:

ARROW:(SYS@db01):PRIMARY> r
  1  SELECT
  2    MIN(checkpoint_change#) start_scn,
  3    GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn
  4  FROM v$backup_datafile
  5  WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0)
  6*

 START_SCN BEYOND_SCN
---------- ----------
   1524335    1524339

ARROW:(SYS@db01):PRIMARY>

Why is all of this important?

It allows one to automate the process to validate backup without having to actually run “restore database preview”.

Tested on 11.2.0.4 database.