Fatal Ni Connect Error 12170 Sqlnet.log
+ Solutions etc. Wednesday, August 20, 2014 Removing Fatal NI connect error 12170 from Database Alert Log In our one of the 11gR2 Production Database, there were so many entries w.r.tFatal NI connect error 12170 + TNS-12535: TNS:operation timed out +TNS-00505: Operation timed out (with different ports), found in both RAC Instances Alert Log file. Problem: Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.3.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production Time: 20-AUG-2014 14:17:36 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=xxx.xxx.xxx.xxx)(PORT=44326)) Reason: 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 / Workaround: 1) Add the following line to the sqlnet.ora file on the server. SQLNET.EXPIRE_TIME=10In 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. **In an installation that includes GRID, this parameter should be set in the RDBMS_HOME/network/admin/sqlnet.ora file. This would be the default location for sqlnet.ora file parameters referenced by the instance.
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 http://manishnashikkar.blogspot.com/2014/08/removing-fatal-ni-connect-error-12170.html 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 http://hemora.blogspot.com/2013/05/how-to-resolve-tns-12535-or-tns-00505.html 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=10In 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. Posted by hemora at 11:47 Labels: LISTENER, Network, TNS 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.ORAThanks! 19 June 2015 at 07:59 Anonymous said... how to check firewall idle session timeout bcos I have same issue 15 July 2015 at 08:47 Kiquenet said... About odp.net ora-03113https://community.oracle.com/thread/3870860?start=0&tstart=0 17 December 2015 at 07:43 Post a Comment Newer Post Older Post Home Subscribe to:
this Document Symptoms Changes Cause Solution References APPLIES TO: Oracle Net Services - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1] http://dbafoiz.blogspot.com/2015/03/12170-tns-12535tns-00505-operation.html Information in this document applies to any platform. SYMPTOMS The following error is reported in the database alert log. ***Note the "Client address" is posted within the error stack in this case. Fatal NI connect error 12170. VERSION INFORMATION: TNS for 64-bit Windows: Version 11.2.0.3.0 - Production Oracle Bequeath NT Protocol Adapter for 64-bit Windows: fatal ni Version 11.2.0.3.0 - Production Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production Time: 22-FEB-2014 12:45:09 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: fatal ni connect 60 nt OS err code: 0 ***Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=121.23.142.141)(PORT=45679)) The PORT field here is the ephemeral port assigned to the client for this connection. This does not correspond to the listener port. CHANGES No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database and they are now visible in the alert log. Note: Prior to 11gR1 these same 'Fatal NI connect error 12170' are written to the sqlnet.log. This document describes a problem that arises when a firewall exists between the client and the database server. CAUSE We can search the listener log covering the same time period using this search criteria. (HOST=121.23.142.141)(PORT=45679) The 11glistener log in text format is located here: $ORACLE_BASE/diag/tnslsnr///trace/.log Again, this is the client's IP address and the unique ephemeral port assigned to the client for this connection. In this case, we findthat this connection was established at the listener at this timestamp: 22-FEB-20