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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
systemsautomationAuthor Commented:
the problenm solved when local listener init param was changed and rights were given on /tmp/.oracle folder
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.