This discussion is archived
10 Replies Latest reply: Sep 11, 2013 1:01 AM by user12832764 RSS

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

user12832764 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

  • 10. Re: Need help trouble shooting Database Gateway for MS SQL Server setup, getting ORA-28546 error
    user12832764 Newbie
    Currently Being Moderated

    It works now

     

    Thank you for your help Klaus

Legend

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