Oracle7 Server Administrator's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Recovering a Database


This chapter describes how to recover a database, and includes the following topics:

See Also: Occasionally, this chapter refers you to Oracle Server Manager. To learn how to use Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.

Fundamental Recovery Concepts and Strategies

Before recovering a database, familiarize yourself with the fundamental data structures, concepts and strategies of Oracle recovery. This section describes basic recovery issues, and includes the following topics:

Important Recovery Data Structures

Table 24 - 1 describes important data structures involved in recovery processes. Be familiar with these data structures before starting any recovery procedure.

Data Structure Description
Control File The control file contains records that describe and maintain information about the physical structure of a database. The control file is updated continuously during database use, and must be available for writing whenever the database is open. If the control file is not accessible, the database will not function properly.
System Change Number (SCN) The system change number is a clock value for the Oracle database that describes a committed version of the database. The SCN functions as a sequence generator for a database, and controls concurrency and redo record ordering. Think of the SCN as a timestamp that helps ensure transaction consistency.
Redo Records A redo record is a group of change vectors describing a single, atomic change to the database. Redo records are constructed for all data block changes and saved on disk in the redo log. Redo records allow multiple database blocks to be changed so that either all changes occur or no changes occur, despite arbitrary failures.
Redo Logs All changes to the Oracle database are recorded in redo logs, which consist of at least two redo log files that are separate from the datafiles. During database recovery from an instance or media failure, Oracle applies the appropriate changes in the database's redo log to the datafiles; this updates database data to the instant that the failure occurred.
Backup A database backup consists of operating system backups of the physical files that constitute the Oracle database. To begin database recovery from a media failure, Oracle uses file backups to restore damaged datafiles or control files.
Checkpoint A checkpoint is a data structure in the control file that defines a consistent point of the database across all threads of a redo log. Checkpoints are similar to SCNs, and also describe which threads exist at that SCN. Checkpoints are used by recovery to ensure that Oracle starts reading the log threads for the redo application at the correct point. For Parallel Server, each checkpoint has its own redo information.
Table 24 - 1. Important Recovery Data Structures

See Also: For more information about these and other data structures, see the Oracle7 Server Concepts manual.

Recovery Operations

Media recovery restores a database's datafiles to the most recent point-in-time before disk failure, and includes the committed data in memory that was lost due to failure. Following is a list of media recovery operations:

1. Complete Media Recovery

2. Incomplete Media Recovery

Recovery Planning and Strategies

Before recovering a database, you should create a recovery plan or strategy. This section describes important issues to consider when defining your plan.

Test Backup and Recovery Strategies

You should test your backup and recovery strategies in a test environment before moving to a production system. You should continue to test your system regularly. That way, you can test the thoroughness of your strategies and later avoid real-life crises. Performing test recoveries regularly ensures that your archiving and backup procedures work. It also keeps you familiar with recovery procedures, so that you are less likely to make mistakes in a crisis.

Determine What Type of Recovery Operation Is Appropriate

You can use the RECOVER command when faced with any of the following problems:

Before recovering a database, you must choose an appropriate recovery operation. Your answers to the following questions will determine the most appropriate operation.

See Also: For a detailed list of different problems that media failures can cause and the appropriate recovery operations, see page 24 - 47.

Moving Datafiles

The goal of database recovery is to reopen a database for normal operation as soon as possible. If a media failure occurs because of a hardware problem, the damage should be repaired as soon as possible. However, database recovery does not depend on the resolution of long-lasting hardware problems. Table 24 - 2 lists sections in this Guide that contain procedures for restoring files from a damaged device to other storage devices.

Type of File Section Name Page
Datafile Renaming and Relocating Datafiles for Tablespace 9 - 8
Online Redo Log File Renaming and Relocating Online Redo Log Members 5 - 6
Control File Creating Additional Copies of the Control File, and Renaming or Relocating Control Files 6 - 4
Table 24 - 2. Damaged File Restoration

Coordinate Distributed Recovery

The Oracle distributed database architecture is autonomous in nature. Therefore, depending on the type of recovery operation selected for a single, damaged database, recovery operations may, or may not, have to be coordinated globally among all databases in the distributed database system. Table 24 - 3 summarizes the different types of recovery operations and whether coordination among nodes of a distributed database system is required.

Type of Recovery Operation Implication for Distributed Database System
Restoring a full backup for a database that was never accessed (updated or queried) from a remote node Use non-coordinated, autonomous database recovery.
Restoring a full backup for a database that was accessed by a remote node Shut down all databases and restore them using the same coordinated full backup.
Complete media recovery of one or more databases in a distributed database Use non-coordinated, autonomous database recovery.
Incomplete media recovery of a database that was never accessed by a remote node Use non-coordinated, autonomous database recovery.
Incomplete media recovery of a database that was accessed by a remote node Use coordinated, incomplete media recovery to the same global point-in-time for all databases in the distributed database.
Table 24 - 3. Database Recovery in a Distributed Database System

Coordinate Time-Based and Change-Based Distributed Database Recovery In special circumstances, one node in a distributed database may require recovery to a past point-in-time. To preserve global data consistency, it is often necessary to recover all other nodes in the system to the same point-in-time. This is called "coordinated, time-based, distributed database recovery." The following tasks should be performed with the standard procedures of time-based and change-based recovery described in this chapter.

To Coordinate Time-Based, Distributed Recovery Among Many Nodes in a Distributed Database System

Recover Database with Snapshots If a master database is independently recovered to a past point in time (that is, coordinated, time-based distributed database recovery is not performed), any dependent remote snapshot that was refreshed in the interval of lost time will be inconsistent with its master table. In this case, the administrator of the master database should instruct the remote administrators to perform a complete refresh of any inconsistent snapshot.

Preparing for Media Recovery

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

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:

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:

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 page 4 - 3.

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 page 3 - 2.

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:

		SET AUTORECOVERY ON;

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:

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:

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. 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.
    1. 2.2 If necessary, edit the restored parameter file to indicate the new location of the control files.
    1. 2.3 Start an instance using the restored and edited parameter file and mount, but do not open, the database.
    1. 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.

Performing Complete Media Recovery

This section describes the steps necessary to complete media recovery operations, and includes the following topics:

Do not depend solely on the steps in the following procedures to understand all the tasks necessary to recover from a media failure. If you haven't already done so, familiarize yourself with the fundamental recovery concepts and strategies [*].

See Also: See page 24 - 47 for a detailed list of the different problems that media failures can cause and describes the appropriate methods of recovery from each type of problem.

Performing Closed Database Recovery

This section describes steps to perform closed database recovery of either all damaged datafiles in one operation, or individual recovery of each damaged datafile in separate operations.

To Perform Closed Database Recovery

Attention: If the hardware problem that caused the media failure was temporary, and the data was undamaged (for example, a disk or controller power failure), stop at this point.

Note: If you do not have a backup of a specific datafile, you might be able to create an empty replacement file that can be recovered.

	ALTER DATABASE DATAFILE 'users1' ONLINE;

    1. 8.1 To start closed database recovery of an individual damaged datafile, use the RECOVER DATAFILE statement in Server Manager.
Note: For maximum performance, use parallel recovery to recover the datafiles.

Oracle continues until all required archived redo log files have been applied to the restored datafiles. The online redo log files are then automatically applied to the restored datafiles and notifies you when media recovery is complete. If no archived redo log files are required for complete media recovery, Oracle does not prompt for any. Instead, all necessary online redo log files are applied, and media recovery is complete.

After performing closed database recovery, the database is recovered up to the moment that media failure occurred. You can then open the database using the SQL command ALTER DATABASE with the OPEN option.

See Also: See "Restoring Damaged Datafiles" [*] for more information about creating datafiles.

For more information about datafile lists, see "Listing Database Files Before Backup" [*].

For more information about applying redo log files, see "Applying Redo Log Files" [*].

Performing Open-Database, Offline-Tablespace Recovery

At this point, an open database has experienced a media failure, and the database remains open while the undamaged datafiles remain online and available for use. The damaged datafiles are automatically taken offline by Oracle.

This procedure cannot be used to perform complete media recovery on the datafiles of the SYSTEM tablespace. If the media failure damages any datafiles of the SYSTEM tablespace, Oracle automatically shuts down the database.

See Also: To proceed with complete media recovery, follow the procedure in "Performing Closed Database Recovery" [*].

To Perform Open-Database, Offline-Tablespace Recovery

    1. 1.1 If the database was shut down, start a new instance, and mount and open the database. Perform this operation using the Server Manager Startup Database dialog box (with the Startup Open radio button selected), or with the STARTUP command with the OPEN option. After the database is open, take all tablespaces that contain damaged datafiles offline.
    1. 1.2 If the database is still open and only damaged datafiles of the database are offline, take all tablespaces containing damaged datafiles offline. Oracle identifies damaged datafiles via error messages. Tablespaces can be taken offline using either the Take Offline menu item of Server Manager, or the SQL command ALTER TABLESPACE with the OFFLINE option, as described in "Taking Tablespaces Offline" [*]. If possible, take the damaged tablespaces offline with temporary priority (to minimize the amount of recovery).

Note: If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.

See Also: For more information about redo log application, see "Applying Redo Log Files" [*].

For more information about creating datafiles, see "Restoring Damaged Datafiles" [*].

Performing Open-Database, Offline-Tablespace Individual Recovery

Identical to the preceding operation, here an open database has experienced a media failure, and remains open while the undamaged datafiles remain online and available for use. The damaged datafiles are automatically taken offline by Oracle.

Note: This procedure cannot be used to perform complete media recovery on the datafiles of the SYSTEM tablespace. If the media failure damages any datafiles of the SYSTEM tablespace, Oracle automatically shuts down the database.

To Perform Open-Database, Offline-Tablespace Individual Recovery

    1. 1.1 If the database was shut down, start a new instance, and mount and open the database. Perform this operation using the Server Manager Startup Database dialog box (with the Startup Open radio button selected), or with the STARTUP command with the OPEN option. After the database is open, take all tablespaces that contain damaged datafiles offline.
    1. 1.2 If the database is still open and only damaged datafiles of the database are offline, take all tablespaces containing damaged datafiles offline. Oracle identifies damaged datafiles via error messages. Tablespaces can be taken offline using either the Take Offline menu item of Server Manager, or the SQL command ALTER TABLESPACE with the OFFLINE option, as described in "Taking Tablespaces Offline" [*]. If possible, take the damaged tablespaces offline with temporary priority (to minimize the amount of recovery).

Note: If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.

See Also: For information about how to proceed with complete media recovery, see "Performing Closed Database Recovery" [*].

For more information about creating datafiles, see "Restoring Damaged Datafiles" [*].

Performing Incomplete Media Recovery

This section descrines the steps necessary to complete the different types of incomplete media recovery operations, and includes the following topics:

See Also: Do not rely solely on this section to understand the procedures necessary to recover from a media failure. Also see "Examples of Media Failures and Appropriate Recovery Procedures" [*] for a detailed list of the different types of problems that media failures can cause, and the appropriate methods of recovery from each type of problem.

Changing the System Time on a Running Database

If your database is affected by seasonal time changes (for example, daylight savings time), you may experience a problem if a time appears twice in the redo log and you want to recover to the second, or later time. To deal with time changes, perform cancel-based or change-based recovery to the point in time where the clock is set back, then continue with the time-based recovery to the exact time.

Performing Cancel-Based Recovery

This section describes how to perform cancel-based recovery.:

To Perform Cancel-Based Recovery

Note: If a database control file cannot function or be replaced with a control file backup, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.

Note: Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, recovery will try to update the headers of the read-only files.

Opening the Database After Successful Cancel-Based Recovery

The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option. Resetting the redo log:

Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.

	RESETLOGS after complete recovery through change scn

	RESETLOGS after incomplete recovery UNTIL CHANGE scn

If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)

After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).

If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.

See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" [*].

For more information about creating datafiles, see "Restoring Damaged Datafiles" [*].

To relocate or rename datafiles, see "Renaming and Relocating Datafiles" [*], as necessary.

For more information about listing datafiles, see "Listing Database Files Before Backup" [*].

For more information about applying redo logs, see "Applying Redo Log Files" [*].

Performing Time-Based Recovery

When you are performing time-based, incomplete media recovery, and you are recovering with a backup control file and have read-only tablespaces, contact Oracle Support before attempting this recovery procedure.

To Perfrom Time-Based Recovery

Note: If a database control file cannot function or be replaced with a control file backup because the hardware problem causing the media failure persists, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.

Note: Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, the recovery will try to update the headers of the read-only files.

	ALTER DATABASE DATAFILE 'users1' ONLINE;

Opening the Database After Successful Time-Based Recovery

The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option. Resetting the redo log:

Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.

	RESETLOGS after complete recovery through change scn

	RESETLOGS after incomplete recovery UNTIL CHANGE scn

If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)

After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).

If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.

See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" [*].

For more information about creating datafiles, see "Restoring Damaged Datafiles" [*].

To relocate or rename datafiles, see "Renaming and Relocating Datafiles" [*], as necessary.

For more information about listing datafiles, see "Listing Database Files Before Backup" [*].

For more information about applying redo logs, see "Applying Redo Log Files" [*].

Performing Change-Based Recovery

This section describes how to perform change-based recovery.

To Perform Change-Based Recovery

Note: If a database control file cannot function or be replaced with a control file backup, you must edit the parameter file associated with the database to modify the CONTROL_FILES parameter.

Note: Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, recovery will try to update the headers of the read-only files.

	ALTER DATABASE DATAFILE 'users1' ONLINE;

Opening the Database After Successful Change-Based Recovery

The first time you open the database subsequent to incomplete media recovery, you must explicitly specify whether to reset the log sequence number by including either the RESETLOGS or NORESETLOGS option. Resetting the redo log:

Warning: Resetting the redo log discards all changes to the database made since the first discarded redo information. Updates entered after that time must be re-entered manually.

	RESETLOGS after complete recovery through change scn

	RESETLOGS after incomplete recovery UNTIL CHANGE scn

If you reset the redo log sequence when opening the database, immediately shut down the database normally and make a full database backup. Otherwise, you will not be able to recover changes made after you reset the logs. Until you take a full backup, the only way to recover will be to repeat the procedures you just finished, up to resetting the logs. (You do not need to back up the database if you did not reset the log sequence.)

After opening the database using the RESETLOGS option, check the ALERT log to see if Oracle7 has detected inconsistencies between the data dictionary and the control file (for example, a datafile that the data dictionary includes but does not list in the new control file).

If a datafile exists in the data dictionary but not in the new control file, Oracle7 creates a placeholder entry in the control file under MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery. The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn, so that it points to the datafile only when the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, the tablespace containing the datafile must be dropped.

See Also: See "Creating Additional Copies of the Control File, and Renaming and Relocating Control Files" [*].

For more information about creating datafiles, see "Restoring Damaged Datafiles" [*].

To relocate or rename datafiles, see "Renaming and Relocating Datafiles" [*], as necessary.

For more information about listing datafiles, see "Listing Database Files Before Backup" [*].

For more information about applying redo logs, see "Applying Redo Log Files" [*].

Preparing for Disaster Recovery

This section describes how to plan for and implement disaster recovery procedures for your primary database, and includes the following topics:

Planning and Creating a Standby Database

A standby database maintains a duplicate, or standby copy of your primary (also known as production) database and provides continued primary database availability in the event of a disaster (when all media is destroyed at your production site). A standby database is constantly in recovery mode. If a disaster occurs, you can take the standby database out of recovery mode and activate it for online use. A standby database is intended only for recovery of the primary database; you cannot query or open it for any purpose other than to activate disaster recovery. Once you activate your standby database, you cannot return it to standby recovery mode unless you re-create it as another standby database.

Warning: Activating a standby database resets the online logs of the standby database. Hence, after activation, the logs from your standby database and production database are incompatible.

You must place the data files, log files, and control files of your primary and standby databases on separate physical media. Therefore, it is impossible to use the same control file for both your primary and standby databases.

Creating a Standby Database

This section lists the steps and rules to follow when creating a standby database.

To Create a Standby Database

1. Back up (either online or offline) the data files from your primary database.

2. Create the control file for your standby database by issuing the ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename' command, which creates a modified copy of the primary database's control file.

3. Archive the current online logs of the primary database by issuing the ALTER SYSTEM ARCHIVE LOG CURRENT command. Issuing the ALTER SYSTEM ARCHIVE LOG CURRENT command also ensures consistency among the data files in step 1, the control file in step 2, and the log files.

4. Transfer the standby database control file, archived log files, and backed up data files to the remote (standby) site using operating system commands or utilities. Use an appropriate method if transferring binary files.

Warning: Oracle encourages you to use a datafile naming scheme that keeps the datafile names the same at both the primary and standby databases. If this is not possible, then you can use the datafile name conversion parameters. If you do not use either of these suggested datafile naming schemes, you may end up crashing your standby database.

See Also: For information about setting name conversion parameters when you create your standby database, see "Converting Data File and Log File Names."

Maintaining a Standby Database

This section provides the tasks for maintaining your standby database, including information about clearing standby logfiles.

To Maintain Your Standby Database in Recovery Mode

1. Start up the Oracle instance at the standby database using the NO MOUNT clause.

2. Issue the ALTER DATABASE MOUNT STANDBY DATABASE [EXCLUSIVE / PARALLEL] command.

3. Transfer the archived redo logs from the primary database to the remote (standby) site. Use an appropriate operating system utility for transferring binary data.

4. Place the standby database in recovery mode by issuing the RECOVER [FROM 'location'] STANDBY DATABASE command.

Note: As the archived logs are generated, you must continually transfer and apply them to the standby database. Also, you can only apply logs that have been archived at the primary database to the standby database.

Clearing Online Logfiles You can clear standby database online logfiles to optimize performance as you maintain your standby database. If you prefer not to perform this operation during maintenance, the online logfiles will be cleared automatically during activation. You can clear logfiles using the following statement:

ALTER DATABASE CLEAR LOGFILE GROUP integer;

Converting Data File and Log File Names

You can set the following initialization parameters so that all filenames from your primary database control file are converted for use by your standby database:

If your primary and standby databases exist on the same machine (of course, they should not, but if they are), setting these parameters is advisable, because they allow you to make your standby database filenames distinguishable from your primary database filenames.

The DB_FILE_STANDBY_NAME_CONVERT and LOG_FILE_STANDBY_NAME_CONVERT parameters must have two strings. The first string is a sequence of characters to be looked for in a primary database filename. If that sequence of characters is matched, it is replaced by the second string to construct the standby database filename.

Figure 24 - 1 shows how the filename conversion parameters work:

Figure 24 - 1. Setting Filename Conversion Parameters

Note: If you perform a data file (or log file) RENAME at the standby database, or use the AS clause with the ALTER DATABASE CREATE FILE command, then the conversion parameters will not apply to that file.

Activating a Standby Database

In the event of a disaster, you should (if possible) archive your primary database logs (ALTER SYSTEM ARCHIVE LOG CURRENT), transfer them to your standby site, and apply them before activating your standby database. This makes your standby database current to the same point in time as your primary database (before the failure). If you cannot archive your current online logs, then you must activate the standby database without recovering the transactions from the unarchived logs of the primary database.

After you activate your standby database, its online redo logs are reset. Note that this makes the logs from the standby database and primary database incompatible. Also, the standby database is dismounted when activated, therefore, you are unable to look at tables and views immediately after activation.

To Activate a Standby Database

1. Ensure that your standby database is mounted in EXCLUSIVE mode.

2. Issue the ALTER DATABASE ACTIVATE STANDBY DATABASE command.

3. Shut down your standby instances.

4. As soon as possible, back up your new production database. At this point, the former standby database is now your production database. This task, while not required, is a recommended safety measure, because you cannot recover changes made after activation without a backup.

5. Startup the new production instance.

Note: After you activate your standby database, all transactions from unarchived logs at your original production database are lost.

Altering the Physical Structure of the Primary Database

Altering the physical structure of your primary database can have an impact on your standby database. The following sections describe the effects of primary database structural alterations on a standby database.

Adding Data Files

Adding a data file to your primary database generates redo information that, when applied at your standby database, automatically adds the data file name to the standby control file. If the standby database locates the new file with the new filename, the recovery process continues. If the standby database is unable to locate the new data file, the recovery process will stop.

If the recovery process stops, then perform either of the following procedures before resuming the standby database recovery process:

If you don't want the new data file in the standby database, you can take it offline using the DROP option.

See Also: For more information on offline data file alterations, see "Taking Data Files in the Standby Database Offline" [*].

Renaming Files

Data file renames on your primary database do not take effect at the standby database until the standby database control file is refreshed. If you want the data files at your primary and standby databases to remain in sync when you rename primary database data files, then perform analogous operations on the standby database.

Altering Log Files

You can add log file groups or members to the primary database without affecting your standby database. Likewise, you can drop log file groups or members from the primary database without affecting your standby database. Similarly, enabling and disabling of threads at the primary database has no effect on the standby database.

You may want to keep the online log file configuration the same at the primary and standby databases. If so, when you enable a log file thread with the ALTER DATABASE ENABLE THREAD at the primary database, you should create a new control file for your standby database before activating it. See "Refreshing the Standby Database Control File" [*] for refresh procedures.

If you clear log files at the primary database by issuing the ALTER DATABASE CLEAR UNARCHIVED LOGFILE command, or open the primary database using the RESETLOGS option, you invalidate the standby database. Because the standby database recovery process will not have the archived logs it requires to continue, you will need to re-create the standby database.

Altering Control Files

If you use the CREATE CONTROLFILE command at the primary database to perform any of the following, you may invalidate the standby database's control file:

If you've invalidated the standby database's control file, you must re-create it using the procedures in "Refreshing the Standby Database Control File" [*].

Using the CREATE CONTROLFILE command with the RESETLOGS option on your primary database will force the next open of the primary database to reset the online logs, thereby invalidating the standby database.

Configuring Initialization Parameters

Most initialization parameters at your primary and standby databases should be identical. Specific initialization parameters such as CONTROL_FILES and DB_FILE_STANDBY_NAME_CONVERT should be changed. Differences in other initialization parameters may cause performance degradation at the standby database, and in some cases, bring standby database operations to a halt.

The following initialization parameters play a key role in the standby database recovery process:

See Also: For more information on initialization parameters, see the Oracle7 Server Reference.

Taking Data Files in the Standby Database Offline

You can take standby database datafiles offline as a means to support a subset of your primary database's datafiles. For example, you decide it is undesirable to recover the primary database's temporary tablespaces on the standby database. So you take the datafiles offline using the ALTER DATABASE DATAFILE 'fn' OFFLINE DROP command on the standby database. If you do this, then the tablespace containing the offline files must be dropped after opening the standby database.

Performing Direct Path Operations

When you perform a direct load originating from either direct path load, table create via subquery, or index create on the primary database, the performance improvement applies only to the primary database; there is no corresponding recovery process performance improvement on the standby database. The standby database recovery process still sequentially reads and applies the redo information generated by the unrecoverable direct load.

Primary database processes using the UNRECOVERABLE option are not propagated to the standby database. Why? Because these processes do not appear in the archived redo logs. If you want to propagate such processes to your standby database, perform any one of the following tasks.

To Propagate UNRECOVERABLE Processes to a Standby Database

1. Take the affected datafiles offline in the standby database, and drop the tablespace after activation.

2. Re-create the standby database from a new database backup.

3. Back up the affected tablespace and archive the current logs in the primary database. Transfer the datafiles to the standby database. Then resume standby recovery. This is the same procedure that you would perform to guarantee ordinary database recoverability after an UNRECOVERABLE operation.

If you perform an unrecoverable operation at the primary database, and attempt to recover at the standby database, you will not receive error messages during recovery. Such error messages appear in the standby database alert log. Thus, you should check the standby database alert log periodically.

See Also: For more details, see "Taking Datafiles in the Standby Database Offline" [*].

Refreshing the Standby Database Control File

The following steps describe how to refresh, or create a copy of changes you've made to the primary database control file.

To Refresh the Standby Database Control File

1. Issue the CANCEL command on the standby database to halt its recovery process.

2. Shut down the standby instances.

3. Issue the ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename' statement on the primary database to create the control file for the standby database.

4. Issue the ALTER SYSTEM ARCHIVE LOG CURRENT statement on the primary database to archive the current online logs of your primary database.

5. Transfer the standby control file and archived log files to the standby site.

6. Restart and mount (but do not open) the standby database by issuing the ALTER DATABASE MOUNT STANDBY DATABASE [EXCLUSIVE/PARALLEL] statement.

7. Restart the recovery process on the standby database by issuing the RECOVER [FROM 'location'] STANDBY DATABASE statement.

Unrecoverable Objects and Recovery

You can create tables and indexes using the CREATE TABLE AS SELECT command. You can also specify that Oracle create them as unrecoverable. When you create a table or index as unrecoverable, Oracle does not generate redo log records for the operation. Thus, objects created unrecoverable cannot be recovered, even if you are running in ARCHIVELOG mode.

Note: If you cannot afford to lose tables or indexes created unrecoverable, take a backup after the unrecoverable table or index is created.

Be aware that when you perform a media recovery, and some tables or indexes are created as recoverable while others are unrecoverable, the unrecoverable objects will be marked logically corrupt by the RECOVER operation. Any attempt to access the unrecoverable objects returns an ORA-01578 error message. You should drop the unrecoverable objects, and recreate them, if needed.

Because it is possible to create a table unrecoverable and then create a recoverable index on that table, the index is not marked as logically corrupt after you perform a media recovery. However, the table was unrecoverable (and thus marked as corrupt after recovery), so the index points to corrupt blocks. The index must be dropped, and the table and index must be re-created if necessary.

See Also: For information about the impact of UNRECOVERABLE operations on a standby database, see page 24 - 44.

Read-Only Tablespaces and Recovery

This section describes how read-only tablespaces affect instance and media recovery.

Using a Backup Control File

Media recovery with the USING BACKUP CONTROLFILE option checks for read-only files. It is an error to attempt recovery of a read-only file. You can avoid this error by taking all datafiles from read-only tablespaces offline before doing recovery with a backup control file. Therefore, it is very important to have the correct version of the control file for the recovery. If the tablespace will be read-only when the recovery is complete, then the control file must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read-write at the end of recovery, it should be read-write in the control file. If the appropriate control file is not available, you should create a new control file with the CREATE CONTROLFILE command.

Re-Creating a Control File

If you need to re-create a control file for a database with read-only tablespaces, you must follow some special procedures. Issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command to get a listing of the procedure that you need to follow. The procedure is similar to the procedure for offline normal tablespaces, except that you need to bring the tablespace online after the database is open.

Re-creating a control file can also affect the recovery of read-write tablespaces that were at one time read-only. If you re-create the control file after making the tablespace writeable, Oracle can no longer determine when the tablespace was changed from read-only to read-write. Thus, you can no longer recover from the read-only version of the tablespace. Instead, you must recover from the time of the most recent backup. It is important to backup a tablespace immediately after making it read-write.

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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index