oracle dg4odbc set up for sqlserver connection

I've configured Oracle 11g DG4ODBC to talk to Sqlserver 2012 on Window 2008 Oracle server but It came up with error below :

select * from "employee"@tst

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

I've set up the following:

1) Configure ODBC
     System DSN  (DG4ODBC - both 32 and 64 bits) for SQLServer
     It's been tested OK to connect to the SQLserver  from the Oracle server  


2)  Configure Listener

# listener.ora Network Configuration File: D:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = VSYDORA14)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc
         (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
      )
  )
ADR_BASE_LISTENER = D:\app


3)  Configure TNSNAMES.ORA

 #
#
DG4ODBC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )


4)  Configure DG4ODBC

$ORACLE_HOME/hs/admin/initdg4odbc.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS = FALSE


5) Restarted Listener
Below is the result of status

c:> Lsnrctl Status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                08-AUG-2014 17:03:33
Uptime                    0 days 0 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\product\11.2.0\dbhome_1\network\admin\listener.
ora
Listener Log File         d:\app\diag\tnslsnr\VSYDORA14\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VSYDORA14.osl.local)(PORT=1521)))
Services Summary...
Service "GBLTSTXDB" has 1 instance(s).
  Instance "gbltst", status READY, has 1 handler(s) for this service...
Service "PNGTESTXDB" has 1 instance(s).
  Instance "pngtest", status READY, has 1 handler(s) for this service...
Service "dg4odbc (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1" has 1 instance(s).

  Instance "dg4odbc (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1", status UNKNOWN
, has 1 handler(s) for this service...
Service "gbltst" has 1 instance(s).
  Instance "gbltst", status READY, has 1 handler(s) for this service...
Service "pngtest" has 1 instance(s).
  Instance "pngtest", status READY, has 1 handler(s) for this service...
The command completed successfully

6) TNSPING dg4odbc
It works fine.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora1
4)(PORT = 1521)) (CONNECT_DATA = (SID = dg4odbc)) (HS = OK))
OK (0 msec)


7) Configure Dblink  as below for test

create public database link tst
connect to "user" identified by "pwd" using 'dg4odbc'
/

Both
select user from dual@tst
or
select * from "employee"@tst
generate the error below.

 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

Is there anything missing in the setup above?
Anyone can shed some lights on this?

Many thanks in advance.
OSLEEAsked:
Who is Participating?
 
OSLEEConnect With a Mentor Author Commented:
I've found out that Oracle 11.2.0 do not come with the latest TG4MSSQL so I got this going via 11.2.0  dblink to Oracle 10G and setting up DG4ODBC from Oracle 10G.  It's working fine now even though it's a work around.

Thanks to all.

I think I close the call as is.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
Fist check that you are able to communicate to the target Host i.e Oracle DB Host.

from SQL Server machine ping the target Host.
i.e ping <Target_Host>

Also ensure the global_names parameter is set to false.

Is these a 32-bit or 64-bit version, i think you need to provide the library path in listener.ora file

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = VSYDORA14)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc
         (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=<path>)
      )
  )
ADR_BASE_LISTENER = D:\app
 
i'm also providing you the step by step process for setting up DG
http://www.oratraining.com/blog/2014/01/steps-by-step-connecting-to-microsoft-sql-database-from-oracle-database-using-database-gateway-for-sql-server/
0
 
Tomas Helgi JohannssonCommented:
Hi!

Oracle is case sensitive so  try changing this

3)  Configure TNSNAMES.ORA

 #
#
DG4ODBC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )

to this

3)  Configure TNSNAMES.ORA

 #
#
dg4odbc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )

Regards,
    Tomas Helgi
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
OSLEEAuthor Commented:
Hi TomasHelgi,
I've tried PING from SQLServer. That was OK.
Also tried was   (ENVS=LD_LIBRARY_PATH=<path>) in Listener.  No Difference. Any more ideas?
0
 
OSLEEAuthor Commented:
Hi TomasHelgi,
I've tried the lower case (dg4odbc).  No Difference.  Any more ideas?
0
 
OSLEEAuthor Commented:
Question?
I thought db4odbc is part of Oracle 11g install and it does not require  a separate install.  
Do I need to download and  install SQLGateway separate from Oracle 11g install?
0
 
OSLEEAuthor Commented:
Hi praveencpk
 I've tried PING from SQLServer. That was OK.
 Checked that GLOBAL_NAMES = FALSE
 Also tried was   (ENVS=LD_LIBRARY_PATH=<path>) in Listener.  
No Difference.

This is 64 bit version.
Any more ideas?
0
 
Tomas Helgi JohannssonCommented:
Hi!

Did you download and install the Oracle Gateway  (just to make sure you did ) ?
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

The reason for this is this
"
If you are using 10.2 HSODBC or TG4MSQL then that is the problem because they were never ported to Windows 64-bit. The only generic connectivity option or gateway available on Windows 64-bit is the 11.2 Database Gateway for ODBC (Dg4ODBC) or Database Gateway for SQL*Server (DG4MSQL)"

see further here https://community.oracle.com/thread/2487313

Also make sure that the database and the gateway are not listening on the same port.

Regards,
      Tomas Helgi
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>I thought db4odbc is part of Oracle 11g install and it does not require  a separate install.<<
No its not the part of Oracle 11g install.

>>Do I need to download and  install SQLGateway separate from Oracle 11g install?<<
Yes you need to download it has shown in previous post based on the OS and the version.

Here is the installation on configuration doc which will be helpful.
http://docs.oracle.com/cd/E18281_01/doc/gateways.112/e12013/configsql.htm
0
 
OSLEEAuthor Commented:
Thanks
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.