oracle 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
Table 6-1 displays V$LOG status information that can be crucial in a recovery situation involving online redo logs.

Table 6-1 STATUS Column of V$LOG  
Status Description
UNUSED
The online redo log has never been written to.

CURRENT
The log is active, that is, needed for instance recovery, and it is the log to which Oracle is currently writing. The redo log can be open or closed.

ACTIVE
The log is active, that is, needed for instance recovery, but is not the log to which Oracle 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.


The following sections describe the appropriate recovery strategies for these situations:

Recovering After Losing a Member of a Multiplexed Online Redo Log Group
Recovering After the Loss of All Members of an Online Redo Log Group
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 Oracle allows the database to continue functioning as normal. Oracle writes error messages to the LGWR trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following actions:

If the hardware problem is temporary, then correct it. LGWR 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.


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

--------------------------------------------------------------------------------


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:
SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE
WHERE STATUS='INVALID';

GROUP#    STATUS       MEMBER
-------   -----------  ---------------------
0002      INVALID       /oracle/dbs/log2b.f


Drop the damaged member. For example, to drop member log2b.f from group 2, issue:
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log2b.f';


Add a new member to the group. For example, to add log2c.f to group 2, issue:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.f' 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:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.f' REUSE TO GROUP 2;

Recovering After the Loss of All Members of an Online Redo Log Group
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 the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.

If the group is . . . Then . . . And you should . . .
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 restore a backup and perform incomplete recovery up to the most recent available log.

Current
It is the log that Oracle is currently writing to
Attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available log.


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

To determine whether the damaged groups are active:

Locate the filename 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.f
0001                    /oracle/dbs/log1b.f
0002      INVALID       /oracle/dbs/log2a.f
0002      INVALID       /oracle/dbs/log2b.f
0003                    /oracle/dbs/log3a.f
0003                    /oracle/dbs/log3b.f


Determine which groups are active. For example, enter:
SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

GROUP#  MEMBERS           STATUS     ARCHIVED
------  -------           ---------  -----------
0001   2                 INACTIVE   YES
0002   2                 ACTIVE     NO
0003   2                 CURRENT    NO


If the affected group is inactive, 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
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 . . . Then . . .
Temporary
Fix the problem. LGWR can reuse the redo log group when required.

Permanent
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.


You can clear an active 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;


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

Clearing an unarchived 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.

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 LOGFILE UNARCHIVED GROUP 2;


If there is an offline datafile that requires the cleared unarchived 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 LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;


Immediately back up the database with an operating system utility as described in "Making User-Managed Backups of the Whole Database". Now you can use this backup 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 using the ALTER DATABASE statement as described in "Backing Up the Control File to a Binary File". For example, enter:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

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.

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

Note that the current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.

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

If the media failure is temporary, then correct the problem so that Oracle can reuse the group when required.
Restore the database from a consistent, whole database backup (datafiles and control files) as described in "Restoring Datafiles". For example, enter:
% cp /disk2/backup/*.f /disk1/oracle/dbs


Mount the database:
STARTUP MOUNT


Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow Oracle to reset the online redo logs, you must first mimic incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL


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 as described in "Making User-Managed Backups of the Whole Database". For example, enter:
% cp /disk1/oracle/dbs/*.f /disk2/backup


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

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

Begin incomplete media recovery. Use the procedure given in "Performing Incomplete User-Managed 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 "/oracle/dbs/log_1.rdo" TO "/temp/log_1.rdo";
ALTER DATABASE RENAME FILE "/oracle/dbs/log_2.rdo" TO "/temp/log_2.rdo";


Open the database using the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;


--------------------------------------------------------------------------------
Note:
All updates executed from the endpoint 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, follows:

The current online redo log
An active online redo log
An unarchived online redo log
An inactive online redo log