Home > Oracle in general > How to restore an rman backup without any existing control files

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
;
Categories: Oracle in general Tags:
  1. November 3rd, 2010 at 16:12 | #1

    Hi Ronny,

    Oracle always includes a backup of the controlfile when datafile 1 (system) is taken. Therefore, your customer should have had a backupset which contained the controlfile.

    If you want to restore the controlfile from that backupset you can do it in RMAN with:

    RMAN> set dbid XXXXXXX; — use your dbid
    RMAN> restore controlfile from ‘C:\backup_test\ORA_DF631909818_S143_P1_C1’;

    Regards,
    Martin

    • Ronny Egner
      November 3rd, 2010 at 16:40 | #2

      Hi Martin,

      as always it depends:

      If you have turned OFF controlfile autobackup it is true. The reference (http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/rcmsynta010.htm) states:

      “Disables the autobackup feature (default).
      Any BACKUP command that includes datafile 1 automatically includes the current control file and server parameter file in the backup set. Otherwise, RMAN does not include these files.”

      When controlfile autoback is turned ON the following happens:

      “The first channel allocated during the backup or copy job creates the autobackup and places it into its own backup set; for post-structural autobackups, the default disk channel makes the backup. RMAN writes the control file and server parameter file to the same backup piece. After the control file autobackup completes, the database writes a message containing the complete path of the backup piece and the device type to the alert log.

      The default location for the autobackup on disk is the fast recovery area (if configured) or a platform-specific location (if not configured).”

      And thats exact what happened. The customer backed up to an external NFS directory. The control file was indeed backed up – to the FRA. The FRA however was dropped and re-created afterwards.

  2. pkidna
    January 8th, 2016 at 20:44 | #3

    Here is my situation, I have a restored VM from an earlier time point with functioning Oracle 11g database, the idea was that it would be able to be brought up to date with more current backup, not to belabor a long story, I have a valid backup but the corresponding control file piece got corrupted, and not other copy. The datafiles from that backup are fine and can be cataloged by RMAN, and if you do a restore it works fine but then error because control file is the older one. I am in a pickle because there is some irreplaceable data in the intervening time. It is frustrating because I have the files. Actually of the backup there is technically only one tablespace I really need. I tried recreating control file from scratch but that leads to other errors. Is there anyway to update the older control file to have it recognize the new datafiles? Or any other tricks I might try. Thanks!

    • Ronny Egner
      July 29th, 2016 at 07:04 | #4

      You have to re-create the control file. That´s it.

  3. neeraj
    October 7th, 2017 at 10:20 | #5

    Please help to understand, how can you use DBMS_RESTORE when database in is NOMOUNT state i.e controlfile is not restored. ???

  4. ceyhun
    November 22nd, 2017 at 17:11 | #6

    how can you do it without connection to database?

  1. No trackbacks yet.