Oracle-12c
連接遠端Oracle數據庫時如何解決供應商程式碼17002？
我有一個在 AWS ec2 的 Linux 實例上執行的 Oracle 數據庫 12c 標準版版本 12.2.0.1.0。當我嘗試使用 SQL Developer 從我的機器 (macOS) 遠端連接到它時，我收到一個
IO Error: The Network Adapter could not establish the connectionVendor Code 17002錯誤。
- 在我的機器上使用 sqlplus 進行連接：
sqlplus cd2/ @ec2-publicIp.myCloud.compute.amazonaws.com:1522/orcl SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 3 15:30:56 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-12541: TNS:no listener
- 在 ec2 實例 Security Groups 上，為我的機器的公共 IP 打開了一個 TCP 1522 埠
- telnet 到實例，埠 1522 似乎表明該埠是開放的：
telnet ec2-pubIp.compute.amazonaws.com 1522 Trying pubIp... telnet: connect to address pubIp: Connection refused telnet: Unable to connect to remote host
- 埠 1522 在所有配置中一致使用
- 我驗證了 ec2 主機名（
hostname命令）與從lsnrctl status. 輸出顯示localhost而不是ip-localIp.myCloud.compute.internal。$ hostname ip-localIp.myCloud.compute.internal$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-AUG-2020 15:20:44 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-localIp.myCloud.compute.internal)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 03-AUG-2020 13:30:22 Uptime 0 days 1 hr. 50 min. 22 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/centos/diag/tnslsnr/ip-localIp/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Services Summary... Service "orcl.myCloud.compute.internal" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB.myCloud.compute.internal" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
- 主機名和埠也與配置的預設偵聽器匹配
listener.ora：cat /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ip-localIp.myCloud.compute.internal)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) ) )
- Oracle 參數如圖所示，主機和埠再次匹配：
SQL> show parameter listener_networks NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST = ip-localIp.myCloud .compute.internal)(PORT = 1522 )) SQL> show parameter remote_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener string
- 我可以從 ec2 實例連接：
sqlplus / as sysdba- 我可以從 ec2 實例作為模式/使用者 cd2 連接：
sqlplus cd2/ @orcl SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 3 18:04:13 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Last Successful login time: Mon Aug 03 2020 18:03:52 +00:00 Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production SP2-0310: unable to open file "orcl.sql" SQL> select 1 from dual; 1 ---------- 1 SQL>
- SQL Developer 連接匹配埠和 ec2 的公共域名：
- 這個數據庫伺服器是我們的開發實例，所以沒有安裝許可證
listener.ora通過將HOST值設置為更新0.0.0.0，重新啟動lsnrctl但結果相同：LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) ) )
/etc/hosts內容：127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-localIp.myCloud.compute.internal ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
- 我還使用所有入站流量和為 ec2 實例打開的所有埠對其進行了測試
不，以下內容並不表示您可以通過該埠訪問伺服器：
telnet ec2-pubIp.compute.amazonaws.com 1522 Trying pubIp... telnet: connect to address pubIp: Connection refused telnet: Unable to connect to remote host這只是一個通用的網路問題。只需在連接詳細資訊中輸入錯誤的主機或埠，即可隨時重現 17002 錯誤。
您的偵聽器偵聽內部地址，在您的情況下是環回適配器的 127.0.0.1 地址。但是您嘗試通過外部地址進行連接。
您的雲基礎架構中需要一些轉發/防火牆規則。或者您需要更改偵聽器配置，以便它可以偵聽外部地址。但即使你這樣做，如果沒有上述內容，它也可能無法工作。
更好的選擇是將連接封裝在 SSH 隧道中。您可以通過首選的 SSH 客戶端設置隧道（這適用於 Oracle Cloud，但概念與創建 SSH 隧道到計算節點埠的概念相同）。甚至 SQL Developer 也為此提供了一些內置功能：使用 Oracle SQL Developer 遠端連接到數據庫。
或者這裡有一篇關於在 SQL Developer 中設置 SSH 隧道的部落格文章，帶有截圖：https ://learncodeshare.net/2016/06/02/sql-developer-oracle-database-connection-through-an-ssh-tunnel/