ORA-03113: end-of-file on communication channel after long inactivity in ASP.Net app

Question

I've got a load-balanced (not using Session state) ASP.Net 2.0 app on IIS5 running back to a single Oracle 10g server, using version 10.1.0.301 of the ODAC/ODP.Net drivers. After a long period of inactivity (a few hours), the application, seemingly randomly, will throw an Oracle exception:

Exception: ORA-03113: end-of-file on communication channel
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()

...Oracle portion of the stack ends here...

We are creating new connections on every request, have the open & close wrapped in a try/catch/finally to ensure proper connection closure, and the whole thing is wrapped in a using (OracleConnection yadayada) {...} block. This problem does not appear linked to the restart of the ASP.Net application after being spun down for inactivity.

We have yet to reproduce the problem ourselves. Thoughts, prayers, help?

---

More: Checked with IT, the firewall isn't set to kill connections between those servers.

Answer 1

Check that there isn't a firewall that is ending the connection after certain period of time (this was the cause of a similar problem we had)

Answer 2

Same problem here, but I get ORA-02396 and ORA-03113 with ODP.NET. By the way, I found a guy reporting this error at Oracle forums, I'll try the solution provided in this thread: http://forums.oracle.com/forums/thread.jspa?threadID=191750 May work for you too.

Answer 3

ORA-03113: end-of-file on communication channel

Is the database letting you know that the network connection is no more. This could be because:

1. A network issue - faulty connection, or firewall issue
2. The server process on the database that is servicing you died unexpectedly.

For 1) (firewall) search tahiti.oracle.com for SQLNET.EXPIRE_TIME. This is a sqlnet.ora parameter that will regularly send a network packet at a configurable interval ie: setting this will make the firewall believe that the connection is live.

For 1) (network) speak to your network admin

For 2) Check the alert.log for errors, if the server process failed there will be an error message here and a trace file will have been written to enable support to identify the issue. The error message will reference the trace file.

Support issues can be raised at metalink.oracle.com with a suitable Customer Service Identifier (CSI)

Mathew Butler

Answer 4

You could try this registry hack

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters] "DeadGWDetectDefault"=dword:00000001 "KeepAliveTime"=dword:00120000

If it works just keep increasing the KeepAliveTime. It is currently set for 2 minutes.

Ken

Answer 5

The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)

If this is not something that runs frequently (don't do it on your home page), you can turn off connection pooling.

There is one other "gotcha" that is not mentioned in the article. If the first thing you try to do with the connection is call a stored procedure, ODP will HANG!!!! You will not get back an error condition to manage, just a full bore HANG! The only way to fix it is to turn OFF connection pooling. Once we did that, all issues went away.

Pooling is good in some situations, but at the cost of increased complexity around the first statement of every connection.

If the error handling approach is so good, why don't they make it an option for ODP to handle it for us????

Answer 6

Add Validate Connection=true to your connection string.

Look at this blog to find more about.

DETAILS: After OracleConnection.Close() the real database connection does not terminate. The connection object is put back in connection pool. The use of connection pool is implicit by ODP.NET. If you create a new connection you get one of the pool. If this connection is "yet open" the OracleConnection.Open() method does not really creates a new connection. If the real connection is broken (for any reason) you get a failure on first select, update, insert or delete.

With Validate Connection the real connection is validated in Open() method.