Oracle RMAN restore and recovery: lost datafile and full database scenarios

#Before anything: check the backups are usable

rman target /
LIST BACKUP SUMMARY;
RESTORE DATABASE VALIDATE;          -- reads backups, restores nothing
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-1' VALIDATE;

#Scenario 1: one datafile lost or corrupt

Error: ORA-01110: data file 7: '/u02/oradata/PROD/users01.dbf' with ORA-01157 or ORA-27041.

The rest of the database stays open:

RMAN> ALTER DATABASE DATAFILE 7 OFFLINE;
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> ALTER DATABASE DATAFILE 7 ONLINE;

Restoring to a different location (the original disk is gone):

RUN {
  SET NEWNAME FOR DATAFILE 7 TO '/u03/oradata/PROD/users01.dbf';
  RESTORE DATAFILE 7;
  SWITCH DATAFILE 7;
  RECOVER DATAFILE 7;
}

#Scenario 2: block corruption

RMAN> VALIDATE DATAFILE 7;
SQL>  SELECT * FROM v$database_block_corruption;
RMAN> RECOVER CORRUPTION LIST;

Only the corrupt blocks are restored. The datafile stays online.

#Scenario 3: whole database (server rebuilt)

RMAN> SET DBID 1234567890;           -- from the backup logs or controlfile autobackup name
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

RECOVER stops with an error when it runs out of archive logs. That's expected after a complete loss; the point it reached is the recovery point.

#Scenario 4: point in time (undo a mistake)

RUN {
  SET UNTIL TIME "TO_DATE('2026-10-22 14:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

Warning

Full database point-in-time recovery takes everything back to 14:00, including other users' work. For one table, prefer Flashback Table/Query (if undo still covers it), RECOVER TABLE (12c+, restores into an auxiliary instance), or a restore to a separate server.

RECOVER TABLE app.orders
  UNTIL TIME "TO_DATE('2026-10-22 14:00:00','YYYY-MM-DD HH24:MI:SS')"
  AUXILIARY DESTINATION '/u05/aux'
  REMAP TABLE app.orders:orders_recovered;

#After a RESETLOGS

Take a new full backup immediately. The old backups belong to the previous incarnation (LIST INCARNATION;).

Tip

Keep CONFIGURE CONTROLFILE AUTOBACKUP ON;. Without a controlfile backup, scenario 3 becomes much harder.