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
Thanks for sharing this tip Nika!
Tech tip : Load Data Warehouse job fails with ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Thank you Nika for sharing. When is the 15.2 release scheduled?
15.2 has the ETA sometime in Spring 2017. Since you are an OD customer, we had requested the workaround to be applied proactively on all the affected servers. If you face this issue, please raise a Support ticket with CA Support and ask it to be addressed.
Thank you -Nika