Email: service@parnassusdata.com 7 x 24 online support!
How to Recover Oracle Database from Loss Of Online Redo Log And ORA-312 And ORA-313
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:
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID';
GROUP# STATUS MEMBER
------- ----------- ---------------------
0002 INVALID /<redo log path>/<redo log name>
+ Drop the damaged member.
For example, to drop redo log member from group 2, issue:
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/<redo log path>/<redo log name>';
+ Add a new member to the group.
For example, to add new redo log member to group 2, issue:
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/<redo log path>/<redo log name>' TO GROUP 2;
+ 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:
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/<redo log path>/<redo log name>' REUSE TO GROUP 2;
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:
ALTER DATABASE CLEAR LOGFILE GROUP 2;
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:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
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:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 UNRECOVERABLE DATAFILE;
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:
ora-313 open failed for members of log group 2 of thread 1.
ora-312 online log 2 thread 1 'filename'
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
sql> connect <username>/<password> as sysdba
sql> startup mount
sql> recover database until cancel;
(cancel immediately)
sql> alter database 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
NOTE: If the current online log, needed for instance recovery, is lost, the database must be restored and recovered through the last available archivelog file(PITR).