12 Replies Latest reply: Sep 25, 2012 7:01 AM by Kgronau-Oracle RSS

    ORA-28545 Between ORACLE 10G and SQL SERVER 2008

    964187
      Hi All,

      i'am doing heterogeneous connectivity as oracle 10G (Unbreakable linux4) to sql server (SQL SERVER 2008)

      The DSN and the connections works ok (FreeTDS and UniXODBC).

      I create the dblink

                create database link inforpyme connect to "sa" identified by " " using 'INFORPYME';

      and when I do select * from "sysobjects"@inforpyme I get the next error:

      ORA-28545: error diagnosticado por Net8 al conectar a un agente
      Unable to retrieve text of NETWORK/NCR message 65535
      ORA-02063: 2 lines precediendo a INFORPYME

      ***************************************************************************************
      This is my listener:
      IBK =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY=PNPKEY))
      )
      )
      )

      SID_LIST_IBK =
      (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = ibk)
      (ORACLE_HOME = /App/oracle/ora10g2)
      (SID_NAME = ibk)
      )
      (SID_DESC=
      (SID_NAME=inforpyme)
      (ORACLE_HOME=/App/oracle/ora10g2)
      (PROGRAM=hsodbc)
      )
      )

      ******************************************************************************************
      My tnsnames:
      INFORPYME =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME=inforpyme))(HS=OK)
      )

      The TNSping gets

      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=inforpyme))(HS=OK))
      OK (0 msec)

      ******************************************************************************************
      and the initINFORPYME.ora:
      HS_FDS_CONNECT_INFO = inforpyme
      HS_FDS_TRACE_LEVEL = debug
      HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
      HS_FDS_SHAREABLE_NAME = /usr/local/lib/libtdsodbc.so

      #
      # ODBC specific environment variables
      #
      set ODBCINI=/usr/local/etc/odbc.ini


      Anybody may help me? Thanks
        • 1. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
          Kgronau-Oracle
          Ora-28545 is a configuration issue.

          What is the word size of your oracle 10g installation, is it a 32bit or 64bit?

          What's the output when you call hsodbc from the command line:/App/oracle/ora10g2/bin/hsodbc


          BTW, HS_FDS_SHAREABLE_NAME nneds to point to the ODBC Driver Manager from unixODBC which is called libodbc.so. You configured it to point to the odbc driver library HS_FDS_SHAREABLE_NAME = /usr/local/lib/libtdsodbc.so.

          Edited by: kgronau on Sep 25, 2012 10:37 AM
          • 2. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
            964187
            The word size of my oracle 10g installation, is it a 32bits, and the word size of the SQL SERVER installation is 64bits

            ***********************************************************
            The Output of Hsodbc is

            Oracle Corporation --- TUESDAY SEP 25 2012 12:14:13.903

            Heterogeneous Agent Release 10.2.0.1.0 - Production Built with
            Driver for ODBC

            ************************************************************

            I change my initINFORPYME.ora to

            # This is a sample agent init file that contains the HS parameters that are
            # needed for an ODBC Agent.

            #
            # HS init parameters
            #
            HS_FDS_CONNECT_INFO = inforpyme
            HS_FDS_TRACE_LEVEL = debug
            HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
            HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

            #
            # ODBC specific environment variables
            #
            set ODBCINI=/usr/local/etc/odbc.ini


            but it doesn't work... the same error
            • 3. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
              Kgronau-Oracle
              ld you please post the listener status: lsnrctl status IBK

              Just as an info: HSODBC was desupported in March 2008 and replaced by a follow up product DG4ODBC (Database gateway for ODBC).
              • 4. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                964187
                The listener status

                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-SEP-2012 12:50:16

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

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.1)(PORT=1521)))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                Start Date 24-SEP-2012 18:48:36
                Uptime 0 days 18 hr. 1 min. 40 sec
                Trace Level off
                Security ON: Local OS Authentication
                SNMP OFF
                Listener Parameter File /App/oracle/ora10g2/network/admin/listener.ora
                Listener Log File /App/oracle/ora10g2/network/log/listener.log
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ovm1ora.drvsa.com)(PORT=1521)))
                Services Summary...
                Service "ibk" has 1 instance(s).
                Instance "ibk", status READY, has 1 handler(s) for this service...
                Service "ibkXDB" has 1 instance(s).
                Instance "ibk", status READY, has 1 handler(s) for this service...
                Service "ibk_XPT" has 1 instance(s).
                Instance "ibk", status READY, has 1 handler(s) for this service...
                The command completed successfully

                *****************************************************************************************

                I read about DG4ODBC, but all my manuals are about HSODBC, at the moment i need that this work, after this i'll try with DG4ODBC
                • 5. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                  Kgronau-Oracle
                  There's no listener service for the HSODBC SID inforpyme.

                  In addition the listener you posted at the beginning is called IBK, but according to the listener status you are using a listener called listener (see STATUS of the LISTENER).

                  So I would suggest that you add to the existing listener.ora for the listener called LISTENER just another SID entry for HSODBC.

                  (SID_DESC=
                  (SID_NAME=inforpyme)
                  (ORACLE_HOME=/App/oracle/ora10g2)
                  (PROGRAM=hsodbc)
                  )
                  Once donw, please stop and start the listener (lsnrctl stop & lsnrctl start)

                  You should then see in the service summary at least one service for inforpyme in the status unknown.


                  If you need assistance, feel free to post your current listener.ora file /App/oracle/ora10g2/network/admin/listener.ora
                  • 6. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                    964187
                    Sorry i think has been a misunderstanding

                    this is my /App/oracle/ora10g2/network/admin/listener.ora


                    IBK =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521))
                    (ADDRESS = (PROTOCOL = IPC)(KEY=PNPKEY))
                    )
                    )
                    )

                    SID_LIST_IBK =
                    (SID_LIST =
                    (SID_DESC =
                    (GLOBAL_DBNAME = ibk)
                    (ORACLE_HOME = /App/oracle/ora10g2)
                    (SID_NAME = ibk)
                    )
                    (SID_DESC=
                    (SID_NAME=inforpyme)
                    (ORACLE_HOME=/App/oracle/ora10g2)
                    (PROGRAM=hsodbc)
                    )
                    )
                    ******************************************************************************

                    I stop a start and this is the status

                    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-SEP-2012 13:18:30

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

                    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                    STATUS of the LISTENER
                    ------------------------
                    Alias LISTENER
                    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                    Start Date 25-SEP-2012 13:16:40
                    Uptime 0 days 0 hr. 1 min. 49 sec
                    Trace Level off
                    Security ON: Local OS Authentication
                    SNMP OFF
                    Listener Parameter File /App/oracle/ora10g2/network/admin/listener.ora
                    Listener Log File /App/oracle/ora10g2/network/log/listener.log
                    Listening Endpoints Summary...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ovm1ora.drvsa.com)(PORT=1521)))
                    Services Summary...
                    Service "ibk" has 1 instance(s).
                    Instance "ibk", status READY, has 1 handler(s) for this service...
                    Service "ibkXDB" has 1 instance(s).
                    Instance "ibk", status READY, has 1 handler(s) for this service...
                    Service "ibk_XPT" has 1 instance(s).
                    Instance "ibk", status READY, has 1 handler(s) for this service...
                    The command completed successfully

                    **********************************************************************************************************

                    i don'nt know why doesn't appears the service inforpyme, this it normal?
                    • 7. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                      Kgronau-Oracle
                      Then you have to stop the listener called listener (lsnrctl stop LISTENER) which is a default listener and start the IBK listener: lsnrctl start IBK
                      Or the other option you have is to name it LISTENER instead of IBK. Your /App/oracle/ora10g2/network/admin/listener.ora might then look like:

                      LISTENER =
                      (DESCRIPTION_LIST =
                      (DESCRIPTION =
                      (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.1)(PORT = 1521))
                      (ADDRESS = (PROTOCOL = IPC)(KEY=PNPKEY))
                      )
                      )
                      )

                      SID_LIST_LISTENER =
                      (SID_LIST =
                      (SID_DESC =
                      (GLOBAL_DBNAME = ibk)
                      (ORACLE_HOME = /App/oracle/ora10g2)
                      (SID_NAME = ibk)
                      )
                      (SID_DESC=
                      (SID_NAME=inforpyme)
                      (ORACLE_HOME=/App/oracle/ora10g2)
                      (PROGRAM=hsodbc)
                      )
                      )
                      • 8. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                        964187
                        I did lsnrctl stop LISTENER and
                        lsnrctl start IBK
                        • 9. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                          964187
                          I did lsnrctl stop LISTENER and lsnrctl start IBK

                          my error is gone, but now I have the next

                          ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este mensaje:
                          [Generic Connectivity Using ODBC][H006] The init parameter <HS_FDS_SHAREABLE_NAME> is not set. Please set it in init<orasid>.ora file.
                          ORA-02063: 2 lines precediendo a INFORPYME

                          But in my initINFORPYME the init parameter is set, i don't understand!!

                          initINFORPYME.ora
                          *************************************************
                          # This is a sample agent init file that contains the HS parameters that are
                          # needed for an ODBC Agent.

                          #
                          # HS init parameters
                          #
                          HS_FDS_CONNECT_INFO = inforpyme
                          HS_FDS_TRACE_LEVEL = 4
                          HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
                          HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc

                          #
                          # ODBC specific environment variables
                          #
                          set ODBCINI=/usr/local/etc/odbc.ini
                          **************************************************
                          • 10. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                            Kgronau-Oracle
                            the listener SID is in small letters (inforpyme), but the gateway ini file uses a SID with capital letters. So jut rename initINFORPYME.ora to initinforpyme.ora
                            • 11. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                              964187
                              i have renamed the file and .......... it`s working!!!!!!!!!


                              I am very grateful, thaks a lot!!!!!!!
                              • 12. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
                                Kgronau-Oracle
                                Glad to hear. Please mark this thread as answered and if you like also spend some reward points.