RMAN recovery from loss of all online redo log files
We should be multiplexing the
online redo log files. Each group should have at least 2
members and each member should be located on a different physical disk.
Loss of a single current online
redo log file will require us to restore the entire database and do an
incomplete recovery.
We can simulate this scenario by deleting all the online
redo log files at the OS level.
SQL> select member from v$Logfile;
MEMBER
-------------------------------
/u02/oradata/orcl/redo03.log
/u02/oradata/orcl/redo02.log
/u02/oradata/orcl/redo01.log
Dba1:/u02/oradata/orcl]$
rm redo*.log
If the current online redo log file is lost,the database
hangs and in the alert log file
we can see the following error message:
Tue Jan 30 00:47:19 2007
ARC1: Failed to archive thread 1 sequence 93 (0)
Tue Jan 30 00:47:24 2007
Errors in file /opt/oradata/admin/orcl/bdump/orcl_arc0_32722.trc:
ORA-00313: open failed for members of log group 2 of thread
1
ORA-00312: online log 2 thread 1: '/u02/ORADATA/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Using RMAN we can recover from this error by restoring the
database from the backup and
recovering to the last available archived redo logfile.
From the alert log we can obtain the last archived file in
our case it is sequence 92 as the error shows that it fails to archive the log
file sequence 93.
SQL> select * from v$Log;
GROUP#
THREAD# SEQUENCE# BYTES
MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
----------
---------- ---------- ---------- ---------- --- ---------------- -------------
---------
1 1 95
52428800 1 NO CURRENT 3203078 30-JAN-07
2 1 93
52428800 1 NO INACTIVE 3202983 30-JAN-07
3 1 94
52428800 1 NO INACTIVE 3203074 30-JAN-07
SQL>
archive log list
Database
log mode Archive Mode
Automatic
archival Enabled
Archive
destination /u02/oradata/orcl/arch
Oldest
online log sequence 92
Next log
sequence to archive 93
Current
log sequence 93
Dba10: cd /u02/oradata/orcl/arc
Dba10:/u02/ORADATA/orcl/arch>
ls –lrt
total
54824
-rw-r----- 1 oradata
dba 714240 Jan 29 16:02
arch_1_90_613129285.dbf
-rw-r----- 1 oradata
dba 46281216 Jan 30 00:37
arch_1_91_613129285.dbf
-rw-r----- 1 oradata
dba 11264 Jan 30 00:41
arch_1_92_613129285.dbf
Shutdown the database
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORADATA
instance shut down.
Mount the database
SQL> startup mount;
ORADATA instance
started.
Total
System Global Area 167772160 bytes
Fixed
Size 2069680 bytes
Variable
Size 92277584 bytes
Database
Buffers 67108864 bytes
Redo
Buffers 6316032 bytes
Database
mounted.
Use RMAN connect to the target database:
Dba10:/u02/oradata/orcl]$
rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan
30 00:53:21 2007
Copyright (c) 1982, 2005, Oradata. All rights reserved.
connected to target database: ORCL (DBID=1493612009, not
open)
RMAN> run {
2> set until sequence 93; (Note: set this number to
one higher than the last archived log available)
3> restore database;
4> recover database;
5> alter database
open resetlogs;
6> }
executing command: SET until clause
Starting restore at 30-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oradata: version
5.2.4.0
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore
from backup set
restoring datafile 00001 to /u02/ORADATA/orcl/system01.dbf
restoring datafile 00002 to /u02/ORADATA/orcl/undotbs01.dbf
restoring datafile 00003 to /u02/ORADATA/orcl/sysaux01.dbf
restoring datafile 00004 to /u02/ORADATA/orcl/users01.dbf
restoring datafile 00005 to /u02/ORADATA/orcl/users02.dbf
restoring datafile 00006 to /u02/ORADATA/orcl/users03.dbf
restoring datafile 00007 to /u02/ORADATA/orcl/users05.dbf
restoring datafile 00008 to /u02/ORADATA/orcl/users06.dbf
restoring datafile 00009 to /u02/ORADATA/orcl/users07.dbf
restoring datafile 00010 to /u02/ORADATA/orcl/users04.dbf
restoring datafile 00011 to /u02/ORADATA/orcl/drtbs1.dbf
restoring datafile 00012 to /u02/ORADATA/orcl/drtbs2.dbf
restoring datafile 00013 to /tmp/undonew.dbf
channel ORA_SBT_TAPE_1: reading from backup piece
5mi8ornj_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=5mi8ornj_1_1 tag=TAG20070130T004019
channel ORA_SBT_TAPE_1: restore complete, elapsed time:
00:01:06
Finished restore at 30-JAN-07
Starting recover at 30-JAN-07
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
starting media recovery
archive log thread 1 sequence 92 is already on disk as file
/u02/ORADATA/orcl/arch/arch_1_92_613129285.dbf
archive log filename=/u02/ORADATA/orcl/arch/arch_1_92_613129285.dbf
thread=1 sequence=92
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-JAN-07
database opened
RMAN> exit
The recovery process creates the online redo logfiles at the
operating system level also.
Dba10:/u02/ORADATA/orcl>ls -lrt redo*
-rw-r----- 1 oradata dba
52429312 Jan 30 01:00 redo03.log
-rw-r----- 1 oradata dba
52429312 Jan 30 01:00 redo02.log
-rw-r----- 1 oradata dba
52429312 Jan 30 01:00 redo01.log
Since we have done an incomplete recover with open
resetlogs, we should take a fresh complete backup of the database.
RMAN Backup and User-Managed Recovery from Loss of Redo Log Files
In some cases, losing redo log files may be a tragedy. As the redo log files contain changes made to the database, it means that by losing redo log files, you can lose those changes forever. Look at the redo log loss scenarios and learn how you need to act properly.
Here is the list of possible redo log failures and their solutions with step-by-step demonstrations and explanations.
- Scenario 1: Recovering from loss of multiplexed redo log file
- Scenario 2: Recovering from loss of the redo log member of the Inactive group
- Scenario 3: Recovering from loss of the redo log member of the Current group
- Scenario 4: Recovering from loss of the redo log member of the Active group
Scenario 1: Recovering from Loss of Multiplexed Redo Log File
When one of the multiplexed members of any group is corrupted or accidently dropped, the LGWR process ignores it and writes the information to the only available member. The following scenario gives a little demonstration to easily understand the main concept:
1. Add one member to each redo
log group. Query all redo log members and their status:
SQL> select b.group#, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1.2;
GROUP# STATUS STATUS MEMBER
---------- ---------------- ------- --------------------------------
1 CURRENT /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
2 INACTIVE /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo02.log
3 ACTIVE /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo03.log
2. Add one member per group:
SQL> alter database add
logfile member '/u02/oradata/testdb/redo01.log' to group 1;
Database altered.
SQL> alter database add logfile member '/u02/oradata/testdb/redo02.log' to group 2;
SQL> alter database add logfile member '/u02/oradata/testdb/redo02.log' to group 2;
Database altered.
SQL> alter database add logfile member '/u02/oradata/testdb/redo03.log' to group 3;
Database altered
- Query both views again. You will see the invalid status for each member because they are newly created.
SQL>
select b.group#, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP#
STATUS
STATUS MEMBER---------- ---------------- ------- --------------------------------
1 CURRENT /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
1 CURRENT INVALID /u02/oradata/testdb/redo01.log
2 INACTIVE INVALID /u02/oradata/testdb/redo02.log
2 INACTIVE /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo02.log
3 ACTIVE INVALID /u02/oradata/testdb/redo03.log
3 ACTIVE /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo03.log
6 rows selected.
4. Perform a manual redo log
switch to make those members available and query the views again:
SQL> alter system
switch logfile;
System altered.
SQL> /
SQL> /
System altered.
SQL> /
SQL> /
System altered.
SQL> select b.group#,
a.status, b.status, b.member from v$log
a, v$logfile b where a.group#=b.group#
order by 1,2;
order by 1,2;
GROUP# STATUS STATUS MEMBER
---------- ---------------- ------- -----------------------------------
1 CURRENT /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
1 CURRENT /u02/oradata/testdb/redo01.log
2 INACTIVE /u02/oradata/testdb/redo02.log
<.....output trimmed ......>
<.....output trimmed ......>
6 rows selected.
5. Delete one of the redo log
files from OS, shut down the database, start it, switch the redo log file and
query the views again:
SQL> host rm -rf /u02/oradata/testdb/redo01.log
SQL> shut abort
SQL> startup
SQL> alter system switch logfile;
SQL> shut abort
SQL> startup
SQL> alter system switch logfile;
System altered.
SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log
a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS
STATUS MEMBER---------- --- ---------------- ------- ----------------------------
1 NO CURRENT /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
1 NO CURRENT INVALID /u02/oradata/testdb/redo01.log
2 NO INACTIVE /u02/oradata/testdb/redo02.log
<......output trimmed ......>
<......output trimmed ......>
6 rows selected.
6. Recreated the member by
dropping and creating it again. As the redo log member is in currently used
redo log group, you cannot drop it. Thus, switch the redo log file and try
again:
SQL> alter database drop
logfile member '/u02/oradata/testdb/redo01.log';
alter database drop logfile member '/u02/oradata/testdb/redo01.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1:
'/u01/ORADATA/product/10.2.0/db_1/oradata/testdb/redo01.log'
ORA-00312: online log 1 thread 1: '/u02/oradata/testdb/redo01.log'
SQL> alter system switch logfile;
alter database drop logfile member '/u02/oradata/testdb/redo01.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1:
'/u01/ORADATA/product/10.2.0/db_1/oradata/testdb/redo01.log'
ORA-00312: online log 1 thread 1: '/u02/oradata/testdb/redo01.log'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/u02/oradata/testdb/redo01.log';
SQL> alter database drop logfile member '/u02/oradata/testdb/redo01.log';
Database
altered.
7. Now add the new redo log
file to the same group:
SQL> alter database add logfile member
'/u02/oradata/testdb/redo01.log' to
group 1;
Database
altered.
SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
SQL> select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS STATUS MEMBER
---------- --- ---------------- ------- ----------------------------
1 NO INACTIVE /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
1 NO INACTIVE INVALID /u02/oradata/testdb/redo01.log
<......output trimmed ......>
<......output trimmed ......>
6 rows selected.
8. Perform a manual redo log
switch to make active the redo log file:
SQL>
alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
System altered.
SQL> /
System altered.
SQL>select b.group#, a.archived, a.status, b.status, b.member from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
GROUP# ARC STATUS STATUS MEMBER
---------- --- ---------------- ------- ----------------------------
1 NO CURRENT /u01/oradata/product/10.2.0/db_1/ora
data/testdb/redo01.log
1 NO CURRENT /u02/oradata/testdb/redo01.log
6 rows selected.
Thanks
Thanks for very nice topic. The above provided lessons are very useful to explore Oracle DBA. One can gain knowledge from fundamentals through Oracle DBA Online Training
ReplyDeleteThanks David.
Delete