Automating the Recovery

You can control whether you want to perform a manual ( autorecovery off ) or an automatic recovery ( autorecovery on ). To do this, enter the appropriate SVRMGR command before you enter the Recover command.

• set autorecovery on

The required offline redo log files are automatically processed without requiring any user entry. The names and paths of the offline redo log files are derived from the init.ora parameters log_archive_dest and log_archive_format , which means that that required offline redo log files must first be imported under the appropriate names (see Archiving Parameters).

If the files cannot be imported under log_archive_dest , you can override the source specified in archive_log_dest by entering the command set logsource = . This means the files will now be expected in the directory identified under logsource .

• set autorecovery off

The processing of the individual redo log files must be initiated by the user (default value).

In the process, ORACLE automatically suggests a file derived from log_archive_dest (or logsource ) and log_archive_format . Press RETURN to accept this value.

You can also explicitly enter the name of the redo log files.

Enter

• cancel
to interrupt or cancel the recovery.
• auto
to continue the recovery in automatic mode from this point.
• from
to change the log source.

SAPDBA Recovery: SYSTEM Tablespace Missing

One or more files of the SYSTEM tablespace has been damaged or lost due to a media error.

Backups of the affected files exist. All offline redo log files that have been written since the last backup are available uninterrupted. The control files and all online redo log files are undamaged.

Recovery Procedure

Because the SYSTEM tablespace is affected, the recovery must take place with the database closed. If a backup of the missing files and all redo log entries are available, a complete recovery is possible.

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

shutdown abort

ABORT is required because the loss of individual files from the tablespace means that the changes from SGA can no longer be recorded, and the database cannot be closed properly as a result.

2. Examine the ALERT and trace files to determine the cause of the problem.
3. Use the log files created by the SAP utilities BRBACKUP and BRARCHIVE to find the volume(s) containing the following files:

– Last backup of the SYSTEM tablespace

– Offline redo log files of all instances archived since the last backup

4. Restore the backups of the damaged/lost files and the archived offline redo log files of all instances.

Use the SAP utility BRRESTORE (see Command Options for BRRESTORE).

5. Mount the database. Enter the following SVRMGR commands:

connect internal

startup mount

6. You may have to change the names and paths of the files in the control file. See
Updating the Control File.
7. The database administrator can automate the recovery using the
autorecovery option. See Automating the Recovery. Also read the corresponding ORACLE documentation.
8. Use the following SVRMGR command to start the recovery:

recover database;

9. Depending on the recovery mode (
autorecovery on/off), the required offline redo log files are either processed automatically or you will have to enter their paths and names. The system reads the online redo log files automatically.
10. 1When the message
recovery complete is displayed, use the following SVRMGR command to open the database again:

alter database open;

Please note: in most cases, SAPDBA can be used to correct media errors affecting the data files of a user tablespace. Therefore, we recommend using the SAPDBA recovery functions (see SAPDBA Database Recovery).

SAPDBA Recovery: User Tablespace Missing

One or more data files are missing from a tablespace.

A user tablespace does not contain data from the Data Dictionary, active rollback segments, or temporary segments.

ORACLE issues error messages when a user attempts to access the involved tablespace. Error information is also written to the database ALERT and trace files.

If only one user tablespace is lost, you can perform a tablespace recovery without shutting down the database. Please note, however, that user tablespaces are used intensively in the SAP System, and this procedure is therefore only recommended for experienced database administrators. The loss of a user tablespace often has similar consequences for the SAP system as the loss of the SYSTEM tablespace, because the effects of the loss of this one tablespace are very complex.

A full recovery of the database is possible if you have a backup copy of the corresponding tablespaces and of all redo log files written since the backup.

Recovery Procedure

If you use an SAP database, you should shut down the SAP System before starting the recovery procedure. Tables are used so intensively in the SAP System that it is generally impossible to set the affected tablespace to OFFLINE without terminating the activities of many users.

The information below describes the recovery procedure when the database is closed. For information on recovery options with an open database, please refer to the ORACLE documentation.

Proceed as follows during recovery:

1. Shut down the database system, if it is active, with the following SVRMGR command:

shutdown abort

You have to shut down the database with ABORT because the missing files cannot be closed.

2. Inspect the ALERT and trace files to determine the cause of the problem.

The problem is often that an entire disk has crashed, and you will have to recover more than one tablespace.

3. Use the log files from the SAP utilities BRBACKUP and BRARCHIVE to find the volume(s) that contain the following files:

– Last backup of the lost tablespace(s)

– Offline redo log files of all instances archived since the last backup

It is important to identify the location of the lost files. This information appears at the start of the detail log from the backup you are using.

4. Restore only the damaged or lost files. You can minimize the time required for recovery by only restoring the missing or damaged files.

You also have to restore the archived redo logs of all instances that are required for the recovery.

To do this, use the SAP utility BRRESTORE (see Command Options for BRRESTORE).

5. Mount the database. Enter the following SVRMGR commands:

connect internal

startup mount

6. If you could not restore backed up files into their original directories or had to change file name, you have to update the control file.

Use the following command to change a path:

alter database rename file '' to '';

See Updating the Control File.

7. If the data files of the database were set to status OFFLINE when the error occurred, change the status of the files in the control file to ONLINE.

To find the relevant files, search for "offline" in the ALERT file.

To change the status of a data file in the control file, use the following SVRMGR command:

alter database datafile '' online;

See Updating the Control File.

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

recover database;

When prompted to do so, enter the paths of the offline redo log files required for updating the backup.

The system processes online redo logs automatically.

The alternatives: recover tablespace and recover datafile were not discussed here, since SAP recommends shutting down the database when an error occurs. The recover database command only performs the actions necessary to recover the database, and therefore does not take much longer than the recover tablespace and recover datafile commands.

For information on recover tablespace and recover datafile , please refer to the ORACLE documentation.

9. When the message
recovery complete is displayed, use the following SVRMGR command to start up the database system:

alter database open;

For more detailed information, please refer to the ORACLE documentation.

Please note: in most cases, SAPDBA can be used to correct media errors affecting the data files of a user tablespace. Therefore, we recommend using the SAPDBA recovery functions (see SAPDBA Database Recovery).

SAPDBA Recovery: One Inactive Online Redo Log Missing

If only one member of an inactive online redo log group has been lost, you can use the recovery procedure described in the previous section. Experienced users can correct this error without shutting down the database. For more information, please refer to the ORACLE documentation.

The recovery procedure is different when the following situation occurs:

The database pauses because a redo log switch to an online redo log file was unsuccessful. None of the members in this inactive online redo log group can be read or written to.

If the problem is temporary (e.g. incorrect access rights), you only need to correct it, and you can then use the group again. If the files have been destroyed, however, the group can not be used again.

No data is lost, providing the missing redo log file was fully archived and the archived copy can be read.

Recovery Procedure

Proceed as follows during recovery:

1. Use the following SVRMGR command to shut down the database:

shutdown abort

ABORT is needed because the database system cannot carry out a proper shutdown, due to the damaged group.

2. Find out which file is missing, and check the ALERT and trace files for the reason why the redo log files were lost.
3. Mount the database. Enter the following SVRMGR commands:

connect internal

startup mount

4. If you were running the database in ARCHIVELOG mode and archiving of the damaged online redo log group was not complete, you will have to temporarily switch to NOARCHIVELOG mode before deleting the defective group, because the system will not otherwise allow you to delete the files:

alter database noarchivelog;

5. You can also delete the damaged online redo log files as a group with

alter database drop group ;

or as individual files with

alter database drop logfile '' [,''];

6. To create the new online redo log files (to replace the damaged ones, which you just deleted), enter:

alter database add logfile '';

7. If the database was set to NOARCHIVELOG mode during these actions, change it back to ARCHIVELOG mode now:

alter database archivelog;

8. If you were running the database in ARCHIVELOG mode, and the archiving of the online redo log group was not complete at the moment the problem occurred, it is essential that you now perform an offline backup of the entire database. If you do not, the offline redo log chain will have a gap, and only a partial recovery would be possible if another media error occurred.

SAPDBA Recovery: Current Online Redo Log Missing

A member of the group - or the entire group - of current online redo log files (the redo log files in which the database changes are currently being recorded) is lost.

Use the entries in the ALERT file and in the LGWR trace file to analyze the error situation. Also use the SAPDBA Check function (Check (and repair) database ® Check database). It is the database administrator’s responsibility to check all sources of information for possible LGWR errors. Even if an error allows the instance to continue running (e.g. at least one member of the current group can be written to, errors only in the other members), the error should be corrected as soon as possible.

If you have not been mirroring the online redo log files, as supported by ORACLE (and have also not been using hardware based mirroring), the risk of losing online redo log entries is significantly higher. In order to be able to perform a full recovery, only the entries from the current online redo log file are necessary. Use mirroring to guard against complete loss of the online redo log files. If the mirrored online redo log files are available, you can use these later to perform a complete recovery of the database. Otherwise you will only be able to recover the database to the point of the missing redo log entries (i.e. a partial recovery with loss of data).

For this reason, we strongly recommend once again that you make use of the ORACLE options for mirroring the online redo log files.

Recovery Procedure: Mirrored Current Redo Log Files

For this recovery procedure, we assume that the following requirements are met:

• You used the ORACLE option for mirroring the online redo log files (or have hardware-based mirroring), and therefore have at least one copy of each online redo log file (SAP default: one copy of the online redo log files).

• Apart from the one member of the current online redo log group, no other files have been damaged.

If further files have been damaged, restore the missing files and the missing active redo log and follow the recovery procedure for the category of the missing file (control file or files of the system, user, or index tablespaces).

Proceed with the recovery as follows:

1. If the database system is still active, use the SVRMGR command
shutdown abort to shut it down.
2. Determine the reason why the current online redo log files were lost. Examine the ALERT and trace files.
3. Replace the missing online redo log files with one of their mirrored copies.
4. Start the database with the SVRMGR command
startup. The system automatically performs an instance recovery.

If all members of the current redo log group have been lost, you can only perform a partial recovery. For more information, please refer to the documentation on your ORACLE database system or the section Partial Recovery.

SAPDBA Recovery: All Control Files Missing

You have lost all copies of the control file.

The normal database activities continue until the next update of the control file. When this happens (during the next checkpoint or redo log file switch, at the latest), the database system crashes.

A complete recovery of the database is possible provided one of the following conditions is fulfilled:

• A current backup copy of the control file, that is, a copy with the current structure of the database, exists.

• A current log of the files in the database exists, enabling you to create the control file again.

If all the control files (even the backups) are lost, you must first reconstruct the control file before you can start the recovery process. This procedure is much more complicated and not always guaranteed to be successful. Please therefore strictly observe the following note, which cannot be repeated often enough:

Follow SAP’s recommendations and back up your database after every structure change! If you do this, you always have a backup of a control file that reflects the current structure of the database.

Recovery Using the Backed-Up Control File

In the procedure described in the following, we assume that you are able to restore the control file from your last database backup.

To update the database, the appropriate redo log files must exist.

It is essential that the saved control file always reflects the current structure of the database. The paths for the data and log files and the status of the log sequence numbers are not important, but the control file must have the exact information about the number of files and - indirectly - the number of tablespaces in the database.

Proceed as follows for recovery:

1. If the database system is still operating, shut down all instances with the following SVRMGR command:

shutdown abort

ABORT is generally necessary because the control files are no longer available to include a checkpoint during the shutdown.

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

Check whether other damage has occurred to the database: Find out whether all data files and redo log files are readable.

Back up the online redo log files of all instances (if they exist in readable form) so that you can repeat the recovery process if an error occurs.

3. Place the backup copies of the control file in the directories or on the raw devices specified in the
control_files parameter in the init.ora profile.

If further files were damaged, restore the backup copies of these files. You do not need to restore undamaged files from the backup. If you have to restore data files, you will also have to restore all the offline redo log files of all instances that have been archived since the last backup (for SAP databases, offline redo log files are usually archived by the BRARCHIVE program) in the local archiving directory (default value: /saparch ). For detailed information on recovery after the loss of redo log or data files, please refer to the relevant parts of this documentation and to your ORACLE documentation.

4. Enter the following SVRMGR commands to mount the local instance:

connect internal

startup mount

5. If you could not load backed up files into their original directories or had to change file name, you must update the control file.

Change path or group names with the following command:

alter database rename file '' to '';

See Updating the Control File.

6. If the data files of the database were set to status OFFLINE during the shutdown, change the status of the files in the control file to ONLINE.

To find OFFLINE files, search for "offline" in the ALERT file.

To change the status of a data file in the control file, use the following command:

alter database datafile '' online;

See Updating the Control File.

7. Start recovery with the following SVRMGR command:

recover database until cancel using backup controlfile;

8. If you are prompted to do so, enter the full path name for the redo log files required for recovery, including the active online redo log file.
9. When all redo log files are processed, end the recovery process with the command
cancel.
10. After the message
recovery canceled, you can reopen the database by using the following SVRMGR command:

alter database open resetlogs;

or

alter database open noresetlogs;

The RESETLOGS option initializes the existing online redo log files. Therefore, only use this option after a partial recovery. If a full recovery has been performed, you should not use this option.

The NORESETLOGS option causes the online redo log files to be used in their current form. A complete recovery is required to use this option.

The database system resumes operations with the log sequence number following the number of the last current online redo log file.

11. Carry out a complete backup of the database.

The backup is necessary to back up the control file and to guarantee a full recovery of the database if further database problems occur. See also: Actions after a Partial Recovery.

DB Recovery Using a New Control File

If you do not have a valid copy of the control file, a full recovery is still possible by reconstructing the control file. To do this, you need a current log of all the database files, for example, the BRBACKUP log. Caution: If a structural change to the database has been carried out after this backup, it may be impossible to perform a recovery (e.g. if no backup of the new or changed data files is available).

Proceed as follows during recovery:

1. If the database is still active, shut down all instances with the following SVRMGR command:

shutdown abort

ABORT is generally necessary because the control files are no longer available to include a checkpoint during the shutdown.

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

Make sure no further damage has occurred in the database, and find out whether all data files and online redo log files exist in readable form.

Back up the online redo log files of all instances (if they exist in readable form) so that you can repeat the recovery process if an error occurs.

3. If other files were damaged, restore the backup copies of these files. You do not need to restore undamaged files from the backup. If you have to recover data files, also restore all the offline redo log files of all instances that have been archived since the backup of these data files in the archiving directory.
4. Enter the following SVRMGR commands to demount the database:

connect internal

startup nomount

5. Use the following command to create the control file (for detailed syntax information, please refer to your ORACLE documentation):

create controlfile
database
logfile ''
noresetlogs|resetlogs
maxlogfiles 10
maxlogmembers
datafile ''
maxdatafiles 254
archivelog;

Path names: The path names of the online redo log files and data files can be found in the last detail log from BRBACKUP.

Noresetlogs/Resetlogs: Only select RESETLOGS when an online redo log group was lost in addition to the control file. You should otherwise always use NORESETLOGS.

6. Mount the database.

alter database mount;

7. Start the recovery with the following command (a recovery is required whenever the control file was generated with the RESETLOGS object or when a data file was restored. Recovery is recommended for security reasons in other cases, as well.):

recover database [until cancel] [using backup controlfile];

You must select the option using backup controlfile when you used the RESETLOGS option to create the control file. If you select until cancel , you can interactively decide how many files of the existing redo log files you want to read during the recovery. You should enter all the redo log files of all instances, including the current ones.

8. Use the following SVRMGR command to start up the database:

alter database open [noresetlogs/resetlogs];

– Use alter database open if you created the control file with NORESETLOGS and have performed no recovery or a full recovery (without until cancel ).

– Use alter database open noresetlogs if you created the control file with NORESETLOGS and performed a full recovery despite the use of the until cancel option.

– Use alter database open resetlogs if you created the control file with RESETLOGS or when you performed a partial recovery.

9. After the recovery, be sure to perform a complete backup to save the newly created control file and to ensure that a recovery of the database in the event of failure is possible.

Recovery: One Control File Missing

A copy of the control file cannot be read or written to.

Database activities continue normally until the next update of the control file - that is, until the next checkpoint or redo log file switch - and the database then crashes.

In this case you do not need to carry out an explicit recovery of the database. The system can carry out a recovery automatically, providing a copy of the control file exists.

Recovery Procedure

There are two main options for recovery after one of the control files is lost:

• Replace the missing control file

Find out why the control file was lost. For example, you cannot replace the file when the disk is not available.

Check the ALERT and trace files of the database system to analyze the error. The error messages will indicate which control file is missing, and may also indicate how it was lost.

Replace the missing control file control file as follows:

• Shut down the database and exit SVRMGR.
• Copy an existing control file to the desired storage location at operating system level (see parameter
control_files in the init.ora profile).
• Start SVRMGR and start up the database.

• Modify the database system

Check the ALERT and trace files to find out which control file was lost.

• Shut down the database and exit SVRMGR.
• Delete the missing file from parameter
control_files in profile init.ora (default: /dbs/init.ora).
• Start SVRMGR and start up the database. No other actions are required for recovery.

This solution is only acceptable if you have at least two copies of the control file. This guarantees that at least one mirror copy of the file still exists, even when one control file has been deleted. The database should always have at least two control files (original and mirror). In the SAP System, the control files are often mirrored in three or more directories.