Restore ControlFile with RMAN / Without RMAN

Restoring a controlfile without rman
_____________________________________

suppose you lost a controlfile say control01.ctl. The controlfile got deleted but you have the controlfile
control02.ctl control03.ctl
Now when you login to database, you get below error
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
/u2/oracle/product/10.1.0/db/oradata/SWX/control01.ctl’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

The best thing to do in such scenario quickly is to copy any of the remaining controlfile and rename it to controlfile “control01.ctl”
You can do this using OS command like below:
bash-3.00$ cp -p control02.ctl control01.ctl
Now log back to database and Voila everything should work perfectly
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Restoring a controlfile from rman
_____________________________________

We are goign to assume you have controlfile autobackup on and you have latest full backup in RMAN
When you connect rman to restore control file it gives below error:
RMAN TARGET /
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u2/oracle/product/10.1.0/db/oradata/SWX/control03.ctl’
ORA-27041: unable to open file

Dont panic just login to sqlplus as sysdba and do a shutdown abort (make sure your ORACLR_SID is set properly)
Now log back to RMAN
RMAN TARGET /
connected to target database (not started)

RMAN> restore controlfile from autobackup;
Starting restore at 23-JAN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/23/2011 11:46:36
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

Again it fails. The correct step is as belows:
RMAN> startup nomount
RMAN> set dbid=4217664994
executing command: SET DBID
RMAN>  restore controlfile from ‘/u3/rmanbkp/c-4217664994-20110124-00′;
Starting restore at 24-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK

RMAN> mount database;
database mounted
released channel: ORA_DISK_1

RMAN> restore database;
Starting restore at 24-JAN-11
Starting implicit crosscheck backup at 24-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK
.
.
.
.
.

RMAN> recover database;
Starting recover at 24-JAN-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

RMAN> alter database open resetlogs;
database opened
Several points are worth emphasizing.
1. Recovery using a backup controlfile should be done only if a current control file is unavailable.
2. All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.
4. Technically the above is an example of complete recovery – since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE – no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
‘D:\oracle_data\datafiles\ORCL\TEMP01.DBF’;

Tablespace altered.
 

Category: DatabaseRecovery

Tags:

2 comments

Leave a Reply

Article by: Shadab Mohammad