Oracle Rman Block Corruption Recovery

Cases

Case 1

Error

piece handle=/u04/oradata/sid/rman/current/SID_nungi5m3_1_1_20120720 tag=TAG20120720T075631 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:07:39
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/20/2012 09:27:42

RMAN-03009: failure of backup command on ORA_DISK_3 channel at 07/20/2012 08:55:40
ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/oradata/sid/TS_DATAIMPORT_JOIN_DAT_02.DBF

Recovery Manager complete.

Verify Datafile

dbv file=/u02/oradata/lbc/TS_DATAIMPORT_JOIN_DAT_02.DBF

DBVERIFY: Release 10.2.0.4.0 - Production on 20 17:06:02 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u02/oradata/lbc/TS_DATAIMPORT_JOIN_DAT_02.DBF
Page 229009 is marked corrupt
Corrupt block relative dba: 0x20837e91 (file 130, block 229009)
Bad header found during dbv:
Data in bad block:
 type: 255 format: 7 rdba: 0xffffffff
 last change scn: 0xffff.ffffffff seq: 0xff flg: 0xff
 spare1: 0xff spare2: 0xff spare3: 0xffff
 consistency value in tail: 0x81c00602
 check value in block header: 0xffff
 computed block checksum: 0x352c

^[[6~^[[5~^[[5~^[[5~^[[6~^[[5~^[[5~

DBVERIFY - Verification complete

Total Pages Examined         : 1092608
Total Pages Processed (Data) : 1086158
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1408
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5041
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 1461596826 (543.1461596826)

retrieve file# and block#

select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
       103     452592          1                  0 CORRUPT
       123    1285869          1                  0 FRACTURED
       130     229009          1                  0 CORRUPT

check the db object of block

select * from v$database_block_corruption;
 
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
       103     452592          1                  0 CORRUPT
       123    1285869          1                  0 FRACTURED
       130     229009          1                  0 CORRUPT
 
SQL> select segment_name,segment_type,owner
  2  from dba_extents
  3  where file_id=&file_id
  4  and &block_id between block_id and block_id + blocks -1;
Enter value for file_id: 130
old   3: where file_id=&file_id
new   3: where file_id=130
Enter value for block_id: 229009
old   4: and &block_id between block_id and block_id + blocks -1
new   4: and 229009 between block_id and block_id + blocks -1
 
SEGMENT_NAME     SEGMENT_TYPE   OWNER
-----------------   ---------------- ----------------------------------------
ABCDEFGHIJKLMN    TABLE                USERABC

recreate or recover db object

when the object is a index, drop and recreate it.

set long 9999; -- only when you are using sqlplus
dbms_metadata.get_ddl('INDEX', 'THE_OWNER', 'THE_INDEX');
drop index THE_OWNER.THE_INDEX;
create index THE_OWNER.THE_INDEX ... (the ddl generated by previous get_ddl call);

when the object is a table or other object with real data, should recover the block from backup, and run media recovery.

rman target / nocatalog
recover datafile 130 block 229009;
exit;

check block bump

when the backup is unavailable, check the block dump and confirm the corruption

alter system dump datafile 130 block 229009;
 
System altered.
 
SQL> select
  2    u_dump.value   || '/'     ||
  3    db_name.value  || '_ora_' ||
  4    v$process.spid ||
  5    nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  6    || '.trc'  "Trace File"
  7  from
  8               v$parameter u_dump
  9    cross join v$parameter db_name
 10    cross join v$process
 11          join v$session
 12            on v$process.addr = v$session.paddr
 13  where
 14   u_dump.name   = 'user_dump_dest' and
 15   db_name.name  = 'db_name'        and
 16   v$session.audsid=sys_context('userenv','sessionid');
 
Trace File
--------------------------------------------------------------------------------
/u01/oracle/app/admin/sid/udump/sid_ora_31642.trc
/u01/oracle/app/admin/sidi/udump/sid_ora_6637.trc
 
$ pushd /u01/oracle/app/admin/sid/udump
 
$ head -n 100 sid_ora_31642.trc
 
/u01/oracle/app/admin/sid/udump/sid_ora_31642.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      host-test
Release:        2.6.9-42.ELsmp
Version:        #1 SMP Wed Jul 12 23:32:02 EDT 2006
Machine:        x86_64
Instance name: sid
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 31642, image: oracle@host-test (TNS V1-V3)
 
*** 2012-07-22 02:36:41.027
*** ACTION NAME:() 2012-07-22 02:36:40.980
*** MODULE NAME:(sqlplus@host-test (TNS V1-V3)) 2012-07-22 02:36:40.980
*** SERVICE NAME:(SYS$USERS) 2012-07-22 02:36:40.980
*** SESSION ID:(522.935) 2012-07-22 02:36:40.980
Start dump data blocks tsn: 90 file#: 130 minblk 229009 maxblk 229009
buffer tsn: 90 rdba: 0x35373030 (212/3616816)
scn: 0x3137.34303338 seq: 0x0b flg: 0x31 tail: 0x81c00602
frmt: 0x01 chkval: 0x3030 type: 0x37=unknown
Hex dump of corrupt header 4 = CORRUPT
Dump of memory from 0x0000000006706C00 to 0x0000000006706C14
006706C00 310B3137 35373030 34303338 310B3137  [71.10075830471.1]
006706C10 35373030                             [0075]
Hex dump of block: st=4, typ_found=0
Dump of memory from 0x0000000006706C00 to 0x0000000006708C00
006706C00 310B3137 35373030 34303338 310B3137  [71.10075830471.1]
006706C10 35373030 34303338 310B3137 35373030  [0075830471.10075]
006706C20 34303338 FFFF3137 FFFFFFFF 0BFFFFFF  [830471..........]
006706C30 37303031 30333835 FF313734 FFFFFFFF  [10075830471.....]
006706C40 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF  [................]
...

confirm the previous block dump

with luck we have a previous backup but no archivedlog between the backup and corruption.
so we can try to find the difference between the old block image from backupset and the corrupted block.

alter system dump file 130 block 229009;

Reference:
http://www.packtpub.com/article/oracle-rdbms-sql-command-dump-block