- Dealing with Database Block Corruption in Oracle
- Use DB_VERIFY
- Using Oracle Checksum Facilities
- Salvaging Data from a Corrupt Oracle Database
Salvaging Data from a Corrupt Oracle Database
An Oracle database can become corrupt for various reasons, such as these:
-
Bad hardware
-
Operating system bugs
-
I/O or caching problems
-
Unsupported disk repair utilities running
-
Memory problems
-
Oracle bugs
-
A computer virus
Database corruption frequently results from problems with the hardware, so you should first resolve any hardware problems or reported operating system errors. When all the non-Oracle problems are resolved, you can embark on the adventure of recovering the data from the corrupt database. Follow these steps to recover a corrupt database:
-
Determine the extent of the damage. The information in the alert log, trace files, and the complete error message(s) reported by Oracle can provide enough information to determine the extent of database damage. Suppose that the error message indicates that the damage is done to file# (F) and block# (B).
-
Connect to Server Manager as internal.
-
Determine the file identified as corrupt:
Svrmgr> SELECT name FROM v$datafile WHERE file# = F;
-
Determine the damaged object:
Svrmgr> SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = F AND B BETWEEN block_id AND block_id + blocks - 1;
-
Perform the recovery. The recovery approach depends on the damaged segment, as determined by the query in step 4:
-
For rollback segments or data dictionary corruption, consult Oracle support because this involves using several undocumented parameters that can potentially make the system unrecoverable.
-
For index segments, determine the table that the index belongs to, as follows:
Svrgmgr> SELECT table_owner, table_name FROM dba_indexes WHERE index_name = 'segment_name';
-
For cluster segments, determine the table associated with the cluster, as follows:
Svrmgr> SELECT owner, table_name FROM dba_tables WHERE cluster_name = 'segment_name';
-
For user tables, note the name of the table and its owner.
-
Make sure that the problem isn't intermittent by running the ANALYZE command on the segment at least twice. At the Server Manager prompt, use the following for a table:
-
If a particular hardware or controller is identified as bad, relocate the files to a good disk:
Svrmgr> analyze table owner.tablename validate structure cascade;
Use this command for an index:
Svrmgr> analyze table owner.tablename validate structure cascade;
Use this command for a cluster:
<>Svrmgr> analyze cluster owner.clustername validatestructure cascade;
-
For a database in archivelog mode, move the corrupt datafile offline, and then restore it from backup onto a good disk. Recover the datafile, and then put it back online. The file can now be used.
-
For a database in noarchivelog mode, move the corrupt datafile offline, and then restore it from backup onto a good disk. Put the datafile back online; the file can now be used.
-
Perform the analysis on the object again to make sure that it's no longer corrupt.
At this point, data from the damaged blocks can be salvaged by using several techniques:
-
You can perform media recovery to recover the database to a state before the corruption.
-
You can drop and re-create the object (table or index) by using a valid export.
-
If you know the file number and the block number indicating the corruption, you can salvage the data in the corrupt table by selecting around the bad blocks.
-
Set event 10231 in the init.ora file to cause Oracle to skip software- and media-corrupted blocks when performing full table scans:
Event="10231 trace name context forever, level 10"
Set event 10233 in the init.ora file to cause Oracle to skip software- and media-corrupted blocks when performing index range scans:
Event="10233 trace name context forever, level 10"
TIP
Oracle Support Services has access to several tools, such as Data Unloader (DUL) and BBED (Block Editor), that you can use to extract data from bad blocks. These tools are expensive, though, and there's no guarantee that all the data can be salvaged.