data_engine maintenance on Oracle may fail after 8.2 upgrade with ORA-20005 error in data_engine logs

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

fter upgrading from any previous UIM version to UIM 8.2 where an Oracle backend is in use, some users may find that data_engine's overnight maintenance process is not completing, and old data is not being deleted and/or indexes are not being rebuilt.

The errors seen in the data_engine log will be similar to the following:

[140713318672128] de: Data Maintenance - RC: -1000 [ORA-20008: spn_de_DataMaint_DeleteOldData: Error: -20005 ORA-20005: spn_de_CreateBNTableIndex: Error: -1408 ORA-01408: such column list already indexed], Purge ? : RN_QOS_DATA_0021, Error , Raw Rows: ? ? ? ? ?0, Time used: ? ?204 ms ~= (0 seconds) (QOS_ORACLE_CHECK_DBALIVE)

To resolve this problem, deactivate data_engine and then run the following two Oracle scripts, which will rename the indexes causing this problem and allow maintenance to proceed.

--- SCRIPT 1 ---

set serveroutput on size 30000;
declare
lOldIndex ? ? ? ?varchar2(80);
lNewIndex ? ? ? ?varchar2(80);
lCmd ? ? ? ? ? ? varchar2(255);
cursor lCursorIndexes is
? ? ? select INDEX_NAME
? ? ? from user_indexes
? ? ? where index_name like '%_IDX2'
? ? ? ? ? ? AND table_name like 'RN_QOS_DATA_%';
? ? ? ??
begin
? ?for lIdx in lCursorIndexes loop
? ? ? lOldIndex := lIdx.INDEX_NAME;
? ? ? lNewIndex := lOldIndex;
? ? ? dbms_output.put_line(lNewIndex);
? ? ? lNewIndex := replace(lNewIndex, '_IDX2','_IDX0');
? ? ? dbms_output.put_line(lNewIndex);
? ? ? lCmd := 'ALTER INDEX ' || lOldIndex || ' RENAME TO ' || ?lNewIndex;
? ? ? dbms_output.put_line(lCmd);
? ? ? EXECUTE IMMEDIATE lCmd;
? ?end loop;
end;

--- SCRIPT 2 ---

set serveroutput on size 30000;
declare
lOldIndex ? ? ? ?varchar2(80);
lNewIndex ? ? ? ?varchar2(80);
lCmd ? ? ? ? ? ? varchar2(255);
cursor lCursorIndexes is
? ? ? select INDEX_NAME
? ? ? from user_indexes
? ? ? where index_name like '%_IDX2'
? ? ? ? ? ? AND table_name like 'BN_QOS_DATA_%';
? ? ? ??
begin
? ?for lIdx in lCursorIndexes loop
? ? ? lOldIndex := lIdx.INDEX_NAME;
? ? ? lNewIndex := lOldIndex;
? ? ? dbms_output.put_line(lNewIndex);
? ? ? lNewIndex := replace(lNewIndex, '_IDX2','_IDX1');
? ? ? dbms_output.put_line(lNewIndex);
? ? ? lCmd := 'ALTER INDEX ' || lOldIndex || ' RENAME TO ' || ?lNewIndex;
? ? ? dbms_output.put_line(lCmd);
? ? ? EXECUTE IMMEDIATE lCmd;
? ?end loop;
end;?



----

After you have run both scripts, activate data_engine and maintenance should be able to proceed without throwing these errors.

Environment

Release:
Component: UIMDEG