Ittichai Chammavanijakul

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

• 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

• 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.