topic Re: Connection request keeps timing out when connecting to Oracle RDBMS in Product Forum

Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Mon, 12 Dec 2022 12:25:03 GMT

Hi all, I am currently using the Oracle MDA Utility VBO present on DX to connect to an Oracle database, and mainly uses the "Get Collection" action to run the SQL queries. So far, the Oracle MDA VBO works fine for any attempts to connect to a staging version of the Oracle database and is able to get the desired collection data. The issue here is that when attempting to do the same with a production version of the Oracle Database, I kept facing an issue where, whenever the code stage "Open" is executed to open a connection to the database, it always returns "Connection request timed out".

Attempts were also made to increase the connection timeout for as long as 5 minutes, but the same timeout message was returned as well. The firewall layer has been whitelisted for the runtime resources that runs the code to connect to the Oracle database, but no such luck, as the issue persists as well. Database permissions between the staging version and production version of the Oracle databases are matched, so that rules out any possibility of the issue stemming from the database itself.

Would also like to note that when checked on the database's side, it has stated that the connection between Blue Prism and the database is also established successfully. So, it is quite perplexing on how the connection can be established database side, but still returns a connection timed out message back on Blue Prism's side.

Would highly appreciate for any guidance on how to resolve this "Connection request timed out" issue.

Thanks!

Re: Connection request keeps timing out when connecting to Oracle RDBMS

GeoffHirst — Mon, 12 Dec 2022 12:58:14 GMT

HI Caitlin,

Thank you for being a SSC Blue Prism customer and for using Blue Prism.

So, your staging database works fine but production is playing a few games.

1. Is there a significant difference in the amount of data being retrieved between the two systems? Is the production query returning more data?
2. Are you able to connect to the production database via another means from the same machine? Just so we can rule out issues there.
3. Are there any differences in Oracle versions between the staging and production databases? Is the same version of Oracle in use for both?

These are my initial questions come back to me when you can and we can move on from there.

Once again, thank you for using Blue Prism.

regards

Geoff Hirst
DX Engineer EMEA.

Re: Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Tue, 13 Dec 2022 01:06:09 GMT

Hi @GeoffHirst,

Thank you for your help in advance! Here are the answers to your questions:

1. Is there a significant difference in the amount of data being retrieved between the two systems? Is the production query returning more data?
> Yes, I believe there is quite a difference in terms of amount between the staging and production database. Additionally, I would like to note that the production database would have far more traffic compared to the staging database, as there is other process ongoing within the production database as well.
2. Are you able to connect to the production database via another means from the same machine? Just so we can rule out issues there.
> Yes, I have attempted to manually connect to the database via Oracle SQL Developer and was able to connect and run the SQL queries just fine.
3. Are there any differences in Oracle versions between the staging and production databases? Is the same version of Oracle in use for both?
> I believe that both the staging and production databases are using the same version of Oracle.

Best regards,
Caitlin

Re: Connection request keeps timing out when connecting to Oracle RDBMS

GeoffHirst — Tue, 13 Dec 2022 07:43:49 GMT

Hi Caitlin,

Thank you for taking the time and answering my questions. The version of the asset you have is the 1.1 revision. This is the one that is meant for Oracle 21c (or later), as it has the Bulk update action. Given that you have the asset working fine with your staging database, we have to determine what is different about the connection to your production database. Now, the asset requires the OracleManagedDataAccess.dll to be in the Blue Prism Automate folder, given that you have this working for your staging database can you concur that the dll you have in place for the staging database is in fact the same version (or even the same dll!) as the one for the production DB?

These are the details for the version that was used during development.

What I am trying to determine is, are your production and staging systems the same machines and your production db is just a different database or are the systems totally separate? Can we establish the above before we go any further?

regards
Geoff Hirst

Re: Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Tue, 13 Dec 2022 08:22:27 GMT

Hi Geoff,

I can confirm that the Oracle.ManagedDataAccess.dll used is the same dll used in both the Staging and Production.

Here's the screenshot of the version of the dll currently being used:

And to answer your other question, yes, both the staging and production database systems are in a complete separate environment as well.

Best regards,
Caitlin

Re: Connection request keeps timing out when connecting to Oracle RDBMS

GeoffHirst — Tue, 13 Dec 2022 13:35:05 GMT

Hi Caitlin,

Please could you provide us with the connectionstring details for both your staging and production systems?

Also, could you advise what version of Blue Prism you are using?

Thanks

Geoff Hirst
DX Engineer EMEA

Re: Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Tue, 13 Dec 2022 14:02:58 GMT

Hi Geoff,

Sure! Here's the format of the connection string used, the only difference between staging and production is the hostname, SID, and username and password:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=""HOSTNAME.com.my"")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=""SID"")));User Id=username;Password=password;

And the version of the Blue Prism currently being used is 6.10.1.

Do let me know if there any additional information required.

Best regards,
Caitlin

Re: Connection request keeps timing out when connecting to Oracle RDBMS

ewilson — Tue, 13 Dec 2022 14:18:04 GMT

Hello @Caitlin Lee,

You mentioned that you tried extending the timeout value. Did you try that via the connection string, some Oracle client config file, …?

Cheers,

Re: Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Tue, 13 Dec 2022 14:24:41 GMT

Hi Eric,

I extended it via Connection String. So, I were to intentionally extend the timeout value, I would modify the connection string to:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=""HOSTNAME.com.my"")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=""SID"")));User Id=username;Password=password;Connection Timeout = 120;

Although I would prefer to not need to do this, as stated previously, the original connection string (excluding the connection timeout part) worked without any issue when opening a connection to the Staging Database.

Best regards,
Caitlin

Re: Connection request keeps timing out when connecting to Oracle RDBMS

ewilson — Tue, 13 Dec 2022 15:55:44 GMT

@Caitlin Lee,

Thank you for the info. Could you try including the Connection Timeout setting again, but this time also include Pooling=False;? I'm wondering if the default connection pool handling is potentially introducing an issue.

Also, have to tried looking at the Windows Event Viewer, on the runtime resource, to see if there are any additional details being logged by the Oracle library?

Cheers,
Eric

Re: Connection request keeps timing out when connecting to Oracle RDBMS

GeoffHirst — Tue, 13 Dec 2022 15:56:19 GMT

Hi Caitlin,

Could you try setting Pooling=False in the connection string?

Also, is there any chance there could be a tnsnames.ora file lurking around anywhere that could be overriding the connection string parameters?

thanks

Geoff Hirst
DX Engineer EMEA

Re: Connection request keeps timing out when connecting to Oracle RDBMS

CaitlinLee — Wed, 14 Dec 2022 05:42:05 GMT

Hi Geoff and Eric,

Thanks for taking to help out with my issue, however it seems that my team has already found a way to resolve it!

I did try to do as both of you suggested, which was adding the "Pooling=False" to the connection string, which sadly didn't work, and there was nothing additional to note when looking in the Windows Event Viewer either. However, my team has provided a suggestion to use a service name instead of an SID in the connection string, and the bots are finally able to connect to the production database.

Once again, thank you for taking the time to answer my questions.

Best regards,
Caitlin

Re: Connection request keeps timing out when connecting to Oracle RDBMS

GeoffHirst — Wed, 14 Dec 2022 07:06:29 GMT

Hi Caitlin,

I am pleased to hear that you have a solution.

Good luck in all your endeavours with Blue Prism.

regards

Geoff Hirst
DX Engineer EMEA.