MS SQL到Oracle的數據遷移筆記
一、任務背景
舊系統使用MS SQL Server數據庫,新系統使用Oracle數據庫,現在需要將舊系統中的數據遷移到新系統中,舊數據按照約定的規則轉換後,能夠在新系統中使用或查詢。另外,新系統的實施人員並不清楚舊系統的數據結構,且兩個新舊兩個系統的使用的數據結構有較大的差異。為了敘述方便,將舊系統維護人員簡稱為A,新系統實施人員簡稱為B。
二、總體思路
1、 A與B根據各自了解的信息,一起制定中間表;
2、 A從MS SQL Server提取數據,並導出為中間表數據,保存為txt文件;
3、 B將txt文件中的數據導入Oracle中間表;
4、 B將Oracle中間表的數據,合並到業務邏輯使用的數據表。
三、具體實現
第1步,涉及到具體的業務邏輯,此處省略。
第2步,使用MS SQL Server 的BCP命令從MS SQL Server導出數據,並且使用批處理將多個表一起導出(此處只列出一個表),見下圖:
第3步使用Oracle 的sqlldr命令將上一步導出的數據導入Oracle,見下圖:
控制文件:
第4步,涉及到具體業務邏輯的轉換,這裡使用Merge合並數據,同時兼容增量導入的需要。此處也省略。
最後將這三步使用批處理連貫起來,形成一個文件,最後只需執行此批處理文件即可完成從MS SQL Server導出,到導入Oracle和執行Oracle中的Procedure的整個數據遷移過程。
目錄結構見下圖:
說明:
bad:存放sqlldr執行過程中發生的錯誤信息
bat:存放導出、導入、執行sql文件夾中SQL的批處理文件,見下圖:
執行sql文件夾中SQL的批處理文件:
bat目錄的文件:
ctl:存放sqlldr所需的CTL文件
data:存放BCP導出和sqlldr導入的數據
sql:存放執行Oracle 執行的SQL,在每一個sql文件中SQL語句的最後加上一句”EXIT;“
DATA_Export_Import_3in1.bat:將bat中所有批處理文件按順序合並執行,並且輸出每一步的執行log信息,見下圖。運行此文件即可完成整個過程的數據遷移。
四、總結
剛開始做這個任務的時候,使用的是SQL Developer的復制表功能——從MS SQL Server數據庫將表拷貝到Oracle,見下圖:
使用這個方法,可以順利將數據表拷貝到Oracle,而且操作方便簡潔,但是速度比較慢,兩種方法的比較見下表(由實際操作中多次導數的統計結果):
從上表可以看出,使用”BCP & SQLLDR“方法在同樣數據量的情況下,耗時遠小於”SQL Developer“的方法。
另外在實踐中,還發現使用”BCP & SQLLDR“有以下幾個特點:
1、可以再導出時對要導出的數據進行篩選,通過在BCP命令中編寫SQL;
2、可以對要導入的數據進行一些簡單的格式化(如去除空格、替換不需要的字符等),通過在SQLLDR的CTL文件中處理。
3、整個執行過程都可以獲取log,對出錯問題的處理比較方便。
---------------------------------------------------------------------------------------------------------------------------------------
以上內容為個人實踐總結,如有不足之處,敬請指出。
不知道你所說的“想換ORACLE數據庫”是什麼意思,我只告訴你,如果是你公司有套程序想換後台數據庫,那麼導數據進入oracle不算復雜,照一樓兄弟說的做就可以了。但是麻煩的是SqlServer用的是TSQL,Oracle用的是PL/Sql,兩種sql語言是不完全兼容的(比如好多函數甚至語法都不同),這就意味著僅僅是把數據導入oracle是不行的,前台的程序也需要重新修改,如果程序比較大的話,這部分的工作是相當繁重的。
以上意見僅供樓主參考。
可以用powerbuilder或pl/SQL developer或者sql loader都可以做,最好的工具應該是sql loader但是這個工具比較靈活比較復雜,非常強大