This discussion is archived
1 2 3 Previous Next 44 Replies Latest reply: Aug 8, 2012 10:51 AM by Dharma_ Go to original post RSS
  • 30. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    The ora-28545 error is usually a configuration problem.
    The listener.ora posted previously showed listener_dev was using port 50000 - is this still the case ?

    For listener_dev for sid NILGDB2 you have -

    (ENVS=LD_LIBRARY_PATH = /home/oradev/usr/local/lib:/VIS/DEV/db/tech_st/11.1.0/lib)

    but the shareable name libodbc.so driver manager according to the gateway init file is in the directory -

    /Oracle/DBlink_RPM/Software/usr/local/lib

    and the DB2 driver in the directory -

    /opt/IBMDB2_Driver/clidriver/lib

    so can you change it to -

    (ENVS=LD_LIBRARY_PATH =/Oracle/DBlink_RPM/Software/usr/local/lib:/opt/IBMDB2_Driver/clidriver/lib:/VIS/DEV/db/tech_st/11.1.0/lib)

    Also, they may have been lost in the post but there should be a space at the beginning of eacjh line of lsitener.ora and tnsnames.ora exceept the first -

    SID_LIST_DEV =
    <space>(SID_LIST =
    <space>(SID_DESC =
    <space>(ORACLE_HOME= /VIS/DEV/db/tech_st/11.1.0)
    <space>(SID_NAME = DEV)
    <space>)
    <space>(SID_DESC=
    <space>(SID_NAME = NILGDB2)
    <space>(ORACLE_HOME= /VIS/DEV/db/tech_st/11.1.0/)
    <space>(PROGRAM = dg4odbc)
    (ENVS=LD_LIBRARY_PATH = /home/oradev/usr/local/lib:/VIS/DEV/db/tech_st/11.1.0/lib)
    <space>)
    <space>)

    NILGDB2=
    <space>(DESCRIPTION =
    <space>(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.245)(port=50000))
    <space>(CONNECT_DATA =
    <space>(SID = NILGDB2)
    <space>)
    <space>(HS=OK)
    <space>)

    If you still have problems then post the complete files again and the output from the 'services' dsiplay for listener_dev.

    TNSPING is using the parameter file sqlnet_ifile.ora - does this have a pointer to tnsnames.ora ?

    Regards,
    Mike
  • 31. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai mike,

    Thanks for replay,

    I have changed,whatever u said,in the perivous post,But still i got error,
    S mike,50000 port still in this case.
    I will post all configuration file,

    ****Listener.ora****

    DEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.14)(PORT = 1526))
    )
    )

    SID_LIST_DEV =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME= /VIS/DEV/db/tech_st/11.1.0)
    (SID_NAME = DEV)
    )
    (SID_DESC=
    (SID_NAME = NILGDB2)
    (ORACLE_HOME= /VIS/DEV/db/tech_st/11.1.0/)
    (PROGRAM = dg4odbc)
    (ENVS=LD_LIBRARY_PATH = /Oracle/DBlink_RPM/ODBC_Driver/odbc_cli/clidriver/lib:/VIS/DEV/db/tech_sh /11.1.0/lib)
    )
    )

    ****tnsnames.ora****

    DEV=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.14)(PORT=1526))
    (CONNECT_DATA=
    (SERVICE_NAME=DEV)
    (INSTANCE_NAME=DEV)
    )
    )
    NILGDB2=
    (DESCRIPTION =
    (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.245)(port=50000))
    (CONNECT_DATA =
    (SID = NILGDB2)
    )
    (HS=OK)
    )

    ******initNILGD*B2.ora*******

    HS_FDS_CONNECT_INFO = dblink4db2
    HS_FDS_TRACE_LEVEL=4
    HS_FDS_TRACE_FILE = hstrace.txt
    #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
    HS_FDS_SHAREABLE_NAME = /home/oradev/usr/local/lib/libodbc.so
    set ODBCINI=/etc/odbc.ini

    ******lsnrctl status DEV******

    [oradev@testebs admin]$ lsnrctl status DEV

    LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 04-AUG-2012 19:02:42

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(PORT=1526)))
    STATUS of the LISTENER
    ------------------------
    Alias DEV
    Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
    Start Date 04-AUG-2012 16:29:09
    Uptime 0 days 2 hr. 33 min. 33 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /VIS/DEV/db/tech_st/11.1.0/network/admin/DEV_testebs/listener.ora
    Listener Log File /VIS/DEV/db/tech_st/11.1.0/admin/DEV_testebs/diag/tnslsnr/testebs/dev/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.14)(PORT=1526)))
    Services Summary...
    Service "DEV" has 2 instance(s).
    Instance "DEV", status UNKNOWN, has 1 handler(s) for this service...
    Instance "DEV", status READY, has 1 handler(s) for this service...
    Service "DEV_XPT" has 1 instance(s).
    Instance "DEV", status READY, has 1 handler(s) for this service...
    Service "NILGDB2" has 1 instance(s).
    Instance "NILGDB2", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    *******tnsping NILGDB2*******

    [oradev@testebs admin]$ tnsping NILGDB2

    TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 04-AUG-2012 19:03:35

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

    Used parameter files:
    /VIS/DEV/db/tech_st/11.1.0/network/admin/DEV_testebs/sqlnet_ifile.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.245)(port=50000)) (CONNECT_DATA = (SID = NILGDB2)) (HS=OK))
    TNS-12537: TNS:connection closed


    Please Refer my above mention configuration,if any thing wrong,guide me

    Regards
    Dharma

    Edited by: 933950 on 4 Aug, 2012 6:37 AM
  • 32. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai mike,

    I posted tnsnames.ora,listener.ora,I gv space,but after posting,it will move to nearer margin

    Regards
    Dharma
  • 33. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Dharma,
    Thanks for confirming about the spaces in the files.

    You are getting the error because you are using the wrong port in the tnsnames.ora file. You need to use the host and port of the gateway listener, not the port of the DB2 database.

    listener.ora -

    DEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.14)(PORT = 1526))
    )
    )

    - the gateway listener is using port 1526 so the tnsnames.ora entry should refer to the host and port of the listener -

    NILGDB2=
    <space>(DESCRIPTION =
    <space>(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))
    <space>(CONNECT_DATA =
    <space>(SID = NILGDB2)
    <space>)
    <space>(HS=OK)
    <space>)

    it is the gateway executable that makes the connection to DB2 after it is started by the listener process - you do not conenct directly to the Db2 database using the tnsnames.ora entry - that is used to call the gateway listener.

    Regards,
    Mike

    Edited by: mkirtley on Aug 4, 2012 4:43 PM
  • 34. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear mike & klaus,

    I have chaged,perviously post information,now through these error,

    [oradev@testebs admin]$ sqlplus apps/apps

    SQL*Plus: Release 11.1.0.7.0 - Production on Sun Aug 5 01:07:06 2012

    Copyright (c) 1982, 2008, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> desc BILLSC@ORACLE_DB2
    ERROR:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [


    Regards
    Dharma
  • 35. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    Dharma,
    please add to your gateway init file:
    HS_LANGUAGE=american_america.we8iso8859p1
    HS_NLS_NCHAR=UCS2

    then try again.
    -Klaus
  • 36. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    hai klaus,

    Thanks for ur replay,I will try and let you know,

    Regards
    Dharma
  • 37. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai klaus,

    Now its working,thanks klaus and mike.

    now i want two way communication,but i can query oracle to db2,i need also db2 to oracle,its same process or different



    Regards
    Dharma
  • 38. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    We don't have a utility that allows you to establish a connection from DB2 to Oracle (anymore). You need to use the DB2 Information Integrator, see: http://www.ibm.com/developerworks/data/library/techarticle/0306bhogal1/0306bhogal1.html


    The gateway solution is a one way tool. When you want to spend some money you might also think about using GoldenGate product which allows you to replicate datab between Oracle and DB2 in both ways.

    - Klaus



    P.S.: Please do not forget to close the thread.
  • 39. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai kalus,

    If any other way to link db2 to oracle without license?

    pls replay ASAP

    Regards
    Dharma
  • 40. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    Dharma,

    the Oracle gateways require that the Oracle database is the driving site. So the Oracle database starts the process to fetch or to insert/update/delete data at the Db2 side.

    When the DB2 database is the driving site you need to use an IBM product. The only product I know is the Information Integrator, but it would always be a good idea to ask IBM support if they offer a free tool.
  • 41. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear klaus,

    Is dg4drda.ora two way communication or not?

    suppose,we can use oracle product for 2 way communication,which is the best dg4drda or Golden Gate?

    pls ASAP

    Regards
    Dharma
  • 42. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    Dharma,
    DRDA gateway is similar to DG4ODBC, the Oracle database is the driving site and triggers all actions at the DB2 side (select/insert/update/delete).
    Golden Gate would allow you to replicate data in both ways, but it is not for free.
    More details can be found here:
    http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

    - Klaus
  • 43. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai klaus,

    thanks klaus for ur through out support,and thanks mike also.

    I have got solution,I close these thread

    Regards
    Dharma
  • 44. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear klaus & Mike,

    Thanks for your kind support.

    Regards

    Dharma
1 2 3 Previous Next

Legend

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