Add new comment

On Oracle-L there was a recent discussion about detecting block corruption.

One suggestion was to do an Export to /dev/null. While this may get you 95% of the way there it is mainly relying on side effects of data access to detect any issues. If blocks are cached then it might not root out physical corruption.

How about leveraging RMAN? If you don't already use RMAN for backups or don't have a lot of extra disk space lying around this might be difficult. Turns out RMAN has a command that can be used to efficiently check blocks for physical and then logical corruption. It reads blocks but does not output backup pieces to disk. According to the RMAN Reference manual:

CHECK LOGICAL
Tests data and index blocks that pass physical corruption checks for logical corruption, for example, corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log and server session trace file.
If the sum of physical and logical corruptions detected for a file is no more than its MAXCORRUPT setting, then the backup command completes, and V$DATABASE_BLOCK_CORRUPTION is populated with any corrupt block ranges. If there are more thanMAXCORRUPT corrupt blocks, then the command terminates without updating the view and no output file is created for the backup.
If the initialization parameter DB_BLOCK_CHECKSUM=TRUE, and if MAXCORRUPT and NOCHECKSUM are not set, then specifying CHECK LOGICAL detects all types of corruption that are possible to detect.

Here is an example of the command in use... at the RMAN prompt:

run {
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
backup validate check logical database;
}

If your system can support more I/O feel free to add more channels. (On Solaris try iostat -cxn 5 to view mounts and their associated I/O).

As the command is running you can check long operations to view progress and get an estimated completion time. Open a second session to the database and run the following query:

SELECT ROUND(sofar/totalwork*100,2), v$session_longops.*
FROM v$session_longops
WHERE sofar <> totalwork
ORDER BY target, sid;

After the command is finished, run the following to view any corrupt blocks:

-- Show any errors detected
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

-Dave