Tablespace Point-in-Time Recovery


You can use this function to fully restore and then recover a group of Oracle tablespaces 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 tablespace to the point immediately before the error. In this way, you minimize lost data.

Tablespace PIT recovery is especially useful for Multiple Components in One Database (MCOD). It lets you restore the tablespaces for a single component – for example, if a component upgrade has failed – without affecting the other components in the same database.

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

For more information on how to perform a tablespace point-in-time recovery, see Tablespace Point-In-Time Recovery with BR*Tools.


·        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

·        You must be able to open the database before starting the recovery procedure.


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



       1.      Set Point In Time and Tablespaces 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

You specify the tablespaces that you want to recover or a SAP owner for a multi-component database. BRRECOVER automatically finds tablespaces containing segments belonging to this owner.

       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 Tablespace Status phase

BRRECOVER checks if the:

Ў        Tablespace group to be recovered is self-contained

Ў        Tablespace group to be exported is self-contained

This means that database objects within this group must not have references to objects outside the group.

       4.      Export Tablespace not Being Recovered phase

BRRECOVER sets the tablespaces not being recovered to READ ONLY and exports the tablespaces’ meta data using the Oracle EXP tool.

       5.      Restore Data Files of Tablespaces to Be Recovered phase

BRRECOVER calls BRRESTORE to restore data files of the tablespaces to be recovered, including the SYSTEM and rollback tablespaces, placing them in their original location.

       6.      Apply Incremental Backup phase

Before applying an incremental backup, BRRECOVER sets the data files of exported tablespaces to OFFLINE. Therefore, these tablespace are not recovered.

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

Before applying the offline redo log files, BRRECOVER sets the data files of the exported tablespaces to OFFLINE, if not yet done.

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 and Plug In Exported Tablespaces phase

During this phase BRRECOVER:


                            a.      Opens the database with the option RESETLOGS

                            b.      Creates missing temporary files

                            c.      Temporarily drops exported tablespaces from the database.

                            d.      Imports meta data of exported tablespaces using the Oracle IMP tool.

                            e.      Sets imported tablespaces to READ/WRITE.

                              f.      Checks the status of database files and tablespaces

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