ORA-00313 ,ORA-00312 open failed for members of log group

Posted on by Osama Mustafa in Database
ORA-00313: open failed for members of log group 206 of thread 2
ORA-00312: online log 206 thread 2: ‘/u05/oradata/stdby/’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

 Solution :

1-Mount the database.

SQL>STARTUP MOUNT
Database mounted.

2-Check the status of the logile to see whether it is current.

SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
—————-
CURRENT

 Note : If the status did not CURRENT then simply drop the log file by:

 

SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

3-Add new Redo Log by :

SQL>ALTER DATABASE ADD LOGFILE GROUP 4 ‘u03/App/Oradata/redo3.log’ SIZE 50M;

4-Do Recover and Open Database Resetlog :


SQL>RECOVER DATABASE UNTIL CANCEL;

SQL>ALTER DATABASE OPEN RESETLOGS; 

Enjoy
Osama Mustafa

Published by Osama Mustafa

Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers. In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases. Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.

30 thoughts on “ORA-00313 ,ORA-00312 open failed for members of log group”

  6. Hey man, i got the same problem but with my rac database. I have manual deleted one log file in the +FRA by my mistake, so what do i need to do to get rid of this error?Thanks in advance and sorry for my bad English!

    Like

    Reply

  8. I access asm though asmcmd and looking for the files inside it. Last time i got some problems which relate to file in redo log. I have backup redo log file which i deleted before delete it but i can't get it back though copy in Linux. It's my big mistake. Hiz. Can you give me some advice for that? Here is my alert log after delete one redo log file:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '+FRA/mydb/onlinelog/group_1.256.810237741'ORA-17503: ksfdopn:2 Failed to open file +FRA/mydb/onlinelog/group_1.256.810237741ORA-15012: ASM file '+FRA/mydb/onlinelog/group_1.256.810237741' does not existErrors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_lgwr_16702.trc:ORA-00321: log 1 of thread 1, cannot update log file headerORA-00312: online log 1 thread 1: '+FRA/mydb/onlinelog/group_1.256.810237741'Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_lgwr_16702.trc:ORA-00313: open failed for members of log group 1 of thread 1Thread 1 advanced to log sequence 711 (LGWR switch) Current log# 1 seq# 711 mem# 0: +DATA/mydb/onlinelog/group_1.262.810237741Fri Apr 05 01:02:19 2013Archived Log entry 1122 added for thread 1 sequence 710 ID 0x2c01e5e9 dest 1:Fri Apr 05 01:59:33 2013Clearing Resource Manager plan via parameter

    Like

    Reply

  10. Hi I am also facing the same problem. Here, redolog files has been deleted and there are no redolog files and the one showing is current, How to resolve this?

    Like

    Reply

  13. sir when i try to drop my group that is not current they give error ORA-00350: log 3 of instance orcl3 (thread 1) needs to be archived what solution of this ……..

    Like

    Reply

  19. Worked. One suggestion is to show actual command, like in one scenario I worked, I provided redolog file name with path to complete the recovery process.

    Like

    Reply

  21. Hi,I am in same situation.DB is mounted,And I want to drop current redo log group.please find below error.SQL> alter database clear unarchived logfile group 3;alter database clear unarchived logfile group 3*ERROR at line 1:ORA-01624: log 3 needed for crash recovery of instance DP1 (thread 1)ORA-00312: online log 3 thread 1: '/oracle/DP1/origlogA/log_g13m1.dbf'ORA-00312: online log 3 thread 1: '/oracle/DP1/mirrlogA/log_g13m2.dbf'In mount mode we can't take log switch.please help me.

    Like

    Reply

  22. I also faced the issue while restoring the DB as:(Extract from alert log)Errors in file /u01/oracle/app/oracle/diag/rdbms/imsgnlp1/IMSGNLP1/trace/IMSGNLP1_m000_10840.trc:ORA-00313: open failed for members of log group 6 of thread 1ORA-00312: online log 6 thread 1: '+ORASYS/IMSGNLP1/ONLINELOG/group_6.263.911130891'ORA-17503: ksfdopn:2 Failed to open file +ORASYS/IMSGNLP1/ONLINELOG/group_6.263.911130891ORA-15012: ASM file '+ORASYS/IMSGNLP1/ONLINELOG/group_6.263.911130891' does not existORA-00312: online log 6 thread 1: '/u01/logs-redo/IMSGNLP1/onlinelog/log0601.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Kindly advise.

    Like

    Reply

  25. Hi Osama,I am having similar issues with node 2 in my RAC,12c. Please see extract from the alert log below.Sun Jun 26 07:09:12 2016TABLE BIB_CTL.BAD_DATA: ADDED INTERVAL PARTITION SYS_P78106 (634216) VALUES LESS THAN (707350)Sun Jun 26 07:12:09 2016Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:Sun Jun 26 07:12:09 2016WARNING: Write Failed. group:3 disk:1 AU:38385 offset:0 size:524288path:ORCL:FRA02 incarnation:0x0 asynchronous result:'I/O error' subsys:/opt/oracle/extapi/64/asm/orcl/1/libasm.so krq:0x7f05bca65a60 bufp:0x7f05bd232000 osderr1:0x3 osderr2:0x2e IO elapsed time: 1000 usec Time waited on I/O: 0 usecSun Jun 26 07:12:09 2016Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:ORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]WARNING: Write Failed. group:3 disk:1 AU:38385 offset:524288 size:524288path:ORCL:FRA02 incarnation:0x0 asynchronous result:'I/O error' subsys:/opt/oracle/extapi/64/asm/orcl/1/libasm.so krq:0x7f05bca6d350 bufp:0x7f05bd2b2000 osderr1:0x3 osderr2:0x2e IO elapsed time: 1000 usec Time waited on I/O: 0 usecSun Jun 26 07:12:09 2016Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:ORA-15080: synchronous I/O operation failed to write block 2033664 of disk 1 in disk group RECOORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]ORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]WARNING: failed to write mirror side 1 of virtual extent 993 logical extent 0 of file 2846 in group 3 on disk 1 allocation unit 38385WARNING: group 3 file 2846 vxn 993 block 2033664 write I/O failedSun Jun 26 07:12:09 2016Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:——more BIBN2_arc3_24573.trcTrace file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trcOracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1System name: LinuxNode name: lipcomp2Release: 3.8.13-118.2.1.el6uek.x86_64Version: #2 SMP Thu Nov 26 20:37:55 PST 2015Machine: x86_64Instance name: BIBN2Redo thread mounted by this instance: 2Oracle process number: 65Unix process pid: 24573, image: oracle@lipcomp2 (ARC3)*** 2016-06-26 07:12:09.717*** SESSION ID:(144.54358) 2016-06-26 07:12:09.717*** CLIENT ID:() 2016-06-26 07:12:09.717*** SERVICE NAME:(SYS$BACKGROUND) 2016-06-26 07:12:09.717*** MODULE NAME:() 2016-06-26 07:12:09.717*** CLIENT DRIVER:() 2016-06-26 07:12:09.717*** ACTION NAME:() 2016-06-26 07:12:09.717ORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]DDE rules only execution for: ORA 15080—– START Event Driven Actions Dump ——– END Event Driven Actions Dump ——— START DDE Actions Dump —–Executing SYNC actionsExecuting ASYNC actions—– END DDE Actions Dump (total 0 csec) —–ORA-15080: synchronous I/O operation failed to write block 2033664 of disk 1 in disk group RECOORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]ORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]ORA-15186: ASMLIB error function = [kfk_asm_ioerror], error = [0], mesg = [I/O Error]—————————This led to the restarting of the instance. Kindly advise.Regards,Eric

    Like

    Reply

  27. السلام عليكم1- /AS SYSDBA2- ALTER USER ACT_CUR IDENTIFIED BY ACT_CUR;ERROR at line 1:ORA-01109: database not openيرجى المساعدة ولك جزيل الشكر اخي

    Like

    Reply

  29. Hi there, I have deleted an online current unarchived redo log file and the file is non mirrored/multiplexed. That is the only file which was present in group 1.Now I have already tried incomplete recovery then resetlogs. I have tried clear logfile group and resetlogs and switching of logs but nothing is working here.Could anyone please suggest what shall I do here?P.S. I don't have backup of database

    Like

    Reply

  30. SQL> alter database mount;Database altered.SQL> alter database open;alter database open*ERROR at line 1:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1:'/home/oracle/RAC/onlinelog/o1_mf_1_fcpmzc1n_.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> select status from v$log where group#=1;STATUS—————-CURRENTSQL> alter system switch logfile;alter system switch logfile*ERROR at line 1:ORA-01109: database not openSQL> alter system checkpoint;alter system checkpoint*ERROR at line 1:ORA-01109: database not openSQL> alter database clear unarchived logfile group 1;alter database clear unarchived logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance rac (thread 1)ORA-00312: online log 1 thread 1:'/home/oracle/RAC/onlinelog/o1_mf_1_fcpmzc1n_.log'SQL> alter database drop logfile group 1;alter database drop logfile group 1*ERROR at line 1:ORA-01623: log 1 is current log for instance rac (thread 1) – cannot dropORA-00312: online log 1 thread 1:'/home/oracle/RAC/onlinelog/o1_mf_1_fcpmzc1n_.log'SQL>—————————————-My db is in mount mode i am not able to open db. until db is in open mode i cant do checkpoint & switch log file…kindly guide me what to do…my redo log file is current pleas check above error

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.