Connection request keeps timing out when connecting to Oracle RDBMS

[CaitlinLee]

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!

[GeoffHirst]

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.

[CaitlinLee]

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

[GeoffHirst]

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

[CaitlinLee]

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

[GeoffHirst]

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

[CaitlinLee]

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

[ewilson]

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,​

[CaitlinLee]

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

[ewilson]

@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