This discussion is archived
6 Replies Latest reply: Aug 1, 2012 3:26 PM by user10526656 RSS

Unable to select from SQL Server from Oracle using DG4ODBC

user10526656 Newbie
Currently Being Moderated
Getting error while trying to connect from Oracle 11.2 Linux x86 to Windows 2008 R2 64 bit via Datadirect driver/DG4ODBC

I'm able to connect through Datadirect's demo/example apps but I need to connect through DG4ODBC.


Getting error:
SQL>select * from br_detail@sqlserver

ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQLSERVER


Gateway - - - - - initDG4ODBC.ora

HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect_for_ODBC_70/lib/libodbc.so
set ODBCINI=/opt/Progress/DataDirect/Connect_for_ODBC_70/odbc.ini

Oracle - - - -  tnsnames.ora

DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebizdev12.arvada.org)(PORT=1511))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

Oracle - - - -  listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=/app/oracle/ED12/11.2/db/tech_st/11.2)
(ENV=LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_70/lib/:/app/oracle/ED12/11.2/db/tech_st/11.2)
(PROGRAM=dg4odbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ebizdev12.arvada.org ) (PORT = 1511))
)
)
)

Listener status output

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-JUL-2012 16:13:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebizdev12.arvada.org)(PORT=1511)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 24-JUL-2012 14:20:45
Uptime 0 days 1 hr. 52 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/ED12/11.2/db/tech_st/11.2/network/admin/ED12_ebizdev12/listener.ora
Listener Log File /app/oracle/ED12/11.2/db/tech_st/11.2/log/diag/tnslsnr/ebizdev12/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebizdev12.arvada.org)(PORT=1511)))
Services Summary...
Service "DG4ODBC" has 1 instance(s).
Instance "DG4ODBC", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


tnsping results

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 24-JUL-2012 16:14:46

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

Used parameter files:
/app/oracle/ED12/11.2/db/tech_st/11.2/network/admin/ED12_ebizdev12/sqlnet_ifile.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebizdev12.arvada.org)(PORT=1511)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (0 msec)

Thanks for any help you can provide!

Ian
  • 1. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    kgronau Guru
    Currently Being Moderated
    Config looks good.
    Listener status shows a handle for DG4ODBC (status unknown is fine as the process is spawned by the listener when requested).

    Please execute on your Unix shell:
    /app/oracle/ED12/11.2/db/tech_st/11.2/bin/dg4odbc
    and provide its output.


    Also please type "env" and provide its output.
  • 2. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    user10526656 Newbie
    Currently Being Moderated
    Thanks for your reply kgronau!


    dg4odbc output
    Oracle Corporation --- WEDNESDAY JUL 25 2012 08:35:39.012

    Heterogeneous Agent Release 11.2.0.1.0 - Production Built with
    Oracle Database Gateway for ODBC


    env output
    ADJREOPTS=-Xms128M -Xmx512M
    ADJVAPRG=/app/oracle/ED12/11.2/db/tech_st/11.2/jdk/jre/bin/java
    ADPERLPRG=/app/oracle/ED12/11.2/db/tech_st/11.2/perl/bin/perl
    CONTEXT_FILE=/app/oracle/ED12/11.2/db/tech_st/11.2/appsutil/ED12_ebizdev12.xml
    CONTEXT_NAME=ED12_ebizdev12
    G_BROKEN_FILENAMES=1
    HISTSIZE=1000
    HOME=/home/oraed12
    HOSTNAME=ebizdev12.arvada.org
    INPUTRC=/etc/inputrc
    LANG=en_US.UTF-8
    LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_70/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/lib:/usr/X11R6/lib:/usr/openwin/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/lib:/usr/dt/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/ctx/lib
    LESSOPEN=|/usr/bin/lesspipe.sh %s
    LIBPATH=/app/oracle/ED12/11.2/db/tech_st/11.2/lib:/usr/X11R6/lib:/usr/openwin/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/lib:/usr/dt/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/ctx/lib
    LINK_CNTRL=
    LOGNAME=oraed12
    LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
    MAIL=/var/spool/mail/oraed12
    NLS_DATE_FORMAT=DD-MON-RR
    NLS_LANG=American_America.WE8ISO8859P1
    NLS_NUMERIC_CHARACTERS=.,
    NLS_SORT=binary
    ODBCINI=/opt/Progress/DataDirect/Connect_for_ODBC_70/odbc.ini
    ODBCINST=/opt/Progress/DataDirect/Connect_for_ODBC_70/odbcinst.ini
    OLDPWD=/app/oracle/ED12/11.2/db/tech_st/11.2/network/admin
    ORACLE_HOME=/app/oracle/ED12/11.2/db/tech_st/11.2
    ORACLE_SID=ED12
    ORA_NLS10=/app/oracle/ED12/11.2/db/tech_st/11.2/nls/data/9idata
    ORA_TZFILE=
    PATH=/opt/Progress/DataDirect/Connect_for_ODBC_70/tools:/app/oracle/ED12/11.2/db/tech_st/11.2/perl/bin:/app/oracle/ED12/11.2/db/tech_st/11.2/bin:/usr/bin:/usr/sbin:/app/oracle/ED12/11.2/db/tech_st/11.2/jdk/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:.:/home/oraed12/bin
    PERL5LIB=/app/oracle/ED12/11.2/db/tech_st/11.2/perl/lib/5.8.3:/app/oracle/ED12/11.2/db/tech_st/11.2/perl/lib/site_perl/5.8.3:/app/oracle/ED12/11.2/db/tech_st/11.2/appsutil/perl
    PWD=/app/oracle/ED12/11.2/db/tech_st/11.2/network/admin/ED12_ebizdev12
    SHELL=/bin/bash
    SHLIB_PATH=/app/oracle/ED12/11.2/db/tech_st/11.2/lib:/usr/lib:/app/oracle/ED12/11.2/db/tech_st/11.2/ctx/lib
    SHLVL=1
    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
    TERM=xterm
    TNS_ADMIN=/app/oracle/ED12/11.2/db/tech_st/11.2/network/admin/ED12_ebizdev12
    USER=oraed12
    _=/usr/bin/env
  • 3. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    kgronau Guru
    Currently Being Moderated
    unset these 2 environment variables
    ORA_NLS10=
    ORA_TZFILE=
    then stop & start the gateway listener again and retry the select.
  • 4. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    user10526656 Newbie
    Currently Being Moderated
    kgronau -

    I unset both ORA_NLS10 and ORA_TZFILE then bounced the gateway listener, but still received the same error when running sql.

    Does going from a 32-bit environment to a 64-bit environment require additional configuration or special considerations? I'm working from Metalink doc 466228.1.

    Thanks,
    Ian
  • 5. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    kgronau Guru
    Currently Being Moderated
    No, a 32bit SQL Server client can connect to a 64bit Server. The 28545 error is a configuration issue, but the config looks good.

    Let's create a strace file to check what's happening:
    1. get the listener process id of the gateway listener: ps -ef|grep tns
    2. run strace: strace -fae -o dg4odbc.log -p <process id of step 1>
    3. now open SQL*Plus and run again the statement

    After the error occurred again, abort the strace process and upload the generated output file dg4odbc.log to a public file hoster like dropbox etc and provide me the file link.
  • 6. Re: Unable to select from SQL Server from Oracle using DG4ODBC
    user10526656 Newbie
    Currently Being Moderated
    Thanks for your assistance. We're working to get strace installed onto the server and will update when ready.

    Thanks,
    Ian

Legend

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