Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Configure Heterogeneous Connectivity between SQL Server x Oracle

245

Comments

  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgrounau,
    Thanks for your replies.
    I observed that I have the Aix 64bits with Oracle 10g 64bits and Windows Server 2003 32bits with Sql Server 2005 32bits. Is that probaly my problem?
    I will try to configure this feature with Windows Server and Sql Server 64bits. If this is not work I will try the ODBC downloaded from EasySoft.

    Thanks
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Hi Denis,
    no, that's not a problem. Just install DG4ODBC on your AIX into a separate ORACLE_HOME, install a 64bit ODBC driver on your AIX, configure it and you're done.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgronau,
    Thanks again for your replies.
    When I try to connect in SQL Server I receive the error:

    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from GTWCAPTA
    *28545. 0000 - "error diagnosed by Net8 when connecting to an agent"*
    **Cause: An attempt to call an external procedure or to issue SQL*
    to a non-Oracle system on a Heterogeneous Services database link
    failed at connection initialization. The error diagnosed
    by Net8 NCR software is reported separately.
    **Action: Refer to the Net8 NCRO error message. If this isn't clear,*
    check connection administrative setup in tnsnames.ora
    and listener.ora for the service associated with the
    Heterogeneous Services database link being used, or with
    *'extproc_connection_data' for an external procedure call.*
    Error at Line: 1 Column: 19

    Thanks for your help.

    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Denis,
    ORA-28545 is a configuration mismatch.
    Could you please post your gateway listener.ora file, your tnsnames.ora file located in the Oracle database and finally the listener status of the gateway listener (lsnrctl status <gateway listener name>) and a tnsping output using the configured tns alias from your Oracle database home (tnsping <gateway tn alias>).
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Oct 25, 2011 6:42AM
    Hi Kgronau,
    Thanks again.
    Follows the output.

    listener.ora
    ################
    # Filename......: listener.ora
    # Created.......: created by SAP AG, R/3 Rel. >= 4.0A
    # Name..........:
    # Date..........:
    ################
    LISTENER =
    (ADDRESS_LIST =
    (ADDRESS=
    (PROTOCOL=IPC)
    (KEY= TSA.WORLD)
    )
    (ADDRESS=
    (PROTOCOL=IPC)
    (KEY= TSA)
    )
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = unbru10)
    (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 = TSA)
    (ORACLE_HOME = /oracle/TSA/102_64)
    )
    (SID_DESC=
    (SID_NAME=GtwCapta)
    (ORACLE_HOME=/oracle/TSA/102_64/dg4odbc)
    (PROGRAM=dg4odbc)
    )

    )

    tnsnames.ora
    ################
    # Filename......: tnsnames.ora
    # Created.......: created by SAP AG, R/3 Rel. >= 4.0A
    # Name..........:
    # Date..........:
    ################
    SATP.WORLD =
    (DESCRIPTION =
    (SDU = 32768)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.172.18)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = satp)
    (GLOBAL_NAME = SATP.WORLD)
    )
    )


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


    GtwCapta.WORLD =
    ( DESCRIPTION =
    ( ADDRESS_LIST =
    ( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))
    )
    (CONNECT_DATA=(SID=GtwCapta))
    (HS=OK)
    )

    lsnrctl status
    unbru10:oratsa 2> lsnrctl status

    LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:33:09

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

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TSA.WORLD))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
    Start Date 19-OCT-2011 09:05:31
    Uptime 5 days 23 hr. 27 min. 38 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP ON
    Listener Parameter File /oracle/TSA/102_64/network/admin/listener.ora
    Listener Log File /oracle/TSA/102_64/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA.WORLD)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527)))
    Services Summary...
    Service "GtwCapta" has 1 instance(s).
    Instance "GtwCapta", status UNKNOWN, has 1 handler(s) for this service...
    Service "TSA" has 1 instance(s).
    Instance "TSA", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    lsnrctl status <gateway listener name>
    unbru10:oratsa 4> lsnrctl status GtwCapta

    LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:34:18

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

    TNS-01101: Could not find service name
    unbru10:oratsa 5> lsnrctl status GtwCapta.World

    LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:34:33

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))(CONNECT_DATA=(SID=GtwCapta))(HS=OK))
    TNS-01189: The listener could not authenticate the user

    tnsping gtwcapta
    unbru10:oratsa 6> tnsping gtwcapta

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:35:35

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

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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))) (CONNECT_DATA=(SID=GtwCapta)) (HS=OK)
    )
    OK (0 msec)

    initGtwCapta.ora
    [email protected]:/oracle/TSA/102_64/hs/admin:#more initGtwCapta.ora
    # This is a sample agent init file that contains the HS parameters that are
    # needed for an ODBC Agent.

    #
    # HS init parameters
    #Its my ODBC
    HS_FDS_CONNECT_INFO = DBCaptaCont
    HS_FDS_TRACE_LEVEL = ON
    HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

    #
    # ODBC specific environment variables
    #
    set ODBCINI=<full path name of the odbc initilization file>


    #
    # Environment variables required for the non-Oracle system
    #
    set <envvar>=<value>

    Thanks,
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Denis,
    there are a couple of issues:

    Let me start with your listener:
    It is listening on (HOST = unbru10) (PORT = 1527) and serves the SID
    (SID_NAME=GtwCapta)
    (ORACLE_HOME=/oracle/TSA/102_64/dg4odbc)
    (PROGRAM=dg4odbc)

    It is very risky to use the 10.2 listener to start the 11g gateway which resides in a different home (/oracle/TSA/102_64/dg4odbc).

    You've also tested the tnsping successfully:
    tnsping gtwcapta
    ...
    Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))) (CONNECT_DATA=(SID=GtwCapta)) (HS=OK))

    But look at the address information - it does not match!

    You're trying to connect to a different host!
    Listener:(HOST = unbru10) (PORT = 1527)
    Tnsnames:(HOST=10.20.172.63)(PORT=1521)

    That won't work. Although tnsping reports a successful test in fact it wasn't. TNSPING is only looking for a listener listening on a certain port - but it does not check for the SID. So with the address information you've specified in the tnsnames.ora you've accidentally hit an Oracle listener, but who know what this listener is serving...

    Also have a look at the gateway init file initGtwCapta.ora configuration you've posted:
    1. it seems it is in the database home - not in the gateway hone within hs/admin - see he shell output:/oracle/TSA/102_64/hs/admin:#more
    Now to the config itself. You've specified the odbc.ini contains a DSN entry called DBCaptaCont - but you didn't specify the ODBCINI parameter which points to the odbc.ini file that contains this entry: set ODBCINI=<full path name of the odbc initilization file>

    You also didn't specify the ODBC Driver Manager which is mandatory on Unix: HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>




    in your previous update you've stated you get the ORA-28545 error. this error is happening when the Oracle SQL*Net configuration doesn't match. As explained earlier the listener and tnsnames entries (host & port) do not match. If you are really sure that you have set up DG4ODBC on this unbru10 host, then you have to correct these parameters so that they match. For example change the tnsnames.ora entry to:
    GtwCapta.WORLD =
    ( DESCRIPTION =
    ( ADDRESS_LIST =
    ( ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527))
    )
    (CONNECT_DATA=(SID=GtwCapta))
    (HS=OK)
    )

    Unfortunately it is not sufficient - you also need to modify your listener. You have to specify the ODBC library directory in the listener and for the DG4ODBC executable the full path (because you are using a 10g listener to spawn the 11g gateway)....

    From my perspective it would be best to check out the My Oracle Support note How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install [Document 561033.1] again and start from scratch.

    I would also recommend to log a service request as this would allow us to assist you online.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgronau,
    I verified that i don't have the libodbc.ora.

    [email protected]:/:#find / -name odbc.ini
    [email protected]:/:#find / -name libodbc.so
    [email protected]:/:#find / -name libodbc.

    What happened? I installed the ODBC with no error.
    I verify too (http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHEGGIF), how you can said, i need to adjust the HS_FDS_SHAREABLE_NAME=full_path_of_odbc_driver. I don't have this libs.

    Thanks
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    On Unix you commonly need an ODBC driver Manager and an ODBC driver. Only a few ODBC drivers (for example IBM) include the driver manager code in the odbc driver. But most of the vendors ship a separate CD or rely on a 3rd party ODBC driver manager library.

    When you don't have a driver manager installed, then install the free ODBC driver manager from www.unixodbc.org.
    In addition not every ODBC installation is creating an odbc.ini file. You need to check out the ODBC vendor's documentaion to see which parameters you have to specify in the odbc.ini file.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgronau, what's up?
    I followed your advice, I've already had installed the EasySoft Sql Server ODBC Driver and the Oracle Database Gateway.
    How you advice me, I installed the DG4ODBC in another $ORACLE_HOME. My Oracle 10g in /oracle/TSA/102_64 and my DG4ODBC in /oracle/TSA/112_64.
    At the Windows Server 2003 with Sql Server 2005, I already configured the ODBC Data Source.
    Now i get an error and i need your help.

    Message:
    unbru10:oratsa 5> lsnrctl status

    LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 09:18:01

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

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TSA.WORLD))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
    Start Date 30-NOV-2011 09:02:19
    Uptime 0 days 0 hr. 15 min. 42 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP ON
    Listener Parameter File /oracle/TSA/102_64/network/admin/listener.ora
    Listener Log File /oracle/TSA/102_64/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA.WORLD)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527)))
    Services Summary...
    Service "TSA" has 1 instance(s).
    Instance "TSA", status UNKNOWN, has 1 handler(s) for this service...
    Service "hscapta" has 1 instance(s).
    Instance "hscapta", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    unbru10:oratsa 6> tnsping capta.world

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 09:18:07

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

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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432)) (CONNECT_DATA= (SID = hscapta)) (
    HS = OK)))
    TNS-12537: TNS:connection closed



    Listener
    unbru10:oratsa 4> more listener.ora

    ################
    # Filename......: listener.ora
    # Created.......: created by SAP AG, R/3 Rel. >= 4.0A
    # Name..........:
    # Date..........:
    ################
    LISTENER =
    (ADDRESS_LIST =
    (ADDRESS=
    (PROTOCOL=IPC)
    (KEY= TSA.WORLD)
    )
    (ADDRESS=
    (PROTOCOL=IPC)
    (KEY= TSA)
    )
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = unbru10)
    (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 = TSA)
    (ORACLE_HOME = /oracle/TSA/102_64)
    )
    (SID_DESC=
    (SID_NAME=hscapta)
    (ORACLE_HOME=/oracle/TSA/112_64)
    (PROGRAM=dg4odbc)
    (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib)
    )
    )

    Tnsnames
    unbru10:oratsa 7> more tnsnames.ora

    ################
    # Filename......: tnsnames.ora
    # Created.......: created by SAP AG, R/3 Rel. >= 4.0A
    # Name..........:
    # Date..........:
    ################
    SATP.WORLD =
    (DESCRIPTION =
    (SDU = 32768)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = satp)
    (GLOBAL_NAME = SATP.WORLD)
    )
    )


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

    CAPTA.WORLD=
    (DESCRIPTION=
    (ADDRESS_LIST =
    (ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432))
    (CONNECT_DATA= (SID = hscapta))
    (HS = OK)
    )
    )

    */etc/Odbc.ini*
    [CAPTA_CONT]
    Driver=Easysoft ODBC-SQL Server
    Description=Easysoft SQL Server ODBC driver
    Server=192.168.10.20
    Port=1432
    Database=sefunmp
    User=sa
    Password=adm
    Mars_Connection=YES
    Logging=No
    LogFile=
    QuotedId=Yes
    AnsiNPW=Yes
    Language=
    Version7=No
    ClientLB=No
    Failover_Partner=
    VarMaxAsLong=No
    DisguiseWide=No
    DisguiseLong=No
    DisguiseGuid=No
    Trusted_Connection=No
    Trusted_Domain=
    IPv6=No


    Thanks for your great help.
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Denis,
    your listener.ora is wrong.

    Your output shows:
    Alias LISTENER
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production

    => so you're using a 10.2 listener but you're trying to use a 11.2 gateway (ORACLE_HOME=/oracle/TSA/112_64)

    Commonly it is not supported that an Oracle listener of a lower version spawns a connection to a higher release - Oracle us downwards compatible but not upwards. Best would be to configure a separate listener for the 11.2 gateway in the 11.2 gateway home listening on its own port.


    Next wrong config is the tnsnames.ora. YOU have configured (ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432))
    The connect flow of DG4ODBC is the Oracle database wants to connect to the foreign database, hence it connects to the listener that knows dg4odbc. This connection is done using SQL*Net. So the tnsnames.ora entry of the Oracle database needs to point to the oracle listener that spawns the dg4odbc process which is currently running on unbru10 and listening on port 1527 (but should be changed according to my first comment).

    To be complete DG4ODBC is then loading the ODBC driver and the ODBC driver connects to the foreign database - so the only location where you have to configure the SQL Server connect details is in the odbc.ini.
This discussion has been closed.