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