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

Document ID : KB000004993
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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

Environment:
CA PPM 14.2, 14.3,14.4 with Oracle as Database
Cause:

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

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;