10

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.

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
Ishmael Smyrnow
  • 942
  • 1
  • 9
  • 27
  • 1
    Well maybe it is not that, but I had the "ORA-03135: connection lost contact" on weird stuff when the connection was not lost at all it only happend that Oracle was no longer able to swallow the SQL statement submitted. I got it on huge queries (>2000 line of code). Maybe I am sending you on the right track but I thought after 4 hours no answer I at least could tell you that piece of my own experience. For me it looked like the message always appears when the parser of the SQL command crashed in the background. – hol Aug 28 '12 at 20:47
  • Might be worth checking the alert log on the remote machine to see if that reported an error. If not it maybe points to the problem being at the client end, not the server. Also, are there any firewalls in the way, or anything else at network level that might have logs you can check? – Alex Poole Aug 29 '12 at 07:11
  • A network log would be helpful. The firewall is not on, and it's a Windows server machine hosting Oracle. I'm not sure where TCP traffic is logged on a Windows machine, but I may have to find that out. – Ishmael Smyrnow Aug 31 '12 at 21:20
  • We use ODP.Net for inserts op binaries of over 100 MB and for gigabytes per day, so it can work. Please include your C# code as a sample. – Guido Leenders Jan 05 '14 at 19:01

4 Answers4

3

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.

Andrew Brennan
  • 333
  • 1
  • 9
  • I haven't looked into this issue in some time, but since the troubleshooting methods outlined here look pretty complete, I'm marking it as the answer. – Ishmael Smyrnow Sep 25 '14 at 18:34
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

ali kucuk
  • 31
  • 1
0

Try to use KeepAlive setting, This prevents pooling errors of loosing connection.

https://github.com/oracle/dotnet-db-samples/issues/58 (originally this is for .NET Core and for version of Oracle.ManagedDataAccess.Core no lower than 2.19.100 )

connection.KeepAlive = true; before opening a connection

Rimi
  • 71
  • 2
0

Remove oracle client.. Reinstall oracle client.. and give proper network and oracle connections in tnsnames file

aidaroos
  • 1