But what about if we need to recover several sparse corrupt blocks ? Should we need to issue several different recover commands ? How can I recover a list of sparse corrupt blocks ?
Just have a look at this other example. First of all let's wipe out some blocks of datafile 11 (and in particular blocks number 3,4,5,7,8 and 10)
[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=3 count=3 3+0 records in 3+0 records out 24576 bytes (25 kB) copied, 0.000780782 seconds, 31.5 MB/s [oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=7 count=2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.00026644 seconds, 61.5 MB/s [oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=10 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000561284 seconds, 14.6 MB/sThe database currently doesn't know there are few corrupt blocks on datafile 11:
SQL> select * from v$database_block_corruption; no rows selectedDBVERIFY is able to find them easily.
[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf blocksize=8192 DBVERIFY: Release 11.2.0.2.0 - Production on Mon Mar 18 22:46:36 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/marcov01.dbf Page 3 is marked corrupt Corrupt block relative dba: 0x02c00003 (file 11, block 3) Completely zero block found during dbv: Page 4 is marked corrupt Corrupt block relative dba: 0x02c00004 (file 11, block 4) Completely zero block found during dbv: Page 5 is marked corrupt Corrupt block relative dba: 0x02c00005 (file 11, block 5) Completely zero block found during dbv: Page 7 is marked corrupt Corrupt block relative dba: 0x02c00007 (file 11, block 7) Completely zero block found during dbv: Page 8 is marked corrupt Corrupt block relative dba: 0x02c00008 (file 11, block 8) Completely zero block found during dbv: Page 10 is marked corrupt Corrupt block relative dba: 0x02c0000a (file 11, block 10) Completely zero block found during dbv: DBVERIFY - Verification complete Total Pages Examined : 768 Total Pages Processed (Data) : 196 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 13 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 553 Total Pages Marked Corrupt : 6 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 14853679 (0.14853679)Just let's try to have a backup of tablespace MARCOV.
RMAN> backup tablespace marcov; Starting backup at 18-03-2013 22:41:59 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf channel ORA_DISK_1: starting piece 1 at 18-03-2013 22:41:59 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/18/2013 22:42:07 ORA-19566: exceeded limit of 0 corrupt blocks for file /home/oracle/app/oracle/oradata/orcl/marcov01.dbfIt fails, but as you can see it stops at the first corrupt block, logging just that single one.
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 11 3 1 0 ALL ZEROTo check and have a completed list of corrupt blocks you should issue a backup validate command:
RMAN> backup validate tablespace marcov; Starting backup at 18-03-2013 22:57:52 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 11 FAILED 0 553 768 14853679 File Name: /home/oracle/app/oracle/oradata/orcl/marcov01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 196 Index 0 0 Other 6 19 validate found one or more corrupt blocks See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc for details Finished backup at 18-03-2013 22:57:56Now the V$DATABASE_BLOCK_CORRUPTION view is appropriately filled with all corrupt blocks. There are three contiguous currupt blocks starting from the third, two contiguous corrupt blocks starting from the seventh and the corrupt block number 10.
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 11 10 1 0 ALL ZERO 11 7 2 0 ALL ZERO 11 3 3 0 ALL ZEROHere is the syntax if you want to recover all of them using a single command:
RMAN> recover datafile 11 block 3 to 5,7 to 8,10; Starting recover at 18-03-2013 23:00:54 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00011 channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp tag=TAG20130318T072836 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 18-03-2013 23:01:02When the recover finishes the V$DATABASE_BLOCK_CORRUPTION view doesn't contain any rows:
SQL> select * from v$database_block_corruption; no rows selectedAnd what happens if we have few sparse corrupt blocks on several datafiles ?
Is there a way to use a single command to recover all of them ?
Let's simulate this latest scenario, resetting the same blocks on datafile number 11 and five blocks of datafile number 12 starting from block number 3:
[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=3 count=3 3+0 records in 3+0 records out 24576 bytes (25 kB) copied, 0.000856502 seconds, 28.7 MB/s [oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=7 count=2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000239112 seconds, 68.5 MB/s [oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=10 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.00676381 seconds, 1.2 MB/s [oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/test01.dbf bs=8k conv=notrunc seek=3 count=5 5+0 records in 5+0 records out 40960 bytes (41 kB) copied, 0.000839468 seconds, 48.8 MB/sLet's fill the V$DATABASE_BLOCK_CORRUPTION view using the backup validate command on the two tablespaces:
RMAN> backup validate tablespace marcov,"TEST"; Starting backup at 18-03-2013 23:08:37 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 11 FAILED 0 553 768 14853679 File Name: /home/oracle/app/oracle/oradata/orcl/marcov01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 196 Index 0 0 Other 6 19 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 12 FAILED 0 113 128 14856079 File Name: /home/oracle/app/oracle/oradata/orcl/test01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 5 Index 0 0 Other 5 10 validate found one or more corrupt blocks See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc for details Finished backup at 18-03-2013 23:08:40Here is the list of corrupt blocks on different datafiles:
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 11 10 1 0 ALL ZERO 11 7 2 0 ALL ZERO 11 3 3 0 ALL ZERO 12 3 5 0 ALL ZEROYou can use the following command to let RMAN try to recover all the blocks listed into the V$DATABASE_BLOCK_CORRUPTION view.
RMAN> recover corruption list; Starting recover at 18-03-2013 23:09:53 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00011 channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp tag=TAG20130318T072836 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:03 Finished recover at 18-03-2013 23:10:03The V$DATABASE_BLOCK_CORRUPTION is empty again.
SQL> select * from v$database_block_corruption; no rows selectedNext post will be about how to recover corrupt blocks on SYSTEM tablespace. That's all.