APM 10.5.1 Oracle database upgrade gives the error "ORA-02429: cannot drop index used for enforcement of unique/primary key."

Article Id: 6400
Status: Published
Updated On: 18-03-2018 22:28
Legacy Id: TEC1627009
Products:
CA Application Performance Management Agent (APM / Wily / Introscope) , INTROSCOPE
Issue/Introduction:

After upgrading from APM 10.1 to APM 10.5.x, the installation fails and the Install\SchemaTools.log file contains ERROR:
===
[INFO] [main] [com.wily.apm.dbutil.SqlExecutor] Executed sql statement in file: dbupgrade-apm-oracle-10.1.0.0.sql
[INFO] [main] [com.wily.apm.dbutil.SqlExecutor] Running sql statement: ALTER TABLE appmap_vertices   DROP (search_attrib1,         search_attrib2,         search_attrib3,         search_attrib4,         search_attrib5,         application_id)
[ERROR] [main] [com.wily.apm.dbutil.SqlExecutor] Sql error in file: dbupgrade-apm-oracle-10.1.0.0.sql
[ERROR] [main] [com.wily.apm.dbutil.SqlExecutor] java.sql.SQLException: ORA-02429: cannot drop index used for enforcement of unique/primary key
===

Cause:

The APM 10.1 APPMAP_VERTICES table contains an additional index APPMAP_VERTICES_ATTRIB_LOOKUP which contains 2 columns vertex_id, start_time which are in the primary key of the table.
That index is not explicitly dropped in script dbupgrade-apm-oracle-10.1.0.0.sql. However the "ALTER TABLE appmap_vertices" statement to drop the columns is implicitly trying to drop the index but is failing because of the primary key dependency.
To be able to drop the index requires the APPMAP_VERTICES table primary key constraint to be temporarily disabled.

Environment:

Upgrading from APM 10.1  to APM 10.5.x

Resolution:

To resolve the problem the following steps should be used:
a. Restore a backup of the APM 10.1 Oracle database
b. Run this SQL  
ALTER TABLE APPMAP_VERTICES DISABLE CONSTRAINT <PRIMARY_KEY_CONSTRAINT>; 
DROP INDEX APPMAP_VERTICES_ATTRIB_LOOKUP; 
ALTER TABLE APPMAP_VERTICES ENABLE CONSTRAINT <PRIMARY_KEY_CONSTRAINT>; 
NOTE: <PRIMARY_KEY_CONSTRAINT> is the name of the primary constraint for table APPMAP_VERTICES which is system generated and so the name will be specific to the Oracle instance. However it can be easily determined by the Oracle DBA e.g. using Oracle SQL Developer.
c. Re-run the APM Database schema upgrade from 10.1 to 10.5.x

Additional Information:

This problem has only yet been reported when upgrading an Oracle APM database schema from APM 10.1 to 10.5.x