[20221014]TNS-12543 TNSdestination host unreachable(补充).txt
[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:\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:\>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:\app\oracle\product\12.2.0\dbhome_1\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.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:\app\oracle\product\12.2.0\dbhome_1\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.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","HKLM\Software\Microsoft\IdentityCRL","SUCCESS","Desired Access: Query Value"
"9:01:28.6242689","tnsping.exe","6164","RegQueryValue","HKLM\SOFTWARE\Microsoft\IdentityCRL\DeviceDNSSuffix","SUCCESS","Type: REG_SZ, Length: 40, Data: .devicedns.live.com"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"9:01:28.6243067","tnsping.exe","6164","RegCloseKey","HKLM\SOFTWARE\Microsoft\IdentityCRL","SUCCESS",""
"9:01:28.9240685","tnsping.exe","6164","RegQueryKey","HKLM","SUCCESS","Query: HandleTags, HandleTags: 0x0"
"9:01:28.9240884","tnsping.exe","6164","RegOpenKey","HKLM\SYSTEM\CurrentControlSet\Services\NetBT\Linkage","REPARSE","Desired Access: Read"
"9:01:28.9242478","tnsping.exe","6164","RegOpenKey","HKLM\System\CurrentControlSet\Services\NetBT\Linkage","SUCCESS","Desired Access: Read"
"9:01:28.9242901","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","BUFFER OVERFLOW","Length: 144"
"9:01:28.9243289","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","BUFFER OVERFLOW","Length: 144"
"9:01:28.9243604","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","SUCCESS","Type: REG_MULTI_SZ, Length: 718, Data: \Device\NetBT_Tcpip_{123FAAA1-2325-4124-A876-14547DE0D200}, \Device\NetBT_Tcpip_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}, \Device\NetBT_Tcpip6_{ACC69FC8-61C7-4A75-BD6C-4C509771D463}, \Device\NetBT_Tcpip6_{123FAAA1-2325-4124-A876-14547DE0D200}, \Device\NetBT_Tcpip6_{A6A5EA3E-A54E-4AE1-8023-C5025B95CC8E}, \Device\NetBT_Tcpip6_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}"
"9:01:28.9243956","tnsping.exe","6164","RegCloseKey","HKLM\System\CurrentControlSet\services\NetBT\Linkage","SUCCESS",""
"9:01:31.1748622","tnsping.exe","6164","QueryOpen","R:\tnsnames.ora","NAME NOT FOUND",""
"9:01:31.1750027","tnsping.exe","6164","QueryOpen","E:\app\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.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:\Windows\System32\tzres.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解析.
![]( "轻灵划译")
--//前几天遇到的问题:
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:\>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:\app\oracle\product\12.2.0\dbhome_1\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.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:\app\oracle\product\12.2.0\dbhome_1\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.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","HKLM\Software\Microsoft\IdentityCRL","SUCCESS","Desired Access: Query Value"
"9:01:28.6242689","tnsping.exe","6164","RegQueryValue","HKLM\SOFTWARE\Microsoft\IdentityCRL\DeviceDNSSuffix","SUCCESS","Type: REG_SZ, Length: 40, Data: .devicedns.live.com"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"9:01:28.6243067","tnsping.exe","6164","RegCloseKey","HKLM\SOFTWARE\Microsoft\IdentityCRL","SUCCESS",""
"9:01:28.9240685","tnsping.exe","6164","RegQueryKey","HKLM","SUCCESS","Query: HandleTags, HandleTags: 0x0"
"9:01:28.9240884","tnsping.exe","6164","RegOpenKey","HKLM\SYSTEM\CurrentControlSet\Services\NetBT\Linkage","REPARSE","Desired Access: Read"
"9:01:28.9242478","tnsping.exe","6164","RegOpenKey","HKLM\System\CurrentControlSet\Services\NetBT\Linkage","SUCCESS","Desired Access: Read"
"9:01:28.9242901","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","BUFFER OVERFLOW","Length: 144"
"9:01:28.9243289","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","BUFFER OVERFLOW","Length: 144"
"9:01:28.9243604","tnsping.exe","6164","RegQueryValue","HKLM\System\CurrentControlSet\services\NetBT\Linkage\Export","SUCCESS","Type: REG_MULTI_SZ, Length: 718, Data: \Device\NetBT_Tcpip_{123FAAA1-2325-4124-A876-14547DE0D200}, \Device\NetBT_Tcpip_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}, \Device\NetBT_Tcpip6_{ACC69FC8-61C7-4A75-BD6C-4C509771D463}, \Device\NetBT_Tcpip6_{123FAAA1-2325-4124-A876-14547DE0D200}, \Device\NetBT_Tcpip6_{A6A5EA3E-A54E-4AE1-8023-C5025B95CC8E}, \Device\NetBT_Tcpip6_{7F2CB894-68E0-47D1-9114-7F8A8EC93F66}"
"9:01:28.9243956","tnsping.exe","6164","RegCloseKey","HKLM\System\CurrentControlSet\services\NetBT\Linkage","SUCCESS",""
"9:01:31.1748622","tnsping.exe","6164","QueryOpen","R:\tnsnames.ora","NAME NOT FOUND",""
"9:01:31.1750027","tnsping.exe","6164","QueryOpen","E:\app\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.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:\Windows\System32\tzres.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解析.
