[20221014]TNS-12543 TNSdestination host unreachable(补充).txt

–//前几天遇到的问题:

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:apporacleproduct12.2.0dbhome_1NETWORKADMINsqlnet.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:>sqlplus -s -l scott/book@78

ERROR:

ORA-12543: TNS:destination host unreachable

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

–//出现问题时,脑子一篇混乱,冷静下来仔细理清楚.以前使用的好好的连接串,为什么现在出现问题呢?

–//当看到HOST=0.0.0.78的解析,自己马上想到tnsping 将它解析为IP地址.

–//另外注意看++++的内容,也解析了使用Used EZCONNECT adapter to resolve the alias.

–//192*256*256*256+168*256*256+100*256+78=3232261198,执行如下,也可以验证:

SCOTT@book> @ calcx 192*256*256*256+168*256*256+100*256+78

                                DEC                  HEX

———————————– ——————–

                  3232261198.000000 00000000c0a8644e

R:>tnsping 3232261198

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 – Production on 14-OCT-2022 08:49:00

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:

E:apporacleproduct12.2.0dbhome_1NETWORKADMINsqlnet.ora

Used HOSTNAME 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)

R:>tnsping 0x0c0a8644e

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 – Production on 14-OCT-2022 08:49:29

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:

E:apporacleproduct12.2.0dbhome_1NETWORKADMINsqlnet.ora

Used HOSTNAME 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)

–//后面检查发现本机的sqlnet.ora 9/2号修改过,我尝试做一些注解,发现如下行存在问题:

NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)

–//这里还有1个小插曲,我在每个逗号的前面加入空格,居然通过了,当时脑子确实有点乱,当时还怀疑tnsnames.ora文件格式存在问题.

–//后面我还是看了别的服务器设置,才想到尝试将EZCONNECT放在最后.

–//实际上仔细想想就很容易明白,解析按照EZCONNECT, TNSNAMES, HOSTNAME顺序来解析.

–// 78 数字符合IP地址格式,结果被当成IP= 0.0.0.78解析,自然出现TNS-12543: TNS:destination host unreachable.

–//但是为什么执行tnsping 78x 时会很慢并且通过呢?(当时我的主要精力并没有放在这里),实际上oracle先是把它当作主机名,通过DNS

–//查询转换为IP,这个过程是失败的,接下来才是才是按照TNSNAMES的解析,也就是配置NAMES.DIRECTORY_PATH时不要把EZCONNECT最前面.

–//使用sysinternals自带的工具Procmon.exe跟踪tnsping,可以发现如下内容:

“Time of Day”,”Process Name”,”PID”,”Operation”,”Path”,”Result”,”Detail”

“9:01:28.6241938″,”tnsping.exe”,”6164″,”RegQueryKey”,”HKLM”,”SUCCESS”,”Query: HandleTags, HandleTags: 0x0″

“9:01:28.6242108″,”tnsping.exe”,”6164″,”RegOpenKey”,”HKLMSoftwareMicrosoftIdentityCRL”,”SUCCESS”,”Desired Access: Query Value”

“9:01:28.6242689″,”tnsping.exe”,”6164″,”RegQueryValue”,”HKLMSOFTWAREMicrosoftIdentityCRLDeviceDNSSuffix”,”SUCCESS”,”Type: REG_SZ, Length: 40, Data: .devicedns.live.com”

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

“9:01:28.6243067″,”tnsping.exe”,”6164″,”RegCloseKey”,”HKLMSOFTWAREMicrosoftIdentityCRL”,”SUCCESS”,””

“9:01:28.9240685″,”tnsping.exe”,”6164″,”RegQueryKey”,”HKLM”,”SUCCESS”,”Query: HandleTags, HandleTags: 0x0″

“9:01:28.9240884″,”tnsping.exe”,”6164″,”RegOpenKey”,”HKLMSYSTEMCurrentControlSetServicesNetBTLinkage”,”REPARSE”,”Desired Access: Read”

“9:01:28.9242478″,”tnsping.exe”,”6164″,”RegOpenKey”,”HKLMSystemCurrentControlSetServicesNetBTLinkage”,”SUCCESS”,”Desired Access: Read”

“9:01:28.9242901″,”tnsping.exe”,”6164″,”RegQueryValue”,”HKLMSystemCurrentControlSetservicesNetBTLinkageExport”,”BUFFER OVERFLOW”,”Length: 144″

“9:01:28.9243289″,”tnsping.exe”,”6164″,”RegQueryValue”,”HKLMSystemCurrentControlSetservicesNetBTLinkageExport”,”BUFFER OVERFLOW”,”Length: 144″

“9:01:28.9243604″,”tnsping.exe”,”6164″,”RegQueryValue”,”HKLMSystemCurrentControlSetservicesNetBTLinkageExport”,”SUCCESS”,”Type: REG_MULTI_SZ, Length: 718, Data: DeviceNetBT_Tcpip_{123FAAA1-2325-4124-A876-14547DE0D200}, DeviceNetBT_Tcpip_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}, DeviceNetBT_Tcpip6_{ACC69FC8-61C7-4A75-BD6C-4C509771D463}, DeviceNetBT_Tcpip6_{123FAAA1-2325-4124-A876-14547DE0D200}, DeviceNetBT_Tcpip6_{A6A5EA3E-A54E-4AE1-8023-C5025B95CC8E}, DeviceNetBT_Tcpip6_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}”

“9:01:28.9243956″,”tnsping.exe”,”6164″,”RegCloseKey”,”HKLMSystemCurrentControlSetservicesNetBTLinkage”,”SUCCESS”,””

“9:01:31.1748622″,”tnsping.exe”,”6164″,”QueryOpen”,”R: nsnames.ora”,”NAME NOT FOUND”,””

“9:01:31.1750027″,”tnsping.exe”,”6164″,”QueryOpen”,”E:apporacleproduct12.2.0dbhome_1 etworkadmin nsnames.ora”,”SUCCESS”,”CreationTime: 2018/11/5 10:12:42, LastAccessTime: 2022/10/14 9:01:19, LastWriteTime: 2021/5/19 10:36:19, ChangeTime: 2021/5/19 10:36:19, AllocationSize: 20,480, EndOfFile: 19,788, FileAttributes: A”

“9:01:31.1753375″,”tnsping.exe”,”6164″,”CreateFile”,”C:WindowsSystem32 zres.dll”,”SUCCESS”,”Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened”

–//至于想到在78后面加入一个点,纯粹是灵光一现,实际上连接是很慢的,绕不开DNS解析.