Oracle Control file recovery

How to Recover an Accidentally Deleted Control file .....

Oracle Control file recovery 

Deleted a control file accidentally 

Recover control file 

SQL> connect scott/tiger

Connected.

SQL> create table test_control ( c_date date );  ------- create test table to check out test 

Table created.

SQL> insert into test_control values ( sysdate );

1 row created.

SQL> /

1 row created.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_control ;  

C_DATE

-------------------

20-07-2013 19:37:54

20-07-2013 19:37:56

SQL> !ls -ltr /u01/app/oracle/oradata/vihaan/control*

-rw-r----- 1 oracle oinstall 9748480 Jul 20 19:41 /u01/app/oracle/oradata/vihaan/control02.ctl

-rw-r----- 1 oracle oinstall 9748480 Jul 20 19:41 /u01/app/oracle/oradata/vihaan/control01.ctl

delete the contral file 

SQL> !rm /u01/app/oracle/oradata/vihaan/control*

SQL>  !ls -ltr /u01/app/oracle/oradata/vihaan/control*

ls: /u01/app/oracle/oradata/vihaan/control*: No such file or directory

SQL> archive log list;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/vihaan/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

In alert log file 

ALTER SYSTEM: Flushing buffer cache

Sat Jul 20 19:45:17 2013

Errors in file /u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/vihaan_m000_4757.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/vihaan/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown immediate ;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/vihaan/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Only shutdowm abort option left 

SQL> shutdown abort ;

ORACLE instance shut down.

Recovery Manager complete.

-bash-3.2$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 20 19:47:35 2013

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount ;

Oracle instance started

Total System Global Area    1043886080 bytes

Fixed Size                     2234960 bytes

Variable Size                620758448 bytes

Database Buffers             415236096 bytes

Redo Buffers                   5656576 bytes

RMAN backup location

-bash-3.2$ cd /u01/RmanBackup/VIHAAN/

-bash-3.2$ ls -ltr

total 1056760

-rw-r----- 1 oracle oinstall    9830400 Jul 20 09:17 c-1662646448-20130720-02

-rw-r----- 1 oracle oinstall 1061232640 Jul 20 16:19 VIHAAN821290686_s18_s1

-rw-r----- 1 oracle oinstall     143872 Jul 20 16:19 VIHAAN821290772_s19_s1

-rw-r----- 1 oracle oinstall    9830400 Jul 20 16:19 c-1662646448-20130720-04

RMAN> connect target /

connected to target database: VIHAAN (not mounted)

Restore control file from backup

RMAN> restore controlfile from '/u01/RmanBackup/VIHAAN/c-1662646448-20130720-04';

Starting restore at 20-JUL-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/vihaan/control01.ctl

output file name=/u01/app/oracle/oradata/vihaan/control02.ctl

Finished restore at 20-JUL-13

RMAN> sql 'alter database mount';

sql statement: alter database mount

RMAN>  RECOVER DATABASE;  ---- <<<<<< Imp Only recover no retore database  

Starting recover at 20-JUL-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/vihaan/redo01.log

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/vihaan/redo02.log

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/vihaan/redo03.log

archived log file name=/u01/Archive/1_3_821290607.dbf thread=1 sequence=3

archived log file name=/u01/app/oracle/oradata/vihaan/redo01.log thread=1 sequence=4

archived log file name=/u01/app/oracle/oradata/vihaan/redo02.log thread=1 sequence=5

archived log file name=/u01/app/oracle/oradata/vihaan/redo03.log thread=1 sequence=6

media recovery complete, elapsed time: 00:00:00

Finished recover at 20-JUL-13

RMAN> exit

SQL> connect / as sysdba

Connected.

SQL>  alter database open resetlogs ;

SQL> connect scott/tiger

Connected.

SQL>  alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_control ;  ---- all the row are back :)

C_DATE

-------------------

20-07-2013 19:37:54

20-07-2013 19:37:56