Recovering from User Tablespace Missing


You use this recovery procedure if:

·        One or more data files are missing from a tablespace

·        A user tablespace does not contain data from the Oracle 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 manual tablespace recovery without shutting down the database. However, note 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 complete recovery of the database is possible if you have a backup copy of the corresponding tablespace files and of all redo log files written since the backup.


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

·        This procedure describes the recovery procedure when the database is closed. For more information on recovery options with an open database, see the Oracle documentation.



       1.      If it is running, shut down the database system with the following SQLPLUS 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 files

Ў        Offline redo log files of all instances backed up 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 of the BRBACKUP backup that 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 backed up redo logs of all instances that are required for the recovery.

To do this, use the BRRESTORE.

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

connect / as sysdba

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 '<file name>' to '<file name>';

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 or check the v$datafile view:

select * from v$datafile

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

alter database datafile '<file name>' online;

See Updating the Control File.

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

recover database;

When prompted to do so, enter the paths of the offline redo log files that you need to apply.

The system processes online redo logs automatically.

We do not discuss here the alternatives – recover tablespace and recover datafile – since SAP recommends shutting down the database when an error occurs. The recover database command only performs the actions necessary to recover the damaged files. Therefore, it 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 SQLPLUS command to start up the database system:

alter database open;

For more information, see the Oracle documentation.

In most cases, BRRECOVER can be used to correct media errors affecting the data files of a user tablespace.  For more information, see Complete Database Recovery with BR*Tools.