This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 11, 2012 4:24 AM by upen RSS

ora-12154 TNS Error in DB Link

upen Newbie
Currently Being Moderated
Dear Friends,

I have created DB LINK between SQL Server and Oracle.

When i testing a DB link, I am getting error TNS Error.
"ora-12154 tns could not resolve the connect identifier specified"

Listener File Entry*

# listener.ora Network Configuration File: E:\ORAGATE\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISVORA.IMOP.Local)(PORT = 1520))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1520))
)
)


SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=E:\ORAGATE)
(PROGRAM=dg4msql)
)
)

ADR_BASE_LISTENER = E:\ORAGATE

sqlnet.ora_

# sqlnet.ora Network Configuration File: E:\ORAGATE\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)

tnsnames.ora_

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ISVORA.IMOP.Local)(PORT=1520))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

Please any one give solution.

Regards,
Upendran
  • 1. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Hi,


    SQL Server and Oracle are same server?
    What is the path of tnsnames.ora and sqlnet.ora?

    Regards,
  • 2. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    both are running in different servers.

    E:\ORAGATE\NETWORK\ADMIN\

    Oracle and Oracle gateway running in same server

    Edited by: upen on Dec 10, 2012 10:44 PM
  • 3. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Hi,

    What is the configuration for %ORCALE_HOME%\HS\ADMIN\initxxxxxx.ora (dg4msql) ?
    How about ODBC setting?


    Regards,
  • 4. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    I am not did ODBC Settings.

    E:\ORAGATE\dg4msql\admin_

    # 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=[ISVCDB]//TEN-BIDWH
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER


    Under this directory
    *%ORCALEHOME%\HS\ADMIN\initxxxxxx.ora (dg4msql) ?*_
    i am having only one file
    extproc.ora

    Edited by: upen on Dec 10, 2012 11:10 PM
  • 5. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Hi,

    To my understanding, I think that it is necessary to set up ODBC Data Source and create gw service.
    Regards,
  • 6. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    i am using Oracle Database Gateway for SQL Server
    if i need to configure odbc
    Where i need to configure
    Please tell me the procedure or any document
  • 7. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Oh, Sorry...

    What is "%ORACLE_HOME%\dg4msql\admin\initdg4msql.ora" config?
    How about tnsping to "dg4msql"?
  • 8. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    Ok
    This is my dg4msql.ora

    E:\ORAGATE\dg4msql\admin

    # 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=[ISVCDB]//TEN-BIDWH
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER

    TNSPING Status

    TNS-03505 Failed to resolve name

    Regards,
    Upendran

    Edited by: upen on Dec 11, 2012 12:27 AM
  • 9. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Hi,

    tnsping dg4msql
    Regards,
  • 10. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    Used Parameter files:
    E:\ORAGATE\Networ\admin\Sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    attempting to contact (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=ISVORA.IMOP.Local)(PORT=1520))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
    )
    OK (10 msec)
  • 11. Re: ora-12154 TNS Error in DB Link
    asahide Expert
    Currently Being Moderated
    Hi,

    E:\ORAGATE\dg4msql\admin\initdg4msql.ora
    HS_FDS_CONNECT_INFO=[ISVCDB]//TEN-BIDWH

    HS_FDS_CONNECT_INFO parameter should "host_name/[instance_name][database_name]" <-(SQL Server's).

    Regards,
  • 12. Re: ora-12154 TNS Error in DB Link
    kgronau Guru
    Currently Being Moderated
    The tns alias you're using in your database link must reside in your tnsnames.ora of the Oracle database.

    According to your output:
    Used Parameter files:
    E:\ORAGATE\Networ\admin\Sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    attempting to contact (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=ISVORA.IMOP.Local)(PORT=1520))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
    )
    OK (10 msec)

    the tns alias resides in your GATEWAY home tnsnames.ora file.


    So please make sure sure the alias
    dg4msql =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=ISVORA.IMOP.Local)(PORT=1520))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
    )

    also exists in your database home tnsnames.ora file.
  • 13. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    Hi,

    As per the installation document only i given like this.

    document Ref_
    The default initialization parameter file already has an entry for this parameter. The syntax for HS_FDS_CONNECT_INFO is as follows:
    HS_FDS_CONNECT_INFO= host_name/[instance_name][database_name]
    Where:
    Variable Description
    host_name is the host name or IP address of the machine hosting the SQL Server database.
    instance_name is the instance of SQL Server running on the machine.
    database_name is the SQL Server Database database name.
    Both instance_name and database_name are optional. If instance_name is omitted and database_name is provided, the slash (/) is required. This can be shown as follows:
    HS_FDS_CONNECT_INFO= host_name//database_name

    Which instance name we need to provide. (Oracle & SQL Server)

    Regards,
    Upendran

    Edited by: upen on Dec 11, 2012 1:06 AM
  • 14. Re: ora-12154 TNS Error in DB Link
    upen Newbie
    Currently Being Moderated
    I am not using any alias name.
    I used following cmd for database link.

    CREATE PUBLIC DATABASE LINK TENSUITE CONNECT TO "sa" IDENTIFIED BY "Sqladmin4imop" USING 'dg4msql';
1 2 Previous Next

Legend

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