Friday, October 5, 2012

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

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

25 comments:

  1. thanksssssssssssssssssss

    ReplyDelete
  2. name: hamidreza
    family: fakhari
    email:esmid2@yahoo.com

    thanks for comment

    ReplyDelete
  3. same error in my alert log and continuous trace file created in udump

    ReplyDelete
  4. 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!

    ReplyDelete
    Replies
    1. How could you delete redolog by mistake ?

      Delete
    2. 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 1
      ORA-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.810237741
      ORA-15012: ASM file '+FRA/mydb/onlinelog/group_1.256.810237741' does not exist
      Errors 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 header
      ORA-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 1
      Thread 1 advanced to log sequence 711 (LGWR switch)
      Current log# 1 seq# 711 mem# 0: +DATA/mydb/onlinelog/group_1.262.810237741
      Fri Apr 05 01:02:19 2013
      Archived Log entry 1122 added for thread 1 sequence 710 ID 0x2c01e5e9 dest 1:
      Fri Apr 05 01:59:33 2013
      Clearing Resource Manager plan via parameter

      Delete
  5. What is the solution if the redo log file is the current redolog file?

    ReplyDelete
    Replies
    1. there's command called
      Alter system Switch logfile / Alter system checkpoint

      Delete
  6. 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?

    ReplyDelete
    Replies
    1. alter system switch logfile
      Alter system checkpoint

      but at least you should have 2 redolog on your database .

      Delete
  7. 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 ........

    ReplyDelete
    Replies
    1. SQL> alter database clear unarchived logfile group 3;
      SQL> alter database drop logfile group 3;
      add new log

      Delete
  8. this help me to recover my media recovery after i had same issues

    ReplyDelete
  9. Thanks a lot this is helpful.

    ReplyDelete
  10. Thank you very-very much!!! This post saved me!

    ReplyDelete
  11. 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.

    ReplyDelete
  12. 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.

    ReplyDelete
    Replies
    1. What if you run this command ;-

      alter system checkpoint;

      you should be able to drop it.

      Delete
  13. 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 1
    ORA-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.911130891
    ORA-15012: ASM file '+ORASYS/IMSGNLP1/ONLINELOG/group_6.263.911130891' does not exist
    ORA-00312: online log 6 thread 1: '/u01/logs-redo/IMSGNLP1/onlinelog/log0601.log'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    Kindly advise.

    ReplyDelete
    Replies
    1. "Linux-x86_64 Error: 2: No such file or directory"

      Check if the file is exists

      Delete
  14. 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 2016
    TABLE BIB_CTL.BAD_DATA: ADDED INTERVAL PARTITION SYS_P78106 (634216) VALUES LESS THAN (707350)
    Sun Jun 26 07:12:09 2016
    Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:
    Sun Jun 26 07:12:09 2016
    WARNING: Write Failed. group:3 disk:1 AU:38385 offset:0 size:524288
    path: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 usec
    Sun Jun 26 07:12:09 2016
    Errors 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:524288
    path: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 usec
    Sun Jun 26 07:12:09 2016
    Errors 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 RECO
    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]
    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 38385
    WARNING: group 3 file 2846 vxn 993 block 2033664 write I/O failed
    Sun Jun 26 07:12:09 2016
    Errors in file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc:
    ------
    more BIBN2_arc3_24573.trc
    Trace file /u01/app/oracle/diag/rdbms/bibn/BIBN2/trace/BIBN2_arc3_24573.trc
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Advanced Analytics and Real Application Testing options
    ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1
    System name: Linux
    Node name: lipcomp2
    Release: 3.8.13-118.2.1.el6uek.x86_64
    Version: #2 SMP Thu Nov 26 20:37:55 PST 2015
    Machine: x86_64
    Instance name: BIBN2
    Redo thread mounted by this instance: 2
    Oracle process number: 65
    Unix 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.717

    ORA-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 actions
    Executing 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 RECO
    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]
    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

    ReplyDelete