24

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.

Community
  • 1
  • 1
Greg Hurlman
  • 17,247
  • 6
  • 50
  • 84
  • Maybe **connection pooling** issues ***http://stackoverflow.com/questions/15980979/odp-net-connection-pooling-parameters*** How is your `connection string`? – Kiquenet Nov 27 '15 at 10:45

7 Answers7

19

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 (connection could be unreliable)

For 2) Check the alert.log for errors. If the server process failed there will be an error message. Also 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)

Gryu
  • 1,692
  • 2
  • 10
  • 24
mathewbutler
  • 949
  • 5
  • 7
  • Where is ***alert.log*** and when it's generated? – Kiquenet Nov 27 '15 at 10:52
  • Alert log is a database side log. Talk to your DBA and have them look for the time stamp when you get the error. You should always look at that log for any ORA-3113 or similar loss of session error. – Christian Shay Feb 03 '16 at 08:46
10

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.

Christian13467
  • 4,934
  • 27
  • 34
  • 2
    Note however that setting the flag incurs a **performance penalty**, according to the [docs](http://docs.oracle.com/html/E10927_01/featConnecting.htm): `This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application.` – Evgeniy Berezovsky Dec 20 '13 at 02:48
  • For shure, your absolutly right! The Validate Connection does one extra database roundtrip to ensure the connection is still connected. – Christian13467 Jan 29 '14 at 17:13
  • Issues ***connection pooling*** http://stackoverflow.com/questions/15980979/odp-net-connection-pooling-parameters and https://collecteddotnet.wordpress.com/2009/05/29/understanding-connection-pooling/ – Kiquenet Nov 27 '15 at 10:47
5

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)

hamishmcn
  • 7,333
  • 10
  • 38
  • 45
  • Looks like you have a different problem to what we had (although in our case IT also assured us that it wasn't the firewall, eventually someone realised that the firewall had to be rebooted before the change took place) – hamishmcn Sep 17 '08 at 16:33
  • I encountered this in a DoD Oracle installation. The net admins liked to setup firewall rule to snap shut a connection after so many minutes. Then the pooled connection would die on next operation. – Daniel P. Bullington May 05 '09 at 20:56
4

end-of-file on communication channel:

One of the course of this error is due to database fail to write the log when its in the stage of opening;

Solution check the database if its running in ARCHIVELOG or NOARCHIVELOG

to check use

select log_mode from v$database;

if its on ARCHIVELOG try to change into NOARCHIVELOG

by using sqlplus

  • startup mount
  • alter database noarchivelog;
  • alter database open;

if it works for this

Then you can adjust your flashrecovery area its possibly that your flashrecovery area is full -> then after confirm that your flashrecovery area has the space you can alter your database into the ARCHIVELOG

j0k
  • 21,914
  • 28
  • 75
  • 84
Mathias Stanley
  • 41
  • 1
3

This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.

After correcting the space issue, this particular error message disappeared.

BradleyDotNET
  • 57,599
  • 10
  • 90
  • 109
Rajesh
  • 31
  • 1
2

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.

Nick Chammas
  • 9,813
  • 7
  • 49
  • 105
Ken Wren
  • 150
  • 1
  • 8
0

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????

Brad Bruce
  • 7,255
  • 3
  • 36
  • 57