Solved

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

Posted on 2009-07-01
11
780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 48

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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

627 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