STEP 1: Check database details.
SQL> select
instance_name,version from v$instance;
INSTANCE_NAME VERSION
----------------
-----------------
TEST 11.2.0.3.0
SQL> select member from
v$Logfile;
MEMBER
-------------------------------
/u02/ORACLE/TEST/redo03.log
/u02/ORACLE/TEST/redo02.log
/u02/ORACLE/TEST/redo01.log
STEP 2: Delete log files from os
level to create scenario.
TEST:/u02/ORACLE/TEST>rm
redo*.log
STEP 3: find last archive log
sequence.
If one or all of the online redo logfiles are
delete then the database hangs and
in the alert log file we can see the following error message:
Tue
Jan 30 00:47:19 2015
ARC1:
Failed to archive thread 1 sequence 93 (0)
Tue
Jan 30 00:47:24 2007
Errors
in file /opt/oracle/admin/TEST/bdump/TEST_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/TEST/redo02.log'
ORA-27037:
unable to obtain file status
Linux-x86_64
Error: 2: No such file or directory
The file is missing at the operating system
level.
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 error message in the log file we can
get 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-15
2 1 93
52428800 1 NO INACTIVE
3202983 30-JAN-15
3
1 94
52428800 1 NO INACTIVE
3203074 30-JAN-15
At the operating system also we can find the
last archived logfile:
TEST:/u02/ORACLE/TEST/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
STEP 4: Shut down the database
and startup in Mount stage.
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
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.
STEP 5: Connect to rman to Restore
and Recover database.
TEST:/u02/ORACLE/TEST>rman
target /
Recovery
Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2015
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: TEST (DBID=1493612009, not open)
RMAN> run {
2> set until sequence 93;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
executing
command: SET until clause
Starting
restore at 30-JAN-15
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/TEST/system01.dbf
restoring
datafile 00002 to /u02/ORACLE/TEST/undotbs01.dbf
restoring
datafile 00003 to /u02/ORACLE/TEST/sysaux01.dbf
restoring
datafile 00004 to /u02/ORACLE/TEST/users01.dbf
restoring
datafile 00005 to /u02/ORACLE/TEST/users02.dbf
restoring
datafile 00006 to /u02/ORACLE/TEST/users03.dbf
restoring
datafile 00007 to /u02/ORACLE/TEST/users05.dbf
restoring
datafile 00008 to /u02/ORACLE/TEST/users06.dbf
restoring
datafile 00009 to /u02/ORACLE/TEST/users07.dbf
restoring
datafile 00010 to /u02/ORACLE/TEST/users04.dbf
restoring
datafile 00011 to /u02/ORACLE/TEST/drtbs1.dbf
restoring
datafile 00012 to /u02/ORACLE/TEST/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-15
Starting
recover at 30-JAN-15
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/TEST/arch/arch_1_92_613129285.dbf
archive
log filename=/u02/ORACLE/TEST/arch/arch_1_92_613129285.dbf thread=1 sequence=92
media
recovery complete, elapsed time: 00:00:01
Finished
recover at 30-JAN-15
database
opened
RMAN>exit
STEP 6: Check all the files are
created physically.
The recovery process creates the online redo
logfiles at the operating system level also.
TEST:/u02/ORACLE/TEST>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.
NOTE: Please make sure you remove
all the old archived logfiles from the archived area.
I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!
Keep learning... Have a great day!!!
No comments:
Post a Comment