This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jul 31, 2013 10:16 AM by EdStevens RSS

ORA-12154: TNS:could not resolve the connect identifier specified

d6155051-8805-4c97-9ec7-b3e67644ac2f Newbie
Currently Being Moderated

Hi guys.

This is my first time using Oracel database.

 

I have Oracel Client 11g installed on a WIndow 2008 R2 server at D:\app\product\11.2.0\client_1\network\admin.

I have set the TNS_NAMES to D:\app\product\11.2.0\client_1\network\admin.

 

But then I run Visual Studio 2010 to debug, I got  'ORA-12154: TNS:could not resolve the connect identifier specified'....

 

Please help.

  • 1. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    sb92075 Guru
    Currently Being Moderated

    For System1 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.

    For System2 post hostname, IP#, Operating System name & version & Oracle s/w version to 4 decimal places.

    Clarify which hostname is associated with DataBase (DB) Server system & which hostname with SQL Client.

     

    ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system

    ORA-12154 [B]NEVER[/B] involves the listener, the database itself or anything on the DB Server.

    ORA-12154 occurs when client requests a connection to some DB server system using some connection string.

    TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.

    The lookup operation fails because the name provided can [B]NOT[/B] be resolved to any remote DB.

    The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.

    The most frequent cause for the ORA-12154  error is when the connection alias can not be found in tnsnames.ora.

    The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.

    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]

    http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

  • 2. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    d6155051-8805-4c97-9ec7-b3e67644ac2f Newbie
    Currently Being Moderated

    Hi sb92075, thanks for your reply.

    I had went through the url that you provided, but no luck, it's still showing the same error message.

     

    I tried to use sqlplus to see if the connection can open at command prompt and it was successful.

    So I would suspect that the visual studio cannot find the TNSNAME.ORA file, any idea how I can change the path so that visual studio can read from the file?

  • 3. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    sb92075 Guru
    Currently Being Moderated

    Here is  a free clue.

     

    Two type of error exist.

    1) Error of commission; where you do something wrong.

    2) Error of omission; where you don't do something that needs to be done

     

    It is really hard to "see" an error of omission; unless you know what should exist.

     

    From which type of error do you suffer?

     

    I am not standing behind you.

     

    It is not possible to say what you are doing wrong, since we don't know what you are actually doing.

     

    I don't know what you have.

    I don't know what you do.

    I don't know what you see.

    It is really, Really, REALLY difficult to fix a problem that can not be seen.

    use COPY & PASTE so we can see what you do & how Oracle responds.

     

    PLEASE provide the previously requested details regarding specifics about the systems you have!

  • 4. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    Dreams Newbie
    Currently Being Moderated

    First check your listener is up and running, if it's ok then check do you configure tns entries that should be ping to your database.

    if it is not configure the listener and tns entries.

  • 5. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    SANCHITGUPTA Newbie
    Currently Being Moderated

    Hi

    try to do two things

     

     

    1. ping your tns entry like

    c:\> tnsping <<connection_string/database_name>>

     

    connection_string is from tnsname.ora file, bold orcl  is connection_string as in below example

     

    ORCL =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = chd-sgupta1)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = orcl)

        )

      )

     

     

    Note : if its pinging then go to 2nd step

     

    2. add entry in the listener.ora file of your database like

    in my example i have two database orcl and primary and i made the entry in the listener.ora file

     

     

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = orcl)

          (ORACLE_HOME = D:\app\oracle\product\10.2.0\db_1)

          (SID_NAME = orcl)

        )

       (SID_DESC =

          (GLOBAL_DBNAME = primary)

          (ORACLE_HOME = D:\app\oracle\product\10.2.0\db_1)

          (SID_NAME = primary)

        )

      )

     

    Hope it will helpful for you

     

    Regards

    Sanchit Gupta

  • 6. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    EdStevens Guru
    Currently Being Moderated

    Dreams wrote:

     

    First check your listener is up and running, if it's ok then check do you configure tns entries that should be ping to your database.

    if it is not configure the listener and tns entries.

    SIGH!

     

    Please, please, please go back and re-read the cause of the reported error.

    The request never left the client.  It never arrived at the listener. It never arrived at a place that it expected a listener.  The reported error is the same as saying "I couldn't call my friend because I couldn't find his telephone number".  And your suggestion is the equivalent of "if you can't call your friend, please check the telephone company switchboard." 

     

    The listener has zero, zilch, nada, null to do with ora-12154.  Checking the listener when dealing with ora-12154 is a complete, total, utter waste of time.

  • 7. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    EdStevens Guru
    Currently Being Moderated

    d6155051-8805-4c97-9ec7-b3e67644ac2f wrote:

     

    Hi sb92075, thanks for your reply.

    I had went through the url that you provided, but no luck, it's still showing the same error message.

     

    I tried to use sqlplus to see if the connection can open at command prompt and it was successful.

    So I would suspect that the visual studio cannot find the TNSNAME.ORA file, any idea how I can change the path so that visual studio can read from the file?

    If you can make a connection with sqlplus, then your basic oracle client configuration is correct.  Your error is not with oracle, but with your configuration of Visual Studio.  It is obviously either

    1) not useing the same tnsnames file that sqlplus is using, or (more likely)

    2) Not passing the correct net service name when requesting a connection.

     

    Google "configure visual studio to connect to oracle database". 

  • 8. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    DBA_1976 Explorer
    Currently Being Moderated

    I don;t know visual studio. But since you said that you are able to connect to the database using SQL PLUS, then your connection details are correct. But check whether visual studio uses the same tnsnames.ora.

     

    This link might help

     

    Connecting to Oracle from Visual Studio - Kirk Evans Blog - Site Home - MSDN Blogs

  • 9. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    sb92075 Guru
    Currently Being Moderated

    logging into the DB using sqlplus may have been done without using SQL*Net.

    The report about using sqlplus is 100% inconclusive regarding SQL*net & the listener.

  • 10. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    EdStevens Guru
    Currently Being Moderated

    sb92075 wrote:

     

    logging into the DB using sqlplus may have been done without using SQL*Net.

    The report about using sqlplus is 100% inconclusive regarding SQL*net & the listener.

    True.  I assumed that the sqlplus connection was using sqlnet, but OP never demonstrated that .

     

    Wiping egg off my face.

  • 11. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    EdStevens Guru
    Currently Being Moderated

    SANCHITGUPTA wrote:

    <snip>

    2. add entry in the listener.ora file of your database like

    in my example i have two database orcl and primary and i made the entry in the listener.ora file

    <snip>

     

    Hope it will helpful for you

     

    Regards

    Sanchit Gupta

     

    It won't.  Please stay after school and write on the blackboard 100 times:

     

    "ORA-12154 HAS NOTHING TO DO WITH THE LISTENER CONFIGURATION"

     

    Telling people to check and/or re-configure their listener in response to an ora/tns-12154  is probably the most often repeated and most wrong advice I see on this forum.  Makes me want to beat my head on the desk top every time I see it.

  • 12. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    EdStevens Guru
    Currently Being Moderated

    d6155051-8805-4c97-9ec7-b3e67644ac2f wrote:

     

    Hi sb92075, thanks for your reply.

    I had went through the url that you provided, but no luck, it's still showing the same error message.

     

    I tried to use sqlplus to see if the connection can open at command prompt and it was successful.

    So I would suspect that the visual studio cannot find the TNSNAME.ORA file, any idea how I can change the path so that visual studio can read from the file?

    As SB later pointed out, we/I may have assumed too much in accepting your statement that you got a successful connection with sqlplus.

     

    Please show us a successful sqlplus connection by copying the entire command and response and pasting back to your next post. Do the same with 'tnsping'

     

     

    ============================================================================

    BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a known identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is like going to the pub with a bag over your head.

    ============================================================================

  • 13. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    Abufazal Journeyer
    Currently Being Moderated

    Could you paste the code snippet from Visual studio, where you have specified the connection details.

  • 14. Re: ORA-12154: TNS:could not resolve the connect identifier specified
    SANCHITGUPTA Newbie
    Currently Being Moderated

    Dear EdStevens

     

    My replay was quite right, how u can say without try or apply.....  below i m showing the example with error and solution.... please read carefully and do not say again to me and others......

     

     

     

    C:\Documents and Settings\com1>sqlplus sys/xyz123@abc as sysdba

     

    SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 31 20:58:24 2013

     

    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

     

    Connected to an idle instance.

     

    SQL> startup

    ORACLE instance started.

     

    Total System Global Area 1610612736 bytes

    Fixed Size                  2077904 bytes

    Variable Size             503319344 bytes

    Database Buffers         1090519040 bytes

    Redo Buffers               14696448 bytes

    Database mounted.

    Database opened.

    SQL>

    SQL>

    SQL> shu immediate

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> exit

    Disconnected from Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

     

    ---- Now i m  remove the entry from listener.ora file and after that i stop and start the listener ------------

     

    C:\Documents and Settings\com1>lsnrctl stop

     

    LSNRCTL for 64-bit Windows: Version 10.2.0.5.0 - Production on 31-JUL-2013 20:58

    :39

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    The command completed successfully

     

    C:\Documents and Settings\com1>lsnrctl start

     

    LSNRCTL for 64-bit Windows: Version 10.2.0.5.0 - Production on 31-JUL-2013 20:59

    :11

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

    Starting tnslsnr: please wait...

     

    TNSLSNR for 64-bit Windows: Version 10.2.0.5.0 - Production

    System parameter file is D:\oracle\product\10.2.0\db_1\network\admin\listener.or

    a

    Log messages written to D:\oracle\product\10.2.0\db_1\network\log\listener.log

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc

    )))

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CHD-GOLDEN-G.SEASIA.ROOT

    .SEASIACONSULTING.COM)(PORT=1521)))

     

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    STATUS of the LISTENER

    ------------------------

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.5.0 - Produ

    ction

    Start Date                31-JUL-2013 20:59:14

    Uptime                    0 days 0 hr. 0 min. 3 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.o

    ra

    Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\listener.log

     

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CHD)(PORT=1521)))

    Services Summary...

    Service "PLSExtProc" has 1 instance(s).

      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "abc" has 1 instance(s).

      Instance "abc", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

     

     

    C:\Documents and Settings\com1>

    C:\Documents and Settings\com1>sqlplus sys/xyz123@abc as sysdba

     

    SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 31 20:59:38 2013

     

    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

     

    ERROR:

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

    descriptor

     

     

    Enter user-name:

    ERROR:

    ORA-01017: invalid username/password; logon denied

     

     

    Enter user-name:

    ERROR:

    ORA-01017: invalid username/password; logon denied

     

     

    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

     

    ------ Look the same error  ORA-12514: come after removed the entry  from listener.ora file ---------


    ------ now i make the entry again in listener.ora file,  and after that i stop and start the listener --------

     

    C:\Documents and Settings\com1>

    C:\Documents and Settings\com1>lsnrctl stop

     

    LSNRCTL for 64-bit Windows: Version 10.2.0.5.0 - Production on 31-JUL-2013 21:00

    :09

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    The command completed successfully

     

    C:\Documents and Settings\com1>lsnrctl start

     

    LSNRCTL for 64-bit Windows: Version 10.2.0.5.0 - Production on 31-JUL-2013 21:00

    :21

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

    Starting tnslsnr: please wait...

     

    TNSLSNR for 64-bit Windows: Version 10.2.0.5.0 - Production

    System parameter file is D:\oracle\product\10.2.0\db_1\network\admin\listener.or

    a

    Log messages written to D:\oracle\product\10.2.0\db_1\network\log\listener.log

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc

    )))

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CHD-GOLDEN-G.SEASIA.ROOT

    .SEASIACONSULTING.COM)(PORT=1521)))

     

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    STATUS of the LISTENER

    ------------------------

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.5.0 - Produ

    ction

    Start Date                31-JUL-2013 21:00:23

    Uptime                    0 days 0 hr. 0 min. 3 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.o

    ra

    Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\listener.log

     

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CHD)(PORT=1521)))

    Services Summary...

    Service "PLSExtProc" has 1 instance(s).

      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "abc" has 1 instance(s).

      Instance "abc", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

     

    C:\Documents and Settings\com1>

    C:\Documents and Settings\com1>

    C:\Documents and Settings\com1>

    C:\Documents and Settings\com1>sqlplus sys/xyz123@abc as sysdba

     

    SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 31 21:00:32 2013

     

    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

     

    Connected to an idle instance.

     

    SQL> startup

    ORACLE instance started.

     

    Total System Global Area 1610612736 bytes

    Fixed Size                  2077904 bytes

    Variable Size             503319344 bytes

    Database Buffers         1090519040 bytes

    Redo Buffers               14696448 bytes

    Database mounted.

    Database opened.

    SQL>

     

     

     

    --- Now i showed you Edstevens, by above method my problem have resolved, and i solved this type of problem more than 100 times...

    one point more is that when your database base is in idle state then without entry in the listener.ora file you can not connect to your database using connection string and face the 12514 error.

     

     

    Regards

    Sanchit

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points