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. Use the following SQLPLUS command to shut down the database:
If this fails, use:
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
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):
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.