Load Data Warehouse Job Fails with ORA-06512 Error

Article Id: 125744

Status: Published

Updated On: 01-02-2019 13:36

Products:

CLARITY PPM FOR ITG , Clarity PPM On Premise , CLARITY PPM FEDERAL , Clarity PPM SaaS , Clarity PPM SaaS - Application , Clarity PPM SaaS - Application , 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