Load Data Warehouse Job Fails with ORA-06512 Error

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse job fails with the following error: 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ERROR ENABLING CONSTRAINT. SQLERRM : ORA-02270: no matching unique or primary key for this column-list
ORA-06512: at "DWH.CMN_ENABLE_CONSTRAINTS_SP", line 26

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.

Environment

Release:
Component: ODDWH

Resolution

Run the following SQL query to re-enable 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;
 

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

Additional Information

KB000109473: PPM Patch installation fails with error ORA-02270 on PPM_DWH.CMN_ENABLE_DISABLE_SP

For more details and additional discussion check our Communities article:
https://communities.ca.com/thread/241780360-tech-tip-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-ora-06512-at-ppmdwhcmnenabledisablesp