Tuesday, July 8, 2014

ORA-00205: error in identifying control file, check alert log for more info

Cause :- This error can occur for lost/corrupted any control file.
In my situation I have unfortunately lost one of control file from two files.

Solution :- Create pfile from spfile > Edit pfile > Change "*.control_files=" parameter. Open database using pfile.

SQL> select status from v$instance;

STATUS
------------
STARTED

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> show parameter contr

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\APP\ORADATA\DBRMAN\CONTROL0
                                                 1.CTL, D:\APP\FLASH_RECOVERY_A
                                                 REA\DBRMAN\CONTROL02.CTL
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> create pfile='D:\pfile.ora' from spfile;

File created.

Now edit pfile

=================================== Old Pfile ===================================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base='D:\app'#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\DBRMAN\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\DBRMAN\control01.ctl','D:\app\oradata\DBRMAN\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBRMAN'
*.db_recovery_file_dest='D:\app\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)'
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
==================================== After Editing New Pfile ==========================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base='D:\app'#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\DBRMAN\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\DBRMAN\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBRMAN'
*.db_recovery_file_dest='D:\app\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)'
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
============================================================================================================

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> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> CONN /AS SYSDBA
Connected to an idle instance.
SQL> STARTUP PFILE=D:\pfile.ora;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             310378960 bytes
Database Buffers          218103808 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile='D:\pfile.ora';

File created.

18 comments:

  1. Thanks for the workaround sir! helped me alot!

    ReplyDelete
  2. the answer was really helpful to me thanq so much

    ReplyDelete
  3. Thanks for this lifesaving soln.

    ReplyDelete
  4. Thank you for existing!!!!!!

    ReplyDelete
  5. Lost Both pfile and spfile.

    create pfile='./temp_jan24_pfile.ora' from memory;
    create spfile='./temp_jan24_spfile.ora' from memory;

    While removed control entries, and trying to startup.
    Same ORA-00205 error.

    show param pfile and spfile are showing null.

    Any workaround.

    Thanks in advance,
    Srikar

    ReplyDelete
  6. Hi
    i try some follow now it work:
    STARTUP PFILE=/export/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/D:\pfile.ora;

    ReplyDelete
  7. In spite of trying with the absolute path, was not working.

    SQL> STARTUP PFILE='xxxxx/dbs/temp_jan24_pfile.ora';
    ORACLE instance started.
    ....
    ORA-00205: error in identifying control file, check alert log for more info

    ReplyDelete
  8. Hi, Its useful, Hope this may also useful here:
    https://ora-data.blogspot.in/2016/12/what-is-control-file-and-how-to-do-multiplexing-in-oracle.html

    Thanks

    ReplyDelete
  9. hi after doing that rajib query still i have same error,db is not mounted pls give a solution

    ReplyDelete
  10. MMNL started with pid=16, OS id=1701
    starting up 1 shared server(s) ...
    ORACLE_BASE from environment = /d02/app/oracle/
    Thu Oct 04 14:29:10 2018
    ALTER DATABASE MOUNT
    ORA-00210: cannot open the specified control file
    ORA-00202: control file: '/d02/app/oracle/oradata/GGSRC/stndbyctr0l.ctl'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    ORA-205 signalled during: ALTER DATABASE MOUNT...
    Thu Oct 04 14:29:10 2018
    Checker run found 1 new persistent data failures
    Thu Oct 04 14:29:11 2018
    Errors in file /d02/app/oracle/diag/rdbms/ggsrc/GGSRC/trace/GGSRC_m000_1787.trc:
    ORA-00210: cannot open the specified control file
    ORA-00202: control file: '/d02/app/oracle/oradata/GGSRC/stndbyctrl.ctl'
    ORA-27041: unable to open file
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    ReplyDelete
  11. [abits@ebstest dbs]$ mv /d02/app/oracle/oradata/GGSRC/stndbyctrl.ctl /d02/app/oracle/oradata/GGSRC/stndbyctr01.ctl
    [abits@ebstest dbs]$
    [abits@ebstest dbs]$ vi initGGSRC.ora
    abits@ebstest dbs]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 4 14:28:47 2018

    Copyright (c) 1982, 2009, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup pfile='initGGSRC.ora';
    ORACLE instance started.

    Total System Global Area 730714112 bytes
    Fixed Size 2216944 bytes
    Variable Size 432016400 bytes
    Database Buffers 289406976 bytes
    Redo Buffers 7073792 bytes
    ORA-00205: error in identifying control file, check alert log for more info

    ReplyDelete
  12. SQL>show parameter control
    G:\app\admin\product\12.2.0\dbhome_1\database\ora_control1,G:\app\admin\product\12.2.0\dbhome_1\database\ora_control2,G:\app\admin\product\12.2.0\dbhome_1\database\ora_control3

    (results in 3 control files) but physically they are not present there in G drive
    and after following above procedure at the end it shows same error ORA-00205
    Sir please help me out

    ReplyDelete