Restore CONTROLFILE and/or SPFILE from autobackup

As I mentioned earlier in post about setting up autobackup, its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:

RMAN TARGET=/

And enter command to restore CONTROLFILE:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 01-FEB-07

The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:

RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-FEB-07

After restoring SPFILE You can convert it to PFILE, so You can easily read settings:

RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''

Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291. Now we can start recovery:

RMAN target=/

At the beginning set Database ID:

RMAN> SET DBID 669001291;

Then startup instance:

RMAN> startup force nomount;

and run recovery of SPFILE

RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE SPFILE FROM AUTOBACKUP; }

I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.

RMAN> shutdown;
RMAN> startup force nomount;
RMAN> RUN { 
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }

Now You can continue recovery of datafiles.

Paweł