Recovery: Index Tablespace Missing 

Generally, ORACLE treats an index tablespace just like a user tablespace. You can therefore apply the recovery procedure for a user tablespace as described above. An additional recovery option also exists for SAP databases, however; it is described below.

Recovery Procedure

  1. Use the following SVRMGR command to shut down the database:
    2.

    shutdown abort
  2. Find out which data file is affected by the media error, using the information in the ALERT and trace files and/or the Check function from the SAPDBA recovery menu.
  3. Mount the database. Enter the following SVRMGR commands:
    4.

    connect internal

    startup mount
  4. Set the data files to OFFLINE with:
    5.

    alter database datafile '<complete file name>' offline;
  5. Open the database with:
    6.

    alter database open;
  6. First 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 SAPDBA function to export the affected index tablespace (Export of table and index definitions with options Compress extents: no, Use SAP DDIC for NEXT: no, Reduce object size: no, Change storage manually: no).
  8. Determine the default storage parameters that apply to this tablespace by examining ORACLE table DBA_TABLESPACES. Write down these values.
  9. Enter the following SVRMGR commands to delete the index tablespace and create it again:
    10.

    alter tablespace <tablespace name> offline;
    drop tablespace <tablespace name> including contents;

    Delete the files belonging to the tablespace at operating system level, if any still exist.

    create tablespace <tablespace name>
    datafile '<complete file name>' size <n>M
    default storage (<storage parameters>);

    For more information on these ORACLE commands, please refer to the ORACLE documentation.

  10. Use the SAPDBA import functionality to import the indexes (script ind<TSP>.sql ). The recovery of the index tablespace is complete.