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

[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 }'