[Update 2011-07] Setting JDBC oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR new in 11g http://download.oracle.com/docs/cd/B28359_01/rac.111/b28254/hafeats.htm http://download.oracle.com/docs/cd/B28359_01/rac.111/b28252/configwlm.htm [end of update 2011-07] If host in tns entry points to a non-existing IP (or hostname that immediately resolves to an IP that does not exist), such as timeouttest = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 123.45.67.89)(PORT = 1521)) ) (CONNECT_DATA = (service_name = anything)) ) Windows tnsping takes about 21 seconds to get TNS-12535: TNS:operation timed out. C:\>echo. | time 2>nul: | head -1 & tnsping timeouttest & echo. | time 2>nul: | head -1 The current time is: 10:54:22.67 TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 12-FEB-2008 10:54:22 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 123.45.67.89)(PORT = 1521))) (CONNECT_DATA = (service_name = anything))) TNS-12535: TNS:operation timed out The current time is: 10:54:43.99 That took 21.32 seconds (echo, time, ... commands took a little time). This 21 second timeout can't be changed by setting sqlnet.send_timeout, sqlnet.recv_timeout, or sqlnet.inbound_connect_timeout or sqlnet.outbound_connect_timeout in sqlnet.ora (as of Oracle 10.2.0.3, see Note:438060.1, and be aware of "Bug:6743100 SQLNET.OUTBOUND_CONNECT_TIMEOUT CAUSES SQLPLUS TO HANG WITH NTS AUTHENTICATION"). But setting TcpMaxConnectRetransmissions to 2 (default 3) shortens the timeout to 9 seconds. Search for explanation of this param at google.com/microsoft.html. Changing this param should be followed by reboot; without reboot, there's no effect. (I also tried setting TcpInitialRTT to 2 (default 3). No effect was observed.) The same test done on Linux (2.6.9-34.0.2.EL) takes 3 minutes: ... TNS-12535: TNS:operation timed out real 3m9.110s user 0m0.016s sys 0m0.011s If you change a few TCP parameters, the time can be shortened: # cd /proc/sys/net/ipv4 # echo 3000 > tcp_keepalive_time #default 7200 # echo 5 > tcp_retries2 #default 15 # echo 1 > tcp_syn_retries #default 5 $ time tnsping timeouttest ... TNS-12535: TNS:operation timed out real 0m9.025s user 0m0.013s sys 0m0.010s -------------------------------------------------------------------------------- * 10g and 11g Enhancement 10g's other clients, e.g. sqlplus, Java OCI apps, but not tnsping and of course not Java thin clients, do honor sqlnet.outbound_connect_timeout. 10g documentation mentions outbound_connect_timeout as a cman.ora parameter (Connection Manager parameter), not sqlnet.outbound_connect_timeout (sqlnet.ora param); but it's documented in 11g documentation. 11g has one more parameter tcp.connect_timeout, a sqlnet.ora parameter, that controls basic TCP timeout, as well as sqlnet.outbound_connect_timeout, which controls Oracle Net (SQL*Net) timeout. Tnsping seems to be Oracle Net oblivious, or partially so. Therefore only tcp.connect_timeout can shorten tnsping timeout. -------------------------------------------------------------------------------- * Why this experiment? If you want to set up failover between multiple addresses, the sooner the client connects to a failed-over host, the better. Suppose you have MyDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521)) (Failover=Yes) ) (CONNECT_DATA = (service_name = MyService)) ) when one of the two hosts is down, you want the client to connect to the other one in a very short time instead of 21 seconds. Note: If you use RAC and Oracle version is 10g, when VIP works properly, you don't need to tune the OS TCP timeout.