配置透明网关的步骤

基本步骤

说明

HostNamedmserver

Win Server Oracle Database Microsoft SQL Server

UserName:User Password:User

Database:CFDai TableName:Test

都使用 tcp/ip 默认端口

基本步骤

: 安装 TRANSPARENT GATEWAY FOR MSSQL选件输入主机名和数据库名

安装完后在d:\oracle\ora的目录下产生一个目录D:\oracle\ora\tgmsql

同时自动生成文件inittgmsqlora

其中内容为

HS_FDS_CONNECT_INFO=dmserverCFDai #服务器名数据库名

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

注第一行可写为HS_FDS_CONNECT_INFO=dmserver;DATABASE=CFDai

: 配置 LISTENERORA

SID_LIST_LISTENER =

(SID_LIST =

……

(SID_DESC =

(GLOBAL_DBNAME = tgmsql) #可重命名

(PROGRAM = tgmsql)

(SID_NAME = tgmsql) #可重命名

(ORACLE_HOME = D:\oracle\ora) #数据库主目录名

)

)

: 停止并重启 DATABASE 和 LISTENER服务

: 配置 tnsnamesora在文件尾增加以下内容

tgmsql =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dmserver)(PORT = ))

)

(CONNECT_DATA =

(SID = tgmsql)

)

(HS=OK)

)

: 在SQL Plus或SQLPlus Worksheet中通过如下语句建立数据库连接

CREATE PUBLIC DATABASE LINK DB_SQL CONNECT TO User IDENTIFIED by User USING tgmsql;

: 在SQL Plus或SQLPlus Worksheet中测试

select * from test@DB_SQL;

取消数据库连接

DROP PUBLIC DATABASE LINK DB_SQL;

FW:多谢！我也刚刚搞定

原来是一个低级错误——tnsnamesora文件配置错误（直接拷贝后修改结果没改主机地址！指到别的地方了）））另外注意lsnrctl中显示status=unknow不必惊讶正常

今天阅读了Oracle i附带的Oracle Transparent Gateways文档整理了一下供参考！

搜索关键字

Oracle Transparent Gateways

透明网关

Oracle database applications can be executed against nonOracle database servers using SQL*Connect or the Oracle Open Gateway

Oracle Transparent Gateways

The capabilities SQL mappings data type conversions and interface to the remote nonOracle system are contained in the gateway The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and nonOracle systems

The gateway can be installed on any machine It can be on the same machine as the Oracle database or on the same machine as the nonOracle system or on a third machine as a standalone Each configuration has its advantages and disadvantages The issues to consider when determining where to install the gateway are network traffic operating system platform availability hardware resources and storage

Configuring the Gateway

After installing the gateway(supplied with Oracle i setup package) perform the following tasks to configure the gateway for Microsoft SQL Server:

Configuring the Gateway(ORACLE_HOME\tgmsql\admin\initora on gateway side)

Configuring Oracle Net Services Listener for the Gateway(listenerora on gateway side)

Configuring the Oracle Database Server for Gateway Access(tnsnamesora on oracle side)

Creating Database Links(on oracle side)

Configuring the Gateway

Task : Choose a System Identifier for the Gateway

The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instanceThe SID is used as part of the file name for the initialization parameter file

Task : Customize the Initialization Parameter File

The initialization parameter file must be available when the gateway is started

如ORACLE_HOME\tgmsql\admin\initmytgmsqlora

注意HS_FDS_CONNECT_INFO= server_namedatabase_name

HS_FDS_DEFAULT_OWNER = 缺省用户

Configuring Oracle Net Services Listener for the Gateway

The gateway requires Oracle Net Services to provide transparent data access After configuring the gateway configure Oracle Net Services to work with the gateway

Configure Oracle Net Services TNS Listener for the Gateway

If you are already running a TNS listener that listens on multiple database SIDs add only the following syntax to SID_LIST in the existing listenerora file:

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

)

(SID_DESC=

)

(SID_DESC=

(SID_NAME=mytgmsql)

(ORACLE_HOME=oracle_home_directory)

(PROGRAM=tgmsql)

)

)

Configuring the Oracle Database Server for Gateway Access

Before you use the gateway to access Microsoft SQL Server data you must configure the Oracle database server to enable communication with the gateway over Oracle Net Services

Configuring Oracle Net Services for the Oracle Database Server

The tnsnamesora file is required by the Oracle database server accessing the gateway but not by the gateway Edit the tnsnamesora file to add a connect descriptor for the gateway:

connect_descriptor=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST= host_name)

(PORT= port_number)

)

(CONNECT_DATA=

(SID= mytgmsql))

(HS=OK))(HS=OK) specifies that this connect descriptor uses the Oracle Heterogeneous Services option

Creating Database Links

接下来Configuring the Gateway for Multiple Microsoft SQL Server Databases

Configuring the Gateway

Create Two Initialization Parameter Files

> cd ORACLE_HOME\tgmsql\admin

> copy inittgmsqlora inittgmsqlora

> copy inittgmsqlora inittgmsqlora

Change the value of the HS_FDS_CONNECT_INFO parameter in the new files

For inittgmsqlora enter the following:

HS_FDS_CONNECT_INFO=msql_ntdb

For inittgmsqlora enter the following:

HS_FDS_CONNECT_INFO=msql_ntdb

Configuring Oracle Net Services Listener for the Gateway

Add Entries to listenerora

Add two new entries to the TNS listener configuration file listenerora

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=tgmsql)

(ORACLE_HOME= oracle_home_directory)

(PROGRAM=tgmsql)

)

(SID_DESC=

(SID_NAME=tgmsql)

(ORACLE_HOME= oracle_home_directory)

(PROGRAM=tgmsql)

)

(SID_DESC=

(SID_NAME=tgmsql)

(ORACLE_HOME= oracle_home_directory)

(PROGRAM=tgmsql)

)

)

Configuring the Oracle Database Server for Gateway Access

tnsnamesora

old_db_using=(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(PORT=)

(HOST=gtwhost))

(CONNECT_DATA=

(SID=tgmsql))

(HS=OK))

new_db_using=(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(PORT=)

(HOST=gtwhost))

(CONNECT_DATA=

(SID=tgmsql))

(HS=OK))

new_db_using=(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(PORT=)

(HOST=gtwhost))

(CONNECT_DATA=

(SID=tgmsql))

(HS=OK))

Creating Database Links

上一篇：Inode 结构

下一篇：如何使用Leading提示改变表连接方式