14
Performing Operating System Recovery

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

What Is Media Recovery?

Media recovery is the recovery of a database to a specific time. It involves these basic stages:

Restoring Files

The first step in media recovery is to restore database files. Restoring involves reconstructing an original copy of a files or files from a backup. For example, assume that you query V$DATAFILE to see your datafiles:

SQL> SELECT name FROM v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/dbs/tbs_01.f
/oracle/dbs/tbs_02.f
/oracle/dbs/tbs_03.f
/oracle/dbs/tbs_11.f
/oracle/dbs/tbs_12.f
/oracle/dbs/tbs_21.f
/oracle/dbs/tbs_22.f
7 rows selected.

Restoring these datafiles involves using O/S commands to copy backups of the datafiles to their default locations, overwriting the files with the same names currently there, or to new locations. For example, to restore datafile tbs_12.f from backup tbs_12.bak, you might enter:

% cp /oracle/backup/tbs_12.bak /oracle/dbs/tbs_12.f

Recovering Datafiles

The second step is to perform media recovery on the specified datafiles. Media recovery is the application of online and archived redo logs to restored datafiles in order to update them to a current or non-current time. Media recovery is complete when you use all available redo to recover the database to the most current SCN; it is incomplete when you do not.

Oracle recovery occurs in two phases:

Rolling forward
Rolling back

During the roll forward phase of recovery, Oracle applies the changes recorded in the redo log records--either online, archived, or both--to the datafiles. Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. After rolling forward, the data blocks contain committed as well as uncommitted changes.

During the rolling back phase, Oracle use rollback segments to undo the effects of uncommitted transactions applied during the rolling forward phase. Oracle uses rollback segments to identify those transactions that were never committed, yet were recorded in the redo log. Figure 14-1 shows the two stages of rolling forward and rolling back.

Figure 14-1 Basic Recovery Procedure

Determining Which Files to Recover

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

The following query displays the file ID numbers of datafiles that require media recovery as well as the reason for recovery (if known) and the SCN/time when recovery needs to begin:

SQL> SELECT * FROM v$recover_file;

FILE#      ONLINE  ERROR              CHANGE#    TIME     
---------- ------- ------------------ ---------- ---------
        14 ONLINE                              0          
        15 ONLINE  FILE NOT FOUND              0          
        21 OFFLINE OFFLINE NORMAL              0

Note:
The view 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.

Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. For example, enter:

SQL> SELECT d.name, t.name 
  2  FROM v$datafile d, v$tablespace t 
  3  WHERE t.ts# = d.ts# 
  4  AND d.file# in (14,15,21);  # use values obtained from V$RECOVER_FILE query

NAME                               TABLESPACE_NAME
---------------------------------- ----------------
/oracle/dbs/tbs_14.f               TBS_1  
/oracle/dbs/tbs_15.f               TBS_2  
/oracle/dbs/tbs_21.f               TBS_3

Besides determining which files to recover, you must also know which files you should not recover. The following have special implications for media recovery:

Unrecoverable Tables and Indexes

You can create tables and indexes using the CREATE TABLE AS SELECT statement. 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, you cannot recover objects created unrecoverable, 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 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. Drop the unrecoverable objects and re-create 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 media recovery. The table was unrecoverable (and thus marked as corrupt after recovery), however, 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 "Determining Whether a Backup is Required After UNRECOVERABLE Operations".

Read-Only Tablespaces

Media recovery with the USING BACKUP CONTROLFILE option checks for read-only files. You cannot recover a read-only file. To avoid this error, take datafiles from read-only tablespaces offline before doing recovery with a backup control file.

Use 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 unavailable, execute a CREATE CONTROLFILE statement as described in "Losing All Copies of the Current Control File". If you need to re-create a control file for a database with read-only tablespaces, issue the following to obtain the procedure that you need to follow:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

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.

See Also: To learn about taking trace backups of the control file, see "Backing Up the Control File to a Trace File".

Restoring Files

If you determine that media recovery is necessary, restore the files necessary to perform it. Learn how to execute the following tasks:

Restoring Backup 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. If you cannot restore a damaged datafile to its original location (for example, you must replace a disk, so you restore the files to an alternate disk), then you must indicate the new locations of these files to the control file of the associated database.

To restore backup datafiles to their default location:

Determine which datafiles to recover using the techniques described in "Determining Which Files to Recover".
Copy backups of the damaged datafiles to their default location using O/S commands. For example, to restore tbs_14.f on UNIX you might issue:
```
% cp /disk2/backup/tbs_14.bak /disk1/oracle/dbs/tbs_14.f   
```

Re-Creating Datafiles when Backups Are Unavailable

If a datafile is damaged and no backup of the file is available, you can still recover the datafile if:

All archived 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).

To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. 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 disk2:
```
ALTER DATABASE CREATE DATAFILE 'disk1:users1' AS 'disk2:users1';
    

         
```
This statement creates an empty file that matches the lost file. Oracle looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

Perform media recovery on the empty datafile. For example, enter:

RECOVER DATAFILE 'disk2:users1'

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.

Note:
You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement, since the necessary redo data is 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 restore necessary archived redo logs:

To determine which archived redo log files you need, query V$LOG_HISTORY and V$RECOVERY_LOG. You will need all redo information from the time the datafile was added to the database if no backup of the datafile is available.

View Description

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

If space is available, restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST. Oracle locates the correct log automatically when required during media recovery.

For example, enter:
```
% cp /disk2/arc_backup/*.arc /disk1/oracle/dbs/arc_dest
```
If sufficient space is not available at the location indicated by the destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:
```
SET LOGSOURCE /disk2/temp   # set location using SET command
ALTER DATABASE RECOVER FROM '/disk2/temp' DATABASE;  # set in RECOVER statement
```
After an archived log is applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example, after making the log directory your working directory, enter:
```
% rm *.arc 
```

View	Description
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 initialization 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.

See Also: For more information about the data dictionary views, see the Oracle8i Reference.

Understanding Basic Media Recovery Procedures

Before beginning recovery, familiarize yourself with the following topics:

Using Media Recovery Statements

Oracle uses these basic media recovery SQL*Plus statements, which differ only in the way the system determines the set of files to be recovered:

RECOVER DATABASE
RECOVER TABLESPACE
RECOVER DATAFILE

Each statement uses the same criteria to determine whether files are recoverable. Oracle prevents two recovery sessions from recovering the same file and prevents media recovery of a file that is in use.

You can also use the SQL statement ALTER DATABASE RECOVER, although Oracle strongly recommends you use the SQL*Plus RECOVER statement instead so that Oracle will prompt you for the names of the archived redo logs.

See Also: For more information about SQL*Plus RECOVER statements, see SQL*Plus User's Guide and Reference. For more information about the ALTER DATABASE RECOVER statement, see Oracle8i SQL Reference.

RECOVER DATABASE Statement

RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. For example, issue the following at the SQL prompt to recover the whole database:

RECOVER DATABASE

If you shut down all instances cleanly, and did not restore any backups, issuing RECOVER DATABASE returns an error indicating that no recovery is required. It also fails if any instances have the database open, since they have the datafile locks. To perform media recovery on an entire database, the database must be mounted EXCLUSIVE and closed.

RECOVER TABLESPACE Statement

RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. For example, enter the following at the SQL prompt to recover tablespace TBS_1:

RECOVER TABLESPACE tbs_1

The tablespaces must be offline to perform the recovery. Oracle indicates an error if none of the files require recovery.

RECOVER DATAFILE Statement

RECOVER DATAFILE lists the datafiles to be recovered. For example, enter the following at the SQL prompt to recover datafile /oracle/dbs/tbs_22.f:

RECOVER DATAFILE '/oracle/dbs/tbs_22.f'

The database can be open or closed, provided that you can acquire the media recovery locks. If the database is open in any instance, then datafile recovery can only recover offline files.

See Also: For more information about media recovery statements, see the Oracle8i SQL Reference.

Applying Archived Redo Logs

During complete or incomplete media recovery, Oracle applies redo log files 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 SQL*Plus, it returns the following lines and prompts:

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 you use an ALTER DATABASE ... RECOVER statement. However, no prompt is displayed.

Suggested Archived Redo Log Filenames

Oracle suggests archived redo log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT and using information from the control file. For example, the following are possible settings for archived logs:

LOG_ARCHIVE_DEST_1 = /oracle/arc_dest/arc
LOG_ARCHIVE_FORMAT = r_%t_%s.arc

SQL> SELECT name FROM v$archived_log;

NAME
-------------------------------
/oracle/arc_dest/arcr_1_467.arc
/oracle/arc_dest/arcr_1_468.arc
/oracle/arc_dest/arcr_1_469.arc
/oracle/arc_dest/arcr_1_470.arc

Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST destination, and the value for LOG_ARCHIVE_FORMAT is never altered, Oracle can suggest and apply log files to complete media recovery automatically.

To restore archived redo logs to a non-default location:

Change the value for this parameter to a new location. For example, enter:

LOG_ARCHIVE_DEST_1 = /oracle/new_location

Move the log files to the new location. For example, enter:

% cp /oracle/arc_dest/* /oracle/new_location

Start a new instance and mount the database:
```
STARTUP MOUNT
    

         
```
Initiate beginning media recovery as usual. For example, enter:
```
RECOVER DATABASE
    

         
```

In some cases, you may want to override the current setting for the destination parameter as a source for redo 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 redo log files at the location specified by the destination parameter.

To recover using logs in a non-default location:

Mount the archived redo logs to an alternate location. For example, enter:

% cp /disk1/oracle/arc_dest/* /disk2/temp

Specify the alternate location to Oracle for the recovery operation. Use the LOGSOURCE parameter of the SET statement or the RECOVER ... FROM parameter of the ALTER DATABASE statement. For example, enter:
```
SET LOGSOURCE "/disk2/temp"
    

         
```
Recover the offline tablespace:
```
RECOVER TABLESPACE offline_tbsp
```
Note:
Overriding the redo log source does not affect the archive redo log destination for filled online groups being archived.

Consider overriding the current setting for the destination parameter 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 Logs Automatically Using the SQL*Plus RECOVER Statement

When using SQL*Plus, use the following command to automate the application of the default filenames of archived redo logs needed during recovery:

SET AUTORECOVERY ON

No interaction is required when you issue the RECOVER statement, provided that the necessary files are in the correct locations with the correct names.

The filenames used when you use SET AUTORECOVERY ON are derived from the values of the initialization parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1 in conjunction with LOG_ARCHIVE_FORMAT. If you execute SET AUTORECOVERY OFF, which is the default option, then you must enter the filenames manually, or accept the suggested default filename.

To automate the application of archived redo logs:

Restore a backup of the offline datafiles. This example restores a consistent backup of the whole database:
```
% cp /oracle/work/BACKUP/tbs* /oracle/dbs
```
Make sure the database is mounted. For example, if the database is shut down, enter:
```
SQL> STARTUP MOUNT
```

Turn on autorecovery:

SQL> SET AUTORECOVERY ON
Autorecovery                    ON

Recover the desired datafiles. This example recovers the whole database:
```
SQL> RECOVER DATABASE
```

Oracle automatically suggests and applies the necessary archived logs:

ORA-00279: change 53577 generated at 01/26/99 19:20:58 needed for thread 1
ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_802.arc
ORA-00280: change 53577 for thread 1 is in sequence #802
Log applied.
ORA-00279: change 53584 generated at 01/26/99 19:24:05 needed for thread 1
ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_803.arc
ORA-00280: change 53584 for thread 1 is in sequence #803
ORA-00278: log file '/oracle/work/arc_dest/arcr_1_802.arc' no longer needed for this 
recovery
Log applied.
ORA-00279: change 53585 generated at 01/26/99 19:24:14 needed for thread 1
ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_804.arc
ORA-00280: change 53585 for thread 1 is in sequence #804
ORA-00278: log file '/oracle/work/arc_dest/arcr_1_803.arc' no longer needed for this 
recovery
Log applied.
Media recovery complete.

If you use an OPS configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only compute the name of the first archived redo log file from the first thread. You may have to apply the first log file from the other threads. Once the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent logfiles in those threads.

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

Applying Logs Individually Using ALTER DATABASE RECOVER

When you perform media recovery using SQL statements, Oracle does not display a prompt for log files 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, 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.):

SQL> 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 #
SQL> 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 #
SQL> ALTER DATABASE RECOVER LOGFILE 'logfile2';
. . . 
Repeat until all logs are applied.)
Statement processed.
SQL> ALTER TABLESPACE users ONLINE;
Statement processed.

Applying Logs Automatically Using ALTER DATABASE RECOVER

In this example, assume that the backup files have been restored, and that the user has administrator privileges. As in the method you used with SQL*Plus, 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_filename;

An example of the first statement follows:

SQL> ALTER DATABASE RECOVER AUTOMATIC TABLESPACE users;
Statement processed.
SQL> 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:

SQL> 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 #
SQL> ALTER DATABASE RECOVER AUTOMATIC LOGFILE 'logfile1';
Statement processed.
SQL> 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 statement, 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 Oracle8i Reference.

Successful Application of Redo Logs

If you are using SQL*Plus's recovery options (not SQL statements), each time Oracle finishes applying a redo log file, the following message is returned:

Log applied.

Oracle then prompts for the next log in the sequence or, if the most recently applied log is the last required log, terminates recovery.

Unsuccessful Application of Redo Logs

If the suggested file is incorrect or you provide an incorrect filename, Oracle returns an error message. For example, you may see something similar to the following:

ORA-00308: cannot open archived log '/oracle/work/arc_dest/arcr_1_811.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery cannot continue until the required redo log file is applied. If Oracle returns an error message after supplying a redo log filename, the following scenarios are possible:

Error Possible Cause Solution

ORA-27037: unable to obtain file status

Entered wrong filename.
Log is missing.

Re-enter correct filename.
Restore backup archived redo log.

ORA-27047: unable to read the header block of file

The log may have been partially written or become corrupted.

If you can locate an uncorrupted or complete copy of the log, apply that copy; you do not need to restart recovery.
If no copy of the log exists and you know the time of the last valid redo entry, perform incomplete recovery; in this case, restart recovery from the beginning, including restoring backups.

Error	Possible Cause	Solution
ORA-27037: unable to obtain file status	Entered wrong filename. Log is missing.	Re-enter correct filename. Restore backup archived redo log.
ORA-27047: unable to read the header block of file	The log may have been partially written or become corrupted.	If you can locate an uncorrupted or complete copy of the log, apply that copy; you do not need to restart recovery. If no copy of the log exists and you know the time of the last valid redo entry, perform incomplete recovery; in this case, restart recovery from the beginning, including restoring backups.

Interrupting the Application of Redo Logs

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

Enter the word CANCEL when prompted for a redo log file.
Use your operating system's interrupt signal if you must abort when recovering an individual datafile, or when automated recovery is in progress.

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.

There are several reasons why, after starting recovery, you may want to restart. For example, if 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.

Recovering a Database in NOARCHIVELOG Mode

If a database is in NOARCHIVELOG mode and a media failure damages some or all of the datafiles, the only option for recovering the database is usually to restore the most recent whole database backup. If you are using Export to supplement regular backups, then 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. If your database was in ARCHIVELOG mode, however, the redo log covering this interval would have been available as archived log files or online log files. Using archived redo logs would have enabled you to use complete or incomplete recovery to reconstruct your database, thereby minimizing 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 consistent whole database backup (your only option at this point), follow the steps below.

To restore the most recent whole database backup to the default location:

If the database is open, abort the instance:
```
SHUTDOWN ABORT
    

         
```
Correct the media problem so that the backup database files can be restored to their original locations.
Restore the most recent whole database backup using O/S commands. Restore all of the datafiles and control files of the whole database backup, not just the damaged files. This example restores a whole database backup:
```
% cp /oracle/work/BACKUP/tbs* /oracle/dbs  # restores datafiles
% cp /oracle/work/BACKUP/cf.f /oracle/dbs  # restores control file
```

Mimic incomplete database recovery by issuing the following statement:

ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;

Open the database and reset the current redo log sequence to 1:
```
ALTER DATABASE OPEN RESETLOGS;
    

         
```
A RESETLOGS operation invalidates all redo in the online logs. Restoring from a whole database 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 failure.

To restore the most recent whole database backup to a new location:

If the database is open, shut it down:
```
SHUTDOWN NORMAL
    

         
```
If the hardware problem has not been corrected and some or all of the database files must be restored to alternative locations, restore the most recent whole database backup to a new location. Restore all of the datafiles and control files of the whole database backup, not just the damaged files. For example, enter:
```
% cp /disk2/BACKUP/tbs* /disk3/oracle/dbs
% cp /disk2/BACKUP/cf.f /disk3/oracle/dbs
```
If necessary, edit the restored parameter file to indicate the new location of the control files.
```
     CONTROL_FILES = '/disk3/oracle/dbs/cf.f'
```
Start an instance using the restored and edited parameter file and mount, but do not open, the database. For example, this statements mounts the database using the initialization file initPROD1.ora:
```
STARTUP MOUNT pfile=initPROD1.ora
    

         
```
If the restored datafile filenames will be different, rename the restored datafiles in the control file. For example, you might enter:
```
ALTER DATABASE RENAME FILE '/disk1/oracle/dbs/tbs1.f' TO '/disk3/oracle/dbs/tbs1.f';
```

If applicable, rename the online redo log files. For example, enter:

ALTER DATABASE RENAME FILE '/disk1/oracle/dbs/log1.f' TO '/disk3/oracle/dbs/log1.f';

Mimic incomplete database recovery by issuing the following statement:
```
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
```
Mimic incomplete database recovery by issuing the following statement:
```
ALTER DATABASE RECOVER CANCEL;
    

         
```
Open the database and reset the current redo log sequence to 1:
```
ALTER DATABASE OPEN RESETLOGS;
    

         
```
A RESETLOGS operation invalidates all redo in the online logs. Restoring from a whole database 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 failure.

See Also: For more information about renaming and relocating datafiles, see the Oracle8i Administrator's Guide.

Recovering a Database in ARCHIVELOG Mode

To begin media recovery operations when your database is running in ARCHIVELOG mode, use one of the following options:

The ALTER DATABASE RECOVER statement
The SQL*Plus RECOVER statement (recommended)

To start any type of media recovery, you must adhere to the following restrictions:

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

See Also: For more information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

Performing Media Recovery in Parallel

Use parallel block recovery to tune the roll forward phase of media recovery. In parallel block recovery, Oracle uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient. For example, if the redo log contains a substantial number of entries, slave 1 takes responsibility for one part of the log file, slave 2 takes responsibility for another part, slave 3 takes responsibility for a third part, etc. Crash, instance, and media recovery of many datafiles on different disk drives are good candidates for parallel block recovery.

Use the RECOVERY_PARALLELISM initialization parameter to specify the number of concurrent recovery processes for any instance or media 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 you do not specify the PARALLEL clause of the RECOVER command. The value of this parameter must be greater than 1 and cannot exceed the value of the PARALLEL_MAX_SERVERS parameter. Parallel block recovery requires a minimum of eight recovery processes to improve upon serial recovery.

Note that recovery is usually I/O bound on reads to data blocks. Consequently, parallelism at the block level may only help recovery performance if it increases total I/Os, e.g., by bypassing O/S restrictions on asynchronous I/Os. Systems that have efficient asynchronous I/O typical see little improvement from using parallel block recovery.

See Also: For more information on parallel recovery, see Oracle8i Tuning. For more information about the RECOVERY_PARALLELISM parameter, see the Oracle8i Reference.

Performing Complete Media Recovery

When you perform complete recovery, you can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later.

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

See Also: Familiarize yourself with the fundamental recovery concepts and strategies in Chapter 3, "Developing a Backup and Recovery Strategy".

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.

This section describes how to perform cancel-based media recovery in these stages:

Shut down the database and correct the media damage if possible.
Restore the necessary files.
Recover the database.

To prepare for closed database recovery:

If the database is open, shut it down using the ABORT option:
```
SHUTDOWN ABORT
    

         
```
If you are recovering from a media error, correct it if possible.
If the hardware problem that caused the media failure was temporary, and the data was undamaged (for example, a disk or controller power failure), simply start the database and resume normal operations:
```
STARTUP
```

To restore the necessary files:

Determine which datafiles to recover using the techniques described in "Determining Which Files to Recover".
If files are permanently damaged, identify the most recent backups for the damaged files. Restore only the datafiles damaged by the media failure: do not restore any undamaged datafiles or any online redo log files.

For example, if /oracle/dbs/tbs_10.f is the damaged file, you may consult your records and determine that /oracle/backup/tbs_10.backup is the most recent backup. If you do not have a backup of a specific datafile, you may be able to create an empty replacement file that can be recovered.

Use an O/S utility to restore the files to their default location or to a new location. For example, a UNIX user restoring /oracle/dbs/tbs_10.f to its default location might enter:

% cp /oracle/backup/tbs_10.backup /oracle/dbs/tbs_10.f

Follow these guidelines when determining where to restore datafile backups:

If Then

The hardware problem is repaired and you can restore the datafiles to their default locations

Restore the datafiles to their default locations and begin media recovery.

The hardware problem persists and you cannot restore datafiles to their original locations

Restore the datafiles to an alternative storage device. Indicate the new location of these files to the control file. Use the operation described in "Renaming and Relocating Datafiles" in the Oracle8i Administrator's Guide, as necessary.

If	Then
The hardware problem is repaired and you can restore the datafiles to their default locations	Restore the datafiles to their default locations and begin media recovery.
The hardware problem persists and you cannot restore datafiles to their original locations	Restore the datafiles to an alternative storage device. Indicate the new location of these files to the control file. Use the operation described in "Renaming and Relocating Datafiles" in the Oracle8i Administrator's Guide, as necessary.

To recover the restored datafiles:

Connect to Oracle with administrator privileges, then start a new instance and mount, but do not open, the database. For example, enter:
```
STARTUP MOUNT
    

         
```
Obtain the datafile names of all datafiles by checking the list of datafiles that normally accompanies the current control file or querying the V$DATAFILE view. For example, enter:
```
SELECT name FROM v$datafile;
    

         
```
Ensure that all datafiles of the database are online. For example, to guarantee that a datafile named /oracle/dbs/tbs_10.f is online, enter the following:
```
ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;
    

         
```
If a specified datafile is already online, Oracle ignores the statement. If you prefer, create a script to bring all datafiles online at once as in the following:
```
SQL> SPOOL onlineall.sql
SQL> SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM v$datafile;
SQL> SPOOL OFF
SQL> @onlineall
    

         
```

Issue the command to recover the database, tablespace, or datafile. For example, enter:

RECOVER DATABASE   # recovers whole database
RECOVER TABLESPACE users   # recovers specific tablespace
RECOVER DATAFILE '/oracle/dbs/tbs_10';   # recovers specific datafile

Follow these guidelines when deciding which statement to execute:

To Then

Recover all damaged files in one step

Execute RECOVER DATABASE (recommended) or ALTER DATABASE RECOVER DATABASE

Recover an individual tablespace

Execute RECOVER TABLESPACE (recommended) or ALTER DATABASE RECOVER TABLESPACE

Recover an individual damaged datafile

Execute RECOVER DATAFILE (recommended) or ALTER DATABASE RECOVER DATAFILE

Parallelize recovery of the whole database or an individual datafile

See "Choosing Parallel Recovery Options".

If you choose not to automate the application of archived redo logs, accept or reject each required redo log file that Oracle prompts you for. If you automated recovery, Oracle applies the necessary logs automatically. Oracle continues until all required archived and online redo log files have been applied to the restored datafiles.
Oracle notifies you when media recovery is complete. If no archived redo log files are required for complete media recovery, Oracle applies all necessary online redo log files and terminates recovery.
Open the database:
```
ALTER DATABASE OPEN;
    

         
```

To	Then
Recover all damaged files in one step	Execute RECOVER DATABASE (recommended) or ALTER DATABASE RECOVER DATABASE
Recover an individual tablespace	Execute RECOVER TABLESPACE (recommended) or ALTER DATABASE RECOVER TABLESPACE
Recover an individual damaged datafile	Execute RECOVER DATAFILE (recommended) or ALTER DATABASE RECOVER DATAFILE
Parallelize recovery of the whole database or an individual datafile	See "Choosing Parallel Recovery Options".

See Also: For more information about applying redo log files, see "Performing Complete Media Recovery".

Performing Open Database Recovery

It is possible for a media failure to occur while the database remains open, leaving the undamaged datafiles online and available for use. Oracle automatically takes the damaged datafiles offline.

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.

This section describes how to perform cancel-based media recovery in these stages:

Prepare the database for recovery by making sure it is open and taking the affected tablespaces offline.
Restore the necessary files.
Recover the database.

See Also: To proceed with complete media recovery of SYSTEM tablespaces datafiles, follow the procedure in "Performing Closed Database Recovery".

To prepare for open database recovery when the database is shut down:

Start a new instance, and mount and open the database. For example, enter:
```
STARTUP
    

         
```
After the database is open, take all tablespaces that contain damaged datafiles offline. For example, if tablespace TBS_1 contains damaged datafiles, enter:
```
ALTER TABLESPACE tbs_1 OFFLINE TEMPORARY;
    

         
```
Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, proceed with database recovery by restoring damaged files to an alternative storage device.

To prepare for recovery in an open database:

If the database is open when you discover that recovery is required, take all tablespaces containing damaged datafiles offline. For example, if tablespace TBS_1 contains damaged datafiles, enter:
```
ALTER TABLESPACE tbs_1 OFFLINE TEMPORARY;
    

         
```
If possible, take the damaged tablespaces offline with temporary priority to minimize the amount of recovery.
Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, proceed with database recovery by restoring damaged files to an alternative storage device.

To restore datafiles in an open database:

If files are permanently damaged, restore the most recent backup files 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 use ALTER DATABASE CREATE DATAFILE to create an empty replacement file, which can be recovered.
If you restored one or more damaged datafiles to alternative locations, indicate the new locations of these files to the control file of the associated database by using the procedure in "Renaming and Relocating Datafiles" in the Oracle8i Administrator's Guide, as necessary.

To recover offline tablespaces in an open database:

Connect to the database with administrator privileges. For example, connect as SYS to database PROD1:
```
% sqlplus sys/sys_pwd@prod1
    

         
```
Start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces using one step:
```
RECOVER TABLESPACE tbs_1  # begins recovery on datafiles in tbs_1
```
Note:
For maximum performance, use parallel recovery to recover the datafiles. See"Performing Media Recovery in Parallel".
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 using SET AUTORECOVERY ON, Oracle prompts 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 to complete media recovery.

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.
When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online. For example, to bring tablespace TBS_1 online, issue:
```
ALTER TALBESPACE tbs_1 ONLINE;
    

         
```

See Also: For more information about redo log application, see "Performing Complete Media Recovery". For more information about creating datafiles, see the Oracle8i Administrator's Guide.

Performing Incomplete Media Recovery

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

Note that 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 media recovery in these stages:

Prepare for recovery by backing up the database and correct any media failures.
Restore backup control files (if necessary) and backup datafiles.
Perform media recovery on the restored backup using the RECOVER DATABASE statement, terminating it with a CANCEL.

To prepare for cancel-based recovery:

If the database is still open and incomplete media recovery is necessary, abort the instance:
```
SHUTDOWN ABORT
    

         
```
Make a whole backup of the database--all datafiles, a control file, and the parameter files of the database--as a precautionary measure in case an error occurs during the recovery procedure.
If a media failure occurred, correct the hardware problem that caused the failure.

To restore the files necessary for cancel-based recovery:

If the current control files do not match the physical structure of the database at the intended time of recovery, e.g., if a datafile was added after the point in time to which you intend to recover, then restore a backup of the control file.

The restored control file should reflect the database's physical file structure, i.e., contain the names of datafiles and online redo log files, at the point at which incomplete media recovery is intended to finish. Review the list of files that correspond to the current control file as well as each control file backup to determine the correct control file to use.

If necessary, replace all current control files of the database with the correct control file backup. Alternatively, create a new control file.

Note:
If a database control file cannot function or be replaced with a control file backup, take it out of the CONTROL_FILES parameter list in the parameter file associated with the database.

Restore backups taken as part of a full or partial backup of all the datafiles of the database. You must have taken all backup files used to replace existing datafiles 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, create an empty replacement file that can be recovered. If you added a datafile after the intended time of recovery, you do not need to restore a backup for this file since it will no longer be used for the database after recovery is complete.
If you solved the hardware problem that caused a media failure and can restore all datafiles to their original locations, then restore the files. If a hardware problem persists, restore damaged datafiles to an alternative storage device.

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

To perform cancel-based recovery:

Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
```
% sqlplus sys/change_on_install@prod1
    

         
```
Start a new instance and mount the database:
```
STARTUP MOUNT
    

         
```
If you restored one or more damaged datafiles to alternative locations, indicate the new locations of these files to the control file of the associated database.
Begin cancel-based recovery:
```
RECOVER DATABASE UNTIL CANCEL
```
If you are using a backup control file with this incomplete recovery, specify the USING BACKUP CONTROLFILE option in the RECOVER command.
```
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
```
Note:
If you do not specify the UNTIL CANCEL clause on the RECOVER statement, you will not be able to open the database until a complete recovery is done.

Oracle applies the necessary redo log files to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. Note that if the control file is a backup file, you must supply names of online logs.

Note:
If you use an OPS configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only computes the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. Once the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent log files in those threads.

Continue applying redo log files until the most recent, undamaged redo log file has been applied to the restored datafiles.
Cancel recovery after Oracle has applied the redo log file just prior to the damaged file:
```
CANCEL
    

         
```
Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before it is complete and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file.

Performing Time-Based Recovery

This section describes how to perform the time-based media recovery procedure in these stages:

Back up the database as a precaution and correct any media failures.
Restore backup control files (if necessary) and backup datafiles.
Perform media recovery on the restored backup using the RECOVER DATABASE statement with the UNTIL TIME option.

Note:
If you are performing time-based, incomplete recovery using a backup control file and have read-only tablespaces, contact Oracle Support before attempting this procedure.

To prepare for time-based recovery:

Follow the same preparation procedure described in the section "Performing Cancel-Based Recovery".

To restore the files necessary for time-based recovery and bring them online:

If the current control files do not match the physical structure of the database at the intended time of recovery, restore a backup control file that reflects the database's physical file structure at the point at which incomplete media recovery should finish. To determine which control file backup to use:

Review the list of files that corresponds to the current control file and each control file backup to determine the correct control file to use.
If necessary, replace all current control files of the database with the correct control file backup.

Alternatively, create a new control file to replace the missing one.

Note:
If a database control file cannot function or be replaced with a control file backup, take it out of the CONTROL_FILES parameter list in the parameter file associated with the database.

Restore backups of all the datafiles of the database. All backups used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to January 2 at 2 p.m., then restore all datafiles with backups completed before this time. Follow these guidelines:

If Then

You do not have a backup of a datafile

Create an empty replacement file, which can be recovered.

A datafile was added after the intended time of recovery

Do not restore a backup of this file, since it will no longer be used for the database after recovery completes.

The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations

Restore the files and skip Step 5 of this procedure.

A hardware problem persists

Restore damaged datafiles to an alternative storage device.

If	Then
You do not have a backup of a datafile	Create an empty replacement file, which can be recovered.
A datafile was added after the intended time of recovery	Do not restore a backup of this file, since it will no longer be used for the database after recovery completes.
The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations	Restore the files and skip Step 5 of this procedure.
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.

Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
```
% sqlplus sys/change_on_install@prod1
    

         
```
Start a new instance and mount the database:
```
STARTUP MOUNT
    

         
```
If one or more damaged datafiles were restored to alternative locations in Step 2, indicate the new locations of these files to the control file of the associated database. For example, enter:
```
ALTER DATABASE RENAME FILE '/oracle/dbs/df2.f' TO '/oracle/newloc/df2.f';
```
Obtain the names of all datafiles requiring recovery by:
- Checking the list of datafiles that normally accompanies the control file being used.
- Querying the V$DATAFILE view.
Make sure that all datafiles of the database are online. All datafiles of the database must be online unless an offline tablespace was taken offline normally. For example, to guarantee that a datafile named user1 (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.

To perform time-based recovery:

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

         
```
Apply the necessary redo log files to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup, you must supply names of online logs.
Apply 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.

Performing Change-Based Recovery

This section describes how to perform recovery to a specified SCN in these stages:

Back up the database as a precaution and correct any media failures.
Restore backup control files (if necessary) and backup datafiles.
Perform media recovery on the restored backup using the RECOVER DATABASE statement with the UNTIL TIME option.

To prepare for change-based recovery:

Follow the same preparation procedure described in the section "Performing Cancel-Based Recovery".

To restore files necessary for change-based recovery:

Follow the same restore procedure described in the section "Performing Time-Based Recovery".

To perform change-based recovery:

Begin change-based recovery, specifying the SCN for recovery termination. The SCN is specified as a decimal number without quotation marks. For example, to recover until SCN 100 issue:
```
RECOVER DATABASE UNTIL CHANGE 100;
    

         
```
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_1 or 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.
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 Media Recovery

To preserve the log sequence number when opening a database after recovery, execute the following statement:

ALTER DATABASE OPEN NORESETLOGS;

To reset the log sequence number when opening a database after recovery, execute the following statement:

ALTER DATABASE OPEN RESETLOGS;

The RESETLOGS option is required after incomplete media recovery. Resetting the redo log:

Discards any redo information that was not applied during recovery, ensuring that it will never be applied.
Re-initializes 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.

.

Deciding Whether to Specify RESETLOGS or NORESETLOGS

Use the following rules when deciding whether to specify RESETLOGS or NORESETLOGS:

Always specify the RESETLOGS option after incomplete media recovery. For example, you must have specified a previous time or SCN, not one in the future.
Specify the NORESETLOGS option after performing complete media recovery (unless you used a backup control file). This rule applies when you intentionally performed complete recovery and when you performed incomplete recovery but actually recovered all changes in the redo logs anyway.
Always specify the RESETLOGS option if you used a backup of the control file in recovery, regardless of whether you performed complete or incomplete recovery.
Do not specify the RESETLOGS option if you are using the archived logs of the database for a standby database. If you must reset the log, then you will have to re-create your standby database.

If you reset the log sequence number, Oracle returns different messages depending on whether recovery was complete or incomplete. If the recovery was complete, the following message appears in the alert.log file:

RESETLOGS after complete recovery through change scn

If the recovery was incomplete, this message is reported in the ALERT file:

RESETLOGS after incomplete recovery UNTIL CHANGE scn

If you attempt to reset the log when you should not, or if you neglect to reset the log when you should, Oracle returns an error and does not open the database. Correct the error and try again.

Guidelines for Opening in RESETLOGS Mode

Perform the following actions after opening the database in RESETLOGS mode:

Making a Whole Database Backup

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

Checking the Alert Log

After opening the database using the RESETLOGS option, check the alert.log to see whether Oracle 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, Oracle 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, you must drop the tablespace containing the datafile.

In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file. In both cases, Oracle includes an explanatory message in the alert.log file to let you know what was found.

See Also: For more information about applying redo logs, see "Performing Complete Media Recovery".

Recovering a Pre-RESETLOGS Backup

In pre-Oracle8i releases, DBAs typically backed up online logs when performing cold consistent backups to avoid opening the database with the RESETLOGS option (if they were planning to restore immediately).

A classic example of this technique was disk maintenance, which required the database to be backed up, deleted, the disks reconfigured, and the database restored. DBAs realized that by not restarting in RESETLOGS mode, they would not have to perform a whole database backup immediately after the database was restored. This backup was required since it was impossible to perform recovery using a backup taken before using RESETLOGS--especially if any errors occurred after resetting the logs.

In Oracle version 8 and higher, there is only one situation in which you can use a pre-RESETLOGS backup to roll forward--if you have a consistent backup of the database, taken immediately before you open the database with the RESETLOGS option, and a control file that is valid after you open the database with RESETLOGS. It is then unnecessary to back up or restore online redo logs.

The following scenario illustrates a situation when you can use a pre-RESETLOGS backup. Suppose you wish to perform hardware striping reconfiguration, which requires the database files to be backed up and deleted, the hardware reconfigured, and the database restored.

On Friday night you perform the following actions:

Cleanly shut down the database:
```
SHUTDOWN IMMEDIATE
    

         
```
Perform a whole database backup (control files and datafiles). For example, enter
```
% cp /oracle/dbs/* /oracle/backup
```
Note:
At this point you must not reopen the database.

.
Perform O/S maintenance.
Restore the datafiles and control files. For example, enter:
```
% cp /oracle/backup/* /oracle/dbs
```
Mount the database:
```
STARTUP MOUNT
    

         
```

Initiate cancel-based recovery:

RECOVER DATABASE UNTIL CANCEL

Open the database with the RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS;

On Saturday morning the scheduled batch jobs run, generating archived redo logs. If a hardware error occurs on Saturday night that requires you to restore the whole database, you can restore the backup taken immediately before opening the database with the RESETLOGS option, and roll forward using the logs produced on Saturday.

On Saturday night you do the following:

Abort the instance (if it still exists):
```
SHUTDOWN ABORT
    

         
```
Restore all damaged files from the backup made on Friday night:
```
% cp /oracle/backup/* /oracle/dbs
```
Note:
If you have the current control file, do not restore it; otherwise you must restore a control file that was valid after opening the database with RESETLOGS.
Begin complete recovery, applying all the archived logs produced on Saturday. Use SET AUTORECOVERY ON to automate the log application.
```
SET AUTORECOVERY ON
RECOVER DATABASE
    

         
```
Open the database:
```
STARTUP
    

         
```

In this scenario, if you had opened the database after the Friday night backup and before opening the database with RESETLOGS, or, did not have a control file from after opening the database, you would not be able to use the Friday night backup to roll forward. You must have a backup after opening the database with the RESETLOGS option in order to be able to recover.

14Performing Operating System Recovery

What Is Media Recovery?

Restoring Files

Recovering Datafiles

Figure 14-1 Basic Recovery Procedure

Determining Which Files to Recover

Unrecoverable Tables and Indexes

Read-Only Tablespaces

Restoring Files

Restoring Backup Datafiles

To restore backup datafiles to their default location:

Re-Creating Datafiles when Backups Are Unavailable

To re-create a datafile for recovery:

Restoring Necessary Archived Redo Log Files

To restore necessary archived redo logs:

Understanding Basic Media Recovery Procedures

Using Media Recovery Statements

RECOVER DATABASE Statement

RECOVER TABLESPACE Statement

RECOVER DATAFILE Statement

Applying Archived Redo Logs

Suggested Archived Redo Log Filenames

To restore archived redo logs to a non-default location:

To recover using logs in a non-default location:

Applying Logs Automatically Using the SQL*Plus RECOVER Statement

To automate the application of archived redo logs:

Applying Logs Individually Using ALTER DATABASE RECOVER

Applying Logs Automatically Using ALTER DATABASE RECOVER

Successful Application of Redo Logs

Unsuccessful Application of Redo Logs

Interrupting the Application of Redo Logs

Recovering a Database in NOARCHIVELOG Mode

To restore the most recent whole database backup to the default location:

To restore the most recent whole database backup to a new location:

Recovering a Database in ARCHIVELOG Mode

Performing Media Recovery in Parallel

Performing Complete Media Recovery

Performing Closed Database Recovery

To prepare for closed database recovery:

To restore the necessary files:

To recover the restored datafiles:

Performing Open Database Recovery

To prepare for open database recovery when the database is shut down:

To prepare for recovery in an open database:

To restore datafiles in an open database:

To recover offline tablespaces in an open database:

Performing Incomplete Media Recovery

Performing Cancel-Based Recovery

To prepare for cancel-based recovery:

To restore the files necessary for cancel-based recovery:

To perform cancel-based recovery:

Performing Time-Based Recovery

To prepare for time-based recovery:

To restore the files necessary for time-based recovery and bring them online:

To perform time-based recovery:

Performing Change-Based Recovery

To prepare for change-based recovery:

To restore files necessary for change-based recovery:

To perform change-based recovery:

Opening the Database after Media Recovery

Deciding Whether to Specify RESETLOGS or NORESETLOGS

Guidelines for Opening in RESETLOGS Mode

Making a Whole Database Backup

Checking the Alert Log

Recovering a Pre-RESETLOGS Backup

14
Performing Operating System Recovery