6 Replies Latest reply: Dec 11, 2012 7:47 AM by EdStevens RSS

    Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier

    979067
      I'm trying to make a connection heterogeneous DBLINK between Oracle and SQLSever, however me the following error is returned: ORA-12154.

      I am setting up as follows:


      TNSNAMES.ora:
      dg4msql =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = S015145.SETAPE.COM)(PORT = 1522))
      (CONNECT_DATA =
      (SID = dg4msql)
      )
      (HS = OK)     
      )
      
      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = S015145.SETAPE.COM)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      ORACLR_CONNECTION_DATA = 
        (DESCRIPTION = 
          (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
          ) 
          (CONNECT_DATA = 
            (SID = CLRExtProc) 
            (PRESENTATION = RO) 
          ) 
        ) 
      LISTENER.ora:
      # listener.ora Network Configuration File: /u01/gateways/network/admin/listener.ora
      # Generated by Oracle configuration tools.
      
      MSSQL = 
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = S015145.SETAPE.COM)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      )
      )
      
      SID_LIST_LISTENER11G =
      (SID_LIST = 
      (SID_DESC =
      (SID_NAME=dg4msql)
      (ORACLE_HOME=/u01/gateways)
      (ENVS=LD_LIBRARY_PATH=/u01/gateways/dg4msql/driver/lib:/u01/gateways/lib)
      (PROGRAM=dg4msql)
      )
      )
      
      ADR_BASE_MSSQL = C:\product\11.2.0\tg_1
      INITDG4MSQL.ora :
       This is a customized agent init file that contains the HS parameters
      # that are needed for the Database Gateway for Microsoft SQL Server
      
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO=S015145.SETAPE.COM//Teste
      # alternate connect format is hostname/serverinstance/databasename
      HS_FDS_TRACE_LEVEL=DEBUG
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER
      Thx
        • 1. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
          BluShadow
          did you reload the listener?
          C:\> lsnrctl reload
          • 2. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
            979067
            No, I will see if it resolves.


            @edit.

            Not resolved, the same error occurs.
            SQL Error: ORA-12154:   "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.
            Edited by: Everton. on 11/12/2012 04:44
            • 3. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
              BluShadow
              Are you sure you've updated the correct tnsname.ora file? Sometimes there are sample copies lying around or you could have multiple database homes on the server etc.
              • 4. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
                979067
                Well, the server is local, "S015145.SETAPE.COM" is the name of the local machine (localhost). I do not have familiarity with Oracle, follow an article for such purpose. So I do not know if the tnsnames.ora is correct.
                • 5. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
                  EdStevens
                  BluShadow wrote:
                  did you reload the listener?
                  C:\> lsnrctl reload
                  Cmon, Blu. You know by now that ORA-12154 never got to the listener.
                  • 6. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
                    EdStevens
                    Everton. wrote:
                    I'm trying to make a connection heterogeneous DBLINK between Oracle and SQLSever, however me the following error is returned: ORA-12154.

                    I am setting up as follows:
                    First, remember that the database owning the dblink is acting exactly like any other client.

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