DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, December 29, 2016

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 %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
 
 
 
Done..!! 

No comments: