邏輯存儲結構 邏輯存儲結構圖 表空間 表空間文件在Linux下存放在 /var/lib/mysql文件中的 xxx.ibd 文件就是表空間文件 表空間文件用來存儲，記錄，索引等數據。 段 段分為，數據段(Leaf node segment) ，索引段(Non-leaf node segment)，回 ...
[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解析.
