How to Recover an Oracle Database with RMAN

How to Recover an Oracle Database with RMAN

RMAN recovery ranges from a simple datafile restore to a complete point-in-time database recovery. Understanding which scenario applies — and whether you can do it online — is critical for minimizing downtime. This guide covers every common recovery scenario with step-by-step commands.

Recovery Scenarios Overview

Scenario	Downtime?	Command
Non-critical datafile offline	No	RMAN online recovery
SYSTEM/UNDO datafile lost	Yes (mount mode)	Complete recovery
Point-in-time recovery	Yes	RECOVER DATABASE UNTIL
Tablespace recovery	Partial (TS offline)	RECOVER TABLESPACE
Block-level corruption	No	RECOVER … BLOCK
Loss of control file	Yes (mount mode)	Restore control file

Prerequisites

Before any recovery, establish what you have:

-- Check which files are missing or offline
SELECT file#, name, status
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM');

-- Check archived log availability
RMAN> LIST ARCHIVELOG ALL;

-- Check available backups
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'SYSDATE-7';

-- Check current SCN
SELECT current_scn, resetlogs_time FROM v$database;

Step 1: Complete Recovery (No Data Loss)

Use when a datafile is lost but all archived logs are available. If the lost file is SYSTEM or an UNDO datafile, the database must be in mount mode.

Recovering a Non-Critical Datafile (Online)

-- Take the datafile offline (database stays open)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' OFFLINE;

-- Or if the file is already inaccessible, check alert log for ORA-01157

-- Connect to RMAN and restore/recover
RMAN> RUN {
  RESTORE DATAFILE '/u01/oradata/ORCL/app_data01.dbf';
  RECOVER DATAFILE '/u01/oradata/ORCL/app_data01.dbf';
}

-- Bring the datafile back online
RMAN> ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' ONLINE;

-- Verify the database is healthy
RMAN> SELECT open_mode FROM v$database;

Recovering SYSTEM or UNDO Datafile (Requires Mount Mode)

# Shut down the database if it is not already down
sqlplus / as sysdba
SQL> SHUTDOWN ABORT
SQL> STARTUP MOUNT
SQL> EXIT

rman target /

RMAN> RUN {
  -- Restore the missing/corrupted datafile(s)
  RESTORE DATABASE;
  -- Apply all archived redo logs to bring fully current
  RECOVER DATABASE;
}

-- Open the database
RMAN> ALTER DATABASE OPEN;

Step 2: Point-in-Time Recovery (PITR)

Use when you need to undo logical corruption — an accidental table drop, incorrect bulk DML, or application error.

-- Identify the target SCN or time before the error
-- Check the alert log or AWR to estimate when the error occurred

-- Put database in mount mode
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

-- Recover to a specific time
RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}
-- Open with RESETLOGS — required after UNTIL recovery
RMAN> ALTER DATABASE OPEN RESETLOGS;

-- Recover to a specific SCN
RMAN> RUN {
  SET UNTIL SCN 12345678;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

-- Recover to a specific restore point or log sequence
RMAN> RUN {
  SET UNTIL SEQUENCE 1234 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

Step 3: Tablespace Point-in-Time Recovery (TSPITR)

TSPITR recovers one tablespace to a past point in time while the rest of the database stays current. Use for logical errors confined to a single tablespace.

-- Take the target tablespace offline
SQL> ALTER TABLESPACE app_data OFFLINE IMMEDIATE;

-- Recover just the tablespace to a specific time
RMAN> RECOVER TABLESPACE app_data
        UNTIL TIME "TO_DATE('2024-03-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS')"
        AUXILIARY DESTINATION '/u03/tspitr_work';

-- Bring the tablespace back online
SQL> ALTER TABLESPACE app_data ONLINE;

Note: TSPITR creates an auxiliary instance internally. The AUXILIARY DESTINATION path must have sufficient space (roughly equal to the tablespace size).

Step 4: Individual Block Recovery

Block media recovery (BMR) recovers only specific corrupt blocks — the rest of the datafile stays accessible.

-- Find corrupt blocks from the alert log or:
SELECT owner, segment_name, segment_type,
       file_id, block_id
FROM dba_extents
WHERE file_id = 5
  AND block_id BETWEEN 12000 AND 12010;

-- Recover specific corrupt blocks (database stays online)
RMAN> RECOVER DATAFILE 5 BLOCK 12001, 12002, 12003;

-- Recover all corrupt blocks found by RMAN's backup validation
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
-- RMAN reports corrupt blocks in V$DATABASE_BLOCK_CORRUPTION

RMAN> RECOVER CORRUPTION LIST;

Step 5: Restore and Recover Control File

If all control files are lost and no autobackup is available, use the backup control file.

# Start the database in NOMOUNT state
sqlplus / as sysdba
SQL> STARTUP NOMOUNT
SQL> EXIT

rman target /

-- If control file autobackup is configured and reachable
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- If you know the autobackup location
RMAN> RESTORE CONTROLFILE
        FROM '/u02/backup/ORCL/ctrl_C-1234567890-20240315-00';

-- Mount the database after restoring control file
RMAN> ALTER DATABASE MOUNT;

-- Recover and open with RESETLOGS
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Step 6: Recover from Flash Recovery Area Loss

If the FRA is lost (disk failure), RMAN can recover from primary backup copies.

-- Point RMAN to an alternative backup location
RMAN> CATALOG START WITH '/u03/backup_copy/';

-- Crosscheck to find which backups are available
RMAN> CROSSCHECK BACKUP;

-- Proceed with restore using the cataloged backups
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Advanced Examples

Recover a Table Using Automated RMAN Table Recovery (12c+)

Oracle 12c introduced the ability to recover a single table from an RMAN backup without restoring the whole database.

-- Recover a specific table to a point in time (creates the table in a staging schema)
RMAN> RECOVER TABLE app_schema.orders
        UNTIL TIME "TO_DATE('2024-03-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS')"
        AUXILIARY DESTINATION '/u03/recover_work'
        REMAP TABLE app_schema.orders:orders_recovered;

-- The table is re-created as ORDERS_RECOVERED in APP_SCHEMA
-- Verify the data, then rename or merge as needed

Test Recovery Without Restoring to Production

-- Restore to a different location for testing (does not touch production)
RMAN> RUN {
  SET NEWNAME FOR DATABASE TO '/u03/test_restore/%b';
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  RECOVER DATABASE;
}

Common Mistakes and Pitfalls

Opening without RESETLOGS after PITR — After any RECOVER DATABASE UNTIL ... operation, you must open with RESETLOGS. Forgetting this causes ORA-01194.

Not having archived logs that cover the recovery window — RMAN cannot recover past a gap in the archived log sequence. Confirm log availability with LIST ARCHIVELOG ALL before starting.

TSPITR on a tablespace with foreign key dependencies — If another tablespace has tables with foreign keys pointing into the recovered tablespace, TSPITR may fail or produce referential integrity violations. Check dependencies first.

Recovering to a different server without updating the control file paths — After a restore to a different server, use CATALOG and SWITCH DATAFILE ALL to update file locations in the control file.

Not taking a backup after OPEN RESETLOGS — After opening with RESETLOGS, previous backups cannot be used to recover past the RESETLOGS SCN. Take a full backup immediately.

Verification Queries

-- Verify the database opened successfully
SELECT name, open_mode, log_mode, resetlogs_time
FROM v$database;

-- Check all datafiles are online and consistent
SELECT file#, name, status, fuzzy, checkpoint_change#
FROM v$datafile_header
ORDER BY file#;

-- Verify no invalid objects after recovery
SELECT COUNT(*) AS invalid_objects
FROM dba_objects
WHERE status = 'INVALID';

-- Check alert log for post-recovery errors
-- adrci: show alert -tail 50

-- Verify data integrity after point-in-time recovery
-- Run application-level sanity checks:
-- SELECT COUNT(*) FROM app_schema.orders WHERE order_date > SYSDATE - 1;

Related Topics

• How to Create an RMAN Backup - Create the backups you need for recovery
• RMAN Documentation - Complete RMAN reference
• RMAN Commands Cheat Sheet - Quick command reference
• Oracle Errors: ORA-01578 - Data block corrupted