Forum Stats

  • 3,770,503 Users
  • 2,253,127 Discussions
  • 7,875,489 Comments

Discussions

Configure Heterogeneous Connectivity between SQL Server x Oracle

DenisGomes
DenisGomes Member Posts: 77
edited Dec 2, 2011 6:52AM in Heterogeneous Connectivity
Hi Gurus,
I had been configuring the heterogeneous connectivity between SQL Server 2005 (Windows Server 2003) and Oracle 10.2.0.4.0 (AIX 6.1).
I found a lot of guides and some of them, indicated that i needed to configure only ODBC and files listener.ora, tnsnames.ora e init<sid>.ora. In other hand, some of them, indicated that i need to install a software in WinServer 2003 to this procedure.
I configured through ODBC in WinServer 2003 and adjusted the files listener.ora, tnsnames.ora e init<sid>.ora in Aix.
Now, when i try to ping by tnsping the Windows Server, i receive the output.

tnsping dbcapta.world

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 20-SEP-2011 15:22:07

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
*/oracle/PRD/102_64/network/admin/sqlnet.ora*


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521)) (CONNECT_DATA = (SID = DBCaptaCont)) (HS = OK))
TNS-12541: TNS:no listener


Listener

LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= PRD.WORLD)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= PRD)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = machine)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = PRD)
(ORACLE_HOME = /oracle/PRD/102_64)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=DBCAPTACONT)
(ORACLE_HOME = /oracle/PRD/102_64)
(PROGRAM=hsodbc)
)
)

TNSNAMES
PRD.WORLD=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = machine)
(PORT = 1527)
)
)
(CONNECT_DATA =
(SID = PRD)
(GLOBAL_NAME = PRD.WORLD)
)
)

DBCAPTA.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = xxx.xxx.xxx.xxx) (PORT = 1521))
(CONNECT_DATA =
(SID = DBCaptaCont))
(HS = OK)
)

Could you help us?
Is this the correct procedure?Or. do we need to install a software in Windows Server?

Thanks,
Denis
Brazil
«1345

Comments

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Hi Denis,
    let me first refer to your configuration as it contains a major issue with the port numbers and that's why tnsping fails:
    In the listener.ora you use port 1527 whereas in the tnsnames.ora you use 1521 (assuming that the hostname in your listener and tnsnames.ora match).
    The tnsping is like just checking if there's a listener listening on the port and host specified in the tnsnames.ora so the host and port number must match.


    I also don't want to miss to clarify some statements in your introduction:
    I found a lot of guides and some of them, indicated that i needed to configure only ODBC and files listener.ora, tnsnames.ora e init<sid>.ora.
    => That's correct
    In other hand, some of them, indicated that i need to install a software in WinServer 2003 to this procedure
    => it depends WHERE you install the gateway

    So now in details:
    With 10.2.0.4 Oracle shipped by default a component called HSODBC which allows you to connect to a foreign database using a foreign ODBC driver. HSODBC was bundled with the Oracle database and you had to use HSODBC on the same platform from the same home as your Oracle database release.
    BUT HSODBC was desupported in March 2008 and replaced by a follow up product called Database Gateway for ODBC (=DG4ODBC) which is now more flexible then HSODBC. DG4ODBC can for example be installed independently from the Oracle database - even on its own machine. DG4ODBC is availoable as release 11.2.0.2 and supported with Oracle database 10.2.0.4.

    So my advise would be to use DG4ODBC rather then using the desupported HSODBC product. As mentioned earlier, DG4ODBC is independent from the Oracle database which gives you more flexibility.
    For example you can install DG4ODBC on your Oracle database AIX box, then you will need a suitable 3rd party ODBC driver manager and ODBC driver (for AIX) which is able to connect to the SQL Server.
    A note how to configure DG4ODBC is available on MOS: How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install [Document 561033.1]

    Taking care of another sentence in your introduction:
    I configured through ODBC in WinServer 2003 and adjusted the files listener.ora, tnsnames.ora e init<sid>.ora in Aix.
    gives us a second opportunity:
    You already have an ODBC driver configured on your Windows box. The init<sid>.ora and the listener entries on the AIX box can be ignored - they are useless as you don't have an ODBC driver on your AIX box.
    When you want to use the existing ODBC configuration, then you need to download DG4ODBC for Windows and configure it according to note:
    For 32bit Windows, please use:
    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install [Document 466225.1]
    For 64bit Windows, please use:
    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install [Document 1266572.1]


    Like HSODBC the license for DG4ODBC is included in your Oracle database license - even when you install it on a separate machine. So when you now configure DG4ODBC on Windows, you need to set up on the Windows box the ODBC driver, the gateway init file and a listener. On the Oracle database side then edit the tnsnames.ora and make sure that you now refer to the listener running on the Windows box.


    Finally you may ask where you get DG4ODBC from ... You can download it from My Oracle Support (=MOS), from OTN or from edelivery.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgrounau,
    Thanks for your reply. I believe that this recommendation might resolve our problems, but how can i download this DG4ODBC?
    We have only a SAP contract and i don't have access to Oracle Metalink and My Oracle Support. For example, when we want to apply patches in Oracle, we have to install patches that SAP recommends and releases for download.

    Thanks very much.
    Denis
    Brazil
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Look at OTN:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

    There's not the latest release (it is only available through MOS), but the base 11.20.1 release which will work as well.

    Look for 11.2.0.1 and your platform, then click on "see all" link right behind the platform. For every platform where the gateways are available, you'll find Oracle Database Gateways 11g Release 2 (11.2.0.1.0) - download it and make sure you install the free component Database Gateway for ODBC (=DG4ODBC).
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgrnoau,
    Thanks very much for your reply.I believe that this is the most valuable reply i already received in this forum.
    I'm downloading the DG4ODBC for Aix and Windows Server. I will post the result and the solution.

    Thanks very much.
    Denis
    Brazil
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi Kgrnoau,
    I downloaded the DG4ODBC for non-oracle database. I already installed in AIX and i have a problem.
    I get the error when i execute the relink command.

    Starting Oracle Universal Installer...

    Checking swap space: must be greater than 500 MB. Actual 20480 MB Passed
    Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-10_10-32-51AM. Please wait ...libjvm.so preloadLibrary(/tmp/OraInstall2011-
    *10-10_10-32-51AM/jre/bin/libjava.a): 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a.*
    *0509-103 The module has an invalid magic number.*
    libjvm.so preloadLibrary(/tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a): 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51
    AM/jre/bin/libjava.a.
    *0509-103 The module has an invalid magic number.*
    *<error: unable to load java ( 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a.*
    *0509-103 The module has an invalid magic number.)>*
    JVMJ9VM015W Initialization error for library jclscar_23(14): JVMJ9VM009E J9VMDllMain failed

    Could you help us?

    Aix 6.1
    Oracle 10g

    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Denis,
    hope you didn't install the 11g gateway software into an Oracle database 10g home - as this won't work because the libraries are completely different.
    So can you please be more precise - where did you install which product?

    When you state you have an issue with the relink command- which product do you want to relink, the gateway or the database? In which home does it reside? In its own or in the database home?
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Oct 10, 2011 2:57PM
    Hi kgronau,
    Thanks for your reply.
    I installed the Oracle database and gateway in the same path (/oracle/TSA/102_64). I read in this forum, guys indicate that we have to install the gateway in the different location of the Oracle Database 10g. Do you agree with this?
    I accessed the $ORACLE_HOME/bin and tried the relink. In the same case, if I try to execute the dbca, i got the same error.
    Now, I decide to continue the configuration and i get another error.
    I want to access the SQL Server (WinServer 2003 32bits) by Oracle Database (Aix 64bits).
    Do you believe that this error is caused because the odbc are different platforms ?


    ORA-28513: internal error in heterogeneous remote agent
    ORA-02063: preceding line from GTWCAPTA
    *28513. 00000 - "internal error in heterogeneous remote agent"*
    **Cause: An internal error has occurred in the Oracle remote agent*
    supporting a heterogeneous database link.
    **Action: Make a note of how the error was produced and contact the*
    customer support representative of the agent vendor.
    Error at Line: 1 Column: 19

    Thanks very much
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    When installing 11g software into an existing 10g home, then you have corrupted your current installation. The libraries shipped with 11g are different from 10g and thus this won't work.
    Best would be restore from a backup the 10g Oracle_Home (only the software, a db restore isn't required). If you don't have a backup from your database home, deinstall the gateway, install the 10g software and apply all previous applied patches again.

    As I tried to explain, when you install DG4ODBC on AIX, then you need to have the ODBC driver on the AIX platform. When you only have an ODBC driver for Windows, then you have to install DG4ODBC for Windows on the machine where you have the ODBC driver installed. DG4ODBC requires the ODBC driver and needs to load the ODBC driver libraries. So it needs to be on the same machine on which your ODBC driver resides.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi Kgronau,
    Thanks again.
    I restored the Oracle binaries .
    I have a question, What tool do i need to install in Aix? When i started to install in Aix, i can see six or more options, for example, Oracle Database Gateway for Informix, for Sybase, for Microsoft Sql server, for Teradata, for APPC, for WebSphere, for ODBC...
    How i want to connect the Oracle to Sql Server, Do i need to install the Oracle Database Gateway for Microsoft Sql Server in Aix (Oracle) and the Oracle Database Gateway for Oracle in Windows (Sql Server)?

    Thanks very much.

    Denis
    Brazil
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    All these gateways Oracle Database Gateway for Informix, for Sybase, for Microsoft Sql server, for Teradata, for APPC, for WebSphere require an additional support license - they are NOT for free.

    The only gateway which is for free is the Oracle Database Gateway for ODBC.
    When you install this gateway into its own Oracle_home directory on your AIX box, then you need a 64bit ODBC driver which is able to connect to your SQL Server. Common ODBC vendors who sell this kind of driver are DataDirect, Openlink and Easysoft.
    When you decide to use the Database Gateway for ODBC on the AIX box, then you don't need to install any software in the Windows machine. All components must reside on the AIX box (the gateway and the ODBC driver).

    If you want to use a solution completely for free, then you have to use Database Gateway for ODBC on the WINDOWS platform. On this machine you need to make sure that you have installed also the Microsoft MS SQL Server ODBC driver which is available for free on Windows from Microsoft.

    You can't install DG4ODBC on AIX and use the MS SQl Server ODBC driver on Windows. That does NOT work. The ODBC driver is establishing the connection to the foreign database and it is loaded by DG4ODBC. So it must reside on the same machine where you install DG4ODBC.
This discussion has been closed.