Database Block Corruption - Deduction

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.

The following rman commands will deduct for physical/logical database corruption for the database and logs into db_validate.log file.

$ rman target / catalog rman/@rcat
RMAN> spool log to '/home/oracle/Scripts/db_validate.log';
RMAN>allocate channel for maintenance device type disk;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> run {
1 backup validate database;
2 backup check logical validate database;
}

To monitor this process, issue the following sql statement from SQLPLUS.

SQL> select sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complete"
from v$session_longops where opname like 'RMAN%' and opname not like '%aggregate%'
and totalwork != 0 and sofar <> totalwork
/

After this process completed, look for "block corruption" string in alert.log file, also query the v$database_block_corruption dynamic view, which will retrun records if there is a corruption in the database.

Reference:
Note:283053.1 - How To Use RMAN To Check For Logical & Physical Database Corruption