Oracle DBA

In the ever-changing landscape of database administration, a catastrophic loss of all datafiles is one of the most alarming situations an Oracle DBA can face.
However, when equipped with valid backups and the right strategy, even this scenario can be handled gracefully using Oracle RMAN (Recovery Manager).

In this article, we’ll discuss how to perform a complete database-level recovery when all datafiles are lost, using both the current control file and a backup control file.
You’ll also learn how to deal with read-only and temporary tablespaces and use the UNTIL clause for point-in-time restores.

---

Assumptions

Before starting, ensure the following:

• You have valid backups of your database.
• You have access to all archived redo logs generated after the backup was taken.
• You have all the redo required to recover the datafiles completely.

With these prerequisites met, you can proceed confidently.

---

Scenario I — Recovery Using the Current Control File

In this situation, we’ll mount the database, restore all datafiles, recover the database, and then open it normally.

Step 1: Connect to RMAN and Mount the Database

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Mon Feb 24 10:04:14 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /
connected to target database (not started)

RMAN> startup mount;

Output:

Oracle instance started
database mounted

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size              671089544 bytes
Database Buffers           390070272 bytes
Redo Buffers                 5517312 bytes

Step 2: Restore the Database

RMAN> restore database;

Sample Output:

Starting restore at 24-FEB-2014 10:04:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/DB11G/oradata/brij/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/DB11G/oradata/brij/example01.dbf
...
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 24-FEB-2014 10:06:35

Step 3: Recover the Database

RMAN> recover database;

Sample Output:

Starting recover at 24-FEB-2014 15:23:16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:09
Finished recover at 24-FEB-2014 15:23:26

Step 4: Open the Database

RMAN> alter database open;

Output:

database opened

Recovery completed successfully!

Note: RMAN automatically performs restore failover — if a backup piece is missing or corrupted, RMAN will search older backup sets until it finds a valid one.

What About Read-Only and Temporary Tablespaces?

Read-Only Tablespaces

By default, RMAN skips read-only tablespaces during a restore.
If you need them restored as well, use the CHECK READONLY clause:

RMAN> restore database check readonly;

Temporary Tablespaces

You don’t need to restore or re-create missing temporary tablespace tempfiles.
Oracle automatically re-creates them when the database is opened:

Oracle automatically detects missing tempfiles and re-creates them for locally managed temporary tablespaces.

Restoring From a Tagged Backup

If you wish to restore from a specific backup tag, use:

RMAN> restore database from tag WEEKLY_BACK;

This helps when you’ve strategically tagged backups such as WEEKLY_BACK, MONTHLY_FULL, etc.

Using the UNTIL Clause in RMAN Restore Commands

Sometimes, you may want to restore to a specific point in the past.
You can achieve this using the UNTIL clause in several ways:

A) Restore Until a Specific SCN

RMAN> restore database until SCN 5634174;

Use this when you know the exact System Change Number (SCN) for recovery.

B) Restore Until a Specific Log Sequence

RMAN> restore database until sequence 17;

Ideal when you have the archived redo log sequence number handy.

C) Restore Until a Restore Point

If you’ve created restore points in your database:

RMAN> restore database until restore point WEEKLY_BACK;

D) Restore Until a Specific Time

RMAN> restore database until time 'sysdate - 5';

or a precise timestamp:

RMAN> restore database until time "to_date('08-jan-2024 13:00:00','dd-mon-rrrr hh24:mi:ss')";

This method is particularly useful for point-in-time recovery (PITR) scenarios.

---

Scenario II — Recovery Using a Backup Control File

If the current control file is also lost, you can restore using a backup control file.

Step-by-Step Procedure:

RMAN> connect target /;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

Important: Whenever a backup control file is used during recovery,
the database must be opened with RESETLOGS.