[20221012]TNS-12543 TNSdestination host unreachable.txt

[20221012]TNS-12543 TNSdestination host unreachable.txt

--//今天嘗試本機連線測試庫,出現如下問題.sqlplus報ORA-12543: TNS:destination host unreachable錯誤.

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:\>tnsping 192.168.100.78
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 09:38:12
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=192.168.100.78)(PORT=1521)))
OK (20 msec)

--//如果我使用78時,oracle把它當作IP地址,變成了0.0.0.78.我檢查tnsnames.ora檔案,發現存在定義.
78 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
    )
    (SDU = 32768)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = book)
    )
  )

--//嘗試另外的tnsnames別名78x.
R:\>tnsping 78x
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 09:43:17
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 TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book) (UR = A)))
OK (20 msec)
--//使用78x連線正常,僅僅有點慢.
--//問題在於我以前使用sqlplus連線是正常的,不會報錯.而現在報錯.

R:\>sqlplus -s -l scott/book@78
ERROR:
ORA-12543: TNS:destination host unreachable
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//使用Net Manager開啟tnsnames.ora檔案正常,說明檔案沒有問題.

--//進入E:\app\oracle\product\12.2.0\dbhome_1\network\admin目錄觀察,發現
E:\app\oracle\product\12.2.0\dbhome_1\network\admin>dir
 驅動器 E 中的卷是 app
 卷的序列號是 DACE-DD40

 E:\app\oracle\product\12.2.0\dbhome_1\network\admin 的目錄

2022/10/12  09:43    <DIR>          .
2022/10/12  09:43    <DIR>          ..
2020/03/11  17:30               733 listener.ora
2018/11/05  09:10    <DIR>          sample
2022/09/02  09:43               571 sqlnet.ora
2020/03/11  17:30            19,311 tnsnames - 副本.ora
2021/05/19  10:36            19,788 tnsnames.ora
               4 個檔案         40,403 位元組
               3 個目錄 36,502,573,056 可用位元組

--//sqlnet.ora 2022/9/2修改過.
R:\>cat e:sqlnet.ora
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (EZCONNECT,TNSNAMES,HOSTNAME)

#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on

#NAMES.DIRECTORY_PATH= (HOSTNAME)

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER

#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168

--//好像也沒有怎麼問題.難道我以前從來沒有遇到過呢.改名sqlnet.ora為sqlnet.orax.

d:\notes>tnsping 78
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 12-OCT-2022 10:04:32
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))) (SDU = 32768) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = book)))
OK (20 msec)

--//說明sqlnet.ora檔案修改存在問題.我重新使用Net Manager配置,檔案儲存後內容如下:
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on
#NAMES.DIRECTORY_PATH= (HOSTNAME)
#SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
#SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER
#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)

DIAG_ADR_ENABLED = OFF

--//問題依舊.我把NAMES.DIRECTORY_PATH順序修改如下:
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT)

--//問題消失,這樣優先選擇TNSNAMES.實際上問題在於EZCONNECT寫在前面相當於把78當作IP地址.
--//實際上我還發現如果修改如下
NAMES.DIRECTORY_PATH= (EZCONNECT , TNSNAMES, HOSTNAME)

--//注意EZCONNECT與逗號(,)之間存在空格.就可以透過.實際上這樣EZCONNECT ,這樣寫錯誤的,oracle給配置埋了一個坑.
--//所以不建議維護時手工修改該檔案,要注意一些細節.
--//我最終修改如下:
# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

#SECURE_REGISTER_LISTENER= (TCP)
#USE_DEDICATED_SERVER=on
#NAMES.DIRECTORY_PATH= (HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
#SEC_USER_UNAUTHORIZED_ACCESS_BANNER
#DEFAULT_SDU_SIZE=65535
#SQLNET.ENCRYPTION_CLIENT = rejected
#SQLNET.ENCRYPTION_TYPES_CLIENT =3des168

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT)
#NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)

DIAG_ADR_ENABLED = OFF

--//如果你遇到上面的情況如何解決呢?很簡單,你可以嘗試在後面加入一個點(.),看看是否可以透過.測試如下:
--//設定NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES, HOSTNAME)的情況下:

R:\>sqlplus -s -l scott/book@78 @ ver1
ERROR:
ORA-12543: TNS:destination host unreachable
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

R:\>sqlplus -s -l scott/book@78. @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
quit

--//最後還是建議還是EZCONNECT放在後面,不然連線確實有點慢.