?
Solved

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

Posted on 2007-08-07
10
Medium Priority
?
610 Views
Last Modified: 2013-12-19
I get quite tired of getting tns wrongly set up. Every time I see the following message I knew it is tns error

Enter password:                                                                 ERROR:                                                                          ORA-12514: TNS:listener does not currently know of service requested in connect descriptor                                                                      

So how to set Oracle TNS for both oracle 9 and oracle 10g.

I need to know the concept or principle behind, I've so many solution regarding this, but all solutions lead to one thing:

it never give any principal behind it. I mean what is SID? what is service name? Is there a difference in these terms between Oracle 9i and Oracle 10g?
0
Comment
Question by:shortandsharp
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Accepted Solution

by:
leenus2b earned 500 total points
ID: 19652642
I think there is not much diff in 9i and 10g when u come to TNS side,
The concept is as simle as how courier will be delivered to an address

When u give sqlplus scott / tiger @ instance name,first it will see tnsnames.ora and collect the information

to which PORT it has to go,
to which SID it should request for ,
to which DOMAIN it has to go,
to which HOST(server name) it has to go in that domain,
which PROTOCOL it has to use.
WHICH TYPE OF CONNECTION IT WANT ( DEDICATED OR SHARED)

and it submits the details to listener there..

IListener will hav the following info
to which SID it has to work for,
to which domain it belongs to,
to which host itz working for,
to which port it is working on,
(listener also can be define to hear a particular protocol)

If every thing matches it will create a session and ur user credentials will be verified.if any thing not matches it will throw an error

U can note down what are details u r giving when u configuring LISTENER(DB side) and TNSNAMES (Client side) using "netmgr" and see the info
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 500 total points
ID: 19653201
I guess you are using Oracle 10g.
There is a famous error by creation of new DBs -
they have not static registration by the listener and
the dynamic registration doesn't work.
The fix is:

For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g installation.
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

Thats all.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19655093
"what is SID?"
This is the "name" (or identifier) of the database instance that the listener knows.  In Oracle8 and earlier this was the preferred (and maybe the only) way for the listener to connect to the database instance.

"what is service name?"
In Oracle9 and 10, this is an alternate identifier for the database instance.  The older way (using a "SID") still works in Oracle9 and 10, but they default to using the service name instead.

"Is there a difference in these terms between Oracle 9i and Oracle 10g?"
No.

But "SID" does have another meaning inside the database instance (this has been true at least since Oracle7).  That is, each user session also has a unique numeric "SID" value that is visible in v$session.  That "SID" in Oracle is totally different from the "SID" in the listener.ora or tnsnames.ora file.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:shortandsharp
ID: 19660503
Please correct me if I'm wrong

tnsnames.ora only concerns client

listener.ora concers the db server right

there both of these parameters must be match?

right? thanks
0
 
LVL 27

Assisted Solution

by:Tolomir
Tolomir earned 500 total points
ID: 19660675
yes
0
 
LVL 27

Expert Comment

by:Tolomir
ID: 19660686
e.g.

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.20)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )


---
correspondig tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ENVTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = envtest)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
0
 

Author Comment

by:shortandsharp
ID: 19660791
I can see that both matches (HOST = 192.168.100.20)(PORT = 1521

but how is the service_name in tnsames.ora not found in listener.ora

set?

thanks a lot!
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 19662423
That is correct, the service_name value from the tnsnames.ora file does *NOT* have to be in the listener.ora file.  But, you may have to add a "SID_DESC..." entry to the listener.ora file manually like schwertner suggested since without that, the instance does not always register itself dynamically with the listener.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

862 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