Using transparent gateways in Oracle to link to SQL Server (GO)

Test environment Introduction

1, Oracleserver

Database version:10.2.0

ip:192.168.1.5

Oracle_home:d:\oracle\product\10.2.0\db_1

2. Oracle Transparent Gateway Server

version:10.2.0

ip:192.168.1.15

installation directory D:\oracle\product\10.2.0\tg_1

3. MS SQL Server

SQL Server Version:2008r2

ip:192.168.1.25

The original Oracle database, SQL Server database is already in use, need to guide some data to the Oracle database.

STEP1: Install Oracle gateways slightly ... During the installation of the transparent gateway, you will be asked to configure the database for those databases and select the for SQL Server.

STEP2: Configure the relevant files under the transparent gateway; the next server, the database can not be filled; now all we have to do is copy the Inittg4msql.ora file under D:\oracle\product\10.2.0\tg_1\tg4msql\admin, and modify it into the name Init<sid>.

Ora I test the connection Gslimsdb_ningbo library here, for the configuration convenient renamed to G5, so that is Initg5.ora content is:

Using transparent gateways in Oracle to link to SQL Server
Test environment Introduction
1, Oracleserver
Database version:10.2.0 ip:192.168.1.5
Oracle_home:d:\oracle\product\10.2.0\db_1 2, Oracle transparent Gateway Server version:10.2.0 ip:192.168.1.15
installation directory D:\oracle\product\10.2.0\tg_1 3, MS SQL Server
SQL Server VERSION:2008R2 ip:192.168.1.25
The original Oracle database, SQL Server database is already in use, need to guide some data to the Oracle database.
STEP1: Install Oracle gateways slightly ...
During the installation of the transparent gateway, you will be asked to configure the database for those databases and select the for SQL Server.
STEP2: Configure the relevant files under the transparent gateway;
The next server, the database can not be filled; now all we have to do is copy
D:\oracle\product\10.2.0\tg_1\tg4msql\admin under the Inittg4msql.ora file, and modify the name to Init<sid>.

Ora I test the connection Gslimsdb_ningbo library here, for the configuration convenient renamed to G5, so that is Initg5.ora content is:

========================= foot = = = = = = = = = = ============================
# This was a sample agent Init file that contains the HS parameters that's # needed for the Transparent Gateway for SQL S Erver #
# HS Init Parameters #
hs_fds_connect_info= "Server=192.168.1.25;database=g5" Hs_fds_trace_level=off
Hs_fds_recovery_account=recover
Hs_fds_recovery_pwd=recover
======================================================================
If configured at installation time, the resulting hs_fds_connect_info=192.168.1.25.g5 connection will be problematic, and the Hs_fds_connect_info settings can be in several formats, such as:
Hs_fds_connect_info=server_name.db_name, but this notation server_name cannot be an IP, and the port must be the default of 1433, such as:
Hs_fds_connect_info=sqlserverhostname. G5

Next, configure the Listener.ora file under D:\oracle\product\10.2.0\tg_1\network\admin

========================= foot = = = = = = = = = = ============================
# Listener.ora Network Configuration File:d:\oracle\product\10.2.0\tg_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

Sid_list_listener =

(Sid_list =

(Sid_desc =
(Sid_name = Plsextproc)
(Oracle_home = D:\oracle\product\10.2.0\tg_1)

(program = Extproc))
(Sid_desc =
(Sid_name = G5)
(Oracle_home = D:\oracle\product\10.2.0\tg_1)

(program = tg4msql)))

LISTENER =
(Description_list =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP) (HOST = rdserver2) (PORT = 1521))

)

)
======================================================================

The red part here is the new configuration, the other is the default, and then configure
Tnsnames.ora files under D:\oracle\product\10.2.0\tg_1\network\admin :

========================= foot = = = = = = = = = = ============================
# Tnsnames.ora Network Configuration File:d:\oracle\product\10.2.0\tg_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
Extproc_connection_data =

(DESCRIPTION =

(Address_list =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))

)
(Connect_data =
(SID = Plsextproc)

(PRESENTATION = RO)

)

)

g5=
(description=

(address=
(PROTOCOL=TCP) (host=192.168.1.25) (port=1521))

(Connect_data=

(SID=G5)

)
(Hs=ok)

)
======================================================================

Step3: start or reload monitoring

Viewing the Listening status


STEP4: Create an Dblink connection on the Oracle database sqlserver2008
========================= foot = = = = = = = = = = ============================
Create Public database link Con_mssql connect to "sa" identified by "password"

Using ' (description=

(address=
(PROTOCOL=TCP) (host=192.168.1.15) (port=1521))

(Connect_data=

(SID=G5)

)
(Hs=ok)

)‘;
======================================================================
then you can test:
========================= foot = = = = = = = = = = ============================

SelectCount (*) from [email protected]_mssql;
======================================================================
After execution, there will be no error, the result is so transparent network management configuration completed;

Using transparent gateways in Oracle to link to SQL Server (GO)