Datamart Extraction job Failed: NBI_CLEAN_DATAMART_SP: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Products

• Clarity PPM On Premise

Issue/Introduction

The Datamart Extraction job fails with the following error message:

PMD application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - : ORA-20000: Error in NBI_CLEAN_DATAMART_SP: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "NIKU.NBI_EXTRACT_SP", line 362
ORA-06512: at line 1

After this failure, we executed the job again and it's successfully completed.  How can we determine what caused this error? 

Environment

Release: All supported Clarity releases
Component:

Resolution

The NOWAIT error means that something else had a database resource such as a specific table referenced, locked. Perhaps the previously submitted Datamart Extraction (DME) job had not finished running yet due to interruption in BG service.  Or a job/process was running that was also updating the NBI tables, which caused a lock. 

1. Please check with your DBA for any database locks,  there could be a lock from a previous failed extraction. 
2. Check for incompatible jobs configured and which jobs were running at this time. Anything that could be updating the NBI tables may be causing the lock.  The following jobs may need to be set as incompatible to Datamart Extraction job.
   
   • Rate Matrix Extraction 
   • Oracle Table Analyze
   • anything that could be touching to the NBI tables
     If you see that a manual run of Datamart Extraction or scheduled at another time works, this is a strong indication that there is a job that has to be set as incompatible
3. Check for any concurrent processes that run at the same time that have steps updating the same NBI_ tables 

Additionally:

This error could also mean that the amount of connection parallelism needs to be reduced to half of the server capacity to reduce resources overflowing. Please check with DBA to see if they can enable this

The Datamart Extraction is I/O intensive as it detects incremental changes in the transaction tables and then writes those changes to the Datamart reporting tables. In a typical environment, there is enough CPU power to launch multiple parallel processes to access the disks in parallel, therefore minimizing the overall Datamart run times. Please check installation guide to check how to enable it.

 

Additional Information

If the above solution, does not resolve the issue, please reach out to Broadcom Support.