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.

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:

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.

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}
   

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:

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.

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.