[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解析.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2918297/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20221012]TNS-12543 TNSdestination host unreachable.txt
- 問題解決:TNS-12543: TNS:destination host unreachable
- Unable to execute SonarScanner analysis: Fail to get bootstrap index from server: Host is unreachable(Host unreachable)AIbootIndexServer
- [20180928]ora-01426(補充).txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20220603]測試quiz night(補充).txtUI
- [20211215]提示precompute_subquery補充.txt
- [20211116]plsql_code_type=native補充.txtSQL
- [20221014]資料檔案2的小疑問.txt
- [20210803]使用那個shared pool latch(補充).txt
- [20211013]測試遠端監聽補充.txt
- [20211221]提示precompute_subquery補充2.txt
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20211025]12c sequence nocache測試補充.txt
- [20220120]超長sql語句補充4.txtSQL
- [20220119]超長sql語句補充3.txtSQL
- [20180129]簡單探究cluster table(補充)4.txt
- [20181229]簡單探究cluster table(補充)3.txt
- [20181227]簡單探究cluster table(補充)2.txt
- [20211021]windows新建文字檔案帶日期(補充).txtWindows
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20200414]Linux下快速刪除大量檔案(補充).txtLinux
- [20201208]為什麼返回2行記錄補充.txt
- [20190214]11g Query Result Cache RC Latches補充.txt
- [20190211]簡單測試埠是否開啟(補充).txt
- 能ping通虛擬機器,但snmp報文 Destination unreachable(Host administratively prohibited虛擬機
- [20211009]8K資料庫最大行號補充.txt資料庫
- [20201116]測試CURSOR_SPACE_FOR_TIME(10g)(補充).txt
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20210708]find -mtime +0 0 -0時間問題補充.txt
- [20210626]find -mtime +N N -N時間問題補充.txt
- [20210625]find -mtime +N N -N時間問題補充.txt
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20190312]關於增量檢查點的疑問(補充).txt
- [20230227]探究v$session.SQL_EXEC_ID在共享池(補充).txtSessionSQL
- [20181030]避免表示式在sql語句中(10g)(補充).txtSQL