Oracle7 Server Administrator's Guide

Preparing for Media Recovery

This section describes issues related to media recovery preparation, and includes the following topics:

Media Recovery Commands

Issues Common to All Media Recovery Operations

Restoring a Full Backup, NOARCHIVELOG Mode

Specifying Parallel Recovery

See Also: For information about the appropriate method of recovery for each type of problem, see "Examples of Recovery Procedures"

Media Recovery Commands

There are three basic media recovery commands, which differ only in the way the set of files being recovered is determined. They all use the same criteria for determining if files can be recovered. Media recovery signals an error if it cannot get the lock for a file it is attempting to recover. This prevents two recovery sessions from recovering the same file. It also prevents media recovery of a file that is in use. You should be familiar with all media recovery commands before performing media recovery.

RECOVER DATABASE Command

RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. If all instances were cleanly shutdown, and no backups were restored, RECOVER DATABASE indicates a no recovery required error. It also fails if any instances have the database open (since they have the datafile locks). To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.

RECOVER TABLESPACE Command

RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. To translate the tablespace names into datafile names, the database must be mounted and open. The tablespaces must be offline to perform the recovery. An error is indicated if none of the files require recovery.

RECOVER DATAFILE Command

RECOVER DATAFILE lists the datafiles to be recovered. The database can be open or closed, provided the media recovery locks can be acquired. If the database is open in any instance, then datafile recovery can only recover offline files.

See Also: For more information about recovery commands, see the Oracle7 Server SQL Reference guide.

Issues Common to All Media Recovery Operations

This section describes topics common to all complete and incomplete media recovery operations. You should be familiar with these topics before proceeding with any recovery process.

Determining Which Files to Recover

You can often use the table V$RECOVER_FILE to determine which files to recover. This table lists all files that need to be recovered, and explains why they need to be recovered.

Note: The table is not useful if the control file currently in use is a restored backup or a new control file created since the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$RECOVER_FILE accurately.

The following query displays the file ID numbers of datafiles that require recovery:

SELECT file#, online, error

   FROM v$recover_file;

FILE#       ONLINE     ERROR

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

   0014     ONLINE

   0018     ONLINE     FILE NOT FOUND

   0032     OFFLINE    OFFLINE NORMAL

...

Use the data dictionary view V$DATAFILE, which contains the file's NAME and FILE#, to find the name of a file based on its file number.

Restoring Damaged Datafiles

If a media failure permanently damages one or more datafiles of a database, you must restore backups of the damaged datafiles before you can recover the damaged files.

Relocating Damaged Files If a damaged datafile cannot be restored to its original location (for example, a disk must be replaced, so the files are restored to an alternate disk), the new locations of these files must be indicated to the control file of the associated database. Therefore, use the procedure given in "Renaming and Relocating Datafiles" .

Recovering a Datafile Without a Backup If a datafile is damaged and no backup of the file is available, the datafile can still be recovered if:

all log files written since the creation of the original datafile are available

the control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

Use the CREATE DATAFILE clause of the ALTER DATABASE command to create a new, empty datafile, replacing a damaged datafile that has no corresponding backup. However, you cannot create a new file based on the first datafile of the SYSTEM tablespace because it contains information not covered by redo logs. For example, assume that the datafile "disk1:users1" has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk 2:

ALTER DATABASE CREATE DATAFILE 'disk1:users1' AS 'disk2:users1';

Note: The old datafile is renamed as the new datafile when an ALTER DATABASE CREATE DATAFILE statement is executed.

This statement enables you to create an empty file that matches the lost file. Oracle looks at information in the control file and the data dictionary to obtain size information. Next, you must perform media recovery on the empty datafile. All archived redo logs written since the original datafile was created must be mounted and reapplied to the new, empty version of the lost datafile during recovery. If the database was created in NOARCHIVELOG mode, the original datafiles of the SYSTEM tablespace cannot be restored using an ALTER DATABASE CREATE DATAFILE statement because the necessary archived redo logs are not available.

Restoring Necessary Archived Redo Log Files

All archived redo log files required for the pending media recovery eventually need to be on disk, so that they are readily available to Oracle.

To determine which archived redo log files you need, you can use the tables V$LOG_HISTORY and V$RECOVERY_LOG. V$LOG_HISTORY lists all of the archived logs, including their probable names, given the current archived log file naming scheme (as set by the parameter LOG_ARCHIVE_FORMAT). V$RECOVERY_LOG lists only the archived redo logs that Oracle needs to perform recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT. Be aware that you will need all the redo information from the time the datafile was added to the database.

If space is available, restore all of the required archived redo log files to the location currently specified by the initialization parameter LOG_ARCHIVE_DEST. By doing this, you enable Oracle to locate automatically the correct archived redo log file when required during media recovery. If sufficient space is not available at the location indicated by LOG_ARCHIVE_DEST, you can restore some or all of the required archived redo log files to any disk accessible to Oracle. In this case, you can specify the location of the archived redo log files before or during media recovery.

After an archived log is applied, you can delete the restored copy of the archived redo log file to free disk space. However, make sure that a copy of each archived log group still exists on offline storage.

See Also: For more information about tables, see the Oracle7 Server Reference.

Starting Media Recovery

If a damaged database is in ARCHIVELOG mode, it is a candidate for either complete media recovery or incomplete media recovery operations. To begin media recovery operations, use one of the following options of Server Manager:

the Apply Recovery Archives dialog box

the Server Manager RECOVER command

the SQL command ALTER DATABASE

To start any type of media recovery, you must have administrator privileges. All recovery sessions must be compatible. One session cannot start complete media recovery while another performs incomplete media recovery. Also, you cannot start media recovery if you are connected to the database via a multi-threaded server process.

See Also: For more information on multi-threaded server processes, see .

Recovery Scenarios

The following scenarios describe various ways to invoke media recovery.

Recovering a Closed Database After the database is mounted, but closed, start closed database recovery (complete or incomplete) using either Server Manager's Apply Recovery Archives dialog box, or the RECOVER command with the DATABASE parameter.

The following statement recovers the database up to a specified time using a control file backup:

RECOVER DATABASE

   UNTIL TIME '1992-12-31:12:47:30' USING BACKUP CONTROLFILE;

Recovering an Offline Tablespace in an Open Database After the tablespaces of interest are taken offline, you can start open-database, offline-tablespace recovery using the RECOVER command with the TABLESPACE parameter. You can recover one or more offline tablespaces. The remainder of the database may be left open and online for normal database operation.

The following statement recovers two offline tablespaces:

RECOVER TABLESPACE ts1, ts2;

After the tablespaces that contain the damaged files have been taken offline, and you are positive the associated datafiles are also offline (check the file's status in V$DATAFILE), recover selected datafiles using the RECOVER command with the DATAFILE parameter:

RECOVER DATAFILE 'filename1', 'filename2';

The SQL command equivalent of Server Manager media recovery options is the SQL command ALTER DATABASE command with the RECOVER clause. Generally, database recovery should be performed using Server Manager; which prompts you for information and returns messages from the system. However, if you want to design your own recovery application using SQL commands, use the ALTER DATABASE command.

Starting Recovery During Instance Startup You can start complete media recovery using the STARTUP command with the RECOVER option in Server Manager. After an instance is started, and the database is mounted, complete media recovery proceeds as described in "Complete Media Recovery" .

See Also: For information about taking tablespaces offline, see "Taking Tablespaces Offline" .

For more information about the STARTUP command, see .

Applying Redo Log Files

During complete or incomplete media recovery, redo log files (online and archived) are applied to the datafiles during the roll forward phase of media recovery. Because rollback data is recorded in the redo log, rolling forward regenerates the corresponding rollback segments. Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. As a log file is needed, Oracle suggests the name of the file. For example, if you are using Server Manager, it returns the following lines and prompt:

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for

           thread #

ORA-00289: Suggestion : logfile

ORA-00280: Change #### for thread # is in sequence #

Specify log: [<RET> for suggested | AUTO | FROM logsource |

   CANCEL ]

Similar messages are returned when using an ALTER DATABASE... RECOVER statement. However, no prompt is displayed.

Applying Log Files

This section describes how log files can be applied in different environments.

Suggested Log Filenames Oracle suggests log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT and using information from the control file. Therefore, if all the required archived log files are mounted at LOG_ARCHIVE_DEST, and the value for LOG_ARCHIVE_FORMAT is never altered, Oracle can suggest and apply log files to complete media recovery automatically without your intervention. If the location specified by LOG_ARCHIVE_DEST is not available (for example, because of media failure), you can change the value for this parameter, move the log files to the new location, and start a new instance before beginning media recovery.

In some cases, you might want to override the current setting for LOG_ARCHIVE_DEST as a source for log files. For example, assume that a database is open and an offline tablespace must be recovered, but not enough space is available to mount the necessary log files at the location specified by LOG_ARCHIVE_DEST. In this case, you can mount the log files to an alternate location, then specify the alternate location to Oracle for the recovery operation. To specify the location where required log files can be found, use the LOGSOURCE parameter of the SET command in Server Manager. Use the RECOVER...FROM parameter of the ALTER DATABASE command in SQL.

Note: Overriding the log source does not affect the archive log destination for filled online groups being archived.

Consider overriding the current setting for LOG_ARCHIVE_DEST when not enough space is available to mount all the required log files at any one location. In this case, you can set the log file source to an operating system variable (such as a logical or an environment variable) that acts as a search path to several locations.

See Also: Such functionality is operating system-dependent. See your operating system-specific Oracle documentation for more information.

Applying Log Files when Using Server Manager If the suggested archived redo log file is correct, apply the suggested archived redo log. You do not have to specify a filename unless the suggested file is incorrect. After a filename is provided, Oracle applies the redo log file to roll forward the restored datafiles.

In Server Manager, you can have Oracle automatically apply the redo log files that it suggests by choosing either of the following options:

Before starting media recovery, issue the following Server Manager statement to turn on automatic recovery:

		SET AUTORECOVERY ON;

Automatic application of the suggested redo log starts once recovery begins.

After media recovery is started, enter "auto" when prompted for a redo log file. Automatic application of the suggested redo log starts from this point.

Suggested redo log files are automatically applied until a suggested redo log is incorrect or recovery is complete. You might need to specify online redo log files manually when using cancel-based recovery or a backup of the control file.

See Also: For examples of logfile application, see your operating system-specific Oracle documentation.

Application of Log Files When Using SQL Commands Application of redo log files is similar to the application of log files. However, a prompt for log files is not displayed after media recovery is started. Instead, you must provide the correct log file using an ALTER DATABASE RECOVER LOGFILE statement. For example, if a message suggests LOG1.ARC, you can apply the suggestion using the following statement:

ALTER DATABASE RECOVER LOGFILE 'log1.arc';

As a result, recovering a tablespace requires several statements, as indicated in the following example (DBA input is boldfaced; variable information is italicized.):

> ALTER DATABASE RECOVER TABLESPACE users;

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for

           thread #

ORA-00289: Suggestion : logfile1

ORA-00280: Change #### for thread # is in sequence #

> ALTER DATABASE RECOVER LOGFILE 'logfile1';

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for

           thread #<D%0>

ORA-00289: Suggestion : logfile2

ORA-00280: Change #### for thread # is in sequence #

> ALTER DATABASE RECOVER LOGFILE 'logfile2';

(Repeat until all logs are applied.)

Statement processed.

> ALTER TABLESPACE users ONLINE;

Statement processed.

In this example, it is assumed that the backup files have been restored, and that the user has administrator privileges.

Like the method you used with Server Manager, automatic application of the redo logs can be started with the following statements, before and during recovery, respectively:

ALTER DATABASE RECOVER AUTOMATIC ...;

ALTER DATABASE RECOVER AUTOMATIC LOGFILE suggested_log_file;

An example of the first statement follows:

> ALTER DATABASE RECOVER AUTOMATIC TABLESPACE users;

Statement processed.

> ALTER TABLESPACE users ONLINE;

Statement processed.

In this example, it is assumed that the backup files have been restored, and that the user has administrator privileges.

An example of the ALTER DATABASE RECOVER AUTOMATIC LOGFILE statement follows:

> ALTER DATABASE RECOVER TABLESPACE users;

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for

           thread #

ORA-00289: Suggestion : logfile1

ORA-00280: Change #### for thread # is in sequence #

> ALTER DATABASE RECOVER AUTOMATIC LOGFILE 'logfile1';

Statement processed.

> ALTER TABLESPACE users ONLINE;

Statement processed.

In this example, assume that the backup files have been restored, and that the user has administrator privileges.

Note: After issuing the ALTER DATABASE RECOVER command, you can view all files that have been considered for recovery in the V$RECOVERY_FILE_STATUS view. You can access status information for each file in the V$RECOVERY_STATUS view. These views are not accessible after you terminate the recovery session.

See Also: For information about the content of all recovery-related views, see the Oracle7 Server Reference.

Successful Application of Redo Logs If you are using Server Manager's recovery options (not SQL statements), each time Oracle finishes applying a redo log file, the following message is returned:

Log applied.

Make sure that the message "Log applied" is returned after each application of a redo log file. If the suggested file is incorrect or you provide an incorrect filename, an error message is returned instead. If you see an error message instead of "Log applied," a redo log file required for recovery has not been applied. Recovery cannot continue until the required redo log file is applied.

If an error message is returned after supplying a redo log filename, one of the following errors has been detected:

If the error message says that the file cannot be found, you may have entered the wrong filename. Re-enter the correct filename.

If the redo log file is found, but cannot be opened, then it may be locked. After unlocking the redo log file, re-enter the filename.

If a redo log file is found and opened, but cannot be read, an I/O error is returned. In this case, the redo log file may have been only partially written or may have been corrupted. If you can locate an uncorrupted or complete copy of the log, you can simply apply that copy; you do not need to restart recovery. Otherwise, if no other copy of the log exists and you know the time of the last valid redo entry, you can perform time-based or change-based recovery; in this case, you must restart recovery from the beginning, including restoring backups.

Interrupting Media Recovery

If you start a media recovery operation and must then interrupt it (for example, because a recovery operation must end for the night and resume the next morning), you can interrupt recovery at any time by taking either of the following actions:

Enter the word "cancel" when prompted for a redo log file.

If you must abort when recovering an individual datafile, or when automated recovery is in progress, use your operating system's interrupt signal.

After recovery is canceled, it must be completed before opening a database for normal operation. To resume recovery, restart it. Recovery resumes where it left off when it was canceled.

Warning: There are several reasons why, after starting recovery, you may want to restart. If, for example, you want to restart with a different backup or want to use the same backup, but need to change the end-time to an earlier point-in-time than you initially specified, then the entire operation must recommence by restoring a backup. Failure to do so may result in "file inconsistent" error messages when attempting to open the database.

Restoring a Full Backup, NOARCHIVELOG Mode

If a database is in NOARCHIVELOG mode and a media failure damages some or all of the datafiles, usually the only option for recovering the database is to restore the most recent full backup. If you are using Export to supplement regular backups, you can instead restore the database by importing an exported backup of the database.

The disadvantage of NOARCHIVELOG mode is that to recover your database from the time of the most recent full backup up to the time of the media failure, you have to re-enter manually all of the changes executed in that interval. However, if your database was in ARCHIVELOG mode, the redo log covering this interval would have been available as archived log files or online log files. This would have enabled you to use complete or incomplete recovery to reconstruct your database and minimize the amount of lost work.

If you have a database damaged by media failure and operating in NOARCHIVELOG mode, and you want to restore from your most recent full backup (your only option at this point), perform the following tasks.

To Restore the Most Recent Full Backup (NOARCHIVELOG Mode)

1. If the database is open, shut it down using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.

2. If the hardware problem that caused the media failure has been corrected so that the backup database files can be restored to their original locations, follow only Step 2.1 before proceeding to Step 3. If, on the other hand, the hardware problem has not been corrected and some or all of the database files must be restored to alternative locations, follow Steps 2.1 through 2.4.

2.1 Restore the most recent full backup. All of the datafiles and control files of the full backup must be restored, not just the damaged files. This guarantees that the entire database is synchronized to a single point in time.

2.2 If necessary, edit the restored parameter file to indicate the new location of the control files.

2.3 Start an instance using the restored and edited parameter file and mount, but do not open, the database.

2.4 Perform the steps necessary to record the relocation of the restored datafiles as described in "Renaming and Relocating Datafiles" . If applicable, perform the steps necessary to record the relocation of online redo log files, as described in "Renaming and Relocating Online Redo Log Members" .

See Also: See "Using the Export and Import Utilities for Supplemental Database Protection" .

Specifying Parallel Recovery

The RECOVERY_PARALLELISM initialization parameter specifies the number of concurrent recovery processes to use for any recovery operation. Because crash recovery occurs at instance startup, this parameter is useful for specifying the number of processes to use for crash recovery. The value of this parameter is also the default number of processes used for media recovery if the PARALLEL clause of the RECOVER command is not specified. The value of this parameter must be greater than one and cannot exceed the value of the PARALLEL_MAX_SERVERS parameter.

In general, parallel recovery is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. Crash recovery (recovery after instance failure) and media recovery of many datafiles on different disk drives are good candidates for parallel recovery. Parallel recovery requires a minimum of eight recovery processes to improve upon serial recovery.

See Also: For more information on parallel recovery, see Oracle7 Server Concepts.

For more information about initialization parameters, see the Oracle7 Server Reference.