當偵聽器設置為主機 IP 地址時，與 Oracle DB 19c 的連接失敗

我是 Oracle DB 的新手，需要幫助解決以下問題。

我正在使用sqlplus (SQL*Plus: Version 21.1.0.0.0) 連接到Oracle DB 19c。

**聲明：**當我將文件中的 IP 更改為“ ”時，我可以從192.168.0.109主機（如下：192.168.0.108``192.168.0.106``0.0.0.0``localhost``listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

**面臨的問題：**當我將文件中的 IP 更改為主機 IP ( )時，無法從 VM ( 192.168.0.108) 或 LAN ( ) 中的其他系統進行連接。請在下面找到修改：192.168.0.106``192.168.0.109``listener.ora``listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.109)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

錯誤詳情： sqlplus工具報錯如下：

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

現在，讓我也分享一下tnsnames.ora文件的內容（如果需要）：

LISTENER_POLA =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
   (CONNECT_DATA =
     (SID = CLRExtProc)
     (PRESENTATION = RO)
   )
 )

POLA =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = pola)
   )
 )

更多細節：

1. Oracle DB 所在的主機 (192.168.0.109) 可從包括 VM 在內的所有其他系統訪問
2. telnet 192.168.0.109 1521所有系統都顯示它已連接。
3. VM 在 Oracle Virtual Box 6.1 上執行並配置為橋接模式。
4. Oracle DB 19c 和 VM 在 Windows Server 2016 之上執行。

查詢： 當listener.ora文件配置了主機系統的IP地址（192.168.0.109）時，我需要做什麼配置才能讓sqlplus可以從同一區域網路內的任何系統連接到Oracle DB？

LSNRCTL 狀態的輸出：

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 16-MAR-2021 23:18:31

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.109)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                16-MAR-2021 23:16:07
Uptime                    0 days 0 hr. 2 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Polarbear\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Polarbear\diag\tnslsnr\WIN-4SO5O8T8UJ6\listener\alert\log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.109)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
 Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

命令 sqlplus / as sysdba 的輸出：

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 17 00:00:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select count(*) from dba_tables;

 COUNT(*)
----------
     2182

也許在你的 listener.ora 中添加這樣的東西：

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = POLA)
     (ORACLE_HOME= C:\Polarbear\WINDOWS.X64_193000_db_home)
     (SID_NAME = POLA)
   )
 )

您可以執行此查詢來驗證您的全域名稱：

select global_name from global_name;

SID 應該是

select INSTANCE_NAME from v$instance;

鮑比

引用自：https://dba.stackexchange.com/questions/287138