CODEWITHSUNDEEP
.netssrs-2008odp.netoracleclient
Subhash Dike
Subhash Dike

Reputation: 1891

Oracle Connection to .Net - Connection Pool

This is more specifically with SQL Server Reporting Services, however I also see this as typical .Net application issue as well.

Here is the problem. We are using SSRS to report data from Oracle database. From day 1 our users are complaining that they get errors like ORA-2396: Exceeded maximum idle time, ORA-01012-Not logged on etc.

We have included DBAs, app developers, network engineer, but so far we could not find any better clue to that.

Recently while perform some research, I have observed that many people have documented one "known" issue with .Net connectivity to oracle. As they mention, if we are using connection pooling, there is a possibility that even un-useful connection might be available in the pool. On next request, this connection might get used and at that point the error will be thrown. As confirmed by our DBA, our oracle instance is set to kill the connection if it's idle for X minutes.

Here are the links which has the aforesaid mention http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.90).aspx

http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx

There are couple of more as well. However I do not get a very confirmed statement anywhere stating that yes, this is an issue with current (latest versions of .Net and oracle etc).

On solution part, they mention that we shall be using "Validate Connection=true" attribute as part of connection string. But if I try to use that, it says "Key word not supported - validate connection". ( I have also tried Validcon)

Questions I have are these 1. Is it confirmed that this "dirty" read from connection pool problem exists? 2. If so, what are the solutions? 3. If not, what could be causing the issues at our end.

If you need more information, feel free to comment.

Upvotes: 3

Views: 4340

Answers (2)

b_levitt
b_levitt

Reputation: 7415

The root issue here belongs your DBAs. I see the same error when I use an account (ie my personal oracle account) that is succeptible to the same idle connection cleanup that our DBAs have running. The best fix would be to have them assign an "service" account to you that is not terminated after an idle time.

That said, I would guess that you are getting "Key word not supported - validate connection" because you are using the microsoft provider and not the oracle provider. I'm not that familiar with SSRS connections so I'm not sure how you select one over the other. In .net however it's the difference between System.Data.OracleClient and Oracle.DataAccess.

If you get that resolved, the validate connection option adds overhead by pre-pinging a connection before using it, making it marginally better than simply disabling pooling. Another thing you can try is setting min pool size = 0 which lets the pool go down to nothing, leaving a smaller chance that a connection sits idle for too long. Unfortunately it's not a perfect fix as the provider only checks for stale connections AFTER they are used.

Here's the reference to the connection string parameters for oracle's data provider if you didn't already have it: http://download.oracle.com/docs/html/E10927_01/featConnecting.htm#i1006393

Upvotes: 1

Yahia
Yahia

Reputation: 70369

This is a known problem and it occurs wherever you use connection pooling and depends on several things - one of them being the settings on the server... sometimes even complicated when using an Oracle RAC... Some Oracle .NET providers have built-in solutions for pooling taking care of this problem seamlessly... one such provider is the one sold by Devart... http://www.devart.com/dotconnect/oracle/

I am not affiliated with Devart, just a happy customer...

Upvotes: 0

Related Questions