CA PPM Load Data Warehouse Job Fails with error ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired with Oracle as Database

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

CA PPM Load Data Warehouse Job Fails with error ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired with Oracle as Database

 

Steps to Reproduce

1. Schedule an Incremental Load Data Warehouse job few times per day 

 

Expected Result:The job to complete successfully every time

Actual Result: The Load Data Warehouse job fails intermittently (sometimes once per month) with error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Any Full or Incremental Load after this will also fail until the lock is removed

Cause

This is a known bug CLRT-81011 and targeted to be fixed in next major release 

Environment

CA PPM 14.2, 14.3,14.4 with Oracle as Database

Resolution

1. Contact the DBA to manually remove the lock from the specified table, then run Load Data Warehouse job - Full Load 
2. To avoid the locks from happening in future ask the DBA to run the following script on the DWH database: 

BEGIN
    FOR J IN (select INDEX_NAME from USER_INDEXES where INDEX_NAME like 'DWH%')
      LOOP
      EXECUTE IMMEDIATE 'ALTER INDEX '|| J.INDEX_NAME || ' NOLOGGING NOPARALLEL';
    END LOOP J;
END;