| 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:
- Detect and report corruptions
- Evaluate the cost and benefits of using
- Make objects usable
- 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;
num_fix := 0;
SCHEMA_NAME => 'SCOTT',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
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
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
CHECK_OBJECT: Detects and reports corruptions within a table or index
FIX_CORRUPT_BLOCKS: Marks as corrupt blocks that were previously identified by the check_object procedure
DUMP_ORPHAN_KEYS: Reports index entries that point to rows within corrupt data blocks
REBUILD_FREELISTS: Rebuilds the free lists of an object
SKIP_CORRUPT_BLOCKS: When used, ignores the blocks that are marked corrupt during table and index scans
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.