Oracle RMAN Point-in-Time Recovery (PITR) & Media Corruption Repair Runbook

Executive Summary: Media Recovery & Corruption Defense in Enterprise Oracle

hen an enterprise Oracle database suffers accidental table truncation, logical application corruption, physical storage block corruption, or media failure, DBAs must execute precise recovery procedures to restore database integrity while minimizing downtime.

While routine backups protect against complete server loss, production recovery scenarios typically fall into two critical categories:

1. Point-in-Time Recovery (PITR): Rolling back the database or specific tablespace to a precise SCN or timestamp prior to a catastrophic logical event.
2. Block Corruption Repair: Locating and repairing isolated corrupted database blocks (ORA-01578) online without shutting down the database.

This runbook details production procedures for executing RMAN Database Point-in-Time Recovery (DBPITR), online block media recovery using RMAN RECOVER BLOCK, and diagnosing corruption using V$DATABASE_BLOCK_CORRUPTION per Oracle Database Backup and Recovery User’s Guide.

🏗️ Architecture & Media Recovery Workflow

🔍 Diagnostic Checklist: Identifying Corruption & Recovery Points

1. Query Physical Block Corruption

When an application encounters ORA-01578: Oracle data block corrupted, query V$DATABASE_BLOCK_CORRUPTION to determine affected file numbers, block IDs, and corruption types (ALL ZERO, FRACTURED, CHECKSUM, CORRUPT, LOGICAL).

-- Diagnostic 1: Identify All Known Corrupted Data Blocks
SELECT 
    file#, 
    block#, 
    blocks, 
    corruption_change#, 
    corruption_type 
FROM v$database_block_corruption 
ORDER BY file#, block#;

2. Map Corrupted Block to Database Segment

Identify the specific table or index owning the corrupted block to evaluate business impact:

-- Diagnostic 2: Map Corrupted File and Block to Object Name
SELECT 
    owner, 
    segment_name, 
    segment_type, 
    partition_name 
FROM dba_extents 
WHERE file_id = &file_number 
  AND &corrupt_block_id BETWEEN block_id AND (block_id + blocks - 1);

3. Determine Target SCN for Point-in-Time Recovery

For logical corruption (e.g. accidental DROP TABLE or batch corruption), identify the exact SCN or timestamp prior to the event using V$LOG_HISTORY or Flashback Query:

-- Diagnostic 3: Find Target SCN Prior to Corruption Event
SELECT first_change#, next_change#, first_time 
FROM v$log_history 
WHERE first_time >= TO_DATE('2026-08-03 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY first_time ASC;

🚀 Step-by-Step Recovery Runbooks

Scenario A: Online RMAN Block Media Recovery (Zero Downtime)

When isolated blocks are corrupted (ORA-01578), perform Block Media Recovery using RMAN. Unlike full datafile restores, block recovery restores only the damaged blocks online while the database remains open and accessible to application users.

# Connect to RMAN Target Database
rman target /

# RMAN Step 1: Validate Database to Populate V$DATABASE_BLOCK_CORRUPTION
RMAN> VALIDATE DATABASE;

# RMAN Step 2: Recover Specific Corrupted Datafile Blocks Online
RMAN> RECOVER DATAFILE 4 BLOCK 128, 129, 130;

# RMAN Step 3: Alternatively Recover All Blocks Flagged in V$DATABASE_BLOCK_CORRUPTION
RMAN> RECOVER CORRUPTION LIST;

💡 Tip: Block Media Recovery requires existing RMAN full/incremental backups and continuous archived redo logs covering the block’s change history.

Scenario B: Isolating Corrupted Blocks via DBMS_REPAIR

If backups are unavailable or delayed, use the PL/SQL package DBMS_REPAIR to mark corrupted blocks so queries skip them without aborting application execution:

-- Step 1: Create Repair and Orphan Key Tables
EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE', DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACTION);
EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE', DBMS_REPAIR.ORPHAN_TABLE, DBMS_REPAIR.CREATE_ACTION);

-- Step 2: Check Object and Populate Repair Table
DECLARE
  v_num_corrupt INT := 0;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'HR_APP',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE',
    corrupt_count => v_num_corrupt
  );
  DBMS_OUTPUT.PUT_LINE('Corrupt Blocks Found: ' || v_num_corrupt);
END;
/

-- Step 3: Mark Software Corrupt Blocks in Repair Table
DECLARE
  v_num_fixed INT := 0;
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'HR_APP',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE',
    fix_count => v_num_fixed
  );
  DBMS_OUTPUT.PUT_LINE('Blocks Marked Corrupt: ' || v_num_fixed);
END;
/

-- Step 4: Enable Skip Corrupt Blocks to Allow Table Scans to Bypass Corrupted Blocks
EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('HR_APP', 'EMPLOYEES');

Scenario C: Full Database Point-in-Time Recovery (DBPITR)

When catastrophic logical corruption occurs across multiple tables, execute Database Point-in-Time Recovery to roll the entire database back to a clean historical point.

Step 1: Mount the Database

Database Point-in-Time Recovery must be performed in MOUNT state.

# Shut Down Database and Mount Instance
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
EOF

Step 2: Run RMAN RESTORE and RECOVER UNTIL SCN

Execute an RMAN block specifying UNTIL SCN or UNTIL TIME:

# Run RMAN Point-in-Time Recovery Script
rman target / << EOF
RUN {
  # Set Target SCN Boundary
  SET UNTIL SCN 48291054;
  
  # Allocate Parallel I/O Channels
  ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
  ALLOCATE CHANNEL ch02 DEVICE TYPE DISK;
  
  # Restore Physical Datafiles
  RESTORE DATABASE;
  
  # Apply Redo Logs Up to Target SCN
  RECOVER DATABASE;
  
  RELEASE CHANNEL ch01;
  RELEASE CHANNEL ch02;
}
EOF

Step 3: Open Database with RESETLOGS

After recovery completes successfully up to the specified SCN, open the database with RESETLOGS to establish a new log sequence incarnation:

-- Open Database with RESETLOGS
SYS@ORCL> ALTER DATABASE OPEN RESETLOGS;

Step 4: Immediate Post-Recovery Backup

Opening a database with RESETLOGS resets the redo log sequence. Immediately take a new full RMAN backup:

# Take Full Backup Following RESETLOGS
rman target / << EOF
BACKUP DATABASE PLUS ARCHIVELOG;
EOF

🛡️ Preventing Corruption: Enabling Active Data Checksums

To detect silent disk subsystem or SAN block corruption before it impacts application queries, enforce full block checksumming in spfile:

-- Enforce Full Block Checksumming & Lost Write Protection
ALTER SYSTEM SET db_block_checksum = FULL SCOPE=BOTH;
ALTER SYSTEM SET db_lost_write_protect = TYPICAL SCOPE=BOTH;

📚 Official Documentation & Technical References

• Oracle Database Backup and Recovery User’s Guide — Performing RMAN Database Point-in-Time Recovery
• Oracle Database Backup and Recovery Reference — RMAN RECOVER Command Syntax
• ORACLE-BASE — RMAN Database Point-in-Time Recovery (DBPITR) Procedures

Need emergency database recovery assistance or automated backup validation audits across your enterprise databases? Contact our Infrastructure Recovery Team or explore our Enterprise Health Audits.