1 - 2 minutes readCase 5: Redo log is not CURRENT and not archived, means missing file is in ACTIVE status.

If the redo log is lost before it is archived then there are a few transactions in the redo log that are lost too. Since a crash recovery is no more possible, a media recovery is required.

The DB needs to be restored from a previous backup and recovered until the last available archive log is applied. The reason I say crash recovery is not possible is that by definition a crash recovery should need only online redo logs to recover the database to a consistent state. If a recovery needs a log file that is not online (like archive logs) then the recovery is no more a crash recovery. It becomes media recovery. In our current case since we are applying the archives and using the previous backup to restore it is not a crash recovery.

Also since we cannot recover database completely as some data is lost in the redo log files before being archived, this becomes an incomplete recovery too. The DB needs to be opened up in RESETLOGS mode.

The steps to recover are below:

Step 1: Restore the DB from previous backup and open the DB in mount state.
Step 2 : Recover database

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Step 3: Open database with resetlog option

SQL> ALTER DATABASE OPEN RESETLOGS;