9

I have a .Net service that connects to an Oracle database on every request. It works fine at the beginning, but after some number of requests I start getting:

Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
   at MyApp.Services.OracleConnectionWithRetry.ExecuteReader(OracleCommand command)
   ...

Any idea what might be the problem? I dispose all the connections, results and parameters. The load on this service is, well, very low.

Grzenio
  • 35,875
  • 47
  • 158
  • 240
  • just out of curiosity, did you solve this problem with checking the connection status programmatically (i.e. if open already, do nothing) OR setting Validation Connection=true in the web.config, OR both? – Luke Hutton Jul 13 '11 at 23:30
  • 2
    Hi @Luke, I "solved" this problem on a personal level - I quit my job to do a PhD :) – Grzenio Jul 16 '11 at 11:52
  • hehe congratulations, unfortunately I can't go down that route, not smart enough ;) – Luke Hutton Jul 18 '11 at 21:26
  • @luke, seriously now, have you tried the third option from sandyiits answer? I think I would start from there. – Grzenio Jul 20 '11 at 09:43
  • I got a sample web app that will exercise and test all three options, and combinations of, just awaiting for approval. I will report back, thanks. – Luke Hutton Jul 20 '11 at 23:20
  • I couldn't reproduce with the sample app, but I deployed a new ODP data access class composed of all three suggestions by sandyiit and tested on a subset of DA methods. They haven't failed for the past 3 days, where they used to about ~8 times a day. I'm going to cover the rest of the DA methods and hopefully no more 03135 :) – Luke Hutton Jul 29 '11 at 18:35

2 Answers2

13

It happens because your code requests a connection from the Oracle Connection Pool and the connection pool returns a disconnected / stale connection to the Oracle DB. ODP.NET does not itself test the connection status of the connection sent to client.

So to be safe, either you check the connection status == Open for the connection received from the pool when you do a Connection.Open()

OR

let ODP.NET do the checking for you by setting Validate Connection = true in your connection string in web.config.

Both this methods have a impact on performance as they test the connection status each time you need to connect to the database.

A third option which I use is use of exceptions. First be optimistic and use whateven connection is returned from the connection pool. If you get a ORA - 3135 then request a new connection and execute your query again like a while loop. In best case, you can get your 1st connection as valid and your query will execute. In worst case, all the connections in your pool are stale in which case the code will be executed N time (where N is the connection pool size).

sandyiit
  • 1,597
  • 3
  • 17
  • 23
  • I found the Validate Connection option to be a good solution. It added about 20% overhead on average for my application. It would be higher though if you do a lot of trivial queries. Also, there is some overhead to checking the connection status, I think it might involve a round-trip to the server. It was not any faster to check the connection each time in code than to just set the Validate Connection option in the connection string in my testing. – Preston McCormick Mar 05 '12 at 19:28
  • Thats why refrain from checking to start with. Go with the connection you have. Get a new connection if the current one fails. – sandyiit Aug 31 '12 at 11:09
  • When you say to place Validate Connection inside the connection string, are you talking about within the ORA file or in app.config? – William Mar 13 '13 at 21:41
2

I have seen this happen too; try turning off connection pooling with "Pooling=false" in the connection string. I have a theory that idle connections in the pool expire, but ODP.NET does not realize that they have expired, and then when your app grabs one and tries to do something you get that exception.

William Gross
  • 2,083
  • 2
  • 17
  • 35