簡易 Oracle 資料同步問題, 使用 Materialized View

環境

公司使用的 ERP 系統是屬於單一家公司單一個 Schema,在大陸工廠因速度的問題所以把資料庫都建置在當地,所以就變成台灣一個資料庫,大陸兩個工廠各有一個資料庫。

 

需求

部門在前兩年開始自行開發電子簽核系統,初期只針對台灣公司導入及開發,今年度開始嘗試把大陸兩廠納入到支援的範圍,當然程式必須要針對新的公司別(主要是資料庫的緣故)做不同幅度的調整,但是最令人頭痛的是調整後的執行速度。

影響速度的因素有三點,第一是兩岸間的網路速度,當時我們僅利用 Untangle 的 OpenVPN 把兩邊串起來,速度當就沒有辦法保障,第二點是我們為了開發程式(當然還有其他用途)方便,跟大陸資料庫的連結是使用 DB Link 的方式進行,轉一手的速度自然比較慢,最後一點是Web Server 是放置在台灣,所以只要是由大陸連結都要看到由台灣傳過去的畫面,再加上資料的往返,這樣自然快不起來。

這樣導致簽核速度的低落,更慘的是簽核最後一關通常都是高階長官,而簽核的內容都必須回填 ERP 系統上某些欄位,執行速度上最差可以到30-45秒,開始測試時就被罵得半死,在不影響整個程式的邏輯上 (因為整個翻寫成本太大),只能靠資料的功能來加速整個過程。

嘗試

Oracle 資料本身其實有提供 RAC 的功能,但是這玩意需要很高的授權費用,可能比我們自行修改程式的成本還高,一開始就打消這個念頭。

最簡單的當然就是 Trigger,只要大陸的資料庫有任何寫入、更新動作,就自動更新在台灣對應的 Table,很可惜這個作法只持續的半天,對岸的 User 就哇哇叫,在操作 ERP 存檔變得龜速;當然是因為寫入到資料庫時 Trigger 會因為還要兼顧寫回到台灣的資料庫,速度上會受到嚴重的影響。

此時腦海中就只出現了三個字,非同步,要解決檔案存取所耗掉的時間差,也就只能把台灣端當然成是主要的資料存取來源,透過非同步的方式去更新對岸的資料庫。

首先要解決的是對岸的資料要如何同步回台灣來,透過整個 Table 的 Export 及 import 是無法接受的,雖然這樣在實際上是可行但是過程變得相當緩慢,經過收集資料及詢問專家後,建議我們可以參考使用 Materialized View,這個View有點特別是的 (1) 建立好的 View 不會因為原始資料變化而變更,而是要下變更的指令才會更新,(2)鑒於以上的特性其他在資料庫她是有點以 Table 的方式記錄,所以她也可以建立 Index 喔,要做大量資料查詢時記得要把 Index 也同時建立,(3)既然她有Table 的特性,那也可對她 update (建立時多加入 for update) 指令,(4)可以 Update,那 Trigger 呢?也是有支援的啦。

我們的第二次嘗試就是建立 Materialized View,然後建立 Trigger 把原本要寫回到大陸資料庫先寫到台灣,然後透過 Trigger 寫到對岸去,當然前幾次少量測試時很正常,但是就開始遇到了更新 Materialized View 卡住,必須要砍掉 Session (kill session)的方式才能繼續下去,雖無明顯證據證明,判斷是大陸對同一個 table 更新,我們又用 trigger 的方式把資料又回寫到對方去,造成互相咬住的現象,當然這個方式運行的幾天,但每天幾乎都有狀況要人為介入調整,宣告失敗。

 

最終方案

在經過內部討論及諮詢專家意見後,在建立 Materialized View 時可以多加入 Log On 選項,該選項可以在來源端紀錄該 table 自從上次同步以後所有的異動紀錄,在更新 Materialized View 就可以很快速的執行完畢,這段時間同事也開發了一套非同步資料的程式,主要的作用是把原先要寫到遠端(大陸)的資料 SQL 指令先儲存到一個暫存 Table,然後利用排程執行一隻小程式去執行這些 SQL 指令,來達到非同步的目的,自從變更後以來已經執行了兩三周都不須人為介入。

相關指令:

在大陸端建立 Materialized View Log

create materialized view log on table_Name@DBLink_name with rowid;

本地端建立 Materialized view

Create materialized view log on table_Name@DBLink_name with rowid;

本地端更新 Materialized View

EXEC DBMS_MVIEW.REFRESH(‘Schema_Name.MView_Name’, atomic_refresh => FALSE);

強制全部更新:

EXEC DBMS_MVIEW.REFRESH(‘Schema_Name.MView_Name ‘, ‘C’);

當然要記得把Index 也要建立一下,否則查詢時資料會明顯地慢很多.

 

參考文件:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

發表迴響