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.

        TNS for Linux: Version - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version - Production
        TCP/IP NT Protocol Adapter for Linux: Version - 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.


Add the following line to the sqlnet.ora file on the server.

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.


Alum77 said...

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

Anonymous said...

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

Kiquenet said...

About ora-03113

mike jilligan said...

There are some interesting closing dates on this article but I don’t know if I see all of them middle to heart. There may be some validity but I will take maintain opinion till I look into it further. Good article , thanks and we want extra! Added to FeedBurner as well betfair online casino