Solved

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

Posted on 2009-07-01
11
763 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
Comment Utility
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
Comment Utility
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
Comment Utility
They are on same host, but they are different instances - inprod1, inprod2 and inprod3.
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 400 total points
Comment Utility
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
Comment Utility
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
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

Expert Comment

by:mrjoltcola
Comment Utility
@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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
@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
Comment Utility
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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now