Performing an Incomplete Recovery


An incomplete recovery means that some data cannot be recovered after a database error. If one of the following problem constellations occurs, you cannot restore your database completely:

·        All copies of the control file have been lost and you do not have any of the following files either:

Ў        Current backup copy of the control file

Ў        Log of the files in the database

In such cases, contact your SAP consultant or an Oracle specialist. It might be possible to perform a complete recovery anyway. This depends on the exact situation, however, and cannot be explained in detail here.

·        All members of the current online redo log group have been lost.

·        One or more database files has been damaged and requires recovery, but one of the redo log files (offline redo log files or online redo log group) required for recovery is missing. You do not have a backup copy of the required redo log entries.

When you follow the SAP recommendations, this problem should not occur. The loss of all the members in an online redo log group is highly improbable, since the mirrored copies should be stored on different disks. The offline redo logs should also be regularly backed up twice to tape using BRARCHIVE.

·        A tablespace has been lost and you have no backup copy of the tablespace. You should always have at least three generations of backups for every data file in the database, as well as the corresponding redo log files.

An incomplete recovery causes data to be irretrievably lost, because the database can only be recovered in an older version. You can significantly reduce this risk by using a continual backup procedure for your database. Therefore, you ought to adhere to the configuration and backup procedures recommended by SAP.

If you do not use the current control file for a recovery, but instead an older copy, it is essential that you indicate this by adding the following to your command:

using backup controlfile

The following section only describes the incomplete recovery after the loss of an offline redo log file.

For more information on incomplete recovery, see the Oracle documentation.


Loss of an Offline Redo Log File

A media error has occurred in the data file area of the database, and one of the offline redo log files is no longer readable.  For this reason, the recovery terminates with the last available redo log file in the sequence.


       1.      If the database system is still active, use the following SQLPLUS command to shut it down:

connect / as sysdba

shutdown abort

ABORT is required in most cases, because the loss of individual data files means that changes in the SGA can no longer be copied to disk.

       2.      Use the ALERT and trace files to analyze the error.

       3.      Use BRRESTORE to restore all the available backups of all data files and the offline redo log files of all instances.

       4.      After an incomplete recovery, the structure of the database may no longer be identical to that fixed in the current control file. Therefore, use a copy of the control file that reflects the structure of the database at the end of the recovery if possible.

       5.      Mount the database by entering the following SQLPLUS commands:

connect / as sysdba

startup mount

       6.      If you could not load the backup files into their original directories or had to change file names, updating the control file.

Use the following command to change the path names:

alter database rename file '<file name>' to '<file name>';

       7.      You might be able to automate the recovery.

       8.      Use the following SQLPLUS command to start the recovery:

recover database until cancel;

The use of the option until cancel means that the online redo logs will be reset when opened or not, depending on whether parameter resetlogsor noresetlogs is used.

If you do not use the current control file, enter the command:

recover database until cancel using backup controlfile;

       9.      Depending on the recovery mode, the required offline redo log files are processed automatically (autorecovery on) until the file for the next log sequence number cannot be found, or (with autorecovery off) the recovery is stopped with cancel.

   10.      Once the message recovery complete or recovery canceled is displayed, you can use the following SQLPLUS command to open the database again:

alter database open resetlogs;

RESETLOGS: The existing online redo log files are initialized, and the current log sequence number is reset to 1.

NORESETLOGS: The online redo log files are not initialized.  This option should only be used when you did not use the option using backup controlfile and (unusually) all the online redo log files, including the current ones, were processed during the recovery.

The options resetlogsand noresetlogs are only possible after a recover database until... or after a recovery with the option using backup controlfile. See also Finishing an Incomplete Recovery.

Point-in-Time Recovery

You can also select a point-in-time recovery, which you can perform either manually or automatically.  In contrast to the incomplete recovery with until cancel, this recovery is terminated at a specific time or specified system change number.

Use one of the following SQLPLUS commands:

recover database until time 'dd-mm-yyyy:hh:mm:ss';

recover database until change <scm>;

Depending on the recovery mode (manual or automatic), the required redo log files are processed automatically, or you have to enter their paths and names.  When the specified point in time is reached, the recovery is terminated. See also Finishing an Incomplete Recovery.