MAA best practices dictates that to avoid such scenarios, 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/ORACLE/opsdba/redo03.log
/u02/ORACLE/opsdba/redo02.log
/u02/ORACLE/opsdba/redo01.log
opsdba:/u02/ORACLE/opsdba>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/oracle/admin/opsdba/bdump/opsdba_arc0_32722.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u02/ORACLE/opsdba/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/ORACLE/opsdba/arch
Oldest online log sequence 92
Next log sequence to archive 93
Current log sequence 93
opsdba: cd /u02/ORACLE/opsdba/arc
opsdba:/u02/ORACLE/opsdba/arch> ls –lrt
total 54824
-rw-r----- 1 oracle dba 714240 Jan 29 16:02 arch_1_90_613129285.dbf
-rw-r----- 1 oracle dba 46281216 Jan 30 00:37 arch_1_91_613129285.dbf
-rw-r----- 1 oracle dba 11264 Jan 30 00:41 arch_1_92_613129285.dbf
Shutdown the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Mount the database
SQL> startup mount;
ORACLE 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:
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (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 Oracle: 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/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00005 to /u02/ORACLE/opsdba/users02.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf
restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf
restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
restoring datafile 00012 to /u02/ORACLE/opsdba/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/ORACLE/opsdba/arch/arch_1_92_613129285.dbf
archive log filename=/u02/ORACLE/opsdba/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.
opsdba:/u02/ORACLE/opsdba>ls -lrt redo*
-rw-r----- 1 oracle dba 52429312 Jan 30 01:00 redo03.log
-rw-r----- 1 oracle dba 52429312 Jan 30 01:00 redo02.log
-rw-r----- 1 oracle 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.
The following two tabs change content below.
Gavin Soorma
Good example – slight change –> set until sequence XX thread 1;
rman Target SYS/XXX@BACKUPS catalog Rmancat/XXX@Rmancat
RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 26 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}