[20180812]TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT.txt

[20180812]TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT.txt

https://blog.dbi-services.com/transport_connect_timeout-and-retry_count/
--//重複測試:

TRANSPORT_CONNECT_TIMEOUT

Now, just adding the TRANSPORT_CONNECT_TIMEOUT to the connection string description to reduce the timout to N seconds:

RETRY_COUNT

If you lower the timeout, you may give a chance to retry a few times with RETRY_COUNT. There, RETRY_COUNT=2 will give 3
attempts ( 1 + 2 retries ) to the address list:

0.建立測試連線配置:
# cat /tmp/tnsnames.ora
DEFAULT=
 (DESCRIPTION=
  (CONNECT_DATA=(SERVICE_NAME=pdb1))
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
  )
 )

DESCRIPTION=
 (DESCRIPTION=
  (CONNECT_DATA=(SERVICE_NAME=pdb1))
  (TRANSPORT_CONNECT_TIMEOUT=4)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
  )
 )

RETRY_COUNT=
 (DESCRIPTION=
  (CONNECT_DATA=(SERVICE_NAME=pdb1))
  (TRANSPORT_CONNECT_TIMEOUT=4)
  (RETRY_COUNT=2)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
   (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
  )
 )

INCREASING=
 (DESCRIPTION_LIST=
  (LOAD_BALANCE=off)
  (DESCRIPTION=
   (CONNECT_DATA=(SERVICE_NAME=pdb1))
   (TRANSPORT_CONNECT_TIMEOUT=1)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
   )
  )
  (DESCRIPTION=
   (CONNECT_DATA=(SERVICE_NAME=pdb1))
   (TRANSPORT_CONNECT_TIMEOUT=5)
   (RETRY_COUNT=1)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
   )
  )
  (DESCRIPTION=
   (CONNECT_DATA=(SERVICE_NAME=pdb1))
   (TRANSPORT_CONNECT_TIMEOUT=2)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))
   )
  )
 )
--//原來還可以這樣寫.

$ export TNS_ADMIN=/tmp

1.測試1:
$ time tnsping DEFAULT
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-AUG-2018 08:47:16
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SERVICE_NAME=pdb1)) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))))
TNS-12535: TNS:operation timed out
real    2m0.136s
user    0m0.007s
sys     0m0.009s


2.測試2:
$ time tnsping DESCRIPTION
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-AUG-2018 08:46:20
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SERVICE_NAME=pdb1)) (TRANSPORT_CONNECT_TIMEOUT=4) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))))
TNS-12535: TNS:operation timed out
real    0m8.025s
user    0m0.008s
sys     0m0.009s

3.測試3:
$ time tnsping RETRY_COUNT

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-AUG-2018 08:48:56

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SERVICE_NAME=pdb1)) (TRANSPORT_CONNECT_TIMEOUT=4) (RETRY_COUNT=2) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))))
TNS-12535: TNS:operation timed out

real    0m24.041s
user    0m0.005s
sys     0m0.011s

4.測試4:
$ strace -tT tnsping INCREASING 2>&1 | grep -C1 --color=auto -E 'poll.*|inet_addr[()".0-9]*'
08:51:55 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000020>
08:51:55 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.10")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000085>
08:51:55 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869488693 <0.000020>
08:51:55 mmap(NULL, 528384, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f7279d2b000 <0.000026>
08:51:55 poll([{fd=4, events=POLLOUT}], 1, 1000) = 0 (Timeout) <1.001114>
08:51:56 close(4)                       = 0 <0.000045>
--
08:51:56 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000017>
08:51:56 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.11")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000040>
08:51:56 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869488793 <0.000021>
08:51:56 poll([{fd=4, events=POLLOUT}], 1, 1000) = 0 (Timeout) <1.001232>
08:51:57 close(4)                       = 0 <0.000036>
--
08:51:57 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000016>
08:51:57 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.10")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000036>
08:51:57 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869488893 <0.000017>
08:51:57 poll([{fd=4, events=POLLOUT}], 1, 5000) = 0 (Timeout) <5.005381>
08:52:02 close(4)                       = 0 <0.000084>
--
08:52:02 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000017>
08:52:02 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.11")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000039>
08:52:02 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869489394 <0.000016>
08:52:02 poll([{fd=4, events=POLLOUT}], 1, 5000) = 0 (Timeout) <5.005130>
08:52:07 close(4)                       = 0 <0.000039>
--
08:52:07 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000018>
08:52:07 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.10")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000044>
08:52:07 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869489894 <0.000023>
08:52:07 poll([{fd=4, events=POLLOUT}], 1, 5000) = 0 (Timeout) <5.005088>
08:52:12 close(4)                       = 0 <0.000088>
--
08:52:12 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000013>
08:52:12 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.11")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000038>
08:52:12 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869490395 <0.000017>
08:52:12 poll([{fd=4, events=POLLOUT}], 1, 5000) = 0 (Timeout) <5.005265>
08:52:17 close(4)                       = 0 <0.000038>
--
08:52:17 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000017>
08:52:17 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.10")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000036>
08:52:17 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869490895 <0.000017>
08:52:17 poll([{fd=4, events=POLLOUT}], 1, 2000) = 0 (Timeout) <2.002116>
08:52:19 close(4)                       = 0 <0.000037>
--
08:52:19 fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0 <0.000017>
08:52:19 connect(4, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("10.10.10.11")}, 16) = -1 EINPROGRESS (Operation now in progress) <0.000036>
08:52:19 times({tms_utime=0, tms_stime=0, tms_cutime=0, tms_cstime=0}) = 8869491096 <0.000016>
08:52:19 poll([{fd=4, events=POLLOUT}], 1, 2000) = 0 (Timeout) <2.002223>
08:52:21 close(4)                       = 0 <0.000037>

--//注意看poll那行,時間間隔:1,1,5,5,5,5,2,2.
--//另外我在windows下測試,無論如何測試間隔都是42秒,視乎這些引數在windows下無效.使用12c.
--//附上windows測試的批處理指令碼:

D:\tools\linux\usr\local\wbin\date
tnsping %1%
D:\tools\linux\usr\local\wbin\date


d:\>cc DESCRIPTION

d:\temp\cc>D:\tools\linux\usr\local\wbin\date
Mon Aug 13 20:37:59 中國標準時間 2018

d:\>tnsping DESCRIPTION

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 13-AUG-2018 20:37:59

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
D:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SERVICE_NAME=pdb1)) (TRANSPORT_CONNECT_TIMEOUT=4) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)) (ADDRESS=(PROTOCOL=T
CP)(HOST=10.10.10.11)(PORT=1521))))