Find segments with corrupted blocks

RMAN picks up logical block corruption when you use the following option in your backup
script:

backup full check logical

The following procedure helps to identify segments with corrupted blocks if there are many
occurrences of block corruption in the alert log file:

1. Get the list of all currupted blocks (replace alert_DB.log with the actual file name)

grep 'logical corruption' alert_DB.log > tmp.txt

Output will be something like this:

Error backing up file 16, block 1355: logical corruption
Error backing up file 16, block 1361: logical corruption
Error backing up file 16, block 1365: logical corruption
...

2. Generate an SQL script to find out which segments have corrupted blocks (I'm not good at
awk so there could be a more elegant solution I guess, but this works anyway)

cat tmp.txt |\
awk '{ printf ("%s %s\n", $5, $7); }' |\
awk -F, '{ printf ("%s %s\n", $1, $2); }' |\
awk -F: '{ printf ("%s\n", $1); }' |\
awk '{ printf ("SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO FROM DBA_EXTENTS WHERE
FILE_ID = %s AND %s BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;\n", $1, $2); }' >
find_segs.sql

3. Run the script (this may take a while)

SQLPLUS> col SEGMENT_NAME format a30
SQLPLUS> spool corrupted_segments.log
SQLPLUS> @find_segs
SQLPLUS> spool off

4. Get the segment names

sort -u corrupted_segments.log