Oracle RMAN Database Restore and Recovery Guide

Affiliate Disclosure: This post contains affiliate links. Oracle Scripts may earn a commission if you buy through them, at no extra cost to you.
calendar Feb 4, 2026 / · 12 min read · RMAN Database Recovery Point-in-Time Recovery v$backup_datafile v$datafile  ·
Share on: linkedin copy

Oracle RMAN Database Restore and Recovery Guide

Purpose

A restore that fails at the first step is usually a startup problem, not a backup problem. RMAN needs the instance in the right state for each command. A lost controlfile needs a started instance with no mounted database. A lost datafile needs a mounted database. Each script below begins with the startup mode that fits its case.

The four RMAN scripts cover the four cases a DBA meets most. The first is full restore and recovery after a complete loss. The second is point-in-time recovery when archived logs must also come back from backup. The third is point-in-time recovery when the controlfile and archived logs are still on disk. The fourth is a plain recovery of datafiles that are already in place. A short SQL query at the end lists which datafiles went into which backup set, so you can plan a restore before you start it.

Restore and recovery are the subject here. For the backup side, read the RMAN backup commands guide.

Code

 1-- 1. Full restore and recovery
 2startup nomount;
 3run {
 4  allocate channel  t1 type disk;
 5  allocate channel  t2 type disk;
 6  allocate channel  t3 type disk;
 7  allocate channel  t4 type disk;
 8  restore controlfile;
 9  restore archivelog all;
10  alter database mount;
11  restore database;
12  recover database;
13}
14sql 'alter database open resetlogs';
15
16-- 2. Point-in-time recovery, archived logs restored from backup
17startup nomount;
18run {
19  set until time ="to_date('30/08/2006 12:00','dd/mm/yyyy hh24:mi')";
20  allocate channel  t1 type disk;
21  allocate channel  t2 type disk;
22  allocate channel  t3 type disk;
23  allocate channel  t4 type disk;
24  restore controlfile;
25  restore archivelog all;
26  alter database mount;
27  restore database;
28  recover database;
29}
30sql 'alter database open resetlogs';
31
32-- 3. Point-in-time recovery, controlfile and archived logs already on disk
33startup mount;
34run {
35  set until time ="to_date('08/02/2007 14:00','dd/mm/yyyy hh24:mi')";
36  allocate channel  t1 type disk;
37  allocate channel  t2 type disk;
38  allocate channel  t3 type disk;
39  allocate channel  t4 type disk;
40  restore database;
41  recover database;
42}
43sql 'alter database open resetlogs';
44
45-- 4. Simple recovery of datafiles already in place
46startup mount;
47run {
48  allocate channel  t1 type disk;
49  recover database;
50}

 1-- 5. Which datafiles are in which backup set, and when did each finish
 2set pages 999 lines 100
 3col name format a60
 4break on set_stamp skip 1
 5select set_stamp
 6,to_char(ba.completion_time, 'HH24:MI DD/MM/YY') finish_time
 7,df.name
 8from v$datafile df
 9,v$backup_datafile ba
10where df.file# = ba.file#
11and ba.file# != 0
12order by set_stamp, ba.file#
13/

Code Breakdown

Script 1: full restore and recovery

  • startup nomount starts the instance and does not mount the database. Use it when the controlfile itself must be restored, because there is no usable controlfile to mount.
  • allocate channel t1 type disk (t1 to t4) opens four channels. A channel is a connection between RMAN and a database instance. Each connection starts a database server session, and that session does the work of backing up, restoring or recovering. Four channels let one job read four backup sets at the same time.
  • restore controlfile restores the controlfile. Read the note below this list before you run it with no recovery catalog.
  • restore archivelog all restores archived redo log files from backup. See the Key Points for when this step is needed.
  • alter database mount mounts the database with the restored controlfile. The steps after it run against the mounted database.
  • restore database restores the datafiles. It does not restore the controlfile or the server parameter file. That is why the script restores the controlfile as a separate step.
  • recover database applies redo to the restored datafiles. For complete recovery, RMAN applies archived redo log files and online redo logs.
  • sql 'alter database open resetlogs' opens the database with a new log sequence. It is required after a restore with a backup controlfile and after incomplete recovery.

Controlfile restore with no catalog. When the database is not mounted and RMAN is not connected to a recovery catalog, a plain restore controlfile does not work. You must name the autobackup. The version of script 1 for this case is:

1startup nomount;
2set dbid 1234567890;   -- replace with the DBID of your database
3restore controlfile from autobackup;

RMAN displays the DBID each time you connect to the target database, so write it down before you need it. The same clause also works with the server parameter file.

Script 2: point-in-time recovery

  • set until time sets the recovery target for the restore and recover commands that follow it in the same run block. The time here is an example from the original scripts. Replace it with the time just before the damage.
  • The position matters. Put set until before both restore and recover. If you set the time after the restore, the restored files may have time stamps later than the target time. You then may not be able to recover to that time.
  • Everything after the target is lost. All changes made after the target time are discarded when you open with resetlogs.

Script 3: point-in-time recovery with files already on disk

  • startup mount is used because the controlfile is good. No controlfile restore is needed, so the script is shorter.
  • No restore archivelog is in this script, because the archived logs are already in the archive destination. RMAN also restores archived logs by itself when recover needs them, so you seldom need to restore logs by hand.

Script 4: simple recovery

  • recover database alone is enough when the datafiles are in place and only redo must be applied. This script recovers the whole database, so it mounts the database first. To recover single datafiles or tablespaces while the database is open, take those files offline before you run recover.
  • No resetlogs is needed, because the script does not recover to a point in time and does not use a backup controlfile. Open the database with a normal alter database open.

Script 5: datafile backup check

  • v$backup_datafile shows control files and datafiles in backup sets, read from the control file.
  • set_stamp is the backup set stamp. Rows with the same stamp are in the same backup set, so the break command prints a blank line between sets.
  • file# is the datafile number. It is set to 0 for the control file, so ba.file# != 0 leaves the control file out of the report.
  • completion_time is the time the backup finished. The to_char call prints it as hour, minute, day, month and year.
  • v$datafile supplies the file name, joined on file#.

Key Points

  • The startup mode follows the failure. A restore of the controlfile starts from nomount. A restore of datafiles needs a mounted database. Choose the script by what you have lost.
  • Plain restore controlfile has a limit. With no mounted database and no catalog, you must use from autobackup. If the database is mounted or open, you must use the to filename clause instead.
  • restore database is not the whole job. It leaves out the controlfile and the server parameter file. Restore them with their own commands.
  • After a restored controlfile, always run recover. You must run recover after you mount a restored controlfile, and you must open with resetlogs.
  • Incomplete recovery needs resetlogs. A restore with a backup controlfile and any point-in-time recovery both need it.
  • Archived log restore before the mount. The database can be started, mounted or open for restore archivelog, so scripts 1 and 2 run it before the mount. That rule does not say how RMAN finds the log backups when there is no catalog and no mounted control file. If the step fails in that case, move it after alter database mount. The recover command also restores archived redo log files as needed, so you seldom need to restore logs manually.
  • Point-in-time recovery needs archive log mode. It also needs backups of all datafiles from before the target, and archived logs for the period between those backups and the target.

Insights and Best Practices

Run the backup check first

Run script 5 before you decide anything. It shows which datafiles each backup set holds and when each set finished. If a datafile has no row, the control file holds no backup set record for it, and you should find out why before you rely on the restore.

Use set until as a unit

For point-in-time recovery, enter set until before both restore and recover in one run block. The time then applies to both. This keeps the restored datafiles old enough for the recover step to use them. A set command inside a run block stays in force until the block ends or another set changes the same value.

Mind the incarnation

Each resetlogs starts a new incarnation. RMAN can recover through a resetlogs operation when the datafiles to be recovered are from a parent incarnation. A target time that is not in the current incarnation is a special case. You must run reset database to the incarnation that was current at the target, and you must restore a controlfile from that incarnation.

Look at the data before you commit

When you recover with a backup controlfile, you can open the database read-only in SQL*Plus before the resetlogs open. Run your queries and confirm the damage is gone. Open with resetlogs only when the results are right. After resetlogs opens the database, the changes after the target are abandoned.

Set the channel count by measurement

You control the degree of parallelism in a job by the number of channels you allocate, up to 255. Each channel works on one backup set or image copy at a time. Channels beyond the number of backup sets have nothing to read. Start with the number of disks or tape drives that can read at once, measure the restore time, and change the count from there.

Back up again after the open

A new backup after resetlogs is still good practice. It is not a strict need, because RMAN can recover through resetlogs from a parent incarnation. A fresh backup gives you a clean start in the new incarnation. The steps are in the backup commands guide.

Related books recommended

Links go to Amazon. As an Amazon Associate, Oracle Scripts earns from qualifying purchases; your price is unchanged.

When to Run This

  • Script 1: every copy of the controlfile is lost, or the whole server is rebuilt and the database must come back from backup.
  • Script 2: a bad change, such as a wrong delete or a faulty batch job, must be undone, and the archived logs are not on disk.
  • Script 3: the same bad change, but the controlfile and archived logs are still available.
  • Script 4: the datafiles are in place and only redo must be applied to them.
  • Script 5: before any restore, and during recovery drills, to check what each backup set holds.

Troubleshooting Common Issues

  • RMAN error on restore controlfile in nomount. You have no catalog and no from autobackup clause. Set the DBID with set dbid and use restore controlfile from autobackup. If RMAN finds no autobackup, it signals an error and stops. The search starts on the current day, or on the set until day, and goes back up to maxdays days. The default is 7 and the maximum is 366.
  • Recovery asks for an unknown log. RMAN reports an error such as RMAN-06054: media recovery requesting unknown log. If you did not give an until time, use the catalog command to add the missing redo logs to the repository. Then run recover again. RMAN also searches for logs that are not in its records and catalogs any it finds.
  • Recover cannot reach the target time. The restored files may be newer than the target. This can happen when set until came after the restore. Run set until first, then restore again.
  • open resetlogs fails. The command fails if a datafile is offline, unless the file went offline normally or is read-only. Bring the file online or deal with it first.
  • A restore command is refused. Each restore command has its own rule for the database state, as the Key Points show for restore controlfile. Check which state the instance is in and compare it with the startup line of the script.
  • Script 5 returns no rows. The view is read from the control file, so that control file holds no backup set records. A controlfile restored from an older backup may be the cause. Check which control file is mounted before you trust an empty result.

References

Posts in this series