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
