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

Question

When trying to connect to a remote Oracle SQL Server using Oracle SQL Developer, I get the following error message:

Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

A couple of points to note:

1. I have had this working in pretty much this configuration, but the box went bang so now can't see exactly. But I remember not having to configure Listener.ora last time around.
2. The Oracle DB Server is at a remote location, but to connect last time I had to install Oracle DB, which is what I have done this time. But I'm not trying to connect to my local DB, but the remote DB.

When I tried to change the Host in the Listener to 110.241.84.62, I can't restart the OracleOraDB12Home1TNSListener service.

Googling implies that my problem lies somewhere in Listener.ora. Here are my Listener.ora and TNSNames.ora files.

Listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\xoneill\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\xoneill\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = DWH1)
      (ORACLE_HOME = D:\app\xoneill\product\12.2.0\dbhome_1)
      (GLOBAL_DBNAME = DWH1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
#      (ADDRESS = (PROTOCOL = TCP)(HOST = 110.241.84.62)(PORT = 1521))
    )
  )

I have tried setting the Host to the IP address of the remote server (see commented line above) but when I comment the localhost line and uncomment this then to restart the OracleOraDB12Home1TNSListener service, it Starts and Stops immediately with the following error:

Actually, even just saving the Listener.ora file seems to stop the service. But I can restart fine if HOST = localhost but when HOST = 110.241.84.62, it's the above error.

TnsNames.ora:

# tnsnames.ora Network Configuration File: D:\app\xoneill\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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


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

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

DWH1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 110.241.84.62)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DWH1)
    )
  )

From my Googling it seems like some LSNRCTL commands were useful:

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory

LSNRCTL> services dwh1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=110.241.84.62)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DWH1)))
TNS-01189: The listener could not authenticate the user

Trying to connect with SQLPlus gives the same message:

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 10 22:48:00 2018

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

Enter user-name: OBREAD@"110.241.84.62/DWH1:dedicated/OBREAD"
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

I feel like the answer lies somewhere in the LSNRCTL commands (I note that LSNRCTL services gives a different error of authentication) but I don't know enough to actually fully understand.

Appreciate any help.

Jono

Answer 1

Your statement "The Oracle DB Server is at a remote location, but to connect last time I had to install Oracle DB, " is puzzling. You do NOT need to install Oracle DB on your local machine to connect to a remote db. You just need to install the Oracle client.

First, get rid of your listener.ora (for the time being) then restart the listener. It should start just fine with all default values, which should allow the db to self-register. Wait 60 seconds, then (on the db server) issue 'lsnrctl status'. You should see an instance with a status of "READY". Note the SERVICE_NAME associated with it.

Next, in the tnsnames.ora on your client machine, make sure the entry 'DWH1' references the same SERVICE_NAME.

For more, see http://edstevensdba.com/oracle-tns/ora-12514-tnslistener-does-not-currently-know-of-service/