[Recovery, 1] various situations of redo log recovery

Authorization, thentheappropriaterecoveryproceduredependsonthefollowingconsiderations: Theconfigurationoftheonlineredolo

Recovering After the Loss of Online Redo Log Files If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following considerations: the configuration of the online redo lo

Recovering After the Loss of Online Redo Log Files

If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following considerations:

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

Table 30-3 displays V $ LOG status information that can be crucial in a recovery ation situation involving online redo logs.

Table 30-3 STATUS Column of V $ LOG

Status	Description
UNUSED	The online redo log has never been written.
CURRENT	The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
ACTIVE	The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing. it may be in use for block recovery, and may or may not be archived.
CLEARING	The log is being re-created as an empty log after an alter database clear logfile statement. After the log is cleared, then the status changes to UNUSED.
CLEARING_CURRENT	The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE	The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.


Recovering After Losing a Member of a Multiplexed Online Redo Log Group

You can recover after losing a member of a multiplexed online redo log group. The database continues to function as usual during the following conditions:

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 usual, but error messages are written to the log writer trace file and the alert_SID.log of the database.

You can resolve the problem of a missing member of a multiplexed online redo log group by taking one of the following actions:

If the hardware problem is temporary, then correct it. The log writer process accesses the previusly 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.

Note:

The newly added member provides no redundancy until the log group is reused.

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

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

Drop the damaged member. For example, to drop member redo02.log from group 2, issue the following statement:

ALTER DATABASE DROP LOGFILE MEMBER '/disk1/oradata/trgt/redo02.log';

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

ALTER DATABASE ADD LOGFILE MEMBER '/disk1/oradata/trgt/redo02b.log'   TO GROUP 2;

If the file that you want to add exists, then it must be the same size as the other group members, and you must specify the REUSE option. For example:

ALTER DATABASE ADD LOGFILE MEMBER '/disk1/oradata/trgt/redo02b.log'  REUSE TO GROUP 2;

Recovering After Losing All Members of an Online Redo Log Group

Restore an online redo log group after all its members are lost.

If a media failure damages all members of an online redo log group, then 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 a media fault damages all log group members, failure in different scenarios depends on the log group type and database archiving mode.

If the damaged online redo log group is current and active, then it is needed for crash recovery; otherwise, it is not. Table 30-4 outlines the varous recovery scenarios.

If the current and active log groups are damaged, fault recovery is required.

Table 30-4 Recovering After the Loss of an Online Redo Log Group

If the Group Is...	Then...	And You shoshould...
Inactive	It is not needed for crash recovery	Clear the archived or unarchived group.
Active	It is needed for crash recovery	Attempt to issue a checkpoint and clear the log; if impossible, then you must either use Flashback Database or restore a backup and perform incomplete recovery up to the most recent available redo log. Try to execute the checkpoint and clear logs. If not, you must either flash back to the database or use the dump backup to execute Incomplete recovery to the latest available redo logs.
Current	It is the redo log that the database is currently writing	Attempt to clear the log; if impossible, then you must either use Flashback Database or restore a backup and perform incomplete recovery up to the most recent available redo log.




To determine whether the damaged group is active or inactive.

Determine whether the damaged group is acitve or inactive

Locate the file name of the lost redo log in V $ LOGFILE and then look for the group number corresponding to it. For example, enter:

SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;GROUP#    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, execute the following SQL query (sample output sorted DED ):

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

Perform one of the following actions:

If the affected group is inactive, then follow the procedure in "Losing an Inactive Online Redo Log Group ".

If the affected group is active (as in the preceding example), then follow the procedure in "Losing an Active Online Redo Log Group ".



Losing an Inactive Online Redo Log Group

Lost inactive 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. the log writer 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. reinitialize the damaged group manually by issuing the alter database clear logfile statement as described in this section.

All inactive log group members are damaged. The program depends on whether you can locate the inactive log group's media problem (whether the log file is damaged). If the media fault is temporary, log writer can reuse the log group. If a media fault is permanent, the damaged inactive log Group stops normal database operations. Run the alter database clear logfile statement to manually reinitialize the damaged log group.

Clearing Inactive, Archived Redo

Clear inactive archived logs

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.

You can clear the inactive log group when the database is enabled or disabled. Depends on whether the corrupted log group is 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, Unarchived Redo

Clear inactive Unarchived logs

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 before 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. clearing a not-yet-archived-redo-log, prevents complete recovery from backups due to the missing log.

Logs that have not been archived can be reused. If the log is started before the last change, this operation will cause the backup to be unavailable, unless the log file is set to offline before the first change. After that, if you need to clear the logs for Backup recovery, you cannot back up and restore them.

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

Clear an inactive log. The log is not archived:

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

SQL> STARTUP MOUNT

Clear the log using the UNARCHIVED keyword.

For example, to clear log group 2, issue the following SQL statement:

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

If there is an offline data file that requires the cleared log to bring it online, then the keywords unrecoverable datafile are required. the data file must be dropped because the redo logs necessary to bring the data file online are being cleared, and there is no copy of it. for example, enter:

If there is a data file offline, you need to clear the log to bring the data file online, then you need to use the keyword unrecoverable datafile. The data file must be deleted because

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

Immediately back up all data files in the 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:

Back up all the data files immediately using the operating system tool, so that you have a log group that does not depend on the cleared log group to perform full recovery backup,

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

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

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

Failure of clear logfile Operation Failed to clear log files


The alter database clear logfile statement can fail with an I/O error due to media failure when it is not possible:

If the alter database clear logfile statement cannot perform the following operations, the I/O errors caused by media faults will fail,

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

Create a new log file using the name of the currently configured log file to migrate the log to another media

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

Re-use the name of the current configuration log file to re-create the log file because its own name is invalid or unavailable.

In these cases, the alter database clear logfile statement (before loading ing the I/O error) wocould 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 attempted to create the new redo log file and write zeros to it. this fact is reflected in V $ LOG. CLEARING_CURRENT.

In these cases, the alter database clear logfile statement

Losing an Active Online Redo Log Group

If the database is still running and the lost active redo log is not the current log, then issue the alter system checkpoint statement. if the operation is successful, then the active redo log becomes inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group ". if the operation is unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.

The current log is the one LGWR is currently writing. if a lgwr I/O operation fails, then LGWR terminates and the instance fails. in this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.

Recovering from the Loss of Active Logs in NOARCHIVELOG Mode

In this scenario, the database archiving mode is NOARCHIVELOG.

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

If the media failure is temporary, then correct the problem so that the database can reuse the group when required.

Restore the database from a consistent, whole database backup (data files and control files). For example, enter:

% cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/

Mount the database:

STARTUP MOUNT

Because online redo logs are not backed up, you cannot restore them with the data files and control files. To allow the database to reset the online redo logs, you must first mimic incomplete recovery:

RECOVER DATABASE UNTIL CANCELCANCEL

Open the database using the RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS;

Shut down the database consistently. For example, enter:

SHUTDOWN IMMEDIATE

Make a whole database backup.

If the media failure is temporary, then correct the problem so that the database can reuse the group when required. If the media failure is not temporary, then use the following procedure.

Recovering from Loss of Active Logs in ARCHIVELOG Mode

In this scenario, the database archiving mode is ARCHIVELOG.

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

Begin incomplete media recovery, recovering up through the log before the damaged log.

Ensure that the current name of the lost redo log can be used for a newly created file. if not, then rename the members of the damaged online redo log group to a new location. for example, enter:

ALTER DATABASE RENAME FILE "/disk1/oradata/trgt/redo01.log" TO "/tmp/redo01.log";ALTER DATABASE RENAME FILE "/disk1/oradata/trgt/redo02.log" TO "/tmp/redo02.log";

Open the database using the RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS;

Note:

All updates executed from the end point of the incomplete recovery to the present must be re-executed.Loss of Multiple Redo Log Groups

If you have lost multiple groups of the online redo log, then use the recovery method for the most difficult log to recover. the order of difficulty, from most difficult to least difficult, is as follows:

The current online redo log

An active online redo log

An unarchived online redo log

An inactive online redo log


Source: >