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?

Improve company productivity with a Business Account.Sign Up

x
 
mrjoltcolaConnect With a Mentor Commented:
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
luyanAuthor Commented:
They are on same host, but they are different instances - inprod1, inprod2 and inprod3.
0
 
schwertnerConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.