[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做简单判断端口是否打开的判断,存在局限性.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3027615/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
