[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解析.