1 2 3 Previous Next 44 Replies Latest reply: Aug 8, 2012 12:51 PM by Dharma_ Go to original post RSS
      • 30. Re: Create DB-Link for Oracle Database 11g to IBM DB2
        Mkirtley-Oracle
        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_
          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_
            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
              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_
                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-Oracle
                  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_
                    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_
                      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-Oracle
                        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_
                          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-Oracle
                            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_
                              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-Oracle
                                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_
                                  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_
                                    Dear klaus & Mike,

                                    Thanks for your kind support.

                                    Regards

                                    Dharma
                                    1 2 3 Previous Next