Following my last post about Oracle Transparent Gateways which was theoretical post, this post will be more practical.
In this post I'll show an example of configuring Oracle Transparent Gateway for MS SQL Server.
The steps are:
In my environment there are 3 servers involved:
Configure the gateway initialization parameter file
After Oracle Transparent Gateway for MS SQL server installed on ORAGWSRV01 server, a new default initialization file is created at: $ORACLE_HOME\tg4msql\admin\inittg4msql.ora.
$ORACLE_HOME - is the directory where Transparent Gateway installed.
Edit the inittg4msql.ora file and specify the MS Sql Server connection details as follow:
in our case:
Alternatively you can set it as follow:
Configure the listener
In order to make the listener work with the gateway we should configure it.
The listener.ora must have at least 2 entries:
On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora as follow:
In our case:
After finishing the Gateway configuration, restart the listener (lsnrctl stop; lsnrctl start).
Configure the Oracle database tnsnames.ora
Creating DB link from Oracle database to MS SQL Server
create database link ORCLDB2MSQLDB
Where MSQLUSER & MSQLPWD are the user name and password to connect to the MS SQL server.
For more information about Transparent Gateway for Microsoft SQL Server: