[20240912]记录使用tnsping遇到的问题.txt

[20240912]记录使用tnsping遇到的问题.txt --//tnsping用来检测数据库是否连接存在许多局限性,记录自己在使用tnsping遇到的问题. 1.环境: --//关闭数据库开启监听. 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))     )   ) $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 11:59:55 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 11:16:10 Uptime                    0 days 0 hr. 43 min. 45 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 2.测试1: d:\tmp>tnsping 192.168.56.101/book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:01:51 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=1521))) OK (10 msec) --//ok,没有打开数据库,开启服务,但是tnsping测试通过,说明tnsping仅仅用来检查端口是否开放. 3.测试2: --//修改服务端监听端口1523: $ cat 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 stop LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:04:55 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 111: Connection refused $ lsnrctl start LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:05:02 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 12:05:03 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 192.168.56.101/book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:05:29 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=1521))) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OK (10 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 12:05:35 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) --//视乎好像给人错误的感觉,这样设置1521,1523端口都是开放的,如果数据库是打开的,1521端口可以连接数据库.可以自行测试. --//实际上前面的修改我是先修改监听配置文件,然后再做停止监听操作. --//实际上lsnrctl stop是报错的.看前面操作. --//如果使用netstat -tnlp查询: # netstat -tnlp | egrep "Proto|tnslsnr" Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN      10792/tnslsnr tcp        0      0 192.168.56.101:1521     0.0.0.0:*               LISTEN      10792/tnslsnr tcp        0      0 127.0.0.1:1523          0.0.0.0:*               LISTEN      12051/tnslsnr tcp        0      0 192.168.56.101:1523     0.0.0.0:*               LISTEN      12051/tnslsnr --//可以发现1521,1523端口是开放的. $ lsnrctl stop LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:11:21 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) The command completed successfully $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:11:55 Copyright (c) 1991, 2021, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523))) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 2: No such file or directory d:\tmp>tnsping 192.168.56.101/book01p TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:11:39 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=1521))) OK (30 msec) --//我停止监听,但是1521端口还是打开的. --//上面修改正确的操作是先lsnrctl stop ,然后修改监听配置文件就不会出现这样的情况了. --//现在反向演示看看. --//修改服务端监听端口为1521: $ cat 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))     )   ) $ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:14:35 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 11:16:10 Uptime                    0 days 0 hr. 58 min. 25 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 --//现在显示lsnrctl status可以发现1521端口. $ lsnrctl stop LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 12-SEP-2024 12:16:38 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 # netstat -tnlp | egrep "Proto|tnslsnr" Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name 4.总结: --//这类错误在工作中很常见,以后要注意.要注意一些细节,就能避免这类的奇怪错误发生.