While starting the database you are seeing following message in your alertlog file and your database stop in mount stage.
ORA-00313: open failed
for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'/data2/TTREC/onlinelog/o1_mf_2_cwfcyvnm_.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 2 thread 1:
'/data1/TTREC/onlinelog/o1_mf_2_cwfcyvj9_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
While investigating further,you found out
redo log group 2 is in ACTIVE state – How you will troubleshoot this issue?
I am going to simulate this scenario and
will explain how we can recover our database in such case.
Checking the status of redo log group-
SQL> @redo
SQL> set echo on
SQL> set lines 222
SQL> col member format a66
SQL> select member, a.group#, a.status,
b.status, a.archived from v$log a, v$logfile b where a.group# =
b.group# order by a.group#, member;
Redo log group 1 is the current redo log
and 2 is active, meaning that they are required for recovery. When we say they
are required for recovery means that the content has not been flushed to
datafiles from database buffer cache. DBWR is yet to do that. This happens when
checkpoint has just started and DBWR is yet to perform its job.But at the same
time you can see that these active redo log files has been archived by ARCH
process. This is important for us.
SQL> exit
Disconnected from Oracle Database 12c
Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application
Clusters, OLAP, Advanced Analytics
and Real Application Testing options
I am gonna rename the ACTIVE redo log
member.
[oracle@mydb01]:[TTREC1] $ mv /data1/TTREC/onlinelog/o1_mf_2_cwfcyvj9_.log
/data1/TTREC/onlinelog/o1_mf_2_cwfcyvj9_.log_bkp
[oracle@mydb01]:[TTREC1] $ mv /data2/TTREC/onlinelog/o1_mf_2_cwfcyvnm_.log
/data2/TTREC/onlinelog/o1_mf_2_cwfcyvnm_.log_bkp
I renamed my active redo log members, so I
am gonna crash my databas.
[oracle@mydb01]:[TTREC1] $ sqlplus / as
sysdba
SQL*Plus: Release 12.1.0.2.0 Production on
Fri Sep 2 16:19:03 2016
Copyright (c) 1982, 2014, Oracle. All
rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application
Clusters, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut abort
;
ORACLE instance shut down.
SQL>
Now I will try to Startup my database. This
will fail as it wont be able to recover because it will not be able to access
redo log group 2.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed
Size
3712904 bytes
Variable
Size
1996490872 bytes
Database
Buffers 134217728 bytes
Redo
Buffers
13062144 bytes
Database mounted.
ORA-00313: open
failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'/data2/TTREC/onlinelog/o1_mf_2_cwfcyvnm_.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 2 thread 1:
'/data1/TTREC/onlinelog/o1_mf_2_cwfcyvj9_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
SQL> select name,open_mode from
v$database ;
NAME
OPEN_MODE
--------- --------------------
TTREC MOUNTED
Next, verify that the damaged log group has
an ACTIVE status as follows-
SQL> @redo
SQL> set lines 222
SQL> col member format a66
SQL> select member, a.group#, a.status,
b.status, a.archived from v$log a, v$logfile b where a.group# =
b.group# order by a.group#, member;
SQL>
SQL>
Now we can recover database until cancel
because just “recover database” will not be able to recover since the active
redo log file is missing. Even though this file is archived, database wont be
aware of this.
SQL> recover
database until cancel;
ORA-00279: change 132000260 generated at
09/02/2016 16:17:50 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_02/o1_mf_1_130_cwl6g4jy_.arc
ORA-00280: change 132000260 for thread 1 is
in sequence #130
Specify log: {<RET>=suggested |
filename | AUTO | CANCEL}
auto
ORA-00279: change 132000961 generated at
09/02/2016 16:18:44 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_02/o1_mf_1_131_%u_.arc
ORA-00280: change 132000961 for thread 1 is
in sequence #131
ORA-00278: log file
'/data2/TTREC/archivelog/2016_09_02/o1_mf_1_130_cwl6g4jy_.arc' no longer needed
for this recovery
ORA-00308: cannot open archived log '/data2/TTREC/archivelog/2016_09_02/o1_mf_1_131_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but
OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be
consistent
ORA-01110: data file 1:
'/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf'
SQL>
Here it has applied all the archived log
files, but still this is not recovered completely. This is where you should
supply the current online redo log file. Recover until
cancel again and supply current redo log file which is redo log 1.
SQL> @redo
SQL> set echo on
SQL> set lines 222
SQL> col member format a66
SQL> select member, a.group#, a.status,
b.status, a.archived from v$log a, v$logfile b where a.group# =
b.group# order by a.group#, member;
SQL> recover database until cancel;
ORA-00279: change 132000961 generated at
09/02/2016 16:18:44 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_02/o1_mf_1_131_%u_.arc
ORA-00280: change 132000961 for thread 1 is
in sequence #131
Specify log: {<RET>=suggested |
filename | AUTO | CANCEL}
/data1/TTREC/onlinelog/o1_mf_1_cwj40jc4_.log --> I specified one of the current redo log file name here.
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
We were able to recover here after loosing
the active redo log file, because that active redo log file was archived by
archiver process.
