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

I ran into this issue today and thought it was worth blogging about to keep track of things.  Basically, this just means that the Oracle index is unusable, broken, loading, whatever.  To resolve this, you might need to rebuild the index or drop the index and create a new one.

Some useful SQL:

select owner, index_name 

  from all_indexes 

where domidx_status != 'VALID' or domidx_opstatus !='VALID';

This will list down the bad Oracle indices for you to fix.

drop index [index_name];

alter index [index_name] rebuild;

create index [index_name] on [expression];

I think these are self-explanatory.