CODEWITHSUNDEEP
javajdbcoracle11g
watson94
watson94

Reputation: 63

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

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

Upvotes: 6

Views: 106769

Answers (3)

Brandon Stanley
Brandon Stanley

Reputation: 81

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

Upvotes: 5

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 2

tom87416
tom87416

Reputation: 542

The tnsnames.ora is fine, it is used for the sqlplus client. The listener.ora is not good. You are missing the XE service when you type lsnrctl service Hard to believe you have to setup listener.ora to install Oracle express.

Here is a example from my testing VM.

Service "PEXPROC" has 1 instance(s).
  Instance "PEXPROC", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "ocmdb" has 1 instance(s).
  Instance "ocmdb", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0
         LOCAL SERVER
The command completed successfully

My sample segment in listener.ora

SID_LIST_LISTENER_OCM =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ocmdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ocmdb)
    )
  )

Upvotes: 0

Related Questions