If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Oracle Database - Standard Edition - Version 9.0.1.0 and later

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Oracle Database - Personal Edition - Version 9.0.1.0 and later

Generic UNIX



***Checked for relevance on 29-Mar-2012*** 

PURPOSE

This article aims at walking you through some of the common recovery scenarios 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

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

- The database was shutdown normally before loss of archivelog file

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:

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 an error will occur:

Immediately back up the whole database including controlfile, so that you have a backup you can use for complete recovery without relying on the cleared log group. 

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.

3) Loss of online logs after normal shutdown 

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 missing 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 missing log is current they should simply perform fake recovery and then open resetlogs

Be sure the location (directory) for the online log files exists before trying to open the database.  If not available then create it and rerun the resetlogs else this will give error