Recovering From A Lost Controlfile

I decided to play a little with control file recovery. It is always wise to have controlfiles multiplexed on separate disks but for testing purposes my controlfiles will be placed in the same directory.

What will happen when I delete one of my three controlfiles?

From documentation:

Damage to any control file, whether it is multiplexed or not, halts database operation when the database attempts to read or write to the damaged control file (which happens frequently, for example at every checkpoint and log switch).

Little demonstration...


Environment:
Linux 32 bit
Oracle 10gR2


Delete one controlfile:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/control01.ctl
/u01/app/oracle/oradata/ora10g/control02.ctl
/u01/app/oracle/oradata/ora10g/control03.ctl


SQL> !rm /u01/app/oracle/oradata/ora10g/control03.ctl

First I will try shutdown immediate:
(Operation failed but database is still up and active.)

SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

-- from alert.log:

Sat Aug 21 23:52:00 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sat Aug 21 23:52:00 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sat Aug 21 23:52:01 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

What will happen if I copy good controlfile to location of missing one and then try to alter tablespace.

SQL> !cp /u01/app/oracle/oradata/ora10g/control01.ctl -
> /u01/app/oracle/oradata/ora10g/control03.ctl

SQL> alter system checkpoint;

System altered.



SQL> alter tablespace users read only;
alter tablespace users read only
     *
ERROR at line 1:
ORA-03113: end-of-file on communication channel



-- from alert.log:
Sat Aug 21 18:54:29 2010
********************* ATTENTION: ********************
The controlfile header block returned by the OS
has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
please do the following:
(1) Save all copies of the controlfile for later
    analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(3) Unmount the instance.
(4) Use the script in the trace file to
    RE-CREATE THE CONTROLFILE and open the database.
*****************************************************
Instance terminated by DBW0, pid = 7444

Instance was immediately terminated.


What if I execute startup now:
(Operation failed with error below.)

SQL> startup
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              71304712 bytes
Database Buffers          222298112 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora10g/users01.dbf'
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora10g/users01.dbf'

So what is the proper way to handle this controlfile recovery.

Delete one controlfile to simulate disk or controller failure.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/control01.ctl
/u01/app/oracle/oradata/ora10g/control02.ctl
/u01/app/oracle/oradata/ora10g/control03.ctl

SQL>

SQL> !rm /u01/app/oracle/oradata/ora10g/control03.ctl

Shutdown abort database and copy good copy of controlfile to the location of the lost one. You can also modify parameter file (CONTROL_FILES parameter) if you have to change location of lost controlfile.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/ora10g/control01.ctl -
> /u01/app/oracle/oradata/ora10g/control03.ctl

SQL> startup
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              71304712 bytes
Database Buffers          222298112 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL>

Database is up and fully functional.

REFERENCE
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro008.htm#sthref96