Invalid index on sde.gdb_items

[Gunterr]

A client reported an error 'ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE' while trying to load a raster.
Looking into the issue, we see that the Domain index on the SDE.DGB_ITEMS is the only invalid index.
We tried a rebuild of the index (as the SDE user) which failed with the following error:

alter index  "SDE"."A1_IX1" rebuild
Error report -
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20085: Parameter ST_SRID 0 is different from ST_GEOMETRY_COLUMNS srid (4326).
ORA-06512: at "SDE.SPX_UTIL", line 1245
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1345
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2492
29858. 00000 -  "error occurred in the execution of ODCIINDEXALTER routine"
*Cause:    Failed to successfully execute the ODCIIndexAlter routine.
*Action:   Check to see if the routine has been coded correctly.

 I'm not sure where it is getting the ST_SRID '0' from.
The index is defined as:

CREATE INDEX "SDE"."A1_IX1" ON "SDE"."GDB_ITEMS" ("SHAPE")
   INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX"  PARAMETERS ('ST_GRIDS = 100 ST_SRID = 4326 ST_COMMIT_ROWS = 10000  PCTFREE 0 INITRANS 4');

All the st_geometries in the table are either null or have 4326 defined in them.

As we believe this is a system generated table we are not sure if dropping and recreating the index will break other things.

Note a second attempt at rebuilding the index provided a slightly different error:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20083: Parameter ST_SRID does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: at "SDE.SPX_UTIL", line 1232
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1345
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2492
29858. 00000 - "error occurred in the execution of ODCIINDEXALTER routine"

Any suggestions?

Note:
Oracle 19c
This is the UV database, the loading of the rasters into development worked perfectly

Regards
Gunther 

[MarceloMarques · Accepted solution]

https://support.esri.com/en-us/knowledge-base/error-ora29861-domain-index-is-marked-loadingfailedunu...

See the resolution steps below and example. If you continue to experience a problem then please open a ticket with Esri Technical Support for further assistance.

/*

DROP INDEX "SDE"."A1_IX1" FORCE;

CREATE INDEX "SDE"."A1_IX1" ON "SDE"."GDB_ITEMS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS = .012:.4:12 ST_SRID = 4326 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4');

*/

Resolution

1 - We executed the below-mentioned SQL query to identify which of the domain indexes were invalid:

SELECT index_name, table_name, index_type, domidx_status, domidx_opstatus FROM user_indexes WHERE index_type='DOMAIN' ORDER BY 1;

2 - Retrieved the DDL for all the above indexes by running the below-mentioned SQL query:

SELECT dbms_metadata.get_ddl('INDEX','A4042_IX1','SDE')||chr(10)||'/' from dual;

3 - Dropped the respective indexes using the below-mentioned query:

Drop Index Index_Name Force;

4 - Recreated the respective indexes by using the DDL which was retrieved earlier.

----------------------------------------------------------------------------------------------------------

! ! !  O T H E R    E X A M P L E   ! ! !  Featureclass Spatial Index

DESCRIBE ALL_INDEXES;

SELECT count(*) FROM ALL_INDEXES d

WHERE (d.status NOT IN ('VALID','N/A')) OR

(d.domidx_status NOT IN ('VALID','N/A')) OR

(d.domidx_opstatus NOT IN ('VALID','N/A'));

SELECT * FROM ALL_INDEXES d

WHERE (d.status NOT IN ('VALID','N/A')) OR

(d.domidx_status NOT IN ('VALID','N/A')) OR

(d.domidx_opstatus NOT IN ('VALID','N/A'));

SELECT index_name, table_name, index_type, domidx_status, domidx_opstatus FROM user_indexes WHERE index_type='DOMAIN' ORDER BY 1;

/*

A316_IX1 UNITS DOMAIN VALID FAILED

*/

--https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_METADATA.html#GUID-A4683EEE-6F54-4081-B7BF-1496096675FA

SET LONG 2000000

SET PAGESIZE 0

SELECT dbms_metadata.get_ddl('INDEX','A316_IX1','GIS') from dual;

/*

CREATE INDEX "GIS"."A316_IX1" ON "GIS"."UNITS" ("SHAPE")

INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS = 20 ST_SRID = 300006 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4 TABLESPACE GIS_SDATA');

*/

DROP INDEX GIS.A316_IX1 FORCE;

--Index GIS.A316_IX1 dropped.

CREATE INDEX "GIS"."A316_IX1" ON "GIS"."UNITS" ("SHAPE")

INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS = 20 ST_SRID = 300006 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4 TABLESPACE GIS_SDATA');

--Index "GIS"."A316_IX1" created.

SELECT count(*) FROM ALL_INDEXES d

WHERE (d.status NOT IN ('VALID','N/A')) OR

(d.domidx_status NOT IN ('VALID','N/A')) OR

(d.domidx_opstatus NOT IN ('VALID','N/A'));

--0 zero!

[Gunterr]

Thanks, the index was dropped and recreated and appears ok.
We were just hesitant as it was a sde generated index not a user generated one.

[MarceloMarques]

@Gunterr - I am glad the issue is fixed.