Oracle free block corruption - test case
Tags:
Oracle,
Technical Track
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 nicerLAX:(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 FreeLAX:(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 AdvisorNotice 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 createdRMAN> 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>
Backup check logical datafile
Is this going to work?YEAH! It works and is an ONLINE operation.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>
Check Index
Q.E.D. But, but I don't have 11g. This is a good reason to make a test case for upgrading.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)>
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# ;
