Clarity

Expand all | Collapse all

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

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

    Broadcom Employee
    Posted Nov 01, 2016 04:35 PM

    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



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



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

    Posted Jan 05, 2017 01:25 PM

    Thank you Nika for sharing. When is the 15.2 release scheduled?



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

    Broadcom Employee
    Posted Jan 06, 2017 05:17 PM

    Hi Manjusha,

     

    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