知識星球點選▲關註 “資料和雲” 給公眾號標星置頂
更多精彩 第一時間直達
劉偉
劉偉，雲和恩墨軟體開發部研究院研究員；前微博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,簡單來說,有五步.
-
指定logminer的執行目錄
-
新增所需要分析的日誌檔案進入分析佇列.
-
啟動logminer.
-
分析redo日誌.
-
關閉分析會話.
以下就來詳細解釋每一步.
註意:下文假設操作都是在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開啟等命令均在程式碼註釋中,提供給感興趣的人參考.
