The worst day of an Oracle DBA's career is a restore they've never rehearsed. A datafile is gone, or a DELETE ran without a WHERE, or a storage array quietly corrupted a handful of blocks — and now the only thing between you and a résumé-updating outage is a backup you hope works and a procedure you think you remember.

This is the runbook for that day. It rests on one distinction most people fumble under pressure —
restore is not recover — and it maps each failure to the specific commands that fix it, from a single
corrupt block to a whole database rewound to the second before someone's mistake. Everything targets
Oracle 19c, with notes for 23ai/26ai.

The short version. RESTORE copies datafiles back from your backups; RECOVER rolls them forward by applying redo. Most recoveries are RESTORE DATABASE then RECOVER DATABASE.
To rewind past a human error, use point-in-time recovery: SET UNTIL (time or SCN) at the top of a RUN block, then restore, recover, and OPEN RESETLOGS. For a few bad blocks, block media recovery fixes just those without touching the datafile. Lost the controlfile? NOMOUNT, SET DBID, RESTORE CONTROLFILE FROM AUTOBACKUP. And the rule under all of it: an untested backup is a hope, not a recovery plan.

Restore vs recover — the distinction that saves you

Two verbs, two completely different actions, and confusing them is how people make a bad day worse:

  • RESTORE copies datafiles back from your backups. It rewinds those files to whenever the backup was taken.
  • RECOVER takes those restored files and rolls them forward by applying archived and online redo — either all the way to the present, or to a point in time you choose.

You almost never restore alone; a restore on its own throws away every transaction since the backup. The
normal shape of a recovery is restore, then recover:

RMAN> RESTORE DATABASE;    -- copy datafiles back from the most recent backup
RMAN> RECOVER DATABASE;    -- apply redo to roll them forward to now
RMAN> ALTER DATABASE OPEN;

Enter fullscreen mode Exit fullscreen mode

Hold that pair in your head — everything below is a variation on which files you restore and how far
you recover.

The failure → action map

You don't run the same command for a single corrupt block as for a lost database. Match the failure to
the smallest recovery that fixes it — that's what keeps your outage small:

What failed What you run Who's down
A few corrupt blocks RECOVER ... BLOCK / RECOVER CORRUPTION LIST those blocks only — DB stays open
One datafile / tablespace RESTORE + RECOVER DATAFILE/TABLESPACE (online) that datafile only
Many datafiles / whole DB RESTORE + RECOVER DATABASE (mounted) full outage
A bad DELETE / DROP (logical) Flashback (fast) or point-in-time recovery / RECOVER TABLE varies
Controlfile lost RESTORE CONTROLFILE FROM AUTOBACKUP full outage
SPFILE lost RESTORE SPFILE FROM AUTOBACKUP full outage

One datafile, without taking the database down

The best-case recovery, and the most common in real life: a single datafile or tablespace is lost while
the rest of the database keeps serving. Take just that datafile offline, restore and recover it, bring
it back — users on every other tablespace never notice.

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

Enter fullscreen mode Exit fullscreen mode

(Swap DATAFILE 7 for TABLESPACE users to work at the tablespace level.) This is why restore speed
matters more than most teams think — the typical recovery is one file, not the whole estate.

The whole database: restore then recover

If the database is down — many files lost, or you're rebuilding on fresh hardware — mount it and run the
pair against everything:

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Enter fullscreen mode Exit fullscreen mode

This is complete recovery: every committed transaction, right up to the moment of failure, is back.
No RESETLOGS needed, because you recovered all the way to the present — the redo timeline is unbroken.

Rewinding past a bad DELETE: point-in-time recovery

Complete recovery brings you back to now — which is useless when now already contains the problem: a
DELETE without a WHERE, a bad release, a truncated table that committed an hour ago. For that you need
incomplete recovery — stop applying redo just before the mistake.

Here's the rule that catches people out: SET UNTIL goes at the top of a RUN block, before both
RESTORE and RECOVER. Set it only before RECOVER and your restored files may already carry
timestamps past your target, and RMAN can't rewind them.

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

Enter fullscreen mode Exit fullscreen mode

You can target a time, an exact SCN (SET UNTIL SCN 12345678 — no ambiguity about "which 14:29"),
a log sequence, or a named restore point. When you know the SCN of the damage, use it — it's
precise where a timestamp can be a second off in either direction.

OPEN RESETLOGS is mandatory after any incomplete recovery. It resets the online redo logs and starts
a new database incarnation — a fresh redo timeline branching off the old one. Two consequences worth
internalizing: take a full backup immediately afterward (your old backups now belong to a previous
incarnation), and know that RMAN can still navigate incarnations if you ever need to go back further.

Two faster paths when they apply:

  • Flashback Database (if you enabled it) rewinds the entire database in minutes with no restore at all — FLASHBACK DATABASE TO SCN 12345678;. It's the first thing to reach for on a logical error, and it's exactly why The Oracle HA Decision Tree pairs backups with Flashback as the only real answer to human error.
  • RECOVER TABLE (12c+) restores a single dropped or damaged table from your RMAN backups — Oracle spins up a temporary auxiliary instance behind the scenes, extracts just that table to a point in time, and imports it back. No full-database rewind, no downtime for everyone else over one table.

Just a few bad blocks: block media recovery

When storage corrupts a handful of blocks rather than a whole file, restoring the entire datafile is
wildly disproportionate. Block media recovery repairs only the corrupt blocks — and the datafile
stays online the whole time, so mean-time-to-recovery collapses.

RMAN records corrupt blocks in V$DATABASE_BLOCK_CORRUPTION during backups and VALIDATE. You can then
fix everything on that list in one command:

RMAN> VALIDATE DATABASE;          -- populates V$DATABASE_BLOCK_CORRUPTION
RMAN> RECOVER CORRUPTION LIST;    -- repairs every block RMAN has flagged
-- or a single, known block:
RMAN> RECOVER DATAFILE 7 BLOCK 1234;

Enter fullscreen mode Exit fullscreen mode

Two limits to know going in: block media recovery is an Enterprise Edition feature, and it repairs
physical corruption only — it cannot fix logical block corruption (for that, restore/recover the
object or use Flashback). Catch corruption early by adding CHECK LOGICAL to your validates and
backups: BACKUP VALIDATE CHECK LOGICAL DATABASE; scans for both physical and logical damage before it
becomes a 3am page.

Losing the controlfile or the SPFILE

The controlfile is the map RMAN uses to find everything — lose every copy and you can't even MOUNT. If
you have controlfile autobackup on (you should: CONFIGURE CONTROLFILE AUTOBACKUP ON;), recovery is
mechanical — with one catch. Without a controlfile and without a recovery catalog, RMAN doesn't know
your DBID, and it needs the DBID to locate the autobackup:

RMAN> STARTUP NOMOUNT;
RMAN> SET DBID 320066378;                    -- from your records, not the database
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Enter fullscreen mode Exit fullscreen mode

A lost SPFILE follows the same shape (RESTORE SPFILE FROM AUTOBACKUP from NOMOUNT). The one thing
to do today: write your DBID down somewhere outside the database. It's the piece you can't recover
from the database when the database is precisely what you've lost.

The recovery decision: match the failure to the smallest recovery that fixes it. A corrupt block doesn't need a database restore; a human error doesn't need bare-metal recovery.

flowchart TD
  A{What failed?}
  A -- "A few corrupt blocks" --> B[Block media recovery<br/>RECOVER CORRUPTION LIST<br/>datafile stays online]
  A -- "One datafile / tablespace" --> C[Offline it, RESTORE + RECOVER<br/>the file, bring it online<br/>rest of DB stays up]
  A -- "Whole DB / new hardware" --> D[MOUNT, RESTORE + RECOVER<br/>DATABASE, OPEN<br/>complete recovery]
  A -- "Human error, already committed" --> E{Flashback enabled?}
  E -- Yes --> F[FLASHBACK DATABASE<br/>minutes, no restore]
  E -- No --> G[Point-in-time recovery<br/>SET UNTIL, RESTORE, RECOVER<br/>OPEN RESETLOGS]
  A -- "Controlfile / SPFILE" --> H[NOMOUNT, SET DBID<br/>RESTORE ... FROM AUTOBACKUP]

Enter fullscreen mode Exit fullscreen mode

Prove it before you need it

Everything above assumes the backup you're restoring actually works — and most teams don't find out
whether it does until the worst possible moment. Two RMAN commands close that gap without a full restore:

  • RESTORE DATABASE VALIDATE confirms the backups a restore would need exist and aren't corrupt — without writing a single datafile. Add CHECK LOGICAL to scan for logical corruption too.
  • RESTORE DATABASE PREVIEW reports the exact backups and archived logs RMAN would use, so you can confirm your recovery window is intact before an incident, not during one. 
RMAN> RESTORE DATABASE VALIDATE CHECK LOGICAL;
RMAN> RESTORE DATABASE PREVIEW;

Enter fullscreen mode Exit fullscreen mode

But validation is not a rehearsal. The only real test is an actual restore to a scratch host, on a
schedule. A backup you've never restored is a hope; a restore you ran last month is a plan. If you take
one thing from this post, take that.

What teams get wrong

  • Confusing restore and recover — restoring without recovering (and silently discarding everything since the backup), or assuming "restore" finished the job.
  • SET UNTIL in the wrong place — after RESTORE instead of before it, so the restored files overshoot the target time.
  • Forgetting OPEN RESETLOGS after incomplete recovery — or not realizing it forks a new incarnation, and failing to take a fresh backup immediately after.
  • No controlfile autobackup, or a DBID nobody wrote down — the recovery you can't even begin.
  • Full restore when a smaller tool would do — reaching for RESTORE DATABASE when Flashback (minutes) or RECOVER TABLE (one object) fixes it with a fraction of the downtime.
  • Never testing a restore. The backup that runs green every night and has never once been restored is the one that fails when it finally matters.

The one-paragraph version

Restore copies datafiles back; recover rolls them forward with redo — most recoveries are RESTORE
DATABASE then RECOVER DATABASE, and you OPEN when you've recovered to the present. To rewind past a
mistake, wrap SET UNTIL / RESTORE / RECOVER in a RUN block and OPEN RESETLOGS. Fix a few bad
blocks with RECOVER CORRUPTION LIST while the datafile stays online; recover a lost controlfile from
autobackup after SET DBID in NOMOUNT; and reach for Flashback or RECOVER TABLE before a full
restore when the problem is logical. Then do the part nobody enjoys and everybody needs: test the
restore on a schedule, because an untested backup was never a recovery plan.

FAQ

What is the difference between RESTORE and RECOVER in RMAN?

RESTORE copies datafiles back from your RMAN backups, rewinding them to the time the backup was taken. RECOVER then applies archived and online redo logs to roll those restored files forward — either to the present (complete recovery) or to a chosen point in time (incomplete recovery). A normal recovery is RESTORE DATABASE followed by RECOVER DATABASE; restoring without recovering would discard every transaction since the backup.

How do I recover an Oracle database to a point in time before a mistake?

Use database point-in-time recovery (incomplete recovery). Put a SET UNTIL clause (a time, SCN, log sequence, or restore point) at the top of a RUN block, before both RESTORE DATABASE and RECOVER DATABASE, so the target applies to both. After recovery you must open the database with ALTER DATABASE OPEN RESETLOGS, which starts a new incarnation — take a full backup immediately afterward. If Flashback Database is enabled, FLASHBACK DATABASE TO SCN is usually faster because it needs no restore.

Can I recover just a few corrupt blocks without restoring the whole datafile?

Yes — that is block media recovery, and the datafile stays online the entire time. RMAN records corrupt blocks in V$DATABASE_BLOCK_CORRUPTION during backups and VALIDATE; RECOVER CORRUPTION LIST then repairs everything on that list, or RECOVER DATAFILE n BLOCK m fixes a specific block. It is an Enterprise Edition feature and repairs physical corruption only — it cannot fix logical block corruption.

What do I do if I lose the control file?

If controlfile autobackup is enabled, start the instance in NOMOUNT, set the DBID (SET DBID), run RESTORE CONTROLFILE FROM AUTOBACKUP, then MOUNT, RECOVER DATABASE, and OPEN RESETLOGS. Without a recovery catalog, RMAN needs the DBID to find the autobackup, so record your DBID somewhere outside the database. A lost SPFILE is recovered the same way with RESTORE SPFILE FROM AUTOBACKUP.

Do I have to OPEN RESETLOGS after recovery?

Only after incomplete recovery (point-in-time recovery) or after recovering with a backup control file. Complete recovery to the present does not need RESETLOGS. When you do open with RESETLOGS, Oracle starts a new database incarnation, so you should take a fresh full backup right away because prior backups belong to the earlier incarnation.

How do I test that my RMAN backups actually work?

Use RESTORE DATABASE VALIDATE (add CHECK LOGICAL) to confirm the needed backups exist and are not corrupt without restoring, and RESTORE DATABASE PREVIEW to see exactly which backups and archived logs a recovery would use. But those checks are not a rehearsal — the only real proof is a scheduled, actual restore to a separate scratch host. A backup that has never been restored is a hope, not a recovery plan.

Recovery is the floor under every other high-availability choice — RAC and Data Guard survive hardware
and site failures, but only backups and Flashback survive a bad DELETE. For where each fits, see
The Oracle HA Decision Tree.

Practice this before you need it. The rman/ recovery lab
lets you break a throwaway Oracle Database Free with Docker and recover it for real: delete a datafile
and RESTORE/RECOVER it, corrupt a block and repair just that block with block media recovery,
rewind past a committed DELETE with point-in-time recovery, and prove a restore works with
RESTORE ... VALIDATE. It row-checks every recovery, so it only passes if the data actually comes back.
The first time you run a restore should never be in production.

Originally published at uptimearchitect.com. I write here in a personal capacity — questions or feedback are welcome via the contact page.