Load DWH job fails with errors "ORA-02049: timeout..." or "ORA-00054: resource busy..."

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The incremental Load Data Warehouse (DWH) job fails with the below error in the BG-DWH log:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_TEAM). SQLERRM : ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at line 24

If the full job runs, it fails immediately with the error in the BG-DWH log:

ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: BEGIN
    DWH_CFG_POP_HISTORY_SP;
    EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';
 DWH_CFG_PRE_CONFIG_SP(P_DBLINK => 'SCHEMADWH_SCHEMA', P_FULL_RELOAD => 'Y');
 DWH_CFG_RECOMPILE_SP;
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SCHEMADWH.DWH_CFG_PRE_CONFIG_SP", line 31
ORA-06512: at "SCHEMADWH.DWH_CFG_PRE_CONFIG_SP", line 31
ORA-06512: at line 4

Cause

This is due to contention in the job scheduler.

Resolution

1. Clear out any canceled job runs.
2. Set the job incompatibilities for the Load DWH job if you have noticed any specific compatibility issue
3. Try running the job sometime later to take care of the deadlock situation
4. Check with the DBA team to check for inactive sessions that need to be removed.

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures