Description:-

In this blog I will explain how to recover a loss of all online redo log files using RMAN

Step 1:- Check the member and status of redo logfiles

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Jan 28 19:17:52 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode                        Archive Mode
Automatic archival Enabled
Archive destination                       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        1
Next log sequence to archive     3
Current log sequence                  3

SQL> select member from v$logfile;

SQL> select * from v$log;

Step 2: We can simulate this scenario by deleting all the online redo log files at the OS level.

Step 2 : Shutdown and mount the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.

Step 3: Connect to RMAN and recover until the available sequence.

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 20:21:27 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run 
{
set until sequence 3;
restore database;
recover database;
alter database open resetlogs;
}2> 3> 4> 5> 6> 7>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp tag=TAG20210128T200722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JAN-21
Statement processed
RMAN>

SQL> select name,open_mode from v$database;

Since we have done an incomplete recover with open resetlogs, we should take a fresh
complete backup of the database.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8