10 Replies Latest reply: Jul 20, 2009 9:40 AM by nb*427594*su RSS

    Trying to set up new hs from 10g to SQL Server 200X

    510477
      I've been following the instructions found here:
      http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/admin.htm#169356

      in trying to set up an ODBC-based connection from an internal Oracle 10gr2 server on Windows 2003 Server to an external (ie 3rd-party hosted) SQL Server. The ODBC checks out just fine for connection test, etc. I am trying to set up a database link using heterogeneous services but keep getting this error when I try to validate the link:
      ORA-28545: error diagnosed by Net8 when connecting to an agent
      Unable to retrieve test of NETWORK/NCR message 65535
      ORA-02063: preceding 2 lines from TUMS
      I started out by modifying the existing inithsodbc.ora file and renaming it to inithsTUMS.ora. Here are contents:
      # This is a sample agent init file that contains the HS parameters that are
      # needed for an ODBC Agent. 
      
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = TUMS
      HS_FDS_TRACE_LEVEL = OFF
      "TUMS" is the name of the ODBC datasource.

      Next, I modified the listener.ora file. Here it is:
      # listener.ora Network Configuration File: E:\oracle\10gr2\DB\network\admin\listener.ora
      # Generated by Oracle configuration tools.
      
      LISTENER_TS10G1 =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ts10g1)(PORT = 1521)(IP = FIRST))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521)(IP = FIRST))
          )
        )
      
      SID_LIST_LISTENER_TS10G1 =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = E:\oracle\ora10gr2\ASM)
            (PROGRAM = extproc)
          )
          (SID_DESC =
            (GLOBAL_DBNAME = +ASM)
            (SID_NAME = +ASM)
            (ORACLE_HOME = E:\oracle\ora10gr2\ASM)
          )
          (SID_DESC =
            (SID_NAME = TS1)
            (GLOBAL_DBNAME = TS)
            (ORACLE_HOME = E:\oracle\ora10gr2\DB)
          )
        )
      
      LISTENERTUMS =
       (ADDRESS_LIST=
            (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
            (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
      
      SID_LIST_LISTENERTUMS=
        (SID_LIST=
            (SID_DESC=
               (SID_NAME=TUMS)
               (ORACLE_HOME=E:\oracle\10gr2\DB)
               (PROGRAM=hsodbc)
             )
            )
      After doing this I tried to start the listener. It started, and I got an OK on TNSPING.
      TNSNAMES.ora
      # tnsnames.ora Network Configuration File: E:\oracle\10gr2\ASM\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      TS =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = TS)
          )
        )
      
      LISTENERS_TS =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521))
      
      
      TS1 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = TS)(UR=A)
            (INSTANCE_NAME = TS1)
          )
        )
      
      +ASM =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521))
          (CONNECT_DATA =
            (SERVICE_NAME = +ASM)
          )
        )
      
      ASM =
         (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.201)(PORT = 1521))
            (CONNECT_DATA =
               (SERVER = DEDICATED)
               (SID = +ASM)
               (UR=A)
            )
         )
      
      TUMS=
         (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.201)(PORT=1522))
            (CONNECT_DATA = (SID=TUMS))
            (HS = OK)
         )
      Can anyone point me at a potential problem source?
        • 1. Re: Trying to set up new hs from 10g to SQL Server 200X
          Toni Lazarin
          Could you put
          HS_AUTOREGISTER=true 
          in your inithsTUMS.ora and
          go to cmd and type
          lsnrctl status  LISTENERTUMS
          ?
          • 2. Re: Trying to set up new hs from 10g to SQL Server 200X
            MMEGE-Oracle
            Hi,

            I can see your LISTENER.ORA is in this PATH:
            E:\oracle\10gr2\DB\network\admin\listener.ora
            and your TNSNAMES.ORA is in
            E:\oracle\10gr2\ASM\network\admin\tnsnames.ora

            Unless your have the TNS_ADMIN environment variable, be sure your TNSNAMES.ORA is localized in the ORACLE_HOME\network\admin
            path from the ORACLE_SERVER where you configure the inithsodbc.ora file and not from the client side.
            Without knowing your configuration,, it should be in this path:
            E:\oracle\10gr2\DB\network\admin\

            Regards,

            Mireille
            • 3. Re: Trying to set up new hs from 10g to SQL Server 200X
              510477
              okay. I added the additional parameter to the inithsTUMS.ora file
              E:\oracle\10gr2\DB>lsnrctl status LISTENERTUMS
              
              LSNRCTL for 32-bit Windows: Version 10.2.0.2.0 - Production on 27-MAY-2009 09:47
              :04
              
              Copyright (c) 1991, 2005, Oracle.  All rights reserved.
              
              Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.201)(PORT=1522))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENERTUMS
              Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.2.0 - Produ
              ction
              Start Date                27-MAY-2009 09:37:01
              Uptime                    0 days 0 hr. 10 min. 3 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   E:\oracle\10gr2\DB\network\admin\listener.ora
              Listener Log File         E:\oracle\10gr2\DB\network\log\listenertums.log
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.201)(PORT=1522)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
              Services Summary...
              Service "TUMS" has 1 instance(s).
                Instance "TUMS", status UNKNOWN, has 1 handler(s) for this service...
              The command completed successfully
              
              E:\oracle\10gr2\DB>
              Now when I try I get a different error message:
              SQL> select * from TCHeader@TUMS;
              select * from TCHeader@TUMS
                                     *
              ERROR at line 1:
              ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
              [Generic Connectivity Using ODBC][H006] The init parameter
              <HS_FDS_CONNECT_INFO> is not set. Please set it in init<orasid>.ora file.
              ORA-02063: preceding 2 lines from TUMS
              What did I miss?
              • 4. Re: Trying to set up new hs from 10g to SQL Server 200X
                510477
                okay. I renamed the file inithsTUMS.ora to initTUMS.ora. That seems to have progressed things a little.

                Now can anyone help me with the following error?
                ERROR at line 1:
                ORA-02085: database link TUMS.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
                HO.WORLD
                • 5. Re: Trying to set up new hs from 10g to SQL Server 200X
                  510477
                  I found a metalink document that says to add to the init.ora file, but it doesn't give examples of what to add.
                   To use global names in database names in combination with Heterogeneous 
                   services the init<HS-sid>.ora needs to be configured correctly to reflect
                   the global names.
                   So edit the init<hs-sid>.ora file and add the following 2 lines
                   HS_DB_NAME = <datasource>                                                       
                   HS_DB_DOMAIN = <DOMAIN>  
                  Does anyone have a clue what is supposed to go in these areas or if I'm even on the right track?
                  • 6. Re: Trying to set up new hs from 10g to SQL Server 200X
                    Kgronau-Oracle
                    The error message states:
                    ERROR at line 1:
                    ORA-02085: database link TUMS.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to HO.WORLD

                    HO.WORLD is the default, but TUMS.REGRESS.RDBMS.DEV.US.ORACLE.COM was expected. So you need to configure the parameters HS_DB_NAME and DOMAIN:
                    HS_DB_NAME = TUMS
                    HS_DB_DOMAIN = REGRESS.RDBMS.DEV.US.ORACLE.COM
                    • 7. Re: Trying to set up new hs from 10g to SQL Server 200X
                      510477
                      Okay, that got me closer, I think. Here is the initTUMS.ora contents after the above adjustments:
                      HS_FDS_CONNECT_INFO = TUMS
                      HS_FDS_TRACE_LEVEL = OFF
                      HS_AUTOREGISTER = true
                      HS_DB_NAME = TUMS
                      HS_DB_DOMAIN = REGRESS.RDBMS.DEV.US.ORACLE.COM
                      When I try and run a simple query, this is what I get:
                      SQL> select count(*) from PRHeader@TUMS;
                      select count(*) from PRHeader@TUMS
                                                    *
                      ERROR at line 1:
                      ORA-02019: connection description for remote database not found
                      • 8. Re: Trying to set up new hs from 10g to SQL Server 200X
                        Kgronau-Oracle
                        2019 means the database link (in your case named TUMS) isn't found. Maybe you've dropped it or the user can't access it.
                        Try to recreate it: create database link tums connect to ... identified by .... using 'TUMS';
                        • 9. Re: Trying to set up new hs from 10g to SQL Server 200X
                          510477
                          Thanks. That did the trick.

                          I really appreciate your help.
                          • 10. Re: Trying to set up new hs from 10g to SQL Server 200X
                            nb*427594*su
                            in the database from where you run the select (and where you created the database link), modify the initialization parameter global_names to 'FALSE'