Both Classic Replicat and Integrated Replicat can abend on this error. This blog will discuss the causes of this abend and how you can resolve it.
This error message, Error ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE, will appear in your replicat report file. To some DBAs, a Domain Index is not something they see or use on a regular basis. This type of error is usually due to one of two scenarios: 1) the index is invalid and needs to be rebuilt, or 2) the index was created just prior to the problematic DML statement and the database has not completely recognized the index for DML operations yet.
This is fairly easy to quickly identify by checking the DBA_INDEXES view. You can't just look at the STATUS of the index. You must query the DBA_INDEXES view and look at the domain index columns (DOMIDX_STATUS, DOMIDX_OPSTATUS).
select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes where
index_type like '%DOMAIN%' and (domidx_status <> 'VALID' or domidx_opstatus <> 'VALID') and TABLE_NAME like '%<table_name>%' ;
If either domidx_status or domidx_opstatus is NOT VALID. The index must be rebuilt. Rebuild the index with the following SQL:
ALTER INDEX <index_name> REBUILD;
In some cases, the DDL for the index may need to be pulled from the source system to be executed on the target to build the index. Any partitioned domain index can end up in this state intermittently due to normal failures. When a domain index operation fails on one partition of a partitioned domain index, it is expected that the user will execute a DDL to rebuild the failed partition.
Once the index is rebuilt, the replicat will restart without issue.
In this case, usually the DDL for the creation of the new Domain Index is executed just prior to the DML statement which is loading data into the table for which the new Domain Index was created. Both DDL and DML replication is enabled in this case. This is fairly easy to see in the Replicat Report file.
2015-08-29 08:11:14 INFO OGG-00487 DDL operation included [include all],
optype [CREATE], objtype [INDEX], objowner [TEST23], objname [TEST23_TAB04].
2015-08-29 08:11:14 INFO OGG-01407 Setting current schema for DDL
operation to [TEST23].
2015-08-29 08:11:14 INFO OGG-00484 Executing DDL operation.
In some cases, the DML statement that immediately follows the Index Creation DDL, may not recognize the new index on its initial attempt. When the replicat autorestarts, it is usually enough time for the index to be recognized. If this scenario happens often in your environment in order to avoid replicat abends, we suggest adding the following to your replicat parameter file to force replicat to retry the DML 10 times before abending:
REPERROR 29861 retryop maxretries 10
Hopefully, this post will address most of the scenarios where this ORA-29861 error will occur. If your situation is not matching the scenarios described above, then you may need to contact Oracle Support for further investigation.