Validating backups and database files using RMAN

Tuesday, December 29, 2009

The most critical tasks for every DBA are database backup and recovery procedures. Knowledge of all Oracle backup and recovery scenarios is practically the most essential skill. For almost every problem there is some solution, but if you can't restore/recover data you are in deep trouble.

To avoid possible problems it is important to regularly monitor backups and to perform validations using various tools. You can't say that you have valid backup until you successfully perform test restore on another machine. There are several methods to validate backups taken with RMAN. Restores on another machine is the best validation method and you should perform it regularly according how critical your database is. Big benefit from performing test restores is valuable practice you'll get.

Another method for validating backups is using RMAN commands.
As a part of my daily tasks I'am using this simple RMAN script for backup validation:

restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;

In this script I'am doing this checks:
- check most recent RMAN database backup
- check most recent RMAN archivelog(s) backup
- check most recent controlfile backup
- check most recent RMAN spfile backup

To check older backups just set SET UNTIL clause.

Using RESTORE VALIDATION I can verify whether database could be successfully restored or not using existing backups.

It is good practice to automate this tasks and then grep logs for errors. RESTORE VALIDATE operation takes time and it consumes valuable resources so you have to think about when to schedule validation.

You can consider this RMAN validation as addition to your regular trial recoveries.

About using RMAN to check Logical & Physical Database Corruption
During a RMAN backup or RMAN 'backup validate' every block currently used or previously used is read into memory then written to another portion of memory. During this memory to memory write the block is checked for corruption. Therefore RMAN's BACKUP command with the VALIDATE and CHECK LOGICAL clauses allow a Database Adminstrator to quickly check for both physical and logical corruption. If the initialization parameter DB_BLOCK_CHECKSUM=TRUE, specifying CHECK LOGICAL detects all types of corruption that are possible to detect.

Physical corruption can be defined as a damage to the internal data structure and prevents Oracle from being able to read the data in the corrupted blocks.
Logical corruption is situation when Oracle is able to find the data, but data values are incorrect.

To validate all datafiles execute:
RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup check logical validate database;
5> release channel c1;
6> }

After validate is complete query v$database_block_corruption to see if there are any rows listed there. Additionally you can check alert log for corruptions.

Metalink notes: [ID 338607.1], [ID 283053.1], [ID 428570.1]



Post a Comment