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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.