Wednesday, 1 May 2013

How To Resolve TNS-12535 or TNS-00505 Operation Timed Out Errors


While examining logs I have found lots of “TNS Operation Timeout Errors” in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-MAY-2009 18:54:41
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=3448))


The same error message was repeating during whole day almost for every application server.

I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections. 

Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.

Solution:

Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10


In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.

3 comments:

Alum77 said...

A great post.
I had the same problem with an Oracle 12.1.0.10 and I have solved adding SQLNET.EXPIRE_TIME = 10 in the last line of file SQLNET.ORA
Thanks!

Anonymous said...

how to check firewall idle session timeout bcos I have same issue

Kiquenet said...

About odp.net ora-03113

https://community.oracle.com/thread/3870860?start=0&tstart=0