Restoring controlfile scenario?

Scenario when all control files for some reason have been lost. The following scenario shows how to restore controlfile and open the database.

— 1) Try to shutdown database which will fail as the control file(s) doesn’t exists so one needs to perform shutdown abort
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oradata/TEST/control1.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

— 2) Start database in nomount to restore the controlfile as controlfile is missing so database can only be started in mount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

— 3) Start rman and connect to target database and set DBID
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release – Production on Tue Dec 7 21:08:53 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: TEST (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

— 4) Restore controlfile from autobackup
RMAN> restore controlfile from autobackup;

Starting restore at 07-DEC-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101207
channel ORA_DISK_1: autobackup found: c-1992878807-20101207-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oradata/TEST/control1.ora
output filename=/u01/oradata/TEST/control2.ora
output filename=/u01/oradata/TEST/control3.ora
Finished restore at 07-DEC-10

— 5) Open database in mount
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

— 6) Recover database
RMAN> recover database;

Starting recover at 07-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/oradata/TEST/redo3a.log
archive log filename=/u01/oradata/TEST/redo3a.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 07-DEC-10

— 7) Database has to be open with resetlogs
RMAN> alter database open resetlogs;

database opened

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.