Detect and Repair Corrupted Blocks in Oracle AI Database 26ai

A corruption incident begins with a practical question: can the database recover the original data, or can it only make the undamaged portion accessible? That distinction determines the response. RMAN block media recovery restores damaged blocks from usable copies and applies redo. The DBMS_REPAIR package supports a different operation: identifying damage and, when necessary, marking blocks so supported scans can skip them. Skipping a block does not recover its rows.

This lesson follows an incident from its first error through diagnosis, recovery selection, and verification. File 5, block 30123, and HR.EMPLOYEES are illustrative identifiers. Substitute values confirmed in your database. The examples have not been executed against a database, and their output depends on the actual damage, privileges, configuration, and available recovery material.

4. Choose a recovery method that matches the problem

A table containing customer orders should not be dropped simply because an error names one of its blocks. First determine whether a good copy can restore those rows. Likewise, rebuilding an index cannot supply missing base-table records. Document the selected method, expected downtime, recovery point, and verification criteria so the application owner understands what success will mean.

Consider two incidents with the same initial symptom: a report cannot read an order. In the first, the affected block is known, a usable backup exists, and the required redo is accessible. The plan can focus on recovering that block and retesting the report. In the second, several files have unexplained read failures and the storage team is still investigating. Starting a sequence of isolated repairs may obscure the larger problem. The next useful action is to establish a reliable inventory and recovery source.

Keep availability and completeness as separate acceptance criteria. A service might become available quickly while a subset of historical records remains unresolved. That may be acceptable for one application and unacceptable for another. Record who can make that decision, which operations can resume, and how users will recognize incomplete results. A DBA should not silently convert a recovery incident into a permanent data-retention decision.

6. Use DBMS_REPAIR only with a clear salvage plan

When recovery cannot restore the required data, an administrator may consider salvaging accessible rows. Before doing so, preserve the damaged state and available diagnostics, assess supported object types, and obtain the data owner's acceptance of missing records. The following examples describe a nonpartitioned demonstration table. They are separate stages with a decision point between diagnosis and mutation.

Run package administration with the required administrative privileges in the affected container. Create a uniquely named repair table using ADMIN_TABLES; the package creates it in SYS. Here, REPAIR_LESSON4 must not already exist and USERS must be a suitable available tablespace. Do not drop an existing diagnostic table just to reuse an example name.

BEGIN
  DBMS_REPAIR.ADMIN_TABLES(
    table_name => 'REPAIR_LESSON4',
    table_type => DBMS_REPAIR.REPAIR_TABLE,
    action => DBMS_REPAIR.CREATE_ACTION,
    tablespace => 'USERS');
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_corrupt_count BINARY_INTEGER;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    object_type => DBMS_REPAIR.TABLE_OBJECT,
    repair_table_name => 'REPAIR_LESSON4',
    corrupt_count => l_corrupt_count);
  DBMS_OUTPUT.PUT_LINE('Corruptions reported: ' || l_corrupt_count);
END;
/

SELECT object_name, block_id, marked_corrupt
FROM sys.repair_lesson4;

The corrupt_count argument is an output variable, not an optional comment. Review the recorded findings before proceeding. There is no guaranteed count of one: a real check may report different results or fail for reasons requiring further investigation. The DBMS_REPAIR reference documents procedure signatures, restrictions, and privileges.

Separate mutation: mark blocks, then enable skipping if approved

The next operations do not restore the damaged rows. FIX_CORRUPT_BLOCKS uses findings from the repair table, rechecks blocks, and marks remaining corrupt blocks software corrupt. The label printed below deliberately describes marking, rather than claiming that lost employee records have returned.

SET SERVEROUTPUT ON
DECLARE
  l_fix_count BINARY_INTEGER;
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    object_type => DBMS_REPAIR.TABLE_OBJECT,
    repair_table_name => 'REPAIR_LESSON4',
    fix_count => l_fix_count);
  DBMS_OUTPUT.PUT_LINE('Blocks marked software corrupt: ' || l_fix_count);
END;
/

-- Separate decision: allow supported scans to skip marked blocks.
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    object_type => DBMS_REPAIR.TABLE_OBJECT,
    flags => DBMS_REPAIR.SKIP_FLAG);
END;
/

Skipping changes what a successful query means. A result can now omit data from marked blocks. It does not promise that every access path will work: corrupt index root and branch blocks are not skipped like eligible leaf blocks. Treat the salvaged result as incomplete until reconciled. Oracle's corrupted-data administration guide explains the operational tradeoffs.

Copying accessible rows to another table or exporting them with Data Pump does not recreate unreadable rows. Reconstruct missing information from a separately verified source where possible. Review indexes, foreign keys, constraints, triggers, grants, and application relationships when rebuilding objects. A table populated with the remaining rows can still be an incorrect replacement for the original business dataset.

Keep the original diagnostic record alongside the reconstruction record. For each missing group of records, note the source used to replace it, the checks performed, and any uncertainty that remains. If only a historical export is available, distinguish records restored from that export from later changes that still need reconstruction. Do not assume an older export represents the same recovery point as related tables.

Rehearse reconstruction in an isolated environment before switching the application to a replacement object. Use the rehearsal to identify dependencies and establish measurable acceptance checks. Preserve evidence even when the rehearsal succeeds: it explains why a reconstructed dataset is trustworthy and gives the next administrator a way to investigate discrepancies. Removing the skip setting later is also a planned decision; doing so does not reverse lost data or repair the original damaged blocks.

7. Verify recovery at both database and application levels

After RMAN recovery, validate the affected file again and review the corruption inventory. Compare the actual results with the original incident record rather than assuming that a successful return code closes the case:

-- RMAN session
VALIDATE CHECK LOGICAL DATAFILE 5;

-- SQL session with appropriate visibility
SELECT con_id, file#, block#, blocks, corruption_type
FROM v$database_block_corruption
ORDER BY con_id, file#, block#;

Then repeat the application operation that exposed the damage and check representative records and relationships. After salvage, a lower row count is an impact to explain, not a cosmetic difference to hide. Compare totals, key coverage, and related records against trusted evidence from before the incident. Record which data was recovered, reconstructed, omitted, or still uncertain.

For example, suppose an employee table becomes queryable after skipping one damaged block. A payroll report returning without error is insufficient acceptance evidence. Compare employee identifiers with an independently trusted roster, investigate missing departments or manager relationships, and reconcile the payroll period's totals. The application owner should approve the resulting dataset based on those checks, rather than on the fact that SQL now completes.

Finally, investigate the cause and protect the next recovery. Correlate database errors with storage, operating-system, and memory diagnostics. Review checksum and block-checking settings against the platform's support guidance and workload cost. Schedule validation and recovery rehearsals, retain sufficient archived redo, and confirm that backups can actually be restored. Multiple archive destinations from the previous lesson help preserve redo availability, but their existence alone does not establish that every required log is usable.

The incident record should include the affected container, files and blocks, original errors, selected recovery method, commands actually executed, verification results, and any accepted data loss. This turns a one-time repair into a repeatable operational procedure. The next lesson introduces Oracle LogMiner and the information available when examining redo activity.