"ORA-03135: connection lost contact" when inserting large file

Question

I'm trying to do an insert with a potentially large amount of binary data into a remote Oracle (11g) database using Entity Framework (ODP.Net). It works fine for really small files (<5 KB), but for larger ones (e.g. 44 KB) I get an error: "ORA-03135: connection lost contact".

I don't think it's timing out, as the exception occurs within a second of executing the command.

I tried setting both of the following in my connection string, but to no avail:

• Validate Connection=true
• Pooling=false

I also looked in the listener.log file on the remote machine. It shows the connections being made, but no sign of exceptions or terminated connections.

I'm up for suggested fixes or troubleshooting methods.

Edit:

The same SQL operations work when accessing an Oracle instance on the local network.

Answer 1

This can be a complicated issue with many different possible causes and solutions. Start here:

http://www.dba-oracle.com/t_ora_03135_connection_lost_contact.htm

Answer: The oerr utility shows this for the ORA-03135 error:

ORA-03135: connection lost contact

Cause: 1) Server unexpectedly terminated or was forced to terminate. or 2) Server timed out the connection.

Action: 1) Check if the server session was terminated. 2) Check if the timeout parameters are set properly in sqlnet.ora.

The ORA-03135 error is common when connecting remotely when a firewall terminates the connection.

One solution for the ORA-03135 error is to increase the value of the sqlnet.ora expire_time parameter in the sqlnet.ora file or to check for a expire parameter in a SQL*Plus profile.

To diagnose the ORA-03135 error, start by checking to see if the OS PID still exists, using the ps –ef|grep syntax.

Check to see if there is a Network Address Translation (NAT) between the client and server

In Windows, check to see if a Windows firewall is checking for your local connections:

Windows XP -> Control panel -> security -> Tab "Advanced"

Also, setting the parameters sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername to 0 can stop the ORA-03135 error.

Also as above, check the database alert log for errors occurring at the same time as the connection dropping. Sometimes it can be caused by an ora-600 or ora-7445, for example. It shouldn't appear in the listener log as you only get this error after handshaking has been completed.

Answer 2

This situation generally caused by disconnected/stale connection is taken from connection pool. If you get this exception after Pooling=false, most probably firewall blocks the connection because of tcp idle timeout. Ensure tcp idle timeout value is bigger than expected command execution time