Being a DBA we all have good knowledge on feature of its database.   As we know that Oracle has introduced a new feature in 11g which can be used to get assistance in case of loss or corruption of datafiles, redo log files or controlfiles. This tool is called data recovery advisor and is accessible from the command line as well as from the GUI. Today we are going to have look on those steps which are use to to recover contiguous corrupted data blocks using ‘recover datafile’ RMAN syntax.

Naturally, you should consider this kind of recovery when you have few corrupt blocks in a large datafile otherwise you should consider another familiar kind of recovery.
To simulate a recover of corrupt blocks I begin to create first a new tablespace formed by one small datafile, a user, a table and some rows:

Let’s update the statistics on this new table…

… and let’s see how many blocks this segment has and how many are used:

From a sqlplus session I want to dump some blocks of the new datafile using the following command:

and few of them are reported here (I have omitted several lines):

Running DBVERIFY on the datafile no error or corrupt block is detected:

Using the following command, dd resets two blocks of the datafile starting from the fourth.

Let’s dump again the same few blocks of the datafile …

… and compare them with the previous blocks. Blocks marked as (11/4) and (11/5) are totally reset and currently identified as (0/0):

Now DBVERIFY is able to find two corrupt blocks on the datafile:

During a backup if RMAN detects corrupt blocks it terminates the command: the default threshold of total number of physical and logical corruptions permitted on a file is zero, so no corrupt blocks are tolerated.
MAXCORRUPT is the setting you can use to modify this behavior and tolerate more than one corrupt block. Have a look at the following example, considering that I have two corrupt blocks. When I specify 1 as maxcorrupt setting my backup still fails:

When I specify to tolerate at most two corrupt blocks the backup command is able to complete successfully (backup piece: o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp). It should be clear anyway that I was able to complete a backup including a datafile containing two corrupt blocks!!!

After RMAN detects these kind of problems it writes which blocks in a datafile were marked corrupt on the V$DATABASE_BLOCK_CORRUPTION view and, until these corrupt blocks are not repaired, the rows remain in the view. Datafile 11 seems to have 2 corrupt blocks starting from block number 4:

On the alert log you can find the following errors:

To perform a recovery of these blocks the database has to be in ARCHIVELOG mode, could be either in MOUNT or OPEN mode and it is not necessary to put the affected datafiles offline. Let’s try to recover first one block, specifying the following command:

As you can see RMAN is able to look in a older backup untill it find a useful backup piece. The first backup piece used by RMAN was indeed o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp, containing the same corrupt blocks because I previously used the set maxcorrupt option. Only one block was recovered so the V$DATABASE_BLOCK_CORRUPTION view is still populated, but with a different information. Only one block seems to be corrupt on datafile 11:

Let’s check again the dump of the datafile.

The block number (11/4) now contains useful and valid information, whereas block number (11/5) is still corrupt and identified by (0/0):

Let’s try to recover the block number 5:

Again RMAN “failover to previous backup”, it is able to recover block number 5 using the next available backup piece (o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp) and the V$DATABASE_BLOCK_CORRUPTION is finally empty.

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.