TROUBLESHOOTING ORA-03135 connection lost contact from ODP.NET connections with connection pooling enabled

SYMPTOMS

ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:

ORA-03135: connection lost contact

CAUSE

The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.

Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.

For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.

SOLUTION


The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:

1) Simply catch the exception, and retry the operation.


2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.

Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.


3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.


Note 151972.1 Dead Connection Detection (DCD) Explained


4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact.  To disable connection pooling, add "pooling=false" to the connection string.