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.

1 comment: