Saturday, March 28, 2015

12170 TNS-12535/TNS-00505: Operation Timed Out error on alert log after Firewall Implementation



Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)
Description: https://support.oracle.com/epmos/adf/images/t.gif


Description: https://support.oracle.com/epmos/adf/images/t.gif
In this Document







APPLIES TO:
Oracle Net Services - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
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=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 11g listener 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 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=121.23.142.141)(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=121.23.142.141)(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 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

**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 consider your business requirement for allowing connections to remain or appear 'idle' before implementing these suggestions.
REFERENCES
NOTE:257650.1 - Resolving Problems with Connection Idle Timeout With Firewall
NOTE:1286376.1 - Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log



Doc ID 1335630.1
Doc ID 1286376.1

configuration on ODA :

[oracle@primoda1 ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora 
SQLNET.EXPIRE_TIME=10
SQLNET.INBOUND_CONNECT_TIMEOUT=2000
DIAG_ADR_ENABLED = OFF
DIAG_ADR_ENABLED_LISTENER = OFF






3 comments:

  1. It is illegal to post MOS note publicly.

    ReplyDelete
  2. If the MOS were more intelligent, there would be no need to publish this information

    ReplyDelete
  3. iS YOUR PROBLEM SOLVED BY ONLY SETTING SQLNET.EXPIRE_TIME=n

    ReplyDelete