X oracle直接读写ms sqlserver数据库（一）如何下载oracle database gateway for sqlserver

oracle直接读写ms sqlserver数据库（一）如何下载oracle database gateway for sqlserver

 

想从Oracle实时同步数据到Ms Sqlserver，需要在Oracle里面直连Sqlserver进行数据的读写，可以在Oracle服务器上安装oracle database gateway for sqlserver组件，关于如何安装此组件，网上找不到详细资料，特将我的安装过程记录下来分享给需要的网友：

1.Oracle企业版自带了gateway for odbc，但for Sqlserver版并未安装，需要到官网下载。首先打开Oracle官网，进入Oracle 11g下载首页（根据自己的版本）：

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

根据服务器的机型选择32位或64位的版本，并点击版本下载链接后面的“See All ”链接：

2.然后找到Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows

并下载。此文件有538M。

===================================================================================================================================================

===================================================================================================================================================

通过Oracle Database Gateways进行数据迁移的解决方案

在某客户系统中，涉及到将之前老业务系统中的数据（如客户资料、缴费记录等）迁移到新系统数据库中，需要保证燃气公司各项业务不中断，做到无缝链接。 老系统数据库为Sql Server 2000，存在数据库模型较混乱，结构不清晰等问题，另外因老业务系统已经运行很多年，数据量较大（包含15万燃气用户，将近20年的业务数据量），需要在数据迁移后保证数据准确无误。

1 概述

1.1 问题或场景

在某客户系统中，涉及到将之前老业务系统中的数据（如客户资料、缴费记录等）迁移到新系统数据库中，需要保证燃气公司各项业务不中断，做到无缝链接。

老系统数据库为Sql Server 2000，存在数据库模型较混乱，结构不清晰等问题，另外因老业务系统已经运行很多年，数据量较大（包含15万燃气用户，将近20年的业务数据量），需要在数据迁移后保证数据准确无误。

1.2 目的

本文介绍通过Oracle Database Gateways工具将SqlServer2000数据库迁移到Oracle11g的解决方案。

1.3 工具和技术

Oracle Database Gateways：Oracle Database Gateways是Orace数据库企业版中的重要工具之一，最新版本与Oracle企业版一起发布，版本号为12c，其中包括多个第三方数据库的版本。本文主要介绍11g版本Oracle Database Gateway For SqlServer的应用。下载地址http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/112010-win64soft-086667-zhs.html

1.4 系统运行环境

Sql Server 2000，Oracle 11g

2 总体解决方案

2.1 安装和配置Oracle Database Gateways

Oracle Database Gateways安装比较简单，其中几个关键选项如下：

勾选服务：

填写数据库（可以先任意填写，然后在配置文件中修改）：

安装完毕后，会弹出界面需要配置监听，该监听与Oracle数据库监听服务区分开，默认端口为1522，可以配置服务名与端口号，本文举例服务名取为OracleOraGtw11g_home1TNSListener，端口为1522。配置完后，系统服务会多出来一个监听服务：

监听配置完毕后，进入Oracle安装目录，会发现多出来一个文件夹：

进入该文件夹下dg4msqladmin目录下有一个initdg4msql.ora文件，该文件的格式是init<SID>.ora，这里的SID在后面需要用到，系统默认的是dg4msql，一般情况这样就可以了，如果改名，在之后使用这个修改后的名字即可，比如本文举例修改为dg4msql1，则文件名变成initdg4msql1.ora。该文件是Gateway的初始参数文件，描述连接的是哪个SQL Server数据库。打开该文件，格式如下：

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="127.0.0.1;database=xfsoft"    --此处配置SQL Server数据库的IP和数据库名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=sa  --此处配置SQL Server数据库的用户名
HS_FDS_RECOVERY_PWD=admin    --此处配置SQL Server数据库的密码

注意如果是连接Sql Server2008数据库，配置信息如下：

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[127.0.0.1]/MSSQLSERVER/xfsoft  --与2000数据库差异：中间需配置Sql Server 2008服务名，配置格式也有变化
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=sa
HS_FDS_RECOVERY_PWD=fr_tcisA

进入该文件夹下NETWORKADMIN，配置监听配置文件listener.ora，添加以下一段配置：

# listener.ora Network Configuration File: D:oracleproduct11.2.0	g_1NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = dg4msql1)     --此处SID即为前面init.ora文件名里指定的SID
    (ORACLE_HOME = D:oracleproduct11.2.0	g_1)    --此处配置ORACLE_HOME
    (PROGRAM = dg4msql)      --此处为指明程序名称，为固定值不能修改
  )
)
LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
  )
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522))
  )
)
ADR_BASE_LISTENER = D:oracleproduct11.2.0	g_1

配置文件tnsnames.ora

# tnsnames.ora Network Configuration File: D:oracleproduct11.2.0	g_1NETWORKADMIN	nsnames.ora
# Generated by Oracle configuration tools.
dg4msql1 =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = dg4msql1)  --此处SID要与前面init.ora文件名里指定的SID一致
  )
  (HS = OK)                    -此处为固定配置，指定是外部程序而不是Oracle数据库实例
)

以上配置完毕后，重新启动下监听服务。再进入Oracle数据库创建DBLink，

create public database link mssqldb connect to "sa" identified by "admin" using 'dg4msql1';

其中“sa”、“admin”分别为访问SqlServer数据库用户名和密码，“dg4msql1”为之前配置的SID名。

通过PLSQL Developer访问数据库，即可直接通过DBLink访问SqlServer数据库表了：

2.2 数据导入

通过PLSQL Developer，即可同时访问Oracle和SqlServer数据库，极大的方便了数据迁移的便利，但如果单表数据量比较大，中间又通过Gateway解析消耗，访问速度受到影响，所以建议将SqlServer数据表导入到Oracle临时表中，例如通过下列语句导入：

create table tab_userinfo as select * from Tab_Userinfo@mssqldb;

如果是要将全部表都导入过来，表数量太多，一个一个写语句太过繁琐，可考虑在SqlServer中查询系统视图，将该库所有表查询生成该sql的结果集，然后在PLSQL Developer端命令行批量执行，如下所示：

执行以下语句

select 'create table ' + name + ' as select * from ' + name + '@mssqldb;' from sys.all_objects where type='U';

结果集：

然后复制该结果集数据在PLSQL Developer命令窗口中粘贴可批量进行处理；

通过该方式即可将所有表导入到Oracle数据库中，此后再编写数据迁移脚本就方便多了。

3 注意事项

3.1 SqlServer数据库对象区分大小写问题

Oracle数据库的对象名是不区分大小写的，比如创建数据表，如果表名或列名不用双引号括起来，T_KH_USERINFO 与t_kh_userinfo是一致的，列名USERID与userid也是一致的；但在SqlServer对象名是区分大小写的，所以通过Gateway访问查询过来的表名和列名是都区分大小写的。这样访问SqlServer数据库相当的麻烦，比如你查询SqlServe数据库中某张表的某个字段，需要弄清楚大小写，然后加上双引号才能访问。比如：

select "UserList","UserID","UserNo","UserName","Address","AreaNo" from tab_userinfo@mssqldb;

通过数据导入的方式，也会发现导入的表中列名会有大小写的区分，因为在通过该种结果集方式默认建表，是会将结果集中的列名直接带过来的，在表查看界面查询sql，会发现所有的列名都是带上双引号的，如下图：

通过“create table tab_userinfo as select * from Tab_Userinfo@mssqldb where 1=2;”语句建表

如果一条一条写如下的重命名列的语句，会相当的繁琐：

alter table tab_userinfo rename column "UserID" to UserID;

于是还是需要通过在SqlServer查询分析器中访问SqlServer数据库系统视图，生成相应的结果集来批量处理：

select 'alter table ' + b.name + ' rename column '+'"' + a.name + '"' +' to ' + a.name + ';'

from sys.all_columns a

left join sys.all_objects b on a.object_id = b.object_id

where a.object_id

in(select object_id from sys.all_objects where type='U');

查询结果集：

然后将复制到PLSQL Developer命令窗口中批量执行即可。

3.2 部分字段类型无法导入

在导入SqlServer数据库数据表会发现，部分SqlServer字段，如image、ntext、text等，这些字段类型在Oracle数据库并没有对应字段想匹配，所以在查询包含这部分字段类型的表时，会出现无法查询、无法导入的问题：

碰到该类问题，需要在SqlServer端写脚本将数据导入出成文本，然后再通过编写PLSQL脚本进行导入，本文就不再列出该解决方案，建议除非实际业务需要，否则可略过这部分表的导入。