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

Article Id: 4993
Status: Published
Updated On: 14-02-2018 08:11
Legacy Id: TEC1028652
Products:
CLARITY PPM FOR ITG , CA Project & Portfolio Management , CLARITY PPM FEDERAL , CA Project & Portfolio Management SAAS , STARTER PACK-CLARITY PPM
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;