Dealing with Oracle Database Block Corruption in 11g

When we look at physical damage of datafiles, the effects are not always so drastic that the file is completely destroyed. Sometimes, only small parts of the file are damaged resp. corrupted. It may very well not even noticed for a while. This posting is designed to show how to a) detect block corruption and b) recover from it. First requirement for a demonstration is to actually get a block corruption. The following little script damages one block of which the table emp from user scott consists:

Whenever we get these kind of error messages, we need to check all the blocks. Typically, we get them during an RMAN backup, but I like to defer that a little to show an 11g New Feature before. Checking all blocks now:

We have already a couple of 11g New Features here: The syntax has changed from backup validate (since 9i) to just validate (11g) – probably to make clear that this does not perform a backup but a check of corrupted blocks instead. Before 11g, the command did not show the verbose list of checked resp. corrupted blocks like we see above. The addition check logical will also check for logical block corruption, which is not done by default. Checking all the blocks here is more efficient than doing an immediate recovery of the one block mentioned in the error message above. There may be many more not spotted yet. Same is true for an ordinary backup that would interrupt at the first spotted corrupted block as we will see later on. The validate command populated the view v$database_block_corruption, that is now internally read by RMAN in order to repair all the found corrupted blocks. The next 11g New Feature here is: It will take the block out of the Flashback Logs, if present there!

I was so bold that I did not even take a backup before – to make sure this new feature must be used:

RMAN> list backup;
specification does not match any backup in the repository

I’m going to take a backup now, but before that, I trigger again block corruption. So we will see that RMAN stops at the first noticed corrupted block. No Third-Party-Tool would recognize the block corruption, BTW, so we have another reason to actually use RMAN here. If we say backup check logical database instead of just backup database, RMAN will also check for logical block corruption during the backup.

Again the same sequence as above validate check logical database & blockrecover corruption list will solve the problem. During the whole process, the users tablespace remains online and usable, except the emp table of scott.

Conclusion: We have a powerful tool with RMAN to spot and repair corrupted blocks by using intact versions of the corrupted blocks from backup (since 9i already) or even from Flashback Logs (since 11g) – which is probably faster – while keeping up the availability of the affected tablespace.

As you’ll be aware another great way of dealing with block corruption is to have active dataguard running, then the block corruption can be repaired from the standby, without the user seeing the error.

Savaş,
of course, if the Datafile Header is damaged, this is no “ordinary” Block Corruption and cannot be resolved with Blockrecovery. A restore of the whole datafile from backup is needed then, followed by a complete recovery.
Yes, dbv is another way to detect corrupted blocks – also a legacy export with the conventional path or analyze validate structure would do that. But I consider these as minor options compared to the usage of RMAN to detect corrupted blocks – which is already in so far attractive as RMAN will be used for backups most likely anyway.

We performed block recovery using RMAN blockrecover corruption list;
Recovery completed successfully. Blocks disappeared from v$database_block_corruption
But after first backup they returned back to v$database_block_corruption as :

Very Informative Article!! In case, these steps get failed to fix oracle database block corruption then you should use third party Oracle database recovery software i.e. Stellar Phoenix Oracle Recovery to fix this issue. These software repairs corrupt DBF file as well as all oracle database objects.

Saikat, generally, RMAN should also be able to identify logical corruption with the VALIDATE CHECK LOGICAL DATABASE command from above. If RMAN cannot detect respectively repair it, you may try dbverify for detection and a complete recovery of the affected datafile afterwards. And you should of course call for Oracle Support :)

Hi, Thanks for the reply, actually recently we faced such issue where oracle support analyst mentioned RMAN does not identify inter-block inconsistency…moreoevr, the support analyst said that logical corruptions can’t be repaired using backup/restore/recovery..Only way is to use dbms_repair package…and in that case we will loose the data….Is there any other way to repair logical corruption without loosing data?