ORA-00313: open failed for members of log group string of thread string tips

ORA-00313: open failed for members of log group string of thread string tips

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

At this article I shall present how to resolve open failed for members of log groups. 

So last night i have seen many errors about those groups. 

I wanted to recreate my redo log groups however as my research there was an other solution. 

The Oracle docs note this on the ora-00313 error:

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

Cause: The online log cannot be opened. May not be able to find file.

Action: See accompanying errors and make log available.

1.solution

 1-Check status of log file group

SQL> select group#,thread#,status from v$log ;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT

2- Clear logfile.

SQL> alter  database clear unarchived logfile group 1;
Database altered.

3-check again groups.

SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 ACTIVE
3 1 CURRENT

5-Switch log file to use

SQL>Alter system switch logfile;

6-check again groups.

SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 ACTIVE

So, that's it. if you have problem with an other log try for others to. 

İf so you have problem again try 2 solution.

2.Solution

1-CLOSE DATABASE.

SQL>shu immediate;
Database closed.

2-Check the status of the logile 

select
 b.group#, a.archived, a.status, b.status, b.member
from
 v$log a, v$logfile b
where a.group#=b.group#
order by 1,2;

    GROUP# ARC STATUS           STATUS  MEMBER
---------- --- ---------------- ------- ----------------------------
         1 YES   INACTIVE            /u01/app/oracle/oradata/redo01.log
         2 YES   ACTIVE               /u01/app/oracle/oradata/redo02.log
         3 NO    CURRENT            /u01/app/oracle/oradata/redo03.log

 If the status is CURRENT don't try do drop.

3. Drop log group
SQL>ALTER DATABASE DROP LOGFILE GROUP 1;

4-Add new Redo Log by :

SQL>ALTER DATABASE ADD LOGFILE GROUP 1 ' /u01/app/oracle/oradata/redo01.log' SIZE 50M;


4-Recover DB and Open

SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;