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

Question

I have problem, when trying to connect to Oracle Database XE via listener:

 sqlplus system/my_password@XE

I get the following error:

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

I use Ubuntu 12.10 and Oracle Express Edition 11g.

My main goal to connect java + oracle db with jdbc, but there i get the same error.

Here are my listener.ora

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = watson)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

and tnsnames.ora

# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = watson)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

and

lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2014 17:01:44
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

Answer 1

I had to restart my OracleServiceXE service in windows services and it was working again.

Answer 2

Assuming your database is failing to register even when the listener has been running for a while, it sounds like your local_listener parameter isn't explicitly set, so your database is trying to register on a default address; and since it's failing to register, whatever address it's using doesn't seem to be the same one the listener is on.

Assuming watson is resolving to a useful external IP address, which is probably the case if you're able to get that TNS error from outside your box, you can tell the database to use the same address to register:

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=watson)(PORT=1521))' scope=memory;
alter system register;

If that works and lsnrctl services now shows XE and you're happy with it, you can change the memory to both and re-execute so it persists across the next DB restart.

Alternatively, if you've only tried connecting within the same box, watson might be resolving to something unhelpful, like 127.0.0.1 if it's set to do that in your /etc/hosts; or your /etc/hosts address might not agree with your DNS entry. You can change the listener.ora and tnsnames.ora to the 'right' FQDN or IP address instead, as long as that matches whatever your database thinks is the network name.