Recovering from Index Tablespace Missing 

Use

Generally, Oracle treats an index tablespace just like a user tablespace. Therefore, you can use the recovery procedure for a user tablespace. The procedure below describes an additional recovery option for SAP databases.

Procedure

...

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

shutdown immediate

If this fails, use:

shutdown abort

       2.      Find out which data file is affected by the media error, using the information in the ALERT and trace files.

       3.      Mount the database by entering the following SQLPLUS commands:

connect / as sysdba

startup mount

       4.      Set the data files to OFFLINE with:

alter database datafile '<complete file name>' offline;

       5.      Open the database with:

alter database open;

       6.      Make sure that the index tablespaces do not contain any tables. you can check this using the Oracle tables DBA_SEGMENTS and DBA_TABLES.

       7.      Use the corresponding BRSPACE function to create the DDL statements for the affected indexes:

brspace –f tbreorg –s <tablespace_name> -d only_ind

       8.      Drop the affected tablespace, including contents:

brspace –f tsdrop –t <tablespace_name> -f

       9.      Recreate the affected tablespace:

brspace –f tscreate –t <tablespace_name> -d index

   10.      Use SQLPLUS to recreate the indexes (script ddl.sql in a subdirectory of sapreorg):

SQL> @ddl

The recovery of the index tablespace is complete.

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.