My Oracle Support Banner

After Calling "Action any.DDService purge_history", Several Database Indices Become UNUSABLE (Doc ID 2651788.1)

Last updated on MARCH 12, 2021

Applies to:

Oracle Network Management for Utilities - DMS - Version 2.3.0.2.0 to 2.4.0.1.0 [Release 2.3 to 2.4]
Oracle Utilities Network Management System - Version 2.3.0.2.0 to 2.4.0.1.0 [Release 2.3 to 2.4]
Information in this document applies to any platform.

Symptoms

On : 2.3.0.2.0 version, NMS Infrastructure

After calling "Action any.DDService purge_history", several indices become UNUSABLE.

 

These errors start appearing in the PFDBService log:

-- ORACLE ERROR -- OCI return code from OCIStmtExecute is OCI_ERROR
while processing command 'INSERT into flm_dev_violations_warnings (solution_id, analysis_key, update_date, load_type, fdr_cls, fdr_idx, dev_cls, dev_idx, phase, eda_type, violation_code, violation_value, violation_limit, percent, status, nominal_voltage) VALUES (:sol_id, :analysis_key, TO_DATE(:upd_time, 'MM/DD/YYYY HH24:MI:SS'), :load_type, :fdr_cls, :fdr_idx, :dev_cls, :dev_idx, :phase, :eda_type, :violation_code, :violation_value, :violation_limit, :violation_percent, :status, :nominal_voltage )'
ORA-01502: index 'NMS_DB.FLM_ONGOING_VIOLATIONS_IDX' or partition of such index is in unusable state
ORA-06512: at "NMS_DB.TR_FLM_DEVICE_VIOLATIONS", line 3
ORA-04088: error during execution of trigger 'NMS_DB.TR_FLM_DEVICE_VIOLATIONS'


-- ORACLE ERROR -- OCI return code from OCIStmtExecute is OCI_ERROR
while processing command 'UPDATE flm_dev_violations_history SET end_date =  TO_DATE(:end_date, 'MM/DD/YYYY HH24:MI:SS') WHERE nvl2(end_date, 1, 0) = 0 and (fdr_cls,fdr_idx,dev_cls,dev_idx,phase,violation_code) not in   (select fdr_cls,fdr_idx,dev_cls,dev_idx,phase,violation_code from flm_dev_violations_warnings where solution_id = 0 and load_type=0 and violation_code > 100 and violation_code <200)'
ORA-01502: index 'NMS_DB.FLM_ONGOING_VIOLATIONS_IDX' or partition of such index is in unusable state
parse offset = 0
01/31/20 07:47:03: 0x809,ctp=11005b148,"sql" sql error: (UPDATE flm_dev_violations_history SET end_date =  TO_DATE(:end_date, 'MM/DD/YYYY HH24:MI:SS') WHERE nvl2(end_date, 1, 0) = 0 and (fdr_cls,fdr_idx,dev_cls,dev_idx,phase,violation_code) not in   (select fdr_cls,fdr_idx,dev_cls,dev_idx,phase,violation_code from flm_dev_violations_warnings where solution_id = 0 and load_type=0 and violation_code > 100 and violation_code <200)) from (1/41:22544682.0), result=-1 ORA-01502: index 'NMS_DB.FLM_ONGOING_VIOLATIONS_IDX' or partition of such index is in unusable state


For additional information on the purge history job see: <Document 2482167.1> What Happened to the Script "product_dms_purge_history.ces" in 2.3.0.1?

For other data purging related KM Document references see:

<Document 1385163.1> Improving Model Build Performance with mb_purge.ces and Managing Index Statistics

Changes

 This only impacts systems where Oracle's Database PARTITIONING is licensed.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
 Workaround
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.