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.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.