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

As an Oracle Database Administrator, performing a complete recovery of an Oracle database is a critical task that requires precision and a clear understanding of the process. The following steps and associated Recovery Manager (RMAN) commands are to be executed to achieve a complete recovery using log files.
  1. Step 1: Determine the Recovery Point: Before initiating the recovery process, you must identify the point to which the database needs to be recovered. This could be the point of failure or a specific point in time.
  2. Step 2: Start the Database in Mount State: The database must be started in NOMOUNT state and then mounted to proceed with the recovery. Do not open the database at this stage.
    STARTUP NOMOUNT;
    ALTER DATABASE MOUNT;
    
  3. Step 3: Assess the Database: Evaluate the database to determine which files require recovery. Use RMAN to check for datafiles that need to be restored and any archive logs that will be applied during the recovery.
    RMAN> LIST FAILURE;
    
  4. Step 4: Restore the Datafiles: Using RMAN, restore the necessary datafiles from the backups. If the whole database is to be restored, you can issue a command to restore all datafiles.
    RMAN> RESTORE DATABASE;
    
  5. Step 5: Apply Archived Redo Logs: Recover the database using the archived redo logs. This step applies all necessary redo logs to bring the database to the desired state.
    RMAN> RECOVER DATABASE;
    
  6. Step 6: Open the Database with RESETLOGS: Once recovery is complete, open the database with the RESETLOGS option. This action resets the log sequence and creates a new incarnation of the database.
    ALTER DATABASE OPEN RESETLOGS;
    
  7. Step 7: Perform a Consistency Check: After the database is opened, it's advisable to perform a consistency check to ensure that the database is fully synchronized and there are no logical errors.
    ANALYZE DATABASE VALIDATE STRUCTURE CASCADE;
    
  8. Step 8: Backup the Database: After a successful recovery, take a full backup of the database to ensure that you have a current and consistent backup.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

These are the basic steps and commands for performing a complete recovery of an Oracle database. However, depending on the specific scenario, additional steps and options may be necessary. For example, if you are recovering to a specific point in time, you would use the `SET UNTIL` clause with the `RECOVER DATABASE` command. It is also crucial to keep the RMAN repository up-to-date and to have a good understanding of the backup and recovery catalog, if used. Regularly testing your recovery procedures is best practice to ensure you can recover your database quickly and effectively when necessary.

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.

SQL>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:
    SQL> alter system archive log start to <new location>;
    
  2. Use the
    RECOVER FROM <NEW LOCATION>
    
    command, like this:
    SQL>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 paragraph demonstrates how to locate the datafiles and all the archived logs needed during recovery.

How to locate Files needed for Recovery

  1. You are told that a datafile is corrupt and needs recovery. You have already connected to database ORCL as sysdba. To find the file needing recovery, you query the data dictionary view V$RECOVER_FILE. Type
    SELECT * FROM V$RECOVER_FILE;
    

    at the prompt and press
    Enter.x
  2. At this time, you may want to find the name for datafile #5. To do this, you query the DBA_DATA_FILES view. Type
    SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID = 5;
    

    at the prompt and press Enter.
  3. Now you need to find out what log files are needed for the recovery. Type the following SQL:
    SELECT * FROM V$RECOVERY_LOG;
    
    at the prompt and press Enter.
  4. Now you have the information about the files you need for recovery. This is the end of the Simulation. Click the Exit button.

Complete DBrecovery Basics - Quiz

Click the Quiz link below to test your knowledge of complete database recovery.
Complete DB Recovery Basics - Quiz
The next lesson demonstrates how to perform a closed database recovery.