CREATING DBLINK BETWEEN ORACLE AND SQLSERVER USING ODBC GATEWAY METHOD

Creating DBLINK between ORACLE AND SQLSERVER using ODBC gateway method

Configuration Steps:

1.       Install ODBC Gateway 11gR2

1.1   Download the Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit) from the  

        following link :

                 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

1.2   unzip the file and install the gateway

   

          

    

    

   

   

    

   

2.       Create DSN in windows

Go to Control Panel àAdministrative Tools àData sources (ODBC)àSystem DSN

2.1   click add and follow the below steps

2.2   Select SQL SERVER from driver list. 

2.3   Next Screen give the following details

             Name:  <DSN NAME >     // This name will use in initdg4odbc.ora file as a DSN NAME

            Description: any message 

                           

                 

             Server : <Sql server name >

                                 

                                 

 

                          

                             

2.4   Select Sql Authentication and provide the sql server username and password.

2.5   Connection will established when all the given details (username, password, server) correct otherwise it will show error. 

                  

    

2.6   Once the connection established, it will show the database list and other things related to sql server. 

                    

2.7   Follow the screens and finally test the connection. Now , The DSN name have created and added to the list. 

3.       Edit the initdg4odbc.ora file

Go to initdg4odbc.ora file location and edit the following parameters

     Location:  E:\product\11.2.0\tg_1\hs\admin

     Parameters:

              HS_FDS_CONNECT_INFO = mssql     // Give the DSN name which have already created

              HS_FDS_TRACE_LEVEL = off

4.       Configure the listener

Location: E:\product\11.2.0\tg_1\NETWORK\ADMIN

                dg4odbc =

 (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.97)(PORT=1530))

 )

SID_LIST_dg4odbc=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=dg4odbc)

         (ORACLE_HOME=E:\product\11.2.0\tg_1)

         (PROGRAM=dg4odbc)

      )

  )

4.1   Run the dg4odbc listener Manually

C:\lsnrctl start dg4odbc

5.       Configure the tnsnames in oracle box(Linux OS)

Location:   $ cd /d1/oracledb/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

dg4odbc =

 (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.97)(PORT=1530))

 )

SID_LIST_dg4odbc=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=dg4odbc)

         (ORACLE_HOME=E:\product\11.2.0\tg_1)

         (PROGRAM=dg4odbc)

      )

  )

$ tnsping dg4odbc

6.       Create a dblink

 Sql> create public database link mlink

          Connect to “sa”

          Identified by “apps”

          Using ‘dg4odbc’;       // this is tnsname entry

             

7.       Run the query based on sqlserver tables. Now, you can get the result from sqlserver.

ORACLE to ORACLE  DBlink

TNS entry on both the nodes

General  Ledger Super User -> setup -> system -> Database links