[20240917]tnsping诊断问题的局限性3.txt

1.补充测试:
--//在关闭数据库的情况下:
$ lsnrctl status
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 13-SEP-2024 08:50:56
Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                13-SEP-2024 08:41:06
Uptime                    0 days 0 hr. 9 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/centtest/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

d:\notes>tnsping 192.168.56.101:1521/xxxxxx
TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 13-SEP-2024 08:51:20
Copyright (c) 1997, 2021, Oracle.  All rights reserved.
Used parameter files:
D:\tools\app\Administrator\homes\OraDB21Home1\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=xxxxxx))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
OK (10 msec)
--//完全不存在的服务也是ok的.说明tnsping仅仅检查端口是否开放.

2.继续测试:
$ netstat -tnp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0     96 192.168.56.101:22       192.168.56.1:49674      ESTABLISHED -


d:\notes>tnsping 192.168.56.101 100
....

K (20 msec)
K (0 msec)
K (10 msec)
K (20 msec)
K (0 msec)
--//相当于瞬间发出100个tnsping.

$ netstat -tnpo | head
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name     Timer
tcp        0      0 192.168.56.101:1521     192.168.56.1:49794      TIME_WAIT   -                    timewait (58.11/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49823      TIME_WAIT   -                    timewait (58.31/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49776      TIME_WAIT   -                    timewait (57.95/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49848      TIME_WAIT   -                    timewait (58.55/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49832      TIME_WAIT   -                    timewait (58.41/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49822      TIME_WAIT   -                    timewait (58.30/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49841      TIME_WAIT   -                    timewait (58.52/0/0)
tcp        0      0 192.168.56.101:1521     192.168.56.1:49777      TIME_WAIT   -                    timewait (57.95/0/0)
--//tnsping相当于半连接，这样的缺点是执行完成在服务端的连接不会马上结束，出现timewait(缺省60秒关闭)

$ netstat -tnpo | grep 192.168.56.101:1521|wc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
    100     900   12200

--//你可以发现占用100个连接，即使完成断开也需要60秒释放连接(看最后一列的timewait，具体与内核一些参数有关或者sqlnet.ora配
--//置有关)。

# grep port /etc/sysctl.d/98-oracle.conf
net.ipv4.ip_local_port_range = 9000 65500

--//以上连接标识仅仅client端的端口号不同,连接最多65500-9000=56500,也就是最多56500个连接.
--//可以想象如果某个客户端tnsping发起56500个连接瞬间消耗连接数量,导致正常的登录操作无法连接.
--//这个在linux下很容易实现.
--//$ seq 1e8 | xargs -IQ -P 24 tnsping 192.168.56.101 10000

# sysctl -w 'net.ipv4.ip_local_port_range=9000 12500'
net.ipv4.ip_local_port_range = 9000 12500
--//为了加快测试减少端口范围。12500-9000 = 3500

D:\tmp\study> tnsping 192.168.56.101 14600
...

# seq 10 | xargs -IQ bash -c "netstat -tnpo|grep 192.168.56.101:1521|wc|ts.awk;sleep 1"
[2024-09-17 09:41:12]    3560   32040  434314
[2024-09-17 09:41:13]    3686   33174  449680
[2024-09-17 09:41:14]    3800   34200  463600
[2024-09-17 09:41:15]    3898   35082  475556
[2024-09-17 09:41:16]    4019   36171  490306
[2024-09-17 09:41:17]    4109   36981  501298
[2024-09-17 09:41:19]    4210   37890  513602
[2024-09-17 09:41:20]    4333   38997  528614
[2024-09-17 09:41:21]    4429   39861  540338
[2024-09-17 09:41:22]    4558   41022  556057

--//建立测试脚本t.bat
d:\tmp>cat t.bat
@echo off
D:\tools\linux\usr\local\wbin\date +"%%T.%%N" > t.txt
seq 2400 | xargs -P 24 -iQ sqlplus scott/book@book01p @ver1 > nul
D:\tools\linux\usr\local\wbin\date +"%%T.%%N" >> t.txt

d:\tmp>t

d:\tmp>cat t.txt
10:17:10.%N
10:17:28.%N
--//该版本的date不支持%N参数，基本需要18秒。

--//等待全部timewait消失。
# netstat -tnpo|grep 192.168.56.101:1521

d:\tmp>t

d:\tmp>t

d:\tmp>cat t.txt
10:20:02.%N
10:20:19.%N
--//没有测试出来.仅仅需要的时间少一些.

# sysctl  'net.ipv4.ip_local_port_range'
net.ipv4.ip_local_port_range = 9000     12500

# netstat -tnpo | egrep "Proto|3553"
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name     Timer
tcp        0      0 192.168.56.101:1521     192.168.56.1:49681      ESTABLISHED 3553/oraclebook      keepalive (7148.82/0/0)
--//噢，搞错了，这个是限制本机的192.168.56.101端口范围。

3.测试3：
--//在本机执行如下：
$ seq 1e8 | xargs -IQ -P 24 tnsping 192.168.56.101 10000
..
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
TNS-12545: Connect failed because target host or object does not exist
--//到一定时间可以发现如上内容。

# seq 10 | xargs -IQ bash -c "netstat -tnpo | egrep '192.168.56.101:1521     192.168.56.101:' |wc|ts.awk;sleep 1"
[2024-09-17 15:16:41]    2682   24138  326731
[2024-09-17 15:16:42]    2650   23850  322814
[2024-09-17 15:16:43]    2601   23409  316880
[2024-09-17 15:16:44]    2489   22401  303125
[2024-09-17 15:16:45]    2452   22068  298599
[2024-09-17 15:16:46]    2386   21474  290589
[2024-09-17 15:16:47]    2347   21123  285885
[2024-09-17 15:16:48]    2287   20583  278530
[2024-09-17 15:16:49]    2236   20124  272236
[2024-09-17 15:16:50]    2155   19395  262430

--//如果出现以上TNS-12545: Connect failed because target host or object does not exist错误时执行如下：
$ rlsql -s -l scott/book@192.168.56.101:1521/book01p
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//出现登录问题是间歇性，而且反而是netstat -tnpo | egrep '192.168.56.101:1521     192.168.56.101:' |wc最小的时候出现问题。

$ ls -l /u01/app/oracle/diag/tnslsnr/centtest/listener/alert
total 1667032
-rw-r-----. 1 oracle oinstall 314572975 2024-09-13 09:12:57 log_1.xml
-rw-r-----. 1 oracle oinstall 314572990 2024-09-13 09:18:23 log_2.xml
-rw-r-----. 1 oracle oinstall 314572890 2024-09-13 09:27:41 log_3.xml
-rw-r-----. 1 oracle oinstall 314572808 2024-09-17 10:17:02 log_4.xml
-rw-r-----. 1 oracle oinstall 314572912 2024-09-17 15:20:09 log_5.xml
-rw-r-----. 1 oracle oinstall 122336504 2024-09-17 15:30:51 log.xml
--//每个log都是300M。

$ grep  12545   log*.xml
--//没有任何记录，感觉应该有记录，至少我执行登录时报错。

4.附上ts.awk代码:

$ cat /usr/local/bin/ts.awk
# /bin/bash
gawk '{ print strftime("[%Y-%m-%d %H:%M:%S]"), $0 }'