配置透明网关的步骤

cfdai

for SQL Server透明网关已经配通，谢谢！

sim

to cfdai:

可否講講你的方法 ?  謝謝 !

yyy

我今天捣鼓了一天！
cfdai把你的贴出来吧！

拜托！！！

cfdai

说明：
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;

yyy

多谢！我也刚刚搞定。
原来是一个低级错误——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

玉面飞龙

帮我看看 我的测试怎么过不去


环境
win2000server   Oracle 9.02 enterprise  Sqlserver2000

主机名字:YUELAIKEZHAN
我的inityuelaikezhan.ora
HS_FDS_CONNECT_INFO="YUELAIKEZHAN.master"
#YUELAIKEZHAN为SQL服务器名
#HS_FDS_CONNECT_INFO=LocalServer
# 配置了sql server的odbc  但是这个也不行 同样的错误
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_DEFAULT_OWNER=sa
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
我的lisener文件
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = E:\Oracle9i\Oracle)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = yuelaikezhan) # 可自己命名
      (PROGRAM = tg4msql)
      (SID_NAME = yuelaikezhan) # SID 自己命名
      (ORACLE_HOME = E:\Oracle9i\Oracle)
    )

我的tnsname文件:
yuelaikezhan =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = YUELAIKEZHAN)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = yuelaikezhan)
    )
    (HS=OK)
  )


数据库和监听器都重新启动后
SQL> startup force
ORACLE 例程已经启动。

Total System Global Area  110172924 bytes
Fixed Size                   453372 bytes
Variable Size              92274688 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select * from spt_monitor@db_sql;
select * from spt_monitor@db_sql
                          *
ERROR 位于第 1 行:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Transparent gateway for MSSQL][H006] The init parameter <HS_FDS_CONNECT_INFO>
is not set. Please set it in init<orasid>.ora file.
ORA-02063: 紧接着2 lines（源于DB_SQL）

我在inityuelaikezhan.ora里面把 "yuelaikezhan.master"改成一个odbc后还是不行 同样的错误
此时
SQL> host lsnrctl status
服务 "orcl" 包含 2 个例程。
  例程 "orcl", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
  例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orclXDB" 包含 1 个例程。
  例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "yuelaikezhan" 包含 1 个例程。
  例程 "yuelaikezhan", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功


可能是哪里的问题那?

guostong

HS_FDS_CONNECT_INFO="YUELAIKEZHAN.master"

去掉引号试试

cfdai

1。改为HS_FDS_CONNECT_INFO=YUELAIKEZHAN.master
或者HS_FDS_CONNECT_INFO="SERVER=YUELAIKEZHAN;DATABASE=master"
2。SQL Server必须是用户登陆的方式连接，而不是直接用Windows系统用户的方式

aleckqian

hiwanghao：真想找你要这个东西呢，没想到就自己发现了它。

liuxnp

我的OS是WINDOWS2000 server SP3
ORACLE 9.2.0.1
SQLSERVER2000
HOSTNAME ：websofts2003
都使用 tcp/ip 默认端口


1: 安装 TRANSPARENT GATEWAY 选件（我安装的是FOR MSSQL）
安装完成了以后有这样一个目录 oracle_home\ora920\tg4msql
安装的过程中要求输入服务器名和数据库，我这里输入的是sql2000的服务器和数据库名，所以都是websofts2003。

2: 配置 LISTENER.ORA
SID_LIST_LISTENER=                                      
  (SID_LIST=                                            
      (SID_DESC=                                       
         (SID_NAME=tg4msql)#看inittg4msql.ora                             
         (ORACLE_HOME=D:\oracle\ora92)
         (PROGRAM=tg4msql)                              
       )                                                
      )  

3: 在 oracle_home\ora920\tg4msql\admin
inittg4msql2000.ora
其中内容为：
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=websofts2003.websofts2003"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

4: 重启 DATABASE AND LISTENER
SQL> connect sys/caldera as sysdba

已连接。
SQL> alter system set global_names=false;

系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。

LSNRCTL> stop
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
命令执行成功
LSNRCTL> start
启动tnslsnr：请稍候...
。。。。。。
命令执行成功

5: 配置 D:\ORACLE\ora92\tg4msql\admin\tnsnames.ora
tg_sql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = websofts2003)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK)
)

6: 建立 DATABASE LINK
SQL> CREATE PUBLIC DATABASE LINK DB_SQL
  2  CONNECT TO SA IDENTIFIED BY PASSWORD
  3  USING 'tg_sql'
  4  /

数据库链接已创建。

SQL> CREATE PUBLIC DATABASE LINK MS_SQL
  2  CONNECT TO SA IDENTIFIED BY PASSWORD
  3  USING 'test'
  4  /

数据库链接已创建。

7: 完成，测试
SQL> select * from t3@db_sql;
select * from t3@db_sql
                 *
ERROR 位于第 1 行:
ORA-12154: TNS: 无法处理服务名

SQL> select * from t3@ms_sql.US.ORACLE.COM;
select * from t3@ms_sql.US.ORACLE.COM
                 *
ERROR 位于第 1 行:
ORA-12154: TNS: 无法处理服务名

CREATE PUBLIC DATABASE LINK DB_SQL CONNECT TO sa IDENTIFIED by PASSWORD USING 'tg4msql';