Saturday, March 10, 2012

Block corruption

Today we found block corruption (both table & index blocks got corrupted) in one of our production database.
we came to konw about this block corruptions during RMAN Full backup. Initially it reported about physical block corruption.
after that we manually ran "backup validate check logical database" to find any Logical block corruption in database.
once the execution of RMAN validate command completes it reports about the block corruptions in
V$DATABASE_BLOCK_CORRUPTION view and found one table block had logical corruption.
SQL>select * from v$database_block_corruption;
   FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        14     610816          1         6.4175E+12 LOGICAL        
        63     499126          1                  0 CORRUPT
        62     351954          1                  0 CORRUPT
        68     121023          2                  0 FRACTURED
        62     351446          1                  0 CORRUPT
        62     352465          2                  0 FRACTURED
        63     499125          1                  0 FRACTURED

Finally there were six index blocks(physical corruption) and one table block(Logical corruption) got corrupted on our production database!
For Logical corruption we used DBMS_REPAIR package to skip the corrupted block and for Index corruption we rebuilded all those indexes
from that tablespace to new tablespace and dropped old one.

Note:
In this scenario, the reason why we rebuild indexes to new tablespace is, to eliminate that corrupted block. If you are
rebuilding corrupted indexes on same tablespace corrupted block won't get remove, still it will show if you ran DBVerify utility.

No comments:

Post a Comment