Saturday, May 12, 2012

Losing One Control File

The SQL below was run in an Oracle 9 database. It shows what happens when you lose one control file (assuming you had more than one in the first place). If you want to try it yourself, you should do so on a test database which you are not afraid to lose. First I checked the names of the control files as follows:
 
SQL> select value from v$parameter
  2  where name = 'control_files'
  3  /
 
VALUE
--------------------------------------------------------------------------------
/cisdev/mvltst/mvl_tables/control01.ctl, /cisdev/mvltst/mvl_redo1/control02.ctl
 
SQL>
 
Then I deleted one of the control files in the OS:
 
MVLTST /cisdev/mvltst/mvl_redo1 >ls control*
control02.ctl
MVLTST /cisdev/mvltst/mvl_redo1 >rm control02.ctl
MVLTST /cisdev/mvltst/mvl_redo1 >ls control*
ls: control* not found
MVLTST /cisdev/mvltst/mvl_redo1 >
 
Then as soon as I ran some SQL in the database, which needed to use the control file(s), it failed:
 
SQL> l
  1  select file_name, bytes
  2* from dba_data_files
SQL> /
from dba_data_files
     *
ERROR at line 2:
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile:
'/cisdev/mvltst/mvl_redo1/control02.ctl'
ORA-27041: unable to open file
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
 
SQL>
 
I closed the database using shutdown abort:
 
SQL> shutdown
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/cisdev/mvltst/mvl_redo1/control02.ctl'
ORA-27041: unable to open file
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL>
 
I made a new copy of the 2nd control file:
 
MVLTST /cisdev/mvltst/mvl_tables >ls control*
control01.ctl
MVLTST /cisdev/mvltst/mvl_tables >cp control01.ctl \
> ../mvl_redo1/control02.ctl
MVLTST /cisdev/mvltst/mvl_tables >
 
Then, once I had opened the database:
 
SQL> startup
ORACLE instance started.
 
Total System Global Area   76518176 bytes
Fixed Size                   733984 bytes
Variable Size              67108864 bytes
Database Buffers            8388608 bytes
Redo Buffers                 286720 bytes
Database mounted.
Database opened.
SQL>
 
The query, which failed above, worked OK:
 
SQL> l
  1  select file_name, bytes
  2* from dba_data_files
SQL> /
 
FILE_NAME                                       BYTES
------------------------------------------ ----------
/cisdev/mvltst/mvl_tables/user_data.dbf      20971520
/cisdev/mvltst/mvl_tables/marval_data.dbf  3145728000
/cisdev/mvltst/mvl_tables/undo_1.dbf        209715200
/cisdev/mvltst/mvl_tables/system.dbf        209715200
 
SQL>

No comments: