GoldenGate基本原理、安裝過(guò)程和基本維護(hù).doc

目錄一、GoldenGate介紹1二、GoldenGate安裝實(shí)施42.1創(chuàng)建GoldenGate軟件安裝目錄42.2 GoldenGate的管理用戶42.3安裝GoldenGate軟件52.4設(shè)置數(shù)據(jù)庫(kù)歸檔模式52.5打開(kāi)數(shù)據(jù)庫(kù)的附加日志62.6開(kāi)啟數(shù)據(jù)庫(kù)強(qiáng)制日志模式62.7創(chuàng)建GoldenGate管理用戶62.8編輯GLOBALS參數(shù)文件72.9管理進(jìn)程MGR參數(shù)配置72.10抽取進(jìn)程EXTN參數(shù)配置82.11 傳輸進(jìn)程DPEN參數(shù)配置92.12建立OGG的DDL對(duì)象102.13 數(shù)據(jù)初始化112.14 容災(zāi)端管理進(jìn)程MGR參數(shù)配置132.15編輯GLOBALS參數(shù)文件142.16 容災(zāi)端復(fù)制進(jìn)程REPN參數(shù)配置142.17創(chuàng)建復(fù)制進(jìn)程repn152.18啟動(dòng)生產(chǎn)端傳輸進(jìn)程和容災(zāi)端復(fù)制進(jìn)程152.19測(cè)試場(chǎng)景16三GoldenGate基本運(yùn)維命令16四、常見(jiàn)故障排除17一、GoldenGate介紹GoldenGate軟件是一種基于日志的結(jié)構(gòu)化數(shù)據(jù)復(fù)制軟件。GoldenGate 能夠?qū)崿F(xiàn)大量交易數(shù)據(jù)的實(shí)時(shí)捕捉、變換和投遞，實(shí)現(xiàn)源數(shù)據(jù)庫(kù)與目標(biāo)數(shù)據(jù)庫(kù)的數(shù)據(jù)同步，保持亞秒級(jí)的數(shù)據(jù)延遲。GoldenGate能夠支持多種拓?fù)浣Y(jié)構(gòu)，包括一對(duì)一，一對(duì)多，多對(duì)一，層疊和雙向復(fù)制等等。GoldenGate基本架構(gòu)Oracle GoldenGate主要由如下組件組成 Extract Data pump Trails Collector Replicat ManagerOracle GoldenGate 數(shù)據(jù)復(fù)制過(guò)程如下：利用抽取進(jìn)程(Extract Process)在源端數(shù)據(jù)庫(kù)中讀取Online Redo Log或者Archive Log，然后進(jìn)行解析，只提取其中數(shù)據(jù)的變化信息，比如DML操作增、刪、改操作，將抽取的信息轉(zhuǎn)換為GoldenGate自定義的中間格式存放在隊(duì)列文件(trail file)中。再利用傳輸進(jìn)程將隊(duì)列文件(trail file)通過(guò)TCP/IP傳送到目標(biāo)系統(tǒng)。目標(biāo)端有一個(gè)進(jìn)程叫Server Collector，這個(gè)進(jìn)程接受了從源端傳輸過(guò)來(lái)的數(shù)據(jù)變化信息，把信息緩存到GoldenGate 隊(duì)列文件(trail file)當(dāng)中，等待目標(biāo)端的復(fù)制進(jìn)程讀取數(shù)據(jù)。 GoldenGate 復(fù)制進(jìn)程(replicat process)從隊(duì)列文件(trail file)中讀取數(shù)據(jù)變化信息，并創(chuàng)建對(duì)應(yīng)的SQL語(yǔ)句，通過(guò)數(shù)據(jù)庫(kù)的本地接口執(zhí)行，提交到目標(biāo)端數(shù)據(jù)庫(kù)，提交成功后更新自己的檢查點(diǎn)，記錄已經(jīng)完成復(fù)制的位置，數(shù)據(jù)的復(fù)制過(guò)程最終完成。二、GoldenGate安裝實(shí)施2.1創(chuàng)建GoldenGate軟件安裝目錄在數(shù)據(jù)庫(kù)服務(wù)器上創(chuàng)建文件系統(tǒng)：/u01/gg，作為GoldenGate的安裝目錄。2.2 GoldenGate的管理用戶安裝GoldenGate軟件和維護(hù)GoldenGate軟件時(shí)，可以使用系統(tǒng)上的oracle用戶。GoldenGate安裝目錄的所有者必須是GoldenGate管理用戶，本次實(shí)施過(guò)程中使用oracle用戶作為GoldenGate管理用戶，添加oracle用戶的環(huán)境變量(在生產(chǎn)端和容災(zāi)端均要進(jìn)行以下操作)：export GG_HOME=/u01/ggexport LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/usr/bin:/libexport PATH=$GG_HOME:$PATH2.3安裝GoldenGate軟件切換到oracle用戶，將GG軟件的壓縮包存放到GoldenGate安裝目錄下，即/u01/gg，將這個(gè)壓縮包進(jìn)行解壓到GoldenGate安裝目錄下(在生產(chǎn)端和容災(zāi)端均要進(jìn)行以下操作)：tar -zxvf *.gz 進(jìn)入到GoldenGate安裝目錄，運(yùn)行GGSCI命令以進(jìn)入GG界面(在生產(chǎn)端和容災(zāi)端均要進(jìn)行以下操作)：cd /u01/gg./ggsci在GGSCI界面下創(chuàng)建子目錄(在生產(chǎn)端和容災(zāi)端均要進(jìn)行以下操作)：GGSCIcreate subdirs至此，GoldenGate軟件安裝完畢。2.4設(shè)置數(shù)據(jù)庫(kù)歸檔模式查看數(shù)據(jù)庫(kù)的歸檔模式：SQLarchive log list;如果是非歸檔模式，需要開(kāi)啟歸檔模式：shutdown immediate;startup mount;alter database archivelog;alter database open;2.5打開(kāi)數(shù)據(jù)庫(kù)的附加日志打開(kāi)附加日志并切換日志（保證Online redo log和Archive log一致）alter database add supplemental log data ;alter database add supplemental log data (primary key, unique,foreign key) columns;alter system switch logfile;2.6開(kāi)啟數(shù)據(jù)庫(kù)強(qiáng)制日志模式alter database force logging;2.7創(chuàng)建GoldenGate管理用戶在生產(chǎn)端和容災(zāi)端均要進(jìn)行以下操作：-create tablespaceSQLcreate tablespace ogg datafile $ORACLE_BASE/oradata/test/ogg01.dbf size 300M ;- create the user SQLcreate user ogg identified by ogg default tablespace ogg;- grant role privileges SQLgrant resource, connect, dba to ogg;2.8編輯GLOBALS參數(shù)文件切換到GoldenGate安裝目錄下，執(zhí)行命令：cd /u01/gg./ggsciGGSCIEDIT PARAMS ./GLOBALS -指定進(jìn)行DDL復(fù)制的數(shù)據(jù)庫(kù)用戶，DML可不配置在文件中添加以下內(nèi)容：GGSCHEMA ogg -指定的進(jìn)行DDL復(fù)制的數(shù)據(jù)庫(kù)用戶CHECKPOINTTABLE ogg.checkpoint利用默認(rèn)的密鑰，生成密文：GGSCIencrypt password ogg encryptkey defaultEncrypted password: AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB 記錄這個(gè)密文，將在以下進(jìn)程參數(shù)的配置中使用。2.9管理進(jìn)程MGR參數(shù)配置PORT 7839DYNAMICPORTLIST 7840-7860-AUTOSTART *AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45以下兩句用于DDL,單獨(dú)配置DML時(shí)，可不添加PURGEDDLHISTORY MINKEEPDAYS 11,MAXKEEPDAYS 14PURGEMARKERHISTORY MINKEEPDAYS 11, MAXKEEPDAYS 142.10 在目標(biāo)端配置GoldenGate軟件復(fù)制進(jìn)程Dblogin userid ogg,password oggAdd checkpointtable ogg.checktable2.11抽取進(jìn)程EXTN參數(shù)配置Add extract extn,tranlog,begin nowAdd exttrail ./dirdat/na,extract extn,megabytes 100EXTRACT extnsetenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY defaultREPORTCOUNT EVERY 1 MINUTES, RATEDISCARDFILE ./dirrpt/discard_extn.dsc,APPEND,MEGABYTES 1024DBOPTIONS ALLOWUNUSEDCOLUMNWARNLONGTRANS 2h,CHECKINTERVAL 3mEXTTRAIL ./dirdat/naTRANLOGOPTIONS EXCLUDEUSER OGG TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbfFETCHOPTIONS NOUSESNAPSHOTTRANLOGOPTIONS CONVERTUCS2CLOBSTRANLOGOPTIONS altarchivelogdest primary instance test /oradata/arch-TRANLOGOPTIONS RAWDEVICEOFFSET 0DYNAMICRESOLUTION以下兩句是配置DDL需要：DDL INCLUDE ALLDDLOPTIONS addtrandata, NOCROSSRENAME, REPORT table QQQ.*;table CUI.*;2.11 傳輸進(jìn)程DPEN參數(shù)配置Add extract dpen,exttrailsource ./dirdat/naAdd rmttrail ./dirdat/na,extract dpenEXTRACT dpenRMTHOST 192.168.56.7 , MGRPORT 7839, compressPASSTHRUnumfiles 50000RMTTRAIL ./dirdat/naTABLE QQQ.*;TABLE CUI.*;2.12建立OGG的DDL對(duì)象$ cd /u01/gg $ sqlplus / as sysdbaSQL marker_setup.sqlEnter GoldenGate schema name:oggalter system set recyclebin=off;SQL ddl_setup.sqlEnter GoldenGate schema name: oggSQL role_setup.sqlGrant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:SQLGRANT GGS_GGSUSER_ROLE TO where is the user assigned to the GoldenGate processes.注意這里的提示：需要手工將這個(gè)GGS_GGSUSER_ROLE指定給extract所使用的數(shù)據(jù)庫(kù)用戶（即參數(shù)文件里面通過(guò)userid指定的用戶），可以到sqlplus下執(zhí)行類(lèi)似的sql:SQLGRANT GGS_GGSUSER_ROLE TO ogg;注：這里的ogg是extract使用的用戶。如果你有多個(gè)extract，使用不同的數(shù)據(jù)庫(kù)用戶，則需要重述以上過(guò)程全部賦予GGS_GGSUSER_ROLE權(quán)限。運(yùn)行以下腳本，使觸發(fā)器生效：SQL ddl_enable.sql注：在生產(chǎn)端開(kāi)啟抽取前，先禁用DDL捕獲觸發(fā)器，調(diào)用ddl_disable.sql。2.13 數(shù)據(jù)初始化在初始化過(guò)程中，源數(shù)據(jù)庫(kù)不需要停機(jī)，初始化過(guò)程分為三個(gè)部分： 生產(chǎn)端開(kāi)啟抽取進(jìn)程； 生產(chǎn)端導(dǎo)出數(shù)據(jù)； 容災(zāi)端導(dǎo)入數(shù)據(jù)；在生產(chǎn)端添加抽取進(jìn)程、傳輸進(jìn)程以及相應(yīng)的隊(duì)列文件,執(zhí)行命令如下：/創(chuàng)建進(jìn)程 EXTN GGSCIadd extract extn,tranlog,begin nowGGSCIadd exttrail ./dirdat/na,extract extn,megabytes 500/創(chuàng)建進(jìn)程 DPEN GGSCIadd extract dpen,exttrailsource ./dirdat/naGGSCIadd rmttrail ./dirdat/na,extract dpen,megabytes 500在生產(chǎn)端啟動(dòng)管理進(jìn)程：GGSCI start mgr啟用DDL 捕獲trigger：$ cd /u01/gg$ sqlplus “/as sysdba”SQL ddl_enable.sql在生產(chǎn)端啟動(dòng)抽取進(jìn)程：GGSCI start EXTN在數(shù)據(jù)庫(kù)中，獲取當(dāng)前的SCN號(hào)，并且記錄這個(gè)SCN號(hào)：SQLselect to_char(dbms_flashback.get_system_change_number) from dual;603809在數(shù)據(jù)庫(kù)中，創(chuàng)建數(shù)據(jù)泵所需目錄并賦予權(quán)限：SQLCREATE OR REPLACE DIRECTORY DATA_PUMP AS /u01;SQLgrant read ,write on DIRECTORY DATA_PUMP to ogg;在生產(chǎn)端利用數(shù)據(jù)泵導(dǎo)出數(shù)據(jù)：expdp ogg/ogg schemas=QQQ directory=DATA_PUMP dumpfile=QQQ_bak_%U flashback_scn=123456789 logfile=expdp_QQQ.log filesize=4096mexpdp ogg/ogg schemas=CUI directory=DATA_PUMP dumpfile=CUI_bak_%U flashback_scn=123456789 logfile=expdp_ CUI.log filesize=4096mexpdp ogg/ogg schemas=test1 directory=DATA_PUMP dumpfile=test1_bak_%U flashback_scn=603809 logfile=expdp_QQQ.log filesize=4096m把導(dǎo)出的文件傳輸?shù)饺轂?zāi)端，利用數(shù)據(jù)泵將數(shù)據(jù)導(dǎo)入：Impdp ogg/ogg DIRECTORY=DATA_PUMP DUMPFILE=QQQ_bak_%U logfile=impdp_ QQQ.logImpdp ogg/ogg DIRECTORY=DATA_PUMP DUMPFILE=CUI_bak_%U logfile=impdp_CUI.log2.14 容災(zāi)端管理進(jìn)程MGR參數(shù)配置PORT 7839DYNAMICPORTLIST 7840-7860-AUTOSTART ER *-AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 2userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default2.15編輯GLOBALS參數(shù)文件切換到GoldenGate安裝目錄下，執(zhí)行命令：cd /u01/gg./ggsciggsciEDIT PARAMS ./GLOBALS在文件中添加以下內(nèi)容：GGSCHEMA ogg -指定的進(jìn)行DDL復(fù)制的數(shù)據(jù)庫(kù)用戶2.16 容災(zāi)端復(fù)制進(jìn)程REPN參數(shù)配置REPLICAT repnsetenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY defaultSQLEXEC ALTER SESSION SET CONSTRAINTS=DEFERREDREPORT AT 01:59REPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDassumetargetdefsDISCARDFILE ./dirrpt/repna.dsc, APPEND, MEGABYTES 1024DISCARDROLLOVER AT 02:30ALLOWNOOPUPDATESREPERROR (1403, discard)DDL INCLUDE MAPPED DDLOPTIONS REPORTMAPEXCLUDE QQQ.T0417MAP QQQ.*, TARGET QQQ.*;MAP CUI.*, TARGET CUI.*;2.17創(chuàng)建復(fù)制進(jìn)程repn 執(zhí)行以下命令創(chuàng)建復(fù)制進(jìn)程repn：GGSCIadd replicat repn, exttrail ./dirdat/na, nodbcheckpoint2.18啟動(dòng)生產(chǎn)端傳輸進(jìn)程和容災(zāi)端復(fù)制進(jìn)程GGSCIstart dpenGGSCIstart REPLICAT repn aftercsn 1234567892.19測(cè)試場(chǎng)景（1）在生產(chǎn)端數(shù)據(jù)庫(kù)上，創(chuàng)建一張表。（2）在生產(chǎn)端數(shù)據(jù)庫(kù)上，修改這個(gè)張表的數(shù)據(jù)。（3）在生產(chǎn)端數(shù)據(jù)庫(kù)上，刪除這張表。三GoldenGate基本運(yùn)維命令（1）查看進(jìn)程狀態(tài)GGSCIinfo all 查看GG整體運(yùn)行情況，比如進(jìn)程Lag延時(shí)，檢查點(diǎn)延時(shí)。GGSCIinfo 查看某個(gè)進(jìn)程的運(yùn)行狀況，比如抽取進(jìn)程正在讀取哪個(gè)歸檔日志或者聯(lián)機(jī)重做日志，傳輸進(jìn)程正在傳送哪一個(gè)隊(duì)列文件，復(fù)制進(jìn)程正在使用哪一個(gè)隊(duì)列文件。GGSCIinfo showch 查看某個(gè)進(jìn)程運(yùn)行的詳細(xì)信息。（2）查看進(jìn)程報(bào)告GGSCIview report 報(bào)錯(cuò)時(shí)，從進(jìn)程報(bào)告里獲取錯(cuò)誤信息。（3）在操作系統(tǒng)上，查看GoldenGate安裝目錄的使用率$ df -h查看ogg目錄是否撐滿。四Logdump工具使用五Goldengate初級(jí)的性能優(yōu)化BatchsqlInsert abend限制內(nèi)存使用顆粒度拆分六、goldengate版本升級(jí)七、goldengate雙向復(fù)制八、生產(chǎn)庫(kù)與容災(zāi)庫(kù)之間的回切八、異構(gòu)數(shù)據(jù)庫(kù)之間的數(shù)據(jù)轉(zhuǎn)換，數(shù)據(jù)過(guò)濾篩選 四、常見(jiàn)故障排除故障（1）錯(cuò)誤信息：OGG-00446 Could not find archived log for sequence 53586 thread 1 under alternative destinations. SQL . Last alternative log tried /arch_cx/1_53586_776148274.arc., error retrieving redo file name for sequence 53586, archived = 1, use_alternate = 0Not able to establish initial position for sequence 53586, rba 44286992.處理辦法：將缺失的歸檔日志從備份中恢復(fù)出來(lái)。如果依舊找不到所需歸檔日志，那么只能重新實(shí)施數(shù)據(jù)初始化。故障（2）錯(cuò)誤信息：OGG-01154 Oracle GoldenGate Delivery for Oracle, repn.prm: SQL error 1691