Need assistance in recovering DB corruption blocks.
Database Version-
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Environment: TWO node RAC on ASM instace.
PROBLEM:
--------
We had a database crash due to underlying storage issue and after bringing up
the database, we can see huge database block corruption in the database.
Currently we have 23625 blocks corrupted in database. We need your assistance
in recovering the the blocks at the earliest. From our end, we were trying
recover current list from rman,however it failed with pga shortage as the
number of block is huge. Recovering by recover datafile "" block no"" will
take days of time as we have 228 files to recover.
DIAGNOSTIC ANALYSIS:
--------------------
Sample Alert Log :
SEGMENT OWNER = SYSMAN, SEGMENT TYPE = Index Segment
Sat Oct 08 08:11:57 2011
Errors in file
/u04/oraout/intem01/diag/rdbms/intem_aemidbp/intem01/trace/intem01_lms1_21819.
trc (incident=499317):
ORA-01578: ORACLE data block corrupted (file # 138, block # 216446)
ORA-01110: data file 138: '+INTEM_DATA_01/intem/datafile/mgmt63.dbf'
Errors in file
/u04/oraout/intem01/diag/rdbms/intem_aemidbp/intem01/trace/intem01_lms1_21819.
trc:
ORA-00605: Message 605 not found; product=RDBMS; facility=ORA
ORA-01578: ORACLE data block corrupted (file # 138, block # 216446)
ORA-01110: data file 138: '+INTEM_DATA_01/intem/datafile/mgmt63.dbf'
Errors in file
/u04/oraout/intem01/diag/rdbms/intem_aemidbp/intem01/trace/intem01_j001_24452.
trc (incident=499873):
ORA-01578: ORACLE data block corrupted (file # 138, block # 216446)
ORA-01110: data file 138: '+INTEM_DATA_01/intem/datafile/mgmt63.dbf'
Sat Oct 08 08:11:57 2011
Sweep Incident[499873]: completed
Sat Oct 08 08:11:57 2011
Sweep Incident[499317]: completed
Hex dump of (file 138, block 216446) in trace file
/u04/oraout/intem01/diag/rdbms/intem_aemidbp/intem01/trace/intem01_m000_28753.
trc
Corrupt block relative dba: 0x22834d7e (file 138, block 216446)
Bad check value found during validation
Data in bad block:
type: 6 format: 2 rdba: 0x22834d7e
last change scn: 0x0af4.528b66fa seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x66fa0601
check value in block header: 0xfca4
CURRENT STATUS:
We have tried recovering some blocks, however few successfull. Currently v$database_block_corruption is showing 64 blocks corrupted, however it keeps on fluctuating, we do not see a consistency here.
Unfortunately, althoug we have Datab Guard configured for standby db, because of filer crash our DR block also got corrupted in somehow, along with the backups.
We do not have healthy backup as well. However, we have all archives intact. We do not want to do a point in time recovery and rollback till the time before the issue starts, as it will result heavy Data lost of 2 weeks.
Looking out for all of your assistance.