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

[ApurvDanke]

Hi,

We recently migrated our Oracle geodatabase from AIX to Oracle Linux platform. The geodatabase version is 10.4.1. We're using SDO_Geometry as our spatial type. While doing our database checks, we encountered an error while doing a reconcile/post.

As per our versioning framework, we have SDE.Default as the top version in hierarchy. Below that, we have 3 user schema versions - Schema1.ADMIN, Schema2.Admin, Schema3.Admin , all of them are children of SDE.Default.

Now under each schema admin version we have further children which are the user versions. So for example, we have a version Schema1.User1 which is child of Schema1.ADMIN, which in turn is child of SDE.Default.

Now in the migrated database, we did some edits in Schema1.User1 version, which were successfully reconciled and posted to Schema1.ADMIN. The error has come when we do reconcile and post from Schema1.ADMIN to SDE.Default. Here reconcile still works, it is in the post that we get this error.

The ESRI article in the link given below is applicable for ArcSDE 9.2-9.3.1 and for ST_Geometry. In our case we do not have ArcSDE, we have ESRI Geodatabase 10.4.1 and enabled SDO_Geometry.

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

What could be the cause of this? Is it related to tablespace or something else?

Regards,

Apurv

[George_Thompson]

It may be related to the domain index on the "GDB_*" table that is invalid. Run the following SQL to verify the indexes

col index_name for A20
col table_name for A15
col index_type for A10
col domidx_status for A13
col domidx_opstatus for A15
select index_name, table_name, index_type, domidx_status, domidx_opstatus from user_indexes where index_type='DOMAIN' order by 1;

- If the index is INVAILD, drop and rebuild it.

[ApurvDanke]

Thanks George.

I ran the SQL logging in as SDE user and this is the result in our staging database.

Looks like all are valid. However, now we have a strange observation. Now when we do a reconcile/post to SDE.DEFAULT the error is not coming. But when we run "Synchronize Changes" python script to replicate the data updates from our staging database to consumption database, we are getting the error as given below.

Synchronisation Started at Time 2018-10-08 10:50:58.501000

Source SDE = [Our staging database SDE connection]

Replication ID = [Our replica name]

Target SDE = [Our consumption database SDE connection]

Exception caught at :2018-10-08 10:51:14.283000

Code = 1 (FAILURE), Underlying DBMS error [ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE]

Error on Line 86

Replica Synchronisation Failed for Site Area

Code = Underlying DBMS error [ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE]

Now I ran the same SQL query in our consumption database, and I see there is an index which is in FAILED state.

How to drop and rebuild this index? Also our staging database is versioned, while consumption is non-versioned.

Thanks for your help.

Regards,

Apurv

[George_Thompson]

You could try something like this:

SQL> select dbms_metadata.get_ddl('INDEX','A1_IX1','SDE')||chr(10)||'/' from dual;
example:  CREATE INDEX "SDE"."A1_IX1" ON "SDE"."GDB_ITEMS" ("SHAPE")  INDEXTYPE IS "

SQL> Drop Index

SQL> Recreate Index using the ddl from above

[LeonardoEspinosa]

In my case, the index error was presented as "failed" in the table GDB_ITEMS, 
and I solve it, only by deleting the index without trying to create it again

Regards
Leonardo