10 Replies Latest reply: Mar 24, 2010 9:53 PM by EdStevens RSS

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

    user12158503
      Hi,
      I created a new database called test_db and craeted a databaselink to database progdb. But when I try to query the table prospects in progdb using this database link, it gives me an error. Can someone please tell me where I am wrong?

      CREATE DATABASE LINK progdb
      CONNECT TO uatuser IDENTIFIED BY ****
      USING 'progdb';

      select * from prospects@progdb

      ORA-12154: TNS:could not resolve the connect identifier specified
        • 1. Re: ORA-12154: TNS:could not resolve the connect identifier specified
          sb92075
          post content of tnsnames.ora

          post results from following commands

          lsnrctl status
          tnsping progdb
          lsnrctl service
          • 2. Re: ORA-12154: TNS:could not resolve the connect identifier specified
            EdStevens
            user12158503 wrote:
            Hi,
            I created a new database called test_db and craeted a databaselink to database progdb. But when I try to query the table prospects in progdb using this database link, it gives me an error. Can someone please tell me where I am wrong?

            CREATE DATABASE LINK progdb
            CONNECT TO uatuser IDENTIFIED BY ****
            USING 'progdb';

            select * from prospects@progdb

            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 "test_db" should have an entry for "progdb".

            And for the second time today ... 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".
            • 3. Re: ORA-12154: TNS:could not resolve the connect identifier specified
              user12158503
              I get an error when I try to do lsnrctl service. I think it looks for the default listener on 1521. But I had changed the listener to lsnr_test_db(port-1522). I tried to set local_listener to lsnr_test_db, but it still doesn't change the value of local_listener. See below all what you asked for -


              tnsnames.ora -

              # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
              # Generated by Oracle configuration tools.
              ##################################################################################################

              TEST_DB =
              (DESCRIPTION =
              (ADDRESS =
              (PROTOCOL = TCP)
              (HOST = utxwachiti.mk.com)
              (PORT = 1522)
              )
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SID = TEST_DB)
              )
              )


              LISTENER_TEST_DB =
              (ADDRESS =
              (PROTOCOL = TCP)
              (HOST = utxwachiti.mk.com)
              (PORT = 1522)
              )


              PROGDB =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS =
              (PROTOCOL = TCP)
              (HOST = utxwachiti.mk.com)
              (PORT = 1583)
              )
              )
              (CONNECT_DATA =
              (SID = PROGDB )
              )
              )

              lsnrctl status -

              oracle@<test_db>:/oravol01/oradata/progp1 $lsnrctl status lsnr_test_db

              LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-MAR-2010 15:05:46

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
              STATUS of the LISTENER
              ------------------------
              Alias lsnr_test_db
              Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
              Start Date 18-MAR-2010 00:24:05
              Uptime 6 days 14 hr. 41 min. 40 sec
              Trace Level off
              Security ON: Local OS Authentication
              SNMP OFF
              Listener Parameter File /opt/oracle/product/11.1.0/dev/network/admin/listener.ora
              Listener Log File /opt/oracle/diag/tnslsnr/utxwachiti/lsnr_test_db/alert/log.xml
              Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=utxwachiti.mk.com)(PORT=1522)))
              Services Summary...
              Service "test_db" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              Service "test_dbXDB" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              Service "test_db_XPT" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              The command completed successfully


              tnsping progdb:

              oracle@<test_db>:/opt/oracle/product/11.1.0/dev/network/admin $tnsping progdb

              TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 24-MAR-2010 15:13:22

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

              Used parameter files:
              /opt/oracle/product/11.1.0/dev/network/admin/sqlnet.ora


              Used TNSNAMES adapter to resolve the alias
              Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = utxwachiti)(PORT = 1580))) (CONNECT_DATA = (SID = progdb)))
              OK (0 msec)


              lsnrctl service:

              oracle@<test_db>:/opt/oracle/product/11.1.0/dev/network/admin $lsnrctl service

              LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-MAR-2010 15:21:28

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

              Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
              TNS-12541: TNS:no listener
              TNS-12560: TNS:protocol adapter error
              TNS-00511: No listener
              Linux Error: 111: Connection refused

              oracle@<test_db>:/opt/oracle/product/11.1.0/dev/network/admin $lsnrctl service lsnr_test_db

              LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-MAR-2010 15:21:37

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
              Services Summary...
              Service "test_db" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              Handler(s):
              "DEDICATED" established:26 refused:0 state:ready
              LOCAL SERVER
              Service "test_dbXDB" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              Handler(s):
              "D000" established:0 refused:0 current:0 max:1022 state:ready
              DISPATCHER <machine: utxwachiti, pid: 9961>
              (ADDRESS=(PROTOCOL=tcp)(HOST=utxwachiti)(PORT=23197))
              Service "test_db_XPT" has 1 instance(s).
              Instance "test_db", status READY, has 1 handler(s) for this service...
              Handler(s):
              "DEDICATED" established:26 refused:0 state:ready
              LOCAL SERVER
              The command completed successfully


              Then, I tried to set local_listener -

              SQL> show parameter local

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              local_listener string LISTENER_TEST_DB
              log_archive_local_first boolean TRUE
              SQL> alter system set local_listener='lsnr_TEST_DB' scope=spfile;

              System altered.

              SQL> show parameter local

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              local_listener string LISTENER_TEST_DB
              log_archive_local_first boolean TRUE

              SQL> alter system set local_listener='lsnr_TEST_DB' scope=BOTH
              2 ;
              alter system set local_listener='lsnr_TEST_DB' scope=BOTH
              *
              ERROR at line 1:
              ORA-02097: parameter cannot be modified because specified value is invalid
              ORA-00119: invalid specification for system parameter LOCAL_LISTENER
              ORA-00132: syntax error or unresolved network name 'lsnr_TEST_DB'


              SQL> exit


              Can you please tell me where I am wrong?
              • 4. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                Lubiez Jean-Valentin
                Hello,


                Please, check if in your tnsnames.ora you have defined an entry for progdb. If not
                you'll have to add it.


                Hope this help.
                Best regards,
                Jean-Valentin
                • 5. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                  user12158503
                  Thanks to all, whi have tried to help.
                  Yes, my tnsnames.ors on test_db does have an entry for progdb -



                  tnsnames.ora -

                  # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
                  # Generated by Oracle configuration tools.
                  ##################################################################################################

                  TEST_DB =
                  (DESCRIPTION =
                  (ADDRESS =
                  (PROTOCOL = TCP)
                  (HOST = utxwachiti.mk.com)
                  (PORT = 1522)
                  )
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SID = TEST_DB)
                  )
                  )
                  LISTENER_TEST_DB =
                  (ADDRESS =
                  (PROTOCOL = TCP)
                  (HOST = utxwachiti.mk.com)
                  (PORT = 1522)
                  )
                  PROGDB =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS =
                  (PROTOCOL = TCP)
                  (HOST = utxwachiti.mk.com)
                  (PORT = 1583)
                  )
                  )
                  (CONNECT_DATA =
                  (SID = PROGDB )
                  )
                  )
                  • 6. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                    19426
                    Tnsping result:
                    /*
                    Used TNSNAMES adapter to resolve the alias
                    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = utxwachiti)(PORT = 1580))) (CONNECT_DATA = (SID = progdb)))
                    OK (0 msec)
                    */
                    Tnsnames.ora:
                    /*
                    PROGDB =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS =
                    (PROTOCOL = TCP)
                    (HOST = utxwachiti.mk.com)
                    (PORT = 1583)
                    )
                    )
                    */

                    Obviously tnsping does not refer to THIS tnsnames.ora. Didn't you read Ed's post about database links?
                    /*
                    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 "test_db" should have an entry for "progdb".
                    */

                    Port 1580 versus port 1583

                    Werner

                    Edited by: oradba on 24.03.2010 21:40

                    Edited by: oradba on 24.03.2010 21:41
                    • 7. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                      Lubiez Jean-Valentin
                      Hello,

                      You posted:

                      PROGDB =
                      (DESCRIPTION =
                      (ADDRESS_LIST =
                      (ADDRESS =
                      (PROTOCOL = TCP)
                      (HOST = utxwachiti.mk.com)
                      (PORT = 1583)
                      )
                      )
                      (CONNECT_DATA =
                      (SID = PROGDB )
                      )
                      )
                      So, Ok you have an entry for PROGDB but you should check if on the server utxwachiti.mk.com the
                      SID of the database is PROGDB (and uses the port 1583 as previously posted).

                      More over, you may check if on the sqlnet.ora (on the server side) the NAMES.DIRETORY_PATH parameter
                      is as follow:
                      NAMES.DIRETORY_PATH = (TNSNAMES,...)
                      Else, about the error when you try to modify the LOCAL_LISTENER, it's not related to the ORA-12154 error,
                      and this parameter must be set carrefully. If you misconfigure this parameter you may not be able to start
                      the database.


                      Hope this help.
                      Best regards,
                      Jean-Valentin

                      Edited by: Lubiez Jean-Valentin on Mar 24, 2010 9:50 PM
                      • 8. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                        EdStevens
                        user12158503 wrote:
                        I get an error when I try to do lsnrctl service. I think it looks for the default listener on 1521. But I had changed the listener to lsnr_test_db(port-1522). I tried to set local_listener to lsnr_test_db, but it still doesn't change the value of local_listener. See below all what you asked for -
                        <snip>

                        This has nothing to do with your originally reported problem, but let point out another Incredibly Simple Concept (tm).

                        A single listener, with the default name of listener, using the default port of 1521, will happily service multiple database instances, of multiple versions, running from multiple Oracle Homes.

                        There is rarely any need to give the listener a funny name. There is rarely any reason to have more than one listener. Creating a listener specific to a single database is the equivalent of the telephone company building an entire switchboard to service one telephone number.
                        • 9. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                          user12158503
                          Ed, Thanks a lot. Thanks to everyone's help and all the patience. I am a new DBA and so get confused in between. I appreciate everyone's help.

                          What about my local_listener? Does that have to be set to the listener lsnr_test_db(port 1522) or listener_test_db(port1521). I went with a different port because I read that it's more safer to not use the default port. Do I need to remove listener_test_db ? My database listens on 1522.
                          • 10. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                            EdStevens
                            user12158503 wrote:
                            Ed, Thanks a lot. Thanks to everyone's help and all the patience. I am a new DBA and so get confused in between. I appreciate everyone's help.

                            What about my local_listener? Does that have to be set to the listener lsnr_test_db(port 1522) or listener_test_db(port1521). I went with a different port because I read that it's more safer to not use the default port. Do I need to remove listener_test_db ?
                            I wouldn't have a "lsnr_test_db". I'd just have one listener one each database server, and I'd call it ... (ta da!) . . . "listener". Do not name your listener as if it has any inherent connection to a particular database. It doesn't. It's just a switchboard. If you had mutltiple databases on the server, you'd still only need one listener, so don't name it as if it were dedicated to the service of one particular database - even if in fact you only have one database on the server.

                            Remember, the listener is a server side (not a client-side) process, and it is an entirely separate process from the database(s). In your case you have two database servers, one hosting your test db and one hosting your prod db. Each host machine gets one listener. Not each database.
                            My database listens on 1522.
                            No it doesn't. Your listener listens on port 1522. It's fine to use a non-standard port. Many people do just that for the same reason you mention. But get it fixed in your mind that it is the listener that uses the port, not the database. The client sends a request to the machine and port specified in the client's tnsnames.ora (and remember, in the case of a db link, a db is acting as a client to yet another db). The listener (not the database) is listening on that port. Once the listener gets the request, it starts a server process between the client and the database instance, and assigns a different port for the communication between the client and that server process. And at that point, the server has no more to do with that particular connection. The listener can actually be stopped, and it will have no effect whatsoever on established connections.