Troubleshooting Service Request Error: ORA-29861: Domain Index Is Marked Loading/Failed/Unusable (Doc ID 306854.1)

A number of different user steps can result in the Service Request for or Contact Center displaying the error shown below, for example:

• Saving a Service Request
• Submitting Charges
• Generating a New Task
• Searching for a Service Request, etc.

Error:   

ERROR: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE in Package CS_ServiceRequest_PVT Procedure Create_ServiceRequest.

This troubleshooting guide provides the steps to resolve this error.

 

Explanation of the Issue

Previous to Oracle Applications Tablespace Model (OATM) by default each one of the Oracle eBusiness Suite product would have two dedicated tablespaces: one holding the data elements and the other for storing the indexes. eg CSD (for Oracle Service base tables) and CSX (for indexes related to the Oracle Service product).

Under OATM all these product related tablespaces have been consolidated in two main tablespaces: one for holding the base tables and the other for holding the related indexes. That reduces the total number of tablespaces which, in turn, simplifies administration.

When implementing OATM the CSX tablespace (containing the indexes for CS tables) is deleted.  As per the OATM the indexes belonging to CS are now created in the APPS_TS_TX_IDX tablespace.
OATM does not provide an automatic migration of content from the old tablespaces to the new tablespaces.  Therefore the CS indexes have not been moved to the new tablespace.  The tablespace that the CS Text index is created in, is controlled by the CTX Text Index Preferences.

It is necessary to:
- recreate the CS text indexes preferences
- recreate CS text indexes
- run the necessary concurrent request to recreate and synchronize indexes.