Clarity PPM

Expand all | Collapse all

Tech Tip: ORA-02270: no matching unique or primary key for this column-list ORA-06512: at "PPM_DWH.CMN_ENABLE_DISABLE_SP"

  • 1.  Tech Tip: ORA-02270: no matching unique or primary key for this column-list ORA-06512: at "PPM_DWH.CMN_ENABLE_DISABLE_SP"

    Posted 07-05-2017 05:15 PM

    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;

    1. 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.



  • 2.  Re: Tech Tip: ORA-02270: no matching unique or primary key for this column-list ORA-06512: at "PPM_DWH.CMN_ENABLE_DISABLE_SP"

    Posted 07-12-2017 06:07 AM

    Thank you for sharing this, Nika

     

    Regards

    NJ