初嘗oracle GoldenGate 備份軟件.doc

初嘗oracle GoldenGate 備份軟件前言GoldenGate軟件是一種基于日志的結(jié)構(gòu)化數(shù)據(jù)復制軟件，它通過解析源數(shù)據(jù)庫在線日志或歸檔日志獲得數(shù)據(jù)的增量變化，再將這些變化應用到目標數(shù)據(jù)庫，從而實現(xiàn)源數(shù)據(jù)庫與目標數(shù)據(jù)庫同步。GoldenGate 可以在異構(gòu)的IT基礎結(jié)構(gòu)（包括幾乎所有常用操作系統(tǒng)平臺和數(shù)據(jù)庫平臺）之間實現(xiàn)大量數(shù)據(jù)亞秒一級的實時復制,從而在可以在應急系統(tǒng)、在線報表、實時數(shù)據(jù)倉庫供應、交易跟蹤、數(shù)據(jù)同步、集中/分發(fā)、容災等多個場景下應用。同時，GoldenGate可以實現(xiàn)一對一、廣播(一對多)、聚合(多對一)、雙向、點對點、級聯(lián)等多種靈活的拓撲結(jié)構(gòu)。環(huán)境要求：1. 支持的平臺支持的Oracle Database 版本在當前的Oracle GoldenGate 10.4版本中,支持如下Oracle Database版本Oracle 8i (DML support only)Oracle 9.1 and 9.2 (DML and DDL support)Oracle 10.1 and 10.2 (DML and DDL support)Oracle 11g (DML and DDL support)支持的操作系統(tǒng)平臺幾乎所有主流的操作系統(tǒng),GoldenGate都支持Windows 2000, 2003, XP,Linux,Sun Solaris,HP NonStop,HP-UX2. 操作系統(tǒng)需求內(nèi)存要求GoldenGate對操作系統(tǒng)內(nèi)存的需求主要取決于Extract和Replicat進程數(shù)，一般建議2G。DISK空間需求 由于要數(shù)據(jù)庫必須設置為歸檔模式，所以要求至少保存7天以上的日志，建議剩余空間在10G100G之間。對于RAC環(huán)境對于RAC環(huán)境,GoldenGate的相關(guān)軟件和工作目錄需要配置在shared disk環(huán)境中,從而保證對所有node都是可用的,從任何一個node都可以啟動GoldenGate的進程,當其中一個node出現(xiàn)異常時,可以在剩余的node啟動而無須修改任何配置參數(shù)。否則如果運行在單個node上的話,需要將剩余node中的歸檔日志通過一定的技術(shù)共享出來并加載到GoldenGate運行節(jié)點。對于RAC環(huán)境,GoldenGate要求所有node必須保持時鐘同步,同時必須保持所有RAC node和運行extract進程的node節(jié)點保持時鐘同步。因為GoldenGate會比較本地的系統(tǒng)時間和commit的時間戳。所以不能忽略這個設置。否則可能導致數(shù)據(jù)復制的紊亂。3. 數(shù)據(jù)庫需求數(shù)據(jù)庫客戶端GoldenGate需要安裝full Oracle client,以便于GoldenGate程序訪問Oracle XDK libraries。數(shù)據(jù)庫用戶建議使用sys或任何其他具有sysdba/sysasm權(quán)限的用戶。數(shù)據(jù)庫配置對于Source Oracle Database要求數(shù)據(jù)庫處于歸檔模式下打開補充日志打開force logging分別配置連接源數(shù)據(jù)庫和目標數(shù)據(jù)庫的主機字符串建立一個專門的用戶如Goldengate來負責將所需的數(shù)據(jù)同步到遠端，而不是用已有的業(yè)務用戶這個用戶需要哪些權(quán)限呢?Create user goldengate identified by goldengate default tablespace users temporary tablespace temp quota unlimited on users ;GRANT CONNECT TO goldengate;GRANT ALTER ANY TABLE TO goldengate;GRANT ALTER SESSION TO goldengate;GRANT CREATE SESSION TO goldengate;GRANT FLASHBACK ANY TABLE TO goldengate;GRANT SELECT ANY DICTIONARY TO goldengate;GRANT SELECT ANY TABLE TO goldengate;GRANT RESOURCE TO goldengate;GRANT drop ANY TABLE TO goldengate;GRANT DBA TO goldengate;注*補充日志是否打開查詢語句：select supplemental_log_data_min from v$database;打開補充日志語句：alter database add supplemental log data; 打開force logging語句： Alter database force logging;配置golengate環(huán)境：os linux 4.7 x86_64 數(shù)據(jù)庫：10.0.2.1 64位IP設置：源 192.168.8.1 目標 192.168.8.2SID: ORCL1. 安裝goldengate 軟件在oracle官方網(wǎng)站上下載goldengate對應平臺的安裝程序進入到GoldenGate的安裝目錄.運行GGSCIIn GGSCI, issue the following command to create the GoldenGate working directories.CREATE SUBDIRSIssue the following command to exit GGSCI.EXIT2. 數(shù)據(jù)復制初始化GoldenGate中支持的data load 方法有Loading data with a database utilityLoading data from file to ReplicatLoading data from file to database utilityLoading data with a GoldenGate direct loadLoading data with a direct bulk load to SQL*LoaderLoading tables with Teradata table copyLoading data with Teradata load utilities注*這點我沒怎么研究，我采用的rman異地恢復3. 配置進程第一步: 在源數(shù)據(jù)庫和目標數(shù)據(jù)庫分別配置管理進程mgrGGSCI edit params mgrport 7809(保存退出)GGSCI start mgrManager started.GGSCI info allProgramStatus Group Lag ime Since ChkptMANAGER RUNNING(可以正常啟動)第二步：源數(shù)據(jù)庫增加兩個進程*.一般源數(shù)據(jù)庫需要應配置兩個進程，一個負責抽取數(shù)據(jù)(ZLE_01)，另一個負責投遞數(shù)據(jù)(ZLE_02)，默認只支持DML，如果要支持DDL需要執(zhí)行一些單獨的配置。在源數(shù)據(jù)庫系統(tǒng)中執(zhí)行如下命令增加一個Extract group,詳細的ADD EXTRACT語法參見官方手冊說明或GGSCI的online help.GGSCI add extract , TRANLOG, BEGIN NOW, THREADS 其中g(shù)roup name表示這個Extract group的名字TRANLOG 表示使用事務日志的方式BEGIN NOW 表示從現(xiàn)在開始,你也可以指定一個具體的開始時間THREADS 表示從哪個instance上運行,只對RAC環(huán)境有用,單機環(huán)境比需要指定該Option2.1配置抽取進程我這里是單機環(huán)境,extract group name定義為zle_01GGSCI DBLOGIN USERID goldengateorcl1, PASSWORD goldengateGGSCI add extract ZLE_01, tranlog,begin nowEXTRACT added.GGSCI add RMTTRAIL ./dirdat/ra, extract zle_01, megabytes 50查看一下剛才定義的這個extract process的結(jié)果GGSCI info extract zle_01EXTRACT ZLE_01 Initialized 2010-01-19 11:50 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:22 ago)Log Read Checkpoint Oracle Redo Logs 2010-01-19 11:50:39 Seqno 0, RBA 0補充一個命令: info extract zle_01, showch 看的更詳細為該抽取Extract進程配置參數(shù)文件，在源數(shù)據(jù)庫執(zhí)行如下命令GGSCI edit param zle_01增加如下內(nèi)容EXTRACT ZLE_01SETENV (ORACLE_SID=ORCL)USERID goldengateORCL1, PASSWORD goldengateRMTHOST 192.168.8.1, MGRPORT 9001RMTTRAIL ./dirdat/raTABLE ZLHIS.TEST;RMTTRAIL告訴extract process需要將trail文件寫到哪里TABLE部分說明我這個Extract 進程只處理ZLHIS用戶下的TEST表好了,到這里就可以啟動上面定義的Capture 進程了,執(zhí)行如下命令GGSCI start extract zle_01Sending START request to MANAGER (mgr) .EXTRACT ZLE_01 startingGGSCIVerify the results:GGSCI info EXTRACT ZLE_01, DETAILGGSCI view REPORT ZLE_01GGSCI info extract ZLE_01好了,到這里就完成了一個最簡單的抽取Extract process的配置并啟動，下面來配置投遞Extract process進程。2.2 配置投遞進程在Source系統(tǒng)執(zhí)行如下命令GGSCI ADD EXTRACT ZLE_02, EXTTRAILSOURCE ./dirdat/ra, BEGIN nowGGSCI ADD RMTTRAIL ./dirdat/la, EXTRACT ZLE_02*(./dirdat/la為遠端接收目錄,必須存在)GGSCI edit params zle_02添加內(nèi)容如下：extract ZLE_02dynamicresolutionPASSTHRUrmthost 192.168.8.2, mgrport 9001, compressrmttrail ./dirdat/lanumfiles 3000TABLE ZLHIS.TEST ;GGSCI start extract zle_02Sending START request to MANAGER (mgr) .EXTRACT ZLE_02 starting查看下狀態(tài)：GGSCI info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING ZLE_01 00:00:00 00:00:05EXTRACT RUNNING ZLE_02 00:00:00 00:00:072.3配置接收進程在目標系統(tǒng)(Target system)上執(zhí)行如下命令增加一個復制組(Replicat Group)GGSCI DBLOGIN USERID goldengateorcl2, PASSWORD goldengateGGSCIadd replicat ZLR_01, EXTTRAIL ./dirdat/la , nodbcheckpoint注,我這個中配置Extract process那個示例對應的,所以這里的exttrail文件名正好是Extract process中定義的RMTTRAIL ./dirdat/la這點需要大家注意.GGSCI edit param ZLR_01增加如下內(nèi)容并保存退出REPLICAT ZLR_01SETENV (ORACLE_SID=ORCL)USERID goldengateORCL2, PASSWORD goldengateHANDLECOLLISIONSASSUMETARGETDEFSallownoopupdatesdynamicresolutionnumfiles 3000DISCARDFILE ./dirrpt/ ZLR_01.DSC,append,megabytes 10map ZLHIS.TEST , target ZLHIS.TEST ;在Target系統(tǒng)中,執(zhí)行如下命令GGSCI start replicat ZLR_01Verify the results:GGSCI info replicat ZLR_01REPLICAT ZLR_01 Last Started 2010-01-19 13:50 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:03 ago)Log Read Checkpoint File ./dirdat/la000000 First Record RBA 0查看下狀態(tài)：GGSCI info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING ZLR_01 00:00:00 00:00:09測試效果在源數(shù)據(jù)庫分別插入、修改、刪除一條數(shù)據(jù)。查看抽取進程狀態(tài)：GGSCI stats ZLE_01Sending STATS request to EXTRACT ZLE_01 .Start of Statistics at 2010-01-26 02:53:01.Output to ./dirdat/ra:Extracting from ZLHIS.TEST to ZLHIS.TEST:* Total statistics since 2010-01-26 02:15:01 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Daily statistics since 2010-01-26 02:15:01 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Hourly statistics since 2010-01-26 02:15:01 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Latest statistics since 2010-01-26 02:15:01 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00End of Statistics.可以看到已經(jīng)有記錄，而且已經(jīng)抽取成功，再查看投遞進程，GGSCI stats ZLE_02Sending STATS request to EXTRACT ZLE_02 .Start of Statistics at 2010-01-26 03:04:13.Output to /u01/ggs/dirdat/re:Extracting from ZLHIS.TEST to ZLHIS.TEST:* Total statistics since 2010-01-26 02:14:25 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Daily statistics since 2010-01-26 02:14:25 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Hourly statistics since 2010-01-26 03:00:00 * No database operations have been performed.* Latest statistics since 2010-01-26 02:14:25 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00End of Statistics.切換到目標數(shù)據(jù)庫環(huán)境，同樣查看接受應用進程。GGSCI stats ZLR_01Sending STATS request to REPLICAT ZLR_01 .Start of Statistics at 2010-01-26 05:58:36.Replicating from ZLHIS.TEST to ZLHIS.TEST:* Total statistics since 2010-01-26 04:46:57 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Daily statistics since 2010-01-26 04:46:57 * Total inserts 1.00 Total updates 1.00 Total deletes 1.00 Total discards 0.00 Total operations 3.00* Hourly statistics since 2010-01-26 05:00:00 * Total inserts 0.00 To