10 Replies Latest reply: Sep 11, 2013 3:01 AM by user12832764 RSS

    Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error

    user12832764

      Dear All,

       

       

      I am trying to setup Oracle Database Gateway for MS SQL Server(I don't have admin access to the MS SQL Server).  When I try to test the dblink connection, I get the following error.

      I issued select * from dual@xyz;

       

       

      ORA-28546: connection initialization failed, probable Net8 admin error

      ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=dg4msql)))

      ORA-02063: preceding 2 lines from xyz

      28546. 0000 -  "connection initialization failed, probable Net8 admin error"

       

       

      My setup is the following.

      1. Oracle 11gR2 running on Oracle Linux 6, the same version gateway is installed on the same server, but different home directory.

      2. Oracle DB home directory is /u01/app/oracle/product/11.2.0/dbhome_1, gateway home directory is /u01/gateway.

      3. I use the same listener.ora file for both the db and the gateway.

      4. the contents of my listener.ora, tnsnames.ora and the initdg4msql.ora are below.

       

      /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            #(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

            (ADDRESS = (PROTOCOL = TCP)(HOST = db.abc.com.tw)(PORT = 1522))

          )

        )

      mssql =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          )

        )

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (GLOBAL_DBNAME=db.abc.com.tw)

            (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

            (SID_NAME=orcl)

          )

        )

      SID_LIST_mssql =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME=dg4msql)

            (ENVS=LD_LIBRARY_PATH=/u01/gateway/dg4msql/driver/lib:/u01/gateway/lib)

            (PROGRAM_NAME=/u01/gateway/bin/dg4msql)

          )

        )

       

       

       

       

      /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

      LISTENER_ORCL =

        (ADDRESS = (PROTOCOL = TCP)(HOST = db.abc.com.tw)(PORT = 1522))

      ORCL =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = db.abc.com.tw)(PORT = 1522))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = orcl.abc.com.tw)

          )

        )

      dg4msql  =

        (DESCRIPTION=

          (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT = 1521))

          (CONNECT_DATA=(SID=dg4msql))

          (HS=OK)

        )

       

      /u01/gateway/dg4msql/admin/initdg4msql.ora

      HS_FDS_CONNECT_INFO=[192.168.0.1]:1433//XIETEST

      # alternate connect format is hostname/serverinstance/databasename

      HS_FDS_TRACE_LEVEL=255

      HS_FDS_RECOVERY_ACCOUNT=RECOVER

      HS_FDS_RECOVERY_PWD=RECOVER

       

      dblink was created using statement below.

      CREATE DATABASE LINK "xyz.abc.COM.TW"

      CONNECT TO SA IDENTIFIED BY

      USING 'dg4msql';

       

      Couple more things, that I have done to check the work above.

       

      [oracle@db admin]$ ping 192.168.0.1

      PING 192.168.0.1 (192.168.0.1) 56(84) bytes of data.

      64 bytes from 192.168.0.1: icmp_seq=1 ttl=61 time=7.61 ms

      64 bytes from 192.168.0.1: icmp_seq=2 ttl=61 time=7.41 ms

      ^C

      --- 192.168.0.1 ping statistics ---

      2 packets transmitted, 2 received, 0% packet loss, time 1818ms

      rtt min/avg/max/mdev = 7.417/7.516/7.615/0.099 ms

       

       

      [oracle@db admin]$ tnsping dg4msql

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-SEP-2013 10:53:11

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

      Used parameter files:

      /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT = 1521)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))

      OK (0 msec)

       

       

      [oracle@db admin]$ lsnrctl status mssql

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-SEP-2013 10:54:23

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

      STATUS of the LISTENER

      ------------------------

      Alias                     mssql

      Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

      Start Date                11-SEP-2013 10:09:46

      Uptime                    0 days 0 hr. 44 min. 37 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

      Listener Log File         /u01/app/oracle/diag/tnslsnr/db/mssql/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

      Services Summary...

      Service "dg4msql" has 1 instance(s).

        Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

      The command completed successfully

       

       

       

       

      Obviously, I would like to get this setup to work, but I also have couple other questions regarding this setup.

      1. When installing the gateway, it asks to run the root.sh script.  the root.sh script as for the following.

      Enter the full pathname of the local bin directory: [/usr/local/bin]:

      The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)

      [n]: n

      The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

      [n]: n

      The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

      [n]: n

       

       

      is the above answer correct?

       

       

      Thanks for reading.

        • 1. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
          Kgronau-Oracle

          You're using your Oracle database listener to spawn the gateway which is installed in a different home. As you configured a separate listener for the gateway, why not starting the gateway listener from the gateway home?

           

          While looking at MSSQL Listener file you configured:

            (PROGRAM_NAME=/u01/gateway/bin/dg4msql)

          but the parameter is PROGRAM, not PROGRAM_NAME, so correct would be

          (PROGRAM=dg4msql)

          Once done, please STOP and START the MSSQL listener and check again.

           

          another minor issue - SQL Server is commonly case sensitive, so please make sure to surround username/password by double quotes to preserve the case:

          CREATE DATABASE LINK "xyz.abc.COM.TW"

          CONNECT TO "sa" SA IDENTIFIED BY "<password>" USING 'dg4msql';

           

          - Klaus

          • 2. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
            user12832764

            Dear Klaus,

             

            Thanks for the reply.

             

            I changed the parameter in the listener file, and stop and restarted the listener.  I go the following error trying to connect.

             

            An error was encountered performing the requested operation:

             

             

            ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

            ORA-02063: preceding line from TAICON

            28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"

            *Cause:    The cause is explained in the forwarded message.

            *Action:   See the non-Oracle system's documentation of the forwarded

                       message.

            Vendor code 28500

             

            any idea on where to look for the forwarded message?

            Is it possible the table dual doesn't exist on SQL SERVER or is my SQL server username / password might be wrong?

            • 3. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
              Kgronau-Oracle

              When the username/password is not correct you would get a dedicated error message stating that uid/pwd is not correct. In general the dual table does not exist on the SQL Server side, but should be mimicked by the gateway internal mechanisms. The 28500 is a generic error and a gateway trace might be helpful.

               

              Could you please set HS_FDS_TRACE_LEVEL=255 and in the gateway init file and then post the trace?

               

              - Klaus

              • 4. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
                user12832764

                Dear Klaus,

                 

                I set the trace in the gateway init file already, but I don't see log file under the /u01/gateway/dg4msql/log directory, will it be somewhere else?

                my gateway init file is below

                HS_FDS_CONNECT_INFO=[192.168.0.1]:1433//XIETEST

                # alternate connect format is hostname/serverinstance/databasename

                HS_FDS_TRACE_LEVEL=255

                HS_FDS_RECOVERY_ACCOUNT=RECOVER

                HS_FDS_RECOVERY_PWD=RECOVER

                • 5. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
                  Kgronau-Oracle

                  Yes, you commonly find the gateway trace in the <gateway home>/dg4msql/log. Just to be sure, you started a new SQL*Plus session after you added the trace parameter and then tried to select, is this correct?

                  BTW, what's the output when you type at the shell:  /u01/gateway/bin/dg4msql and what's the output of the command "env"?

                   

                  - Klaus

                  • 6. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
                    user12832764

                    Dear Klaus,

                     

                    I did start a new SQL*PLUS session

                     

                    The output of  /u01/gateway/bin/dg4msql is

                    [oracle@db log]$ /u01/gateway/bin/dg4msql

                     

                     

                     

                     

                    Oracle Corporation --- WEDNESDAY SEP 11 2013 14:45:06.604

                     

                     

                    Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production  Built with

                       Oracle Database Gateway for MSSQL

                     

                     

                    The output of env is

                    HOSTNAME=db.abc.com.tw

                    SHELL=/bin/bash

                    TERM=xterm

                    HISTSIZE=1000

                    ORACLE_UNQNAME=orcl

                    TMPDIR=/tmp

                    QTDIR=/usr/lib64/qt-3.3

                    QTINC=/usr/lib64/qt-3.3/include

                    USER=oracle

                    LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib

                    LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.tbz=01;31:*.tbz2=01;31:*.bz=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:

                    ORACLE_SID=orcl

                    ORACLE_BASE=/u01/app/oracle

                    ORACLE_HOSTNAME=db.abc.com.tw

                    MAIL=/var/spool/mail/oracle

                    PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin

                    PWD=/home/oracle

                    LANG=en_US.UTF-8

                    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass

                    HISTCONTROL=ignoredups

                    SHLVL=1

                    HOME=/home/oracle

                    TMP=/tmp

                    LOGNAME=oracle

                    QTLIB=/usr/lib64/qt-3.3/lib

                    CVS_RSH=ssh

                    CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/jlib:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/jlib

                    LESSOPEN=|/usr/bin/lesspipe.sh %s

                    ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

                    G_BROKEN_FILENAMES=1

                    _=/bin/env

                    • 7. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
                      Kgronau-Oracle

                      LD_LIBRARY_PATH and PATH point to the database home, but as long as ENV in the listener is set up correctly that shouldn't be an issue.

                       

                      We need to have a look with strace where the gateway fails.

                      You need to first find the listener process that spawns the gateway connection: ps -ef|grep tns

                      Then start strace: strace -fae -o dg4msql.log -p <PID of the listener that spawns the gateway process>

                      Now start again SQL*Plus and select from the SQL Server. Once you got the error, kill strace and ther's now a dg4msql.log created in the directory where you started strace. Upload the strace outputfile dg4msql.log to a file share like DropBox and provide me the link.

                      - Klaus

                      • 9. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
                        Kgronau-Oracle

                        The strace shows:

                        2796  open("/u01/app/oracle/diag/tnslsnr/db/mssql/trace/mssql.log", O_WRONLY|O_CREAT|O_APPEND, 0660) = 12

                        9564  access("/u01/app/oracle/product/11.2.0/dbhome_1/dg4msql/admin/initdg4msql.ora", F_OK <unfinished ...>

                        2796  write(12, "11-SEP-2013 15:02:44 * (CONNECT_"..., 175 <unfinished ...>

                        9564  <... access resumed> ) = -1 ENOENT (No such file or directory)

                        2796  <... write resumed> ) = 175

                        2796  close(12 <unfinished ...>

                        9564  brk(0x14a3000) = 0x14a3000

                        2796  <... close resumed> ) = 0

                        2796  poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=-1}, {fd=-1}], 4, -1 <unfinished ...>

                        9564  access("/u01/app/oracle/product/11.2.0/dbhome_1/dg4msql/log/dg4msql_agt_9564.trc", F_OK) = -1 ENOENT (No such file or directory)

                        9564  access("/u01/app/oracle/product/11.2.0/dbhome_1/dg4msql/log/dg4msql_agt_9564.trc", F_OK) = -1 ENOENT (No such file or directory)

                        9564  statfs("/u01/app/oracle/product/11.2.0/dbhome_1/dg4msql/log/", 0x7fff91f005b8) = -1 ENOENT (No such file or directory)

                        9564  open("/u01/app/oracle/product/11.2.0/dbhome_1/dg4msql/log/dg4msql_agt_9564.trc", O_WRONLY|O_CREAT|O_TRUNC, 0666) = -1 ENOENT (No such file or directory)

                         

                        => so it always uses the Database Home as Oracle_home

                         

                        With this info I rechecked the listener and saw you're missing the ORACLE_HOME setting in the dg4msql SID section. Please correct your mssql_sid entry to:

                         

                        SID_LIST_LISTENER =

                          (SID_LIST =

                            (SID_DESC =

                              (GLOBAL_DBNAME=db.abc.com.tw)

                              (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

                              (SID_NAME=orcl)

                            )

                          )

                        SID_LIST_mssql =

                          (SID_LIST =

                            (SID_DESC =

                             (ORACLE_HOME=/u01/gateway)

                              (SID_NAME=dg4msql)

                              (ENVS=LD_LIBRARY_PATH=/u01/gateway/dg4msql/driver/lib:/u01/gateway/lib)

                              (PROGRAM_NAME=/u01/gateway/bin/dg4msql)

                            )

                          )

                         

                        Afterwards please stop and start again the mssql listener and check again.

                         

                        - Klaus