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.
