通過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安裝目錄，會發現多出來一個檔案夾：

進入該檔案夾下\dg4msql\admin目錄下有一個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      

進入該檔案夾下\NETWORK\ADMIN，配置監聽配置檔案listener.ora，添加以下一段配置：

# listener.ora Network Configuration File: D:\oracle\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = dg4msql1)     --此處SID即為前面init.ora檔案名裡指定的SID
    (ORACLE_HOME = D:\oracle\product\11.2.0\tg_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:\oracle\product\11.2.0\tg_1      

配置檔案tnsnames.ora

# tnsnames.ora Network Configuration File: D:\oracle\product\11.2.0\tg_1\NETWORK\ADMIN\tnsnames.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 [email protected];

如果是要将全部表都導入過來，表數量太多，一個一個寫語句太過繁瑣，可考慮在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 [email protected];

通過資料導入的方式，也會發現導入的表中列名會有大小寫的區分，因為在通過該種結果集方式預設建表，是會将結果集中的列名直接帶過來的，在表檢視界面查詢sql，會發現所有的列名都是帶上雙引号的，如下圖：

通過“create table tab_userinfo as select * from [email protected] 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腳本進行導入，本文就不再列出該解決方案，建議除非實際業務需要，否則可略過這部分表的導入。