Connection to Oracle DB 19c fails when listener is set to the host IP address

Question

I am new to Oracle DB and need help in solving the following issue.

I am using sqlplus (SQL*Plus: Version 21.1.0.0.0) to connect to the Oracle DB 19c.

Statement: I am able to connect to the Oracle DB from the host (192.168.0.109), VM running on top of the host (192.168.0.108), and also from other system within the same LAN (192.168.0.106) when I change the IP to 0.0.0.0 from 'localhost' in the listener.ora file as below:

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

Issue faced: Cannot connect from the VM (192.168.0.108) or from the other systems within the LAN (192.168.0.106) when I change the IP to the host IP (192.168.0.109) in the listener.ora file. Please find the modified listener.ora below:

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

Error Details: The error reported by sqlplus tool is as follows:

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

Now, let me share the content of the tnsnames.ora file too (if required):

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)
    )
  )

Some more details:

1. The host on which the Oracle DB is residing (192.168.0.109) is reachable from all the other systems including the VM
2. telnet 192.168.0.109 1521 from all the systems is showing it's connected.
3. The VM is running on Oracle Virtual Box 6.1 and is configured in Bridge mode.
4. The Oracle DB 19c and the VM is running on top of Windows Server 2016.

Query: What configuration do I need to do so that sqlplus can connect to the Oracle DB from any system within the same LAN when the listener.ora file is configured with the IP address of the host system (192.168.0.109)?

Output of LSNRCTL status:

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

Output of the command 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

Answer 1

Maybe add something like this to your listener.ora:

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

You might run this query to verify your global name:

select global_name from global_name;

SID should be

select INSTANCE_NAME from v$instance;

Bobby