1 Reply Latest reply: Apr 15, 2011 8:59 PM by EdStevens RSS

    ORA-00313: open failed for members of log group 3 of thread 1

    user10189144
      hi all,

      i got after reboot the system the following message

      SQL> startup
      ORACLE instance started.

      Total System Global Area 2.6206E+10 bytes
      Fixed Size 2131496 bytes
      Variable Size 2181040600 bytes
      Database Buffers 2.4008E+10 bytes
      Redo Buffers 14643200 bytes
      Database mounted.
      ORA-00313: open failed for members of log group 1 of thread 1
      ORA-00312: online log 1 thread 1: '/oracle/PLF/origlogA/log_g11m1.dbf'
      ORA-00312: online log 1 thread 1: '/oracle/PLF/mirrlogA/log_g11m2.dbf'

      first i do these

      SQL> select member from v$logfile;

      MEMBER
      --------------------------------------------------------------------------------
      /oracle/PLF/origlogB/log_g14m1.dbf
      /oracle/PLF/mirrlogB/log_g14m2.dbf
      /oracle/PLF/origlogA/log_g13m1.dbf
      /oracle/PLF/mirrlogA/log_g13m2.dbf
      /oracle/PLF/origlogB/log_g12m1.dbf
      /oracle/PLF/mirrlogB/log_g12m2.dbf
      /oracle/PLF/origlogA/log_g11m1.dbf
      /oracle/PLF/mirrlogA/log_g11m2.dbf


      SQL> select GROUP# ,status from v$log;

      GROUP# STATUS
      ---------- ----------------
      1 INACTIVE
      4 INACTIVE
      3 CURRENT
      2 INACTIVE

      then
      SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

      Database altered.

      SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle/PLF/origlogA/log_g11m1.dbf','/oracle/PLF/mirrlogA/log_g11m2.dbf')size 200M;

      Database altered.

      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-00313: open failed for members of log group 2 of thread 1
      ORA-00312: online log 2 thread 1: '/oracle/PLF/origlogB/log_g12m1.dbf'
      ORA-00312: online log 2 thread 1: '/oracle/PLF/mirrlogB/log_g12m2.dbf'

      SQL> select GROUP# ,status from v$log;

      GROUP# STATUS
      ---------- ----------------
      1 UNUSED
      4 INACTIVE
      3 CURRENT
      2 INACTIVE

      after that i do

      SQL> alter database drop logfile group 2;

      Database altered.

      SQL> ALTER DATABASE ADD LOGFILE GROUP 2('/oracle/PLF/origlogB/log_g12m1.dbf','/oracle/PLF/mirrlogB/log_g12m2.dbf')size 200M;

      Database altered.


      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-00313: open failed for members of log group 3 of thread 1
      ORA-00312: online log 3 thread 1: '/oracle/PLF/origlogA/log_g13m1.dbf'
      ORA-00312: online log 3 thread 1: '/oracle/PLF/mirrlogA/log_g13m2.dbf'


      SQL> alter system switch logfile;
      alter system switch logfile
      *
      ERROR at line 1:
      ORA-01109: database not open

      now i have this

      SQL> select GROUP# ,status from v$log;

      GROUP# STATUS
      ---------- ----------------
      1 UNUSED
      4 INACTIVE
      3 CURRENT
      2 UNUSED


      PLEASE HELPME to solve this, its the production system
        • 1. Re: ORA-00313: open failed for members of log group 3 of thread 1
          EdStevens
          user10189144 wrote:
          hi all,

          i got after reboot the system the following message
          And what did you do before reboot? Why were you rebooting a production system?
          >
          SQL> startup
          ORACLE instance started.

          Total System Global Area 2.6206E+10 bytes
          Fixed Size 2131496 bytes
          Variable Size 2181040600 bytes
          Database Buffers 2.4008E+10 bytes
          Redo Buffers 14643200 bytes
          Database mounted.
          ORA-00313: open failed for members of log group 1 of thread 1
          ORA-00312: online log 1 thread 1: '/oracle/PLF/origlogA/log_g11m1.dbf'
          ORA-00312: online log 1 thread 1: '/oracle/PLF/mirrlogA/log_g11m2.dbf'

          first i do these

          SQL> select member from v$logfile;

          MEMBER
          --------------------------------------------------------------------------------
          /oracle/PLF/origlogB/log_g14m1.dbf
          /oracle/PLF/mirrlogB/log_g14m2.dbf
          /oracle/PLF/origlogA/log_g13m1.dbf
          /oracle/PLF/mirrlogA/log_g13m2.dbf
          /oracle/PLF/origlogB/log_g12m1.dbf
          /oracle/PLF/mirrlogB/log_g12m2.dbf
          /oracle/PLF/origlogA/log_g11m1.dbf
          /oracle/PLF/mirrlogA/log_g11m2.dbf


          SQL> select GROUP# ,status from v$log;

          GROUP# STATUS
          ---------- ----------------
          1 INACTIVE
          4 INACTIVE
          3 CURRENT
          2 INACTIVE

          then
          SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

          Database altered.

          SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle/PLF/origlogA/log_g11m1.dbf','/oracle/PLF/mirrlogA/log_g11m2.dbf')size 200M;

          Database altered.

          SQL> alter database open;
          alter database open
          *
          ERROR at line 1:
          ORA-00313: open failed for members of log group 2 of thread 1
          ORA-00312: online log 2 thread 1: '/oracle/PLF/origlogB/log_g12m1.dbf'
          ORA-00312: online log 2 thread 1: '/oracle/PLF/mirrlogB/log_g12m2.dbf'

          SQL> select GROUP# ,status from v$log;

          GROUP# STATUS
          ---------- ----------------
          1 UNUSED
          4 INACTIVE
          3 CURRENT
          2 INACTIVE

          after that i do

          SQL> alter database drop logfile group 2;

          Database altered.

          SQL> ALTER DATABASE ADD LOGFILE GROUP 2('/oracle/PLF/origlogB/log_g12m1.dbf','/oracle/PLF/mirrlogB/log_g12m2.dbf')size 200M;

          Database altered.


          SQL> alter database open;
          alter database open
          *
          ERROR at line 1:
          ORA-00313: open failed for members of log group 3 of thread 1
          ORA-00312: online log 3 thread 1: '/oracle/PLF/origlogA/log_g13m1.dbf'
          ORA-00312: online log 3 thread 1: '/oracle/PLF/mirrlogA/log_g13m2.dbf'


          SQL> alter system switch logfile;
          alter system switch logfile
          *
          ERROR at line 1:
          ORA-01109: database not open

          now i have this

          SQL> select GROUP# ,status from v$log;

          GROUP# STATUS
          ---------- ----------------
          1 UNUSED
          4 INACTIVE
          3 CURRENT
          2 UNUSED


          PLEASE HELPME to solve this, its the production system
          So what research of the problem led you to believe that dropping an entire redo log group (now multiple entire redo log groups) was the solution? Do you realize you must have a minimum of two redo log groups for the db to even operate?

          You have a production system down. That justifies a Sev 1 ticket with oracle support, which will get you to your solution much faster than relying on unpaid volunteers of sometimes dubious expertise and that may live 12 time zones from you.

          Edited by: EdStevens on Apr 15, 2011 8:58 PM