6 Replies Latest reply: Aug 1, 2012 5:26 PM by user10526656 RSS

    Unable to select from SQL Server from Oracle using DG4ODBC

    user10526656
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    Thanks for your assistance. We're working to get strace installed onto the server and will update when ready.

                    Thanks,
                    Ian