9 Replies Latest reply on Jul 29, 2009 5:06 AM by 546111

    DB Link and ora-12154 Error

    javed555
      Hi,

      I have created a database link which works fine on host but when I try to run same sql statement from any client tool like sqlplsu or toad remotely it gives me error ORA-12154: TNS:could not resolve service name

      Please note that the tnsnames entry already present in host tnsnames.ora file and same select statement runs fine on host. Any help please.
        • 1. Re: DB Link and ora-12154 Error
          sb92075
          12154, 00000, "TNS:could not resolve the connect identifier specified"
          // *Cause:  A connection to a database or other service was requested using
          // a connect identifier, and the connect identifier specified could not
          // be resolved into a connect descriptor using one of the naming methods
          // configured. For example, if the type of connect identifier used was a
          // net service name then the net service name could not be found in a 
          // naming method repository, or the repository could not be
          // located or reached.
          // *Action:
          //   - If you are using local naming (TNSNAMES.ORA file):
          //      - Make sure that "TNSNAMES" is listed as one of the values of the
          //        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA)
          //      - Verify that a TNSNAMES.ORA file exists and is in the proper
          //        directory and is accessible.
          //      - Check that the net service name used as the connect identifier
          //        exists in the TNSNAMES.ORA file.
          //      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
          //        file.  Look for unmatched parentheses or stray characters. Errors
          //        in a TNSNAMES.ORA file may make it unusable.
          //   - If you are using directory naming:
          //      - Verify that "LDAP" is listed as one of the values of the
          //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA).
          //      - Verify that the LDAP directory server is up and that it is
          //        accessible.
          //      - Verify that the net service name or database name used as the
          //        connect identifier is configured in the directory.
          //      - Verify that the default context being used is correct by
          //        specifying a fully qualified net service name or a full LDAP DN
          //        as the connect identifier
          //   - If you are using easy connect naming:
          //      - Verify that "EZCONNECT" is listed as one of the values of the
          //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA).
          //      - Make sure the host, port and service name specified
          //        are correct.
          //      - Try enclosing the connect identifier in quote marks.
          // 
          //   See the Oracle Net Services Administrators Guide or the Oracle
          //   operating system specific guide for more information on naming.
          • 2. Re: DB Link and ora-12154 Error
            javed555
            I have already verified these. I can't understand why it works on host but not on client.
            • 3. Re: DB Link and ora-12154 Error
              Srini Chavali-Oracle
              I do not believe Oracle messages lie ;-) Can you pl cut and paste your commands from host and client ?

              Srini
              • 4. Re: DB Link and ora-12154 Error
                sb92075
                I can't understand why it works on host but not on client.
                Client is different than host.
                • 5. Re: DB Link and ora-12154 Error
                  517085
                  You check your sqlnet.ora and tnsnames.ora files on client?

                  Regards,

                  Rodrigo Almeida
                  • 6. Re: DB Link and ora-12154 Error
                    javed555
                    Client tnsnames.ora and sqlnet.ora looks good. I can connect to both databses from client. The error only shows when I use the db link in slecet statement.
                    • 7. Re: DB Link and ora-12154 Error
                      sb92075
                      Post Operating System (OS) name & version for DB server system.
                      Post results of SELECT * from v$version.

                      post output from DB server system

                      lsnrctl service


                      The listener.log file contains a record for every connection request, both success & failures.
                      Post last 20 - 30 lines from listener.log which show 12154 result code in record

                      With DBLINK, GLOBAL_NAME can come into play.

                      Edited by: sb92075 on Jul 28, 2009 10:13 AM
                      • 8. Re: DB Link and ora-12154 Error
                        517085
                        Stranger!

                        This error apply also to INSERT/DELETE/UPDATE statement, using same dblink?

                        Regards,

                        Rodrigo Almeida
                        • 9. Re: DB Link and ora-12154 Error
                          546111
                          copy the contents of tnsnames.ora and sqlnet.ora file from host and append it to the tnsnames.ora and sqlnet.ora file at client side.Then try to access the database using the dblink.Hope this will work