Steps to recover a loss of all online redo log files using RMAN
Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
Redo entries record data that we can use to rebuild all changes made to the database, including the undo segments. Therefore, the redo log also protects our rollback data.
Step 1:
Check the member and status of redo log files
SQL> startup
ORACLE instance started.
Total System Global Area 1862270976 bytes
Fixed Size 8621952 bytes
Variable Size 553648256 bytes
Database Buffers 1291845632 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/u01/app/oracle/oradata/GDB1/onlinelog/o1_mf_3_hrtnw2g2_.log
/u01/app/oracle/fast_recovery_area/gdb1/GDB1/onlinelog/o1_mf_3_hrtnw3tw_.log
/u01/app/oracle/oradata/GDB1/onlinelog/o1_mf_2_hrtnvj5k_.log
/u01/app/oracle/fast_recovery_area/gdb1/GDB1/onlinelog/o1_mf_2_hrtnvlcx_.log
/u01/app/oracle/oradata/GDB1/onlinelog/o1_mf_1_hrtnvj30_.log
/u01/app/oracle/fast_recovery_area/gdb1/GDB1/onlinelog/o1_mf_1_hrtnvl5l_.log
Step 2:
Removing online log files physically
[oracle@gg1 onlinelog]$ lso1_mf_1_hrtnvj30_.log o1_mf_2_hrtnvj5k_.log o1_mf_3_hrtnw2g2_.log
[oracle@gg1 onlinelog]$ rm *.log
Step 3:
We can see the alert log,
SQL> select value from v$diag_info where NAME=’Default Trace File’;
VALUE
——————————————————————————–
/u01/app/oracle/diag/rdbms/gdb1/gdb1/trace/gdb1_ora_11287.trc
Step 4:
Shutdown and mount the database
[oracle@gg1 ~]$ export ORACLE_SID=gdb1[oracle@gg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 2 21:26:32 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1862270976 bytes
Fixed Size 8621952 bytes
Variable Size 553648256 bytes
Database Buffers 1291845632 bytes
Redo Buffers 8155136 bytes
Database mounted.
Step 5:
Connect to RMAN and recover until the available sequence.
[oracle@gg1 ~]$ export ORACLE_SID=gdb1[oracle@gg1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 – Production on Sun Jan 3 13:14:56 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: GDB1 (DBID=2619077893, not open)
RMAN> run
{
set until sequence 49;
restore database;
recover database;
alter database open resetlogs;
}
executing command: SET until clause
Starting restore at 03-JAN-21
using target database control file instead of recovery catalog
flashing back control file to SCN 3194019
ORACLE error from target database:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
skipping datafile 1; already restored to file /u01/app/oracle/oradata/GDB1/datafile/o1_mf_system_hrtnqmj7_.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/GDB1/datafile/o1_mf_sysaux_hrtnsc60_.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/GDB1/datafile/o1_mf_undotbs1_hrtnt4h8_.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/GDB1/datafile/o1_mf_users_hrtnt5lz_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 03-JAN-21
Starting recover at 03-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 48 is already on disk as file /u01/app/oracle/fast_recovery_area/gdb1/GDB1/archivelog/2021_01_02/o1_mf_1_48_hz132yh6_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/gdb1/GDB1/archivelog/2021_01_02/o1_mf_1_48_hz132yh6_.arc thread=1 sequence=48
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-JAN-21
Statement processed
RMAN>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
GDB1 READ WRITE
