完整版oracleGoldenGate學習筆記

1、Oracle GoldenGate 測試文檔1 Oracle GoldenGate 介紹GoldenGate TDM （交易數(shù)據(jù)管理）軟件是一種基于日志的結(jié)構(gòu)化數(shù)據(jù)復(fù)制軟件，它通過解析源 數(shù)據(jù)庫在線日志或歸檔日志獲得數(shù)據(jù)的增刪改變化，再將這些變化應(yīng)用到目標數(shù)據(jù)庫，實現(xiàn)源數(shù)據(jù)庫與目標數(shù)據(jù)庫同步、雙活。GoldenGate TDM軟件可以在異構(gòu)的IT基礎(chǔ)結(jié)構(gòu)（包括幾乎所有常用操作系統(tǒng)平臺和數(shù)據(jù)庫平臺）之間實現(xiàn)大量數(shù)據(jù)亞秒一級的實時復(fù)制，其復(fù)制過程簡圖如下：H杯系 生產(chǎn)系統(tǒng)如上圖所示，GoldenGate TDM的數(shù)據(jù)復(fù)制過程如下：利用捕捉進程（Capture Process）在源系統(tǒng)端讀取Onli

2、ne Redo Log或Archive Log,然后進行解 析，只提取其中數(shù)據(jù)的變化如增、刪、改操作，并將相關(guān)信息轉(zhuǎn)換為GoldenGate TDM自定義的中間格式存放在隊列文件中。再利用傳送進程將隊列文件通過TCP/IP傳送到目標系統(tǒng)。捕捉進程在每次讀完log中的數(shù)據(jù)變化并在數(shù)據(jù)傳送到目標系統(tǒng)后，會寫檢查點，記錄當前完成捕捉的 log位置，檢查點的存在可以使捕捉進程在中止并恢復(fù)后可從檢查點位置繼續(xù)復(fù)制；目標系統(tǒng)接受數(shù)據(jù)變化并緩存到 GoldenGate TDM隊列當中，隊列為一系列臨時存儲數(shù)據(jù)變 化的文件，等待投遞進程讀取數(shù)據(jù)；GoldenGate TDM投遞進程從隊列中讀取數(shù)據(jù)變化并創(chuàng)建對

3、應(yīng)的SQL語句，通過數(shù)據(jù)庫的本地接口執(zhí)行，提交到數(shù)據(jù)庫成功后更新自己的檢查點，記錄已經(jīng)完成復(fù)制的位置，數(shù)據(jù)的復(fù)制過程最終完成。由此可見，GoldenGate TDM是一種基于軟件的數(shù)據(jù)復(fù)制方式，它從數(shù)據(jù)庫的日志解析數(shù)據(jù)的變 化（數(shù)據(jù)量只有日志的四分之一左右）。GoldenGate TDM將數(shù)據(jù)變化轉(zhuǎn)化為自己的格式，直接通過TCP/IP網(wǎng)絡(luò)傳輸，無需依賴于數(shù)據(jù)庫自身的傳遞方式，而且可以通過高達9:1的壓縮率對數(shù)據(jù)進行壓縮，可以大大降低帶寬需求。在目標端，GoldenGate TDM可以通過交易重組，分批加載等技術(shù)手段大大加快數(shù)據(jù)投遞的速度和效率，降低目標系統(tǒng)的資源占 用，可以在亞秒級實現(xiàn)大量數(shù)據(jù)

4、的復(fù)制，并且目標端數(shù)據(jù)庫是活動的Golde nGate TDM提供了靈活的應(yīng)用方案，基于其先進、靈活的技術(shù)架構(gòu)可以根據(jù)用戶需求組成各種拓撲結(jié)構(gòu)，如圖所示:一對一對多NOTWm MAW1SE日GoldenGate TDM可以提供可靠的數(shù)據(jù)復(fù)制，主要體現(xiàn)在下面三點：保證事務(wù)一致性GoldenGate TDM在災(zāi)備數(shù)據(jù)庫應(yīng)用復(fù)制數(shù)據(jù)庫交易的順序與在生產(chǎn)中心數(shù)據(jù)庫上的 順序相同， 并且按照相同的事務(wù)環(huán)境提交，確保在目標系統(tǒng)上數(shù)據(jù)的完整性和讀一致性，為實時查詢和事務(wù)處理創(chuàng)造了條件。檢查點機制保障數(shù)據(jù)無丟失GoldenGate TDM的抽取和復(fù)制進程使用檢查點機制記錄完成復(fù)制的位置。對于抽取進程，其檢 查

5、點記錄當前已經(jīng)抽取日志的位置和寫隊列文件的位置；對于投遞進程，其檢查點記錄當前讀取隊列文件的位置。檢查點機制可以保證在系統(tǒng)、網(wǎng)絡(luò)或GoldenGate TDM進程故障重啟后數(shù)據(jù)無丟失?？煽康臄?shù)據(jù)傳輸機制Golde nGate TDM用應(yīng)答機制傳輸交易數(shù)據(jù)，只有在得到確認消息后才認為數(shù)據(jù)傳輸完成，否 則將自動重新傳輸數(shù)據(jù)，從而保證了抽取出的所有數(shù)據(jù)都能發(fā)送到備份端。數(shù)據(jù)傳輸過程中支持128位加密和數(shù)據(jù)壓縮功能。Oracle公司的GoldenGate產(chǎn)品，可以在異構(gòu)的IT基礎(chǔ)結(jié)構(gòu)之間實現(xiàn)大量數(shù)據(jù)的秒一級的數(shù)據(jù)捕捉、 轉(zhuǎn)換和投遞。Golde nGate可以支持幾乎所有常用操作系統(tǒng)如和數(shù)據(jù)庫平臺，如下

6、表所示：操作系統(tǒng)數(shù)據(jù)庫MS NT, 2000, XP, Linux. Sun Solaris,HP-UX, IBM AIX, HP Non Stop, TRU64, IBMz/OS, OS/390Oracle, DB2, MS SQL Server, MySQL,En scribe, SQL/MP, SQL/MX, Sybase, Teradata,其 他ODBC兼容數(shù)據(jù)庫112. Oracle GoldenGate For Oracle(windows 平臺)安裝比如安裝文件解壓縮后GoldenGate的安裝非常簡單，將解壓縮安裝文件到指定目錄。的路徑為d:ggs安裝步驟如下：1. 進入命令

7、行2. 切換到ggs目錄下3. 輸入命令 in stall addservice addeve nts4. 輸入ggsci進入GoldenGate的命令行5. 輸入 create subdirsOK, GoldenGate安裝就已經(jīng)完成了。實際操作如下C:Documents and SettingsPONYd:D:cd ggsD:ggs in stall addservice addeve ntsOracle Golde nGate messages in stalled successfully.Service GGSMGR created.In stall program termi na

8、ted n ormally.D:ggsggsciOracle Golde nGate Comma nd In terpreter for OracleVersion 1040.19 Build 002Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55Copyright (C) 1995, 2009, Oracle an d/or its affiliates. All rights reservedGGSCI (PONY) 1 create subdirsCreati ng subdirectories un der curre nt

9、directory D:ggsParameter filesD:ggsdirprm: createdReport filesD:ggsdirrpt: createdCheckpo int filesD:ggsdirchk: createdProcess status filesD:ggsdirpcs: createdSQL script filesD:ggsdirsql: createdDatabase defi niti ons filesD:ggsdirdef: createdExtract data filesD:ggsdirdat: createdTemporary filesD:gg

10、sdirtmp: createdVeridata filesD:ggsdirver: createdVeridata Lock filesD:ggsdirverlock: createdVeridata Out-Of-S ync filesD:ggsdirveroos: createdVeridata Out-Of-Sy nc XML files D:ggsdirveroosxml: createdVeridata Parameter filesD:ggsdirverparams: createdVeridata Report filesD:ggsdirverrep ort: createdV

11、eridata Status filesD :ggsdirverstatus: createdVeridata Trace filesD :ggsdirvertrace: createdStdout filesD:ggsdirout: created3數(shù)據(jù)庫復(fù)制實施文檔(DML)3.1準備工作準備兩臺機器，分別為SourceDB, TargetDB,通過TCP/IP網(wǎng)絡(luò)進行互連SourceDB : WIN XP 環(huán)境,Oracle 10g 10.2.0.1.0TargetDB : WIN 2003 環(huán)境 Oracle 10g 10.2.0.1.0必須保證SourceDB是運行在歸檔模式下。目的

12、：將SourceDB中相關(guān)Schema中的數(shù)據(jù)同步復(fù)制到TargetDB相對應(yīng)的Sechema中 本文中要同步的是SouceDB中的SAJET,SJ,SMT丄ANG到Target中的SAJET,SJ,SMT丄ANG 用戶下首先要保持SourceD, TargetDB相關(guān)Schema中的初始數(shù)據(jù)一致，可以通過Exp/lmp,Rman,冷 備等方式實現(xiàn).雙方DB建立GoldenGate用戶，賦予DBA權(quán)限，用于GoldenGate連接DB.雙方DB安裝GoldenGate,安裝方式如第二章節(jié)所示 SourceDB必須開始最小附加日志模式通過以下語句查看DB是否開啟了最小附加日志模式SQL sele

13、ct SUPPLEMENTAL_LOG_DA TA_MIN from v$database;如果沒有開啟數(shù)據(jù)庫的最小附加日志，則通過以下語句開啟SQLalter database add suppleme ntal log data;開啟表的最小附加日志，通過 GoldenGate命令行來添加GGSCI (PONY) 1 dblogi n userid sajet password techSuccessfully logged into database.GGSCI (PONY) 2 add tran data sajet.*第一條命令表示登錄到 Database第二條命令表示添加Sajet

14、用戶下所有表的最小附加日志同樣的命令開啟SJ丄ANG,SMT用戶下 的表的最小附加日志。3.2配置GoldenGate3.2.1 配置 SourceDB 的 GoldenGateSourceDB端需要配置mgr進程，添加一個extract進程，和一個遠端隊列。1配置mgr參數(shù)GGSCI (PO NY) 2 edit param mgr此時系統(tǒng)自動會新建一個名為mgr的參數(shù)文件，填入以下內(nèi)容后保存PORT 7809-mgr進程使用的TCP/IP端口偵聽請求2新增一個抽取進程，在 GGSCI命令行中輸入如下命令：GGSCI (PONY) 1 add extract ext1 ,tra nlog,b

15、egin nowEXTRACT added.新增一個抽取進程，負責抓取SourceDB中變化的數(shù)據(jù)，基于日志方式，立即生效3.配置抽取進程GGSCI (PONY) 2 edit param ext1此時系統(tǒng)會自動新建一個名為 ext1的參數(shù)文件，填入以下內(nèi)容后保存extract ext1userid golde ngate,password golde ngatermthost 172.17.17.61, mgrport 7809rmttrail d:ggsdirdatr1dyn amicresoluti ongettr un catestable sajet.*;table sj.*;tab

16、le smt.*;table Ian g.*;抽取進程名為ext2連接本機DB的賬號和密碼遠程主機地址以及服務(wù)端口號(TargetDB)遠程隊列的位置仃argetDB)優(yōu)化參數(shù)，動態(tài)分析表結(jié)構(gòu)是否抓取Truncate的數(shù)據(jù)需要抽取哪些tableo4.新增遠端隊列位置GGSCI (PONY) 3 add rmttrail d:ggsdirdatr1 extract ext2 RMTTRAIL added.新增一個遠程隊列，位置為在TargetDB的d:ggsdirdatr1,是抽取進程extl抽取的數(shù)據(jù)隊列，注意和抽取進程中配置的rmttrail d:ggsdirdatr1 致。開啟所有進程GG

17、SCI (PONY) 11 start mgrStarti ng Ma nager as service (GGSMGR).Service started.GGSCI (PONY) 14 start ext1Se ndi ng START request to MANAGER (GGSMGR) .EXTRACT EXT1 starti ng查看進程的運行情況GGSCI (PONY) 185 info allProgram StatusGroupLagTime Since ChkptMANAGERRUNNINGEXTRACTRUNNINGEXT100:00:0000:00:06OK, Mgr進程

18、和EXT進程都已經(jīng)正常運行 SouceDB端的配置完成。3.2.2 酉己置 TargetDB 的 GoldenGate1. 配置mgr參數(shù)GGSCI (PONY) 2 edit param mgr此時系統(tǒng)自動會新建一個mgr的參數(shù)文件，填入以下內(nèi)容后保存PORT 7809DYNAMICPORTLIST 7840-7850-mgr進程使用的TCP/IP端口偵聽請求接受遠端隊列的端口列表。2. 新增一個復(fù)制進程GGSCI (PONY) 2ADD replicat rep1 EXTTRAIL d:ggsdirdatr1, no dbcheckpoi nt新增一個復(fù)制進程，將抽取到隊列中的文件解析后寫

19、進TargetDB3. 配置復(fù)制進程GGSCI (PONY) 2 edit param repl此時系統(tǒng)會自動新建一個參數(shù)文件，填入一下內(nèi)容后保存replicat repluserid golde ngate,password golde ng ate assumetargetdefs reperror default,discarddiscardfile D:oradatadiscardrepsz.dsc,appe nd,megabytes 100 gettr un catemap sajet.*, target sajet.*;map sj.*, target sj.*;map Ian g

20、.*, target Ian g.*;map smt.*, target smt.*;復(fù)制進程名登入TargetDB的賬號和密碼兩臺DB數(shù)據(jù)結(jié)構(gòu)一致則使用此參數(shù)如果復(fù)制出錯，則繼續(xù)，并將錯誤放進 discardfile中復(fù)制truncate操作復(fù)制的源表為sajet, sj, la ng, smt用戶下的所有表，目標為TargetDB中對用用戶下的所 有表開啟mgr和rep進程GGSCI (PONY) 20 start mgrStarti ng Ma nager as service (GGSMGR).Service started.GGSCI (PONY) 21 start rep1Sen

21、di ng START request to MANAGER (GGSMGR) .REPLICAT REP1 starti ng查看進程的運行情況GGSCI (PONY) 22 info allProgram StatusGroupLagTime Since ChkptMANAGERRUNNINGREPLICAT RUNNINGREP100:00:0000:00:07OK,MGR進程和REP進程運行正常至此，兩臺DB的GoldenGate都已經(jīng)配置完成。3.3測試DML操作首先往SourceDB中插入一行C:Docume nts and Setti ngsAdm ini stratorsqlp

22、lusSQL*Plus: Release 10.2.0.1.0 Production on Mon Mar 8 15:33:47 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter user-name: sajet/techhitronConn ected to:Oracle Database 10g En terprise Edition Release 10.2.0.1.0 64bit ProductionWith the Partitio ning, OLAP and Data Mi ning opti onsS

23、QL in sert into sajet.sys_emp values (10000786；TEST1 VTEST1WTEST/1000021 row created.SQL commit2；Commit complete.看看TargetDB中是否有同樣的記錄存在呢SQL connect sajet/techgolde ngaConn ected.SQL select coun t (*) from sajet.sys_emp2 where emp_id=10000786;COUNT(*)同步過來了。我們可以看看EXT1進程的相關(guān)統(tǒng)計信息1GGSCI (sajet-project) 189

24、 stats extlSe nding STATS request to EXTRACT EXT1 .Start of Statistics at 2010-03-08 15:47:51.DDL replicati on statistics (for all trails):* Total statistics since extract startedOperati ons40.00Mapped operatio ns28.00Unm apped operati ons0.00Other operati ons12.00Excluded operati ons0.00Output to d

25、:ggsdirdatr1:Extracting from GOLDENGATE.GGS MARKER to GOLDENGA TE.GGS MARKER:* Total statistics since 2010-03-05 09:51:51 *No database operati ons have bee n performed* Daily statistics since 2010-03-08 00:00:00 *No database operati ons have bee n performed* Hourly statistics since 2010-03-08 15:00:

26、00 *No database operati ons have bee n performed* Latest statistics since 2010-03-05 09:51:51 *No database operati ons have bee n performedExtracting from SAJET.GOLDEGA TETEST to SAJET.GOLDEGATETEST: * Total statistics since 2010-03-05 09:51:51 *Total in serts3.00Total updatesTotal deletesTotal trun

27、 cates2.00Total discards5.00Total operati ons* Daily statistics since 2010-03-08 00:00:00 t JNo database operati ons have bee n performed.* Hourly statistics si nee 2010-03-08 15:00:00 *No database operati ons have bee n performed.* Latest statistics si nee 2010-03-05 09:51:51 *Total in serts3.00Tot

28、al updates0.00Total deletes0.00Total trun cates2.00Total discards0.00Total operati ons5.00Extracti ng from SAJET.SYS_EMP to SAJET.SYS_EMP:|* Total statistics since 2010-03-05 09:51:51 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.00* Daily statistics since

29、 2010-03-08 00:00:00 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.00* Hourly statistics si nee 2010-03-08 15:00:00 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total deletes0.002.002.000.00Total operati onst Latest statistics sin

30、ce 2010-03-05 09:51:51 *Total in sertsTotal updates0.002.00Total discardsTotal operati onsExtracti ng from SAJET.MLOG$_SYS_EMP to SAJET.MLOG$_SYS_EMP:* Total statistics since 2010-03-05 09:51:51 Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.00* Daily statis

31、tics since 2010-03-08 00:00:00 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.00* Hourly statistics si nee 2010-03-08 15:00:00 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.004 GoldenGate Support DDL 安裝1. 選擇一個Sche

32、ma存放支持DDL的對象，本文選擇GoldenGate2. 編輯Globals參數(shù)文件3. 關(guān)掉 Oracle Recycle bin Latest statistics since 2010-03-05 09:51:51 *Total in serts2.00Total updates0.00Total deletes0.00Total discards0.00Total operati ons2.00End of Statistics.相信以上的內(nèi)容大家都看的懂吧。其他的DML操作在此省略。On Oracle 10ga nd up, system recycle bin must be d

33、isabled.To turn off the recycle bin: Oraclel OgRelease 2 and later: Set the RECYCLEBIN initialization parameter toOFF. Oraclel OgRelease 1: Set the RECYCLEBIN initialization parameter to FALSE.4. 在命令行，切換到GGS目錄5. 運行 SQLPLUS6. run marker_setup.sql, en ter Golde nGate schema(golde ngate)7. run ddl_setu

34、p.sqlen ter schema:golde ngatechoose in stallation mode: INITIALSETUP8. run role setup.sql9. grant role(GGS_GGSUSER_ROLE) to all Golde nGate Extract users;10. run ddl_e nable.sql兩端都安裝完成.具體如下：GGSCI (PONY) 23 edit param globals填入如下內(nèi)容后保存GGSCHEMA golde ngateD:ggssqlplus/ as sysdbaSQL*Plus: Release 10.2.

35、0.1.0 - Production on 星期三 3 月 3 10:12:06 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.連接到：Oracle Database 10g En terprise Edition Release 10.2.0.1.0 ProductionWith the Partitio ning, OLAP and Data Mi ning optio nsSQL marker_setup.sqlMarker setup scriptYou will be prompted for the n ame

36、of a schema for the Golde nGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.En ter Golde nGate schema n ame:golde ngateMarker setup table script complete, running verification script.Please en ter the

37、 n ame of a schema for the Golde nGate database objects:Setting schema name to GOLDENGA TEMARKER TABLEOKMARKER SEQUENCEOKScript complete.SQL ddl_setup.sqlGolde nGate DDL Replicati on setup scriptVerify ing that curre nt user has privileges to in stall DDL Replicati onYou will be prompted for the n a

38、me of a schema for the Golde nGate database object s.NOTE: The schema must be created prior to running this script.NOTE: On Oracle 10g and up, system recycle bin must be disabled.NOTE: Stop all DDL replication before starting this installation.En ter Golde nGate schema n ame:golde ngateYou will be p

39、rompted for the mode of in stallati on.To install or reinstall DDL replication, enter INITIALSETUPTo upgrade DDL replicatio n, en ter NORMALEnter mode of installation: initialsetupWork ing, please wait.Spooli ng to file ddl_setup_spool.txtUsi ng GOLDENGATE as a Golde nGate schema name, INITIALSETUP

40、as a mode of in stalla tion.Work ing, please wait.RECYCLEBIN must be empty.This in stallation will purge RECYCLEBIN for all users. To proceed, en ter yes. To stop in stallation, en ter no.En ter yes or no:yesDDL replication setup script complete, running verification scriptPlease en ter the n ame of

41、 a schema for the Golde nGate database objects:Setting schema name to GOLDENGA TEDDLORA_GETTABLESPACESIZE STATUS:Lin e/posCLEAR_TRACE STATUS:Lin e/posErrorNo errorsNo errors27CREATE_TRACE STA TUS:Lin e/posTRACE PUT LINE STATUS:Line/posINITIAL SETUP STATUS:Lin e/posDDLVERSIONSPECIFIC PACKAGE STATUS:L

42、ine/posDDLREPLICATION PACKAGE STATUS:DDLREPLICATION PACKAGE BODY STA TUS:Lin e/posDDL HISTORY TABLEOKDDL DUMP TABLESOKDDL DUMP COLUMNSOKDDL DUMP LOG GROUPSOKDDL DUMP PARTITIONSOKDDL DUMP PRIMARY KEYSOKDDL SEQUENCEOKGGS_TEMP_COLSOKGGS_TEMP_UKOKDDL TRIGGER CODE STA TUS:Line/posDDL TRIGGER INSTALL STA

43、TUSOKDDL TRIGGER RUNNING STATUSENABLEDSTAYMETADATA IN TRIGGEROFFDDL TRIGGER SQL TRACINGDDL TRIGGER TRACE LEVELLOCATION OF DDL TRACE FILEC:ORACLEPRODUCT10.2.0ADMINTESTUDUMP/ggs ddl traceogAn alyz ing in stallati on status.STATUS OF DDL REPLICATIONSUCCESSFUL in stallation of DDL Replication software c

44、ompo nentsScript complete.SQL role_setup.sqlGGS Role setup scriptThis script w川 drop and recreate the role GGS_GGSUSER_ROLETo use a different role name, quit this script and then edit the params.sql scri pt to cha nge the gg_role parameter to the preferred n ame. (Do not run the script.)You will be prompted for t