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
cat tmp.txt |
awk '{ printf ("%s %sn", $5, $7); }' |
awk -F, '{ printf ("%s %sn", $1, $2); }' |
awk -F: '{ printf ("%sn", $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
Done..!!
