Recovery with Archiving  «Prev  Next»

Lesson 4 The complete recovery operation
Objective List the steps in a complete recovery using log files.

Complete Recovery Operation in Oracle

Three basic Complete Recovery Commands

Recover syntax

There are three basic complete recovery commands. You can decide which RECOVER command to use based on the set of files you need to recover.
The optional AUTOMATIC command indicates that the archived and online redo logs are automatically applied.
The following table lists the commands and their descriptions:

COMMAND FILES TO RECOVER RECOVERY METHOD
RECOVER [AUTOMATIC] DATABASE Online datafiles that require redo logs to be applied Closed database recovery
RECOVER [AUTOMATIC] TABLESPACE <NUMBER> | <NAME> All datafiles in the tablespace Open database recovery
RECOVER [AUTOMATIC] DATAFILE <NUMBER> | <NAME> A specific datafile Both closed and open database recovery

If you use Enterprise Manager media recovery options, you may add
ALTER DATABASE
in front of the RECOVER command.

Online and archived redo log files are applied to the datafiles during both complete and incomplete database recovery. These log files are applied during what is referred to as the roll forward phase of recovery. When rolling forward, Oracle uses as many redo log files as needed to recover the database up to a point-in-time just prior to the media failure. To make Oracle automatically apply redo log files, you have three options:
  1. Enter
    AUTO
    
    when you are prompted for an archived log file,
  2. Use the
    RECOVER AUTOMATIC
    
    command,
  3. If you are using Enterprise Manager, issue the command SET AUTORECOVERY ON before starting recovery.
View the code below to see an example of recovery with redo log files.
SVRMGR>RECOVER DATAFILE USER_DATA;
ORA-00279: change 209920...01/20/99 15:09:23 needed for thread 1
ORA-00289: suggestion: \orant\database\archive\arch_12.rdo
ORA-00280: change 209920 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
... ...

If all the required archived log files are available in the LOG_ARCHIVE_DEST directory, Oracle automatically suggests and applies the log files to complete media recovery. However, sometimes the location specified by LOG_ARCHIVE_DEST is not available because of media failure, or it could be that archived logs have been moved due to limited disk space. In the later case, you can mount the archived log files to an alternate location and then specify the alternate location to Oracle before or during the recovery.

Specifying alternate locations

You can specify an alternate location by using one of the following commands:
  1. Use the ALTER SYSTEM ARCHIVE LOG command like this:
    SVRMGR> alter system archive log start to <new location>;
  2. Use the RECOVER FROM <NEW LOCATION> command, like this: SVRMGR>RECOVER FROM '<NEW LOCATION>' DATABASE;
  3. Specify the location and name you are prompted by Oracle at the following line:
    Specify log: {<REF>=suggested | filename | AUTO | CANCEL}-

Locating files needed for Recovery

You can use data dictionary views to locate the required files for a complete media recovery. Below is a list of these data dictionary views:

DATA DICTIONARY VIEWS INFORMATION PROVIDED
V$RECOVERY_LOG Lists all archived logs required for recovery
V$ARCHIVED_LOG and V$LOG_HISTORY List all archived logs for a database
V$RECOVER_FILE Lists all datafiles needing recovery

The following simulation demonstrates how to locate the datafiles and all the archived logs needed during recovery.
Locate Files Needed For Database Recovery.
The next lesson demonstrates how to perform a closed database recovery.

Complete DBrecovery Basics - Quiz

Click the Quiz link below to test your knowledge of complete database recovery.
Complete DB Recovery Basics - Quiz