oracle透明网关的配置步骤

配置透明网关的步骤


我现在使用的OS是WIN2000
ORACLE 9.0
SQLSERVER2000
HOSTNAME ： SVR1
都使用 tcp/ip 默认端口


1: 安装 TRANSPARENT GATEWAY 选件（我安装的是FOR MSSQL）
安装完成了以后有这样一个目录 oracle_home/ora90/tg4msql

2: 配置 LISTENER.ORA
(SID_DESC =
(GLOBAL_DBNAME = tg4sql) # 可自己命名
(PROGRAM = tg4msql)
(SID_NAME = sql2000) # SID 自己命名
(ORACLE_HOME = D:/oracle/ora90)
)

3: 在 oracle_home/ora90/tg4msql/admin
建文件 init<SID>.ora 由于LISTENER里定义SID为 sql2000
因此文件名为：initsql2000.ora
其中内容为：
HS_FDS_CONNECT_INFO=svr1.master # svr1为SQL服务器名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

4: 重启 DATABASE AND LISTENER

5: 配置 tnsnames.ora
tg_sql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SVR1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sql2000)
)
(HS=OK)
)

6: 建立 DATABASE LINK
CREATE PUBLIC DATABASE LINK DB_SQL
CONNECT TO SA IDENTIFIED BY PASSWORD
USING 'tg_sql'

7: 完成，测试
select * from t_test@db_sql

8: 请注意使用 DBLINK 时，INIT.ORA中 GLOBAL_NAME 的设置，
如果为 ture , 请使用DBLINK的全称，可在 all_db_links 中查到，
如果为 FALSE， 不必使用全称，当出现
“ORA-02019：为找到远程数据库的连接说明”
时，请使用全称，如：
select * from t_test@db_sql.US.ORACLE.COM

--------------------------------------
补充两点

1. set global_names=false in 9i init.ora. Otherwise, ORA-02085.

2. For SQL 7: HS_FDS_CONNECT_INFO=SQLDB.pubs # where pubs would be the SQL db name you are to connect to.
For SQL 2000: You have to create a ODBC data source namely pubs and use the following syntax:
HS_FDS_CONNECT_INFO=pubs # where pubs is your ODBC data source name.
The reason in doing that is because DB library (SQLDB) in aging out in release of SQL 2000, OLEDB would be a preferable way.

---------------------------------------
基本步骤

说明：
HostName：dmserver
Win2000 Server Oracle Database 9.2.0.1.0 Microsoft SQL Server 2000
UserName:User Password:User
Database:CFDai TableName:Test
都使用 tcp/ip 默认端口1521
基本步骤：
1: 安装 TRANSPARENT GATEWAY FOR MSSQL选件，输入主机名和数据库名
安装完后，在d:/oracle/ora92的目录下产生一个目录D:/oracle/ora92/tg4msql
同时，自动生成文件：inittg4msql.ora
其中内容为：
HS_FDS_CONNECT_INFO=dmserver.CFDai #服务器名.数据库名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
注：第一行可写为HS_FDS_CONNECT_INFO="dmserver;DATABASE=CFDai"

2: 配置 LISTENER.ORA
SID_LIST_LISTENER =
(SID_LIST =
……
(SID_DESC =
(GLOBAL_DBNAME = tg4msql) #可重命名
(PROGRAM = tg4msql)
(SID_NAME = tg4msql) #可重命名
(ORACLE_HOME = D:/oracle/ora92) #数据库主目录名
)
)

3: 停止并重启 DATABASE 和 LISTENER服务

4: 配置 tnsnames.ora，在文件尾增加以下内容：
tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dmserver)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK)
)

5: 在SQL Plus或SQLPlus Worksheet中通过如下语句建立数据库连接
CREATE PUBLIC DATABASE LINK DB_SQL CONNECT TO User IDENTIFIED by User USING 'tg4msql';

6: 在SQL Plus或SQLPlus Worksheet中测试
select * from test@DB_SQL;

7：取消数据库连接
DROP PUBLIC DATABASE LINK DB_SQL;

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

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

搜索关键字：
Oracle Transparent Gateways
透明网关

Oracle database applications can be executed against non-Oracle 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 non-Oracle system are contained in the gateway. The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle 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 non-Oracle 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 9i setup package), perform the following tasks to configure the gateway for Microsoft SQL Server:
1.Configuring the Gateway(ORACLE_HOME/tg4msql/admin/init<SID>.ora on gateway side)
2.Configuring Oracle Net Services Listener for the Gateway(listener.ora on gateway side)
3.Configuring the Oracle Database Server for Gateway Access(tnsnames.ora on oracle side)
4.Creating Database Links(on oracle side)

1.Configuring the Gateway
Task 1: Choose a System Identifier for the Gateway
The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance.The SID is used as part of the file name for the initialization parameter file.
Task 2: Customize the Initialization Parameter File
The initialization parameter file must be available when the gateway is started.
如：ORACLE_HOME/tg4msql/admin/initmytg4msql.ora
注意：HS_FDS_CONNECT_INFO= server_name.database_name
HS_FDS_DEFAULT_OWNER = 缺省用户

2.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 listener.ora file:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=.
.
)
(SID_DESC=.
.
)
(SID_DESC=
(SID_NAME=mytg4msql)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=tg4msql)
)
)
3.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 tnsnames.ora file is required by the Oracle database server accessing the gateway, but not by the gateway. Edit the tnsnames.ora file to add a connect descriptor for the gateway.:
connect_descriptor=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= host_name)
(PORT= port_number)
)
(CONNECT_DATA=
(SID= mytg4msql))
(HS=OK))--(HS=OK) specifies that this connect descriptor uses the Oracle Heterogeneous Services option.

4.Creating Database Links


接下来：Configuring the Gateway for Multiple Microsoft SQL Server Databases
1.Configuring the Gateway
Create Two Initialization Parameter Files
> cd ORACLE_HOME/tg4msql/admin
> copy inittg4msql.ora inittg4msql2.ora
> copy inittg4msql.ora inittg4msql3.ora
Change the value of the HS_FDS_CONNECT_INFO parameter in the new files.
For inittg4msql2.ora, enter the following:
HS_FDS_CONNECT_INFO=msql_nt2.db2
For inittg4msql3.ora, enter the following:
HS_FDS_CONNECT_INFO=msql_nt3.db3
2.Configuring Oracle Net Services Listener for the Gateway
Add Entries to listener.ora
Add two new entries to the TNS listener configuration file, listener.ora.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=tg4msql)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
(SID_DESC=
(SID_NAME=tg4msql2)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
(SID_DESC=
(SID_NAME=tg4msql3)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
)
3.Configuring the Oracle Database Server for Gateway Access
tnsnames.ora ：
old_db_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql))
(HS=OK))
new_db2_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql2))
(HS=OK))
new_db3_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql3))
(HS=OK))
4.Creating Database Links

------------------------------------
我的理解（e文太差，读不懂文档）：
所谓透明网关就是在oracle中增加一个服务，这个服务负责对sqlserver（或者其他数据库）的连接。sqlserver对而客户端（sqlplus等）来说是透明的，sqlplus只需象访问oracle数据库一样访问sqlserver。

配置透明网关需要两步：
1。配置一个在oracle服务器上的监听
2。配置一个tnsname，该tnsname指向透明网关所在的位置（即oralce服务器），而不是sqlserver所在的服务器，并且使用刚刚第一步配置的监听。所以在tnsnames.ora中HOST = serveraddr应该是oracle所在的地址，而不是sqlserver所在的地址（我就是这样配才测试通过的）。我不能理解为什么这么多大侠用host＝sqlserveraddr都能成功。