30
Mar
07

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

The ORA-12154 error is fairly common.

There is a lot of reason why this could happen. Very often, it’s due to multiple oracle_homes on the server conflicting each others.

I will try to address the issues of multiple homes first and after that, explain a bit the use of service_name and SID in tnsnames.ora and how this affects the connectivity.

Configuration for a multiple home environment

The situation is as follow:

We have installed oracle 10g on a server and created a database on it. Everything connects fine. We can use the following commands:

C:\>set oracle_sid=SID
C:\>sqlplus / as sysdba

We can also use tns methods to connect to the database.

Then, we install an application using oracle forms and therefore install oracle forms.

Weird stuff starts happening.

When running an sqlplus command we can see that sqlplus version is 8.1.7. Same for tnsping.
Clearly, when typing the command, we are directed to the wrong oracle home. I would prefer my latest version 10.2.0.3.

This problem involves a wrong configuration of the PATH environment variable.
Go to control panel>system>advanced>environment variable.

Locate the PATH variable.

Normally, c:\[oracle_home]\bin should be at the very beginning of the PATH variable.
On my environment it’s c:\oracle\product\102\bin
However, what I get is c:\orant\bin;

The solution is simple:

Move the string c:\oracle\product\102\bin to be at the very beginning of the PATH environment variable.

Run sqlplus:

C:\>sqlplus
SQL*Plus: Release 10.2.0.3.0

Great. Now it’s using oracle 10g again.

However, when I try to connect to my database using tnsnames.ora regardless of the location (client or server), I get an ORA-12154 error.

The problem is that due to too many oracle forms, network information are conflicting.
There are:

c:\oracle\product\102\network\admin
c:\orant\network\admin
c:\orant\net80\network\admin

So, what we are going to do is create one to rule them all!

Create a new folder outside of the oracle binaries. We are going to make it independant of the binaries.
My folder looks like this:

c:\oracle\network\admin

Go back to the environment variable screen in control panel>system>advanced

Create a new environment variable called TNS_ADMIN:
in the ‘value’ field, add the new folder: c:\oracle\network\admin

Copy tnsnames.ora, sqlnet.ora and listener.ora from c:\oracle\product\102\network\admin
Paste into c:\oracle\network\admin

Stop/start listener

C:\>lsnrctl stop
C:\>lsnrctl start


From this point, you should be able to call your database using tnsnames.ora without conflict.

However, the path to a proper use is long.

My sqlnet.ora looks like this:

NAMES.DEFAULT_DOMAIN = world

sqlnet.authentication_services = (NTS)

SQLNET.EXPIRE_TIME = 0

SQLNET.ENCRYPTION_SERVER = requested

SQLNET.ENCRYPTION_CLIENT = requested

NAMES.DIRECTORY_PATH= (TNSNAMES)

The interesting part is the bold value:
NAMES.DEFAULT_DOMAIN

The second interesting part is in the tnsnames.ora:

MYDB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NACS_SERVER)(PORT = 1910))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB.WORLD)
)
)

When the domain is specified in the sqlnet, and if you are using a service_name, then it is required to add the domain name following your DB name. A Service_name is different from the SID parameter.

When using a SID, you have to call the actual DB SID (MYDB) without the .world.
But service_name is not really a SID. It’s usually used when multiple applications run on the same DB to make them think they got their own database (where in fact they are sharing it).

When SERVICE_NAME is used, make sure to use .world following your db name.
Make sure to add .world to your tns name entry (the bold areas above).

Finally, when calling sqlplus make sure to use the following syntax:

sqlplus user/password@mydb.world

If the .world is not specified, the listener will consider that the tns entry does not exist thus generating an ORA-12154.

I hope this guide to connectivity with tnsnames.ora and its sometimes murky areas has been cleared with this post.

Powered by ScribeFire.

About these ads

38 Responses to “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”


  1. 1 Mahesh
    June 28, 2007 at 1:25 am

    Hi Nicolas,

    That is really very very useful information.
    Is there any version related difference in SID and SERVICE_NAME.
    e.g. In earlier versions only SID was available or only SERVICE_NAME ?

    I have following questions

    1. What are probable errors those might be occurred at the time of implicit sysdba connection ? Offcourse alongwith solutions.

    2. Is there any difference in following logins ?
    sqlplus sys/acomplexepassword as sysdba
    sqlplus sys/acomplexepassword@MYDB.WORLD

  2. 2 Ather
    April 21, 2008 at 9:01 pm

    Hi

    I’m facing same problem, I cannot connect after upgrade to 10.2.0.2.
    Error Ora-12514: TNS: listener does not currently know of service request in connect descriptor.

    Any solution, The above solution I tried not working.

    Thanks
    Ather Hussain
    email : atherhussain9@yahoo.com
    North America

  3. April 22, 2008 at 6:48 am

    Hi,

    It will be hard to help with that much detail. I think all the problems are pretty much covered in this article.

    I would suggest that you check your sqlnet.ora parameters and your tnsnames.ora make sure that you use a service_name rather than an SID in the tnsnames.ora

    Also, you should upgrade to 10.2.0.3. 10.2.0.2 is not a version I would recommend.

    Nico

  4. 5 Seenu
    May 22, 2008 at 2:22 pm

    The case u have said is ok if you are in server. In my client place i receive ora-12514 error. How can i solve this.

  5. May 22, 2008 at 2:37 pm

    Not true. some clients have many oracle home problem as well.

    TNS_ADMIN can help fix problems on client as well.

    All in all, what I’ve find out is that most of the time fixing this problem just require a rigorous step by step implementation.

    Describe your client a little bit.

    If you think you are facing a bigger issue, you can start traces.

  6. 7 Lanquaye Lamptey
    July 9, 2008 at 3:49 pm

    Dear sir/madam,

    I am an oracle student and i have a major problem in which you can help me solve that.
    My problem is ‘Enterprise Manager’ it is diffecult for me to open and sometime it does come at all.
    Please is there anything yuo can do to help me.

    Thank you.

  7. 8 ritesh
    October 12, 2008 at 7:33 pm

    sir ,
    i hv the same problem
    my sqlnet.ora file look like this

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

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    my using linux….
    plz help regarding this…..

  8. 9 shankaran
    October 9, 2009 at 7:44 pm

    Thanks a lot for your useful info. :)

  9. 10 George V
    December 10, 2010 at 8:26 pm

    Thanks for the advice. My tnsnames.ora entry used SERVICE_NAME instead of SID. Switching it to SID fixed the problem.

  10. 11 Jaime Rodriguez
    December 30, 2010 at 4:13 am

    I solved the error “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor” like this:
    1 – In All Programs, search for the option “Configuration and Migration Tools”
    2 – Select the option “Net Manager”
    3 – In the Net Manager windows, in the left tree view open the option “Local”, then open “Listeners” and click on the node with the listener name, generally called “LISTENER”
    4 – In the right side you will see several options for the Listener, in the top dropdown select the option “Database Services”
    5 – You will be prompted for three values, these values lets know the listener what databases it will be listening for. I have entered this (on my environment)
    Global Database Name: ORCL
    Oracle Home Directory: C:\oracle\product\10.1.0\Db_1
    SID: ORCL
    6 – Finally, select File (on the top menu) and Save Configuration. As a result, the listener.ora was set to:

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = ORCL)
    (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
    (SID_NAME = ORCL)
    )
    )

    In my case, installing Oracle, the listener was not correctly set up, so it was unable to listen anything for the ORCL database that was set up on the installation process.

    I hope this can help you.

    Jaime Rodriguez Villalta.

    • 12 Clarence
      March 1, 2011 at 6:52 pm

      Thanks for your your help – I spent hours trying to figure this out and using Net Manager sorted it!

      What is strange is that it appeared to work fine one day then not the next … even though I didn’t install any new software. Anyway – thanks again.

    • 13 tg
      April 7, 2012 at 11:15 am

      thnx a lot… ur solution worked

    • July 5, 2012 at 5:03 am

      Hi Jaime Rodriguez,
      You are great man. I am very grateful to you for sharing so-far unknown tool “Net Manager” with all of us. In fact it gave me the solution as well. I feel very satisfied with your great help.

      It is very weird that during database installation Oracle doesn’t install or configure proper listener parameters. It is not at all reliable very sadly.

      What I did now is that I deleted everything from the listener file. And used NETCA tool to create a fresh listener for the database, then used the tool Net Manager as you suggested to set the listener to listen the database service. And then stopped and started the listener using lsnrctl tool. Now it clearly shows that it listens for the database.

      For tnsnames file, I usually configure manually, but this time I deleted everything and created it fresh by using the NETCA tool. I did the same for both databases. Thank God, this time both databases are perfectly connected without the cruel ora-12514 error.

      I am grateful to you man. Keep it up. Thanks a lot.

      Vimal Rathinasamy.

  11. 15 Suman
    January 26, 2011 at 3:00 am

    It is very useful.Thank you Very Much

  12. February 3, 2011 at 11:51 am

    Hello there, I found your blog by way of Google while searching for first assist to get a coronary heart assault as well as your post
    looks very fascinating for me.

  13. 17 Hitesh
    February 15, 2011 at 10:47 am

    We have a situation wherein the settings are correctly working for most of the times. The Listener.ora and TNSnames.ora files are also correct as the application is running. Surprisingly, sometimes we get an error ORA-12154. On restart of server, the error gets resolved. What is the way that the database is not required to be restarted…

  14. March 21, 2011 at 5:16 pm

    I have a problem with my Oracle 10g R2 on windows 2003 R2 Enterprise.

    The database is working, but the enterprise manager says that the instance and listener are down, completely false. This is one of the error related to this fail El listener está caído: Failed to tnsping.

    Can u help me,

    Thanks

    Ing. David Azouth

  15. 19 Dave Emmith
    March 25, 2011 at 4:30 pm

    Unfortunately for me, none of the above suggestions has worked. Beginning yesterday, for reasons unknown, things really began getting flaky with my 10g database and now all I see are ORA-12505s and ORA-12514s – wish to God I knew what was happening with this.

    • 20 Dave Emmith
      March 25, 2011 at 4:32 pm

      Just noticed that 12514 is mentioned in the title of this post but throughout the post it is referred to as 12154 — so which is it?

  16. 21 Rams
    April 28, 2011 at 8:20 am

    Hi Jaime Rodriguez,

    i was very very tired of finding out the solution for

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

    but finally your information worked to get out from this problem., thanks so so much

    Rams

  17. August 25, 2011 at 8:16 am

    We are a bunch of volunteers and starting a brand new scheme in our community. Your web site provided us with valuable info to paintings on. You’ve performed an impressive activity and our entire community will likely be grateful to you.

  18. 23 Prasad
    November 18, 2011 at 10:01 am

    thanks guys.. I was also facing the same issue.it really help me to resolve the problem.

  19. 24 Roberto
    December 5, 2011 at 10:19 pm

    Thanks a lot!! the solution for me was to change SERVICE_NAME to SID in tnsnames.ora as George V suggested

  20. 25 Himanshu
    May 19, 2012 at 2:48 am

    Thanks, very useful

  21. 26 Vin
    April 16, 2013 at 9:17 am

    see am facing the same problem, below is my tnsnames.ora

    TEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TEST)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = TEST))
    )

    this is correct

  22. 27 yours fan
    July 28, 2013 at 11:16 am

    my bro excellent

  23. August 23, 2013 at 12:13 pm

    If some one wants to be updated with latest technologies after that he must be go to see this web site
    and be up to date daily.

  24. August 31, 2013 at 3:01 pm

    My brother suggested I might like this blog. He was totally
    right. This post truly made my day. You cann’t imagine just how
    much time I had spent for this information! Thanks!

  25. February 18, 2014 at 11:07 am

    Why visitors still use to read news papers when in this technological
    globe everything is presented on web?

  26. June 28, 2014 at 6:14 am

    Hi too every body, it’s my first pay a quick visit of this weblog; this webpage consists of amazing and genuinely fine material designed ffor visitors.

  27. July 2, 2014 at 10:05 am

    Highly energetic article, I loved that a lot. Will there be a part
    2?

  28. July 10, 2014 at 6:27 pm

    It’s an awesome post in support of all the online visitors; they will
    obtain benefit from it I am sure.

  29. August 1, 2014 at 8:12 am

    I am genuinely thankful to the owner of this site who has shared this impressive
    piece of writing at at this place.

  30. August 10, 2014 at 7:35 am

    Hi it’s me, I am also visiting this site on a regular basis, this
    web site is really good and the viewers are in fact sharing nice thoughts.

  31. August 20, 2014 at 5:01 pm

    Fantastic goods from you, man. I have understand your stuff prior to and
    you are just too excellent. I actually like what you’ve acquired here, really like what you are
    saying and the best way during which you are saying it.

    You’re making it enjoyable and you continue to care for to keep it smart.

    I can not wait to learn much more from you. That is actually a tremendous site.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: