Being an Oracle DBA we all are known about data block. Here this line explains the technical definition of data block A data block is the smallest unit of storage in an Oracle database. Every database has a default block size (specified when the database is created), although blocks in different tablespaces may have different block sizes. Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS.
These views show how many blocks are allocated for database object and how many blocks are available(free) in a segment/extent.
This post explains about those steps which give the solution of to recover sparse corrupted data blocks using ‘recover datafile’ RMAN syntax. What 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/s
The database currently doesn’t know there are few corrupt blocks on datafile 11:
SQL> select * from v$database_block_corruption; no rows selected
DBVERIFY 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.dbf
It 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 ZERO
To 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:56
Now the V$DATABASE_BLOCK_CORRUPTION view is appropriately filled with all corrupt blocks. There are three contiguous corrupt 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 ZERO
Here 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:02
When the recover finishes the V$DATABASE_BLOCK_CORRUPTION view doesn’t contain any rows:
SQL> select * from v$database_block_corruption; no rows selected
And 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/s
Let’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:40
Here 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 ZERO
You 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:03
The V$DATABASE_BLOCK_CORRUPTION is empty again.
SQL> select * from v$database_block_corruption; no rows selected
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
