Restore database from lost control file

Restoring the database after decommission of database:

I have come a cross with this experience to restore and recover the database after it had been decommission.


In our environment we have enabled the controlfile autobackup as below ...

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;



and you backup the database using RMAN , then the control files will be backed up , however you need to be aware where the controlfile backup is stored. you can know this by observing the backup command.

For example this is extract from RMAN output with autobackup enabled and we issued backup database command , at the last few line of RMAN output you may get lines like the following :

Starting Control File Autobackup at 30-MAR-10

piece handle=/oracle/oracle/product/10.2.0/db_1/dbs/c-2012553556-20100330-00

Now you can restore the control file connecting to the database on nomount mode :

SQL> startup nomount;

ORACLE instance started...

[oracle@ebilldev test]$ rman

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 30 17:01:44 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> set dbid=2012553556
executing command: SET DBID

Then you need to issue the following command to set RMAN to look on the right location :

RMAN> set controlfile autobackup format for device type
disk to '/oracle/oracle/product/10.2.0/db_1/dbs/%F';
RMAN> restore controlfile from autobackup;
Starting restore at 30-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20100330
channel ORA_DISK_1: autobackup found: /oracle/oracle/product/10.2.0/db_1/dbs/c-2012553556-20100330-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/recover/test/oraData/control01.ctl
output filename=/recover/test/oraData/control02.ctl
output filename=/recover/test/oraData/control03.ctl
Finished restore at 30-MAR-10

Then mount,recover,and open the database resetlogs :

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 30-MAR-10
configuration for DISK channel 2 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
starting media recovery
archive log thread 1 sequence 48 is already on disk as file /recover/test/redo/redo03.log
archive log filename=/recover/test/redo/redo03.log thread=1 sequence=48
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-MAR-10
RMAN> alter database open resetlogs;
database opened