In Oracle 10g R2,we can use RMAN to check the corruptions in the database/datafiles or in archivelogs.
$) rman target / nocatalog
RMAN> backup validata database;
RMAN> backup validate datafile 4;
RMAN> backup validate archivelog all;
By default RMAN only checks for physical corruption not for logical corruptions. In order to make the rman to even check for logical corruptions we can go with check option in the backup validate command.
RMAN> backup validate check logical database;
RMAN> backup validate check logical datafile 4;
RMAN> backup validate check logical archivelog all;
Now after performing the above operations, if there are any corruptions in the database you can query the V$DATABASE_BLOCK_CORRUPTION view to find for any corruptions in database.
After taking the RMAN backup of you database, if you want to check the integrity of the backupsets you can use VALIDATE command
RMAN> VALIDATE BACKUPSET 51;
If you want to check whether the database can be restored using the available RMAN backups you can use restore with validate command
RMAN> RESTORE DATABASE VALIDATE;
If you want to check whether a tablespace can be restored using existing backups then use
RMAN> restore tablespace users validate;
If some of your backupsets are missing then below are the messages that RMAN throws while validating
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman/full17ldiqjb_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/rman/full17ldiqjb_1_1 tag=TAG20100513T120706
channel ORA_DISK_1: validation complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman/full16ldiqjb_1_1
ORA-19870: error reading backup piece /u01/app/oracle/rman/full16ldiqjb_1_1
ORA-19505: failed to identify file "/u01/app/oracle/rman/full16ldiqjb_1_1"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
failover to previous backup
data file 1 will be created automatically during restore operation
data file 2 will be created automatically during restore operation
data file 4 will be created automatically during restore operation
Finished restore at 11-AUG-10
