DOMIDX_OPSTATUS Status For 'CSF_MD%' Indexes Failed. (Doc ID 1083577.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Field Service - Version 12.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 23-Dec-2013***


Symptoms

The 'CSF_MD%' indexes should have the VALID status for ‘status’ and ‘domidx_opstatus’ columns but after Geospatial installation, DOMIDX_OPSTATUS status for 'CSF_MD%' indexes is failed.

STEPS
-----------------------
1. Followed the <Note 565980.1> Release 12.1 - Field Service Spatial Upgrading Document .

2. Ran the script csfgeoidx.sql is located at $csf_top/patch/115/sql to create spatial indexes on all the spatial tables.

3. Ran the script csfmdmvw.sql, located in $CSF_TOP/patch/115/sql to refresh materialized views

4. Run the query

SELECT index_name, status, table_name, parameters, domidx_opstatus
FROM all_indexes
WHERE index_name like 'CSF_MD%'
AND domidx_opstatus is not null;


The output was:

CSF_MD_ADM_BOUNDS_MAT_V_N1	VALID	CSF_MD_ADM_BOUNDS_MAT_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_HYDROS_MAT_V_N1	        VALID	CSF_MD_HYDROS_MAT_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_LAND_USES_MAT_V_N1	VALID	CSF_MD_LAND_USES_MAT_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_POIS_MAT_V_N1	        VALID	CSF_MD_POIS_MAT_V	LAYER_GTYPE=POINT SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RAIL_SEGS_MAT_V_N1	VALID	CSF_MD_RAIL_SEGS_MAT_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RD_SEGS_FUNC0_V_N1	VALID	CSF_MD_RD_SEGS_FUN0_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RD_SEGS_FUNC1_V_N1	VALID	CSF_MD_RD_SEGS_FUN1_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RD_SEGS_FUNC2_V_N1	VALID	CSF_MD_RD_SEGS_FUN2_V	SDO_COMMIT_INTERVAL=500 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RD_SEGS_FUNC3_V_N1	VALID	CSF_MD_RD_SEGS_FUN3_V	SDO_COMMIT_INTERVAL=10000 SDO_DML_BATCH_SIZE=50000 TABLESPACE=APPS_TS_TS_IDX	FAILED

CSF_MD_RD_SEGS_FUNC4_V_N1	VALID	CSF_MD_RD_SEGS_FUN4_V	SDO_COMMIT_INTERVAL=10000 SDO_DML_BATCH_SIZE=50000 TABLESPACE=APPS_TS_TS_IDX	FAILED


5. When run the script csfmdmvw.sql by providing the correct tablespace and using user APPS/PASS@<SID>. SET SERVEROUTPUT ON to view the debug messages, the output is

Enter value for idx_tablespace: APPS_TS_TX_IDX

Refresh of CSF_MD_ADM_BOUNDS_MAT_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_HYDROS_MAT_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_LAND_USES_MAT_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_POIS_MAT_V is successfull

Refresh of CSF_MD_RAIL_SEGS_MAT_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_RD_SEGS_FUN0_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_RD_SEGS_FUN1_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_RD_SEGS_FUN2_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_RD_SEGS_FUN3_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_MD_RD_SEGS_FUN4_V failed : ORA-12008: error in materialized view refresh path

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

Refresh of CSF_WOM_ROAD_HIWAY_MAT_V failed : ORA-12008: error in materialized view refresh path

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

Index CSF_MD_ADM_BOUNDS_MAT_V_N1 on table CSF_MD_ADM_BOUNDS_MAT_V exists

Index CSF_MD_ADM_BOUNDS_MAT_V_N3 on table CSF_MD_ADM_BOUNDS_MAT_V exists

Index CSF_MD_HYDROS_MAT_V_N1 on table CSF_MD_HYDROS_MAT_V exists

Index CSF_MD_HYDROS_MAT_V_N4 on table CSF_MD_HYDROS_MAT_V exists

Index CSF_MD_LAND_USES_MAT_V_N1 on table CSF_MD_LAND_USES_MAT_V exists

Index CSF_MD_POIS_MAT_V_N1 on table CSF_MD_POIS_MAT_V exists
 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms