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)