Clarity

Hello fellow community members,

Today I wanted to inform the communities of a known issue and its resolution:

Symptoms:

Error: ORA-02270: no matching unique or primary key for this column-list

Error ORA-06512: at "PPM_DWH.CMN_ENABLE_DISABLE_SP", line 67 during patch installation or upgrade

or Error: ORA-02270: no matching unique or primary key for this column-list  ORA-06512: at "PPM_DWH.CMN_ENABLE_CONSTRAINTS_SP", line 10 during Load Data Warehouse job run

Here is an example of the full error during upgrade:

8/26/16 8:41 PM (ExecTask) Total time: 0H:0M:15S

8/26/16 8:41 PM (ExecTask)        at com.niku.dbtools.DriverApp.installSchemaDriver(DriverApp.java:911)

8/26/16 8:41 PM (ExecTask)        at com.niku.dbtools.Utilities.run(Utilities.java:1862)

8/26/16 8:41 PM (ExecTask)        at com.niku.dbtools.Utilities.main(Utilities.java:975)

8/26/16 8:41 PM (ExecTask) Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error: ORA-02270: no matching unique or primary key for this column-list

8/26/16 8:41 PM (ExecTask) ORA-06512: at "PPM_DWH.CMN_ENABLE_DISABLE_SP", line 67

8/26/16 8:41 PM (ExecTask) ORA-06512: at line 1

8/26/16 8:41 PM (ExecTask)

Root Cause:

The root cause is the Load Data Warehouse job had failed, so the PK were disabled by another stored procedure CMN_DISABLE_CONSTRAINTS_SP, but not reenabled back. Or if it happened after refresh, the services were not stopped and the job was running when the backup was taken.

Workaround:

If you're upgrading:

1. After a full database rollback, run Load Data Warehouse job - Full until successful completion (fix any errors on the way)
2. Re-run the upgrade/ patch installation once the job is successful

If you're facing this whilst running the Load Data Warehouse job:

1.Run the following SQL query to reenable the constraints back on the Data Warehouse database:

BEGIN

-- Enable PK constraints and log the exceptions into the dwh_fk_pk_exceptions table

FOR I IN (SELECT TABLE_NAME, CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE STATUS = 'DISABLED'

AND CONSTRAINT_TYPE = 'P'

)

LOOP

EXECUTE IMMEDIATE 'ALTER TABLE ' ||I.TABLE_NAME|| ' ENABLE CONSTRAINT ' ||I.CONSTRAINT_NAME || ' EXCEPTIONS INTO DWH_FK_PK_EXCEPTIONS';

END LOOP I;

END;

2. Once done, re-run the Load Data Warehouse job

This SQL solution can also be used with upgrade issues just ensure you do a full database rollback before running the query.

The issue is logged as CLRT-80494.

Thank you for sharing this, Nika

Regards

NJ