Description:-
Now we are going to see how to recover a loss of all online redo log files using RMAN
Steps to recover a loss of all online redo log files using RMAN :-
Step 1:- Check the member and status of redo logfiles
[oracle@trichy ~]$ export ORACLE_SID=orcl [oracle@trichy ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 19 11:47:10 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_gj3nr1p6_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_gj3nrgp4_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_gj3nq31c_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_gj3nr1ox_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_gj3nq2vx_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_gj3nq6l1_.log
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
—————- ————- ——— ———– ——— ———-
1 1 4 209715200 512 2 NO
INACTIVE 1971271 13-JUN-19 2012907 13-JUN-19 0
2 1 5 209715200 512 2 NO
INACTIVE 2012907 13-JUN-19 2139855 19-JUN-19 0
3 1 6 209715200 512 2 NO
CURRENT 2139855 19-JUN-19 1.8447E+19 0
Step 2:-Simulate a failure
[oracle@trichy ~]$ cd /u01/app/oracle/oradata/ORCL/onlinelog/
[oracle@trichy onlinelog]$ ls -lrth
total 601M
-rw-r—– 1 oracle oinstall 201M Jun 19 11:53 o1_mf_3_gj3nr1p6_.log
-rw-r—– 1 oracle oinstall 201M Jun 19 11:54 o1_mf_1_gj3nq2vx_.log
-rw-r—– 1 oracle oinstall 201M Jun 19 11:54 o1_mf_2_gj3nq31c_.log
[oracle@trichy onlinelog]$ rm -rf *
Step 3:-Simultaneously see the alert log in another session
[oracle@trichy onlinelog]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Jun 19 11:56:52 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select VALUE from v$diag_info where NAME='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23648.trc cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/ [oracle@trichy trace]$ tail -100f alert_orcl.log In the alert log file we can see the following error message: Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_29488.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/ORCL/onlinelog/o1_mf_1_gj3nq2vx_.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2019-06-20T00:40:24.873812+05:30 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_29488.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/ORCL/onlinelog/o1_mf_1_gj3nq2vx_.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2019-06-20T00:40:24.875896+05:30 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29529.trc: ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_gj3nq2vx_.log' Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile. Step 4:-Shutdown and mount the database
[oracle@trichy onlinelog]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 19 11:56:52 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
[oracle@trichy onlinelog]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 20 00:41:38 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2415917880 bytes Fixed Size 8899384 bytes Variable Size 520093696 bytes Database Buffers 1879048192 bytes Redo Buffers 7876608 bytes Database mounted. SQL>
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 20-JUN-19
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/ORCL/datafile/o1_mf_system_gj3nlsqc_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_gj3nnkn6_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_gj3noo2w_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_gj3nop82_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_06_19/o1_mf_nnndf_TAG20190619T115306_gjmo6bhl_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_06_19/o1_mf_nnndf_TAG20190619T115306_gjmo6bhl_.bkp tag=TAG20190619T115306
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20-JUN-19
Starting recover at 20-JUN-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_06_20/o1_mf_1_2_fqcoh2gd_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_06_20/o1_mf_1_2_fqcoh2gd_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 20-JUN-19
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
——— ——————–
ORCL 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
