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..!!
Recommended Posts
