实现Oracle10g与MSSQLServer 2005互联

实现与MSSQLServer 互联

 

不同平台的互连一般称之为数据库的异构服务，现在各大数据库之间都可以实现这样的异构互连，只是各厂商的具体实现不一样，如：在SQL   SERVER里面叫做LINKED   SERVER，通过ODBC实现与其它数据库的互联。

而ORACLE实现异构服务的技术叫做透明网关（Transparent   Gateway），当然之前ORACLE还采用过通用连接技术。目前ORACLE利用透明网关可以实现和SQL   SERVER、SYBASE、DB2等多种数据库的互联。

透明网关的体系结构也很简单，在ORACLE和MS SQL SERVER之间使用ORACLE透明网关实现互连互通，其中透明网关服务器可以与ORACLE或SQL   SERVER数据库在同一台主机上，也可以是在独立的一台主机上。

本文先讨论在oracle10g下如何配置透明网关去访问MS SQLServer2005数据库。然后再讨论MS SQLServer2005如何访问Oracle10g。

 

一．利用透明网关实现oracle10g访问MSSQLServer2005

1.    准备系统环境

假设有两台数据库服务器：

a. MS SQLServer2005数据库服务器

机器名：L15　IP：192.168.9.100

OS：Microsoft Windows XP Professional2002 Service Pack2

SQLServer2005数据库版本号:

Microsoft 2005 - 9.00.4035.00 (Intel X86)

Nov 24 13:01:59

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

数据库名：webdb

 

b. Oracle10g数据库服务器

机器名：L20　IP：192.168.9.120

OS：Microsoft Windows XP Professional2002 Service Pack2

Oracle数据库版本号: Oracle10g10.2.0.1

 

 

2.    下载透明网关

Oracle10g下，透明网关是一个单独的组件，需要单独下载

（10201_gateways_win32.zip）

 

3.    安装网关

前面提到了，透明网关可以安装在MS SQLServer2005数据库服务器上，也可以安装在Oracle10g数据库服务器，也可以安装在单独的一台服务器上，作为独立的透明网关服务器。本例是在MS SQLServer2005数据库服务器安装透明网关：

解压后，进入目录，点击setup.exe，启动安装界面，安装oracle transparent gateway for microsoft sql server；

先指定主目录详细信息：

选择需要安装的网关，我们是需要与MS SQLServer2005通信，因此需要选中如下图所示的选项。

输入sqlserver地址和sqlserver数据库名，这里你可以输入正确的，也可以随便输入，或者留空，我们可以安装完成后再配置。本例中的Microsoft SQL Server的值应该为“L15”，Microsoft SQL数据库的值应该为“webdb”

点击下一步后开始透明网关的安装

安装完后，会弹出配置监听界面，因为我们要手工配置，点击取消即可。当然与可以选中“执行典型配置”

4.    配置透明网关

在刚刚安装过网关的服务器（L15机器上）上配置透明网关参数文件，默认情况下，安装透明网关时会生成一个默认的参数文件：inittg4msql.ora，它的sid是tg4msql，你可以使用这个文件，也可以新建一个文件。本例选择默认的文件inittg4msql.ora。

进入$GATEWAY_HOME\tg_1\tg4msql\admin目录（这里的$GATEWAY_HOME是指透明网关的安装主目录)，本例的目录是：D:\oracle\product\10.2.0\tg_1\tg4msql\admin　，用记事本打开inittg4msql.ora，并编辑为如下内容（注意红色字体的内容为服务器的IP与SQLServer的数据库名）：

# 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=192.168.9.100;DATABASE=webdb"

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

 

如果是新建一个文件：initjss.ora，这个名字随便取，但要注意格式：init.ora，把它理解为oracle的参数文件就可以。

HS_FDS_CONNECT_INFO的设置有多种格式，如：

HS_FDS_CONNECT_INFO=server_name.db_name，但这种写法server_name不能为IP，且端口必须为默认的1433，如：

HS_FDS_CONNECT_INFO=L15.webdb

 

5.    配置监听器

在刚刚安装过网关的服务器（L15机器上）上配置监听器。

进入$GATEWAY_HOME\network\admin下，编辑listener.ora文件：

注意：下面的ORACLE_HOME实际上指的是安装透明网关的主目录，sid_name要与上一步配置的参数文件的名称相对应：

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME = PL***tProc)

     (ORACLE_HOME = D:\oracle\product\10.2.0\tg_1)

     (PROGRAM = extproc)

   )

   (SID_DESC=

        (SID_NAME=tg4msql)#SID_NAME是参数文件inittg4msql.ora里的tg4msql

        (ORACLE_HOME=D:\oracle\product\10.2.0\tg_1)

        (PROGRAM=tg4msql)

   )   

 )

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.9.100)(PORT =1521)) #此处HOST填的是透明网关所在机器（L15）的IP地址或机器名，端口号也是 网关机器对应的端口号 

     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

   )

 )

6.    启动监听

进入$GATEWAY_HOME\BIN\lsnrctl start

D:\oracle\product\10.2.0\tg_1\bin\lsnrctl start

 

C:\>D:\oracle\product\10.2.0\tg_1\bin\lsnrctl start

 

LSNRCTL for 32-bit Windows: Version10.2.0.1.0 - Production on 06-12月-2009 14:50:36

 

Copyright (c) 1991, 2005, Oracle. All rights reserved.

 

启动tnslsnr:请稍候...

 

TNSLSNR for 32-bit Windows: Version10.2.0.1.0 - Production

系统参数文件为D:\oracle\product\10.2.0\tg_1\network\admin\listener.ora

写入D:\oracle\product\10.2.0\tg_1\network\log\listener.log的日志信息

监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.100)(PORT=1521)))

监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY = EXTPROC0))

 

正在连接到(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.100)(PORT=1521)))

LISTENER的STATUS

------------------------

别名                     LISTENER

版本                     TNSLSNR for 32-bit Windows: Version10.2.0.1.0 - Production

启动日期                 06-12月-2009 14:50:38

正常运行时间             0天0小时0分1秒

跟踪级别                 off

安全性                   ON: Local OS Authentication

SNMP                     OFF

监听程序参数文件         D:\oracle\product\10.2.0\tg_1\network\admin\listener.ora

监听程序日志文件         D:\oracle\product\10.2.0\tg_1\network\log\listener.log

 

监听端点概要...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.100)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY = EXTPROC0))

服务摘要..

服务"PL***tProc"包含1个例程。

 例程"PL***tProc",状态UNKNOWN,包含此服务的1个处理程序...

服务"jss"包含1个例程。

 例程"tg4msql",状态UNKNOWN,包含此服务的1个处理程序...

命令执行成功

 

至此，在透明网关上的工作就算完成了。

7.    在Oracle10g数据库上配置

在Oracle10g数据库上配置tnsname.ora，添加内容如下：

tg4=

  (DESCRIPTION=

     (ADDRESS=

        (PROTOCOL=TCP)

        (HOST=192.168.9.100) #此HOST应和透明网关MSSQLSERVER机器上的IP或机器名

        (PORT=1521)

     )

     (CONNECT_DATA= (SID=tg4msql))#此SID应和透明网关MSSQLSERVER上设定的SID相同 

     (HS=OK) #这个很重要，表示支持异构服务器

   )

8.    创建数据库链接

用下面的方法建立数据库链接：

SQL＞create database link mssqldb connect tosaidentified by "123456" using 'tg4';

注：1.　sa是MSSQLServer的帐户名，密码要用引号引住，using后的网络服务名与要用引号引住。

2.　如果oracle服务器上没有创建网络服务名，则可以用下面的方法来建立数据库链接：

create public database link mssqldb

connect to sa

identified by "123456 "

using '(DESCRIPTION =

   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.9.100)(PORT= 521)) --SQLServer机器上的IP或机器名

   (CONNECT_DATA = (SID = tg4msql )) --SQLServer机器上的SID

   (HS=OK)

 ) ';

 

 

9.    测试连接

SQL> select * frommssqldb;

当从oracle数据库上发出这个sql时，实际上通过tnsname.ora配置的tg4信息转到10.10.10.2上，当10.10.10.2的监听监听到10.10.10.1的请求时，再次通过initjss.ora配置的HS_FDS_CONNECT_INFO信息转到sqlserver数据库上，最终把用户需要的数据返回。如果这个sql有记录返回，说明透明网关没有问题。

oracle的透明网关向前兼容做得不好，oracle 9i数据库不能通过10g的透明网关方位sqlserver。

10. 总结

访问SQLServer时SQL语句中的字段名经常要加引号。

      select “f1”from t1

访问SQLServer的Text字段要在Oracle中创建全局临时表导入后才能访问。 

建立数据库链接时密码要加引号以区分大小写（SQLServer是区分大小写的），

存在二进制的字段的表最好不要通过透明网关访问。SQLServer中的表名称、字段名成最好修改为大写。

透明网关连接SQLServer其实是一个不容易的事情，一是访问速度慢，二是不稳定，我在做这方面的时就发现经常做不成功。总之就是不可靠，但如果连接成功，基本是可靠的，只是访问速度慢点而已。

透明网关还可以连接到多个sqlserver上，且端口非默认端口，连接非默认端口的sqlserver比较麻烦，HS_FDS_CONNECT_INFO的设置要借助sqlserver别名来解析。（如果是的话就比较简单，用HS_FDS_CONNECT_INFO=://语法即可），本文不再阐述。

 

 

二．利用MSSQLServer2005数据库链访问oracle10g

有时候我们希望在一个MSSQLServer2005下访问另一个sqlserver数据库上的数据，或者访问其他oracle数据库上的数据，要想完成这些操作，我们首要的是创建数据库链接。

数据库链接能够让本地的一个sqlserver登录用户映射到远程的一个数据库服务器上，并且像操作本地数据库一样。那么怎么创建数据库链接呢？我现在有两种方法可以实现。

1.    在sqlserver2005的器实现

建立，这个比较简单的，首先在　"服务器对象"节点下的“数据库链接”节点　上点右键　，在出现的菜单中点　“新建数据库链接”　，然后会弹出一个界面，需要我们填写的有：链接服务器（这是一个名字，自己根据情况自行定义，以后就要通过他进行远程访问了），提供程序名称（这个就是选择数据驱动，根据数据库类型来选择，不能乱选，否则链接不上的），数据源（对于sqlserver就是远程数据库服务器的主机名或者IP,对于oracle　就是在oracle net config中配置的别名），安全上下文用户和口令（也就是远程服务器的用户和口令）。对于图形化建立与oracle10g的数据库链,如下两图的选项所示.

2.    利用系统存储过程

创建一个sqlserver对sqlserver的数据库链接：

exec sp_addlinkedserver  'link_northsnow','','SQLOLEDB','远程服务器主机名或域名或ip地址'

exec sp_addlinkedsrvlogin 'link_northsnow','false',null,'用户名','用户口令'

创建一个sqlserver对Oracle的数据库链接：

exec sp_addlinkedserver 'link_ora', 'Oracle', 'MSDAORA', 'oracle数据库服务器网络服务名'

exec sp_addlinkedsrvlogin 'link_ora', false, 'sa', 'oracle用户名', 'oracle用户口令'

有了数据库链接我们就可以使用了。对于sqlserver和oracle中的使用方法是有区别的。

对于sqlserver：

 create view v_lhsy_user as select * from link_northsnow.lhsy.dbo.sys_user

 select * from v_lhsy_user

其中lhsy为远程的数据库名

sys_user为表名

　       对于oracle：

create view vvv as select * from link_ora..SCOTT.EMP

select * from vvv;

其中SCOTT为远程oracle数据库服务器的一个用户名，EMP为该用户在该服务器上的一个表，要非常注意的是：数据库链接（link_ora）后面有两个点（..），再往后面必须全部大写，查询的对象一般为表格或者视图，不能查询同义词。

 

3.    删除数据库链接

要想删除数据库链接，也有两种方法，

一种是在企业管理器中操作，这个简单。

另一种是用系统存储过程：

exec sp_dropserver 数据库链接名称,'droplogins'

本文转载自：http://space.itpub.net/10532/viewspace-696713  转载请注明出处