[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.总结:
--//这类错误在工作中很常见,以后要注意.要注意一些细节,就能避免这类的奇怪错误发生.