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.

  Caution

  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.

  End of the caution.

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.

Prerequisites

• If you use an SAP database, you 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.

Procedure

1. If it is running, shut down the database system with this 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 need to recover more than one tablespace.

3. Use the log files from the SAP tools BRBACKUP and BRARCHIVE to find the volumes 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 BRRESTORE.

5. Mount the database with these SQLPLUS commands:

   connect / as sysdba

   startup mount

6. If you were not able to restore backed-up files to their original directories or if you had to change file names, 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 or check the v$datafile view:

   select * from v$datafile

   Change the status of a data file in the control file with this SQLPLUS command:

   alter database datafile '' online;

   See Updating the Control File.

8. Start the recovery with this SQLPLUS command:

   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, see the Oracle documentation.

9. When the message recovery complete is displayed, start up the database system with this SQLPLUS command:

   alter database open;

For more information, see the Oracle documentation.

Note:

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