0

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
5
  • ORA-12514 means that you connected successfully to the listener but that the service that you requested is unknown by the listener. If you run lsnrctl status on the database server VM you can see the list of known services. The tnsnames.ora file on your client should have 192.168.0.109 as the host if the listener is listening on 192.168.0.109. You should verify that the database is up and that you can see it listed in the listener status (lsnrctl status) output. Commented Mar 16, 2021 at 17:18
  • Thanks @Bobby Durrett for the quick response. Please find the output of the lsnrctl status in the last section of the query and suggest me how to get the service associated with the listener. I cannot see the service name when I set the IP of the host in the listener.ora but can see the service name when 0.0.0.0 is set . Please guide me. Commented Mar 16, 2021 at 17:58
  • It could be several things. I assume that you want to connect to the POLA service and that you have a database named POLA. Make sure that the database is up. If you do sqlplus / as sysdba from the command line on the database server you should be able to run a query like select count(*) from dba_tables. If it is up then it is not registering automatically with the listener and I am not sure all the reasons that could happen. Could have a "LOCAL_LISTENER" set on database. You can use a SID_LIST_LISTENER section in your listener.ora and that might help. Commented Mar 16, 2021 at 18:06
  • Thanks once again @Bobby Durrett. Please find the output of the sqlplus / as sysdba in the last section of the query. Actually, I have created another user to access the POLA database. Not sure what to do to make the connection to the Oracle DB. :-( Commented Mar 16, 2021 at 18:37
  • Is that tnsnames.ora entry exactly the same as the one that works on another host on your LAN? If not then make the entry on the VM the same as the one that works. If they are the same you could try replacing (SERVICE_NAME = pola) with (SID = POLA) to eliminate service name as the problem. Commented Mar 18, 2021 at 16:26

1 Answer 1

0

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

3
  • Thank you @Bobby Durrett. I am now able to connect to the POLA service from the host system where the Oracle DB is running and also from the other system in he same LAN by following your suggestion. But what could be the reason for not getting it connected from the VM (Ubuntu 18.04 LTS)? It is getting the same "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" error. Do I need to make any other changes in the Oracle DB configuration for this? Commented Mar 17, 2021 at 5:49
  • I would look at the tnsnames.ora file on the VM that is getting the ORA-12514 and make sure that the entry for POLA has the right service name which I think is also POLA. If it works from another system on the same LAN then the VM should work with the same tnsnames.ora entry as on that other system. It seems like you probably have the right host and port but maybe a different service name. Commented Mar 17, 2021 at 17:59
  • Thank you @Bobby Durrett. Please find the tnsnames.ora entry here: POLA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.109)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pola) ) ) Commented Mar 18, 2021 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.