You’re attempting to open your database and receive error message like following.
Errors in file
/u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_arc0_3389.trc:
ORA-00313: open
failed for members of log group 3 of thread 1
ORA-00312: online log 3
thread 1: '/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log'
ORA-27041: unable to open
file
Linux-x86_64 Error: 2: No
such file or directory
Additional information: 3
ORA-00312: online log 3
thread 1: '/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log'
ORA-27041: unable to open
file
Linux-x86_64 Error: 2: No
such file or directory
Additional information: 3
Tue Aug 30 17:33:35 2016
Master archival failure:
313
Above message indicates
that two members of an online redo log group 3 have experienced a media
failure.
I am simulating this
scenario by deleting all members of a inactive redo log group.
SQL> @redo.sql
MEMBER
GROUP# STATUS
STATUS
-----------------------------------------------------------------------------
---------- ---------------- -------
/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log
1 INACTIVE
/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log
1 INACTIVE
/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log
2 CURRENT
/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log
2 CURRENT
/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log
3
INACTIVE
/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log
3 INACTIVE
6 rows selected.
First of all you have to
understand that an INACTIVE redo log group is not needed for crash recovery and
it may or may not have been archived.So I just want to make sure that none of
my ongoing operations are affected by losing an inactive redo log group – So I
created a simple table and insert some entry into it.
SQL> create table test
(id number );
Table created.
SQL> insert into test
values (111);
1 row created.
SQL> commit;
Commit complete.
SQL> @redo
MEMBER
GROUP# STATUS
STATUS
-----------------------------------------------------------------------------
---------- ---------------- -------
/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log
1 INACTIVE
/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log
1 INACTIVE
/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log
2 CURRENT
/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log
2 CURRENT
/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log
3 INACTIVE
/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log
3 INACTIVE
6 rows selected.
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 deleted my redo log
group 3, which is in INACTIVE state.
[oracle@mydb01]:[TTREC1]
$ rm /data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log
[oracle@mydb01]:[TTREC1]
$ rm /data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log
At this moment alert log
file won’t display anything about deleted redo log files and it will continue
its operation until oracle tries to access the deleted files. I did a log
switch.
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Tue Aug 30 17:33:19 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>
SQL> alter system
switch logfile ;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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 could see following error in my alert log file
[oracle@mydb01]:[TTREC1]
$ alert
Master archival failure:
313
Tue Aug 30 17:33:35 2016
Errors in file
/u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_arc0_3389.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3
thread 1: '/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log'
ORA-27041: unable to open
file
Linux-x86_64 Error: 2: No
such file or directory
Additional information: 3
ORA-00312: online log 3
thread 1: '/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log'
ORA-27041: unable to open
file
Linux-x86_64 Error: 2: No
such file or directory
Additional information: 3
Tue Aug 30 17:33:35 2016
Master archival failure:
313
Now oracle started
complaining about deleted redo log group – Note that oracle still continue its
operation without any fail. If all members of an online redo log group are
damaged, you won’t be able to open your database. In this situation, Oracle
will allow you to only mount your database.So I bounced my database to simulate that scenario.
[oracle@mydb01]:[TTREC1]
$
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Tue Aug 30 17:33:46 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>
SQL> select name
,open_mode from v$database;
NAME
OPEN_MODE
---------
--------------------
TTREC
READ WRITE
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut
down.
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-03113: end-of-file on
communication channel
Process ID: 14470
Session ID: 232 Serial
number: 16835
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
And my alert log file
shows
[oracle@mydb01]:[TTREC1]
$ alert
Errors in file
/u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_lgwr_14373.trc:
ORA-00313: open
failed for members of log group 3 of thread 1
ORA-00312: online log 3
thread 1: '/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.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 3
thread 1: '/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2: No
such file or directory
Additional information: 3
Tue Aug 30 17:35:55 2016
So I started my database
in mount mode.
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Tue Aug 30 17:36:13 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup mount;
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.
SQL> set lines 4444
SQL> col member for
a77
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;
MEMBER
GROUP# STATUS
STATUS ARC
-----------------------------------------------------------------------------
---------- ---------------- ------- ---
/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log
1
INACTIVE
NO
/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log
1
INACTIVE
NO
/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log
2 CURRENT
NO
/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log
2
CURRENT
NO
/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log
3
INACTIVE
NO
/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log
3
INACTIVE
NO
6 rows selected.
As the status of
redo log group 3 is INACTIVE, then this log group is no longer needed for
crash Therefore, you can use the clear logfile command to re-create all members
of a log group. The following example re-creates all log members of group 3.
SQL> alter database
clear logfile group 3;
alter database clear
logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of
instance TTREC1 (thread 1) needs to be archived
ORA-00312: online log 3
thread 1: '/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log'
ORA-00312: online log 3
thread 1: '/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL>
You could see following
message in the alert log file -
Tue Aug 30 17:37:46 2016
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 08/30/2016 17:33:27 (CHANGE 131509533) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 54
Tue Aug 30 17:37:46 2016
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 08/30/2016 17:33:27 (CHANGE 131509533) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 54
Tue Aug 30 17:37:46 2016
Note:-
1.As the warning makes
clear, you are now missing a redo log file. You will not be able to roll
forward through redo from prior backups. You are advised to make a fresh backup
of the database and archivelogs as soon as possible.ie, If the online redo log
group has not been archived, then it may be required for media recovery and not for a crash recovery.
2. alter database clear
logfile command will recreate the physical file at OS level.
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;
MEMBER
GROUP# STATUS
STATUS ARC
-----------------------------------------------------------------------------
---------- ---------------- ------- ---
/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log
1
INACTIVE
NO
/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log
1
INACTIVE
NO
/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log
2
CURRENT
NO
/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log
2
CURRENT
NO
/data1/TTREC/onlinelog/o1_mf_3_cwbfybx9_.log
3 UNUSED
YES
/data2/TTREC/onlinelog/o1_mf_3_cwbfyc2b_.log
3
UNUSED
YES
6 rows selected.
SQL> !host ls -lrt
/data1/TTREC/onlinelog/o1_mf_3_cwbfybx9_.log
host: couldn't get
address for '-lrt': failure
SQL> host ls -lrt
/data1/TTREC/onlinelog/o1_mf_3_cwbfybx9_.log
-rw-r----- 1 oracle
asmadmin 104858112 Aug 30 17:37 /data1/TTREC/onlinelog/o1_mf_3_cwbfybx9_.log
SQL> host ls -lrt
/data2/TTREC/onlinelog/o1_mf_3_cwbfyc2b_.log
-rw-r----- 1 oracle
asmadmin 104858112 Aug 30 17:37 /data2/TTREC/onlinelog/o1_mf_3_cwbfyc2b_.log
SQL> alter database
open;
Database altered.
SQL> select * from
test;
ID
----------
111
SQL>
Our data is intact after
recovering the lost redo log file group. :)
