DB load throws ORA-02429 cannot drop index in OT

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

While upgrading Automation Engine from to 12.3.X, the following error gets thrown during database upgrade, error is present in DBLoad log file:

declare i integer;
begin
select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC');
if i > 0 then
EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';
end if;
EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';
END;

20200805/155456.030 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: b887a0 DB-HDBC: c6f418
20200805/155456.030 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20200805/155456.030 - U00003592 UCUDB - Status: '' Native error: '2429' Msg: 'ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-06512: at line 7'
20200805/155456.030 - U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: '

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Database: Oracle

Cause

The probably cause is incorrect maintenance on the database. At some point in time, the PRIMARY KEY CONSTRAINT PK_OT has been dropped and recreated.

Resolution

The error is caused by incorrect maintenance on the database:

20201209/100051.426 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20201209/100051.426 - U00003592 UCUDB - Status: '' Native error: '2429' Msg: 'ORA-02429: cannot drop index used for enforcement of unique/primary key

The following statements are used to create the involved table and index:

CREATE TABLE OT(CREATE TABLE OT( OT_OH_Idnr NUMBER(38,0) NOT NULL, OT_Type NUMBER(38,0) NOT NULL, OT_Lnr NUMBER(38,0) NOT NULL, OT_Content CLOB NULL, CONSTRAINT PK_OT PRIMARY KEY ( OT_OH_Idnr, OT_Type, OT_Lnr ) using index TABLESPACE UC4_INDEX, CONSTRAINT FK_OT_OH FOREIGN KEY ( OT_OH_Idnr ) REFERENCES OH ( OH_Idnr )) LOB(OT_Content) STORE AS(ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA;

CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX;

This does create the table, the primary key constraint (which is an index), and the index NK_OT_SEARCH_NC in exactly this sequence.

Please check the Oracle documentation at https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm:

If an index is present containing the same columns and order as the PRIMARY KEY CONSTRAINT, the new PK will be based on this index (NK_OT_SEARCH_NC in this case). This is done by Oracle to decrease space usage (one index less) and increase performance (one index less to maintain).

The correct solution would be to make sure that his database does match our initially delivered script, in this case the following statements in the following order (to be done ONLY after having being validated with Technical Support):

ALTER TABLE OT DROP CONSTRAINT PK_OT;
DROP INDEX NK_OT_SEARCH_NC;
ALTER TABLE OT ADD CONSTRAINT PK_OT PRIMARY KEY (OT_OH_Idnr, OT_Type, OT_Lnr) using index TABLESPACE UC4_INDEX ENABLE VALIDATE;
CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX;

This sequence makes sure the database does match our provided create-script, and will be safe to be updated with our upgrade-scripts in future.

Additional Information

Please reach out to Technical Support to validate the queries if needed and always take a backup of the database before launching the update and/or the statements.