05 Sep RMAN : How to verify I have a consistent backup
The other day I was asked How can I verify that the backup I took was a good backup. For this RMAN has several commands to verify the integrity of these.
All the commands used below do not perform an actual recovery , it only reads and validates the backups , what I do have to say though is that is the word VALIDATE is missing in some commands and depending on which state you have your DB , it will actually start to do the restore process, so be very careful that you include that word.
Here are the commands I used for this exercise, also note that it doesn’t matter if the channel you are allocating is to DISK or to SBT, in this case I used DISK
- RESTORE DATABASE PREVIEW ;
- RESTORE DATABASE VALIDATE;
- RESTORE ARCHIVELOG FROM sequence xx UNTIL SEQUENCE yy THREAD nn VALIDATE;
- RESTORE CONTROLFILE VALIDATE;
- RESTORE SPFILE VALIDATE;
The first thing that you have to do is, if you are not going to use the latest backup, define the time that you want to validate your backup, the first command that we are going to use is RESTORE . . . PREVIEW, this command identifies the needed backup(s) to execute the restore process as well as the Archived Redo Logs needed.
RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> RESTORE DATABASE PREVIEW ;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:54:18
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6 Full 75.34M DISK 00:00:19 03-SEP-2012 22:01:04
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TESTDB_HOT_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/system01.dbf
2 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/sysaux01.dbf
3 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs1_01.dbf
4 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs2_01.dbf
5 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/users_01.dbf
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9 482.00K DISK 00:00:00 03-SEP-2012 22:01:39
BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 8 222449 03-SEP-2012 21:57:53 224442 03-SEP-2012 22:01:33
2 4 222452 03-SEP-2012 21:59:38 224448 03-SEP-2012 22:03:17
1 9 224442 03-SEP-2012 22:01:33 224456 03-SEP-2012 22:01:36
2 5 224448 03-SEP-2012 22:03:17 224459 03-SEP-2012 22:03:21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
12 389.50K DISK 00:00:01 03-SEP-2012 22:21:50
BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 10 224456 03-SEP-2012 22:01:36 225574 03-SEP-2012 22:21:01
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
11 25.00K DISK 00:00:00 03-SEP-2012 22:21:50
BP Key: 11 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
2 6 224459 03-SEP-2012 22:03:21 225577 03-SEP-2012 22:22:45
Media recovery start SCN is 224235
Recovery must be done beyond SCN 224235 to clear datafile fuzziness
Finished restore at 03-SEP-2012 22:55:15
released channel: ch1
The next step, which is RESTORE DATABASE VALIDATE, it will read the pieces of the backup and if it finds an error it will report it.
RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> restore database validate;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:51:44
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445 tag=TESTDB_HOT_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:25
Finished restore at 03-SEP-2012 22:52:38
released channel: ch1
If you looked closely at the result above, this only read the backupset which contain the datafiles, not the Archived Redo Logs, so now we have to verify the integrity of these, as these will also be needed if the backup was an inconsistent backup (Hot Backup). What I recommend is that you get the value of the needed Archived Redo Logs from result of the RESTORE…PREVIEW, once you have these values, run the command RESTORE ARCHIVELOG. . . VALIDATE
RMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> restore archivelog from sequence 8 until sequence 10 thread 1 validate;
5> restore archivelog from sequence 4 until sequence 6 thread 2 validate;
6> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 23:15:11
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:15
Starting restore at 03-SEP-2012 23:15:17
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:21
released channel: ch1
To end this , we just need to validate that we can restore the control file and our binary parameter file
RMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
5> restore controlfile validate;
6> restore spfile validate;
7> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
executing command: SET until clause
Starting restore at 03-SEP-2012 23:23:14
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03 tag=TAG20120903T220143
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:15
Starting restore at 03-SEP-2012 23:23:16
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04 tag=TAG20120903T222152
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:18
released channel: ch1
Conclusion
These commands even though they are simple, they are very powerful to validate your backups, mi recommendation is that you run this at least once a week , as you don’t want to be in a situation in which you need to perform a restore and in that moment find out that you don’t have a valid backup or a valid backup strategy.
Sorry, the comment form is closed at this time.