Monday, January 04, 2010

Oracle is unable to locate control file (ORA-00205) when mounting database

I was testing RMAN restore scenario on one of the test databases and I came across “ORA-00205” error. It seemed very strange why Oracle is not able to find my control files when they are lying there.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\DBTEST\ORADATA\CONTROL01.CTL,C:\DBTEST\ORADATA\CONTROL02.CTL
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> 

To double check what I am seeing in correct, I ran OS command and it confirmed that the files do exist.

C:\>dir C:\DBTEST\ORADATA\CONTROL01.CTL
 Volume in drive C has no label.
 Volume Serial Number is D0FB-F138

 Directory of C:\DBTEST\ORADATA

12/26/2009  01:36 PM         7,061,504 CONTROL01.CTL
               1 File(s)      7,061,504 bytes
               0 Dir(s)  36,819,836,928 bytes free

C:\>dir C:\DBTEST\ORADATA\CONTROL02.CTL
 Volume in drive C has no label.
 Volume Serial Number is D0FB-F138

 Directory of C:\DBTEST\ORADATA

12/26/2009  01:36 PM         7,061,504 CONTROL02.CTL
               1 File(s)      7,061,504 bytes
               0 Dir(s)  36,819,836,928 bytes free

C:\>

Then what’s wrong? Alert log is always handy to drill down for details and even this time it was helpful. Following is the excerpt from the alert log.

ORA-00202: control file: 'C:\DBTEST\ORADATA\CONTROL01.CTL,C:\DBTEST\ORADATA\CONTROL02.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 123) The filename, directory name, or volume label syntax is incorrect.

If you notice, Oracle is trying to locate one control file and is failing to identify it and alas failing. It was a small mistake where I missed to separate the two files with a comma. After the correction, Oracle happily mounted my test database.

SQL> alter system set control_files='C:\DBTEST\oradata\control01.ctl','C:\DBTEST\oradata\control02.c
tl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\DBTEST\ORADATA\CONTROL01.CT
                                                 L, C:\DBTEST\ORADATA\CONTROL02
                                                 .CTL
SQL>