欧美一区二区三区,国内熟女精品熟女A片视频小说,日本av网,小鲜肉男男GAY做受XXX网站

如何將一個Oracle語句轉(zhuǎn)換成mysql的語句

錢琪琛2年前29瀏覽0評論
如何將一個Oracle語句轉(zhuǎn)換成mysql的語句?

OGG全稱為Oracle GoldenGate,是由Oracle官方提供的用于解決異構數(shù)據(jù)環(huán)境中數(shù)據(jù)復制的一個商業(yè)工具。相比于其它遷移工具OGG的優(yōu)勢在于可以直接解析源端Oracle的redo log,因此能夠?qū)崿F(xiàn)在不需要對原表結構做太多調(diào)整的前提下完成數(shù)據(jù)增量部分的遷移。本篇文章將重點介紹如何使用OGG實現(xiàn)Oracle到MySQL數(shù)據(jù)的平滑遷移,以及講述個人在遷移過程中所碰到問題的解決方案。

(一)OGG邏輯架構

參照上圖簡單給大家介紹下OGG邏輯架構,讓大家對OGG數(shù)據(jù)同步過程有個簡單了解,后面章節(jié)會詳細演示相關進程的配置方式,在OGG使用過程中主要涉及以下進程及文件:

Manager進程:需要源端跟目標端同時運行,主要作用是監(jiān)控管理其它進程,報告錯誤,分配及清理數(shù)據(jù)存儲空間,發(fā)布閾值報告等Extract進程:運行在數(shù)據(jù)庫源端,主要用于捕獲數(shù)據(jù)的變化,負責全量、增量數(shù)據(jù)的抽取Trails文件:臨時存放在磁盤上的數(shù)據(jù)文件Data Pump進程:運行在數(shù)據(jù)庫源端,屬于Extract進程的一個輔助進程,如果不配置Data Pump,Extract進程會將抽取的數(shù)據(jù)直接發(fā)送到目標端的Trail文件,如果配置了Data Pump,Extract進程會將數(shù)據(jù)抽取到本地Trail文件,然后通過Data Pump進程發(fā)送到目標端,配置Data Pump進程的主要好處是即使源端到目標端發(fā)生網(wǎng)絡中斷,Extract進程依然不會終止Collector進程:接收源端傳輸過來的數(shù)據(jù)變化,并寫入本地Trail文件中Replicat進程:讀取Trail文件中記錄的數(shù)據(jù)變化,創(chuàng)建對應的DML語句并在目標端回放二、遷移方案(一)環(huán)境信息OGG版本 OGG 12.2.0.2.2 For Oracle OGG 12.2.0.2.2 For MySQL 數(shù)據(jù)庫版本 Oracle 11.2.0.4 MySQL 5.7.21 OGG_HOME /home/oracle/ogg /opt/ogg (二)表結構遷移表結構遷移屬于難度不高但內(nèi)容比較繁瑣的一步,我們在遷移表結構時使用了一個叫sqlines的開源工具,對于sqlines工具在MySQL端創(chuàng)建失敗及不符合預期的表結構再進行特殊處理,以此來提高表結構轉(zhuǎn)換的效率。注意:OGG在Oracle遷移MySQL的場景下不支持DDL語句同步,因此表結構遷移完成后到數(shù)據(jù)庫切換前盡量不要再修改表結構。(三)數(shù)據(jù)遷移數(shù)據(jù)同步的操作均采用OGG工具進行,考慮數(shù)據(jù)全量和增量的銜接,OGG需要先將增量同步的抽取進程啟動,抓取數(shù)據(jù)庫的redo log,待全量抽取結束后開啟增量數(shù)據(jù)回放,應用全量和增量這段期間產(chǎn)生的日志數(shù)據(jù),OGG可基于參數(shù)配置進行重復數(shù)據(jù)處理,所以使用OGG時優(yōu)先將增量進行配置并啟用。此外,為了避免本章節(jié)篇幅過長,OGG參數(shù)將不再解釋,有需要的朋友可以查看官方提供的Reference文檔查詢?nèi)魏文悴焕斫獾膮?shù)。1.源端OGG配置(1)Oracle數(shù)據(jù)庫配置針對Oracle數(shù)據(jù)庫,OGG需要數(shù)據(jù)庫開啟歸檔模式及增加輔助補充日志、強制記錄日志等來保障OGG可抓取到完整的日志信息查看當前環(huán)境是否滿足要求,輸出結果如下圖所示:(2)Oracle數(shù)據(jù)庫OGG用戶創(chuàng)建OGG需要有一個用戶有權限對數(shù)據(jù)庫的相關對象做操作,以下為涉及的權限,該示例將創(chuàng)建一個用戶名和密碼均為ogg的Oracle數(shù)據(jù)庫用戶并授予以下權限(3)源端OGG 管理進程(MGR)配置(4)源端OGG 表級補全日志(trandata)配置表級補全日志需要在最小補全日志打開的情況下才起作用,之前只在數(shù)據(jù)庫級開啟了最小補全日志(alter database add supplemental log data;),redolog記錄的信息還不夠全面,必須再使用add trandata開啟表級的補全日志以獲得必要的信息。(5)源端OGG 抽取進程(extract)配置Extract進程運行在數(shù)據(jù)庫源端,負責從源端數(shù)據(jù)表或日志中捕獲數(shù)據(jù)。Extract進程利用其內(nèi)在的checkpoint機制,周期性地檢查并記錄其讀寫的位置,通常是寫入到本地的trail文件。這種機制是為了保證如果Extract進程終止或者操作系統(tǒng)宕機,我們重啟Extract進程后,GoldenGate能夠恢復到以前的狀態(tài),從上一個斷點處繼續(xù)往下運行,而不會有任何數(shù)據(jù)損失。(6)源端OGG 傳輸進程(pump)配置pump進程運行在數(shù)據(jù)庫源端,其作用非常簡單。如果源端的Extract抽取進程使用了本地trail文件,那么pump進程就會把trail文件以數(shù)據(jù)塊的形式通過TCP/IP協(xié)議發(fā)送到目標端,Pump進程本質(zhì)上是Extract進程的一種特殊形式,如果不使用trail文件,那么Extract進程在抽取完數(shù)據(jù)后,直接投遞到目標端。補充:pump進程啟動時需要與目標端的mgr進程進行連接,所以需要優(yōu)先將目標端的mgr提前配置好,否則會報錯連接被拒絕,無法傳輸抽取的日志文件到目標端對應目錄下(7)源端OGG 異構mapping文件(defgen)生成該文件記錄了源庫需要復制的表的表結構定義信息,在源庫生成該文件后需要拷貝到目標庫的dirdef目錄,當目標庫的replica進程將傳輸過來的數(shù)據(jù)apply到目標庫時需要讀寫該文件,同構的數(shù)據(jù)庫不需要進行該操作。2.目標端OGG配置(1)目標端MySQL數(shù)據(jù)庫配置確認MySQL端表結構已經(jīng)存在MySQL數(shù)據(jù)庫OGG用戶創(chuàng)建mysql> create user 'ogg'@'%' identified by 'ogg';mysql> grant all on *.* to 'ogg'@'%';#### 提前創(chuàng)建好ogg存放checkpoint表的數(shù)據(jù)庫mysql> create database ogg;(2)目標端OGG 管理進程(MGR)配置目標端的MGR進程和源端配置一樣,可直接將源端配置方式在目標端重復執(zhí)行一次即可,該部分不在贅述(3)目標端OGG 檢查點日志表(checkpoint)配置checkpoint表用來保障一個事務執(zhí)行完成后,在MySQL數(shù)據(jù)庫從有一張表記錄當前的日志回放點,與MySQL復制記錄binlog的GTID或position點類似。#### 切換至ogg軟件目錄并執(zhí)行ggsci進入命令行終端shell> cd $OGG_HOMEshell> ggsciggsci> edit param ./GLOBALScheckpointtable ogg.ggs_checkpointggsci> dblogin sourcedb ogg@17X.1X.84.121:3306 userid oggggsci> add checkpointtable ogg.ggs_checkpoint(4)目標端OGG 回放線程(replicat)配置Replicat進程運行在目標端,是數(shù)據(jù)投遞的最后一站,負責讀取目標端Trail文件中的內(nèi)容,并將解析其解析為DML語句,然后應用到目標數(shù)據(jù)庫中。#### 切換至ogg軟件目錄并執(zhí)行ggsci進入命令行終端shell> cd $OGG_HOMEshell> ggsci#### 添加一個回放線程并與源端pump進程傳輸過來的trail文件關聯(lián),并使用checkpoint表確保數(shù)據(jù)不丟失ggsci> add replicat r_cms,exttrail /opt/ogg/dirdat/ms,checkpointtable ogg.ggs_checkpoint#### 增加/編輯回放進程配置文件ggsci> edit params r_cmsreplicat r_cmstargetdb cms@17X.1X.84.121:3306,userid ogg,password oggsourcedefs /opt/ogg/dirdef/cms.defdiscardfile /opt/ogg/dirrpt/r_cms.dsc,append,megabytes 1024HANDLECOLLISIONSMAP cms.*,target cms.*;注意:replicat進程只需配置完成,無需啟動,待全量抽取完成后再啟動。至此源端環(huán)境配置完成 待全量數(shù)據(jù)抽取完畢后啟動目標端回放進程即可完成數(shù)據(jù)準實時同步。3.全量同步配置全量數(shù)據(jù)同步為一次性操作,當OGG軟件部署完成及增量抽取進程配置并啟動后,可配置1個特殊的extract進程從表中抽取數(shù)據(jù),將抽取的數(shù)據(jù)保存到目標端生成文件,目標端同時啟動一個單次運行的replicat回放進程將數(shù)據(jù)解析并回放至目標數(shù)據(jù)庫中。(1)源端OGG 全量抽取進程(extract)配置#### 切換至ogg軟件目錄并執(zhí)行ggsci進入命令行終端shell> cd $OGG_HOMEshell> ggsci#### 增加/編輯全量抽取進程配置文件#### 其中RMTFILE指定抽取的數(shù)據(jù)直接傳送到遠端對應目錄下#### 注意:RMTFILE參數(shù)指定的文件只支持2位字符,如果超過replicat則無法識別ggsci> edit params ei_cmsSOURCEISTABLESETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")SETENV (ORACLE_SID=cms)SETENV (ORACLE_HOME=/data/oracle/11.2/db_1)USERID ogg@appdb,PASSWORD oggRMTHOST 17X.1X.84.121,MGRPORT 7809RMTFILE /opt/ogg/dirdat/ms,maxfiles 100,megabytes 1024,purgeTABLE cms.*;#### 啟動并查看抽取進程正常shell> nohup ./extract paramfile ./dirprm/ei_cms.prm reportfile ./dirrpt/ei_cms.rpt &## 查看日志是否正常進行全量抽取shell> tail -f ./dirrpt/ei_cms.rpt(2)目標端OGG 全量回放進程(replicat)配置#### 切換至ogg軟件目錄并執(zhí)行ggsci進入命令行終端shell> cd $OGG_HOMEshell> ggsciggsci> edit params ri_cmsSPECIALRUNEND RUNTIMETARGETDB cms@17X.1X.84.121:3306,USERID ogg,PASSWORD oggEXTFILE /opt/ogg/dirdat/msDISCARDFILE ./dirrpt/ri_cms.dsc,purgeMAP cms.*,TARGET cms.*;#### 啟動并查看回放進程正常shell> nohup ./replicat paramfile ./dirprm/ri_cms.prm reportfile ./dirrpt/ri_cms.rpt &#### 查看日志是否正常進行全量回放shell> tail -f ./dirrpt/ri_cms.rpt三、數(shù)據(jù)校驗數(shù)據(jù)校驗是數(shù)據(jù)遷移過程中必不可少的環(huán)節(jié),本章節(jié)提供給幾個數(shù)據(jù)校驗的思路共大家參數(shù),校驗方式可以由以下幾個角度去實現(xiàn):1.通過OGG日志查看全量、增量過程中discards記錄是否為0來判斷是否丟失數(shù)據(jù);2.通過對源端、目標端的表執(zhí)行count判斷數(shù)據(jù)量是否一致;3.編寫類似于pt-table-checksum校驗原理的程序,實現(xiàn)行級別一致性校驗,這種方式優(yōu)缺點特別明顯,優(yōu)點是能夠完全準確對數(shù)據(jù)內(nèi)容進行校驗,缺點是需要遍歷每一行數(shù)據(jù),校驗成本較高;4.相對折中的數(shù)據(jù)校驗方式是通過業(yè)務角度,提前編寫好數(shù)十個返回結果較快的SQL,從業(yè)務角度抽樣校驗。四、遷移問題處理本章節(jié)將講述遷移過程中碰到的一些問題及相應的解決方式。(一)MySQL限制在Oracle到MySQL的表結構遷移過程中主要碰到以下兩個限制:1. Oracle端的表結構因為最初設計不嚴謹,存在大量的列使用varchar(4000)數(shù)據(jù)類型,導致遷移到MySQL后超出行限制,表結構無法創(chuàng)建。由于MySQL本身數(shù)據(jù)結構的限制,一個16K的數(shù)據(jù)頁最少要存儲兩行數(shù)據(jù),因此單行數(shù)據(jù)不能超過65,535 bytes,因此針對這種情況有兩種解決方式:根據(jù)實際存儲數(shù)據(jù)的長度,對超長的varchar列進行收縮;對于無法收縮的列轉(zhuǎn)換數(shù)據(jù)類型為text,但這在使用過程中可能導致一些性能問題;2. 與第一點類似,在Innodb存儲引擎中,索引前綴長度限制是767 bytes,若使用DYNAMIC、COMPRESSED行格式且開啟innodblargeprefix的場景下,這個限制是3072 bytes,即使用utf8mb4字符集時,最多只能對varchar(768)的列創(chuàng)建索引;3. 使用ogg全量初始化同步時,若存在外鍵約束,批量導入時由于各表的插入順序不唯一,可能子表先插入數(shù)據(jù)而主表還未插入,導致報錯子表依賴的記錄不存在,因此建議數(shù)據(jù)遷移階段禁用主外鍵約束,待遷移結束后再打開。mysql>set global foreign_key_checks=off;(二)全量與增量銜接HANDLECOLLISIONS參數(shù)是實現(xiàn)OGG全量數(shù)據(jù)與增量數(shù)據(jù)銜接的關鍵,其實現(xiàn)原理是在全量抽取前先開啟增量抽取進程,抓去全量應用期間產(chǎn)生的redo log,當全量應用完成后,開啟增量回放進程,應用全量期間的增量數(shù)據(jù)。使用該參數(shù)后增量回放DML語句時主要有以下場景及處理邏輯:目標端不存在delete語句的記錄,忽略該問題并不記錄到discardfile目標端丟失update記錄- 更新的是主鍵值,update轉(zhuǎn)換成insert- 更新的鍵值是非主鍵,忽略該問題并不記錄到discardfile目標端重復insert已存在的主鍵值,這將被replicat進程轉(zhuǎn)換為UPDATE現(xiàn)有主鍵值的行(三)OGG版本選擇在OGG版本選擇上我們也根據(jù)用戶的場景多次更換了OGG版本,最初因為客戶的Oracle 數(shù)據(jù)庫版本為11.2.0.4,因此我們在選擇OGG版本時優(yōu)先選擇使用了11版本,但是使用過程中發(fā)現(xiàn),每次數(shù)據(jù)抽取生成的trail文件達到2G左右時,OGG報錯連接中斷,查看RMTFILE參數(shù)詳細說明了解到trail文件默認限制為2G,后來我們替換OGG版本為12.3,使用MAXFILES參數(shù)控制生成多個指定大小的trail文件,回放時Replicat進程也能自動輪轉(zhuǎn)讀取Trail文件,最終解決該問題。但是如果不幸Oracle環(huán)境使用了Linux 5版本的系統(tǒng),那么你的OGG需要再降一個小版本,最高只能使用OGG 12.2。(四)無主鍵表處理在遷移過程中還碰到一個比較難搞的問題就是當前Oracle端存在大量表沒有主鍵。在MySQL中的表沒有主鍵這幾乎是不被允許的,因為很容易導致性能問題和主從延遲。同時在OGG遷移過程中表沒有主鍵也會產(chǎn)生一些隱患,比如對于沒有主鍵的表,OGG默認是將這個一行數(shù)據(jù)中所有的列拼湊起來作為唯一鍵,但實際還是可能存在重復數(shù)據(jù)導致數(shù)據(jù)同步異常,Oracle官方對此也提供了一個解決方案,通過對無主鍵表添加GUID列來作為行唯一標示,具體操作方式可以搜索MOS文檔ID 1271578.1進行查看。(五)OGG安全規(guī)則報錯信息2019-03-08 06:15:22 ERROR OGG-01201 Error reported by MGR : Access denied.錯誤信息含義源端報錯表示為該抽取進程需要和目標端的mgr進程通訊,但是被拒絕,具體操作為:源端的extract進程需要與目標端mgr進行溝通,遠程將目標的replicat進行啟動,由于安全性現(xiàn)在而被拒絕連接。報錯原因在Oracle OGG 11版本后,增加了新特性安全性要求,如果需要遠程啟動目標端的replicat進程,需要在mgr節(jié)點增加訪問控制參數(shù)允許遠程調(diào)用解決辦法在源端和目標端的mgr節(jié)點上分別增加訪問控制規(guī)則并重啟## 表示該mgr節(jié)點允許(ALLOW)10.186網(wǎng)段(IPADDR)的所有類型程序(PROG *)進行連接訪問ACCESSRULE, PROG *, IPADDR 10.186.*.*, ALLOW(六)數(shù)據(jù)抽取方式報錯信息2019-03-15 14:49:04 ERROR OGG-01192 Trying to use RMTTASK on data types which may be written as LOB chunks (Table: 'UNIONPAYCMS.CMS_OT_CONTENT_RTF').報錯原因根據(jù)官方文檔說明,當前直接通過Oracle數(shù)據(jù)庫抽取數(shù)據(jù)寫到MySQL這種initial-load方式,不支持LOBs數(shù)據(jù)類型,而表 UNIONPAYCMS.CMSOTCONTENT_RTF 則包含了CLOB字段,無法進行傳輸,并且該方式不支持超過4k的字段數(shù)據(jù)類型解決方法將抽取進程中的RMTTASK改為RMTFILE參數(shù) 官方建議將數(shù)據(jù)先抽取成文件,再基于文件數(shù)據(jù)解析進行初始化導入