Recovering from Index Tablespace Missing

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.

1. Shut down the database with this SQLPLUS command:

   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 with these SQLPLUS commands:

   connect / as sysdba

   startup mount

4. Set the data files to OFFLINE:

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

5. Open the database:

   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. Recreate the indexes with script ddl.sql from subdirectory of sapreorg with this SQLPLUS command:

    SQL> @ddl

The recovery of the index tablespace is complete.