Oracle free block corruption - test case

by Michael Dinh on Nov 4, 2013 12:00:00 AM

My very first blog post at Pythian - Trick or Treat? So there I was, faced with more that 4000 block corruptions as shown from v$database_block_corruption. The script corrupt_seg.sql identified the corruption as Free Block Corruption (block not associated with any segments). So how can free block corruption be removed? One method is to format the corrupted block following - How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1) The above mentioned procedure can be cumbersome as it requires creating trigger, dummy table, extents, etc... Thinking, "There has to be a better way to do this". Remembering, "Block Media Recovery (BMR) using blockrecover" The test case will simulate free block corruption and demonstrate how to recover from free block corruption.

Configuration

$ sqlplus / as sysdba
 
 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 31 11:36:36 2013
 
 Copyright (c) 1982, 2011, Oracle. All rights reserved.
 
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 LAX:(SYS@db01)> show parameter compatible;
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 compatible string 11.2.0.3
 LAX:(SYS@db01)> select * from v$restore_point;
 
 no rows selected
 
 LAX:(SYS@db01)>

Backup Database Level 0

$ rman target /
 
 Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 31 11:47:28 2013
 
 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
 connected to target database: DB01 (DBID=1452485914)
 
 RMAN> list backup summary;
 
 using target database control file instead of recovery catalog
 
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- -------------------- ------- ------- ---------- ---
 737 B A A DISK 31-OCT-2013 11:45:02 1 1 YES AINC0_THU
 738 B 0 A DISK 31-OCT-2013 11:45:07 1 1 YES DINC0_THU
 739 B 0 A DISK 31-OCT-2013 11:45:09 1 1 YES DINC0_THU
 740 B 0 A DISK 31-OCT-2013 11:45:26 1 1 YES DINC0_THU
 741 B 0 A DISK 31-OCT-2013 11:46:00 1 1 YES DINC0_THU
 742 B 0 A DISK 31-OCT-2013 11:46:11 1 1 YES DINC0_THU
 743 B A A DISK 31-OCT-2013 11:46:18 1 1 YES AINC0_THU
 744 B F A DISK 31-OCT-2013 11:46:28 1 1 NO TAG20131031T114620
 
 RMAN>

Create Tablespace and Index

$ sqlplus / as sysdba
 
 SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 31 11:48:17 2013
 
 Copyright (c) 1982, 2011, Oracle. All rights reserved.
 
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 LAX:(SYS@db01)> select name from v$tablespace;
 
 NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS
 TEMP
 USER_DATA
 
 LAX:(SYS@db01)> create tablespace corrupt datafile size 16m;
 
 Tablespace created.
 
 LAX:(SYS@db01)> select name from v$tablespace;
 
 NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS
 TEMP
 USER_DATA
 CORRUPT
 
 6 rows selected.
 
 LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;
 
 Index created.
 
 LAX:(SYS@db01)> alter system switch logfile;
 
 System altered.
 
 LAX:(SYS@db01)>

Find Blocks to Corrupt

Note: Output formatted to look nicer

LAX:(SYS@db01)> @corrupt.sql
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1051 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1043 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1047 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1045 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1034 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2308 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2274 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2258 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2292 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2362 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2244 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2390 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2283 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2228 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2304 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2234 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2238 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=827 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=843 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=835 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=131 << EOF
 CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 EOF
 
 21 rows selected.
 
 LAX:(SYS@db01)> exit

Corrupt Blocks

Note: Output formatted to look nicer

[oracle@lax:db01]/home/oracle
 $ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=827 << EOF 
 > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 > EOF
 0+1 records in
 0+1 records out
 112 bytes (112 B) copied, 2.5018e-05 s, 4.5 MB/s
 [oracle@lax:db01]/home/oracle
 $ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=843 << EOF 
 > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 > EOF
 0+1 records in
 0+1 records out
 112 bytes (112 B) copied, 0.000121112 s, 925 kB/s
 [oracle@lax:db01]/home/oracle
 $ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=835 << EOF 
 > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 > EOF
 0+1 records in
 0+1 records out
 112 bytes (112 B) copied, 9.0063e-05 s, 1.2 MB/s
 [oracle@lax:db01]/home/oracle
 $ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=131 << EOF 
 > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
 > EOF
 0+1 records in
 0+1 records out
 112 bytes (112 B) copied, 0.000117363 s, 954 kB/s
 [oracle@lax:db01]/home/oracle
 $

RMAN Validate Tablespace

RMAN> validate tablespace corrupt;
 
 Starting validate at 31-OCT-2013 12:07:13
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=40 device type=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: SID=45 device type=DISK
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
 List of Datafiles
 =================
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 ---- ------ -------------- ------------ --------------- ----------
 5 FAILED 0 1914 2048 1928464
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data 0 0
  Index 0 0
  Other 4 134
 
 validate found one or more corrupt blocks
 See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2327.trc for details
 Finished validate at 31-OCT-2013 12:07:21
 
 RMAN>

Check for Corruption

There are 4 corrupted blocks, 1 Used by Index, 3 Free

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2 ;
 
 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
 ----- ------ ------ ------------------ ---------
  5 131 1 0 CORRUPT
  5 827 1 0 CORRUPT
  5 835 1 0 CORRUPT
  5 843 1 0 CORRUPT
 
 LAX:(SYS@db01)>
 LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2 , greatest(e.block_id, c.block#) s_blk#
  3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5 - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6 , null description
  7 FROM dba_extents e, v$database_block_corruption c
  8 WHERE e.file_id = c.file#
  9 AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block#
  11 UNION
  12 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
  13 , header_block s_blk#
  14 , header_block e_blk#
  15 , 1 blk_corrupt
  16 , 'Segment Header' description
  17 FROM dba_segments s, v$database_block_corruption c
  18 WHERE s.header_file = c.file#
  19 AND s.header_block between c.block# and c.block# + c.blocks - 1
  20 UNION
  21 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  22 , greatest(f.block_id, c.block#) s_blk#
  23 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
  24 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  25 - greatest(f.block_id, c.block#) + 1 blk_corrupt
  26 , 'Free Block' description
  27 FROM dba_free_space f, v$database_block_corruption c
  28 WHERE f.file_id = c.file#
  29 AND f.block_id <= c.block# + c.blocks - 1 30 AND f.block_id + f.blocks - 1 >= c.block#
  31 order by file#, s_blk#
  32 ;
 
 OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# S_BLK# E_DBLK# BLK_CORRUPT DESCRIPTION
 -------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
 HR INDEX X 5 131 131 1
  5 827 827 1 Free Block
  5 835 835 1 Free Block
  5 843 843 1 Free Block
 
 LAX:(SYS@db01)>

Recreate Corrupted Index

LAX:(SYS@db01)> drop index hr.x;
 
 Index dropped.
 
 LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;
 
 Index created.
 
 LAX:(SYS@db01)>

RMAN Validate Tablespace

Let's use New Feature RMAN 11G : Data Recovery Advisor

RMAN> validate tablespace corrupt;
 
 Starting validate at 31-OCT-2013 12:30:39
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=32 device type=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: SID=37 device type=DISK
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
 List of Datafiles
 =================
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 ---- ------ -------------- ------------ --------------- ----------
 5 FAILED 0 1914 2048 1929833
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data 0 0
  Index 0 1
  Other 3 133
 
 validate found one or more corrupt blocks
 See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2681.trc for details
 Finished validate at 31-OCT-2013 12:30:47
 
 RMAN> list failure;
 
 List of Database Failures
 =========================
 
 Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- -------------------- -------
 19301 HIGH OPEN 31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks
 
 RMAN> list failure 19301 detail;
 
 List of Database Failures
 =========================
 
 Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- -------------------- -------
 19301 HIGH OPEN 31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks
  Impact: Some objects in tablespace might be unavailable
  List of child failures for parent failure ID 19301
  Failure ID Priority Status Time Detected Summary
  ---------- -------- --------- -------------------- -------
  25665 HIGH OPEN 31-OCT-2013 12:07:18 Block 843 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
  25659 HIGH OPEN 31-OCT-2013 12:07:17 Block 835 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
  25653 HIGH OPEN 31-OCT-2013 12:07:16 Block 827 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
 
 RMAN> advise failure;
 
 List of Database Failures
 =========================
 
 Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- -------------------- -------
 19301 HIGH OPEN 31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks
  Impact: Some objects in tablespace might be unavailable
  List of child failures for parent failure ID 19301
  Failure ID Priority Status Time Detected Summary
  ---------- -------- --------- -------------------- -------
  25665 HIGH OPEN 31-OCT-2013 12:07:18 Block 843 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
  25659 HIGH OPEN 31-OCT-2013 12:07:17 Block 835 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
  25653 HIGH OPEN 31-OCT-2013 12:07:16 Block 827 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
  Impact: Object owned by might be unavailable
 
 analyzing automatic repair options; this may take some time
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 analyzing automatic repair options complete
 
 Mandatory Manual Actions
 ========================
 no manual actions available
 
 Optional Manual Actions
 =======================
 no manual actions available
 
 Automated Repair Options
 ========================
 Option Repair Description
 ------ ------------------
 1 Restore and recover datafile 5
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/lax_db01/db01/hm/reco_2597877360.hm
 
 RMAN> repair failure preview;
 
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/lax_db01/db01/hm/reco_2597877360.hm
 
 contents of repair script:
  # restore and recover datafile
  sql 'alter database datafile 5 offline';
  restore datafile 5;
  recover datafile 5;
  sql 'alter database datafile 5 online';
 
 RMAN>

Notice the recommendation: OFFLINE DATAFILE will create downtime. I don't like it. This is a 99999 enviroment. hehe Also, there is no backup since the tablespace was created

Backup check logical datafile

Is this going to work?

RMAN> recover corruption list;
 
 Starting recover at 31-OCT-2013 12:35:06
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 10/31/2013 12:35:07
 RMAN-06026: some targets not found - aborting restore
 RMAN-06023: no backup or copy of datafile 5 found to restore
 
 RMAN> backup check logical datafile 5;
 
 Starting backup at 31-OCT-2013 12:35:23
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 channel ORA_DISK_1: starting compressed full datafile backup set
 channel ORA_DISK_1: specifying datafile(s) in backup set
 input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
 channel ORA_DISK_1: starting piece 1 at 31-OCT-2013 12:35:24
 channel ORA_DISK_1: finished piece 1 at 31-OCT-2013 12:35:27
 piece handle=/oracle/backup/bkup_baonpjkc_1_1 tag=TAG20131031T123523 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
 Finished backup at 31-OCT-2013 12:35:27
 
 Starting Control File and SPFILE Autobackup at 31-OCT-2013 12:35:27
 piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_31/o1_mf_s_830262927_975d83z4_.bkp comment=NONE
 Finished Control File and SPFILE Autobackup at 31-OCT-2013 12:35:42
 
 RMAN> recover corruption list;
 
 Starting recover at 31-OCT-2013 12:35:59
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 
 channel ORA_DISK_1: restoring block(s)
 channel ORA_DISK_1: specifying block(s) to restore from backup set
 restoring blocks of datafile 00005
 channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_baonpjkc_1_1
 channel ORA_DISK_1: piece handle=/oracle/backup/bkup_baonpjkc_1_1 tag=TAG20131031T123523
 channel ORA_DISK_1: restored block(s) from backup piece 1
 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
 
 starting media recovery
 media recovery complete, elapsed time: 00:00:04
 
 Finished recover at 31-OCT-2013 12:36:06
 
 RMAN> validate tablespace corrupt;
 
 Starting validate at 31-OCT-2013 12:36:17
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
 List of Datafiles
 =================
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 ---- ------ -------------- ------------ --------------- ----------
 5 OK 0 1914 2051 1929833
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data 0 0
  Index 0 1
  Other 0 133
 
 Finished validate at 31-OCT-2013 12:36:18
 
 RMAN> list backup summary;
 
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- -------------------- ------- ------- ---------- ---
 737 B A A DISK 31-OCT-2013 11:45:02 1 1 YES AINC0_THU
 738 B 0 A DISK 31-OCT-2013 11:45:07 1 1 YES DINC0_THU
 739 B 0 A DISK 31-OCT-2013 11:45:09 1 1 YES DINC0_THU
 740 B 0 A DISK 31-OCT-2013 11:45:26 1 1 YES DINC0_THU
 741 B 0 A DISK 31-OCT-2013 11:46:00 1 1 YES DINC0_THU
 742 B 0 A DISK 31-OCT-2013 11:46:11 1 1 YES DINC0_THU
 743 B A A DISK 31-OCT-2013 11:46:18 1 1 YES AINC0_THU
 744 B F A DISK 31-OCT-2013 11:46:28 1 1 NO TAG20131031T114620
 745 B F A DISK 31-OCT-2013 11:55:35 1 1 NO TAG20131031T115530
 746 B F A DISK 31-OCT-2013 12:35:24 1 1 YES TAG20131031T123523
 747 B F A DISK 31-OCT-2013 12:35:33 1 1 NO TAG20131031T123527
 
 RMAN>

YEAH! It works and is an ONLINE operation.

Check Index

LAX:(SYS@db01)> select owner, index_name, table_owner, table_name, status from dba_indexes where index_name='X';
 
 OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS
 -------------------- ------------------------------ ------------------------------ ------------------------------ --------
 HR X HR DEPARTMENTS_OLD VALID
 
 LAX:(SYS@db01)>

Q.E.D. But, but I don't have 11g. This is a good reason to make a test case for upgrading.

References:

Unused Block Compression

During unused block compression, RMAN does not check each block. Instead, RMAN reads the bitmaps that indicate what blocks are currently allocated and then only reads the blocks that are currently allocated. Unused block compression is turned on automatically when all of the following five conditions are true: 1. The COMPATIBLE initialization parameter is set to 10.2 or higher. 2. There are currently no guaranteed restore points defined for the database. 3. The data file is locally managed. 4. The data file is being backed up to a backup set as part of a full backup or a level 0 incremental backup. 5. The backup set is created on disk, or Oracle Secure Backup is the media manager.

Null Block Compression

During null block compression, RMAN checks every block to see if it has ever contained data. Blocks that have never contained data are not backed up. Blocks that have contained data, either currently or in the past, are backed up.

Retrieved from Oracle® Database Backup and Recovery User's Guide11g Release 2 (11.2) on October 31, 2013

Metalink notes for reference

Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1) How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1) How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1) RMAN : Block-Level Media Recovery - Concept & Example (Doc ID 144911.1) New Rman Blockrecover command in 11g (Recover corruption list) (Doc ID 1390759.1) RMAN 11G : Data Recovery Advisor - RMAN command line example (Doc ID 762339.1)

corrupt.sql


 set heading off
 set lines 113
 SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
  to_number(S.HEADER_BLOCK + 1) || ' << EOF',
  'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
  'EOF'
  FROM DBA_SEGMENTS s, dba_data_files f
  WHERE f.tablespace_name = 'CORRUPT' AND s.segment_name like 'X%'
 ;

corrupt_seg.sql


 set lines 200 pages 10000 echo on
 col OWNER for a20
 col FILE# for 999
 col BLOCK# for 99999
 col BLOCKS for 99999
 col s_blk# for 99999
 col e_blk# for 99999
 col CORRUPTION_CHANGE# for 999999999999
 col SEGMENT_NAME for a25
 col PARTITION_NAME for a25
 select instance_name from v$instance
 ;
 select * from v$database_block_corruption order by 1,2,3,4
 ;
 
 SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  , greatest(e.block_id, c.block#) s_blk#
  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  - greatest(e.block_id, c.block#) + 1 blk_corrupt
  , null description
 FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
 AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
 UNION
 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
  , header_block s_blk#
  , header_block e_blk#
  , 1 blk_corrupt
  , 'Segment Header' description
 FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
 AND s.header_block between c.block# and c.block# + c.blocks - 1
 UNION
 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) s_blk#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  - greatest(f.block_id, c.block#) + 1 blk_corrupt
  , 'Free Block' description
 FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
 AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
 order by file#, s_blk#
 ;

Topics: Oracle Technical Track

No Comments Yet

Let us know what you think

Share this

Oracle free block corruption - test case

Configuration

Backup Database Level 0

Create Tablespace and Index

Find Blocks to Corrupt

Corrupt Blocks

RMAN Validate Tablespace

Check for Corruption

Recreate Corrupted Index

RMAN Validate Tablespace

Backup check logical datafile

Check Index

References:

Unused Block Compression

Null Block Compression

Metalink notes for reference

corrupt.sql

corrupt_seg.sql

Share this

No Comments Yet

Subscribe by email