DX UIM 20.3 to 20.4 upgrade failed ORA-02443: Cannot drop constraint

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Upgrade from 20.3.3 to 20.4 then to 20.4.8 - encountered an issue with the database sql scripts that are run by the 20.4 installer. When the oracle_ocmetricnamedview_viewvisibility.sql script ran, the install stall failed. Looking at the install log, there was an ORA 02443 error.

Log Error text:

2023-08-28 19:01:57,572 ERROR impl.UIMServerDBSchemaController:run:173 [Thread-38]  - NimException caught(503) , java.sql.SQLException: ORA-02443: Cannot drop constraint  - nonexistent constraint
ORA-06512: at line 5: ORA-02443: Cannot drop constraint  - nonexistent constraint ORA-06512: at line 5

This error is associated to this SQL block:
 
2023-08-28 19:01:57,493 DEBUG util.NimDBScriptRunner:executeSQLCommand:103 [Thread-38]  - Executing SQL command: DECLARE lExists NUMBER;
    BEGIN
      select count(*) into lExists from all_cons_columns where table_name='OCMETRICNAMEDVIEW' and  constraint_name='OCMETRICNAMEDVIEW_UQ' and column_name in('CREATEDBY','CS_ID','GROUP_ID','VIEW_TYPE');
      IF (lExists = 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE OCMETRICNAMEDVIEW DROP CONSTRAINT OCMETRICNAMEDVIEW_UQ';
        EXECUTE IMMEDIATE 'ALTER TABLE OCMETRICNAMEDVIEW ADD CONSTRAINT OCMETRICNAMEDVIEW_UQ UNIQUE (VIEW_NAME, ACCOUNT_ID, CREATEDBY, CS_ID, GROUP_ID, VIEW_TYPE)';
 
      END IF;
   END;

Environment

Release: 20.3
Primary hub server - Windows 2016 (AWS - EC2)
Database - Oracle 19c

Resolution

Given that the select is getting a count to determine the existence of the constraint, the IF condition looks to be incorrect, IF (lExists = 0). I believe that it should be IF (lExists > 0) as it is in the SQL block executed just previous to this one
successful SQL block:

DEBUG util.NimDBScriptRunner:executeSQLCommand:103 [Thread-38]  - Executing SQL command: DECLARE lExists NUMBER;
    BEGIN
      select count(*) into lExists from all_cons_columns where table_name='OCMETRICNAMEDVIEW' and  constraint_name='UNQ_VIEW_ACCOUNT_IDX1';
      IF (lExists > 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE OCMETRICNAMEDVIEW DROP CONSTRAINT UNQ_VIEW_ACCOUNT_IDX1';
      END IF;
   END;

We had the DBA first run the Select statement and verify that the count(*) was 0 indicating that the constraint did not exist.

select count(*) from all_cons_columns where table_name='OCMETRICNAMEDVIEW' and constraint_name='OCMETRICNAMEDVIEW_UQ' and column_name in('CREATEDBY','CS_ID','GROUP_ID','VIEW_TYPE');

Then we had him manually create the constraint:

ALTER TABLE OCMETRICNAMEDVIEW ADD CONSTRAINT OCMETRICNAMEDVIEW_UQ UNIQUE (VIEW_NAME, ACCOUNT_ID, CREATEDBY, CS_ID, GROUP_ID, VIEW_TYPE)';

Then we ran the upgrade again and it was successful.

Additional Information

This issue occurs due to a 'duplicate' issue. To mitigate this, before upgrade, customers can execute the statements listed below which will delete the duplicate and then proceed with the upgrade.

Below is the script that can be used:

--As a precaution first take a backup of the ocmetricnamedview table

Create table tmp_ocmetricnamedview as select * from ocmetricnamedview;

--execute the statement below to delete duplicates

delete from ocmetricnamedview a
where rowid >(select min(rowid) from ocmetricnamedview b where b.view_name=a.view_name
and b.account_id=a.account_id);
commit;