Find segments with corrupted blocks

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..!! 
  • December 29, 2016 | 14 views
  • Comments