Purpose

This article aims at walking you through some of the common
recovery techniques after a loss of Online Redolog

Scope

All Oracle support Analysts, DBAs and Consultants who have a role
to play in recovering an Oracle database

Details

Scenario

You have a database in archive log mode, shutdown immediate and deleted
one of the online redo logs, in this case there are only 2 groups with
1 log member in each.  When you try to open the database you receive
the following errors:

It is not possible to recover the missing log, so the following needs to be performed!

Mount the database and check v$log to see if the deleted log is current;

  - If the log is not current, simply drop the log group (alter database drop logfile group N).
    If there are only 2 log groups then it will be necessary to add another group before dropping this one.

  - If the log is current they should simply perform fake recovery  and then open resetlogs

 
The database will open up as required, providing the log file 'Directory' is available.  
If not available then create it and rerun the resetlogs else this will give error

    ora-344   unable to recreate online log



Recovering After the Loss of Online Redo Log Files: Scenarios

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


1) Recovering After Losing 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.



ACTION PLAN

If the hardware problem is temporary, then correct it. The log writer process accesses the previously
unavailable online redo log files as if the problem never existed.

If the hardware problem is permanent, then drop the damaged member and add a new member by using the
following procedure.




To replace a damaged member of a redo log group:

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

 
+ Drop the damaged member.
  For example, to drop member redo01.log from group 2, issue:

+ Add a new member to the group.
  For example, to add redo02.log to group 2, issue:

+ 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.
  For example:

2) Losing an Inactive Online Redo Log Group

If all members of an online redo log group with INACTIVE status are damaged, then the procedure
depends on whether you can fix the media problem that damaged the inactive redo log group.

If the failure is ... Temporary... then Fix the problem. LGWR can reuse the redo log group when required.
If the failure is ... Permanent... then the damaged inactive online redo log group eventually halts normal database operation.



ACTION PLAN

Reinitialize the damaged group manually by issuing the "ALTER DATABASE CLEAR LOGFILE"
You can clear an inactive redo log group when the database is open or closed.
The procedure depends on whether the damaged group has been archived.

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

If the database is shut down, then start a new instance and mount the database:
STARTUP MOUNT

Reinitialize the damaged log group.
For example, to clear redo log group 2, issue the following statement:

Clearing Inactive, Not-Yet-Archived Redo

Clearing a not-yet-archived redo log allows it to be reused without archiving it. This action makes
backups unusable if they were started before the last change in the log, unless the file was taken
offline prior to the first change in the log.
Hence, if you need the cleared log file for recovery of a backup, then you cannot recover that backup.
Also, it prevents complete recovery from backups due to the missing log.

To clear an inactive, online redo log group that has not been archived:

If the database is shut down, then start a new instance and mount the database:
STARTUP MOUNT


Clear the log using the UNARCHIVED keyword. For example, to clear log group 2,
issue:

If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required.
The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared,
and there is no copy of it.
For example enter:

Note:  If this is performed on an Active (current) logfile then the following error may occur:

 
Immediately back up the whole database with an operating system utility, so that you have a backup you can use
for complete recovery without relying on the cleared log group.
For example, enter:

% cp /disk1/oracle/dbs/*.f /disk2/backup

Back up the database's control file with the ALTER DATABASE statement. For
example, enter:

 

Failure of CLEAR LOGFILE Operation

The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to
media failure when it is not possible to:

* Relocate the redo log file onto alternative media by re-creating it under
  the currently configured redo log filename

* Reuse the currently configured log filename to re-create the redo log file
  because the name itself is invalid or unusable (for example, due to media failure)

In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving the I/O error) would
have successfully informed the control file that the log was being cleared and did not require archiving.

The I/O error occurred at the step in which the CLEAR LOGFILE statement attempts to create the new
redo log file and write zeros to it. This fact is reflected in V$LOG.CLEARING_CURRENT.