Database name and version
SQL>
select instance_name,version from v$instance;
INSTANCE_NAME VERSION
----------------
-----------------
opsdba 10.2.0.2.0
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 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 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
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-07
2 1 93
52428800 1 NO INACTIVE 3202983 30-JAN-07
3 1
94 52428800 1 NO INACTIVE 3203074 30-JAN-07
At the
operating system also we can find the last archived logfile:
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.
Using
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;
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.
No comments:
Post a Comment