Thursday, August 16, 2012

Corruption in the data file blocks

Friends I have seen this issue today.It was related to corrupt blocks reported in the alert log file.It was like following entry in the alert log.

Reread of blocknum=2233910, file=+DG1/main/datafile/db01.76820. found same corrupt data

After initial investigation we found that there is an entry in the V$database block corruption


SQL> select * from gv$database_block_corruption;

   INST_ID      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ---------- ------------------ ---------
         1         36    2233910          1                  0 FRACTURED
         2         36    2233910          1                  0 FRACTURED
        
we decided to run the dbverify utility .


dbv userid=sys/password file=+DG1/main/datafile/db01.76820

it showed no block corrupted but still we had entry in  the v$database_block_corruption.


We then followed the support doc 336133.1.Following query showed that the object is an index but its size is more than 20GB .If we have to rebuild it then it will take lot of time.


 select segment_name, segment_type, owner
       from dba_extents
      where file_id =
        and  between block_id
            and block_id + blocks -1;

To ascertain that this is indeed a corruption we ran rman validate on the datafile.This populated the v$database_block_corruption again and guess what!!
it didn't show any corruption as I was already doubting about it.

No comments: