The 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.
SQL> select member from v$Logfile;
MEMBER
--------------------------------------
D:\ORACLE\ORADATA\NOIDA\REDO01.LOG
D:\ORACLE\ORADATA\NOIDA\REDO03.LOG
D:\ORACLE\ORADATA\NOIDA\REDO02.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:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\NOIDA\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified
The file is missing at the operating system level.
SQL> select group#,sequence#,first_change#,status from v$log
GROUP# SEQUENCE# FIRST_CHANGE# STATUS
---------- ---------- ------------- ---------------
1 61 1997353 CURRENT
3 60 1955915 INACTIVE
2 59 1919750 INACTIVE
Shutdown the database
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 318046208 bytes
Fixed Size 1332920 bytes
Variable Size 255854920 bytes
Database Buffers 54525952 bytes
Redo Buffers 6332416 bytes
Database mounted.
SQL> exit
Using RMAN connect to the target database:
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Apr 22 14:49:25 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NOIDA (DBID=1502483083, not open)
Starting restore at 22-APR-11
using channel ORA_DISK_1
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 00001 to D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\NOIDA\TRANS.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\FLASH_RECOVERY_AREA\NOIDA\BACKUPSET\2011_04_21\O1_MF_NNNDF_TAG20110421T134444_6TZSWBRW_.BKP
channel ORA_DISK_1: piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\NOIDA\BACKUPSET\2011_04_21\O1_MF_NNNDF_TAG20110421T134444_6TZSWBRW_.BKP tag=TAG20110421T134444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:05
Finished restore at 22-APR-11
RMAN> recover database until sequence 61;
Starting recover at 22-APR-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 47 is already on disk as file D:\ARCHIVE\ARC00047_0748802215.001
archived log for thread 1 with sequence 48 is already on disk as file D:\ARCHIVE\ARC00048_0748802215.001
archived log for thread 1 with sequence 49 is already on disk as file D:\ARCHIVE\ARC00049_0748802215.001
archived log for thread 1 with sequence 50 is already on disk as file D:\ARCHIVE\ARC00050_0748802215.001
archived log for thread 1 with sequence 51 is already on disk as file D:\ARCHIVE\ARC00051_0748802215.001
archived log for thread 1 with sequence 52 is already on disk as file D:\ARCHIVE\ARC00052_0748802215.001
archived log for thread 1 with sequence 53 is already on disk as file D:\ARCHIVE\ARC00053_0748802215.001
archived log for thread 1 with sequence 54 is already on disk as file D:\ARCHIVE\ARC00054_0748802215.001
archived log for thread 1 with sequence 55 is already on disk as file D:\ARCHIVE\ARC00055_0748802215.001
archived log for thread 1 with sequence 56 is already on disk as file D:\ARCHIVE\ARC00056_0748802215.001
archived log for thread 1 with sequence 57 is already on disk as file D:\ARCHIVE\ARC00057_0748802215.001
archived log for thread 1 with sequence 58 is already on disk as file D:\ARCHIVE\ARC00058_0748802215.001
archived log for thread 1 with sequence 59 is already on disk as file D:\ARCHIVE\ARC00059_0748802215.001
archived log for thread 1 with sequence 60 is already on disk as file D:\ARCHIVE\ARC00060_0748802215.001
archived log file name=D:\ARCHIVE\ARC00047_0748802215.001 thread=1 sequence=47
archived log file name=D:\ARCHIVE\ARC00048_0748802215.001 thread=1 sequence=48
archived log file name=D:\ARCHIVE\ARC00049_0748802215.001 thread=1 sequence=49
archived log file name=D:\ARCHIVE\ARC00050_0748802215.001 thread=1 sequence=50
archived log file name=D:\ARCHIVE\ARC00051_0748802215.001 thread=1 sequence=51
archived log file name=D:\ARCHIVE\ARC00052_0748802215.001 thread=1 sequence=52
archived log file name=D:\ARCHIVE\ARC00053_0748802215.001 thread=1 sequence=53
archived log file name=D:\ARCHIVE\ARC00054_0748802215.001 thread=1 sequence=54
archived log file name=D:\ARCHIVE\ARC00055_0748802215.001 thread=1 sequence=55
archived log file name=D:\ARCHIVE\ARC00056_0748802215.001 thread=1 sequence=56
archived log file name=D:\ARCHIVE\ARC00057_0748802215.001 thread=1 sequence=57
archived log file name=D:\ARCHIVE\ARC00058_0748802215.001 thread=1 sequence=58
archived log file name=D:\ARCHIVE\ARC00059_0748802215.001 thread=1 sequence=59
archived log file name=D:\ARCHIVE\ARC00060_0748802215.001 thread=1 sequence=60
media recovery complete, elapsed time: 00:02:01
Finished recover at 22-APR-11
RMAN> alter database open resetlogs;
database opened
The recovery process creates the online redo logfiles at the operating system level also.
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.
Enjoy JJJ
1 comment:
Hi
Why do you restore the whole database. It will consume huge time if the size of the database is large. Is this the only way to recover the Online logs ?
Post a Comment