AlertLogErrors: 12170TNS-12535/TNS-00505: Opera

The customer feedback system often reports Session Timeout, which causes the application test to fail. After checking the alert Log, we found that FatalNIconnecterror12170.VERSIONINFORMATION: TNSforHPUX: Version11.2.0.4.0

The customer feedback system often reports Session Timeout, which causes the application test to fail. After checking the alert Log, the Fatal NI connect error 12170 is found. version information: TNS for HPUX: Version 11.2.0.4.0-Production Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.4.0-Pro

The customer feedback system often reports Session Timeout, which causes the application test to fail. After checking the alert Log, it is found that

Fatal NI connect error 12170.  VERSION INFORMATION:        TNS for HPUX: Version 11.2.0.4.0 - Production        Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production        TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production  Time: 29-SEP-2014 20:42:56  Tracing not turned on.  Tns error struct:    ns main err code: 12535TNS-12535: TNS:operation timed out    ns secondary err code: 12560    nt main err code: 505TNS-00505: Operation timed out    nt secondary err code: 238    nt OS err code: 0  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.78.199.47)(PORT=55447))Mon Sep 29 20:42:56 2014

Although most websites or mos describe Fatal NI connect error 12170, the following configurations are considered:

# Adjust listener. ora vi $ ORACLE_HOME/network/admin/listener. ora added: DIAG_ADR_ENABLED_LISTENER = OFFINBOUND_CONNECT_TIMEOUT_LISTENER = 180 # adjust sqlnet. ora vi $ ORACLE_HOME/network/admin/sqlnet. ora added: DIAG_ADR_ENABLED = OFFSQLNET. INBOUND_CONNECT_TIMEOUT = 180

These have been configured, but now reports 12170 TNS-12535 TNS-00505 error, by combining mos found that this problem may occur because the firewall policy setting between the application server and the database server is not suitable for business query requirements, the application server and database server firewall times out (for example, if the application server initiates a large query and runs it on the database server, no results are returned, but the network has timed out and the session is terminated)
Add knowledge points

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 queryor when using JDBC Thin connection pooling. If there is no data 'on the wire' for lengthyperiods of time for any reason, the firewall might terminate the connection.

Solution

The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time.  In cases where this is not feasible, Oracle offers the following suggestion: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  is a non-zero value set in minutes.  See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall

Of course, in addition to the following database solutions, you can also solve the problem at the network firewall level, such as increasing the idle network termination time.

Reference: Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)
Fatal NI Connect Error 12170, 'tns-12535: TNS: operation timed out 'reported in 11g Alert Log (Doc ID 1286376.1)

• Fatal NI connect error 12170
• WARNING: inbound connection timed out (ORA-3136)
• TNS-12525 TNS-12535 TNS-12606
• ORA-609 TNS-12537 & TNS-12547 in 11g Alert. log
• Maximum availability + lgwr sync in DATAGUARD causes the master database to fail to start
• Multiple listeners cannot be started properly due to IPC.
• Incorrect hostname configuration causes TNS-12542 errors
• ERROR OGG-01224 TCP/IP error 110 (Connection timed out); retries exceeded.

Alert Log Errors: 12170 TNS-12535/TNS-00505: Opera, thanks to the original author for sharing.