DB Link between Oracle and MSSQL and resulted “internal error in heterogeneous remote agent”

DB Link between Oracle and MSSQL and resulted “internal error in heterogeneous remote agent”

This blog post based on the experience with one of customer, it is so simple but i rarely worked on these heterogeneous db links, so am writing for myself in future 🙂 and of course for all. 🙂  Let’s see now…  Oracle database contains user created DB links which connects to the MSSQL database and they were working perfect, Now the request is the newly created DB Link was not working and resulted with the error “ORA-28513: internal error in heterogeneous remote agent “, the other DB LInk was working fine as said and below is the status.

SQL> select * from dual@RMQL;

D
-
X

SQL>
The newly created DB Link MSDB which points to the same database results the following error. 

SQL> select * from dual@MSDB; 
select * from dual@MSDB 
* 
ERROR at line 1: 
ORA-28513: internal error in heterogeneous remote agent 
ORA-02063: preceding line from MSDB 

SQL> select dbms_metadata.get_ddl('DB_LINK','MSDB','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','MSDB','PUBLIC')
--------------------------------------------------------------------------------

 CREATE PUBLIC DATABASE LINK "MSDB"  CONNECT TO "MSSQL_USER" IDENTIFIED BY VALUES ':1' USING 'MSDB'

The DB Link structure of course it is same like other which working and also to same database, now rechecked quickly where the gateway configured and how? is there any missing parameters?

SQL> !ps -ef|grep tns
root 71 2 0 2015 ? 00:00:00 [netns]
oracle 21891 1 0 Sep06 ? 00:00:06 /u01/app/oracle/product/12.1.0.2/gateway/bin/tnslsnr LISTENER_GATEWAY -inherit
grid 41051 1 0 Aug25 ? 00:00:37 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
grid 52627 1 0 2015 ? 00:33:30 /u01/app/12.1.0.2/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
grid 57940 1 0 2015 ? 00:49:00 /u01/app/12.1.0.2/grid/bin/tnslsnr listener -inherit
oracle 65322 59946 0 08:42 pts/4 00:00:00 /bin/bash -c ps -ef|grep tns
oracle 65324 65322 0 08:42 pts/4 00:00:00 grep tns
grid 83709 1 0 Aug14 ? 00:00:49 /u01/app/12.1.0.2/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit

In order to check quickly i search with the listener, so that i can access the Gateway home very quickly, From the location “/u01/app/oracle/product/12.1.0.2/gateway/dg4msql/admin”, when checked there is no configuration file exists, earlier i thought there is any missing parameters or wrong parameters such as “HS_FDS_RECOVERY_PWD”, but the story is different and finally created the configuration file with the below values.

[oracle@oracle-ckpt1 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/gateway/dg4msql/admin
[oracle@oracle-ckpt1 admin]$ cat initMSDB.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.0.33:1344//RPT
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=R3cOVER!
HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_DELAYED_OPEN=FALSE
HS_FDS_WORKAROUNDS=16
[oracle@oracle-ckpt1 admin]$

After adding the parameters and then retried, then we are able to use the DB Link and it connected to the MSSQL database, output is below.

SQL> select count(*) from dual@MSDB;

COUNT(*)
----------
 1

SQL>

That is very simple isn’t it? I hope this blog post helps especially who are configuring first time or same situation occurred like me. 🙂

References:  https://docs.oracle.com/cd/E24628_01/doc.121/e57277/ch7_configmanviews.htm#EMVWS32102