Oracle Database Gateways 11g R2 (11.2) Installation and Configuration for heterogeneous connection from Oracle to Microsoft SQL database

Installation

Install the Oracle Database Gateways 11g R2 (11.2). See the screen snapshots of the installation here.

Configuration

• During the installation, the following+ default initialization parameter file is created:

[ORACLE_DG_HOME]\dg4msql\admin\initdg4msql.ora

dg4msql = Database gateway for Microsoft SQL Server. 
If you choose a different database option, use the appropriate path name.

• Copy initdg4msql.ora to a new file init[MSSQL].ora.

[MSSQL] can be any meaningful name easier to refer to, for example, mssqlsale. 
The filename will be in the format of init[MSSQL].ora.

copy initdg4msql.ora initmssqlsale.ora

• Modify newly created file initmssqlsale.ora and modify or add the MS SQL server & database name.

# 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=[SERVERNAME]//DATABASENAME
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

• Modify the listener.ora file. This can be that of the existing listener or new listener. Add SID_DESC to the appropriate place in the listener.ora file.

         (SID_DESC=
            (SID_NAME=[MSSQL])
            (ORACLE_HOME=[ORACLE_DG_HOME])
            (PROGRAM=[DRIVER])
         )

[MSSQL] =  Name of the new configuration file excluding the init and .ora.
For example, if the file name is initmssqlsale.ora, the [MSSQL] will be only mssqlsale.

[ORACLE_DG_HOME] = Oracle Database Gateway Home. This is NOT listener home.

[DRIVER] = dg4msql for Microsoft SQL Server

Sample:

 
LISTENER =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = DGHOST)(PORT = 1521))
      )
    )

SID_LIST_LISTENER = 
   (SID_LIST=
     (SID_DESC=
            (SID_NAME=mssqlsale)
            (ORACLE_HOME=D:\product\11.2.0\tg_1)
            (PROGRAM=dg4msql)
      )
    )
 )

• Restart or reload the listener.

• Validate using lsnrctl status.

C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production
:
:
Services Summary...
Service "mssqlsale" has 1 instance(s).
 Instance "mssqlsale", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

• On different Oracle database where we’d like to connect to this MS SQL database, create a new database link. The new database link will point to the the host where the gateway is installed.

create database link dblink_mssqlsale connect to "username" identified by "password"
using '
     (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=DGHOST)(PORT=1521))
        (CONNECT_DATA=(SID=mssqlsale))
        (HS=OK)
     )';

DGHOST = Host where the Oracle Database Gateway is installed.
mssqlsale = SID name mentioned in the listener.ora.

• Try to query for a table.

select count(*) from information_schema.tables@dblink_mssqlsale;
 COUNT(*)
----------
 26

Add more MS SQL databases

• Repeat the above steps starting with copying the sample file into a new init file, and updating it with an appropriate host and database name.

copy initdg4msql.ora to initmssqlhr.ora

• Update the listener.ora.

  (SID_LIST=
     (SID_DESC=
            (SID_NAME=mssqlsale)
            (ORACLE_HOME=D:\product\11.2.0\tg_1)
            (PROGRAM=dg4msql)
     )
     (SID_DESC=
            (SID_NAME=mssqlhr)
            (ORACLE_HOME=D:\product\11.2.0\tg_1)
            (PROGRAM=dg4msql)
     )
  )

• Restart or reload listener. Check using lsnrctl status.