ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

[abriceno]

If an you are getting an error such like in the logs:

[Oracle]ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
Failed SQL: /* ~~QC */ UPDATE BUG SET BG_TO_MAIL=NULL WHERE BG_TO_MAIL IS NOT NULL;'

A project repair needs to be performed over it. This is caused by a corrupted Oracle Domain Index: BG_TS_IX. To repair such problem please follow these steps:

1. Log into the project schema using SQL Plus or any other ORACLE client tool.

2. Run this query to locate the domain index:
select index_name,index_type,status from all_indexes where owner='<dbname>' and index_type='DOMAIN' (replace the <dname> with the affected project schema)

3. Drop the domain index by executing the following query: drop index <index_name> (replace <index_name> with the result of the previous query)

4. On the ALM Site Administration, please run a Repair on to the affected project.