[20221012]TNS-12543 TNSdestination host unreachable.txt
--//今天尝试本机连接测试库,出现如下问题.sqlplus报ORA-12543: TNS:destination host unreachable错误.
R:\>tnsping 78
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 09:37:50
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.78)(PORT=1521)))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TNS-12543: TNS:destination host unreachable
R:\>tnsping 192.168.100.78
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 09:38:12
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))
OK (20 msec)
--//如果我使用78时,oracle把它当作IP地址,变成了0.0.0.78.我检查tnsnames.ora文件,发现存在定义.
78 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(SDU = 32768)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = book)
)
)
--//尝试另外的tnsnames别名78x.
R:\>tnsping 78x
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 09:43:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book) (UR = A)))
OK (20 msec)
--//使用78x连接正常,仅仅有点慢.
--//问题在于我以前使用sqlplus连接是正常的,不会报错.而现在报错.
R:\>sqlplus -s -l scott/book@78
ERROR:
ORA-12543: TNS:destination host unreachable
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
--//使用Net Manager打开tnsnames.ora文件正常,说明文件没有问题.
--//进入E:\app\oracle\product\12.2.0\dbhome_1\network\admin目录观察,发现
E:\app\oracle\product\12.2.0\dbhome_1\network\admin>dir
驱动器 E 中的卷是 app
卷的序列号是 DACE-DD40
E:\app\oracle\product\12.2.0\dbhome_1\network\admin 的目录
2022/10/12 09:43 <DIR> .
2022/10/12 09:43 <DIR> ..
2020/03/11 17:30 733 listener.ora
2018/11/05 09:10 <DIR> sample
2022/09/02 09:43 571 sqlnet.ora
2020/03/11 17:30 19,311 tnsnames - 副本.ora
2021/05/19 10:36 19,788 tnsnames.ora
4 个文件 40,403 字节
3 个目录 36,502,573,056 可用字节
--//sqlnet.ora 2022/9/2修改过.
R:\>cat e:sqlnet.ora
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT,TNSNAMES,HOSTNAME)
#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on
#NAMES.DIRECTORY_PATH= (HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER
#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168
--//好像也没有怎么问题.难道我以前从来没有遇到过呢.改名sqlnet.ora为sqlnet.orax.
d:\notes>tnsping 78
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 10:04:32
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))) (SDU = 32768) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book)))
OK (20 msec)
--//说明sqlnet.ora文件修改存在问题.我重新使用Net Manager配置,文件保存后内容如下:
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on
#NAMES.DIRECTORY_PATH= (HOSTNAME)
#SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
#SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER
#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)
DIAG_ADR_ENABLED = OFF
--//问题依旧.我把NAMES.DIRECTORY_PATH顺序修改如下:
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT)
--//问题消失,这样优先选择TNSNAMES.实际上问题在于EZCONNECT写在前面相当于把78当作IP地址.
--//实际上我还发现如果修改如下
NAMES.DIRECTORY_PATH= (EZCONNECT , TNSNAMES, HOSTNAME)
--//注意EZCONNECT与逗号(,)之间存在空格.就可以通过.实际上这样EZCONNECT ,这样写错误的,oracle给配置埋了一个坑.
--//所以不建议维护时手工修改该文件,要注意一些细节.
--//我最终修改如下:
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on
#NAMES.DIRECTORY_PATH= (HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER
#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT)
#NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)
DIAG_ADR_ENABLED = OFF
--//如果你遇到上面的情况如何解决呢?很简单,你可以尝试在后面加入一个点(.),看看是否可以通过.测试如下:
--//设置NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)的情况下:
R:\>sqlplus -s -l scott/book@78 @ ver1
ERROR:
ORA-12543: TNS:destination host unreachable
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
R:\>sqlplus -s -l scott/book@78. @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
quit
--//最后还是建议还是EZCONNECT放在后面,不然连接确实有点慢.