Oracle ManagedDataAccess - Connection Request Timed out - Pooling

Question

I'm finally admitting defeat and asking for help. I've done everything I can think of to solve this problem, but it seems I'm incapable of doing it.

I'm working with: VS2010 C# Oracle 12c ODP.Net Managed121012

I have inherited an app that uses both the managed and unmanaged dataaccess dll. It was working until I de-installed oracle. I then re-installed the 11g client for a 64bit machine. Right away I noticed that there was only dataaccess dll for framework 2 installed, but I continued anyway. I then copied all the oci and ora dlls from the client_1 folder into the bin directory of my app as well as the Oracle.DataAccess.dll into my bin directory too. I also copied Oracle.ManagedDataAccess.dll into this folder.

My application ran successfully as long as I didn't change anything on my datasets. I would have happily carried on like this, except I have to create more datasets. When I tried to add a new dataset, my data source connection wizard drop down list was blank. Then I tried to re-create the connections, but could only see the .Net Framework DProviders. I could not see the managed provider. At some point I also got this error "no data provider is currently selected".

Thinking it's because the managed provider wasn't installed I uninstalled the 11g client and installed the 64bit 12c client and copied all the relevant files into the bin of my app. I added the following lines to my app.config file:

      <configSections>
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />

  <system.data>
<DbProviderFactories>
  <remove invariant="Oracle.DataAccess.Client" />
  <remove invariant="Oracle.ManagedDataAccess.Client" />
  <add name="ODP.NET, Managed Driver" invariant="Oracle.DataAccess.Client"
         description="Oracle Data Provider for .NET, Managed Driver"
         type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />     
</DbProviderFactories>

After this I can now see some of the old data sources, but I can't connect to my database because I get a "Connection Request Timed out". When I manually creating a new connection, I can connect fine with the unmanaged provider, but get a connection request timed out error.

I'm really at the end of my rope and would really appreciate fresh eyes before I use the rope.

Thanks in advance.

Answer 1

Since this was the top search result for this error I thought I'd provide a solution to a new way of causing this error. If you are working in standard .Net (i.e. not framework) and accidentally install the "Oracle.ManagedDataAccess" (which is for use in .Net Framework solutions) instead of "Oracle.ManagedDataAccess.Core" you'll get this timeout error with no indication of what is causing it. Spent over an hour trying all sorts of stuff until finding this "oh duh" answer!

Answer 2

Try to add the below properties to your connection string

Connection Timeout=60;Connection Lifetime=180;Pooling=true;Min Pool Size=1;Max Pool Size=100;Incr Pool Size=10;Validate Connection=true

It was worked with me.

Answer 3

Go back Oracle.ManagedDataAccess.Core.dll version 3.21.1

The namespace is the same using Oracle.ManagedDataAccess.Client

Answer 4

Please check the Oracle output parameter. Because my application also was facing the same scenario.

We changed oracle output parameter to Refcursor. Maybe this solution is useful.

Answer 5

We saw this same error when doing some testing using the immediate window in Visual Studio. We were really confused for awhile until we ran the code normally (not using the immediate window) and the problem went away. So there is some kind of interaction with the Oracle ManagedDataAccess and the immediate window in Visual Studio that can cause this error.

I know this isn't going to be the answer for most of you, but I hope it's useful to someone.

Answer 6

Just in case anyone is suffering from this issue when running on mono 4.8. There seems to be a synchronization bug, causing Oracle client is unable to extend connection pool once all connections are used. See this bug

Answer 7

I had identical problem after switching to managed driver. Even wrote a test app using both native and managed drivers. The conclusion was that the managed driver needs much more time to open new connection than the native driver. A solution that worked for us was to set a large connection timeout using connection string.

<connectionStrings>
    <add name="ConnectionString" connectionString="data source=xxxx;user id=xxxx;password=xxxx;persist security info=false;Connection Timeout=120;" />

</connectionStrings>