
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
- Recovery Planning and Strategies
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
- Open-Database, Offline-Tablespace Recovery
- Open-Database, Offline-Tablespace, Individual Datafile 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:
- Media failure has damaged your database.
- You need to recover to a point-in-time in the past (for example, undo an erroneous operational or programmatic change to the database).
- You have lost online logs.
Before recovering a database, you must choose an appropriate recovery operation. Your answers to the following questions will determine the most appropriate operation.
1. What recovery operations are available?
The answer to this first question depends on whether your database is archiving redo logs.
- If the database is in ARCHIVELOG mode, several recovery operations are available.
- If the database is in NOARCHIVELOG mode, usually only one recovery operation is available, which is to restore the most recent full backup and re-enter all work performed since the backup. (If you have used Export to supplement regular backups, you can instead use Import to restore data.) Some special losses are easier to repair.
2. What recovery operations are appropriate for this particular problem?
If the database is in ARCHIVELOG mode, several recovery operations are available to restore a damaged database to a transaction-consistent state.
3. Is the damaged database part of a distributed database?
If so, database recovery may need to be coordinated among the nodes of the distributed database.
4. Are disaster recovery procedures in place?
If you have lost all of your online media, or have determined that your recovery time will be too long, you may want to activate your standby database rather than perform media recovery on your primary database.
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
1. Recover the database that requires the recovery operation using time-based recovery. For example, if a database needs to be recovered because of a user error (such as an accidental table drop), recover this database first using time-based recovery. Do not recover the other databases at this point.
2. After you have recovered the database and opened it using the RESETLOGS option, look in the ALERT file of the database for the RESETLOGS message.
If the message is, "RESETLOGS after complete recovery through change nnnnnnnn," you have applied all the changes in the database and performed a complete recovery. Do not recover any of the other databases in the distributed system, or you will unnecessarily remove changes in them. Recovery is complete.
If the reset message is, "RESETLOGS after incomplete recovery UNTIL CHANGE nnnnnnnn," you have successfully performed an incomplete recovery. Record the change number from the message and proceed to the next step.
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:
- 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 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:
- 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"
.
3. Issue the ALTER DATABASE OPEN RESETLOGS command, which opens the database and resets the current log sequence to 1. It also invalidates all redo entries in the online redo log file. Restoring from a full backup and then resetting the log discards all changes to the database made from the time the backup was taken to the time of the media failure.
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
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 you're recovering from a media error, correct it if possible.
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.
3. If files are permanently damaged, restore the most recent backup files (taken as part of a full or partial backup) of only the datafiles damaged by the media failure. Do not restore any undamaged datafiles or any online redo log files. If the hardware problem has been repaired, and damaged datafiles can be restored to their original locations, do so, and skip Step 6 of this procedure. If the hardware problem persists, restore the datafiles to an alternative storage device of the database server and continue with this procedure.
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.
4. Start Server Manager and connect to Oracle with administrator privileges.
5. Start a new instance and mount, but do not open, the database using either the Server Manager Startup Database dialog box (with the Startup Mount radio button selected), or the STARTUP command with the MOUNT option.
6. If one or more damaged datafiles were restored to alternative locations in Step 3, the new location of these files must be indicated to the control file of the associated database. Therefore, use the operation described in "Renaming and Relocating Datafiles"
, as necessary.
7. All datafiles you want to recover must be online during complete media recovery. To get the datafile names, check the list of datafiles that normally accompanies the current control file, or query the V$DATAFILE view. Then, issue the ALTER DATABASE command with the DATAFILE ONLINE option to ensure that all datafiles of the database are online. For example, to guarantee that a datafile named USERS1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
If a specified datafile is already online, Oracle ignores the statement.
8. To start closed database recovery of all damaged datafiles in one step, use either the Server Manager Apply Recovery Archive dialog box, or an equivalent RECOVER DATABASE statement.
- 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.
9. Now Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle prompts you for each required redo log file.
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. The starting point for this recovery operation can vary, depending on whether you left the database open after the media failure occurred.
- 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.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).
2. Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, you can proceed with database recovery by restoring damaged files to an alternative storage device.
3. If files are permanently damaged, restore the most recent backup files (taken as part of a full or partial backup) of only the datafiles damaged by the media failure. Do not restore undamaged datafiles, online redo log files, or control files. If the hardware problem has been repaired and the datafiles can be restored to their original locations, do so. If the hardware problem persists, restore the datafiles to an alternative storage device of the database server.
Note: If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.
4. If one or more damaged datafiles were restored to alternative locations (Step 3), indicate the new locations of these files to the control file of the associated database by using the procedure in "Renaming and Relocating Datafiles"
, as necessary.
5. After connecting with administrator privileges, use the RECOVER TABLESPACE statement in Server Manager to start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces using one step.
Note: For maximum performance, use parallel recovery to recover the datafiles.
6. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the applying of files is automated, Oracle prompts for each required redo log file.
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.
7. The damaged tablespaces of the open database are now recovered up to the moment that media failure occurred. You can bring the offline tablespaces online using the Place Online menu item of Server Manager, or the SQL command ALTER TABLESPACE with the ONLINE option.
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. The starting point for this recovery operation can vary, depending on whether you left the database open after the media failure occurred.
- 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.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).
2. Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, you can proceed with database recovery by restoring damaged files to an alternative storage device.
3. If files are permanently damaged, restore the most recent backup files (taken as part of a full or partial backup) of only the datafiles damaged by the media failure. Do not restore undamaged datafiles, online redo log files, or control files. If the hardware problem has been repaired and the datafiles can be restored to their original locations, do so. If the hardware problem persists, restore the datafiles to an alternative storage device of the database server.
Note: If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.
4. If one or more damaged datafiles were restored to alternative locations (Step 3), indicate the new locations of these files to the control file of the associated database by using the procedure in "Renaming and Relocating Datafiles"
, as necessary.
5. After connecting with administrator privileges use the RECOVER DATAFILE statement in Server Manager to start recovery of an individual damaged datafile in an offline tablespace
Note: For maximum performance, use parallel recovery to recover the datafiles.
6. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle prompts for each required redo log file.
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.
7. The damaged tablespaces of the open database are now recovered up to the moment that media failure occurred. You can bring the offline tablespaces online using the Place Online menu item of Server Manager, or the SQL command ALTER TABLESPACE with the ONLINE option.
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:
- Performing Cancel-Based Recovery
- Performing Time-Based Recovery
- Performing Change-Based Recovery
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
1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
3. If a media failure occurred, correct the hardware problem that caused the media failure.
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.
5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38.
If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.
If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If a hardware problem persists, restore damaged datafiles to an alternative storage device.
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.
6. Start Server Manager and connect to Oracle with administrator privileges.
7. Start a new instance and mount the database. You can perform this operation using the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
9. If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored in Step 4), indicate this in the dialog box or command used to start recovery (that is, specify the USING BACKUP CONTROLFILE parameter).
10. Use Server Manager Apply Recovery Archives dialog box, or an equivalent RECOVER DATABASE UNTIL CANCEL statement to begin cancel-based recovery.
11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs.
Oracle continues to apply redo log files.
12. Continue applying redo log files until the most recent, undamaged redo log file has been applied to the restored datafiles.
13. Enter "CANCEL" to cancel recovery after Oracle has applied the redo log file just prior to the damaged file. Cancel-based recovery is now complete.
Oracle returns a message indicating whether recovery is successful.
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:
- discards any redo information that was not applied during recovery, ensuring that it will never be applied
- reinitializes the control file information about online redo logs and redo threads
- clears the contents of the online redo logs
- creates the online redo log files if they do not currently exist
- resets the log sequence number to 1
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.
Use the following rules when deciding to specify RESETLOGS or NORESETLOGS:
- Reset the log sequence number if you used a backup of the control file in recovery, no matter what type of recovery was performed (complete or incomplete).
- Reset the log sequence number if the recovery was actually incomplete. For example, you must have specified a previous time or SCN, not one in the future.
- Do not reset logs if recovery was complete (unless you used a backup control file). This applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- Do not reset logs if you are using the archived logs of this database for a standby database. If the log must be reset, then you will have to re-create your standby database.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
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.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
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
1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
3. If a media failure occurred, correct the hardware problem that caused the media failure.
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.
5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38.
If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.
If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If a hardware problem persists, restore damaged datafiles to an alternative storage device.
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.
6. Start Server Manager and connect to Oracle with administrator privileges.
7. Start a new instance and mount the database. This operation can be performed with the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
9. All datafiles of the database must be online unless an offline tablespace was taken offline normally. To get the names of all datafiles to recover, check the list of datafiles that normally accompanies the control file being used or query the V$DATAFILE view. Then, use the ALTER DATABASE command and the DATAFILE ONLINE option to make sure that all datafiles of the database are online. For example, to guarantee that a datafile named USERS1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored), indicate this in the dialog box or command used to start recovery. If a specified datafile is already online, Oracle ignores the statement.
10. Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'.
11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs. Oracle continues to apply redo log files.
12. Continue applying redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.
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:
- discards any redo information that was not applied during recovery, ensuring that it will never be applied
- reinitializes the control file information about online redo logs and redo threads
- clears the contents of the online redo logs
- creates the online redo log files if they do not currently exist
- resets the log sequence number to 1
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.
Use the following rules when deciding to specify RESETLOGS or NORESETLOGS:
- Reset the log sequence number if you used a backup of the control file in recovery, no matter what type of recovery was performed (complete or incomplete).
- Reset the log sequence number if the recovery was actually incomplete. For example, you must have specified a previous time or SCN, not one in the future.
- Do not reset logs if recovery was complete (unless you used a backup control file). This applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- Do not reset logs if you are using the archived logs of this database for a standby database. If the log must be reset, then you will have to re-create your standby database.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
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.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
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
1. If the database is still open and incomplete media recovery is necessary, shut down the database using the Server Manager Shutdown Abort mode of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
2. Make a full backup of the database (all datafiles, a control file, and the parameter files of the database) as a precautionary measure, in case an error is made during the recovery procedure.
3. If a media failure occurred, correct the hardware problem that caused the media failure.
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.
5. Restore backup files (taken as part of a full or partial backup) of all the datafiles of the database. All backup files used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to redo log sequence number 38, then restore all datafiles with backups completed before redo log sequence number 38.
If you do not have a backup of a specific datafile, you can create an empty replacement file, which can be recovered.
If a datafile was added after the intended time of recovery, it is not necessary to restore a backup for this file, as it will no longer be used for the database after recovery is complete.
If the hardware problem that caused a media failure has been solved and all datafiles can be restored to their original locations, do so, and skip Step 8 of this procedure. If a hardware problem persists, restore damaged datafiles to an alternative storage device.
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.
6. Start Server Manager and connect to Oracle with administrator privileges.
7. Start a new instance and mount the database. You can perform this operation using the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
8. If one or more damaged datafiles were restored to alternative locations in Step 5, the new locations of these files must be indicated to the control file of the associated database.
9. To get the names of all datafiles to recover, check the list of datafiles that normally accompany the control file being used or query the V$DATAFILE view. Then, use the ALTER DATABASE command with the DATAFILE ONLINE option to make sure that all datafiles of the database are online. For example, to guarantee that a datafile named USERS1 (a fully specified filename) is online, enter the following statement:
ALTER DATABASE DATAFILE 'users1' ONLINE;
If a specified datafile is already online, Oracle ignores the statement.
If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored), specify the USING BACKUP CONTROLFILE parameter in the dialog box or command used to start recovery.
10. Issue the RECOVER DATABASE UNTIL CHANGE statement to begin change-based recovery. The SCN is specified as a decimal number without quotation marks.
11. Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup file, you must supply names of online logs. Oracle continues to apply redo log files.
12. Continue applying redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.
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:
- discards any redo information that was not applied during recovery, ensuring that it will never be applied
- reinitializes the control file information about online redo logs and redo threads
- clears the contents of the online redo logs
- creates the online redo log files if they do not currently exist
- resets the log sequence number to 1
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.
Use the following rules when deciding to specify RESETLOGS or NORESETLOGS:
- Reset the log sequence number if you used a backup of the control file in recovery, no matter what type of recovery was performed (complete or incomplete).
- Reset the log sequence number if the recovery was actually incomplete. For example, you must have specified a previous time or SCN, not one in the future.
- Do not reset logs if recovery was complete (unless you used a backup control file). This applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway. See the explanation in step 12 for how to examine the ALERT file to see if incomplete recovery was actually complete.
- Do not reset logs if you are using the archived logs of this database for a standby database. If the log must be reset, then you will have to re-create your standby database.
If the log sequence number is reset when opening a database, different messages are returned, depending on whether the recovery was complete or incomplete. If the recovery was complete, the following message appears in the ALERT file:
RESETLOGS after complete recovery through change scn
If the recovery was incomplete, the following message is reported in the ALERT file:
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.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle7 removes references to it from the new control file. In both cases, Oracle7 includes an explanatory message in the ALERT file to let you know what was found.
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:
- DB_FILE_STANDBY_NAME_CONVERT
- LOG_FILE_STANDBY_NAME_CONVERT
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:
- Copy a backup of the added data file from the primary database to the standby database.
- Issue the ALTER DATABASE CREATE DATAFILE command at the standby database.
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:
- change the maximum number of redo log file groups or members
- change the maximum number of data files
- change the maximum number of instances that can concurrently mount and open the database
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:
The COMPATIBLE parameter must be the same at the primary and standby databases. If it is not, you may not be able to apply the logs from your primary database to your standby database.
MAXDATAFILES must be the same at both databases so that you allow the same number of files at the standby database as you allow at the primary database.
CONTROL_FILES must be different between the primary and standby databases. The names of the control files that you list in this parameter for the standby database must exist at the standby database.
- DB_FILE_STANDBY_NAME_CONVERT (or LOG_FILE_STANDBY_NAME_CONVERT)
Set the DB_FILE_STANDBY_NAME_CONVERT (or LOG_FILE_STANDBY_NAME_CONVERT) parameter when you want to make your standby database filenames distinguishable from your primary database filenames. For more information on this parameter see "Converting Data File and Log File Names"
.
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:
- A disk controller fails. Once the disk controller is replaced, the data on the disk can be accessed.
- Power to a storage device is cut off. Once the power is returned, the storage device and all associated data is accessible again.
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:
- If the media failure is temporary, correct the temporary hardware problem and restart the database. Usually, instance recovery is possible, and all committed transactions can be recovered using the online redo log.
- If the media failure is permanent, follow the steps
to recover from the media failure.
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:
- If a temporary or permanent media failure affects any datafiles of the SYSTEM tablespace or any datafiles that contain active rollback segments, the database becomes inoperable and should be immediately shut down if it has not already been shut down by Oracle.
If the hardware problem is temporary, correct the problem and restart the database. Usually, instance recovery is possible, and all committed transactions can be recovered using the online redo log.
If the hardware problem is permanent, follow the procedure in "Performing Closed Database Recovery"
.
- If a temporary or permanent media failure affects only datafiles not mentioned in the previous item, the affected datafiles are unavailable and taken offline automatically by Oracle, but the database can continue to operate.
If the unaffected portions of the database must remain available, do not shut down the database. First take all tablespaces that contain problem datafiles offline using the temporary option. Then follow the procedure in "Performing Open Database-Offline Tablespace Recovery"
.
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:
- If the hardware problem is temporary, correct the problem. After it has been fixed, LGWR accesses the previously unavailable online redo log files as if the problem never existed.
- If the hardware problem is permanent, use the DROP command to drop the damaged member and use the ADD command to add a new member.
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:
- If a temporary media failure affects only an inactive online redo log group, correct the problem; LGWR can reuse the group when required.
- If a media failure permanently prevents access to only an inactive online redo log group, the damaged inactive online redo log group will eventually halt normal database operation.
If you notice the problem before the database shuts down, use the ALTER DATABASE CLEAR LOGFILE command.
If the database has already shut down, perform the following tasks:
To Recover From Loss of an Inactive, Online Redo Log Group
1. Abort the current instance immediately with the Server Manager Shutdown Database dialog box with the Shutdown Abort radio button selected, or the SHUTDOWN command with the ABORT option.
2. Start a new instance and mount the database, but do not open it. This operation can be performed with the Server Manager Startup Database dialog box with the Startup Mount radio button selected, or the STARTUP command with the MOUNT option.
3. If the lost log was archived, issue the ALTER DATABASE CLEAR LOGFILE command.
4. If the lost log was unarchived, issue the ALTER DATABASE CLEAR UNARCHIVED LOGFILE command, and immediately backup the database. Also backup the database's control file (using the ALTER DATABASE command with the BACKUP CONTROLFILE option).
Clearing a log that has not been archived allows it to be reused without archiving it. However, this will make backups unusable if they were started before the last change in the log (unless the file was taken offline prior to the first change in the log). Hence, if the cleared logfile is needed for recovery of a backup, it will not be possible to recover that backup.
If there is an offline datafile that requires the cleared unarchived log to bring it online, the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace will have to be dropped from the database because the redo necessary to bring it online is being cleared, and there is no copy of it.
Note: The ALTER DATABASE CLEAR LOGFILE command could fail (with an I/O error due to media failure) in two cases:
- When it is not possible to relocate the logfile onto alternative media by re-creating it under the currently configured logfile name.
- When it is not possible to reuse the currently configured logfile name to recreate the logfile because the name itself is invalid or unusable (for example, due to media failure).
In these two cases, the CLEAR LOGFILE command (before receiving the I/O error) would have successfully updated the control file to change the state of the logfile to "being cleared" and "not requiring archiving." The I/O error occurred at the step in which CLEAR LOGFILE attempts to create the new logfile and write zeros to it.
At this point, you can complete recovery by executing, in order, the following commands:
- ADD a logfile under a new name.
- DROP the logfile under the old name.
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
1. If the media failure is temporary, correct the problem so that Oracle can reuse the group when required.
2. If the database is in NOARCHIVELOG mode and a permanent media failure prevents access to an active online redo log group, restore the database from a full backup.
After restoring the database, redo the work and open the database using the RESETLOGS option. Updates done after the backup have been lost and must be re-executed. Shut down the database and take a full offline backup.
3. If the database was in ARCHIVELOG mode, incomplete media recovery must be performed. Use the procedure given in "Performing Cancel-Based, Time-Based, or Change-Based Recovery"
, recovering up through the log before the damaged log. Ensure that the current name of the lost redo log can be used for a newly created file. If not, issue the RENAME command to rename the damaged online redo log group to a new location.
4. Open the database using the RESETLOGS option.
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:
- If all datafiles have been backed up after the filled online redo log group (which is now archived) was written, the archived version of the filled online redo log group is not required for complete media recovery operation.
- Assume the most recent backup file of a datafile was taken before the filled online redo log group was written. The group now corresponds to the damaged archived redo log file. At some future point, the corresponding datafile is damaged by a permanent media failure. The most recent backup of the damaged datafile must be used, and incomplete media recovery can only recover the database up to the damaged archived redo log file.
- If time-based recovery is needed, the damaged archived redo log file may be required if you use old datafile backups that were taken before the original online redo log group was written. In this case, the incomplete media recovery can only recover the database up to the damaged archived redo log group.
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
1. If the instance is still running, immediately abort the current instance with the Server Manager Shutdown Abort option of the Shutdown Database dialog box, or the SHUTDOWN command with the ABORT option.
2. Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, you can proceed with database recovery by restoring damaged control files to an alternative storage device.
3. Use an intact copy of the database's control file to copy over the damaged control files. If possible, copy the intact control file to the original locations of all damaged control files. If the hardware problem persists, copy the intact control file to alternative locations. If you restored all damaged control files to their original location, proceed to Step 5. If all damaged control files were not restored, or not restored to their original location, proceed to Step 4.
4. If all damaged control files were not restored, or not restored to their original location in Step 3, the parameter file of the database must be edited so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored.
5. Start a new instance. Mount and open the database.
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:
- If you have executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS since you made the last structural change to the database, and if you have saved the SQL command output, then you can use the CREATE CONTROLFILE command from the output as-is. If, however, your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACE was performed before you made a structural change to the database, then you must edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect that structural change. For example, if you recently added a datafile to the database, then you should add that datafile to the DATAFILE clause of the CREATE CONTROLFILE command.
- If you have not backed up the control file using the TO TRACE option, but instead have used the TO filename option of the ALTER DATABASE BACKUP CONTROLFILE command, then you can use the control file copy to obtain SQL command output. You can do this by copying the backup control file and executing STARTUP MOUNT before executing ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If your control file copy predated a recent structural change, you must edit the TO TRACE output to reflect that structural change.
- If you do not have a backup of the control file (in either TO TRACE format or TO filename format), then you must generate the CREATE CONTROLFILE command manually.
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.