Redo Log Inactive ORA-00313 ORA-00312 ORA-27037

Hi DBA-Mates,

Today, we will discuss some scenario which we have faced and discussed recently in our real time experiences, i.e. related to Inactive online redo log group.
Suppose some of reason, we lost our Inactive online redo log group, then what to do?

Either you or your DBA by mistake dropped that and then he called you that by mistake he has dropped one of the Online redo log group which was inactive, then what will be your next action? What should you do?

Before starting that, I would like to describe about online redo log. This may be helpful for those who is not clear about this. I will try my best to describes in a very simple words.

Online Redo log:

It records all the changes made in an Oracle database. Here all the changes means, when ever a DBA(you) or any users run any commands like Insert, Delete, Update (DML), DDL etc,or any other commands, these all changes will be record in Online Redo log.

It presents for every Oracle database. The online redo log of a database consists of at least two redo log files that are separate from the datafiles (which actually store a database’s data). As part of recovery from an instance or media failure, Oracle applies the appropriate changes in the database’s redo log to the datafiles, which update database data to the instant that the failure occurred.

Now, we will see the scenario and action.

Please find the below details step by step: 

Now we will SIMULATE THE CORRUPTION

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

    GROUP# STATUS

---------- ----------------

         4 INACTIVE

         5 CURRENT

*Here, In above step we can see Group# 4 is INACTIVE status.

SQL> column member format a40

SQL> select GROUP#,member,status  from v$logfile;

    GROUP# MEMBER                                   STATUS

---------- ---------------------------------------- -------

         4 /u01/oracle/oradata/SKU/redo04.log      STALE

         4 /u01/oracle/oradata/SKU/redo04a.log     STALE

         5 /u01/oracle/oradata/SKU/redo05.log

         5 /u01/oracle/oradata/SKU/redo05a.log

* Now,we will create the scenario by removing Inactive Online redolog files as   below:

* This below command can be done from $ prompt also. 
SQL> !rm redo04.log redo04a.log

* Now, shutdown the database. 
SQL> shutdown immediate
Database closed.

Database dismounted.
ORACLE instance shut down.

* Startup the database.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

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

ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/SKU/redo04.log' 

* As we can see in above output, we are getting some ora alerts.
Now, I will show you people the alert.log details:

In ALERT.log below lines observed:

ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/SKU/redo04a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/SKU/redo04.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

RECOVER FROM CORRUPTION:

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE  LOG_MODE     FLASHBACK_ON
---------          ----------            ------------          ------------------
SKU       MOUNTED         ARCHIVELOG   NO

Check the v$log file to check if the file has been archived
SQL> select  GROUP#,STATUS,ARCHIVED from v$log;

    GROUP# STATUS           ARC
---------- ---------------- ---
         5 CURRENT          NO
         4 INACTIVE         YES

Now, we will get many solutions in google for below error which will not work and we will get again some ora errors.
As we can check below:
NOTE:  Both below solutions will not work

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/SKU/redo04.log','/u01/oracle/oradata/SKU/redo04a.log' to Group 4;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/SKU/redo04.log','/u01/oracle/oradata/SKU/redo04a.log' to Group 4
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/oracle/oradata/SKU/redo04.log' - file
already part of database

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/SKU/redo04b.log','/u01/oracle/oradata/SKU/redo04c.log' to Group 4;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/SKU/redo04b.log','/u01/oracle/oradata/SKU/redo04c.log' to Group 4
*
ERROR at line 1:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/SKU/redo04a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/SKU/redo04.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


* Now as per our real time experiences, we tried this below solution.
Actual Solution:  Add a new Group and drop Group 4

SQL>  ALTER DATABASE ADD LOGFILE GROUP 6
  2  '/u01/oracle/oradata/SKU/redo06.log' size 4m;Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/oradata/SKU/redo06a.log' TO GROUP 6;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.

SQL> alter database open;
Database altered.
SQL>

Some more useful links:
ORA-609 Solution
CRONTAB Concepts and Details
R12.1 Vs R12.2
Dataguard Concepts and Setup
Career Planning
Hope this will be useful and helpful. If you guys have any comments and suggestions, please write to us and let us know @ Contact Us. Its good to talk you.