Oracle8i Backup and Recovery Guide
Release 2 (8.1.6)

Part Number A76993-01

Library

Product

Contents

Index

Go to previous page Go to next page

5
Performing Media Recovery

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

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  

You can combine these queries in the following SQL*Plus script (sample output shown below):

COL df# FORMAT 999
COL df_name FORMAT a20
COL tbsp_name FORMAT a10
COL status FORMAT a7
COL error FORMAT a10

SELECT r.file# AS df#, d.name AS df_name, t.name AS tbsp_name, 
       d.status, r.error, r.change#, r.time
FROM v$recover_file r, v$datafile d, v$tablespace t
WHERE t.ts# = d.ts#
AND d.file# = r.file#
/

SQL> @script

 DF# DF_NAME              TBSP_NAME  STATUS  ERROR          CHANGE# TIME
---- -------------------- ---------- ------- ---------- ----------- ----------
  12 /oracle/dbs/tbs_41.f TBS_4      OFFLINE OFFLINE              0  
                                             NORMAL

  13 /oracle/dbs/tbs_42.f TBS_4      OFFLINE OFFLINE              0
                                             NORMAL

  20 /oracle/dbs/tbs_43.f TBS_4      OFFLINE OFFLINE              0
                                             NORMAL

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, then 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 are 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:

Oracle8i Standby Database Concepts and Administration for information about the impact of unrecoverable operations on a standby database. 

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:

"Backing Up the Control File to a Trace File" to learn about taking trace backups of the control 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:

  1. Determine which datafiles to recover using the techniques described in "Determining Which Files to Recover".

  2. Copy backups of the damaged datafiles to their default location using operating system 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:

To re-create a datafile for recovery:

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

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

    RECOVER DATAFILE 'disk2:users1'
    
    
    
  3. 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 because 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:

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

  2. 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
    
    
  3. 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 statement
    ALTER DATABASE RECOVER FROM "/disk2/temp" DATABASE;  # set in RECOVER statement
    
    
  4. 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 
    

    See Also:

    Oracle8i Reference for more information about the data dictionary views. 

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:

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:

SQL*Plus User's Guide and Reference for more information about SQL*Plus RECOVER statements, and Oracle8i SQL Reference for more information about the ALTER DATABASE RECOVER statement. 

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:

Oracle8i SQL Reference for more information about media recovery statements. 

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:

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

    LOG_ARCHIVE_DEST_1 = /oracle/new_location 
    
    
    
  2. Move the log files to the new location. For example, enter:

    % cp /oracle/arc_dest/* /oracle/new_location
    
    
    
  3. Start a new instance and mount the database:

    STARTUP MOUNT
    
    
    
  4. 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:

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

    % cp /disk1/oracle/arc_dest/* /disk2/temp
    
    
    
  2. 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"
    
    
    
  3. 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 statement 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:

  1. Restore a backup of the offline datafiles. This example restores a consistent backup of the whole database:

    % cp /oracle/work/BACKUP/tbs* /oracle/dbs
    
    
  2. Make sure the database is mounted. For example, if the database is shut down, enter:

    SQL> STARTUP MOUNT
    
    
  3. Turn on autorecovery:

    SQL> SET AUTORECOVERY ON
    Autorecovery                    ON
    
    
  4. Recover the desired datafiles. This example recovers the whole database:

    SQL> RECOVER DATABASE
    
    
  5. 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:

Your operating system-specific Oracle documentation for examples of log file application. 

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:

Oracle8i Reference for information about the content of all recovery-related views. 

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.  

Interrupting the Application of Redo Logs

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, interrupt recovery at any time by taking either of the following actions:

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

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:

  1. If the database is open, abort the instance:

    SHUTDOWN ABORT
    
    
    
  2. Correct the media problem so that the backup database files can be restored to their original locations.

  3. Restore the most recent whole database backup using operating system 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
    
    
  4. Mimic incomplete database recovery by issuing the following statement:

    ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
    
    
    
  5. 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:

  1. If the database is open, shut it down:

    SHUTDOWN NORMAL
    
    
    
  2. 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
    
    
  3. If necessary, edit the restored parameter file to indicate the new location of the control files.

         CONTROL_FILES = "/disk3/oracle/dbs/cf.f"
    
    
  4. 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
    
    
    
  5. 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";
    

    See Also:

    Oracle8i SQL Reference for more information about ALTER DATABASE RENAME FILE. 

  6. 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";
    
    
  7. Mimic incomplete database recovery by issuing the following statement:

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

    ALTER DATABASE RECOVER CANCEL;
    
    
    
  9. 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:

    Oracle8i Administrator's Guide for more information about renaming and relocating datafiles. 

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:

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

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, spawned process 1 takes responsibility for one part of the log file, process 2 takes responsibility for another part, process 3 takes responsibility for a third part, etc.

Note:

Typically, recovery is 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, for example, by bypassing operating system restrictions on asynchronous I/Os. Systems that have efficient asynchronous I/O typical see little improvement from using parallel block recovery. 

Use the following SQL*Plus RECOVER statement to perform parallel media recovery:

RECOVER PARALLEL ... ;

The PARALLEL clause of the RECOVER statement has the following options:

DEGREE integer 

Specifies the number of recovery processes used to apply redo entries to datafiles on each instance.  

DEGREE DEFAULT 

Indicates that twice the number of datafiles being recovered is the number of recovery processes to use. 

INSTANCES integer  

Specifies the number of instances to use for parallel recovery. The number of recovery processes specified with DEGREE is used on each instance. Thus, the total number of recovery processes is the integer specified with DEGREE multiplied by the integer specified with INSTANCES. INSTANCES is only pertinent for the Oracle Parallel Server. 

INSTANCES DEFAULT  

Has operating system-specific consequences. For more information about the default behavior of the INSTANCES DEFAULT specification, see the Oracle8i Parallel Server Documentation Set: Oracle8i Parallel Server Concepts; Oracle8i Parallel Server Setup and Configuration Guide; Oracle8i Parallel Server Administration, Deployment, and Performance manual. 

For example, to specify that 5 recovery processes should operate during recovery, specify as follows:

RECOVER DEGREE 5 ... ;

In a different scenario, assume that you are recovering 10 datafiles. Issue the following statement to specify that 20 processes should perform recovery:

RECOVER DEGREE DEFAULT;

Note:

The RECOVERY_PARALLELISM initialization parameter specifies the number of concurrent recovery processes for instance or crash recovery only. Media recovery is not affected by this parameter. 

See Also:

 

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:

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.

Perform the media recovery in these stages:

  1. Shut down the database and correct the media damage if possible.

  2. Restore the necessary files.

  3. Recover the database.

To prepare for closed database recovery:

  1. If the database is open, shut it down using the ABORT option:

    SHUTDOWN ABORT
    
    
    
  2. If you are recovering from a media error, correct it if possible.

  3. 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:

  1. Determine which datafiles to recover using the techniques described in "Determining Which Files to Recover".

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

  3. Use an operating system 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. 

To recover the restored datafiles:

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

    STARTUP MOUNT
    
    
    
  2. 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;
    
    
    
  3. 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:

    SPOOL onlineall.sql
    SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM v$datafile;
    SPOOL OFF
    @onlineall
    
    
    
  4. Issue the statement 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 "Performing Media Recovery in Parallel" 

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

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

  7. Open the database:

    ALTER DATABASE OPEN;
    

    See Also:

    "Performing Complete Media Recovery" for more information about applying redo log files 

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--but not the tablespaces that contain them--if DBWR fails to be able to write to them. Queries that cannot read damaged files receive errors, but Oracle does not take the files offline for this reason alone.

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.

Perform media recovery in these stages:

  1. Prepare the database for recovery by making sure it is open and taking the affected tablespaces offline.

  2. Restore the necessary files.

  3. Recover the database.

    See Also:

    "Performing Closed Database Recovery" for procedures for proceeding with complete media recovery of SYSTEM tablespaces datafiles 

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

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

    STARTUP
    
    
    
  2. 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;
    
    
    
  3. 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:

  1. 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;
    
    
    
  2. 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:

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


  2. 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 the Oracle8i Administrator's Guide.

To recover offline tablespaces in an open database:

  1. Connect to the database with administrator privileges. For example, connect as SYS to database PROD1:

    % sqlplus sys/oracle@prod1
    
    
    
  2. 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"


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

  4. 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:

    Oracle8i Administrator's Guide for more information about creating datafiles. 

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:

  1. Prepare for recovery by backing up the database and correct any media failures.

  2. Restore backup control files (if necessary) and backup datafiles.

  3. Perform media recovery on the restored backup using the RECOVER DATABASE statement, terminating it with a CANCEL.

To prepare for cancel-based recovery:

  1. If the database is still open and incomplete media recovery is necessary, abort the instance:

    SHUTDOWN ABORT
    
    
    
  2. 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.

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

To restore the files necessary for cancel-based recovery:

  1. If the current control files do not match the physical structure of the database at the intended time of recovery, for example, 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, that is, 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. 


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

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

  4. 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:

  1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

    % sqlplus sys/change_on_install@prod1
    
    
    
  2. Start a new instance and mount the database:

    STARTUP MOUNT
    
    
    
  3. 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.

  4. 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 statement.

    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. 


  5. 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 compute 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. 


  6. Continue applying redo log files until the most recent, undamaged redo log file has been applied to the restored datafiles.

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

  1. Back up the database as a precaution and correct any media failures.

  2. Restore backup control files (if necessary) and backup datafiles.

  3. 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:

  1. 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:

  2. 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:00 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. 


    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. 


  3. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

    % sqlplus sys/change_on_install@prod1
    
    
    
  4. Start a new instance and mount the database:

    STARTUP MOUNT
    
    
    
  5. 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";
    
    
  6. 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.

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

  1. 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
    
    
    
  2. 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.

  3. 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:

  1. Back up the database as a precaution and correct any media failures.

  2. Restore backup control files (if necessary) and backup datafiles.

  3. Perform media recovery on the restored backup using the RECOVER DATABASE statement with the UNTIL CHANGE 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:

  1. 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 through SCN 100 issue:

    RECOVER DATABASE UNTIL CHANGE 100;
    
    
    
  2. 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.

  3. 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 SCN, and returns a message indicating whether recovery is successful.

Opening the Database After Media Recovery

Whenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database. The new version of the reset database is called a new incarnation. All archived redo logs generated after the point of the RESETLOGS on the old incarnation are invalid in the new incarnation.

If you perform complete recovery, then you do not have to open the database with the RESETLOGS option: you simply open the database as normal. All previous backups and archived logs created during the lifetime of this incarnation of the database are still valid.

This section contains the following topics:

What Is a RESETLOGS Operation?

Whenever you open the database with the RESETLOGS option, all datafiles get a new RESETLOGS SCN and timestamp. Archived redo logs also have these two values in their header. Because Oracle will not apply an archived redo log to a datafile unless the RESETLOGS SCN and timestamps match, the RESETLOGS operations prevents you from corrupting your datafiles with old archived logs.

Figure 5-1 shows the case of a database that can only be recovered to SCN 2500 because an archived redo log is missing. At SCN 4000, the database crashes. You restore the SCN 1000 backup and prepare for complete recovery. Unfortunately, one of your archived redo logs is corrupted. The log before the missing log contains SCN 2500, so you recover to this point and open with the RESETLOGS option.

As the diagram illustrates, you generate new changes in the new incarnation of the database, eventually reaching SCN 4000. The changes between SCN 2500 and SCN 4000 for the new incarnation of the database are completely different from the changes between SCN 2500 and SCN 4000 for the old incarnation. Oracle does not allow you to apply logs from an old incarnation to the new incarnation. You cannot restore backups from before SCN 2500 in the old incarnation to the new incarnation.

Figure 5-1 Creating a New Database Incarnation


Text description of performa.gif follows.

Text description of the illustration performa.gif.

Determining Whether to Reset the Online Redo Logs

The RESETLOGS option is required after incomplete media recovery or recovery using a backup control file. Resetting the redo log:

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

Executing the ALTER DATABASE OPEN Statements

To preserve the log sequence number when opening a database after media 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;

If you open with the RESETLOGS option, 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.

Following Up After a RESETLOGS Operation

This section describes actions that you should perform 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 cannot recover changes made after you reset the logs. Until you take a full backup, the only way to recover is 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.

In general, backups made before a RESETLOGS operation are not allowed in the new incarnation. There is, however, an exception to the rule: you can restore a pre-RESETLOGS backup only if Oracle does not need to access archived redo logs from before the RESETLOGS to perform recovery.

See Also:

"Recovering a Pre-RESETLOGS Backup"

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

Recovering a Pre-RESETLOGS Backup

In pre-Oracle8 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.

Restoring Pre-RESETLOGS Backups

It is possible to restore the following types of pre-RESETLOGS backups in a new incarnation:

You are prevented from restoring backups of read-write tablespaces that were not made immediately before the RESETLOGS. This restriction applies even if no changes were made to the datafiles in the read-write tablespace between the backup and the RESETLOGS. Because the checkpoint in the datafile header of a backup will be older than the checkpoint in the control file, Oracle has to search the archived logs to determine whether changes need to be applied--and the pre-RESETLOGS archived logs are not valid in the new incarnation.

Restoring a Pre-RESETLOGS Backup: Scenario

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:

  1. Cleanly shut down the database:

    SHUTDOWN IMMEDIATE
    
    
    
  2. Perform a whole database backup. For example, enter

    % cp /oracle/dbs/* /oracle/backup
    


    Note:

    At this point you must not reopen the database. 


  3. Perform operating system maintenance.

  4. Restore the datafiles and control files from the backup that you just made. For example, enter:

    % cp /oracle/backup/* /oracle/dbs
    
    
  5. Mount the database:

    STARTUP MOUNT
    
    
    
  6. Initiate cancel-based recovery:

    RECOVER DATABASE UNTIL CANCEL
    
    
    
  7. 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, then 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:

  1. Abort the instance (if it still exists):

    SHUTDOWN ABORT
    
    
    
  2. 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. 


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


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index