Oracle DBA

Synopsis: This post describes a pragmatic, production-oriented procedure to recover a database when all online redo log files are lost on Oracle 19c. It assumes you have valid RMAN backups and the archived redo logs required for recovery. The example is taken from a lab scenario and is copy-paste ready for documentation or an internal runbook.

---

Overview & Assumptions

This recovery workflow addresses the scenario where the physical online redo log files have been deleted or otherwise lost at the OS level. Recovery is possible provided:

• You have valid RMAN backups of the database (datafiles and control file or control file autobackup).
• You have access to required archived redo logs generated after the last backup.
• You can mount the database and run RMAN.
• You understand that if a backup control file is used during recovery, the database must be opened with RESETLOGS.

Impact: The procedure may require an incomplete recovery and an OPEN RESETLOGS. After a successful RESETLOGS open, a fresh full backup must be taken immediately.

---

Example: Step-by-Step Recovery (lab transcript adapted for copy/paste)

Environment variables and commands shown as executed on the database server. Replace paths, SID, and backup handles with values from your environment.

1. Check current online redo log members and status

export ORACLE_SID=orcl
sqlplus / as sysdba

-- confirm instance and log members
SQL> startup;        -- if not already started
SQL> SELECT member FROM v$logfile;

Sample output (example):

/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

SQL> SELECT group#, thread#, sequence#, status, first_change# FROM v$log;

---

2. Simulate the failure (deleted files at OS level)

Do not run this on production. This step demonstrates the failure used in the example.

cd /u01/app/oracle/oradata/ORCL/onlinelog/
ls -ltrh
# remove all online redo log files (lab only)
rm -rf *

Watch the alert log (in a second session) to observe the failures:

sqlplus / as sysdba
SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';
-- then tail the alert log in a shell:
tail -100f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Sample alert messages:

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_11_gj3nq2vx_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

---

3. Shutdown and mount the instance

Stop the instance cleanly and start it in MOUNT mode:

sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.
Database mounted.

---

4. RMAN: restore and recover to the last available archived sequence

Use RMAN to restore datafiles from backup and recover until the last archived sequence available. In the example we set an UNTIL SEQUENCE to the last available archived log sequence (sequence 3 in the lab transcript). Replace 3 with the highest sequence you have archived up to.

rman target /

RMAN> RUN {
  SET UNTIL SEQUENCE 3;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

Notes about the commands:

• SET UNTIL SEQUENCE <n> instructs RMAN to perform an incomplete recovery up to the specified log sequence.
• RESTORE DATABASE restores datafiles from the RMAN backup set.
• RECOVER DATABASE applies archived redo up to the UNTIL clause.
• ALTER DATABASE OPEN RESETLOGS is required after incomplete recovery (or when restoring using a backup control file).

Sample RMAN output (abridged):

Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
...
restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_gj3nlsqc_.dbf
...
Finished restore at 20-JUN-19

Starting recover at 20-JUN-19
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

---

5. Verify database is open and healthy

SQL> SELECT name, open_mode FROM v$database;

Sample output:

NAME   OPEN_MODE
-----  ----------
ORCL   READ WRITE

Important: Because the recovery used RESETLOGS, you must take a fresh full backup immediately.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

---

Explanation & Rationale

• When all online redo log files are missing, Oracle cannot open the database normally because LGWR and other components expect those files to exist. Starting the instance and mounting the database allows RMAN to restore datafiles and perform media recovery using archived redo logs.
• An incomplete recovery (using UNTIL TIME/SCN/SEQUENCE or an earlier point) is often necessary when the latest online logs are unavailable.
• OPEN RESETLOGS is mandatory after incomplete recovery or when restoring with a backup control file. RESETLOGS resets the online redo sequence and starts a new incarnation of the database.
• After RESETLOGS, previous incarnations’ archived logs are still available for historical recovery, but the current incarnation must be protected with a new backup immediately.

---

Precautions & Best Practices

• Never perform these steps on production without validated backups and an approved maintenance window.
• Confirm the highest archived log sequence available before setting the UNTIL clause. Using a sequence lower than necessary can lead to data loss.
• If you have a control file autobackup and the control file is inconsistent or lost, restore the control file from autobackup first and then mount.
• Always perform BACKUP DATABASE (or BACKUP DATABASE PLUS ARCHIVELOG) immediately after OPEN RESETLOGS.
• Keep the fast recovery area (FRA) sized properly and protect archived logs (e.g., copy to secondary location) to avoid situations where archived logs required for recovery are missing.
• Test this procedure in a non-production environment to become familiar with the commands and outputs.

---

Troubleshooting Notes

• If RMAN reports missing archived logs during RECOVER, locate and restore the required archives (from tape, remote copy, or other backup locations), then RECOVER again.
• If you must restore a backup control file, the flow becomes: RMAN> STARTUP NOMOUNT; RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; RMAN> ALTER DATABASE MOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN RESETLOGS;
• If you cannot find sufficient archived redo logs, consider restoring to the most recent available SCN or sequence and accept the data loss implications after proper authorization.

---

Conclusion

Loss of online redo log files is a recoverable event when you have reliable RMAN backups and archived logs. The key steps are:

1. Mount the database.
2. Use RMAN to RESTORE and RECOVER up to the last available archived sequence.
3. ALTER DATABASE OPEN RESETLOGS.
4. Immediately take a fresh full backup.

This procedure minimizes downtime while ensuring that the database is brought back to a consistent state and that a new recovery baseline is established.