How to restore an rman backup without any existing control files

This week a came across the following very interesting scenario:

The customer made an RMAN online backup and saved it to an NFS location. Controlfile autobackup was on. During backup the backupset was written to that NFS location. The control file however was written to the controlfile autobackup location (i.e. a different location).

After that the customer destroyed all disk groups and re-installed the database servers.

Afterwards he tried to restore the database which failed because there was no controlfile in the backups at all. In order to make it worse the customer had no log files from the RMAN backup session, no DBID and no trace files whatever – including the alert.log. This leaved us with the following situation:

• no autobackup anywhere
• no older controlfile available
• no snapshot controlfile available
• no redo log available
• no backup logfiles
• no “backup controlfile to trace” information
• no DBID
• no alert.log

Under normal circumstances restoring the database from an rman backup without having any control file is impossible. In this article i will show you an approach you might use to restore the datafile anyway.

Before coming up with the solution below i tried several things among them:

• Trying to Re-Create the Controlfile with some dummy file names
• Trying to restore the controlfile from all rman backupsets with “restore controlfile from <path/file>” in case the controlfile was backed up withing a backupset
• Trying to extract the controlfile with DBMS_BACKUP_RESTORE

Nothing worked because there was no controlfile backed up. No way.

Normally this database backup were unable to restore because there was no control file at all. After some digging someone came up with an approach trying to restore the datafiles with DBMS_RESTORE was shown below:

The following PLSQL block demonstrates how to extract datafile from the existing backupsets. All available backupsets are listed in “v_fileTable(1)”. Calling “dbms_backup_restore.RestoreDatafileTo(dfnumber => 1);” searches (and if found in the backupsets) restores datafile 1.

In order to restore all datafile you need to iterate over all datafiles (e.g. 1…n).

DECLARE
v_dev varchar2(50);             -- device type allocated for restore
v_done boolean;                 -- has the controlfile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable;        -- Stores the backuppiece names
v_maxPieces number:=1;          -- Number of backuppieces in backupset

BEGIN
-- Initialise the filetable & number of backup pieces in the backupset
-- This section of code MUST be edited to reflect the customer's available
-- backupset before the procedure is compiled and run. In this example, the
-- backupset consists of 4 pieces:

v_fileTable(1):='C:\backup_test\ORA_DF631909818_S143_P1_C1';
v_fileTable(2):='C:\backup_test\ORA_DF631909818_S144_P1_C1';
-- add all other backupsets if needed
v_maxPieces:=2;

-- Allocate a device. In this example, I have specified 'sbt_tape' as I am
-- reading backuppieces from the media manager. If the backuppiece is on disk,
-- specify type=>null

v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');

-- Begin the restore conversation
sys.dbms_backup_restore.restoreSetDatafile;

dbms_backup_restore.RestoreDatafileTo(dfnumber => 1);

-- Restore the datafile
FOR i IN 1..v_maxPieces LOOP
        sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null);
        IF v_done THEN
                GOTO all_done;
        END IF;
END LOOP;

<<all_done>>
-- Deallocate the device
sys.dbms_backup_restore.deviceDeallocate;

END;
/

Restoring will create the datafile in either you default db-creation dest or in $ORACLE_HOME/dbs. The names unfortunately are lost thus all datafiles are name something like “unnamed<number>” (e.g. “unnamed001” and so on).

After restoring all datafile you need to re-create the control file. Below is an example. Make sure you list ALL datafiles and at least three redo groups:

CREATE CONTROLFILE REUSE DATABASE ORA11P RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 '/path/oracle/dbs/t_log1.f'  SIZE 500K,
  GROUP 2 '/path/oracle/dbs/t_log2.f'  SIZE 500K
# STANDBY LOGFILE
DATAFILE
  '/path/oracle/dbs/t_db1.f',
  '/path/oracle/dbs/dbu19i.dbf',
  '/path/oracle/dbs/tbs_11.f',
  '/path/oracle/dbs/smundo.dbf',
  '/path/oracle/dbs/demo.dbf'
CHARACTER SET WE8ISO8859P1
;