How we can recover our database when all the member of a current redo log group have experienced media failure.When your database loses all members of the CURRENT redo log group, it means you have lost transactions contained in that group: to be able opening your database you have to perform an incomplete recovery until latest valid SCN.Let simulate this issue and recover it.
Let see the redo
log group status.
SQL> @redo
SQL> set echo
on
SQL> set lines
222
SQL> col
status for a10
SQL> col
member format a45
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_cwwwb3lr_.log
1 CURRENT NO
/data2/TTREC/onlinelog/o1_mf_1_cwwwb3wz_.log
1 CURRENT NO
/data1/TTREC/onlinelog/o1_mf_2_cwwwb48n_.log 2 INACTIVE YES
/data2/TTREC/onlinelog/o1_mf_2_cwwwb4l1_.log 2 INACTIVE YES
/data1/TTREC/onlinelog/o1_mf_3_cwwwb4xn_.log 3 ACTIVE YES
/data2/TTREC/onlinelog/o1_mf_3_cwwwb56r_.log 3 ACTIVE YES
6 rows selected.
Lets create one
simple table and insert one row into it , so that it will protected by redo log
group 1.
SQL> create
table test1 (id number);
Table created.
SQL> insert
into test1 values (33);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> @redo
SQL> set echo
on
SQL> set lines
222
SQL> col
status for a10
SQL> col
member format a45
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_cwwwb3lr_.log 1 CURRENT NO
/data2/TTREC/onlinelog/o1_mf_1_cwwwb3wz_.log 1 CURRENT NO
/data1/TTREC/onlinelog/o1_mf_2_cwwwb48n_.log 2 INACTIVE YES
/data2/TTREC/onlinelog/o1_mf_2_cwwwb4l1_.log 2 INACTIVE YES
/data1/TTREC/onlinelog/o1_mf_3_cwwwb4xn_.log 3 ACTIVE YES
/data2/TTREC/onlinelog/o1_mf_3_cwwwb56r_.log 3 ACTIVE YES
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
Here I renamed my
current redo log group members.
[oracle@mydb01]:[TTREC1]
$ mv /data1/TTREC/onlinelog/o1_mf_1_cwwwb3lr_.log
/data1/TTREC/onlinelog/o1_mf_1_cwwwb3lr_.log_bkp
[oracle@mydb01]:[TTREC1]
$ mv /data2/TTREC/onlinelog/o1_mf_1_cwwwb3wz_.log
/data2/TTREC/onlinelog/o1_mf_1_cwwwb3wz_.log_bkp
I am gonna crash
my database.
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Wed Sep 7 18:50:16 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>
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 1 of thread 1
ORA-00312: online
log 1 thread 1:
'/data2/TTREC/onlinelog/o1_mf_1_cwwwb3wz_.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 1 thread 1:
'/data1/TTREC/onlinelog/o1_mf_1_cwwwb3lr_.log'
ORA-27037: unable
to obtain file status
Linux-x86_64
Error: 2: No such file or directory
Additional information:
3
Database Start up failed by complaining about redo log group 1
that was renamed prior to start-up. In preparation for an incomplete recovery,
first determine the last good SCN by querying the FIRST_CHANGE# column from
V$LOG. In this scenario, you’re missing only the current online redo logs.
SQL> select
group#, status, archived, thread#, sequence#, first_change# from v$log;
GROUP# STATUS ARC THREAD#
SEQUENCE# FIRST_CHANGE#
----------
---------------- --- ---------- ---------- -------------
1 CURRENT NO 1 10
132568327
3 ACTIVE YES 1 9
132567478
2 INACTIVE YES 1 8
132467467
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
Its clear that
redo log group 1 is in current state, so we have to perform a incomplete media
recovery. I had taken a rman full backup prior to this exercise.
[oracle@mydb01]:[TTREC1]
$ rman target /
Recovery Manager:
Release 12.1.0.2.0 - Production on Wed Sep 7 18:53:32 2016
Copyright (c)
1982, 2014, Oracle and/or its affiliates.
All rights reserved.
connected to
target database: TTREC (DBID=4201939161, not open)
RMAN> restore
database until scn 132568327;
Starting restore
at 07-SEP-16
using target
database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=332 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=365 device type=DISK
allocated
channel: ORA_DISK_3
channel ORA_DISK_3:
SID=398 device type=DISK
allocated
channel: ORA_DISK_4
channel
ORA_DISK_4: SID=431 device type=DISK
skipping datafile
5; already restored to file /data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf
skipping datafile
4; already restored to file /data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_DISK_1: restoring datafile 00007 to /data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf
channel
ORA_DISK_1: reading from backup piece /t4_nfs/TTREC/56rf7ocj_1_1
channel
ORA_DISK_2: starting datafile backup set restore
channel
ORA_DISK_2: specifying datafile(s) to restore from backup set
channel
ORA_DISK_2: restoring datafile 00003 to
/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf
channel
ORA_DISK_2: restoring datafile 00008 to
/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf
channel
ORA_DISK_2: reading from backup piece /t4_nfs/TTREC/57rf7ocj_1_1
channel
ORA_DISK_3: starting datafile backup set restore
channel
ORA_DISK_3: specifying datafile(s) to restore from backup set
channel
ORA_DISK_3: restoring datafile 00001 to
/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf
channel
ORA_DISK_3: reading from backup piece /t4_nfs/TTREC/59rf7ocj_1_1
channel
ORA_DISK_4: starting datafile backup set restore
channel
ORA_DISK_4: specifying datafile(s) to restore from backup set
channel
ORA_DISK_4: restoring datafile 00002 to /data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf
channel
ORA_DISK_4: restoring datafile 00006 to
/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf
channel
ORA_DISK_4: reading from backup piece /t4_nfs/TTREC/58rf7ocj_1_1
channel
ORA_DISK_1: piece handle=/t4_nfs/TTREC/56rf7ocj_1_1 tag=TAG20160907T175739
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel
ORA_DISK_2: piece handle=/t4_nfs/TTREC/57rf7ocj_1_1 tag=TAG20160907T175739
channel
ORA_DISK_2: restored backup piece 1
channel
ORA_DISK_2: restore complete, elapsed time: 00:00:35
channel
ORA_DISK_3: piece handle=/t4_nfs/TTREC/59rf7ocj_1_1 tag=TAG20160907T175739
channel
ORA_DISK_3: restored backup piece 1
channel
ORA_DISK_3: restore complete, elapsed time: 00:01:05
channel
ORA_DISK_4: piece handle=/t4_nfs/TTREC/58rf7ocj_1_1 tag=TAG20160907T175739
channel
ORA_DISK_4: restored backup piece 1
channel
ORA_DISK_4: restore complete, elapsed time: 00:01:05
Finished restore
at 07-SEP-16
RMAN> recover
database until scn 132568327;
Starting recover
at 07-SEP-16
using channel
ORA_DISK_1
using channel
ORA_DISK_2
using channel
ORA_DISK_3
using channel
ORA_DISK_4
channel
ORA_DISK_1: starting incremental datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for
restore of datafile 00007: /data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf
channel
ORA_DISK_1: reading from backup piece /t4_nfs/TTREC/5irf7pqa_1_1
channel
ORA_DISK_2: starting incremental datafile backup set restore
channel
ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for
restore of datafile 00003: /data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf
destination for
restore of datafile 00008: /data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf
channel
ORA_DISK_2: reading from backup piece /t4_nfs/TTREC/5jrf7pqa_1_1
channel
ORA_DISK_3: starting incremental datafile backup set restore
channel
ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for
restore of datafile 00001: /data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf
channel
ORA_DISK_3: reading from backup piece /t4_nfs/TTREC/5lrf7pqa_1_1
channel
ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4:
specifying datafile(s) to restore from backup set
destination for
restore of datafile 00002: /data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf
destination for
restore of datafile 00006: /data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf
channel ORA_DISK_4:
reading from backup piece /t4_nfs/TTREC/5krf7pqa_1_1
channel
ORA_DISK_1: piece handle=/t4_nfs/TTREC/5irf7pqa_1_1 tag=TAG20160907T182202
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:00
channel ORA_DISK_2:
piece handle=/t4_nfs/TTREC/5jrf7pqa_1_1 tag=TAG20160907T182202
channel
ORA_DISK_2: restored backup piece 1
channel
ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel
ORA_DISK_3: piece handle=/t4_nfs/TTREC/5lrf7pqa_1_1 tag=TAG20160907T182202
channel
ORA_DISK_3: restored backup piece 1
channel
ORA_DISK_3: restore complete, elapsed time: 00:00:01
channel
ORA_DISK_4: piece handle=/t4_nfs/TTREC/5krf7pqa_1_1 tag=TAG20160907T182202
channel
ORA_DISK_4: restored backup piece 1
channel
ORA_DISK_4: restore complete, elapsed time: 00:00:01
starting media
recovery
archived log for
thread 1 with sequence 7 is already on disk as file
/data2/TTREC/archivelog/2016_09_07/o1_mf_1_7_cwznm8f1_.arc
archived log for
thread 1 with sequence 8 is already on disk as file
/data2/TTREC/archivelog/2016_09_07/o1_mf_1_8_cwznp57t_.arc
archived log for
thread 1 with sequence 9 is already on disk as file
/data2/TTREC/archivelog/2016_09_07/o1_mf_1_9_cwzo2q1m_.arc
archived log file
name=/data2/TTREC/archivelog/2016_09_07/o1_mf_1_7_cwznm8f1_.arc thread=1
sequence=7
media recovery
complete, elapsed time: 00:00:00
Finished recover
at 07-SEP-16
RMAN> alter
database open resetlogs;
Statement
processed
RMAN> exit
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Wed Sep 7 18:58:24 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> select *
from test1;
select * from
test1
*
ERROR at line 1:
ORA-00942: table
or view does not exist
SQL>
Apparently I lost
all the transaction that were protected by redo log group 1.
No comments:
Post a Comment