Database Point-In-Time Recovery

You can use this function to fully restore and then recover your Oracle database to a specified point in time (PIT). You normally use this function when there has been a logical error - that is, a user or software error - and you want to recover the database to the point immediately before the error. In this way, you minimize lost data.

This section discusses how to approach database point-in-time recovery.

For more information about how to perform a database point-in-time recovery, see Database Point-In-Time Recovery with BR*Tools.

You can also now use point-in-time recovery for a standby database.

• If the database is still available and you have enough time, we recommend you to:

  • Perform a full offline or online backup. If the database is running, use SAP tools, otherwise use operating system tools.

  • Back up all offline redo log files using BRARCHIVE. For more information, see -a|-archive.

• You must have the following data available if you are not using flashback database:

  • The BRBACKUP logs and the BRARCHIVE Logs

  • The data file backups and an incremental backup if required

  • All offline redo log files between the data backup and the chosen PIT

Any types of database files - data, online redo, control - might be unavailable on disk.

The following graphic shows how database point-in-time recovery works:

1. Set Point In Time for Recovery phase

   You enter the recovery end-point in BRRECOVER by choosing one of the following:

   • Point in time

   • Redo log sequence number

   • System change number

2. Select Database Backup phase

   BRRECOVER determines the eligible backups using the entries in the summary log file for BRBACKUP, back<DBSID>.log (return code 0 or 1). The associated detail logs show whether the required data files were in the backup. The data files can be compiled from various backups. To minimize the subsequent recovery time, BRRECOVER always suggests the most recent backup.

   BRRECOVER also roughly checks the availability of offline redo log files.

   You can also select an incremental backup to be restored before applying offline redo log files. In this case, BRRECOVER automatically selects the corresponding full backup to restore all data files.

   You can also select flashback database if activated and if the recovery endpoint is covered by the flashback data.

3. Check Status of Database Files phase

   BRRECOVER checks the status of database files to determine which are to be overwritten.

4. Restore Control Files phase

   BRRECOVER calls BRRESTORE to restore control files if needed, that is, if they are unavailable or unsuitable for the selected backups.

5. Restore Data Files phase

   BRRECOVER calls BRRESTORE to restore all the data files to their original location.

6. Apply Incremental Backup phase

   If you selected an incremental backup during the Select Database Backups phase, BRRECOVER calls BRRESTORE to restore and apply the selected incremental backup.

7. Determine Offline Redo Log Files Needed phase

   BRRECOVER determines the offline redo log files required for a recovery. The summary log file for BRARCHIVE, arch<DBSID>.log, lists the backups of the offline redo log files. BRRECOVER takes existing online redo log files and offline redo log files in oraarch into consideration.

8. Restore Offline Redo Log Files phase

   BRRECOVER calls BRRESTORE to restore the offline redo log files that have been found back to the oraarch directory.

9. Apply Offline Redo Log Files phase

   BRRECOVER calls SQLPLUS to apply offline redo log files to the database.

   Offline redo log files are applied to the database in groups of at most 100 files. If you have more than 100 files to apply, the restore and apply phases are repeated as necessary.

   Note

   The restore and apply phases can be executed in parallel to minimize total recovery time.

10. Restore Offline Redo Log Files and Flashback phase

    When database flashback is active, BRRECOVER checks whether the given recovery time stamp or system change number (SCN) is available in the flashback data. If so, you can use flashback to perform the database point-int-time recovery.

    BRRECOVER calls BRRESTORE to restore the offline redo log files needed for the flashback to the oraarch directory (if required), then performs flashback database.

    Caution

    Before you use database flashback in your production environment, make sure you have tested it thoroughly.

    Database flashback does not replace regular backups.

    For more information, see Managing Flashback Database with BR*Tools.

11. Open Database phase

    During this phase BRRECOVER:

    1. Opens the database with the option RESETLOGS

    2. Creates missing temporary files

    3. Checks the status of database files and tablespaces

    4. Deletes unnecessary files that are no longer used by the database

Here are two typical scenarios in which you can use database point-in-time recovery: