Saturday, March 10, 2012

How to recover block which is corrupted (Physical corruption)

Today Storage team added new LUN to one of our production database, after that old LUNs were inaccessaible. Means our Index and Datafile mount points were unmounted while database was running.

Then storage and unix team worked on this issue finally they restored those two filesystem.
after that we were successfully able to startup the production database and we came to know about
block corruption during RMAN Full backup.

then based on RMAN error log we used DBVerify utility to check the number of corrupted blcoks.
oracle:prod_db@prod /oracle > dbv file=/ORA/INDEX03/prod_db/indx1K.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Wed Mar 7 17:39:48 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /ORA/INDEX03/prod_db/indx1K.dbf
Page 121023 is influx - most likely media corrupt
Corrupt block relative dba: 0x1101d8bf (file 68, block 121023)Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x1101d8bf
 last change scn: 0x05c2.8be33eb5 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x2896
 computed block checksum: 0xd58f
Page 121024 is influx - most likely media corrupt
Corrupt block relative dba: 0x1101d8c0 (file 68, block 121024)Fractured block found during dbv:
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x3eb50602
 check value in block header: 0x0
 block checksum disabled

DBVERIFY - Verification complete
Total Pages Examined         : 1048576
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1024625
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1989
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 21960
Total Pages Marked Corrupt   : 2
 Total Pages Influx           : 2
Highest block SCN            : 1262329760 (1494.1262329760)
oracle:prod_db@prod /oracle >   
From above output we came to know that block# 121023,121024 got corrupted on datafile# 68. We had nearly 8 corrupted blocks from three different datafiles.

  
SQL>select * from v$database_block_corruption;

   FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        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
 
Initially we rebuilded all corrupted blocks belongs to the Index segment on the same tablespace. Then index corruption got cleared but still DBVfile shows same blocks are corrupted.

So we created new index tablespace then rebuilded all the indexes from old tablespace to new one.

I Hope this article helped to you. I am expecting your suggestions/feedback.

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.