Restore and recovery with RMAN

Walks you step by step through the right RMAN recovery at the moment of failure: 19 scenarios — loss of a datafile, SYSTEM/UNDO, tempfile, corrupt blocks, online redo log, controlfile and spfile; full restore, restore to another directory/ASM and to another server; point-in-time recovery (CDB or PDB PITR), Flashback Database, tablespace PITR, single-table recovery (RECOVER TABLE), dropped PDB; restore over the network from Data Guard (FROM SERVICE), archive-log-only restore, registering moved backups (CATALOG) and a dry run that writes no data (PREVIEW/VALIDATE). Every scenario runs in the order diagnosis → safety net → apply → verification → new backup, with separate steps for single instance, Oracle Restart and RAC (recovery on a single instance with CLUSTER_DATABASE=FALSE). It covers recovering, not taking backups; for the backup plan see the "Oracle backup strategy" guide.

Topology: 1 server (single instance or Oracle Restart) or RAC; source + target in the new-host scenario

Scenarios

• Dry run: RESTORE … PREVIEW / VALIDATE (changes nothing) — Nothing is broken; you want to see, without writing any data, that the backups are really sufficient for a recovery (the database stays open). It is also the first step for the monthly restore drill and when you are not sure which scenario to choose.
• Backups moved to another directory / RMAN cannot find the backup (CATALOG) — The backup pieces exist but RMAN does not know about them (moved, NFS changed, copied from another server): you get RMAN-06023/06025. The database stays open.
• Restore archive logs only (RESTORE ARCHIVELOG) — You need the archive logs of a specific sequence range on disk for LogMiner, a standby gap or manual recovery; the database stays open and no file is overwritten.
• Loss/corruption of a single datafile or tablespace (the database stays open) — The database is open, only one datafile or tablespace cannot be read (ORA-01157/01110/01116); everything else works. If the file belongs to SYSTEM/UNDO choose the "SYSTEM or UNDO" card, if it is a tempfile the "Tempfile" card, and if the file opens but a few blocks are corrupt (ORA-01578) the "Corrupt block" card.
• Corrupt blocks (ORA-01578, block media recovery) — A query returns ORA-01578/ORA-26040, a backup stops with ORA-19566 or VALIDATE found corrupt blocks; the rest of the file is intact. The database stays open and only the corrupt blocks are repaired (Enterprise Edition; on SE2 the file is restored). If the whole file cannot be opened (ORA-01157), use the "Single datafile" card.
• SYSTEM or UNDO datafile lost (the database does not open) — The lost or corrupt file belongs to the SYSTEM or UNDO tablespace: it cannot be taken offline, so the database (only that PDB if it is a PDB file) is shut down and the file is restored in MOUNT. There is no data loss. If it is a file of another tablespace, the "Single datafile" card solves it without shutting the database down.
• Tempfile lost or corrupt (TEMP tablespace) — Sort/hash operations return ORA-01157/ORA-01187/ORA-25153 and the file is a tempfile (the number in the error message is greater than db_files, e.g. data file 201). No restore is needed; a new tempfile is added and the database stays open.
• Online redo log lost or corrupt — ORA-00313/00312 (cannot be opened) or ORA-00327/00366/00367 (corrupt) in the alert log. If one member is lost or the group is INACTIVE, the database stays open; if the CURRENT group is lost, a restore + RESETLOGS is required and the last transactions are lost. The diagnosis step shows the status of the group; choose based on it.
• All controlfile copies lost — The instance cannot find or read the controlfile at the MOUNT stage (ORA-00205/00202/00210); datafiles and redo are intact. If one of the copies is intact, no restore is needed (the diagnosis step shows this); if all are lost, the controlfile is restored from autobackup and the database is opened with RESETLOGS; because the online redo is intact there is no data loss.
• spfile lost — The spfile was deleted or corrupted; the instance does not start (ORA-01078/LRM-00109), or it is running but the file is gone. Datafiles are not touched and there is no data loss; if the instance is still running there is no outage either.
• Full restore, same server (datafiles lost; controlfile and redo intact) — All or most datafiles are gone, the controlfile and online redo logs are intact; the database is shut down and there is no data loss. If the old disk/directory of the files can no longer be used choose the "another directory" card, if the controlfile is lost too choose the "controlfile" card first, and if the server is completely gone choose the "Restore to another server" card.
• Disk gone: restore the database to another directory / ASM (SET NEWNAME) — The disk or disk group holding the datafiles is unusable; the controlfile and redo are intact. The files are restored to a new directory or ASM disk group; the database is shut down and there is no data loss.
• Point-in-time / SCN recovery (PITR, RESETLOGS) — A wrong DELETE/UPDATE/TRUNCATE or a faulty application release: takes the database (or only the PDB) back to a point in the past; all changes after that point are lost (RESETLOGS). Look at narrower options first: a single table → "Recover a single table", a single tablespace → TSPITR, flashback enabled → "Flashback Database" (no restore, much faster).
• Rewind with Flashback Database (restore point / SCN / time) — Flashback is enabled or a guaranteed restore point exists, and no file is lost: the database is rewound within minutes without a restore (Enterprise Edition). RESETLOGS is required. Is it enabled: SELECT flashback_on FROM v$database; → YES, or a row with GUARANTEE_FLASHBACK_DATABASE = YES in v$restore_point.
• Take a tablespace back to a point in the past (TSPITR) — The faulty operation affected only objects in one tablespace; the rest of the database must stay at the present (Enterprise Edition). The tablespace is offline for the duration and all objects in it go back to the target point; if only one or two tables were affected, "Recover a single table" takes less back in time.
• Recover a single table (RECOVER TABLE, 12c+ Enterprise Edition) — A single table was accidentally dropped or damaged; the rest of the database must stay as it is (12c+ Enterprise Edition). If it was dropped and not purged, check the recycle bin first: FLASHBACK TABLE … TO BEFORE DROP takes seconds (the diagnosis step shows it).
• Bring back a dropped PDB (DROP PLUGGABLE DATABASE) — A PDB was removed with DROP PLUGGABLE DATABASE. If the files are still there, it is plugged in again with the XML; if they were deleted, it is brought back from backup (21c+ a single command, 19c through a temporary CDB). The CDB stays open.
• Restore to another server (new host, from controlfile autobackup) — The server is completely gone, or a copy/test environment is to be built: the backup is restored to another server (single instance on a file system only). Recovery goes up to the last backed-up archive log; everything after it is lost; the copy is opened with RESETLOGS.
• Data Guard: get the file from the standby/primary over the network (FROM SERVICE) — Data Guard is in place: you get a lost datafile on the primary from the standby, and a lagging standby or a lost standby controlfile from the primary, without using backup pieces (12c+).

Before you start

• The incident has been recorded and the application team informed: when, what happened, which error message. If a copy of the current state can be taken before the recovery starts, it has been taken (a server/disk snapshot, or at least a copy of the remaining datafiles, the controlfile and the online redo logs); a wrong step can make the situation worse.
• The backups are accessible (mounted and readable): …… and, for the archive logs, the FRA ……. If the backups are on tape/MML, the SBT channel and the software agent are working.
• The DBID is known (…). If not: the connected to target database: … (DBID=…) line in old RMAN logs… or the autobackup file name (…_cf_c-<DBID>-<YYYYMMDD>-<nn>)….
• The auxiliary directory … exists and has as much free space as SYSTEM + SYSAUX + UNDO + the table's tablespace; the server has ~2 GB of free RAM for the temporary instance.
• …RECOVER TABLE ("Recovering tables and table partitions from RMAN backups") is an Enterprise Edition feature.…Standard Edition 2 has no RECOVER TABLE: instead of the steps below, restore the backup to another server ("Restore to another server" + PITR) and take the table out with Data Pump.…
• The same Oracle version and the same RU level are installed software-only on the target server (opatch lspatches identical on both sides), the oracle user/groups and the directory layout match the source, and there is enough disk. No database with the same name is running on the target.
• The maintenance/outage window has been approved… (in this scenario the database stays open; only the affected objects are impacted)…. The backup scheduler (cron/systemd/scheduler) has been stopped until the recovery is finished; otherwise a backup or DELETE OBSOLETE could run in the middle of the operation.
• The Clusterware registration is known: the location of the spfile and password file, …the instances and nodes…the state of the database… (srvctl config database -db …). Save this output before the recovery.
• …Block media recovery…Tablespace point-in-time recovery…Flashback Database… is an Enterprise Edition feature; the command does not work on Standard Edition 2. The SE2 path of this scenario is shown separately in the steps.
• For the temporary (auxiliary) instance, the directory …………… has as much free space as SYSTEM + SYSAUX + UNDO… + …… + PDB…, and the server has at least as much free memory as the target's sga_target (by default RMAN gives the temporary instance the same SGA).
• Data Guard is ready: the password file is the same on both sides (same SYS or SYSBACKUP password), this server's tnsnames.ora has the entries … and …, COMPATIBLE ≥ 12.0. FROM SERVICE does not work with operating system authentication (rman target /); RMAN connects with a password.
• For Flashback Database an FRA is defined and either flashback logging is enabled (FLASHBACK_ON = YES) or the target point is a guaranteed restore point. No datafile is lost/corrupt (flashback does not bring back a lost file), and no datafile has been shrunk and the controlfile has not been recreated after the target point.

Phases of the guide

• Assessment — Assess the situation · Assess the situation · Check the recycle bin (after a DROP a backup may not be needed) · Determine the target point
• Is the required backup there? — Start the instance in MOUNT mode · Start the instance in MOUNT mode (srvctl) · List the backups · Find the autobackup files · Preview the recovery (PREVIEW) · Read and validate the backup pieces (VALIDATE) …
• Datafile / tablespace recovery (online) — Take … offline · Restore and recover … · Bring … online
• Full restore (same server) — Restore, recover and open the database · …Return to cluster mode and start the database with Clusterware…Restart the database with Clusterware (Oracle Restart)…
• Point-in-time / SCN recovery (PITR) — Back up the current state before going back · Bring the database to MOUNT on a single instance (srvctl) · Take the …PDB…database… back to the target point · …Return to cluster mode and start the database with Clusterware…Restart the database with Clusterware (Oracle Restart)…
• Recover a single table (RECOVER TABLE) — Prepare the auxiliary directory · Recover the table from the target point · Compare the recovered data
• Controlfile recovery — Check whether a controlfile copy is left · Check whether a controlfile copy is left (ASM) · Start the instance in NOMOUNT (srvctl) · Restore the controlfile from autobackup and open · …Return to cluster mode and start the database with Clusterware…Restart the database with Clusterware (Oracle Restart)…
• spfile recovery — Rewrite the spfile from the parameters in memory · Write the spfile to ASM from the parameters in memory · Restore the spfile from autobackup · Restore the spfile from autobackup to ASM · Verify that the instance was started with the spfile
• Source server: collect information and transfer the backups — Take a current backup · Write out the DBID, autobackup and directory list · Copy the backups and the password file to the target
• Target server: restore and recovery — Create the directories · Add the oratab entry · Restore the spfile and controlfile, mount · Register the backup pieces (CATALOG) · Restore, recover up to the last archive log and open · Detach the copy from the source (optional)
• Diagnosis: why can RMAN not find the backup? — See the new location of the backup pieces · List the backups RMAN knows and the missing ones
• Safety net — Copy the current state of the controlfile
• Register the backups with RMAN — Register the pieces in the new directory (CATALOG START WITH) · Clean up the records of the old location
• Verification: are the backups found now? — Preview the recovery again · Adjust the backup script to the new location
• Diagnosis: which archive logs are needed and are they in the backup? — Check whether the archive logs are on disk · Find the backup of the archive logs and preview
• Safety net: target directory and space — Prepare the restore directory
• Restore the archive logs — Restore the archive logs (RESTORE ARCHIVELOG)
• Verification and cleanup — List the restored archive logs · Delete the restored archive logs when the job is done
• Diagnosis: is it a corrupt block? — Scan the file (VALIDATE) · List the corrupt blocks and the objects they belong to
• Safety net: is there an intact copy and an archive chain? — Preview the backups of the file and the recovery chain
• Repair the corrupt blocks — Repair the corrupt blocks from backup (RECOVER CORRUPTION LIST) · Standard Edition 2: restore the datafile · Fix NOLOGGING blocks (if any)
• Verification: are the blocks intact? — Scan the file again · Look for the root cause: disk and storage errors
• Diagnosis: is the file SYSTEM or UNDO? — Start the instance in MOUNT mode · Start the instance in MOUNT mode (srvctl) · Verify that the file is SYSTEM or UNDO · Preview and validate the required backup
• Safety net — Copy the current state of the controlfile
• Restore the SYSTEM / UNDO datafile — Close the PDB · Restore, recover and open the datafile · …Return to cluster mode and start the database with Clusterware…Restart the database with Clusterware (Oracle Restart)…
• Diagnosis: is the lost file a tempfile? — See the tempfiles and their status · Check the file and the alert log
• Safety net: space check — Check that there is space for the new tempfile
• Add a new tempfile, remove the lost one — Add a new tempfile and remove the lost one
• Verification — Verify the temp tablespace
• Diagnosis: which group, in which state? — See the status of the redo log groups