1 2 Previous Next 18 Replies Latest reply: Jul 31, 2013 12:16 PM by EdStevens RSS

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

    d6155051-8805-4c97-9ec7-b3e67644ac2f

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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