Description:-
In this article we are going to see how to recover a loss of all online redo log files using RMAN
Let’s start the Demo:-
Steps to recover a loss of all online redo log files using RMAN :-
Step 1:- Check the member and status of redo logfiles
[oracle@18c PROD18C]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 00:56:32 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select member from v$Logfile; MEMBER ----------------------------------------------------------- /u01/app/oracle/oradata/PROD18C/redo03.log /u01/app/oracle/oradata/PROD18C/redo02.log /u01/app/oracle/oradata/PROD18C/redo01.log
Step 2:- Simulate a failure
[oracle@18c PROD18C]$ cd /u01/app/oracle/oradata/PROD18C/ [oracle@18c PROD18C]$ rm redo*
Step 3:-Simultaneously see the alert log in another session
SQL> select VALUE from v$diag_info where NAME='Default Trace File'; VALUE ----- /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_ora_5344.trc cd /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/ [oracle@18c ~]$ tail -50f alert_prod18c.log
In the alert log file we can see the following error message:
Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD18C/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2018-08-17T01:34:11.448234+05:30 Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc: ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD18C/redo02.log' ORA-27037: unable to obtain file status Additional information: 7 2018-08-17T01:34:11.644183+05:30 Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc: ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD18C/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2018-08-17T01:34:11.720628+05:30
Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.
At the operating system also we can find the last archived logfile.From the error message in the log file we can get the last archived file in our case it is sequence 2 as the error shows that it fails to archive the log file sequence 3.
Step 4:-Shutdown and mount the database
[oracle@18c ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 01:40:49 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1660943808 bytes Fixed Size 8658368 bytes Variable Size 1056964608 bytes Database Buffers 587202560 bytes Redo Buffers 8118272 bytes Database mounted.
Step 5:- Connect to RMAN and recover until the available sequence.
RMAN> run
2> {
3> set until sequence 3;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }
executing command: SET until clause
Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
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 /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofotd_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofotd_.bkp tag=TAG20180817T012117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 17-AUG-18
Starting recover at 17-AUG-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcoh2gd_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcoh2gd_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-AUG-18
Statement processed
The recovery process creates the online redo logfiles at the operating system level.Since we have done an incomplete recovery with open resetlogs, we should take a fresh complete backup of the database.
SQL> select name,open_mode from v$database; NAME OPEN_MODE ------- ---------- PROD18C READ WRITE
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
