Oracle gateway11g R2 access to heterogeneous database (MSSQL) configuration documentation

Directory

1 pre-conditions2 download transparent gateway3 Unzip the installation transparent gateway4 configuring TNSNames5 Configuring listeners6 Configuring the Gateway7 restarting Oracle Services8 configuration test9 creating Dblinkdatabase test successiveAnnex
Note: MSSQL does not need to configure an ODBC data source.

1. Pre-conditions

1. Preparatory work

Software name	Operating system	IP Address	Port	User	Password	Version	State
Oracle Database	Windows	localhost	1521	Scott	Scott	Win32 ORACLE11G R2	has been installed
Oracle Transparent Gateway	Windows	localhost				Win32_11gr2_gateways	Not installed
MSSQL Database	Windows	10.22.1.125	1433	Sa	123	Win64 MS SQL 2008	has been installed
Note: This example accesses the msdb database for MSSQL

2. Oracle Database Configuration Overview

Configuration file	Brief introduction	Position
Tnsnames.ora	Configuration instance	$dbhome _1\network\admin
Listener.ora	Listening instance, parameter program is the configuration directory, cannot change	$dbhome _1\network\admin

3. Oracle Transparent Gateway Configuration Overview

Configuration file	Brief introduction	Position
Init*.ora	* Indicates the value of the defined SID, such as the parameter SID in the Tnsnames.ora file and the parameter Sid_name in the Listener.ora file.	$dbhome _1\dg4msql\admin

2. Download the transparent Gateway

Note: This document is written in Oracle gateway11g R2 (Oracle Transparent gateway as an example )

Official website:http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

Then find gateways click to download

3.unzip the installation transparent gateway

1. After extracting the directory, double-click the Setup.exe application to install;

2. After launch, go to the Welcome page and click "Next" to install;

Note: It is recommended to install the same machine, that is, the transparent gateway is installed on the host that already has the Oracle database installed

3. Select the Product installation directory ( emphasis );

The transparent gateway itself also has monitoring, and Oracle itself is also listening, in order to unnecessary trouble, the gateway can be covered in the monitoring of Oracle, the Practice:

Path to Oracle database product installation after selection

4. Proceed with the installation and click "Next";

Select the components you want to install to determine the heterogeneous database you want to access, and here is the example of MSSQL

Click "Next" to continue

5. Fill in the MSSQL installation details or after the installation is completed to the $dbhome_1\dg4msql\nitdg4msql.ora configuration file configuration, click "Next" to continue;

Click Install

Waiting for installation

6. Install complete, click "Exit", to this oraclegateway11g R2 has been installed, open the Oracle Product installation homepage, we can see a dg4msql folder, this is the MSSQL gateway

4. Configure TNSNames

File location: $dbhome _1\network\admin\tnsnames.ora

Configuration:

5. Configuring Listeners

1. Mode one (file configuration):

File location: $dbhome _1\network\admin\listener.ora

Configuration:

2. Mode two (graphical tool configuration):

Open the Graphics tool

Operation Steps

Finally, File > Save network Configuration, the listener is configured.

6. Configure the Gateway

There is a Initdg4msql.ora file under the Dg4msql\admin directory.

The format of the file is Init*.ora, where the SID needs to be used later, the system default is Dg4msql, the general situation so that it can be.

If renamed, then use the modified name after that, and with msdb, the file name becomes Initmsdb.ora.

The file is the gateway's initial parameter file that describes which SQL Server database is connected.

Hs_fds_connect_info=10.22.1.125:1433//msdb

Hs_fds_trace_level=off

Hs_fds_recovery_account=recover

Hs_fds_recovery_pwd=recover

Note: Hs_fds_connect_info= Host Name: Port//Database

7. Restart Oracle Services

Action: Computer---Manage (right-click) services and Applications---services, locate the Oracle Service (monitoring service) for restart operation

8. Configure the Test

Start with attachment, command prompt, enter command: tnsping instance name, for example: this example-tnsping dg4msql

9. Create Dblink

To create a dblink condition:

1. Look at having dblink processing authority;

Log in to the SYS user to view

2. Give creation permission, under normal circumstances give droppublic DATABASE link, create publicdatabase link can be;

Grant DROP public databaselink,create publicly DATABASE LINK to Scott;

We'll switch to Scott. Users to view processing permissions with Dblink

3. Create Dblink

Way one (statement creation, it is recommended to create a statement, this can be a strict case for the database, is a good thing):

Create Public Database link mssqlconnect to "sa" identified by "123" using ' dg4msql ';

Note: since MSSQL wants to differentiate between the case of usernames and passwords, use double quotes to include the user name and password, using the database you created, which is created, for example: ORCL.

Mode two (graphical tool creation):

When you are done, click "Apply" to complete the operation.

Oracle gateway11g R2 access to heterogeneous database (MSSQL) configuration documentation