Why does ADF copy activity to Oracle RDBMS configured with concurrent connections and degree of parallelism always fail?

Question

Configuring ADF copy activity to read from Azure Blob Storage and concurrently write to Oracle database (to speed up the copy activity) fails always with ORA-00054 "Resource busy and acquire with no wait error.....". DBA says ADF had X active connections in parallel and all of them were waiting before ending up failed. This is a test oracle table that no other process has access to read/write and no visible activity outside ADF user per DBA.

On the other side, If I were to restrict the degree of parallelism and concurrent connections to 1 for oracle copy activity ADF would run very slow but doesn't end up fail.

FYI: No performance issues on the oracle rdbms, this is actively serving our test users for a while.

Any help is highly appreciated.

Answer 1

Hi @Ajay Devulapalli

Thank you for posting your query!

The issue you are encountering with the Azure Data Factory (ADF) copy activity to Oracle RDBMS, specifically the ORA-00054 error, typically indicates that the resource you are trying to access is busy and cannot be acquired. This can happen when multiple concurrent connections are attempting to access the same resource, leading to contention.

Resolving the problem:

DDLs or database migrations are only meant to be run during downtime. All the product services should be turned completely off using$TOP/bin/go/stop_local.shand the database should not be in use.

Other alternatives are:

1. Find and stop the session that is preventing the exclusive lock.
2. In Oracle 11g you can set ddl_lock_timeout, for example, allow DDL to wait for the object to become available, simply specify how long you would like it to wait:SQL> alter session set ddl_lock_timeout = 600; Session altered.

Reference: https://www.ibm.com/docs/en/product-master/12.0.0?topic=tdi-ora-00054-resource-busy-acquire-nowait-specified-timeout-expired-error

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.