Recovering from Losing Online Redo Log Files: Scenarios (2023)

When a media failure has affected a database's online redo logs, the appropriate recovery procedure depends on the following:

Table 19-1indicatesV$LOGStatus information that can be critical in a recovery situation with online redo logs.

Recovering from the loss of a member of a multiplexed online redo log group

When a database's online redo log is multiplexed and at least one member of each online redo log group is unaffected by the media failure, the database continues to function normally, but error messages are written to the log writer trace file and theAlarm_SID.Protocolthe database.

Resolve the issue by doing one of the following:

If the hardware problem is temporary, fix 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, delete the corrupted member and add a new member using the following procedure.
Note:
The newly added member provides no redundancy until the log group is recycled.

Locate the filename of the corrupted member inV$LOGDATEI. The booth isINVALIDif the file cannot be accessed:

SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILEWHERE STATUS='INVALID';GROUP# STATUS MEMBER------- ----------- ----------- ----------0002 UNGÜLTIG /oracle/oradata/trgt/redo02.log

Drop the damaged member. For example, to delete a memberredo01.logfrom group2, Output:
```
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';
```
Add a new member to the group. Add for exampleredo02.logto group2, Output:
```
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log' TO GROUP 2;
```
If the file you want to add already exists, it must be the same size as the other group members and you must specify itREUSE. For example:
(Video) Recovery from the LOSS of REDO log files
```
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log' REUSE IN GROUP 2;
```

Recover after losing all members of an online redo log group

When a media failure corrupts all members of an online redo log group, different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the corrupted log group is active, it is required for crash recovery; otherwise it is not.

When the group is . .	Then . . .	And you should. . .
Inactive	It is not required for crash recovery	Delete the archived or unarchived group.
Active	It is required for crash recovery	Try issuing a checkpoint and clearing the log; If this is not possible, you must restore a backup and perform an incomplete restore to the latest redo log available.
Currently	It is the log that the database is currently writing to	Try clearing the log; If this is not possible, you must restore a backup and perform an incomplete restore to the latest redo log available.

Your first task is to determine whether the corrupted group is active or inactive.

Locate the filename of the lost redo applicationV$LOGDATEIand then look for the corresponding group number.For example, enter:

SELECT GROUP NO., STATUS, MEMBER FROM V$LOGFILE;GROUP NO. STATUS MEMBER ------- ----------- ---------------- -----0001 /oracle/dbs/log1a.f0001 / oracle/dbs/log1b.f0002 INVALID /oracle/dbs/log2a.f0002 INVALID /oracle/dbs/log2b.f0003 /oracle/dbs/log3a.f0003 /oracle /dbs/log3b.f

Determine which groups are active. For example, enter:

SELECT GROUP NO., MEMBERS, STATUS, ARCHIVED FROM V$LOG;GROUP NO. MEMBER STATUS ARCHIVED------ ------- --------- --------- -- 0001 2 INACTIVE YES 0002 2 ACTIVE NO 0003 2 CURRENT NO

If the affected group is inactive, follow the procedure inLoss of an inactive online redo log group. If the affected group is active (as in the previous example), follow the procedure in"Loss of an active online redo log group".

Loss of an inactive online redo log group

If all members of an online redo log group are usingINACTIVEStatus are corrupted, what to do depends on whether you can fix the media problem that corrupted the inactive redo log group.

If the error is . .	Then . . .
In the interim	To solve the Problem. LGWR can reuse the redo log group if needed.
Continuous	The corrupted inactive online redo log group eventually halts normal database operations. Manually reinitialize the corrupted group by issuing the`CHANGE` `DATABASE` `CLEAR` `LOGFILE`instruction as described in this section.

Delete inactive archived redo

You can delete an inactive redo log group when the database is open or closed. The procedure depends on whether the corrupted group was archived.

(Video) Recovery of Redo log file using Log Sequence number by RMAN

To delete an archived inactive online redo log group, do the following:

When the database is down, start a new instance and mount the database:
```
INSTALLATION
```
Reinitialize the corrupted log group. For example, to delete the redo log group2, make the following statement:
```
CHANGE DATABASE DELETE LOGFILE GROUP 2;
```

Delete inactive, not yet archived redo

Deleting a redo log that has not yet been archived allows it to be reused without archiving it. This action renders backups unusable if they were started before the last change in the log, unless the file was taken offline before the first change in the log. So if you need the deleted log file to restore a backup, you cannot restore that backup. Also, it prevents full restore of backups due to the missing log.

To delete an inactive online redo log group that has not been archived, do the following:

When the database is down, start a new instance and mount the database:
```
INSTALLATION
```
Clear the log withNOT ARCHIVEDKeyword. For example, to delete a log group2, Output:
```
CHANGE DATABASE DELETE LOGFILE NON-ARCHIVED GROUP 2;
```
If there is an offline data file that requires the deleted log to bring it online, then the keywordsNOT RECOVERABLE TO THE DATEare required. The data file and its entire table space must be dropped because the redo required to bring it online is being dropped and no copy of it exists. For example, enter:
```
CHANGE DATABASE DELETE LOG FILE UNARCHIVED GROUP 2 DATA FILE UNRECOVERABLE;
```
Immediately back up the entire database using an operating system utility so that you have a backup that you can use for a full restore without having to rely on the deleted log group. For example, enter:
```
% cp /disk1/oracle/dbs/*.f /disk2/backup
```
Backup the database control file with theCHANGE DATABASEExpression. For example, enter:
```
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';
```

Error in CLEAR LOGFILE operation

ThatCHANGE DATABASE CLEAR LOGFILEstatement may fail with an I/O error due to a media error if the following is not possible:

(Video) Recovery when control files, datafiles and redo logs are lost

Move the redo log file to alternate media by recreating it under the currently configured redo log filename
Reuse the currently configured log file name to recreate the redo log file because the name itself is invalid or unusable (e.g. due to media failure).

In these cases, theCHANGE DATABASE CLEAR LOGFILEstatement (before the I/O error was received) would have successfully informed the control file that the log was cleared and no archiving was required. The I/O error occurred at the step where theCLEAR LOGFILEstatement attempts to create the new redo log file and write zeros to it. This fact is reflected inV$LOG.CLEARING_CURRENT.

Loss of an active online redo log group

If the database is still running and the active redo log has been lostNotthe current log, then output theCHANGE SYSTEM CHECKPOINTExpression. If successful, the active redo log becomes inactive and you can follow the procedure in"Loss of an inactive online redo log group". If that doesn't work, or your database is stopped, perform one of the procedures in this section, depending on your archiving mode.

The current log is the one that LGWR is currently writing to. If an LGWR I/O fails, LGWR terminates and the instance crashes. In this case you need to restore a backup, do an incomplete restore and open the database withreset logsPossibility.

To recover from the loss of an online active log group in NOARCHIVELOG mode:

If the disk error is transient, fix the problem so the database can reuse the group if necessary.
Restore the database from a consistent, full database backup (data files and control files) as described in"Recovering data files before performing an incomplete recovery". For example, enter:
```
% cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/
```
Mount the database:
```
INSTALLATION
```
Because online redo logs are not backed up, you cannot restore them with the data files and control files. In order for the database to roll back the online redo logs, you must first mimic an incomplete restore:
```
RESTORE DATABASE UNTIL CANCEL
```
Open the database with thereset logsPossibility:
```
ALTER DATABASE OPEN RESETLOGS;
```
Shut down the database consistently. For example, enter:
(Video) How to choose the proper Size for online redo log files using redo log advisor.
```
TURN OFF IMMEDIATELY
```
Create a full database backup.

To recover from the loss of an active online redo log group in ARCHIVELOG mode:

If the disk error is transient, fix the problem so the database can reuse the group if necessary. If the media error is not transient, use the following procedure.

Start incomplete media recovery by restoring log to corrupted log.
Ensure that the current name of the lost redo log can be used for a newly created file. If not, rename the corrupt online redo log group members to a new location. For example, enter:
```
ALTER DATABASE RENAME FILE „?/oradata/trgt/redo01.log“ TO „/tmp/redo01.log“; ALTER DATABASE RENAME FILE „?/oradata/trgt/redo01.log“ TO „/tmp/redo02.log“;
```
Open the database with thereset logsPossibility:
```
ALTER DATABASE OPEN RESETLOGS;
```
Note:
All updates made from the incomplete recovery endpoint to the present must be redone.

Loss of multiple redo log groups

If you lost several sets of online redo log, use the recovery method for the most difficult log to recover. The order of difficulty, from most difficult to least difficult, follows:

The current online redo log
An active online redo log
(Video) RMAN Recovery Scenario - system datafile corrupted or lost - What to do?
An unarchived online redo log
An inactive online redo log