Oracle Direct Read and write MS SQL Server database (ii) Configure transparent gateways

Environment Description:

Database version: 11gR2

Transparent Gateway version: 11g

Operating system Windows server2008_64 bit

Oracle_home Directory: D:\app\Administrator\product\11.2.0\dbhome_1

MS Database ip:192.168.199.245 database: bwonline default port: 1433

Oracle Database ip:172.18.123.113 Service Name: DB1 default Port 1521

Note: Now that you have installed a transparent gateway after installing the database, the home directory for the Transparent gateway installation is the same as the home directory of the database. (Benefits: Easy management of server monitoring)

Get to the point right now:

First, the configuration of the transparent gateway parameter:

1. After installing the gateway software, in the Oracle_home directory there is a dg4msql directory, this is the Gateway software directory.

In the Dg4msql/admin directory has a Initdg4msql.ora file, where the file naming rules for Initsid.ora, the system will automatically generate a Initdg4msql.ora file after installation;

2. In the actual work, create a link to connect the MS database to Bwonline, copy the default Initdg4msql file directly, and modify the suffix as shown in.

3. Edit the Initbwonline.ora file:

Hs_fds_connect_info=192.168.199.245//dbonline

Hs_fds_trace_level=off

Hs_fds_recovery_account=recover

Hs_fds_recovery_pwd=recover

Hs_fds_connect_info parameter Description:

1) parameter format: hostname:port/serverinstance/databasename, where hostname is the machine name or IP address, there is a netizen said here to fill in the machine name can, but after the test machine name and IP address are not the problem;

2) port number: SQL Server2005 default port good for 1433, also has the user to fill in the port number to be able, after the test fills in and does not fill in is possible (may test environment different causes the test result to be different)

3) ServerInstance is the instance name of SQL Server, and does not know when SQL Server also has the instance, generally empty on the line.

4) DatabaseName is the database name of SQL Server and needs to be filled out;

5) The entire parameters of the document I have tried 4 of the total, tested can be.

hs_fds_connect_info= "192.168.199.245;database=bwonline"

Hs_fds_connect_info=[192.168.199.245]//bwonline

Hs_fds_connect_info=192.168.199.245:1433//bwonline

Hs_fds_connect_info=192.168.199.245//bwonline

Second, the monitoring file listener configuration

1. Directory of listening files: Listener.ora file under Oracle_home/network/administrator

2. File format Description: The file is divided into two parts: Sid_list_listener and Listener

Sid_list_listener is the list of listening SIDs, listener is the information of the listening host (IP address and port number setting), as detailed below:

# Listener.ora Network Configuration File:d:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = dg4msql)-----The name of the SID defined earlier

(Oracle_home = D:\app\Administrator\product\11.2.0\dbhome_1)-----ORACLE's HOME directory

(program = dg4msql)-----The configuration directory of programs (the previous version of the transparent gateway directory for TG4MSQL, please note)

)

(Sid_desc =

(Sid_name = bwonline)

(Oracle_home = D:\app\Administrator\product\11.2.0\dbhome_1)

(program = dg4msql)

)

)

The--------highlighting section shows that when the corresponding SID is configured on the transparent gateway, copy the highlighted paragraph and modify the corresponding sid_name.

LISTENER =

(Description_list =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = 172.18.123.113) (port = 1521))----The IP address or hostname and port number of the listening server

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))

)

)

Adr_base_listener = D:\app\Administrator

3. After configuring the above parameters, start monitoring to see the corresponding service name: in cmd input: lsnrctl status

Third, the configuration tnsnames file, this step is relatively simple, I believe are very familiar with.

1. Directory of listening files: Tnsnames.ora file under Oracle_home/network/administrator

DB1 =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 172.18.123.113) (PORT = 1521))----Listen for information about the server

)

(Connect_data =

(service_name = DB1)-----Listener Service Name

)

(HS = OK)

)

Iv. testing of Transparent gateways

1. Create Dblink

Script: CREATE DATABASE LINK Bwonline

CONNECT to SA

Identified by "* * * * *"

USING ' Bwonline ';

2, find the corresponding MS below a table, and then Oracle under the Dblink to access;

The successful return of the data indicates that the corresponding transparent gateways and dblink are not problematic.

Oracle Direct Read and write MS SQL Server database (ii) Configure transparent gateways