Dealing with Database Block Corruption in Oracle
Oracle expert Megh Thakkar discusses how an Oracle DBA can deal with data block corruptions.
Each Oracle data block is written in a proprietary binary format. Before an Oracle data block is used, Oracle checks it for possible block corruption. A block corruption is considered to exist if the format of the data block doesn't conform to its format. This article discusses how an Oracle DBA can deal with data block corruptions.
Checking for data block corruption is performed at the cache and other higher layers of the Oracle code. Information checked at the cache layer includes the following:
Block sequence number
Data block address
If an inconsistency in block format is identified at the cache layer, the block is considered to be media corrupt, whereas an inconsistency identified at a higher layer of Oracle code marks the block as being software corrupt.
Information in the corrupt block is more or less lost; you will have to re-create it by using some data backup or export. Oracle has several toolssuch as the Data Unloader (DUL) utility, which you can use to extract the data out of bad blocksbut typically, using these techniques is very expensive. You have to weigh the cost of using those tools (which aren't guaranteed to be completely successful) and the cost of re-creating the lost information.
Usually you see an error message such as ORA-1578 or ORA-600 when Oracle encounters corrupt blocks. You can use several techniques to determine whether the database is corrupt and also to understand the nature and extent of the corruption.
By analyzing the table structure and its associated objects, you can perform a detailed check of data blocks to identify block corruptions:
Analyze table table_name validate structure cascade;
Data blocks are checked at the cache and higher levels. Index blocks are also checked, and the one-to-one association between the table data and its index rows is verified.