13 Replies Latest reply: Apr 30, 2010 4:14 AM by 735320 RSS

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

    735320
      hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

      could anyone help me on this?

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

      thanks.
        • 1. Re: ORA-12154: TNS:could not resolve the connect identifier specified
          Chinar
          Hi, probably service name don`t exits tnsnames.ora,Post contxet tnsnames.ora file here
          • 2. Re: ORA-12154: TNS:could not resolve the connect identifier specified
            735320
            here is my tns.ora

            GISDB =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
            )
            (CONNECT_DATA =
            (SERVICE_NAME = gisdb)
            )
            )

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


            here is my listener .ora

            # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
            # Generated by Oracle configuration tools.

            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
            (PROGRAM = extproc)
            )
            )

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
            )
            )


            here is my sqlnet.ora

            # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
            # Generated by Oracle configuration tools.

            # This file is actually generated by netca. But if customers choose to
            # install "Software Only", this file wont exist and without the native
            # authentication, they will not be able to connect to the database on NT.

            SQLNET.AUTHENTICATION_SERVICES= (NTS)

            NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
            • 3. Re: ORA-12154: TNS:could not resolve the connect identifier specified
              Rafi (Oracle DBA)
              Hi,
              I have some guidelines for you,can you please go through the details below:

              Error: ORA-12154 / TNS-12154
              Text: TNS:could not resolve service name
              -------------------------------------------------------------------------------
              Cause: The service name specified is not defined correctly in the
              TNSNAMES.ORA file.
              Action: Make the following checks and correct the error:
              - Verify that a TNSNAMES.ORA file exists and is in the proper
              place and accessible. See the operating system specific manual
              for details on the required name and location.
              - Check to see that the service name exists in one of the
              TNSNAMES.ORA files and add it if necessary.
              - Make sure there are no syntax errors anywhere in the file.
              Particularly look for unmatched parentheses or stray characters.
              Any error in a TNSNAMES.ORA file makes it unusable. See
              Chapter 4 in the SQL*Net V2 Administrator's Guide. If
              possible, regenerate the configuration files using the Oracle
              Network Manager.

              *** Important: The notes below are for experienced users - See Note:22080.1

              Explanation:
              The SQL*Net layer cannot find a definition for the alias supplied
              (as in "sqlplus scott/tiger@alias").

              Diagnosis:
              1) Check the tnsnames.ora file you are using, verify that it is accessible.
              Eg: On Unix:
              - Check if you have a $HOME/.tnsnames.ora file - This will be
              used in addition to 'tnsnames.ora'.
              - Check TNS_ADMIN is set in your environment.
              - There is a readable tnsnames.ora file in $TNS_ADMIN

              2) Ensure that the tnsnames.ora file contains a line of the form
              'alias=(...)' for the alias you are specifying.
              Aliases are NOT case sensitive.

              3) Make sure that there are no mismatched parentheses in the
              tnsnames.ora file.

                   4) Even if TNS_ADMIN is set SQL*Net looks in other locations for
                   configuration files. Check the default directories for old (or bad)
                   copies of TNS_NAMES.ORA. Eg: /etc, /var/opt/oracle,
                   $ORACLE_HOME/network/admin

              5) Check the default domain name being used, and the path used to
              locate aliases, in the SQLNET.ORA file.
              The default domain is specified in the NAMES.DEFAULT_DOMAIN
              parameter - this is appended to the alias specified in the
              connect string if there is no domain given.
              Eg: If NAMES.DEFAULT_DOMAIN=mydom.uk
              and a connect to "scott/tiger@mydb" is requested
              SQL*Net will look for the alias "mydb.mydom.uk"
              If NAMES.DIRECTORY_PATH is also specified this determines where
              SQL*Net looks for the alias expansion.

              6) If none of these show an error enable client side tracing
              at level 16 and see what has been written to the client trace
              file. There list of aliases in the trace file under the heading
              'TNS.NAMES.ORA TABLE HAS THE FOLLOWING CONTENTS'.

              7) If ORA-12154 is returned when selecting over a database link from a
              client check that the alias in the link can be resolved in the
              tnsnames.ora file ON THE SERVER.

              8) If you are connecting from a login dialog box, verify that you are
              not placing an "@" symbol before your connect net service name.

              9) When going from Windows to Linux/Unix platforms,
              you can see <carriage return> <new line> characters at the
              end of each line instead of <new line> <new line>. Be sure you
              use ascii mode when ftp'ing this between Windows and Linux/Unix


              Best regards,

              Rafi.
              http://rafioracledba.blogspot.com/
              • 4. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                Chinar
                if sqlplus system/oass@GISDB then you get an error?
                • 5. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                  735320
                  to rafi : thanks rafi..but i think i've tried a lot of method..and it makes me sick to solve this error..
                  to admin : yes i've try your suggesstion..and it stil give me an error..
                  • 6. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                    735320
                    so from my tnsnames.ora, listener name.ora, is there any mistakes or error on that file?
                    • 7. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                      he*420834*ke
                      Hello,

                      please can you post the output from tnsping GISDB executed on the host from where you are trying to connect to the DB?
                      • 8. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                        735320
                        hi,

                        TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 29-APR-2
                        010 17:02:17

                        Copyright (c) 1997, 2006, Oracle. All rights reserved.

                        Used parameter files:
                        C:\oracle\product\10.2.0\db_1\network\ADMIN\sqlnet.ora


                        Used TNSNAMES adapter to resolve the alias
                        Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
                        (HOST = alias-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gisdb)))
                        OK (20 msec)
                        • 9. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                          he*420834*ke
                          Hello,

                          the output of tnsping shows that you are able to connect to the listener listening on alias-PC on port 1521 and the listener knows of service gisdb.

                          Does an sqlplus <user>/<pwd>@gisdb work on the host were you executed the tnsping command?

                          What does an lsnrctl status executed on alias-PC telling you?

                          Henner
                          • 10. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                            EdStevens
                            pimpom wrote:
                            hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

                            could anyone help me on this?

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

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


                            This error means one thing, and one thing only. The client could not find the specified entry in the tnsnames.ora file being used.

                            As a follow-on to that statement, remember that when you use a dblink, the database in which the link is defined is acting as a client to the database that is the target of the link. So in this case, the tnsnames.ora file on the host of your source should have an entry for your target db, as defined in the db_link.

                            And for the umpteenth time ... this error has nothing to do with the status of a listener. The connection request never got far enough to reach a listener. If anyone tells you to check a listener, they are not paying attention, or do not understand how TNS works. This error is the equivelent of not being able to place a telephone call because you don't know the number of the party you want to reach. You wouldn't debug that situation by going to the other guy's house and testing his telephone, or by going to the phone company and testing the switchboard. And you don't debug a ORA-12154 by checking the listener. If I had a top ten list of "Incredibly Simple Concepts (tm)" that should be burned into the brain of everyone who claims to be an Oracle DBA, it would include "ORA-12154 Has Nothing To Do With The Listener".

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


                            Assume you have the following in your tnsnames.ora:
                            larry =
                              (DESCRIPTION =
                                (ADDRESS_LIST =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                                )
                                (CONNECT_DATA =
                                  (SERVICE_NAME = curley)
                                )
                              )
                            Now, when you issue a connect, say like this:
                            $> sqlplus scott/tiger@larry
                            tns will look in your tnsnames.ora for an entry called 'larry'. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

                            Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

                            Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you'll be connected.

                            A couple of important points.

                            First, the listener is a server side only process. It's entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.

                            Second, the tnsnames.ora file is a client side issue. It's purpose is for addressess resolution - the tns equivelent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.


                            What can go wrong?

                            First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.

                            Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"

                            Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"

                            Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"

                            Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"

                            Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"


                            =================================
                            • 11. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                              735320
                              hi,

                              i've change my database to 11g and try to tnsping my database. i got this error..now what does it mean?


                              TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-APR-2
                              010 15:59:43

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

                              Used parameter files:
                              C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

                              TNS-03505: Failed to resolve name

                              thanks
                              • 12. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                                19426
                                ORA-12154: TNS:could not resolve the connect identifier specified and TNS-03505: Failed to resolve name ==> same problem with different words.

                                Werner
                                • 13. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                                  735320
                                  ok thanks..
                                  now i solve the problem by reinstall the client. :)