Repairing Corrupted Blocks in Oracle: Difference between Oracle 11G and Oracle 19c

1. Detecting Data Block Corruption:
   • Oracle 11g:
     • DBVerify Utility (DBV):
       • DBV is used as an external utility to check for corruption. You need to run this tool manually against data files.
       • Example command:
         

         dbv file=<datafile_path> blocksize=<block_size>
         

     • ANALYZE Command:
       • You can manually run `ANALYZE` commands to check for corruption at the table or index level.
         

         	ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;
         	

     • Automatic Database Diagnostic Monitor (ADDM) and Alert Logs:
       • Oracle 11g monitors the system, but administrators must regularly check the alert logs or wait for ADDM to detect corruption issues.
   • Oracle 19c:
     • Automatic Diagnostic Framework (ADF):
       • Oracle 19c integrates more automation into corruption detection. The ADF proactively monitors the database for signs of corruption and reports these via `Automatic Health Monitoring (AHM)`.
     • Real-Time Corruption Detection:
       • Oracle 19c enhances the proactive checking of data corruption, using features like real-time block corruption detection in *Data Guard* environments and Automatic Storage Management (ASM).
     • DBMS_REPAIR Package:
       • Oracle 19c improves the functionality of the `DBMS_REPAIR` package, making it easier to log and fix corruption issues.
     • Data Recovery Advisor (DRA):
       • DRA, available in 11g but enhanced in 19c, helps detect data block corruptions by running integrity checks and generating a repair plan.
2. Correcting Data Block Corruption:
   • Oracle 11g:
     • RMAN Block Media Recovery:
       • To fix corrupt blocks, Oracle 11g allows you to use RMAN (Recovery Manager) for block-level recovery. This requires identifying the block number and running RMAN commands manually.
         

         RMAN> BLOCKRECOVER DATAFILE datafile_number BLOCK block_number;
         

     • DBMS_REPAIR Package:
       • The `DBMS_REPAIR` package allows administrators to mark blocks as corrupt and skip over them to prevent further issues during queries. This must be done manually
     • Export/Import (Data Pump):
       • If corruption affects tables, exporting and re-importing data using Oracle Data Pump can help to reconstruct damaged objects, though this is more time-consuming.
   • Oracle 19c:
     • Automatic RMAN Block Media Recovery:
       • Oracle 19c improves the block media recovery process with automatic repair, making it more seamless and efficient. You can configure RMAN to automatically detect and recover corrupted blocks.
     • Integration with ASM and Cloud Storage:
       • Oracle 19c’s deeper integration with ASM and cloud storage systems like Oracle Cloud Infrastructure allows more seamless recovery. Automatic storage management can often correct corruption without administrator intervention.
     • Automatic Data Guard Features:
       • In Data Guard configurations, Oracle 19c can automatically detect and repair corrupted blocks using the standby database without user input.
     • Data Recovery Advisor:
       • In 19c, DRA is more integrated and can both detect and automatically fix corruption issues based on repair plans generated by RMAN.

• Oracle 19c automates many of the tasks that are manual in Oracle 11g, such as real-time corruption detection and automatic block repair.
• Oracle 19c has tighter integration with cloud storage, ASM, and Data Guard, which allows for more efficient detection and correction of corruption, leveraging cloud infrastructure and redundant systems.
• Oracle 19c reduces the need for manual interaction with DBV and RMAN by proactively handling corruption issues and providing enhanced automation through DRA and RMAN auto-recovery features.

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

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;
/

num fix: 1

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE

1. Enabling Server Output:`SET SERVEROUTPUT ON` allows the PL/SQL block to output messages to the console using `DBMS_OUTPUT`.
2. DECLARE Section:A variable `num_fix` of type `INT` is declared. This will hold the count of blocks that were fixed.
3. BEGIN Section:
   • `DBMS_REPAIR.FIX_CORRUPT_BLOCKS` is called with the following parameters:\
     • `SCHEMA_NAME`: Specifies the schema of the object (in this case, 'SCOTT').
     • `OBJECT_NAME`: The name of the table ('DEPT') with corrupt blocks.
     • `OBJECT_TYPE`: Specifies the object type, which is a table (`dbms_repair.table_object`).
     • `REPAIR_TABLE_NAME`: The name of the repair table where information about the repairs will be recorded (`REPAIR_TABLE`)
     • `FIX_COUNT`: This is an `OUT` parameter that will store the number of blocks fixed, and the value is assigned to `num_fix`.
4. Output:The final line prints the number of fixed blocks to the console using `DBMS_OUTPUT.PUT_LINE`.

• This script calls `DBMS_REPAIR.FIX_CORRUPT_BLOCKS` to attempt to fix any corrupt blocks in the `scott.dept` table.
• The `FIX_COUNT` parameter tracks how many blocks were fixed during the operation.
• If blocks are successfully fixed, the number will be printed to the console.

• Ensure that the `REPAIR_TABLE` has been created beforehand using `DBMS_REPAIR.CREATE_REPAIR_TABLE` if it doesn’t already exist.
• This method only fixes logical corruptions (software-level), and if the corruption is physical (hardware), this may not resolve the issue.
• Always back up your data before running block repair procedures.

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