1 2 Previous Next 19 Replies Latest reply: Dec 11, 2012 6:24 AM by upen RSS

    ora-12154 TNS Error in DB Link

    upen
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Hi,

                          tnsping dg4msql
                          Regards,
                          • 10. Re: ora-12154 TNS Error in DB Link
                            upen
                            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
                              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-Oracle
                                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
                                  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
                                    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