Creating a database link from oracle to oracle database is easy. But setting up db_link from oracle to mssql server isn’t straight forward. We need to do odbc setup. Its knows as heterogeneous db_link.  We can achive this either by using third party odbc drivers or using oracle provided gateways utility.

Here in this article i will show how to create db_link using oracle gateway.

Download the gateway file from OTN.

ms_down1
unzip the file in oracle database server and execute runInstaller

[oracle@localhost HouseKeeping]$ pwd
/u01/home/oracle/HouseKeeping
[oracle@localhost HouseKeeping]$ unzip p13390677_112040_Linux-x86-64_5of7.zip

[oracle@localhost HouseKeeping]$ cd gateways
[oracle@localhost gateways]$ ls -ltr
total 56
drwxr-xr-x  4 oracle oinstall  4096 Aug 26  2013 install
drwxr-xr-x  4 oracle oinstall  4096 Aug 26  2013 legacy
-rwxr-xr-x  1 oracle oinstall  3267 Aug 26  2013 runInstaller
drwxr-xr-x  2 oracle oinstall  4096 Aug 26  2013 response
drwxr-xr-x 14 oracle oinstall  4096 Aug 26  2013 stage
-rw-r--r--  1 oracle oinstall 30016 Aug 27  2013 readme.html
-rw-r--r--  1 oracle oinstall   500 Aug 27  2013 welcome.html

./runInstaller

 

msql1

 

Select the oracle_home same as that of the oracle database.

msql2

 

msql3

 

 

 

 

Here we are connecting to microsoft sql server. So select that sql server gateway option.

 

msql4
Enter the sql server details  to which we are creating the database link.

msql5

 

 

msql6

 

msql7

Run the root.sh file from root user . Now the gateway installation is done.

 

Now add the below entry in listener.ora file and start the listener

( HOST=13.129.101.40)(PORT=1521) — This is the oracle database server ip

LISTENER_dg4mssql=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=13.129.101.40)(PORT=1521))
      )
    )
  )
SID_LIST_LISTENER_dg4mssql=
  (sid_list=
    (sid_desc=
     (sid_name=dg4msql)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
     (program=dg4msql)
(envs="LD_LIBRARY_PATH=/app/oracle/product/11.2.0/db_1/dg4msql/driver/lib:/app/oracle/product/11.2.0/db_1/lib")
    )
  )

lsnrctl start LISTENER_dg4mssql

Now add the tns entry in tnsnames.ora file

dg4msql =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=13.129.101.40)(PORT=1521))
      (CONNECT_DATA=(SID=dg4msql))
      (HS=OK)
  )



[oracle@localhost admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 15-JUL-2015 07:24:06

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=13.129.101.40)(PORT=1521)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))
OK (10 msec)

 

Lets create the database link and test it.

SQL> Create public database link MSTEST connect to MSTEST identified by mstest124 using 'dg4msql';


Database link created.



SQL>  select count(*) from "INFORMATION_SCHEMA"."TABLES"@MSTEST;

2 rows selected