Friday 1 August 2014

How to Restore/Recover Lost of Control File Using Rman

Oracle database maintain three copies of Controlfile at three different locations. There are some situations arises when you loses all your controlfile. So in this case how to restore and recover controlfile using RMAN autobackup option.

One day when I tried to mount the Database I encountered with an error code:
ORA-00205: error in identifying control file, check alert log for more info

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
ORA-00205: error in identifying control file, check alert log for more info
=============================================================
How to restore control file using RMAN

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 15:45:17 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:46:27
RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN> restore controlfile;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:47:03
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP














STEP 1: You need to set DBID before recovery

 SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 17:26:13 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

SQL> select dbid from v$database;

      DBID
----------

3561501508

But if your database is not mounted, you cannot find DBID. So set ID to any number you want.
ex: 

RMAN> set DBID=3561501508


executing command: SET DBID

STEP2: Restore control file from autobackup:

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140801
channel ORA_DISK_1: AUTOBACKUP found: c-3561501508-20140801-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-3561501508-20140801-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\CONTROL01.CTL
Finished restore at 01-AUG-14

STEP3: 

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

STEP4
RMAN> recover database;

Starting recover at 01-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG
archived log file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-AUG-14

RMAN> alter database open resetlogs;

database opened

No comments:

Post a Comment