Reformatting empty but corruptly marked oracle blocks


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!

Advertisements

2 comments

  1. 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 !!

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s