Oracle Gateway11g R2 access to heterogeneous databases (MSSQL) configuration document-graphic details, gateway11gmssql

Oracle Gateway11g R2 access to heterogeneous databases (MSSQL) configuration document-graphic details, gateway11gmssql

Directory

1. Prerequisites
2. Download transparent gateway
3. Unzip and install transparent gateway
4. Configure tnsnames
5. Configure the listener
6. Configure Gateway
7. Restart the Oracle service
8 configuration test
9 create a DBLink
10 database tests
11 attachment
Note: you do not need to configure the ODBC data source for MSSQL.

1. Prerequisites

1. Preparations

Software name	Operating System	IP address	Port	User	Password	Version	Status
Oracle Database	Windows	Localhost	1521	Scott	Scott	Win32 Oracle11g R2	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	Installed
Note: In this example, the MSDB database of MSSQL is accessed.

2. Oracle Database Configuration Overview

Configuration File	Introduction	Location
Tnsnames. ora	Configure an instance	$ Dbhome_1 \ NETWORK \ ADMIN
Listener. ora	Listen to the instance. The PROGRAM parameter is the configuration directory and cannot be changed.	$ Dbhome_1 \ NETWORK \ ADMIN

3. Overview of Oracle transparent gateway Configuration

Configuration File	Introduction	Location
Init *. ora	* Indicates the defined SID value. For example, the parameter SID in the tnsnames. ora file and the parameter SID_NAME In the listener. ora file must be used.	$ Dbhome_1 \ dg4msql \ admin

2. Download transparent gateway

Note: This document takes oracle gateway11g R2 as an example)

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

Find always ways and click to download

3. Unzip and install transparent gateway

1. After the script is decompressed, install the setup.exe application;

2. Go to the welcome page after the startup and click "Next" to install it;

Note: We recommend that you install the transparent gateway on the same host.

3. Select the product installation directory (Key Points);

You can also use transparent gateways to listen, while Oracle also listens. In order to avoid unnecessary trouble, you can include Gateway listening in Oracle listening. Practice:

After selecting it, the path is changed to the path where the Oracle database product is installed.

4. Continue the installation and click "Next ";

Select the component you want to install to determine the heterogeneous database you want to access. Here, MSSQL is used as an example.

Click "Next" to continue

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

Click Install

Waiting for Installation

6. After the installation is complete, click "exit". The OracleGateway11g R2 has been installed. Go to the Oracle product installation homepage. We can see that there is an additional dg4msql folder, which is the MSSQL Gateway.

4. Configure TNSNAMES

File Location: $ dbhome_1 \ NETWORK \ ADMIN \ tnsnames. ora

Configuration:

5. Configure the listener

1. method 1 (file configuration ):

File Location: $ dbhome_1 \ NETWORK \ ADMIN \ listener. ora

Configuration:

2. method 2 (graphical tool configuration ):

Open graphics tools

Procedure

Finally, File> Save the network configuration, and the listener is configured.

6. Configure Gateway

There is an initdg4msql. ora file under the dg4msql \ admin directory.

The file format is init *. ora. The SID here needs to be used later. The default format is dg4msql. Generally, this is the case.

If you change the name, use the modified name. If you use MSDB, the file name is changed to initMSDB. ora.

This is the initial parameter file of the Gateway, which describes the connected SQL Server database.

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 the Oracle service

Operation: choose computer> Manage (right-click)> services and applications> services> Oracle service (listener service) to restart.

8. configuration test

Start-> attachment-> command prompt, enter the name of the tnsping instance, for example, tnsping dg4msql

9. Create a DBLink

DBLink creation conditions:

1. Check that you have the DBLink processing permission;

Log on to the sys user and view

2. Grant the creation permission. Generally, grant the droppublic database link and create publicdatabase link permissions;

Grant drop public databaselink, create public database link to scott;

Switch to scott to view the permissions for handling DBLink.

3. Create a DBLink

Method 1 (statement creation is recommended. This is a good thing for databases with strict case sensitivity ):

Create public database link MSSQLconnect to "sa" identified by "123" using 'dg4msql ';

Note: Because MSSQL is case sensitive to the user name and password, you must use double quotation marks to include the user name and password. using uses the database you created, for example, orcl.

Method 2 (created using a graphical tool ):

After entering the information, click "Apply" to complete the operation.

10. Database test connection

Syntax: Select * from table name @ DBLink name;
Use Case:

Query a data table on SQL Server:

Then, query the data tables on SQL Server through the Gateway in PL/SQL:

11. Appendix 1