1 Reply Latest reply: Jul 26, 2012 1:56 PM by 510477 RSS

    Getting error trying to query db through HS connection

    510477
      I am trying to set up a new heterogenous services connection to mimic one on a 10gr2 server. I went through all the trouble of setting everything up, including listener and found out I had to use DG4ODBC instead on 11.2 as they had axed the 10.2 program. So here's my configuration:

      Platform: Windows 2008 Server 64-bit hosting Oracle 11.2.0.1
      Target: Windows 2008 Server hosting SQL Server 2008
      ORACLE_HOME: E:\Oracle\product\11.2.0\dbhome_1

      odbc set up as SYSTEM DSN, name SQLWEB
      ran Dg4odbcConfig.jar file to obtain settings files:


      [E:\Oracle\product\11.2.0\dbhome_1\hs\admin\initSQLWEB.ora]
       
       HS_FDS_CONNECT_INFO = SQLWEB
       HS_FDS_TRACE_LEVEL=OFF
       #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
      [E:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora]
      # tnsnames.ora Network Configuration File: E:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.
      
      TSA =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = TSORACLE1)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = TSA)
          )
        )
      
      SQLWEB= 
       (DESCRIPTION = 
       (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.168.212)(port=1523)) 
        (CONNECT_DATA = 
         (SID = SQLWEB) 
        ) 
        (HS=OK) 
       )
      (note that 192.168.168.212 is IP for host TSORACLE1)


      tnsping:
      Microsoft Windows [Version 6.1.7601]
      Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
      
      C:\Users\Administrator>tnsping SQLWEB
      
      TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-JUL-2
      012 12:43:42
      
      Copyright (c) 1997, 2010, Oracle.  All rights reserved.
      
      Used parameter files:
      E:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.168.21
      2)(port=1523)) (CONNECT_DATA = (SID = SQLWEB)) (HS=OK))
      OK (0 msec)
      
      C:\Users\Administrator>
      [E:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora]
      # listener.ora Network Configuration File: E:\Oracle\product\11.2.0\grid\network\admin\listener.ora
      # Generated by Oracle configuration tools.
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = TSORACLE1)(PORT = 1521))
          )
        )
      
      ADR_BASE_LISTENER = E:\Oracle
      
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON          # line added by Agent
      
      LISTENER_DYNAMICS =
       (ADDRESS_LIST=
            (ADDRESS=(PROTOCOL=tcp)(HOST=TSORACLE1)(PORT=1522))
       )
      
      SID_LIST_LISTENER_DYNAMICS=
        (SID_LIST=
            (SID_DESC=
               (SID_NAME=DYNAMICS)
               (ORACLE_HOME=E:\Oracle\product\11.2.0\grid)
               (PROGRAM=dg4odbc)
             )
         )
      
      LISTENER_SQLWEB =
       (ADDRESS_LIST=
            (ADDRESS=(PROTOCOL=tcp)(HOST=TSORACLE1)(PORT=1523))
       )
      
      SID_LIST_LISTENER_SQLWEB=
        (SID_LIST=
            (SID_DESC=
               (SID_NAME=SQLWEB)
               (ORACLE_HOME=E:\Oracle\product\11.2.0\grid)
               (PROGRAM=dg4odbc)
             )
         )
      LSNRCTL> status LISTENER_SQLWEB
      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=TSORACLE1)(PORT=1523))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER_SQLWEB
      Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
      ction
      Start Date                26-JUL-2012 12:15:26
      Uptime                    0 days 0 hr. 29 min. 10 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   E:\Oracle\product\11.2.0\dbhome_1\network\admin\listen
      er.ora
      Listener Log File         e:\oracle\diag\tnslsnr\TSORACLE1\listener_sqlweb\alert
      \log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TSORACLE1)(PORT=1523)))
      Services Summary...
      Service "SQLWEB" has 1 instance(s).
        Instance "SQLWEB", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      I am getting the following error when trying to query from the table:
      C:\Users\Administrator>sqlplus /nolog
      
      SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 12:46:04 2012
      
      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
      
      SQL> connect apps
      Enter password:
      Connected.
      SQL> select * from mancamp_location@sqlweb;
      select * from mancamp_location@sqlweb
                                     *
      ERROR at line 1:
      ORA-28513: internal error in heterogeneous remote agent
      ORA-02063: preceding line from SQLWEB
      
      
      SQL>
      Any suggestions?