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

[20240912]tnsping诊断问题的局限性.txt --//tnsping用来检测数据库是否连接存在许多局限性,更多检查端口是否开放,甚至别的工具也许更好. --//通过1个简单的例子说明问题: 1.环境: SYS@book> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. --//关闭数据库开启监听. SYS@book> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. --//服务端监听配置如下:  $ cat  /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))     )   ) --//客户端windwos机器配置如下: # tnsnames.ora Network Configuration File: D:\tools\app\Administrator\homes\OraDB21Home1\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. BOOK =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = book)     )   ) BOOK01P =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = book01p)     )   ) 2.测试1: --//数据库没有开启: $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:35:00 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                12-SEP-2024 08:47:48 Uptime                    0 days 1 hr. 47 min. 11 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:\tmp>tnsping book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:35:52 Copyright (c) 1997, 2021, Oracle.  All rights reserved. Used parameter files: D:\tools\app\Administrator\homes\OraDB21Home1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = book01p))) OK (0 msec) d:\tmp>tnsping book TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:40:06 Copyright (c) 1997, 2021, Oracle.  All rights reserved. Used parameter files: D:\tools\app\Administrator\homes\OraDB21Home1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book))) OK (20 msec) --//我监听数据库没有启动,服务也没有开启,tnsping 报2个连接串连接正常. --//不管数据库是否正常运行，只要监听存在并在运行，tnsping都可以成功解析！ 3.测试2: --//修改服务端监听端口: $ lsnrctl stop LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:46:00 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521))) The command completed successfully $ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1523))       (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))     )   ) $ lsnrctl start LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:46:42 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Starting /u01/app/oracle/product/21.0.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 21.0.0.0.0 - Production System parameter file is /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/centtest/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1523))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date                12-SEP-2024 10:46:42 Uptime                    0 days 0 hr. 0 min. 0 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=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) The listener supports no services The command completed successfully d:\tmp>tnsping book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:48:58 Copyright (c) 1997, 2021, Oracle.  All rights reserved. Used parameter files: D:\tools\app\Administrator\homes\OraDB21Home1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = book01p))) TNS-12541: TNS:no listener --//检测到端口1521没有打开。 --//可以采用如下格式验证端口是否开放: d:\tmp>tnsping 192.168.56.101:1523 TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 11:01:06 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=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1523))) OK (0 msec) d:\tmp>tnsping 192.168.56.101:1523/book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 11:05:25 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=book01p))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1523))) OK (0 msec) --//注意看(SERVICE_NAME=book01p),说明不能使用tnsping检测服务数据库服务是否连接正常. 4.继续测试: --//补充修改监听端口后,数据库做那些修改: SYS@book> startup ORACLE instance started. Total System Global Area  805306280 bytes Fixed Size                  9691048 bytes Variable Size             473956352 bytes Database Buffers          314572800 bytes Redo Buffers                7086080 bytes Database mounted. Database opened. SYS@book> alter system  register; System altered. $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:53:58 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date                12-SEP-2024 10:46:42 Uptime                    0 days 0 hr. 7 min. 16 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=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) The listener supports no services The command completed successfully --//无法注册数据库对应服务... SYS@book> show parameter local_listener PARAMETER_NAME TYPE    VALUE -------------- ------- -------------- local_listener string  LISTENER_BOOK --//在linux配置修改如下 $ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. BOOK =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = book)     )   ) BOOK01P =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = book01p)     )   ) LISTENER_BOOK =   (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//必须手工做如下操作,才能注册对应服务. SYS@book> alter system set local_listener='LISTENER_BOOK'; System altered. SYS@book> alter system  register; System altered. $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 10:59:56 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date                12-SEP-2024 10:46:42 Uptime                    0 days 0 hr. 13 min. 14 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=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) Services Summary... Service "1f36f47256d41c08e0636538a8c03260" has 1 instance(s).   Instance "book", status READY, has 1 handler(s) for this service... Service "book" has 1 instance(s).   Instance "book", status READY, has 1 handler(s) for this service... Service "book01p" has 1 instance(s).   Instance "book", status READY, has 1 handler(s) for this service... Service "bookXDB" has 1 instance(s).   Instance "book", status READY, has 1 handler(s) for this service... Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).   Instance "book", status READY, has 1 handler(s) for this service... The command completed successfully --//现在可以使用1523端口连接数据库.其他连接串修改略. --//测试还原略. 5.简单总结: --//只能使用tnsping做简单判断端口是否打开的判断,存在局限性.