Solved

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

Posted on 2009-07-01
11
770 Views
Last Modified: 2013-12-19
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!
0
Comment
Question by:luyan
  • 5
  • 4
  • 2
11 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756148
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
 

Author Comment

by:luyan
ID: 24756238
Good catch!
If I have other two instance inprod2 and inprod3, how should I add them into listener.ora file?
Thanks!
0
 

Author Comment

by:luyan
ID: 24756265
They are on same host, but they are different instances - inprod1, inprod2 and inprod3.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 400 total points
ID: 24756266
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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 24756371
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756421
@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
 
LVL 47

Expert Comment

by:schwertner
ID: 24756646
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756710
>>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
 

Author Comment

by:luyan
ID: 24756712
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756773
@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
 

Author Closing Comment

by:luyan
ID: 31598847
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question