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)

The database currently doesn’t know there are few corrupt blocks on datafile 11:

DBVERIFY is able to find them easily.

Just let’s try to have a backup of tablespace MARCOV.

It fails, but as you can see it stops at the first corrupt block, logging just that single one.

To check and have a completed list of corrupt blocks you should issue a backup validate command:

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.

Here is the syntax if you want to recover all of them using a single command:

When the recover finishes the V$DATABASE_BLOCK_CORRUPTION view doesn’t contain any rows:

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:

Let’s fill the V$DATABASE_BLOCK_CORRUPTION view using the backup validate command on the two tablespaces:

Here is the list of corrupt blocks on different datafiles:

You can use the following command to let RMAN try to recover all the blocks listed into the V$DATABASE_BLOCK_CORRUPTION view.


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

About The Author

Leave a Reply

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