How to repair Oracle block corruption using DBMS_REPAIR utility

Oracle block corruption happens very rarely but when it happens you may lose all your data. Oracle block corruption is nothing but just some incorrect information in the Oracle data file. Oracle will never be aware of this corruption unless you establish a proper block checking configuration system. To detect & repair block corruption, Oracle provides different methods. One method is to simply drop and recreate the object but this method may not work in all situations. Another method that mostly works is the DBMS_REPAIR package.

Detect corruption: DBMS_REPAIR package is used to detect & repair block corruption in Oracle tables and indexes. It provides various options to detect corruption. Here they are:

1. DBMS_REPAIR: CHECK_OBJECT: This option check block corruption for a particular table or index. It not only detects block corruption but also suggests repair options.
2. DB_VERIFY: It detects block corruption for a database which is in offline mode.
3. ANALYZE TABLE: This command is used with VALIDATE STRUCTURE option to analyze the structure of index, table or cluster. It confirm with message if the structure of object is ok and show error message if corruption is detected.
4. DB_BLOCK_CHECKING: If you set DB_BLOCK_CHECKING initialization parameter to TRUE then it will automatically detect block corruption. It perform corruption check whenever there is a change in block.  

Ignoring corruption: DBMS _REPAIR provides two options that are used to make object usable by ignoring the corruption during scanning of table or index. One option is FIX_CORRUPT_BLOCKS which is used to mark those blocks as corrupted that can cause data loss. Another option is SKIP_CORRUPT_BLOCKS which is used to skip blocks that are marked as corrupted by FIX_CORRUPT_BLOCKS option.

Fix corruption: To repair corruption DUMP_ORPHAN_KEYS procedure is used. It identifies index entries that points to corrupt data block. This information of index entries is stored in orphan key table that also store the row id and key of corrupt record. After this use ALTER INDEX REBUILD ONLINE statement to rebuild the indexes.