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

Document ID : KB000019724
Last Modified Date : 22/05/2018
Show Technical Document Details
Issue:

The Datamart Extraction job might fail with the following error message:

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


After this failure, we execute the job again and it is successful.  How can we determine what caused the timeout for the job to wait?

 

Resolution:

 

  • The NOWAIT error means that something else had a database resource such as a specific table referenced, locked. Maybe the previously submitted Datamart Extraction (DME) job had not finished running yet. There should only be one Datamart Extraction job running at one time.
     
    1. Check for incompatible jobs configured. The following jobs should be incompatible to Datamart Extraction job.
      1. Datamart Extraction (itself)
      2. Datamart Rollup - Time Facts and Time Summary
      3. Time Slicing
      4. Oracle Table Analyze
      5. Investment Allocation 
      6. Rate Matrix Extraction 
    2. Please check for any database locks, perhaps there is still a lock from a previous failed extraction. You can check with DBA for this.
    3. Check for any concurrently Workflow Processes that might be running that has steps that update the same tables 
  • The above error could also mean, if the previous DME job was finished running, that the amount of connection parallelism needs to be reduced to half of the server capacity to reduce resources overflowing. Please check with DBA for this configruation.
  • The Datamart extraction is extremely 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.

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

Additional Information:

Reference TEC528729 : Configuring and Scheduling Some of the Main Jobs