ORA-03113 for OracleException ODP.NET in ASP.NET

Question

We are using ODP.NET in ASP.NET web site.

Environment: ODP.NET Oracle.DataAccess, Version=4.112.2.0 on Windows Server 2008R2. IIS 7.5

Issue:

From time to time, ODP.NET Oracle connection gets terminated with ORA-03113 error. Sequence of events is:

1. Oracle connection is established and connection is opened

2. Using the connection when a procudure/function/SQL statement (like g.e.: OracleCommand.ExecuteNonQuery) is executed, and ORA-03113 error is raised

ORA-03113: end-of-file on communication channel. Process ID: 1660 Session ID: 266 Serial number: 61873

ExceptionType: Oracle.DataAccess.Client.OracleException, Oracle.DataAccess, Version=4.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342

I read in blogs, sites and forums disabling connection pooling would solve this issue. I dont know because I haven't tried it. If that is the workaround, and working; but, I do not want to do this because this would degrade the performance.

at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
at COMPANY.Frk.Data.OracleClient.Command.DoExecuteNonQuery()
at COMPANY.Frk.Data.OracleClient.Command.ExecuteNonQuery()
at COMPANY.Frk.Data.OracleClient.Command.ExecuteStoredFunction()
at COMPANY.B2b.Services.GD.DAL.Login.CheckUser(Int32 companyid, String login, String pass)

In any Oracle docs:

ORA-03113: end-of-file on communication channel Cause: The connection between Client and Server process was broken. It may also happen if the external agent extproc crashes for some reason.

Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time. There may be some system calls in the .NET function which might terminate the process. Remove such calls.

Any suggestions and troubleshooting about it?

Updated:

I think, IT departament have modified Max Pool Size=300;Connection Timeout=30 attributes in connection string.

troubleshooting using ODP.NET Configuration - Tracing and Performance Counters.

1.WCF Traces (if you have WCF service)

<system.diagnostics>

2.ODP.NET Performance Counters

OraProvCfg

3.ODP.NET Traces (Registry or XML) (In Oracle docs, say too using C# code, isn't?)

a. TraceLevel (string)
b. TraceFileName (string) – path and the file name where you want the traces to get dump
c. TraceOutput (string) – 0 for single files and 1 for multiple files

XML config: <oracle.dataaccess.client>    <settings>

Registry:
    HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\<version>
        HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ODP.NET\<version>

    For 64 bit : HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\2.111.7.0
    For 32 bit : HKEY_LOCAL_MACHINE\Software\WOW6432node\Oracle\ODP.NET\2.111.7.0

   My version: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ODP.NET\4.112.2.0

4: Performance Counters. Enabling/configuring the perfmon counters

This is done on a per app (or per home directory, per machine, etc) basis. The best writeup I've seen is here: http://blog.ilab8.com/2011/09/02/odp-net-pooling-and-connection-request-timed-out

they would be particularly useful:

NumberOfActiveConnectionPools
NumberOfActiveConnections
NumberOfFreeConnections
NumberOfInactiveConnectionPools
NumberOfNonPooledConnections
NumberOfPooledConnections
NumberOfReclaimedConnections
NumberOfStasisConnections

"Also note, that if you want to trace ODP.NET fully managed, you need to put the settings inside the app or web config file, not the registry, AND the settings for TraceLevel are different (1 through 7). And finally, when tracing unmanaged ODP.NET remember that there is a separate WOW registry node for 32 bit Windows. If your app is 32 bit, don't accidentally set it under the 64 bit portion of the registry."

OracleConnection Class - Supported Connection String Attributes https://docs.oracle.com/html/B10961_01/client4.htm#1000348

ODP.NET Connection Pooling Parameters

"The Application Pool in IIS was configured to have "Maximum number of worker processes" set different than the default of 1. What I have found is that the number of connections seen in the database can grow up to the Max Pool Size * Number of Worker Processes.

So if I have Max Pool Size of 5 and 5 Worker Processes then the total number of connections allowed is 25. So it seems that each Worker Process has it's own instance of a connection pool that is not shared across other worker processes.

be aware that Connection Pools are per Worker Process per App Domain, so, Max pool Size of 5 * 5 Worker process * 2 Domains each = 50 connections"

References:

11g Release 1 (11.1.0.6.20) http://docs.oracle.com/html/E10927_01/featConfig.htm

11g Release 2 (11.2) http://docs.oracle.com/cd/E11882_01/win.112/e23174/featConfig.htm#ODPNT155

http://webiv.oraclecorp.com/cgi-bin/webiv/do.pl/Get?WwwID=note:730678.1#Connections not found

Oracle Data Provider for .NET Best Practices https://nvtechnotes.wordpress.com/2009/04/13/oracle-data-provider-for-net-best-practices/

2.Enabling/configuring the perfmon counters This is done on a per app (or per home directory, per machine, etc) basis. The best writeup I've seen is here: http://blog.ilab8.com/2011/09/02/odp-net-pooling-and-connection-request-timed-out

Note especially the specific steps for 32 bit apps, and for the install/configure/launch order of operations.

1.Installing the perfmon counters If they are not already installed, follow the instructions here: http://blog.bekijkhet.com/2010/01/odpnet-performance-counters-do-not-show.html

Enable ODP.Net logging http://blogs.msdn.com/b/biztalknotes/archive/2013/03/14/collecting-troubleshooting-data-for-wcf-oracle-adapter.aspx

https://collecteddotnet.wordpress.com/2009/05/29/understanding-connection-pooling/ http://www.codeguru.com/csharp/.net/net_asp/article.php/c19395/Tuning-Up-ADONET-Connection-Pooling-in-ASPNET-Applications.htm http://oradim.blogspot.com.es/2008/02/experimenting-with-connection-pooling.html

Validate Connection=true

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

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.