4

Im using Oracle12c with the application written in C# and using Oracle.ManagedDataAccess.dll to handle the DB Connection.

A product we have has started to occasionally throw this exception after running fine for years:

Oracle.ManagedDataAccess.Client.OracleException (0xFFFFFC0C): Pooled connection request timed out
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

I know the cause of this error. Looking at the code neither the OracleConnection or the OracleCommand objects are being disposed. So these connections are building up until it eventually throws this exception.

The fix is straight forward. Wrap these in Using statements. I dont need help with that.

However my interests me is why this problem has started now. This software was running for years without issue. They did some database maintenance, updated other software on the same server, then this problem started. I dont know what Db maintenance they did.

The connection string in the application does not specify any pool attributes.

Is there a oracle db setting which would cause a lower amount of simultaneous connections in the database that could have caused this to start occurring?

UPATE

I wrote a little test app to check the limit. It just loops around and opens a connection, performs a basic query and doesnt dispose the connection. On my test system it starts throwing this exception after 640ish loops. It varies give or take 10 loops each time i run it.

What is setting this limit?

CathalMF
  • 9,705
  • 6
  • 70
  • 106
  • If I remember properly, a using block, which call dispose, is effectively calling the 'Close method' in the connection object, this method call is what send the Pooled Connection back to the Pool and makes it available again, are you calling the Close method in your loop? if not the Connection will remain alive and not going back to your Pool. – Jesus Salas Mar 13 '19 at 19:10
  • @JesusSalas As i mentioned in the question, i know how to fix it in the code. What im asking is why it started displaying this problem now. Is there some settings in the Oracle DB which could have been changed to reduce the maximum number of connections or sessions. – CathalMF Mar 14 '19 at 10:00
  • 1
    Have you check if there has been a change in the idle connection timeout and related settings in Oracle Settings? If you do not effectively close the connection besides not going back to the pool (and maybe rolling back any implicit active transaction) this could cause to retain a connection as 'active' for longer time, what at the end will exhaust your pool. if these settings were 'lower' that could explain what you are experiencing right now. – Jesus Salas Mar 14 '19 at 17:53
  • I have the same error, the worst is that after the first timeout error oracle cannot recover even after manually cleaning the pool connections, I get the same error until I restart my web. – fsbflavio Apr 11 '19 at 11:53
  • It seems that "connection timeout" also occurs when using a wrong version of the assembly. In our case, instad of 3.1.21.1 (.net core), the error occurred when due to a build error either 3.21.100 or (.net framework) 4.122.21.1 were present. A clear error occurs solely when the Oracle.ManagedDataAccess.dll is not present. Maybe Oracle internally calls some code for a number of times and even with internal errors always returns "connection timeout" from an exception handler? – Guido Leenders Jun 14 '23 at 16:11

1 Answers1

0

I just had the same problem.

The reason, why you get that exception is, that the Oracle Pool Manager doesn't have a free connection anymore (per default you can have up to 100 connections). Often the reason for that problem are not closed connections (so 'using' was the right ways).

Even though you already found a solid solutions for that problem, I want to add those:

Schneewind
  • 71
  • 6