Solution of "ORA-00313: open failed for members of log group # of thread #"

When you  try to open  database and it returned or gave the following error  message: 

• ORA-00313: open failed for members of log group 3 of thread 1
• ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log'
• ORA-27037: unable to obtain file status

What is the cause of the problem or error?

• The redo log files deleted or not reached

What is the solution of the problem or error?

First of all we analyze the redo log  structure  and  how oracle writes redo log? 

1. Oracle Database writes every transaction synchronously to the redo log buffer which is then written to the online redo logs. The contents of the log include uncommitted transactions, undo data, and schema and object management statements. And oracle uses online redo log for just recovery.
2. An online redo log consists of two or more online redo log files. Oracle Database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived. If database is on archived mode. Oracle Database Architecture and Lgwr process figures are below...

        

  Simulation Steps of Deleted Current Log Files Using OS command

•        As begin with we check the database status:
  [oracle@localhost ~]$ sqlplus / as sysdba
  SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 6 13:48:41 2013
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  Connected to an idle instance.(Means that database is not open)
  
• If database is not open we will open and then   we deleted the current log file using OS command:

[oracle@localhost ~]$ sqlplus / as sysdba

We try to start up with nomount:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             306186936 bytes
Database Buffers          142606336 bytes
Redo Buffers                6008832 bytes

 After that we try to mount of  database:

 SQL> alter database mount;
Database altered.
  
After that we try to Open Database:
 

 SQL> alter database open;
Database altered.

 Now the database are  opened and we check the log files status and delete current log files using Operational System




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

    GROUP#    THREAD#  SEQUENCE#    MEMBERS STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------------- -------------
         2          1          4          1 INACTIVE                                         14618453
         3          1          5          1 CURRENT                                        14639675
         4          1          3          1 INACTIVE                                        14611613






 We was backup redo03m.log before deleted and I gave redo03m.log
 

 [root@localhost orcl]# mv redo03.log redo03m.log
[root@localhost orcl]# rm -f redo03.log
[root@localhost orcl]# ls
APEX_1246426611663638.dbf  control01.ctl  redo02m.log  redo4.log     system01.dbf  undotbs01.dbf
APEX_1265209995679366.dbf  example01.dbf  redo03m.log  sysaux01.dbf  temp01.dbf    users01.dbf



Solution 1:

The problem log  group  is 3 please check the error message  ORA-00313: open failed for members of log group 3 of thread 1. Because the current log file is corrupted or deleted so that we open database with reset log files.

  

 SQL> alter database open resetlogs;
Database altered.

 SQL> alter database open;
Database altered.
 
Now database was opened...