How to recover from Loss Of Online Redo Log

 If a media failure has affected the online redo logs of a database, then the

appropriate recovery procedure depends on the following:

The configuration of the online redo log: mirrored or non-mirrored
the type of media failure: temporary or permanent
the types of online redo log files affected by the media failure: CURRENT, ACTIVE, UNARCHIVED, or INACTIVE

The database was shutdown normally before loss of archivelog file

Scenario 1: Loss of INACTIVE Online Redo Log Group

Step 1: Shutdown the database

Step 2: Mount the database.

SQL> STARTUP MOUNT

Step 3: Check the V$LOG view to determine if the file has been archived.

SQL> select GROUP#,MEMBERS,STATUS, ARCHIVED from v$log;

Step 4: If file is archived execute the following command.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Clearing an inactive, online redo log group that has been archived:

Step 1: Start Dtabase is mount

Step 2: Check the V$LOG view to check the file status archived/Not

SQL> select GROUP#,MEMBERS,STATUS, ARCHIVED from v$log;

Step 3: Check V$DATAFILE to determine if there is an any offline datafiles that requires the cleared log to bring it online.

Step 4: execute the ALTER DATABASE CLEAR LOGFILE command the key words UNRECOVERABLE DATAFILE are required.

The datafile and it’s entire tablespace must be dropped from the database because the redo necessary to bring it online is being cleared and there is no copy of it.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 UNRECOVERABLE DATAFILE;

Step 5: After the activity full offline backup is required.

Scenario 2: Loss of a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Step 1: Check the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible

SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID';

Step 2: Drop the damaged member

SQL> ALTER DATABASE DROP LOGFILE MEMBER ' /u03/apps/oracle/oradata/PROD/redo04.log';

Step 3: Add the new member into the group

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u03/apps/oracle/oradata/PROD/redo004.log'' TO GROUP 4;

Step 4: If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE

ALTER DATABASE ADD LOGFILE MEMBER '/u03/apps/oracle/oradata/PROD/redo004.log'' REUSE TO GROUP 2;

Step 5: Determine if a full offline backup is required and perform one if necessary.

Scenario 3: Loss of online redo log group after shutdown

Step 1: Start Dtabase is mount

Step 2: Check the V$LOG view to see if the deleted log is current.

Step 3: Review the alert.log file and any relevant trace files.

Step 4: If the missing log is current they should simply perform dummy recovery and then open resetlogs

sql> connect <username>/<password> as sysdba

sql> startup mount

sql> recover database until cancel;

(cancel immediately)

sql> alter database open resetlogs;

Reference

RMAN Overview

Oracle Redo log Overview

Loss of a Non system Data file recovery scenarios

Overview of RMAN Recovery Catalog

Archive log Basic