Database Point-In-Time Recovery

Use

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 on how to perform a database point-in-time recovery, see Database Point-In-Time Recovery with BR*Tools.

Prerequisites

·        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:

Ў        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.

Features

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

Activities

...

       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 BRBACKUP summary log file 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.

       3.      Check Status of Database Files phase

BRRECOVER checks the status of database files to determine which will 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 BRARCHIVE summary log file 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 saparch or 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 saparch or 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.

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

   10.      Open Database phase

During this phase BRRECOVER:

...

                            a.      Opens the database with the option RESETLOGS

                            b.      Creates missing temporary files

                            c.      Checks the status of database files and tablespaces

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

Example

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

Logical Error

Logical Error with Preceding Structure Change