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.
