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

Hello

I am using Fedora Core 8 and removed Oracle XE (rpm -e oracle-xe-univ) and reinstalled that.

But now when I connect sqlplus hr/hr@XE I get following

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 6 02:55:00 2010

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

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


Enter user-name:


Following are my listener and tnsnames.ora files


Listener
=======
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1555))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)



TNSNAMES
===========
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1555))
    (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)
    )
  )
systemsautomationAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
systemsautomationConnect With a Mentor Author Commented:
the problenm solved when local listener init param was changed and rights were given on /tmp/.oracle folder
0
 
cjl7freelance for hireCommented:
lsnrctl start

or lsnrctl status

And you can do a 'tnsping xe' to try the listener.

//jonas
0
 
systemsautomationAuthor Commented:
I have now changed the port to 1521 from 1555 in both tnsnames and listener.ora

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-APR-2010 03:49:03

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                06-APR-2010 03:48:33
Uptime                    0 days 0 hr. 0 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File         /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[omer@domU-12-31-39-0B-1D-86 admin]$ tnsping xe

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 06-APR-2010 03:49:19

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)
[omer@domU-12-31-39-0B-1D-86 admin]$
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
k_murli_krishnaCommented:
Refer:
http://ora-12514.ora-code.com/
http://www.experts-exchange.com/Databases/Oracle/Q_21855895.html

Check your listener.ora and tnsnames.ora files to make sure that the service is being listened for for the one that you are trying to connect to. If you have the instances registering themselves with the running listener, look at the 'lsnrctl status' to see which one it has registered with and what the listener is listening for.

Changing the SERVICE_NAME in you tnsnames.ora to a known service by the listener can solve your problem.

When you create new DB.
1.
if you named the Global Database Name is: abc.oracle.com and SID is: abc.
In TNSNames you can use: SERVICE_NAME=abc.oracle.com or SID=abc
2.
if you named the Global Database Name is: abc and SID is: abc.
In TNSNames you must use: SID=abc

Sometimes, problem could be let us say a semicolon at the end of the environment variable TNS_ADMIN. Remove it i.e. basically one should have environment variables set to correct values.


0
 
cjl7freelance for hireCommented:
Your tnsping works on port 1521, so a sqlplus should also work on port 1521.

Are you running the sqlplus from the localhost? You have restricted the listener to localhost.

//jonas
0
 
schwertnerCommented:
Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start
0
All Courses

From novice to tech pro — start learning today.