Most of you may have already been tackling block corruption in oracle by simply employing the appropriate rman features. What actually appeared as the top most error on the monitoring log this morning was the following rman ERROR MESSAGE STACK, that looked as usual business at first:
channel ORA_DISK_2: backup set complete, elapsed time: 02:57:02 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup plus archivelog command at 01/01/2012 05:17:04 RMAN-03009: failure of backup command on ORA_DISK_2 channel at 01/01/2012 02:20:02 ORA-19566: exceeded limit of 0 corrupt blocks for file H:\ORACLE\SAN_3\ORADATA\WAZ\TEXT\TEXT_DATIDX_05.DBF
Executing dbv against it proved the assumption of rman.
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Jan 3 11:43:56 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = H:\ORACLE\SAN_3\ORADATA\WAZ\TEXT\TEXT_DATIDX_05.DBF Page 261345 is marked corrupt Corrupt block relative dba: 0x1a83fce1 (file 106, block 261345) Bad header found during dbv: Data in bad block: type: 40 format: 2 rdba: 0x16c3fce1 last change scn: 0x000a.2b57e527 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xe5272802 check value in block header: 0x16f2 computed block checksum: 0x0 Page 261408 is marked corrupt Corrupt block relative dba: 0x1a83fd20 (file 106, block 261408) Bad header found during dbv: Data in bad block: type: 40 format: 2 rdba: 0x16c3fd20 last change scn: 0x000a.2b58ef29 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xef292802 check value in block header: 0xa93d computed block checksum: 0x0 Page 261420 is marked corrupt Corrupt block relative dba: 0x1a83fd2c (file 106, block 261420) Bad header found during dbv: Data in bad block: type: 40 format: 2 rdba: 0x16c3fd2c last change scn: 0x000a.2b58ef29 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xef292802 check value in block header: 0xb930 computed block checksum: 0x0 DBVERIFY - Verification complete Total Pages Examined : 262144 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 257948 Total Pages Failing (Index): 0 Total Pages Processed (Other): 2254 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 1939 Total Pages Marked Corrupt : 3 Total Pages Influx : 0 Highest block SCN : 1084849528 (10.1084849528)
However, trying a block recovery by rman eventually delivered no results (after running for hours unpacking any available backup set) because the blocks in question were empty!
Knowing that oracle always reformats empty (here previously deleted) blocks before writing to it, a dummy table was created to (re)write any available block of the tablespace until a tablespace extension error occurs (do review the dedicated tablespace settings in advance):
create table reformat_me tablespace TEXT_DATIDX as select * from all_objects;
Repeat until the tablespace extension error:
insert into reformat_me select * from reformat_me; commit; ... ORA-1653: unable to extend table reformat_me by 128 in tablespace TEXT_DATIDX
Execute another dbv based check:
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Jan 3 14:40:39 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = H:\ORACLE\SAN_3\ORADATA\WAZ\TEXT\TEXT_DATIDX_05.DBF DBVERIFY - Verification complete Total Pages Examined : 262144 Total Pages Processed (Data) : 116700 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 143913 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1531 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 1088144588 (10.1088144588)
Execute another rman baseline (level 0) backup immediately now!!!
Have fun!
Absolutely brilliant!!
Legend – was trying to resolve this issue for hours before I found this. FYI it only cleared dbv once I’d dropped the table. Then ran rman validate to clear v$database_block_corruption. All good. Thanks !!