This discussion is archived
1 Reply Latest reply: Jul 26, 2012 11:56 AM by 510477 RSS

Getting error trying to query db through HS connection

510477 Pro
Currently Being Moderated
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?

Legend

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