ORACLE9i连接SYBASE的透明网关的配置

发表于:2007-05-25来源:作者:点击数: 标签:
ORACLE实现异种 数据库 连接服务的技术叫做透明网关(Transparent Gateway)。 目前ORACLE利用透明网关可以实现和SQL SERVER、SYBASE、DB2等多种主流数据库的互联。 现在通过oracle访问sybase数据库,把配置oracle9i TRANSPARENT GATEWAY FOR SYBASE 的步骤

ORACLE实现异种数据库连接服务的技术叫做透明网关(Transparent Gateway)。
目前ORACLE利用透明网关可以实现和SQL SERVER、SYBASE、DB2等多种主流数据库的互联。

现在通过oracle访问sybase数据库,把配置oracle9i TRANSPARENT GATEWAY FOR SYBASE
的步骤写成文档,供需要的网友参考!
 
配置TRANSPARENT GATEWAY FOR SYBASE步骤

1.
oracle所在服务器上安装sybase client(或者在同一台server上安装oracle、sybase服务器)
确保能够访问sybase数据库

2.
安装TRANSPARENT GATEWAY FOR SYBASE选件,要用自定义安装。
正确选择sybase的安装目录

3.
选择一个sid字符串准备赋给sybase数据库。如:tg4sybs
设置SYBASE的dll路径到环境变量PATH(这一步很重要)

4.
修改初始化文件,默认的是:
ORACLE_HOME\tg4sybs\admin\inittg4sybs.ora
设置参数
HS_FDS_CONNECT_INFO
格式:HS_FDS_CONNECT_INFO= server_name. database_name[,INTERFACE= interface_file]
server_name. database_name是大小写敏感的。
INTERFACE可选

例子:如下
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=migration_serv.tax
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

#
# Environment variables required for Sybase
#
set SYBASE=d:\sybase
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
上例中
server_name是migration_serv
database_name是tax

5.
配置oracle网络服务的listener,配置文件是:listener.ora
默认路径:ORACLE_HOME\network\admin
加入如下

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME= gateway_sid)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4sybs)
)
)

gateway_sid就是3选择的sid字符串
oracle_home_directory是ORACLE_HOME
tg4sybs若是SYBASE是特定的。如果是其他数据库,会不同。
例子如下:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
    (SID_DESC=
      (SID_NAME=tg4sybs)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM=tg4sybs)
    )
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

6.
停止监听

lsnrctl stop


重新启动监听程序

lsnrctl start

7.
配置oracle server的tnsnames.ora使其能够访问sybase
connect_descriptor=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= host_name)
(PORT= port_number)
)
(CONNECT_DATA=
(SID= gateway_sid))
(HS=OK))

connect_descriptor是连接串,任取,一般为sybs
host_name:oracle server的name
port_number:oracle监听端口
gateway_sid就是3选择的sid字符串

例子如下:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
sybs=
   (DESCRIPTION=
       (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=TCP)(HOST= dw-server1)(PORT= 1521))
       )
   (CONNECT_DATA=
       (SID= tg4sybs)
   )
       (HS=OK)
   )

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

8.建立database link

如:
CREATE  DATABASE LINK sybs  CONNECT TO sa
    IDENTIFIED BY prient 
    USING 'SBYS' ;
   
即可访问sybase 数据库。


需要注意的是,sybase数据库的表名,字段名,如果是小写的,那么在oracle里访问的时候要加上双引号""

如:
SQL〉select "a" from
"b"@sybs;


原文转自:http://www.ltesting.net