Friday, 9 December 2016

ORA-00205- error in identifying control file

Below steps to follow and fix the ORA-00205 error.

Connect the database  as sysdba

[oracle@stalley ~]$ Sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 19 10:24:36 2016
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
STEP-1
SQL> show parameter pfile;
                    Or
         Show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/11.1.0/db_1/ dbs/spfilecvprod.ora

Above query will check the database are running the spfile or pfile.
Most probably the DB is running on SPFILE only.  

STEP-2 
Open that SPFILE we get the control file location.
[oracle@stalleydb dbs]$ cat spfilecvprod.ora
cvprod.__java_pool_size=12582912
cvprod.__large_pool_size=4194304
cvprod.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
cvprod.__pga_aggregate_target=209715200
cvprod.__sga_target=633339904
cvprod.__shared_io_pool_size=0
cvprod.__shared_pool_size=373293056
cvprod.__streams_pool_size=8388608
*.audit_file_dest='/opt/oracle/admin/cvprod/adump'
*.audit_trail='none'
*.compatible='11.1.0.0.0'
*.control_files='/database/cvprod/control01.ctl','/CC"+database/cvprod/control02.ctl','/database/cvprod/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cvprod'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
 
STEP-3
Open the control file to check the all datafile,redolog file available or not. Below query.
[oracle@stalleydb dbs]$ cat /database/cvprod/control01.ctl
 
STEP-4
Create Pfile using Spfile below query.
SQL> create pfile='/database/pfile.ora' from pfile='/opt/oracle/product/11.1.0 /db_1/ dbs/ spfilecvprod.ora’;
File created
 
STEP-5
Open the Pfile and edit the control file script. If there is any existing ctl script remove it.
[oracle@stalleydb ~]$ cd /database
[oracle@stalleydb database]$ vi pfile.ora

STEP-6
SQL> shut immediate;

STEP-7
Startup the pfile in nomount state.
SQL> startup pfile='/database/pfile.ora' nomount;
SQL> startup pfile='/database/pfile.ora' nomount;
 
STEP-8
SQL> alter database mount;
            If you are getting the same control file error the above query will check the SMON and kill the process.
 
STEP-9
[oracle@stalleydb ~]$ ps -ef | grep smon
oracle     761     1  0 10:56 ?        00:00:00 ora_smon_cvprod

[oracle@stalleydb ~]$ kill -9 761 8074

[oracle@stalleysdb ~]$ sqlplus / as sysdba

STEP-10
SQL> startup pfile='/database/pfile.ora' nomount;
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2149040 bytes
Variable Size             603981136 bytes
Database Buffers          226492416 bytes
Redo Buffers                6660096 bytes

STEP=11
SQL> alter database mount;
Database altered.
 
STEP-12
SQL> alter database open;
Database altered.
 
STEP- 13
SQL> select status from v$instance;
STATUS
------------
OPEN