Forum Stats

  • 3,770,140 Users
  • 2,253,075 Discussions
  • 7,875,340 Comments

Discussions

Configure Heterogeneous Connectivity between SQL Server x Oracle

135

Comments

  • DenisGomes
    DenisGomes Member Posts: 77
    Hi kgronau,
    How can i start the Oracle 11g listener at the Oracle 10g installation?
    Or, can i use two listeners at the same machine and with the different versions?

    Thanks,
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    You have to use 2 separate listeners. For 10g a 10g listener and for 11g the 11g listener. To start the database listener, you first need to source your Oracle database environment. When you want to start the gateway listener, then source the gateway environment.
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi Kgronau,
    Do you know how to use this listener?
    I tried to alter the TNS_ADMIN variable, but didn't work. I tried to use the paths at the LD_LIBRARY_PATH, but didn't work too.

    Before
    TNS_ADMIN=/oracle/TSA/102_64/network/admin

    After
    TNS_ADMIN=/oracle/TSA/112_64/network/admin


    Listener
    ################
    # 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)
    )
    )

    LISTENER2=
    (DESCRIPTION=
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528))))

    SID_LIST_LISTENER2=
    (SID_LIST=
    (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:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)
    )
    )

    Thanks for your help.
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    to source the environment for a gateway the minimum settings are:

    TNS_ADMIN=/oracle/TSA/112_64/network/admin
    export TNS_ADMIN
    ORACLE_SID=dg4odbc
    export ORACLE_SID

    ORACLE_HOME=/oracle/TSA/112_64
    export ORACLE_HOME
    PATH=$ORACLE_HOME/bin:$PATH
    export PATH
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$LD_LIBRARY_PATH
    export LD_LIBRARY_PATH
    cd $ORACLE_HOME

    once set you can now use the 11.2 lsnrctl utility to start the 11.2 listener: lsnrctl start LISTENER2
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Nov 30, 2011 8:39AM
    Hi Kgronau,
    Thanks again.
    I adjusted this environment variables for a user that a called oracle. The listener worked with the Oracle 11g.

    [email protected]:/oracle/TSA/112_64:#lsnrctl status listener2

    LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 30-NOV-2011 11:35:07

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528)))
    STATUS of the LISTENER
    ------------------------
    Alias listener2
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
    Start Date 30-NOV-2011 11:29:01
    Uptime 0 days 0 hr. 6 min. 6 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP ON
    Listener Parameter File /oracle/TSA/112_64/network/admin/listener.ora
    Listener Log File /oracle/TSA/112_64/network/log/listener2.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528)))
    Services Summary...
    Service "hscapta" has 1 instance(s).
    Instance "hscapta", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    [email protected]:/oracle/TSA/112_64:#tnsping capta.world

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 30-NOV-2011 11:35:26

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

    Used parameter files:


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528)) (CONNECT_DATA= (SID = hscapta)) (H
    S = OK)))
    OK (0 msec)

    unbru10:oratsa 3> ps -ef | grep lsn
    oratsa 10879128 5701858 1 11:29:39 pts/0 0:00 grep lsn
    oratsa 13828300 1 0 10:30:18 pts/1 0:00 /oracle/TSA/102_64/bin/tnslsnr listener -inherit
    oracle 14811158 1 0 11:29:00 pts/0 0:00 /oracle/TSA/112_64/bin/tnslsnr listener2 -inherit


    Now, i created the dblink and tried to execute the test.

    select * from [email protected]"CAPTA.WORLD"

    ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
    12504. 00000 - "TNS:listener was not given the SID in CONNECT_DATA"
    *Cause: The SID was missing from the CONNECT_DATA.
    *Action: Check that the connect descriptor corresponding to the service
    name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.
    Error at Line: 1 Column: 19

    Tnsnames.ora at the /oracle/TSA/112_64/network/admin
    [email protected]:/oracle/TSA/112_64/network/admin:#more tnsnames.ora
    CAPTA.WORLD=
    (DESCRIPTION=
    (ADDRESS_LIST =
    (ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528))
    (CONNECT_DATA= (SID = hscapta))
    (HS = OK)
    )
    )

    Thanks very much.
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    The Oracle database is 10.2 so it will use the tnsnames.ora which belongs to the 10.2 environment.
    Please source the database environment, then use the 10.2 tnsping to check the alias: tnsping capat.world
  • DenisGomes
    DenisGomes Member Posts: 77
    Kgronau,
    I tried this:


    unbru10:oratsa 1> tnsping capta.world

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 11:42:04

    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 = unbru10) (PORT = 1528)) (CONNECT_DATA= (SID = hscapta)) (HS = OK)))
    OK (0 msec)
    unbru10:oratsa 2>


    Thanks,
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    just saw it - there a mistake in the tnsnames.ora
    change your entry to:

    CAPTA.WORLD=
    <space>(DESCRIPTION=
    <space><space>(ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528))
    <space><space>(CONNECT_DATA=
    <space><space><space> (SID = hscapta))
    <space><space>(HS = OK)
    <space>)


    without address_list and the closing bracket.
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Nov 30, 2011 11:05AM
    Kgronau,
    Now, when i try to execute the command select * from [email protected]"CAPTA.WORLD", I received the error:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    ORA-02063: preceding line from CAPTA
    28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
    *Cause: The cause is explained in the forwarded message.
    *Action: See the non-Oracle system's documentation of the forwarded
    message.
    Error at Line: 1 Column: 28

    Thanks,
    Denis
  • Denis,
    It looks like you are now getting to the SQL*Server database and that is then returning an error.
    Could you set up debug tracing by setting the following in the gateway init<sid>.ora file -

    HS_FDS_TRACE_LEVEL=debug

    and run the select from a new SQLPLUS session. The trace should show the error being returned from SQL*Server.
    What is the full version of the ODBC driver you are using ?

    Regards,
    Mike
This discussion has been closed.