| 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 |
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:
- Enter
AUTO
when you are prompted for an archived log file,
- Use the
RECOVER AUTOMATIC
command,
- 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:
-
Use the
ALTER SYSTEM ARCHIVE LOG command like this:
SVRMGR> alter system archive log start to <new location>;
- Use the
RECOVER FROM <NEW LOCATION> command, like this: SVRMGR>RECOVER FROM '<NEW LOCATION>' DATABASE;
- 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