Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)

Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)

***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 12.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
  Time: 21-10月-2019 23:46:09
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS: 操作超时
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: 操作超时
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.216.19.38)(PORT=61369))
2019-10-21T23:46:25.673055+08:00


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 12.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
  Time: 21-10月-2019 23:46:25
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS: 操作超时
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: 操作超时
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.216.19.28)(PORT=50149))

Oracle Net Services - Version 11.2.0.1 to 18.3.0.0.0 [Release 11.2 to 18.1]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 18.4.0.0.0 [Release 12.1 to 18]
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
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: 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: 60
nt OS err code: 0
***Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(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=XXX.XX.XXX.XXX)(PORT=45679)

 

The 11g listener log in text format is located here:

$ORACLE_BASE/diag/tnslsnr/<your_host>/<listener_name>/trace/<listener_name>.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 find that this connection was established at the listener at this timestamp:  

22-FEB-2014 10:42:10 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=45679)) * establish * test* 0 .

Compare this to the event in the alert.log with special attention to the timestamp.  
The connection was dropped by the instance at 22-FEB-2014 12:45:09 or roughly 2 hours later.

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: 60
nt OS err code: 0
***Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=45679))

The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limit. In the current case 60 identifies Windows underlying transport layer.

The "nt secondary err code" will be different based on the operating system:

Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX : "nt secondary err code: 238"
AIX: "nt secondary err code: 78"
Solaris: "nt secondary err code: 145"

The alert.log message indicates that a connection was terminated AFTER it was established to the instance.  In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database. 

 This would indicate an issue with a firewall where a maximum idle time setting is in place. 

The connection would not necessarily be "idle".  This issue can arise during a long running query or when using JDBC Thin connection pooling.  If there is no data 'on the wire' for lengthy

periods of time for any reason, the firewall might terminate the connection.

 

SOLUTION

The solution would be to discuss increasing the firewall setting for maximum idle time.  This is a security setting, so it should be done with the Network or System Admin.

In cases where this is not feasible, Oracle offers the following POTENTIAL suggestion (with an important note following):

The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.
DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time. 

SQLNET.EXPIRE_TIME=n  Where <n> is a non-zero value set in minutes.  
 

Once this change is in place, there is NO need to restart the listener or the database.  The change will be in place for all newly spawned server processes following the change.

Be aware that connections that were established prior to this setting would not be affected by this change.  Therefore, you may continue to experience some timeouts until all remote connection are established with this setting in place.

See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall

 

**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.  

PLEASE NOTE:
DCD was never designed to be used as a "virtual traffic generator" as we are wanting to use it for here. This is merely a useful side-effect of the feature.
In fact, some later firewalls and updated firewall firmware may not see DCD packets as a valid traffic possibly because the packets that DCD sends are actually empty packets. Therefore, DCD may not work as expected and the firewall / switch may still terminate TCP sockets that are idle for the period monitored, even when DCD is enabled and working.
In such cases, the firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall.

So please consider your business requirement for allowing connections to remain or appear 'idle' before implementing these suggestions and note that this is a workaround which, on some occasions, may not workaround all the network timeouts.

 

 
 

REFERENCES

NOTE:257650.1 - Resolving Problems with Connection Idle Timeout With Firewall
NOTE:1286376.1 - Versions 11, 12 and Newer Databases: Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in Alert Log

=======================

Versions 11, 12 and Newer Databases: Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in Alert Log (Doc ID 1286376.1)

To Bottom

---

In this Document   Symptoms   Changes   Cause   Solution   References   APPLIES TO: Oracle Net Services - Version 11.1.0.6 to 12.2.0.1 [Release 11.1 to 12.2] Information in this document applies to any platform. TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log   SYMPTOMS     nt secondary err code: 110   Monitoring of the 11g database Alert log(s) may show frequent timeout related messages such as: - On Oracle Solaris: *********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS for Solaris: Version 11.2.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production Time: 22-JAN-2011 21:48:23 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: 145 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) --------- The "nt secondary err code" will be different based on the operating system. Linux x86 or Linux x86-64: "nt secondary err code: 110" HP-UX Server: "nt secondary err code: 238" AIX: "nt secondary err code: 78"   CHANGES No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database. Note: Prior to 11gR1 these same 'Fatal NI connect error 12170' are written to the sqlnet.log CAUSE These time out related messages are mostly informational in nature.  The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection. The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems: For the Solaris system: nt secondary err code: 145: ETIMEDOUT 145 /* Connection timed out */ For the Linux operating system: nt secondary err code: 110 ETIMEDOUT 110 Connection timed out For the HP-UX system: nt secondary err code: 238: ETIMEDOUT 238 /* Connection timed out */ For AIX: nt secondary err code: 78: ETIMEDOUT 78 /* Connection timed out */ For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060) Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.  See (Doc ID 454927.1). SOLUTION Suggested Actions: - Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message. For the message incident below you would search the listener log for the 'Client address' string:  (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '22-JAN-2011 21:48:23'. -Corresponding listener log entry: 22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0 - Alert log entry: ------------ Fatal NI connect error 12170. VERSION INFORMATION: TNS for Solaris: Version 11.2.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production Time: 22-JAN-2011 21:48:23 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: 145 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) ------------ Note the time of the client corresponding client connection(s) in the listener log.  Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.   See the following for more information and a potential solution where a firewall may be causing this issue:   Note:1628949.1   Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters: To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora : DIAG_ADR_ENABLED = OFF Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora: DIAG_ADR_ENABLED_<listenername> = OFF    - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read: DIAG_ADR_ENABLED_LISTENER = OFF -Reload or restart the TNS Listener for the parameter change to take effect. REFERENCES NOTE:1628949.1 - Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out NOTE:454927.1 - Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g NOTE:151972.1 - Archived: Dead Connection Detection (DCD) Explained