Oracle 11gr2 connection to Sql Server using dg4msql problem

Question

To whom it may respond to, We have installed Oracle 11g r2 on a Redhat Enterprise Linux 5.4 . We are trying to connect to a Sql Server 2005, after applying some notes the error below is the result we got : "ORA-28513 internal error in heterogenous remote agent".

---

listener.ora is as below :

[oracle@oracledb admin]$ less listener.ora
)
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4msql)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/dg4msql/lib:/u01/app/oracle/product/11.2.0/db_1/lib)
)
)

LOGGING_LISTENER = on

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

TRACE_LEVEL_LISTENER = on

---

tnsnames.ora is as below :

[oracle@oracledb admin]$ less tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

fasdat =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
)
(CONNECT_DATA =
(SID = fasdat)
)
)
dg4msql =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL= TCP)
(HOST = oracledb)
(PORT = 1521)
)
(CONNECT_DATA=
(SID=dg4msql) )
(HS=OK))

---

init4msql.ora is as below :

[oracle@oracledb admin]$ less initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=192.168.1.48:1433//NAVISION
# alternate connect format is hostname/serverinstance/databasename
#HS_FDS_TRACE_LEVEL=0
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
#HS_LANGUAGE=turkish_turkey.WE8ISO8859P9
HS_NLS_NCHAR=WE8ISO8859P9
#HS_FDS_TRACE_LEVEL=DEBUG

We have setup a system dsn at Sql Server 2005 named 'dg4msql' , chose the driver as 'Sql Server' and server as 'local'

We would be glad to hear any ideas to resolve this problem,

Answer 1

You seem to be using the Gateway for MySQL set-up rather than the Heterogeneous Gateway (for ODBC connections). Here is an overview of the process

On SQL Server create a database user and give it read access to the database/tables you want to read via the Oracle database link.

In the gateway home each SQL Server database you want to access should have an init.ora located in $OH/dg4msql/admin in the form initsid.ora where sid is the name of the database to be used in the link (e.g. initbob.ora), so create one

HS_FDS_CONNECT_INFO=msserver1:1234//Example_Database
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_TRANSACTION_MODEL=READ_ONLY

You must now add the new sid to the listener.ora in the gateway home using an additional SID_DESC section inside the existing SID_LIST, for example

(SID_DESC =
(SID_NAME=bob)
(ORACLE_HOME=/oracle/gateway/product/11.2.0)
(ENVS=LD_LIBRARY_PATH=/oracle/gateway/product/11.2.0/dg4msql/driver/lib;/oracle/gateway/product/11.2.0/lib)
(PROGRAM=dg4msql)
)

You should now stop and restart the gateway listener so that the new sid becomes active. NB a reload is not enough.

You must now add the new sid in the tnsnames.ora file for the listener of each database in which you will create a link. You don't need to do this in the gateway home unless it is also a database home in which you will create a database link.

bob =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = severname.example.com)(PORT = 1690))
(CONNECT_DATA = (SID = bob))
(HS = OK)
)

NB: The host and port are for the gateway not for the SQL Server database

In each database that requires a link to the MS-SQL database you should create a database link to your new gateway sid.

CREATE PUBLIC DATABASE LINK bob
CONNECT TO "ms_user" IDENTIFIED BY "ms-passwd" USING 'bob';

where ms-user and ms-password are the SQL Server user you created right at the start.

Now you can test the new database link

SELECT COUNT(*) FROM "Table_Name"@bob;

Once you have this working you can alter the initsid.ora file to add parameters to suit your connection. If you do it this way you can easily add and manage many different databases via the gateway.