This discussion is archived
12 Replies Latest reply: Sep 25, 2012 5:01 AM by kgronau RSS

ORA-28545 Between ORACLE 10G and SQL SERVER 2008

964187 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I did lsnrctl stop LISTENER and
    lsnrctl start IBK
  • 9. Re: ORA-28545 Between ORACLE 10G and SQL SERVER 2008
    964187 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Glad to hear. Please mark this thread as answered and if you like also spend some reward points.

Legend

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