Handling Block Corruptions in Oracle.

Causes of Block Corruption:

- Bad IO hardware / firmware

- OS problems

- Oracle problems

- Recovering through “UNRECOVERABLE” or “NOLOGGING” database actions

Tackle corruption problems:

1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty.

2) Replace or move away from any faulty or suspect hardware.

3) Determine which database objects are affected.

4) Choose the most appropriate database recovery / data salvage option.

(1)Determine the Extent of the Corruption Problem

Whenever a corruption error occurs note down the FULL error messages and look in the instance’s alert log and trace files for any associated errors. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem.

(2) Replace or Move Away from Suspect Hardware

The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option.Here we can move datafile to another volume.
(3) Which Objects are Affected ?

It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption may be on objects which can easily be re-created.

Options:

If the OWNER is “SYS” then contact Oracle support with all details.

For non dictionary tables possible options include:

i)Recovery

OR

ii)Salvage data from the table (or partition) THEN Recreate the table (or partition)

OR

iii)Leave the corruption in place (eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)

(4) Choosing a Recovery Option

-Is any Recovery Required ?

-Is Complete Recovery an option ?

-Can the object be Dropped or Re-created without needing to extract any data from the object itself ?

-Is it required to salvage data before recreating the object ?

-Is it acceptable to leave the corruption in place for the moment?