Nika_Hadzhikidi

Tech tip : Load Data Warehouse job fails with ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Discussion created by Nika_Hadzhikidi Employee on Nov 1, 2016
Latest reply on Jan 6, 2017 by Nika_Hadzhikidi

Hello fellow communities members,

 

 

For your information, we have now fixed the following Clarity PPM issue:

 

CLRT-81011 Load Data Warehouse job fails with database lock ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

Summary:
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 database lock is removed

 

This is fixed in 15.2 (not a released version yet) and will also be ported to some of our lower version patches.

 

In the meantime, this is a workaround (for On Premise customers):

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;

This will disable the parallel index rebuild, which was contributing to the issue.

 

 

I hope this helps -Nika

Outcomes