Backup Recovery   «Prev  Next»
Lesson 4 Repairing corrupted blocks
Objective Detect and repair a corrupted block.

Repairing Corrupted Blocks in Oracle

Oracle provides different methods for detecting and correcting data block corruption. One method is to drop and re-create the object after having detected the corruption. However, this is not always possible or desirable. If data block corruption is limited to a subset of rows, another option is to rebuild the table by selecting all data except for the corrupt rows.
Another way to manage data block corruption is to use the DBMS_REPAIR package. You can use DBMS_REPAIR to detect and repair corrupt blocks within tables and indexes. This approach allows you to address corruptions where possible, and also continue to use the objects while you attempt to rebuild or repair them. DBMS_REPAIR uses the following approach to address corruptions:
  1. Detect and report corruptions
  2. Evaluate the cost and benefits of using DBMS_REPAIR
  3. Make objects usable
  4. Repair corruptions and rebuild the lost data

Oracle Backup Recovery

Example: Fixing Corrupt Blocks

Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECT procedure. Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp. This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.
SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME=> 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

SQL*Plus outputs the following line:
num fix: 1

The following query confirms that the repair was done.
SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE

Lost Data and Logical Inconsistencies

Depending upon the nature of the repair, data may be lost and logical inconsistencies may be introduced. The Database Administrator (DBA) must determine whether the potential loss warrants the use of this feature. To assist the DBA, this package also provides a report mechanism that helps determine whether to use this feature or not.
The following procedures are available within the DBMS_REPAIR package:
  1. CHECK_OBJECT: Detects and reports corruptions within a table or index
  2. FIX_CORRUPT_BLOCKS: Marks as corrupt blocks that were previously identified by the check_object procedure
  3. DUMP_ORPHAN_KEYS: Reports index entries that point to rows within corrupt data blocks
  4. REBUILD_FREELISTS: Rebuilds the free lists of an object
  5. SKIP_CORRUPT_BLOCKS: When used, ignores the blocks that are marked corrupt during table and index scans
  6. ADMIN_TABLES: Provides administrative functions (create, drop, purge) for DBMS_REPAIR repair and orphan key tables
The next lesson is about the functionality of LogMiner.