Performing a Full Restore and Point-In-Time Recovery with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Performing a Full Restore and Point-In-Time Recovery with SAPDBA

Use

This section tells you how to use SAPDBA to perform a full restore and point-in-time recovery of your Oracle database. You typically use this after failures:

Caused by user errors, such as logical errors
During maintenance, such as upgrade errors or data transfer errors

Prerequisites

The following data must be available:

The BRBACKUP logs and the BRARCHIVE logs

The data file backups

All offline redo log files between the data backup and the reset point in time

Features

The SAPDBA function Full restore and recovery supports you in the following scenarios:

Scenario 1

There is an error during an upgrade. You want to recover the database to the point in time before the upgrade.

This graphic is explained in the accompanying text

In the first step, SAPDBA restores the last complete backup without control files and online redo log files, and in the second step recovers the database.

Scenario 2

A logical error occurred during normal database operations that was only recognized later. You want to recover the database to the point in time before the error.

This graphic is explained in the accompanying text

In the first step, SAPDBA restores the last complete backup without control files and online redo log files, and in the second step imports the redo log files and recovers the database.

Scenario 3

A logical error occurred during normal database operations that was only recognized later. The structure of the database was changed between the error and the last complete backup. You want to recover the database to the point in time before the error.

This graphic is explained in the accompanying text

In the first step, SAPDBA restores the last complete backup without control files and online redo log files, and in the second step recovers the structural changes (CREATE DATA <filename> AS <filespec>). In the third, the redo log files are imported and the database recovered.

Scenario 4

A logical error occurred during normal database operations that was only recognized later. The structure of the database was changed several times between the error and the last complete backup. You want to recover the database to the point in time before the error.

This graphic is explained in the accompanying text

In the first step, SAPDBA restores the last complete backup without control files and online redo log files, and in the second step restores the last incremental backup before the error. The changes to the structure are included in the incremental backup. In the third step, the structural changes made between the incremental backup and the logical error are recovered (CREATE DATA <filename> AS <filespec>). In the fourth step, the redo log files are imported and the database recovered.

Scenario 5

A logical error occurred during normal database operations that was only recognized later. The database was reorganized between the error occurring and its discovery. You want to recover the database to the point in time before the error.

This graphic is explained in the accompanying text

In the first step, SAPDBA restores the last complete backup without control files and online redo log files, in the second step restores the control files as they were before the reorganization. During the reorganization the control files were backed up in the directory <SAPDATA_HOME>/sapreorg/. In the third step, the redo log files are imported and the database recovered. (Recovery with the option and USING BACKUP CONTROLFILE).

Scenario 6 (not supported by SAPDBA)

A logical error occurred during normal database operations that was only recognized later. The database was reorganized before the error occurred. You want to recover the database to the point in time before the error.

Caution

SAPDBA does not support this scenario. The most you can do is to recover the database to the point in time before the reorganization, therefore losing data. Always make a complete backup of the database after you reorganize it.

This graphic is explained in the accompanying text

Scenario 7 (risky, not supported by SAPDBA)

In the production system, a logical error occurred during normal database operations that was only recognized later. You want to recover the database to a point in time before the error in a test system. You then want to export the affected tables from the test system into the production system.

Caution

This scenario is highly risky and may destroy the database beyond recovery, since the isolated export and import of individual R/3 tables makes the R/3 Data Dictionary inconsistent. You may not be able to start the R/3 System. SAPDBA does not support this scenario.

This graphic is explained in the accompanying text

Activities

Start the function Full restore and recovery from the SAPDBA menu entries:

	Full restore and recovery
	DATABASE STATE	NOMOUNT \| MOUNT \| OPEN
	RESTORE/RECOVER	allowed \| allowed

		Current setting
A	- Select a backup of type	Full backup \| whole backup
b	- Select incremental backup run	(Only for selected full backup)
c	- Recover until	now \| point in time
d	- Show status
e	- Restore and recover
q	- Return

Choose Select a backup of type to display a list of all possible whole and full backups (on- and offline). Choose the appropriate backup for your needs. If you choose a full backup you can choose the accompanying incremental backup with Select incremental backup run.

Choose Recover until to enter the point in time to which you want to recover the database. You can choose between Recover until now and a point in time of your choice (Recover until YYYY-MM-DD HH.MM.SS).

Choose Show status to display:

the backup you want to use.
the point in time to which you want to recover the database and which redo log files are needed for this.
whether changes to the structure have been made between the chosen backup and the recovery point in time (for example, after a reorganization with data files or a tablespace extension).
whether an operation with the chosen settings is allowed.

Note

Always choose Show status if the function Full restore and recovery is restricted or not possible.

Choose Options if you want to use a control file from a backup for the recovery.

Choose Restore and recover to start the function Full restore and recovery.

After the recovery, check the log file written by SAPDBA <SAPDATA_HOME>/sapreorg/<timestamp>.rsn (for Recover until now) or <SAPDATA_HOME>/sapreorg/<timestamp>.rsp (for Recover until point in time). To do this, choose the SAPDBA function Show/Cleanup ® Show log files/profiles. For more information, see SAPDBA Log Files and Their Contents.

Note

After the Full restore and recovery, the command ALTER DATABASE OPEN RESETLOGS is always executed. For security reasons, make a backup before you open the database again. After you open the database, the current LOG SEQUENCE NUMBER = 1 and its operations overwrite the old redo log files. Back up the offline redo log files before the function is executed.