Friday, April 22, 2011

Recovery from complete loss of all online redo log files using RMAN

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.

Check the number of total redologs files
SQL> select member from v$Logfile;

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.

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.
SQL> select group#,sequence#,first_change#,status  from v$log 
----------      ----------              -------------                    ---------------
         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:
 C:\>rman target sys/ramtech@noida
Recovery Manager: Release - 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)

 RMAN> restore database;
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:

Deepak said...


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 ?