I just had a task to validate rman backup of a production database without actually having to restore it somewhere. Although i prefer to do something like clone the database from backup and see for myself if the instance will actually open and work, there is a way to do simple validation from rman. RMAN allows you to add “validate” keyword to all it’s restore commands. Validation will do exactly the same operations as it would during the restore except the actual writing of the restored backup. It will choose, locate and retrieve rman files from backup devices, read them and validate them. It won’t affect the live database. Just take up resources needed for this operation, so we should still be careful when using it especially with larger parallelism.
So, here is how to actually do the validation, and what should be validated.
1.Determine what you want to validate
open rman, and list your backup with “list backup summary” command. Pick one point in time that is good for validation. For example, pick one that will have to use tape device or one that needs to use tape (ex. for datafiles) and disk (ex. for archivelogs). I here picked only ones that test the SBT_TAPE. It has a timestamp ‘2014-08-12:9:00:00′,’yyyy-mm-dd:hh24:mi:ss’)”.
2. Preview of the operation and pinpoint the backupsets needed for validation.
RMAN offers a great preview command that does nothing except writes and output what would rman do if you would execute this command without the preview option. So let’s do it for our restore.
run {
set until time "to_date('2014-08-11:9:00:00','yyyy-mm-dd:hh24:mi:ss')";
restore database preview;
}
This will output list of backupsets containing datafiles (BS 7506) and backupsets containing archivelogs (7481, 7482, 7499). Now we proceed to validate all of those.
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7506 Full 49.40G SBT_TAPE 00:25:07 12.08.2014-05:23 BP Key: 12921 Status: AVAILABLE Compressed: NO Tag: TAG20140812T045758 Handle: MYDB_FRA<mydb_7583:855377884:1>.dbf Media: c0a86616:53a01494:5843:0010 List of Datafiles in backup set 7506 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------------- ---- 1 Full 3966019235 11.08.2014-02:00 +DATA/mydb/datafile/system.260.795609019 2 Full 3966019238 11.08.2014-02:00 +DATA/mydb/datafile/sysaux.268.795609021 4 Full 3966019235 11.08.2014-02:00 +DATA/mydb/datafile/users.267.795609021 6 Full 3966019235 11.08.2014-02:00 +DATA/mydb/datafile/undotbs2.259.795609223 List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 7481 350.75M SBT_TAPE 00:00:07 11.08.2014-05:10 BP Key: 12861 Status: AVAILABLE Compressed: NO Tag: TAG20140811T045758 Handle: MYDB_FRA<mydb_7568:855292216:1>.dbf Media: c0a86616:53a01494:5843:0010 List of Archived Logs in backup set 7481 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------------- ---------- --------- 1 6818 3965411881 11.08.2014-00:06 3966023530 11.08.2014-02:01 2 5775 3965558201 11.08.2014-00:07 3966024193 11.08.2014-02:01 1 6819 3966023530 11.08.2014-02:01 3966024188 11.08.2014-02:01 1 6820 3966024188 11.08.2014-02:01 3966024201 11.08.2014-02:01 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 7482 256.00K SBT_TAPE 00:00:03 11.08.2014-05:10 BP Key: 12862 Status: AVAILABLE Compressed: NO Tag: TAG20140811T045758 Handle: MYDB_FRA<mydb_7569:855292224:1>.dbf Media: c0a86616:53a01494:5843:0010 List of Archived Logs in backup set 7482 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------------- ---------- --------- 2 5776 3966024193 11.08.2014-02:01 3966024663 11.08.2014-02:01 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 7499 2.02G SBT_TAPE 00:00:43 12.08.2014-05:15 BP Key: 12898 Status: AVAILABLE Compressed: NO Tag: TAG20140812T045758 Handle: MYDB_FRA<mydb_7586:855378884:1>.dbf Media: c0a86616:53a01494:5843:0010 List of Archived Logs in backup set 7499 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------------- ---------- --------- 1 6821 3966024201 11.08.2014-02:01 3966313658 11.08.2014-05:55 2 5777 3966024663 11.08.2014-02:01 3967773273 11.08.2014-12:03 1 6822 3966313658 11.08.2014-05:55 3967039836 11.08.2014-06:33 1 6823 3967039836 11.08.2014-06:33 3967772780 11.08.2014-12:03
3. Validate backup of datafiles
Now we do the same command but with validate instead preview keyword. Also we are going to need to allocate channels if needed, since this command will access the tape device and read the backup piece. There are number of ways of validating backupsets, i will cover only this one as it seems most natural ( we could also use “validate backupset” command for example).
Important thing to point out is that this command WILL NOT validate archivelog backups but only the datafile backup needed for the restore command. Archivelogs need to be validated separately as we have done in the 4th step of this post.
run {
ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
set until time "to_date('2014-08-11:9:00:00','yyyy-mm-dd:hh24:mi:ss')";
restore database validate;
}
If everthing is ok with your backup and it is accessible output should be a successful validation. Othervise, rman will report an error that can be troublesooted.
allocated channel: c1 channel c1: SID=1279 instance=mydb1 device type=SBT_TAPE channel c1: Data Protector A.06.20/407 executing command: SET until clause Starting restore at 13.08.2014-15:22 channel c1: starting validation of datafile backup set channel c1: reading from backup piece MYDB_FRA<mydb_7583:855377884:1>.dbf channel c1: piece handle=MYDB_FRA<mydb_7583:855377884:1>.dbf tag=TAG20140812T045758 channel c1: restored backup piece 1 channel c1: validation complete, elapsed time: 00:16:56 Finished restore at 13.08.2014-15:39 released channel: c1
4. Validate backup of archivelogs
Same as step 3 but for archivelogs. We need to identify which sequences are needed for our restore. This can be done from the preview command in 2nd step. As you can see our environment is RAC with 2 threads of logs. We will need to validate both of them. I won’t put output from this to keep his post shorter, but it is almost identical as from previous step. At the end it should write “validation complete”.
run {
ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
restore archivelog from sequence 6818 until sequence 6824 thread=1 validate;
restore archivelog from sequence 5775 until sequence 5778 thread=2 validate;
}
5. Validate backup of control file or spfile
Above steps are enough for some cases but when validating, why not validate everything. Any restore command can be validated, so we can do it also for controlfile or spfile. For all other uses, examples please see the official oracle reference to RESTORE command and VALIDATE command.
run {
ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
restore controlfile from autobackup validate;
restore spfile from autobackup validate;
}
Still bear in mind that you should validate whole restore procedure from time to time and not just RMAN. Use a separate server that’s not in production and restore or clone whole database along with it’s dependent systems. Write all the needed steps with all the tiniest details that need to be done manually or will be done automatically.