Show TOC

Performing an Incomplete RecoveryLocate this document in the navigation structure

Use

An incomplete recovery means that some data cannot be recovered after a database error. If one of the following problem constellations occurs, you cannot recover 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. However, this depends on the exact situation 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 unlikely, 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. Always follow the configuration and backup procedures that we recommend.

Caution

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

Procedure

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, shut it down with these SQLPLUS commands:

    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. Restore all the available backups of all data files and the offline redo log files of all instances, using BRRESTORE.

  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 with these SQLPLUS commands:

    connect / as sysdba

    startup mount

  6. If you were not able to load backed-up files to their original directories or had to change file name, update the control file, by changing path or group names:

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

    See Updating the Control File.

  7. You might be able to automate the recovery.

  8. Start the recovery with this SQLPLUS command:

    recover database until cancel;

    The option until cancel means that the online redo logs are reset when opened or not, depending on whether parameter resetlogs or noresetlogs is used.

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

    recover database until cancel using backup controlfile;

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

  9. Once the message recovery complete or recovery canceled is displayed, open the database again with this SQLPLUS command:

    alter database open resetlogs;

    Note

    Decide which setting of resetlogs you want to use:

    • resetlogs: initializes the existing online redo log files and resets the current log sequence number to 1

    • noresetlogs: does not initialize the online redo log files. Only use this option 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 resetlogs and 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.