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

book

Article ID: 188260

calendar_today

Updated On:

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 recommended job incompatibilities for the Load DWH job:

    • Refresh Data Warehouse OData Model (SaaS)
    • Restore Domains Job
    • Create Data Warehouse Trend
    • Update Data Warehouse Trend
    • Delete Data Warehouse Trend
    • Time Slicing
    • Update Aggregated Data
    • Datamart Extraction
    • Investment Allocation
    • Post Timesheets
    • Post to WIP
    • Post Transactions to Financial
    • Rate Matrix Extraction
  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: