In this Document

APPLIES TO:

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))

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)

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

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 overpass all the network timeouts. 

REFERENCES

Dead Connection Detection (DCD) Explained (文档 ID 151972.1)

***Checked for relevance on 08-APR-2016***

                        DEAD CONNECTION DETECTION 
                        =========================

OVERVIEW 
-------- 
 
Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including
Oracle Net8 and Oracle NET. DCD detects when a partner in a SQL*Net V2 client/server
or server/server connection has terminated unexpectedly, and flags the dead session
so PMON can release the resources associated with it.
 
DCD is intended primarily for environments in which clients power down their 
systems without disconnecting from their Oracle sessions, a problem
characteristic of networks with PC clients.

DCD is initiated on the server when a connection is established. At this 
time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an 
alarm.  The timer interval is set by providing a non-zero value in minutes for 
the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a "probe" packet to the 
client. (In the case of a database link, the destination of the link
constitutes the server side of the connection.)  The probe is essentially an 
empty SQL*Net packet and does not represent any form of SQL*Net level data, 
but it creates data traffic on the underlying protocol. 
 
If the client end of the connection is still active, the probe is discarded, 
and the timer mechanism is reset.  If the client has terminated abnormally, 
the server will receive an error from the send call issued for the probe, and 
SQL*Net on the server will signal the operating system to release the 
connection's resources. 
 
On Unix servers, the sqlnet.ora file must be in either $TNS_ADMIN or 
$ORACLE_HOME/network/admin. Neither /etc nor /var/opt/oracle alone is valid. 
 
It should be also be noted that in SQL*Net 2.1.x, an active orphan process 
(one processing a query, for example) will not be killed until the query 
completes. In SQL*Net 2.2, orphaned resources will be released regardless of 
activity.

This is a server feature only.  The client may be running any supported 
SQL*Net V2 release.
 
 
THE FUNCTION OF THE PROTOCOL STACK 
---------------------------------- 
 
While Dead Connection Detection is set at the SQL*Net level, it relies heavily
on the underlying protocol stack for it's successful execution. For example,
you might set SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file, but it is unlikely
that an orphaned server process will be cleaned up immediately upon expiration
of that interval. 
 
TCP/IP, for example, is a connection-oriented protocol, and as such, the 
protocol will implement some level of packet timeout and retransmission in an 
effort to guarantee the safe and sequenced order of data packets. If a timely 
acknowledgement is not received in response to the probe packet, the TCP/IP 
stack will retransmit the packet some number of times before timing out. After
TCP/IP gives up, then SQL*Net receives notification that the probe failed.
 
The time that it takes TCP/IP to timeout is dependent on the TCP/IP stack, and
timeouts of many minutes are entirely common.  This has been an area of concern
for many customers, as many retransmissions at the protocol layer causes what
could be a significant lag between the expiration of the DCD interval and the
time when the orphaned process is actually killed. 
 
The easiest way to determine if the protocol stack is causing such a delay 
involves testing different DCD intervals.


TESTING THE PROTOCOL STACK 
--------------------------
Set the SQLNET.EXPIRE_TIME parameter to 1 minute and note the time required to
clean up an orphaned server process.  Then set SQLNET.EXPIRE_TIME to 5 minutes
and again observe the time required to clean up the shadow. If the TCP/IP
timeout is the reason the server resources do not get released, the time to
clean up the shadow should increase by about 4 minutes.

If the TCP/IP retransmission timeout is indeed the problem, the Operating 
System kernel can be tuned to reduce the interval for and number of packet 
retransmissions (on many Unix platforms, the file 
/usr/include/netinet/tcp_timer.h contains the configuration parameters). 
 
Reducing the interval and number of retransmissions may impact other system 
components, since in effect you are shrinking the window allowed for
connections to process data, possibly resulting in inadvertent loss of 
connections during periods of heavy system load.  Slower connections from
remote sites may be impacted by this change.
 
Kernel parameters that may affect retransmission include but are not limited 
to TCP_TTL, TCPTV_PERSMIN, TCPTV_MAX, and TCP_LINGERTIME. 
 
*** To avoid disrupting other system processes, it is important to contact the 
appropriate vendor for assistance in tuning the operating system kernel or 
protocol stack. *** 
 
 
MONITORING DEAD CONNECTION DETECTION 
------------------------------------ 
The best way to determine if DCD is enabled and functioning properly is to 
generate a server trace and search the file for the DCD probe packet. To 
generate a server trace, set TRACE_LEVEL_SERVER=16 and 
TRACE_DIRECTORY_SERVER=<path> in sqlnet.ora on the server (note the location
of the sqlnet.ora file).  The resulting trace file will have a filename of
svr_<PID>.trc and will be located in the specified directory. 
 

Is DCD Enabled? 
--------------- 
For pre-Oracle8i versions, enable level 16 SQL*Net server tracing and search
the resultant server trace file for an entry like the following: 

  osntns: Enabling dead connection detection (1 min) 
 
The timer interval listed should match the value of SQLNET.EXPIRE_TIME.

For Oracle8i onwards, you should see the following:

  nstimini: entry 
  nstimig: entry 
  nstimig: normal exit 
  nstimini: initializing NLTM in asynchronous mode 
  nstimini: normal exit 
  nstimstart: entry

 
Is DCD Working? 
---------------
Search the server trace file for DCD probe packets. They will appear in the
form of empty data packets, as follows: 
 
  nstimexp: entry 
  nstimexp: timer expired at 05-OCT-95 12:15:05 
  nsdo: entry 
  nsdo: cid=0, opcode=67, *bl=0, *what=1, uflgs=0x2, cflgs=0x3 
  nsdo: nsctx: state=8, flg=0x621c, mvd=0 
  nsdo: gtn=93, gtc=93, ptn=10, ptc=2048 
  nsdoacts: entry 
  nsdofls: entry 
  nsdofls: DATA flags: 0x0 
  nsdofls: sending NSPTDA packet 
  nspsend: entry 
  nspsend: plen=10, type=6 
  nttwr: entry 
  nttwr: socket 4 had bytes written=10 
  nttwr: exit 
  nspsend: 10 bytes to transport 
  nspsend:packet dump 
  nspsend:00 0A 00 00 06 00 00 00  |........| 
  nspsend:00 00 00 00 00 00 00 00  |........| 
  nspsend: normal exit 
  nsdofls: exit (0) 
  nsdoacts: flushing transport 
  nttctl: entry 
  nsdoacts: normal exit 
  nsdo: normal exit 
  nstimexp: normal exit

The entry:

  nspsend:00 0A 00 00 06 00 00 00  |........| 
  nspsend:00 00 00 00 00 00 00 00  |........| 
 
represents the probe packet.  Note that DCD packets are 10 bytes long when they
are issued to the protocol stack. Once the protocol header and trailer bytes
for the underlying protocols have been added, the packet could be approximately
70 bytes long.
 
If DCD is enabled, you will see these probe packets written to the trace file
when the timer expires.  If the server is a UNIX system, it might be useful to
establish a connection and tail the trace file: 

  tail -f svr_<PID>.trc 

The time elapsed after each probe packet is written to the server trace should 
match the SQLNET.EXPIRE_TIME value.

Note: from version 9.2.0.4.0 onwards, DCD probe packets are no longer traced in
SQL*Net trace files, however DCD packets can be observed using other forms of
tracing, such as network sniffer tracing.


KNOWN PROBLEMS OR LIMITATIONS 
----------------------------- 
- Of the few reported problems, perhaps the most significant is DCD's poor 
performance on Windows NT.  Dead connections are cleaned up only when the 
server is rebooted and the database is restarted.  Exactly how well DCD works 
on NT depends on the client's protocol implementation. SQL*Net v2.3 has 
improved the performance over earlier releases. 
 
  This has been logged as port-specific Bug#303578. 

 
- On SCO Unix, a problem was reported in which server processes spin, consuming
large amounts of CPU, once the DCD timer expires. The problem is due to improper
signal handling and can be eliminated by disabling DCD.
 
  This is port-specific Bug#293264

- Orphaned resources are not released if only the client application is 
terminated. Only after the client PC has been rebooted does DCD release these 
resources. For example, if a Windows application is killed yet Windows remains
running, the probe packet may be received and discarded as if the connection is
still active.  As it currently stands, it appears that DCD detects dead client
machines, but not dead client processes.
 
  This is logged as generic Bug#280848. 
 
- The SQL*Net V2 implementation on MVS does not use the generic DCD mechanism,
and therefore the SQLNET.EXPIRE_TIME parameter does not apply. The KEEPALIVE
function of IBM's TCP/IP is used instead. This was implemented prior to
development of DCD. 
 
  This is documented in port-specific Bug#301318. 
 
- DCD relies heavily on issuing probe packets during any phase of the connection.
This is not be possible with some protocols which run half-duplex. Hence, DCD is
not enabled on protocols like APPC/LU6.2. 
 
  This is not a bug, but is rather the intended design. 
 
- Local connections using BEQ protocol adapters are not supported with DCD.  
Local connections using the IPC protocol adapters are supported with DCD.

-BUG#1388806 : On Windows NT, DCD FAILS AFTER 16 CONNECTIONS

 
A FINAL NOTE...
--------------
On most OS'es (even more recent versions of Windows) if a process exits 
abnormally or is killed by an administrator, the OS will still gracefully 
clean up resources associated with that process including the network
connection(s).  It will tell the server on the other end that it is closing 
the network connection. DCD is still useful for times when there are problems 
with the physical network (e.g. ethernet cable falls off the machine) or if
the OS kernel panics and crashes (e.g. blue screen of death) before it can
close the network connections.  It may have another side benefit with certain
load balancing hardware, that may prematurely abort connections it thinks have
been idle too long, by sending a dummy packet to the client periodically.
 
Under no circumstances should you rely 100% on Dead Connection Detection.  
It was developed to handle clients that have abnormally exited. Clients should
always exit their applications gracefully. It is the responsibility of the
application developer to make this possible. DCD is intended only to clean up
after abnormal events.
 
DCD is much more resource-intensive than similar mechanisms at the protocol 
level, so if you depend on DCD to clean up all dead processes, that will put 
an undue load on the server. 
 
Clearly it is advantageous to exit applications cleanly in the first place.


REFERENCES
---------- Note:395505.1 How to Check if Dead Connection Detection (DCD) is Enabled in 9i and 10g Note:438923.1 How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any Client/Server Network Tracing

Resolving Problems with Connection Idle Timeout With Firewall (文档 ID 257650.1)

In this Document

APPLIES TO:

PURPOSE

This article describes about the connection idle time-out issues that occur while a firewall or load balancer is used to monitor and control TCP traffic between clients (such as application servers / iAS components)  and Oracle Databases.

SCOPE

This article is primarily intended for Application/Database Administrators and Network Administrators who would like to understand and try to workaround connection "IDLE TIMEOUT" issues in a firewall or F5 load balancer environment.

DETAILS

An Overview

Firewalls (FW) has become common in today's networking to protect the network environment.  The firewall recognizes the TCP protocol and it records the client server socket end-points.  Also, FW recognize the TCP connection closure, and then will release the resources allocated for recording the opening connection. For every end-point pairs , the firewall must also allocate some resources(may be small).

When the client or server closes the communication it sends TCP FIN type packet, this is a normal socket closure. However, it is not uncommon that the client server communication abruptly ending without closing the end points properly by sending FIN packet, for example, when the client or server crashed, power down or a network error which prevents sending the closure packet to the other end. In that cases, the firewall will not know that the end-points will no longer use the opened channel. As a passive intermediary, it had no way to determine if the endpoints are still active. As is it not possible to maintain resources forever, and also, it is a security threat keeping a port open for undefined time. So, firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time. 

Initially FW were designed to protect the application servers, network  and then to protect client/server connection. With these in mind, a time-out in terms of hours (1 hour is the default for most FW)  is reasonable. With the advent of more complex security schemes, FW are not only between client and server, but also between different application servers ( intranet, demilitarized zone (DMZ) , and such) and database servers.  So, the horizon of 1 hour idle time for communication between servers maybe not be appropriate. 

Idle connections can be expected from an application server.  There is the case of J2EE using pooled JDBC connections. The pool usually returns the first available connection to the requester, so the first connections of the pool list are the most likely to be active. The last one, which are at the end of the list, are only used at peak loads, and most of the time it will be inactive. 

Other cases are the connections established from a HTTP Server, either SQL connections from mod_plsql, or AJP connections from mod_oc4j.

Blackout

One of the inconvenience of theses blackout, is that they are passive. None of the endpoints will be notified that the communication was banned . Only when the client or server tries to contact its peer, it comes to know that the peer end is no more active and the communication has already been broken.  

The worst of all scenarios are the so called "passive listeners" .  They will never know.  Because, passive listeners are those processes at an endpoint that are simply waiting for commands to arrive from the other end. A typical example of this are the backend database server processes, which are reading from the socket looking new SQL statements to execute , and after the request is answered, they return to their passive state. When a blackout occurs, they will stay forever in this reading state, unless some of the following techniques are applied.

Resolving problems with connection idle time-out

TCP KeepAlive 

Ensure TCP KeepAlive is set appropriately for your environment. Refer to your OS documentation for details.

DCD for DataBase Servers

For database connections, one of the endpoints is a passive listener, either is a dedicated process or a dispatcher process. If the connection becomes blacked out , this backend will never know that client cannot send any more requests, and then will lock important resources as database sessions, locks , and at least , a file descriptor used for maintaining the socket. 

A solution is to make this backend "not so" passive, using the DCD (dead connection detection) to figure out if the communication is still possible.  

Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10  (10 minutes, for example).  With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released.

There are two benefits with this DCD 
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall *may* consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive. 

2. If the SQLNET.EXPIRE_TIME (let's say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection. 

The first case is recommended when the connection comes from another application server , and the second makes sense for client applications. 

DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10  then do not expect that the connections will be flagged as dead exactly after 10 minutes of the blackout or network outage. Please seeNote:151972.1  "Dead Connection Detection (DCD) Explained" for details on DCD. The TCP timeout and TCP retransmission values also adds to this time.  

AJP Connections 

It is not a default behavior in 9.0.2, but if Patch 2862660 is installed, the connection between and OHS server process and the J2EE can be maintained for more than a single request.  If the parameter Oc4jConnTimeout is set, the OHS will maintain the connection for at least that time.  The problem is that the child process may became inactive before that time-out occurs, and then the connection will remain open. While the child process is inactive, the connection will be idle, and there is chance to be blackout by the FW. 

If this happens, the first thing that the child will do is to close it when it becomes active. But at this time, the TCP socket closing cannot be completed, due the blackout. Although the http child process can simply ignore the closing failure and continue the creation of a new connection, the passive listener at the 
j2ee side (the worker thread) will be hook without a chance for the resources to be released. 

To solve this , the Patch 3151686 must be installed and the java-option 
-Dajp.keepalive=true 
must be enabled. 

After this, the blackout detection will rely on the TCP KeepAlive provided by the operating system. 

As DCD , this process consist in send probes -empty packages- when a socket had been inactive for a period of time. If there is no response, the socket will be closed, and then, even the passive listener, will receive and exception or signal to let him know that the no further communication is possible.


CONCLUSION 

As the firewalls extend their functionality , and are now are placed in between application servers, some tuning and parameter adjusting must be made to overcome the default rules established for client/server communications. However, remember that a firewall idle timeout setting is simply the way this product functions and any changes to it should be made with agreement between all parties involved.