Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Examples of Recovery Procedures

This section describes how to recover from common media failures, and includes the following topics:

Types of Media Failures

Media failures fall into two general categories: permanent and temporary. Permanent media failures are serious hardware problems that cause the permanent loss of data on the disk. Lost data cannot be recovered except by repairing or replacing the failed storage device and restoring backups of the files stored on the damaged storage device. Temporary media failures are hardware problems that make data temporarily inaccessible; they do not corrupt the data. Following are two examples of temporary media failures:

Loss of Datafiles

If a media failure affects datafiles of a database, the appropriate recovery procedure depends on the archiving mode of the database, the type of media failure, and the exact files affected by the media failure. The following sections explain the appropriate recovery strategies in various situations.

Loss of Datafiles, NOARCHIVELOG Mode

If either a permanent or temporary media failure affects any datafiles of a database operating in NOARCHIVELOG mode, Oracle automatically shuts down the database. Depending on the type of media failure, you can use one of two recovery paths:

Loss of Datafiles, ARCHIVELOG Mode

If either a permanent or temporary media failure affects the datafiles of a database operating in ARCHIVELOG mode, the following situations can exist:

Loss of Online Redo Log Files

If a media failure has affected the online redo log of a database, the appropriate recovery procedure depends on the configuration of the online redo log (mirrored or non-mirrored), the type of media failure (temporary or permanent), and the types of online redo log files affected by the media failure (current, active, not yet archived, or inactive online redo log files). The following sections describe the appropriate recovery strategies in various situations.

Loss of an Online Redo Log Member of Mirrored Online Redo Log

If the online redo log of a database is mirrored, and at least one member of each online redo log group is not affected by the media failure, Oracle allows the database to continue functioning as normal (error messages are written to the LGWR trace file and ALERT file of the database). However, you should handle the problem by taking one of the following actions:

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

Loss of All Online Redo Log Members of an Online Redo Log Group

If all members of an online redo log group are damaged by a media failure, different situations can arise, depending on the type of online redo log group affected by the failure and the archiving mode of the database. You can locate the filename in V$LOGFILE, and then look for the group number corresponding to the one you lost to verify the lost file's status (verify that it was inactive).

SELECT *
FROM v$logfile
;
GROUP#     STATUS          MEMBER
------------------------------------------------
0001                        log1
0002                        log2
0003                        log3
SELECT *
FROM v$log
;
GROUP#  MEMBERS            STATUS      ARCHIVED
-------------------------------------------------
 0001     1                  INACTIVE     YES
 0002     1                  ACTIVE       YES
 0003     1                  CURRENT      NO

Loss of an Inactive, Online Redo Log Group If all members of an inactive online redo log group are damaged, the following situations can arise:

To Recover From Loss of an Inactive, Online Redo Log Group

Note: The ALTER DATABASE CLEAR LOGFILE command could fail (with an I/O error due to media failure) in two cases:

You can now open the database.

Loss of an Active Online Redo Log Group If your database is still running and the lost active log is not the current log, you can use the ALTER SYSTEM CHECKPOINT command. If successful, your active log is rendered inactive, and you can follow the steps [*].

If unsuccessful, or if your database has already halted, you cannot use the steps [*]. Instead, perform the following tasks:

To Recover From Loss of an Active Online Redo Log Group

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, use the recovery method for the most difficult log to recover. The order of difficulty, from most difficult to least, follows:

1. the current online redo log

2. the active online redo log

3. the unarchived redo log

4. the inactive online redo log

Loss of Archived Redo Log Files

If the database is operating so that filled online redo log groups are being archived, and the only copy of an archived redo log file is damaged, it does not affect the present operation of the database. However, the following situations can arise if media recovery is required in the future:

Warning: If you know that an archived redo log group has been damaged, immediately backup all datafiles so that you will have a complete backup that does not require the damaged archived redo log.

Loss of Control Files

If a media failure has affected the control files of a database (whether control files are mirrored or not), the database continues to run until the first time that an Oracle background process needs to access the control files. At this point, the database and instance are automatically shut down.

If the media failure is temporary and the database has not yet shut down, immediately correcting the media failure can avoid the automatic shut down of the database. However, if the database shuts down before the temporary media failure is corrected, you can restart the database after fixing the problem (and restoring access to the control files).

The appropriate recovery procedure for media failures that permanently prevent access to control files of a database depends on whether you have mirrored the control files. The following sections describe the appropriate procedures.

Loss of a Member of a Mirrored Control File

Use the following steps to recover a database after one or more control files of a database have been damaged by a permanent media failure, and at least one control file has not been damaged by the media failure:

Note: If all control files of a mirrored control file configuration have been damaged, follow the instructions for recovering from the loss of non-mirrored control files.

To Recover a Database After Control Files Are Damaged

Loss of All Copies of the Current Control File

If all control files of a database have been lost or damaged by a permanent media failure, but all online redo logfiles remain intact, you can recover by creating a new control file (using the CREATE CONTROLFILE command with the NORESETLOGS option). Then execute RECOVER DATABASE followed by ALTER DATABASE OPEN.

Depending on the existence and currency of a control file backup, you have the following options for generating the text of the CREATE CONTROLFILE command:

Recovery From User Errors

An accidental or erroneous operational or programmatic change to the database can cause loss or corruption of data. Recovery may require a return to a state prior to the error.

Note: If the database administrator has properly granted powerful privileges (such as DROP ANY TABLE) to only selected, appropriate users, user errors that require database recovery are minimized.

To Recover Data Lost or Corrupted by User Error

1. Back up the existing, intact database.

2. Leave the existing database intact, but reconstruct a temporary copy of the database up to the time of the user error using time-based recovery.

3. Export the lost or corrupted data from the reconstructed, temporary copy of the database.

4. Import the lost or corrupted data into the permanent database.

5. Delete the files associated with the temporary copy of the reconstructed database to conserve disk space.

The following scenario describes how to recover a table that has been accidentally dropped.

1. The database that experienced the user error can remain online and available for normal use. The database can remain open or be shut down. Back up all datafiles of the existing database in case an error is made during the remaining steps of this procedure.

2. Create a temporary copy of the database to a past point-in-time using time-based recovery. Be careful not to cause a conflict with the existing control file of the permanent database. Restore a single control file backup to an alternative location (step 4) and edit the parameter file, as necessary, or create a new control file at the alternative location. Also, restore all datafiles to alternative locations (step 5) so that you do not affect the permanent copy of the database.

3. Export the lost data using the Oracle utility Export from the temporary, restored version of the database. In this case, export the accidentally dropped table.

Note: System audit options are exported.

4. Import the exported data (step 3) into the permanent copy of the database using the Oracle Import utility.

5. Delete the files of the temporary, reconstructed copy of the database to conserve space.

See Also: For more information about the Import and Export utilities, see Oracle7 Server Utilities.

Contents Index Home Previous Next