This discussion is archived
6 Replies Latest reply: Dec 11, 2012 5:47 AM by EdStevens RSS

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

979067 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    did you reload the listener?
    C:\> lsnrctl reload
  • 2. Re: Error with dblink 'HS'  ORA-12154 Could not resolve the connect identifier
    979067 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points