Introduction:
This is a short article on RMAN where we can check if our backups are really intact and can help us in critical situation when we have to restore and recover the database.
We have a command option called PREVIEW which we can use with RESTORE DATABASE. This option does not actually restore the datafiles from backup but it just tell us SCN number until which we should be recovering our database. It also tells us SCN number until which it can restore.
Lets take a simple example where I have a complete database backup taken for my database including archivelogs.
I will run RESTORE DATABASE PREVIEW command to check if I can get back my database in case of any issues. Then I will wipe out my database and try to recover from the backup to validate if PREVIEW option was showing correct information.
Validating Backupsets
We can validate if our backupsets are intact and does not have any corruption. We can check both physical and logical corruption for the backupsets and make sure they are good and can be used for restore.
Following RMAN command will confirm the same
Physical corruption validation
RMAN> backup validate database archivelog all; Starting backup at 08-FEB-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=272 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=13 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set ... ... ...
Logical corruption validation
RMAN> backup validate check logical database archivelog all; Starting backup at 08-FEB-16 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/datafiles/users02.dbf ... ... ...
If above command doesn’t report any issues, we can be sure that backupsets are intact and can be used for restore/recovery.
Checking PREVIEW
We can use “RESTORE DATABASE PREVIEW” command in RMAN to get preview of all backup sets we are going to use and until what SCN this backup will be able to restore.
This command also gives us the SCN number until which we need to recovery the DB to get all datafiles out of fuzzy status
RMAN> restore database preview; Starting restore at 08-FEB-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=58 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=264 device type=DISK List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 48 Full 209.01M DISK 00:00:43 08-FEB-16 BP Key: 48 Status: AVAILABLE Compressed: YES Tag: TAG20160208T053652 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvy4y2_.bkp List of Datafiles in backup set 48 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/system01.dbf 4 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/undotbs01.dbf 8 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users02.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 47 Full 152.79M DISK 00:00:41 08-FEB-16 BP Key: 47 Status: AVAILABLE Compressed: YES Tag: TAG20160208T053652 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvy4yx_.bkp List of Datafiles in backup set 47 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 3 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/sysaux01.dbf 6 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users01.dbf 9 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users03.dbf ... ... ... List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 54 3.97M DISK 00:00:01 08-FEB-16 BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20160208T055120 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws8m9_.bkp List of Archived Logs in backup set 54 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 43 2955401 08-FEB-16 2956061 08-FEB-16 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 55 80.50K DISK 00:00:00 08-FEB-16 BP Key: 55 Status: AVAILABLE Compressed: YES Tag: TAG20160208T055120 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws9xm_.bkp List of Archived Logs in backup set 55 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 44 2956061 08-FEB-16 2956142 08-FEB-16 1 45 2956142 08-FEB-16 2956308 08-FEB-16 RMAN-05119: recovery can not be done to a consistent state. Media recovery start SCN is 2955419 Recovery must be done beyond SCN 2955603 to clear datafile fuzziness Finished restore at 08-FEB-16
If you check the last 4 lines, it will show the SCN number until which RMAN can restore the backups – SCN 2955419
From SCN 2955419 we need to start applying archive logs. We can identify the sequence number in which this SCN falls using below SQL
SQL> select sequence#, first_change#, next_change# from v$archived_log where FIRST_CHANGE# <= 2955419 and NEXT_CHANGE# >= 2955419; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ------------- ------------ 43 2955401 2956061
So we will need archives from sequence# 43 and we need to recover beyond SCN 2955603.
Trying restore/recover
Lets see I have few/all datafiles missing for database. I tried to bounce the DB and got following error
SQL> startup ORACLE instance started. Total System Global Area 943718400 bytes Fixed Size 2931136 bytes Variable Size 348128832 bytes Database Buffers 587202560 bytes Redo Buffers 5455872 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/datafiles/system01.dbf'
Lets try to restore
[oracle@advait ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 8 06:56:26 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1429382412, not open) RMAN> restore database; Starting restore at 08-FEB-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=248 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=13 device type=DISK skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf skipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/datafiles/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/datafiles/users01.dbf channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/datafiles/users03.dbf ... ... ... channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:01:01 channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/2B0D04DCEFAF55F5E0531438A8C0EDC4/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvzlmt_.bkp tag=TAG20160208T053652 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 08-FEB-16 RMAN>
Once restore complete, lets try to recover
SQL> recover database;
ORA-00279: change 2955419 generated at 02/08/2016 05:36:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_%u_.arc
ORA-00280: change 2955419 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
As expected, its asking for recovery starting from sequence# 43.
We can restore the required archivelogs as well
RMAN> restore archivelog from sequence 43; Starting restore at 08-FEB-16 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=43 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws8m9_.bkp ... ... ... channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 Finished restore at 08-FEB-16 RMAN>
Once archives are restored, we can carry on the recovery
SQL> recover database
ORA-00279: change 2955419 generated at 02/08/2016 05:36:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_cck2fwv
k_.arc
ORA-00280: change 2955419 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_cck2fwvk_.arc
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
In this case it old took sequence# 43 as NEXT_CHANGE# for sequence# 43 was 2956061 which is more than SCN 2955603 required to clear the fuzzy state. Rest of the redo information was also present in online redo logs so database did crash recovery while doing the open.
Hope this helps !!
Persistent Storage Solutions 
Your posts are always clear and fresh Advait. Really good work.