Corrupt database blocks I

I worked with corrupt blocks in a database a few weeks ago and decided to write three posts about it so I don't have to rethink all of it next time it happens. Hardware and the database have improved a lot, because I rarely run into this problem.

The first and obvious thing to state is that corrupt blocks in the database should be detected early by proper fault management (if you use Enterprise Manager you are good). When detected it should be fixed by using RMAN, either by command:

recover corruption list;

or by recovering individual blocks, e.g.

recover datafile 42 block 5 to 6;

In case you don't have any backup or the error went undetected for a long time and you decide that block recovery is not feasible you need to clean it up some other way. If the RMAN backup detects the corrupt block it will abort at first corrupt block (unless you change the SET MAXCORRUPT FOR DATAFILE). This means that there may be more corrupt blocks in the database and you may just as well find them all while you are working on it.

The Database Verification tool is supposed to do find all corrupt blocks, but last time (11.2.0.2 on Linux x64) it failed to find new ones. This tool is documented in the manual Utilities. I looped over all the data files with command dbv, but it was a waste of time. This may have something to do that there are different types of corruptions. Instead I used VALIDATE in RMAN. You can validate individual data files or the entire database:

VALIDATE DATAFILE 42;
VALIDATE DATABASE;

This will not consider MAXCORRUPTIONS, but scan the data file / entire database and update the view v$database_block_corruption with info on corrupt blocks found.

Next post will be on how to find objects that have, or rather, used to have data in these blocks.