歡迎光臨
每天分享高質量文章

這樣做,免費從Oracle同步資料

點擊▲關註 “資料和雲”   給公眾號標星置頂

更多精彩 第一時間直達

劉偉

劉偉,雲和恩墨軟體開發部研究院研究員;前微博DBA,主要研究方向為開源資料庫,分佈式資料庫,擅長自動化運維以及資料庫內核研究。

不得不承認的一點是,當前資料庫的使用趨勢,至少在國內,是逐漸從Oracle轉向MySQL(擴大化概念的話,就是包括PG等在內的開源資料庫,以及rds類的雲資料庫服務,後文統一以MySQL代指),但在實際的操作層面,如果涉及到現有業務改造,躲避不開的一點是,如何讓現有業務平滑地從Oracle切換到MySQL.

 

如果把這個問題局限在DBA的範疇,不考慮應用開發的難處,處理這個問題的普遍思路是,首先把Oracle做一個一致性備份,全量匯入到MySQL,然後從這個一致性的備份作為起點,對Oracle與MySQL同時進行業務操作(一般稱為雙寫),直到某個時間點(一般是兩邊資料庫資料同步時間差距比較小的時候),進行一次業務stop the world,然後切換過去.

 

在”雙寫”這個的處理上,就是八仙過海各顯神通了.有的是在程式入口分流,通過發佈訂閱佇列直接分開兩邊程式去跑,有的是在程式寫入的時候,DAO層隔離上層應用後,自己直接寫兩邊資料庫.等等方式,實際上手段太多了.

 

而本文討論的,則是在假設不對應用進行改造(佇列化,DAO雙寫等)的情況下,從Oracle直接同步資料到MySQL的手段.

 

並且是不花錢的.

 

當然Oracle本體的授權必須得買,省掉的,是Oracle GoldenGate這個”下船稅”.

 

對於並不關心全文細節的讀者,建議直接翻到文章最後面,下載我寫的測試腳本驗證.

 

Logminer介紹

 

如果是一個MySQL DBA,那他最常用的命令列程式之一,必然有mysqlbinlog這個,查故障,資料恢復等等用處簡直不要太多了.

 

而Oracle自帶的logminer,就是Oracle世界的mysqlbinlog.主要用途,就是去分析redo日誌(當然也包括歸檔日誌),從中提取出來資料的變更,解決故障,恢復資料.

 

比如oracle的確是支持閃回,但具體恢復到哪個scn編號,就得需要logminer來確定了.

而本文要用的的功能,則是用這種日誌分析,來處理”近”實時的資料同步問題.

 

Oracle作為閉源的資料庫,其redo格式雖然文件中有所提及,但實際上真的去做二進制檔案分析代價實在太大,這一領域最早的成功者GoldenGate轉手就被Oracle收了,並且考慮到法律問題,logminer就成了上帝給開的最後一扇窗戶了.

 

限制條件

 

曾經業內一位前輩說過,看技術先看限制條件,否則匆匆忙忙研究到最後,卻發現自己需求沒有滿足,就不好了.下文是我目前整理的一些logminer的註意點,以及限制以供參考.

 

如果是分析非本實體產生的日誌,則分析用的實體必須與日誌的源實體為同一個硬體平臺(註意不是操作系統),並且得是獨立的實體,版本號必須等於或者大於源實體,並且資料庫的字符集必須與源實體一致或者是超集(處理mysqlbinlog的字符集問題被坑一臉血的人應該對著限制深有感觸).

 

Logminer的運行目錄,僅能包含一個源資料庫的redo日誌,不能一個目錄下混合來自多個資料庫的redo日誌.

所有的redo日誌必須有相同的RESETLOGSSCN.並且得是8.0以上版本(部分功能得9.0.1以上版本)的Oracle產生的.

 

當然,最重要的,就是源資料庫必須打開歸檔樣式以及supplemental log.

 

在具體的資料型別以及表儲存型別支持上:

不支持BFILE.

不支持ADT(抽象資料型別)

不支持集合型別的列(嵌套表或者varry型別)

不支持取用物件.

不支持使用了表壓縮的表

不支持安全檔案.

 

除了這些之外,同通常的資料型別,以及表儲存型別都是支持的.

 

運行結構

 

Logminer在用法上,是Oracle內置的一套PL/SQL包,因此所有的運行,都是在Oracle實體內部的,其支持兩種樣式,簡單概括來說,一個是直接分析當前自己資料庫的歸檔以及redo日誌,另外一個,是分析其他Oracle資料庫的歸檔以及redo日誌.

 

操作步驟

 

前面說過,本文討論的是近實時同步,當然躲不開得寫程式(程式全文參考後文),而在寫程式之前,先得明白的是,人工執行每個命令的話,需要怎麼做.

 

在執行所有動作之前,需要設置歸檔樣式以及supplemental log,但和本文主題關係不大,就不展開步驟了,網上這方面資料太多了.

 

就執行logminer,簡單來說,有五步.

  1. 指定logminer的運行目錄

  2. 添加所需要分析的日誌檔案進入分析佇列.

  3. 啟動logminer.

  4. 分析redo日誌.

  5. 關閉分析會話.

 

以下就來詳細解釋每一步.

 

註意:下文假設操作都是在sqlplus操作.如果從程式調度,需要用begin end而非execute呼叫程式包.

 

1.指定logminer的運行目錄

在決定哪個目錄作為分析目錄之前,首先需要決定分析期間使用的資料字典用哪個.

 

Logminer提供了兩個選項(文件中的第三個選項flat file已經廢棄,不進行多餘討論),一個是直接使用當前資料庫作為資料字典來源,而另外一個,是使用獨立的logminer字典.

 

官方有個圖很好地說明瞭這兩個選項的選擇路線:

如果需要第一種:

EXECUTE DBMS_LOGMNR.START_LOGMNR(

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

 

如果需要第二種:

EXECUTE DBMS_LOGMNR_D.BUILD(

OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

 

需要註意的是,如果使用第二種方式,需要通過陳述句確定哪些redo日誌中儲存了資料字典,陳述句如下:

SELECT NAME FROM V$ARCHIVED_LOG WHEREDICTIONARY_BEGIN=’YES’;

SELECT NAME FROM V$ARCHIVED_LOG WHEREDICTIONARY_END=’YES’;

 

分析的時候,需要添加這些日誌進入分析流.

 

第二種方式需要定時執行以同步資料字典(比方DDL變更表結構之後),需要註意,否則會導致解析識別錯誤.

 

2.添加需要分析的日誌檔案進入分析佇列

 

這部分執行的,是DBMS_LOGMNR.ADD_LOGFILE這個儲存過程,這個儲存過程有兩個引數,一個是LogFileName,一個是options.

 

LogFileName是將要被分析的日誌的絕對路徑(分析過程中實際使用的目錄是啟動logminer時候指定的,在此處尚未指定)

 

Options則是可以指定兩個選項,一個是NEW,就是結束分析並開始新的分析流.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –

  LOGFILENAME => ‘/oracle/logs/log1.f’, –

  OPTIONS => DBMS_LOGMNR.NEW);

 

而ADDFILE就是添加指定的redo日誌到當前的分析流中.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –

  LOGFILENAME => ‘/oracle/logs/log2.f’, –

  OPTIONS => DBMS_LOGMNR.ADDFILE);

 

除了這種手動人工的添加方式之外,如果logminer是分析自己資料庫的日誌,就可以做到自動尋找日誌併進行分析,而不需要人工指定.

 

具體辦法是,在啟動logminer呼叫DBMS_LOGMNR.START_LOGMNR的時候,增加CONTINUOUS_MINE選項,這個選項會讓logminer從所有歸檔日誌以及redo日誌中,從指定的scn或者時間開始分析日誌,直到到達指定的結束scn編號,如果沒有指定結束時間/scn,那麼分析程式會一直維持分析狀態,任何資料庫實時寫入的資料,都會被”近”實時地分析到(在這裡,作者遇到的問題是,事務commit之後,並不會馬上被分析到,而是等幾分鐘之後才會被分析到,目前尚未確認原因,如果有人知道,望不吝賜教).

 

V$LOGMNR_LOGS視圖包含了當前已經添加的日誌檔案,包括自動發現的日誌檔案

 

3.啟動logminer

 

終於到啟動logminer,這個儲存過程的引數描述如下:

DBMS_LOGMNR.START_LOGMNR (

  startScn           IN NUMBERdefault 0,

  endScn             IN NUMBERdefault 0,

  startTime          IN DATE default’01-jan-1988′,

  endTime            IN DATE default’31-dec-2110′,

  DictFileName       IN VARCHAR2 default”,

  Options            INBINARY_INTEGER default 0 );

 

拋開顯而易見的start,end的四個引數,首先簡單說一下dictfilename.前文提到過,為了同步元資料,需要做幾個選擇,如果當時選擇的是第三種,也就是廢棄掉的使用檔案作為表元資料字典的話,就需要在這個引數指定那個檔案.

 

Options就非常多了,我們逐個梳理下,根據實際需求選擇.下麵列出表格提供參考.

COMMITTED_DATA_ONLY

如果選擇了這個選項,那麼就不會看到回滾或者執行中的事務生成的redo記錄對應的匯出內容,僅能看到提交成功的資料修改的操作記錄.

SKIP_CORRUPTION

如果掃描期間,遇到redo內資料塊(非redo頭)損壞的,就直接跳過

DDL_DICT_TRACKING

前文中有提到當分析的資料庫是另外的資料庫的話,需要使用redo或者檔案儲存資料字典,這個選項會在此基礎上,根據redo的記錄更新內部的資料字典,避免ddl執行導致的資料字典不一致的情況.

DICT_FROM_ONLINE_CATALOG

使用內部資料字典作為表元資料字典,僅適用於本實體分析,與DDL_DICT_TRACKING選項衝突.

DICT_FROM_REDO_LOGS

結合前文提到BUILD階段的REDO儲存資料字典的選項使用

NO_SQL_DELIMITER

輸出的SQL不包含分號,方便匯出的陳述句可以直接執行

NO_ROWID_IN_STMT

預設情況下,生成的SQL陳述句會包含ROW ID,對於僅關心實際資料的話,可以打開這個選項,但對於沒有主鍵或者唯一鍵的表,可能會導致錯誤的更新

PRINT_PRETTY_SQL

格式化輸出的SQL,方便閱讀,但不能直接用於執行

CONTINUOUS_MINE

讓logminer自動發現並掃描日誌檔案,啟動程式僅需要提供scn或者日期.從Oracle 10.1開始,支持Oracle  RAC環境下的日誌解析,還有一個註意點,Oracle  12.2開始,這個引數轉為廢棄,後續可能得想別的變通辦法處理這個問題.

 

呼叫的時候,options欄位,不同的選項以+連接,如下:

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=> –

  DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + –

  DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 

4.分析redo日誌

 

當啟動logminer之後,就可以提取分析出來的日誌了,方式很簡單,就是去查V$LOGMNR_CONTENTS表(需要select any dictionary權限),就可以按照順序獲取到所需要的日誌解析內容了.由於原表列數量非常多,我僅提取出來幾個一般會需要關註的欄位,詳細的描述的話,還是參考官方文件吧.

SCN

資料庫當前記錄的SCN

START_SCN

當前事務開始的SCN,僅在COMMITTED_DATA_ONLY選項啟用後有意義.

COMMIT_SCN

事務提交時候對應的SCN,僅在COMMITTED_DATA_ONLY選項啟用後有意義.

TIMESTAMP

資料變更對應的操作系統時間

START_TIMESTAMP

事務的開始時間,僅在COMMITTED_DATA_ONLY選項啟用後有意義.

COMMIT_TIMESTAMP

事務的提交時間,僅在COMMITTED_DATA_ONLY選項啟用後有意義.

XID

事務ID

OPERATION

SQL操作,這部分內容非常多,一般需要關心的,是INSERT,UPDATE,DELETE,DDL,COMMIT,ROLLBACK,  與事務以及資料操作直接相關的

SEG_OWNER

操作的資料段的擁有者,一般對應實際的表擁有者

SEG_NAME

資料段名稱,對應實際的表名稱或者表分割槽等

TABLE_NAME

操作的表名稱

SEG_TYPE_NAME

操作的段的型別,一般有表,索引以及分割槽等型別

TABLE_SPACE

操作的資料塊對應的表空間

ROW_ID

操作對應的ROW ID

USERNAME

事務的執行者,也就是運行SQL陳述句的用戶名稱

SQL_REDO

對於非臨時表,此處會生成資料操作對應的SQL陳述句

SQL_UNDO

對於非臨時表,此處會生成反轉操作的SQL陳述句,比如insert對應delete.delete對應insert,update更新新資料為老資料

CSF

如果超過4000位元組的SQL,則這個標記為1,表示下一行依然對應這一行的資料變更

SRC_CON_NAME

使用PDB的話,此處為PDB名稱

 

Oracle對這個視圖的查詢執行,看似是查視圖,實際上對應的是對日誌的順序掃描.

 

因此對這個視圖的select,切忌不要直接select * from之後,等拿到所有結果集再行處理,而應該以批次分段的形式處理,否則會導致連接oracle的客戶端程式占用過多記憶體,(比如select的時候,python用fetchmany或者fetchone接口,而非fetchall).

 

另外就是,如果未指定stop scn/時間的話,當查到最新行的時候,sql會話會一直阻塞住,直到有下一行資料產生.

 

在進一步的使用上, 通過DBMS_LOGMNR.MINE_VALUE處理REDO_VALUE/ UNDO_VALUE列可以用來處理並比較修改的資料,另外一個函式COLUMN_PRESENT則可用來保證MINE_VALUE函式的計算必定非null.示例如下:

SELECT

 (XIDUSN || ‘.’ || XIDSLT || ‘.’ || XIDSQN) AS XID,

 (DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, ‘HR.EMPLOYEES.SALARY’) –

  DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, ‘HR.EMPLOYEES.SALARY’)) AS INCR_SAL

  FROM V$LOGMNR_CONTENTS

  WHERE

  OPERATION = ‘UPDATE’ AND

  DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, ‘HR.EMPLOYEES.SALARY’) = 1 AND

  DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, ‘HR.EMPLOYEES.SALARY’) = 1;

 

5.關閉分析會話

 

簡單來說,就是呼叫DBMS_LOGMNR.END_LOGMNR函式,沒有別的花巧.

 

Demo代碼

https://pan.baidu.com/s/1BiFrrV1EyBOMTIcZT23Rkw

 

附件檔案oracle_dumper.py,是一個基於logminer寫的一個每分鐘錶變更行數量計算的腳本程式,作為後續編程處理的參考.文中忽略的oracle用戶授權,supplementlog打開等命令均在代碼註釋中,提供給感興趣的人參考.

    赞(0)

    分享創造快樂