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

In tnsnames.ora:
INPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = inprod.tch.mycollege.edu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = inprod)
    )
  )

In sqlnet.ora:
NAMES.DIRECTORY_PATH= (TNSNAMES)

In listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/10.2.0)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inprod.tch.mycollege.edu)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

sqlplus /@inprod
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jul 1 12:45:47 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Anything wrong?
Thanks!
luyanAsked:
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.

mrjoltcolaCommented:
Sounds like your instance is not registered. Try adding the SID to the listener manually.

listener.ora below, note the 1 line added.

After change, restart the listener (lsnrctl stop / lsnrctl start)


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC = (SID_NAME = inprod))
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inprod.tch.mycollege.edu)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Open in new window

0
luyanAuthor Commented:
Good catch!
If I have other two instance inprod2 and inprod3, how should I add them into listener.ora file?
Thanks!
0
luyanAuthor Commented:
They are on same host, but they are different instances - inprod1, inprod2 and inprod3.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mrjoltcolaCommented:
Same way, if you want to use static config (which I do).


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC = (SID_NAME = inprod))
    (SID_DESC = (SID_NAME = inprod2))
    (SID_DESC = (SID_NAME = inprod3))
  )

Open in new window

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
schwertnerCommented:
It needs more attributes like Home:

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
mrjoltcolaCommented:
@schwertner: >>It needs more attributes like Home:

Actually no. It is not required.

From the Oracle Database Net Services Admin Guide:  http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm

<quote>
On UNIX, this setting is optional. Use it to specify the Oracle home location of the instance. Without this setting, the listener assumes its Oracle home for the instance.

On Windows, this setting is ignored. The Oracle home specified by the ORACLE_HOME parameter in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID of the Windows registry is used.
</quote>

0
schwertnerCommented:
This is a bad practice.
There are many Oracle Homes on some computers and the
mess is big.
The clear is said the location of the software the less surprises
could be expected in the future.
0
mrjoltcolaCommented:
>>This is a bad practice.

Since the parameter is ignored on Windows, and taken from the registry, you can add it all you want, its not going to be bad practice.


>>There are many Oracle Homes on some computers and the mess is big.

Yes, and on Windows, that info is stored in the registry with EACH Oracle Home. As the Oracle documentation reads clearly, it is ignored if you put it there in the location you specified.

0
luyanAuthor Commented:
It works well now. Thanks mrjoltcola!

I checked the listener status:
Service "inprod" has 1 instance(s).
  Instance "inprod", status UNKNOWN, has 1 handler(s) for this service...
Service "inprod2" has 1 instance(s).
  Instance "inprod2", status UNKNOWN, has 1 handler(s) for this service...
Service "inprod3" has 1 instance(s).
  Instance "inprod3", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Question:
If I just want to start one listener only infprod2, what should I do? It seems not define the seperate listener name for each instance in listner.ora.

Thanks!
0
mrjoltcolaCommented:
@schwertner: On UNIX I strongly agree with you! Much better to explicitly add it than to guess, right?

I was just saying for Windows or for simple environments its ok due to the listener defaults.
0
luyanAuthor Commented:
Thanks!
0
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.