How can i get the SQL ID or SID to trace back Oracle debugging from Copy Data Activity from ADF.

Question

Hi Team,

we are facing below error while loading oracle database using copy data activity using Azure Data Factory.

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Error in parameter 1.,Source=msora28.dll,'

I have tried to connect to Oracle DBA. He is asking for SQL ID or SID or the back end query it will execute to trace back the issue. Please help me on getting this details.

Thanks,

Naveen

Answer 1

Hi

Thanks for reaching out to Microsoft Q&A.

From the error it seems the ADF is unable to fetch connection to the oracle database since there are multiple other processes already running (Resource busy/timeout). You cannot get the SID/ProcessID details from the ADF error.

Can you check if you are using the correct & existing database and table names? I trying to see why there it waits and times out instead of connecting instantly which should be the case unless there is truly resource contention from the oracle database.

One way is to try with him to filter out by the loginname(that you are using to connect oracle), I know this might not work all the time because there might not be a unique login for ADF in most cases.

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.