Golodengate 實施步驟

murkey發表於2014-01-11
Go

一、GoldenGate實施環境

source database：aix 6.1 oracle 11.2.0.3

target database：windows server 2008 R2 oracle 11.2.0.3

本專案結構比較清晰，拓樸結構如下圖所示（一對一單向）

clip_image002

需要配置的程式如下：

source database：extract、data pump

target database：replicat

說明如下：

1、主提取程式首先將trail生成在本地，然後datapump讀取本地trail再傳送到目標伺服器，即便網路故障，主提取程式仍然能隨著事務生成trail檔案，而datapump則會暫時停止傳輸，等待網路通暢後在將堆積的本地trail檔案傳送至目標伺服器，從而實現了斷點傳輸的功能。在實際應用中，每一個同步流程都應該配置datapump以應對網路問題。

2、配置程式檢查點（checkpoint）：檢查點記錄了程式讀寫的位置資訊用以資料恢復，目的是為了防止程式因系統、網路崩潰而導致的資料丟失。oracle推薦將複製程式的檢查點資訊存放到資料庫表中進行管理：

首先在./globals引數檔案中加入：

CHECKPOINTTABLE [.

] --指定的檢查點記錄表

然後執行：

GGSCI> DBLOGIN USERID , PASSWORD

GGSCI> ADD CHECKPOINTTABLE [.

] --生成這個檢查點記錄表

3、GoldenGate的DDL同步只支援兩邊一致的資料庫，限制條件較多（如不能進行欄位對映、轉換等），具體可以參考官方文件。DDL的抓取不是通過日誌抓取來捕獲的，而是通過觸發器來實現，所以對源資料庫的效能影響要比單純的資料抓取要大很多，可謂屏棄了GoldenGate的優勢。儘量不要使用GoldenGate的DDL複製功能，在大多數業務系統中，實際上不會有頻繁的資料庫結構變動，完全可以通過手工的方式進行維護。確實有大量DDL操作的環境，如果可以，還是推薦物理DG之類的替換方案；確實要使用GoldenGate的DDL複製，請詳細參考官方文件的限制和說明。

4、如果需要配置DDL支援，需執行如下幾個指令碼：marker_setup.sql，ddl_setup.sql，role_setup.sql，grant GGS_GGSUSER_ROLE to ; ddl_enable.sql。本專案無此需求，故不需配置DDL同步。

5、配置source和target端tnsnames.ora

6、查詢是否有不支援的資料型別

7、是否有壓縮表和不支援的表型別

8、禁用容災端資料庫的外來鍵，trigger和有DML操作的JOB

二、GoldenGate軟體安裝

2.1 Unix下安裝

在source database 和 target database 都執行如下操作：

# su - oracle

/home/oracle> mkdir goldengate

在此goldengate下面解壓下載的安裝包

/home/oracle>cd goldengate

/home/oracle/goldengate> ldd ggsci

將列出所有需要的lib和當前缺少的

新增環境變數

在/home/oracle/. profile檔案裡新增如下內容：

export PATH=/home/oracle/goldengate:$PATH

export LIBPATH =/home/oracle/goldengate:$ORACLE_HOME/lib;

export GGATE=/home/oracle/goldengate

注：

IBM AIX：LIBPATH

HPUX： SHLIB_PATH

SOLARIS/LINUX：LD_LIBRARY_PATH

使環境變數生效

source /home/oracle/. profile

2.2建立目錄

使用ggsci工具，建立必要的目錄

/home/oracle/goldengate > ./ggsci

--呼叫ggsci 工具

GGSCI> create subdirs

Creating subdirectories under currentdirectory /home/oracle/goldengate

Parameter files                /home/oracle/goldengate/dirprm: created

Report files                   /home/oracle/goldengate/dirrpt: created

Checkpoint files               /home/oracle/goldengate/dirchk: created

Process status files           /home/oracle/goldengate/dirpcs: created

SQL script files               /home/oracle/goldengate/dirsql: created

Database definitions files     /home/oracle/goldengate/dirdef: created

Extract data files             /home/oracle/goldengate/dirdat: created

Temporary files                /home/oracle/goldengate/dirtmp: created

Veridata files                 /home/oracle/goldengate/dirver: created

Veridata Lock files            /home/oracle/goldengate/dirver/lock: created

Veridata Out-Of-Sync files     /home/oracle/goldengate/dirver/oos: created

Veridata Out-Of-Sync XML files/home/oracle/goldengate/dirver/oosxml: created

Veridata Parameter files       /home/oracle/goldengate/dirver/params: created

Veridata Report files          /home/oracle/goldengate/dirver/report: created

Veridata Status files          /home/oracle/goldengate/dirver/status: created

Veridata Trace files           /home/oracle/goldengate/dirver/trace: created

Stdout files                   /home/oracle/goldengate/dirout: created

以上就是GG 的安裝，在source 和target database 都執行。

三、配置Source database

注意：只配置Source database，而非target database

歸檔模式、附加日誌、強制日誌

Oracle資料庫需要開啟歸檔日誌，並開啟最小附加日誌模式。

SQL>archive log list;（需要mount狀態，並重啟資料庫，步驟略）

SQL> select supplemental_log_data_min from v$database; --檢視是否開啟了最小附加日誌模式

SQL> alter database add supplemental log data; --開啟最小附加日誌模式

實際生產應用中，最好同時開啟ORACLE的強制日誌模式，以防止源資料庫因直接路徑載入忽略redo生成而導致這部分資料無法同步：

SQL> select force_logging from v$database;

SQL> Alter database force logging;

開啟最小附加日誌模式還不夠，還需要開啟表級的補全日誌，可以在GoldenGate中使用add trandata命令強制重做日誌記錄主鍵值，以保證在目標端能成功複製：

GGSCI> dblogin userid username,password pw --GoldenGate中登入OARCLE資料庫

GGSCI>add trandata username. --表名可以使用萬用字元

注：此方法必須要求表有主鍵值或者非空唯一索引鍵，如果需要同步的只是一個使用者或者某幾個使用者下的某些表，則推薦此方法即可，如下步驟則不需要。

GGSCI> add trandata coss3.per_test,nokey,cols(sampletime, objectid)

--無主鍵指定欄位補全的示例

也可以在資料庫中開啟：

SQL> alter table add supplemental log data (primary key) columns;

千萬不要小看這步日誌設定，其實在GoldenGate的配置中，這步是最容易出錯的環節。如果開啟DDL複製做冗災備份，最好直接在資料庫級別開啟補全日誌：

SQL> alter database add supplemental log data (primary key,unique,foreign key) columns;

檢查一下，全是YES就OK了（整個資料庫級別補全）

SQL> select supplemental_log_data_min,

supplemental_log_data_pk,supplemental_log_data_ui

from v$database;

關於Oracle補全日誌，如果表中無主鍵，則補全一個非空唯一索引列，如果非空唯一索引鍵也沒，那麼會補全除了LOB和LONG型別欄位以外的所有列，更多資訊請參考官方文件。

四、golden gate程式配置

4.1在Source和Target上配置Manager

Source：

GGSCI>edit param mgr --配置GoldenGate主程式引數

port 7801 --GoldenGate主程式埠號

DYNAMICPORTLIST 7802-7810 -GoldenGate為程式間通訊動態分配的埠段，注意這裡如果分配的埠端少於extract-replicat程式對的話，會導致部分程式因通訊失敗而出錯。

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 7

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

target：

GGSCI>edit param mgr

port 7801

DYNAMICPORTLIST 7802-7810

AUTOSTART REPLICAT *

AUTORESTART REPLICAT *,RETRIES 5,WAITMINUTES 7

PURGEOLDREPLICAT S ./dirdat/*,usecheckpoints, minkeepdays 3

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

儲存，生成的引數檔案儲存在GG_HOME\ dirprm下

然後可以啟動GoldenGate主控制程式：

GGSCI>start mgr

GGSCI>info all --檢視程式狀態

如果程式MANAGER狀態顯示為RUNNING則表示主程式已在執行

4.2 source端新增提取程式(extract process)

增加一個抽取：

GGSCI > add extract ext1,tranlog, begin now

GGSCI > edit params ext1

EXTRACT ext1

setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

userid tianch3,password tianch3

GETTRUNCATES

REPORTCOUNT EVERY 5 MINUTES, RATE

numfiles 50000

DISCARDFILE ./dirrpt/extsa.dsc,APPEND,MEGABYTES 50

WARNLONGTRANS 2h,CHECKINTERVAL 3m

EXTTRAIL ./dirdat/sa

DYNAMICRESOLUTION

table tianch3.sbbtdzzsqs;

table tianch3.sbb_ccs_zb;

table tianch3.sbb_cl_normal_cbj;

table tianch3.sbb_cl_normal_cbj_cjr;

table tianch3.sbb_cl_ns_cbj;

table tianch3.sbb_cl_ns_cbj_cjr;

table tianch3.sbb_cl_y_cbj;

table tianch3.sbb_cl_y_cbj_cjr;

table tianch3.sbb_csjs;

table tianch3.sbb_cstd;

table tianch3.sbb_cwbb_bxgs_lrb;

table tianch3.sbb_cwbb_bxgs_syzqybdb;

table tianch3.sbb_cwbb_bxgs_xjllb;

table tianch3.sbb_cwbb_bxgs_zcfzb;

table tianch3.sbb_cwbb_qykjzd_lrb;

table tianch3.sbb_cwbb_qykjzd_syzqyzjbdb;

table tianch3.sbb_cwbb_qykjzd_xjllb;

table tianch3.sbb_cwbb_qykjzd_zcfzb;

table tianch3.sbb_cwbb_syyh_lrb;

table tianch3.sbb_cwbb_syyh_syzqybdb;

table tianch3.sbb_cwbb_syyh_xjllb;

table tianch3.sbb_cwbb_syyh_zcfzb;

table tianch3.sbb_cwbb_xqykjzzzcfzb;

table tianch3.sbb_cwbb_xqy_kjzd_lrb;

table tianch3.sbb_cwbb_xqy_kjzd_xjllb;

table tianch3.sbb_cwbb_ybqy_lrb;

table tianch3.sbb_cwbb_ybqy_syzqybdb;

table tianch3.sbb_cwbb_ybqy_xjllb;

table tianch3.sbb_cwbb_ybqy_zcfzb;

table tianch3.sbb_cwbb_zqgs_lrb;

table tianch3.sbb_cwbb_zqgs_syzqybdb;

table tianch3.sbb_cwbb_zqgs_xjllb;

table tianch3.sbb_cwbb_zqgs_zcfzb;

table tianch3.sbb_dkdjccs_zb;

table tianch3.sbb_dkdj_dsdj_bgb_fb;

table tianch3.sbb_dkdj_dsdj_bgb_zb;

table tianch3.sbb_dkdj_skbgb;

table tianch3.sbb_dkdj_zqjyyhs_bgb;

table tianch3.sbb_dsdj_ccs_jm_fb;

table tianch3.sbb_dsdj_ccs_mx_fb;

table tianch3.sbb_dsdj_ccs_ws_fb;

table tianch3.sbb_dsdj_ccs_wws_fb;

table tianch3.sbb_fcs;

table tianch3.sbb_fspjgtjjj;

table tianch3.sbb_ghjf;

table tianch3.sbb_grsds_grdz_hhqy_nd;

table tianch3.sbb_grsds_grdz_hhqy_nd_mxxx;

table tianch3.sbb_grsds_grdz_hhqy_yj;

table tianch3.sbb_grsds_grdz_hhqy_yj_mxxx;

table tianch3.sbb_grsds_hz;

table tianch3.sbb_grsds_qnycxjj;

table tianch3.sbb_grsds_scjysd;

table tianch3.sbb_grsds_scjysd_a;

table tianch3.sbb_grsds_scjysd_b;

table tianch3.sbb_grsds_zxsb_12w;

table tianch3.sbb_grsds_zxsb_12w_mxxx;

table tianch3.sbb_grsds_zxsb_nd;

table tianch3.sbb_grsds_zxsb_nd_mxxx;

table tianch3.sbb_grsds_zxsb_yf;

table tianch3.sbb_grsds_zxsb_yf_mxxx;

table tianch3.sbb_jyffj;

table tianch3.sbb_qysds_2008_jd_a_fb01;

table tianch3.sbb_qysds_2008_jd_a_zb;

table tianch3.sbb_qysds_2008_jd_b;

table tianch3.sbb_qysds_2012_jd_a_zb;

table tianch3.sbb_qysds_2012_jd_b;

table tianch3.sbb_qysds_2012_nd_b;

table tianch3.sbb_qysds_nd_a_cbfymxb;

table tianch3.sbb_qysds_nd_a_fqy_srmxb;

table tianch3.sbb_qysds_nd_a_fqy_zcmxb;

table tianch3.sbb_qysds_nd_a_gkxc_nstzb;

table tianch3.sbb_qysds_nd_a_gyjz_nstzb;

table tianch3.sbb_qysds_nd_a_jrqy_cbmxb;

table tianch3.sbb_qysds_nd_a_jrqy_srmxb;

table tianch3.sbb_qysds_nd_a_mbksmxb;

table tianch3.sbb_qysds_nd_a_nstzxmmxb;

table tianch3.sbb_qysds_nd_a_srmxb;

table tianch3.sbb_qysds_nd_a_ssyhmxb;

table tianch3.sbb_qysds_nd_a_tzsdmxb;

table tianch3.sbb_qysds_nd_a_tzssbczl;

table tianch3.sbb_qysds_nd_a_tzssbczl_bc;

table tianch3.sbb_qysds_nd_a_ywsds_dmmxb;

table tianch3.sbb_qysds_nd_a_zb;

table tianch3.sbb_qysds_nd_a_zcjz_tzmxb;

table tianch3.sbb_qysds_nd_a_zczj_tzmxb;

table tianch3.sbb_qysds_nd_b;

table tianch3.sbb_qysds_nd_b_ssyhmxb;

table tianch3.sbb_sljsjj;

table tianch3.sbb_tdzzs_one;

table tianch3.sbb_whsyjsf;

table tianch3.sbb_wtdz;

table tianch3.sbb_wtdz_fb;

table tianch3.sbb_wtdz_zb;

table tianch3.sbb_yhs;

table tianch3.sbb_yyes_fb;

table tianch3.sbb_yyes_zb;

table tianch3.sbb_yys_fwy_fb;

table tianch3.sbb_yys_fwy_jcxm_fb;

table tianch3.sbb_yys_jrbx_fb;

table tianch3.sbb_yys_jtysy_fb;

table tianch3.sbb_yys_jzy_fb;

table tianch3.sbb_yys_qy_fb;

table tianch3.sbb_yys_whty_fb;

table tianch3.sbb_yys_xsbdc_fb;

table tianch3.sbb_yys_ydjzy_fb;

table tianch3.sbb_yys_yly_fb;

table tianch3.sbb_yys_yzdx_fb;

table tianch3.sbb_yys_zb;

table tianch3.sbb_yys_zrwxzc_fb;

table tianch3.sbb_zdsy_fdcqybb;

table tianch3.sbb_zdsy_gyqycpybb;

table tianch3.sbb_zdsy_qycwxxbb;

table tianch3.sbb_zdsy_qyssybb;

table tianch3.sbb_zdsy_qywjdc;

table tianch3.sbb_zhsf;

table tianch3.sbb_zys;

請注意 ：

文件中4.2後面落下了一步

GGSCI> ADD EXTTRAIL ./dirdat/sa, EXTRACT EXT1,MEGABYTES 50

這是本地的extrail

然後data pump和target端的./dirdat/ra相關聯

GGSCI> add rmttrail ./dirdat/ra extract dpext1

這是遠端的trail

4.3 source端新增data pump程式

GGSCI> ADD EXTRACT dpext1, EXTTRAILSOURCE ./dirdat/sa, BEGIN now

GGSCI>view params dpext1

EXTRACT dpext1

RMTHOST 172.16.1.81, MGRPORT 7801

PASSTHRU --直通模式或普通模式

numfiles 50000

RMTTRAIL ./dirdat/ra

DYNAMICRESOLUTION

table tianch3.sbbtdzzsqs;

table tianch3.sbb_ccs_zb;

table tianch3.sbb_cl_normal_cbj;

table tianch3.sbb_cl_normal_cbj_cjr;

table tianch3.sbb_cl_ns_cbj;

table tianch3.sbb_cl_ns_cbj_cjr;

table tianch3.sbb_cl_y_cbj;

table tianch3.sbb_cl_y_cbj_cjr;

table tianch3.sbb_csjs;

table tianch3.sbb_cstd;

table tianch3.sbb_cwbb_bxgs_lrb;

table tianch3.sbb_cwbb_bxgs_syzqybdb;

table tianch3.sbb_cwbb_bxgs_xjllb;

table tianch3.sbb_cwbb_bxgs_zcfzb;

table tianch3.sbb_cwbb_qykjzd_lrb;

table tianch3.sbb_cwbb_qykjzd_syzqyzjbdb;

table tianch3.sbb_cwbb_qykjzd_xjllb;

table tianch3.sbb_cwbb_qykjzd_zcfzb;

table tianch3.sbb_cwbb_syyh_lrb;

table tianch3.sbb_cwbb_syyh_syzqybdb;

table tianch3.sbb_cwbb_syyh_xjllb;

table tianch3.sbb_cwbb_syyh_zcfzb;

table tianch3.sbb_cwbb_xqykjzzzcfzb;

table tianch3.sbb_cwbb_xqy_kjzd_lrb;

table tianch3.sbb_cwbb_xqy_kjzd_xjllb;

table tianch3.sbb_cwbb_ybqy_lrb;

table tianch3.sbb_cwbb_ybqy_syzqybdb;

table tianch3.sbb_cwbb_ybqy_xjllb;

table tianch3.sbb_cwbb_ybqy_zcfzb;

table tianch3.sbb_cwbb_zqgs_lrb;

table tianch3.sbb_cwbb_zqgs_syzqybdb;

table tianch3.sbb_cwbb_zqgs_xjllb;

table tianch3.sbb_cwbb_zqgs_zcfzb;

table tianch3.sbb_dkdjccs_zb;

table tianch3.sbb_dkdj_dsdj_bgb_fb;

table tianch3.sbb_dkdj_dsdj_bgb_zb;

table tianch3.sbb_dkdj_skbgb;

table tianch3.sbb_dkdj_zqjyyhs_bgb;

table tianch3.sbb_dsdj_ccs_jm_fb;

table tianch3.sbb_dsdj_ccs_mx_fb;

table tianch3.sbb_dsdj_ccs_ws_fb;

table tianch3.sbb_dsdj_ccs_wws_fb;

table tianch3.sbb_fcs;

table tianch3.sbb_fspjgtjjj;

table tianch3.sbb_ghjf;

table tianch3.sbb_grsds_grdz_hhqy_nd;

table tianch3.sbb_grsds_grdz_hhqy_nd_mxxx;

table tianch3.sbb_grsds_grdz_hhqy_yj;

table tianch3.sbb_grsds_grdz_hhqy_yj_mxxx;

table tianch3.sbb_grsds_hz;

table tianch3.sbb_grsds_qnycxjj;

table tianch3.sbb_grsds_scjysd;

table tianch3.sbb_grsds_scjysd_a;

table tianch3.sbb_grsds_scjysd_b;

table tianch3.sbb_grsds_zxsb_12w;

table tianch3.sbb_grsds_zxsb_12w_mxxx;

table tianch3.sbb_grsds_zxsb_nd;

table tianch3.sbb_grsds_zxsb_nd_mxxx;

table tianch3.sbb_grsds_zxsb_yf;

table tianch3.sbb_grsds_zxsb_yf_mxxx;

table tianch3.sbb_jyffj;

table tianch3.sbb_qysds_2008_jd_a_fb01;

table tianch3.sbb_qysds_2008_jd_a_zb;

table tianch3.sbb_qysds_2008_jd_b;

table tianch3.sbb_qysds_2012_jd_a_zb;

table tianch3.sbb_qysds_2012_jd_b;

table tianch3.sbb_qysds_2012_nd_b;

table tianch3.sbb_qysds_nd_a_cbfymxb;

table tianch3.sbb_qysds_nd_a_fqy_srmxb;

table tianch3.sbb_qysds_nd_a_fqy_zcmxb;

table tianch3.sbb_qysds_nd_a_gkxc_nstzb;

table tianch3.sbb_qysds_nd_a_gyjz_nstzb;

table tianch3.sbb_qysds_nd_a_jrqy_cbmxb;

table tianch3.sbb_qysds_nd_a_jrqy_srmxb;

table tianch3.sbb_qysds_nd_a_mbksmxb;

table tianch3.sbb_qysds_nd_a_nstzxmmxb;

table tianch3.sbb_qysds_nd_a_srmxb;

table tianch3.sbb_qysds_nd_a_ssyhmxb;

table tianch3.sbb_qysds_nd_a_tzsdmxb;

table tianch3.sbb_qysds_nd_a_tzssbczl;

table tianch3.sbb_qysds_nd_a_tzssbczl_bc;

table tianch3.sbb_qysds_nd_a_ywsds_dmmxb;

table tianch3.sbb_qysds_nd_a_zb;

table tianch3.sbb_qysds_nd_a_zcjz_tzmxb;

table tianch3.sbb_qysds_nd_a_zczj_tzmxb;

table tianch3.sbb_qysds_nd_b;

table tianch3.sbb_qysds_nd_b_ssyhmxb;

table tianch3.sbb_sljsjj;

table tianch3.sbb_tdzzs_one;

table tianch3.sbb_whsyjsf;

table tianch3.sbb_wtdz;

table tianch3.sbb_wtdz_fb;

table tianch3.sbb_wtdz_zb;

table tianch3.sbb_yhs;

table tianch3.sbb_yyes_fb;

table tianch3.sbb_yyes_zb;

table tianch3.sbb_yys_fwy_fb;

table tianch3.sbb_yys_fwy_jcxm_fb;

table tianch3.sbb_yys_jrbx_fb;

table tianch3.sbb_yys_jtysy_fb;

table tianch3.sbb_yys_jzy_fb;

table tianch3.sbb_yys_qy_fb;

table tianch3.sbb_yys_whty_fb;

table tianch3.sbb_yys_xsbdc_fb;

table tianch3.sbb_yys_ydjzy_fb;

table tianch3.sbb_yys_yly_fb;

table tianch3.sbb_yys_yzdx_fb;

table tianch3.sbb_yys_zb;

table tianch3.sbb_yys_zrwxzc_fb;

table tianch3.sbb_zdsy_fdcqybb;

table tianch3.sbb_zdsy_gyqycpybb;

table tianch3.sbb_zdsy_qycwxxbb;

table tianch3.sbb_zdsy_qyssybb;

table tianch3.sbb_zdsy_qywjdc;

table tianch3.sbb_zhsf;

table tianch3.sbb_zys;

GGSCI> add rmttrail ./dirdat/ra extract dpext1

4.3 target端建立表空間及授權

Create tablespace tianch datafile size 10240m;

CREATE USER sjcktb IDENTIFIED by oracle DEFAULT TABLESPACE tianch;

GRANT CONNECT TO sjcktb;

GRANT RESOURCE TO sjcktb;

GRANT CREATE SESSION to sjcktb;

GRANT ALTER SESSION to sjcktb;

GRANT CREATE TABLE TO sjcktb;

GRANT FLASHBACK ANY TABLE TO sjcktb;

GRANT SELECT ANY DICTIONARY TO sjcktb;

GRANT SELECT ANY TABLE TO sjcktb;

GRANT ALTER ANY TABLE TO sjcktb;

GRANT UPDATE ANY TABLE TO sjcktb;

GRANT DELETE ANY TABLE TO sjcktb;

授權比較繁瑣，有直接給dba許可權的。

4.4 增加target程式檢查點

Add a Replicat checkpoint table

GGSCI >edit params ./globals

CHECKPOINTTABLE sjcktb.checkpoint

GGSCI > dblogin userid sjcktb, password sjcktb

Successfully logged into database.

GGSCI >add CHECKPOINTTABLE sjcktb.checkpoint

4.5 配置target同步佇列

GGSCI>add replicat rep1 exttrail ./dirdat/ra, checkpointtable sjcktb.checkpoint

GGSCI>view param rep1

REPLICAT rep1

USERID sjcktb,PASSWORD sjcktb

SETENV (NLS_LANG = "American_America.ZHS16GBK")

REPORT AT 01:59

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

numfiles 50000

HANDLECOLLISIONS --去重

assumetargetdefs

DISCARDFILE ./dirrpt/repsa.dsc, APPEND, MEGABYTES 50

GETTRUNCATES

ALLOWNOOPUPDATES

map tianch3.sbbtdzzsqs, target sjcktb.sbbtdzzsqs;

map tianch3.sbb_ccs_zb, target sjcktb.sbb_ccs_zb;

map tianch3.sbb_cl_normal_cbj, target sjcktb.sbb_cl_normal_cbj;

map tianch3.sbb_cl_normal_cbj_cjr, target sjcktb.sbb_cl_normal_cbj_cjr;

map tianch3.sbb_cl_ns_cbj, target sjcktb.sbb_cl_ns_cbj;

map tianch3.sbb_cl_ns_cbj_cjr, target sjcktb.sbb_cl_ns_cbj_cjr;

map tianch3.sbb_cl_y_cbj, target sjcktb.sbb_cl_y_cbj;

map tianch3.sbb_cl_y_cbj_cjr, target sjcktb.sbb_cl_y_cbj_cjr;

map tianch3.sbb_csjs, target sjcktb.sbb_csjs;

map tianch3.sbb_cstd, target sjcktb.sbb_cstd;

map tianch3.sbb_cwbb_bxgs_lrb, target sjcktb.sbb_cwbb_bxgs_lrb;

map tianch3.sbb_cwbb_bxgs_syzqybdb, target sjcktb.sbb_cwbb_bxgs_syzqybdb;

map tianch3.sbb_cwbb_bxgs_xjllb, target sjcktb.sbb_cwbb_bxgs_xjllb;

map tianch3.sbb_cwbb_bxgs_zcfzb, target sjcktb.sbb_cwbb_bxgs_zcfzb;

map tianch3.sbb_cwbb_qykjzd_lrb, target sjcktb.sbb_cwbb_qykjzd_lrb;

map tianch3.sbb_cwbb_qykjzd_syzqyzjbdb, target sjcktb.sbb_cwbb_qykjzd_syzqyzjbdb;

map tianch3.sbb_cwbb_qykjzd_xjllb, target sjcktb.sbb_cwbb_qykjzd_xjllb;

map tianch3.sbb_cwbb_qykjzd_zcfzb, target sjcktb.sbb_cwbb_qykjzd_zcfzb;

map tianch3.sbb_cwbb_syyh_lrb, target sjcktb.sbb_cwbb_syyh_lrb;

map tianch3.sbb_cwbb_syyh_syzqybdb, target sjcktb.sbb_cwbb_syyh_syzqybdb;

map tianch3.sbb_cwbb_syyh_xjllb, target sjcktb.sbb_cwbb_syyh_xjllb;

map tianch3.sbb_cwbb_syyh_zcfzb, target sjcktb.sbb_cwbb_syyh_zcfzb;

map tianch3.sbb_cwbb_xqykjzzzcfzb, target sjcktb.sbb_cwbb_xqykjzzzcfzb;

map tianch3.sbb_cwbb_xqy_kjzd_lrb, target sjcktb.sbb_cwbb_xqy_kjzd_lrb;

map tianch3.sbb_cwbb_xqy_kjzd_xjllb, target sjcktb.sbb_cwbb_xqy_kjzd_xjllb;

map tianch3.sbb_cwbb_ybqy_lrb, target sjcktb.sbb_cwbb_ybqy_lrb;

map tianch3.sbb_cwbb_ybqy_syzqybdb, target sjcktb.sbb_cwbb_ybqy_syzqybdb;

map tianch3.sbb_cwbb_ybqy_xjllb, target sjcktb.sbb_cwbb_ybqy_xjllb;

map tianch3.sbb_cwbb_ybqy_zcfzb, target sjcktb.sbb_cwbb_ybqy_zcfzb;

map tianch3.sbb_cwbb_zqgs_lrb, target sjcktb.sbb_cwbb_zqgs_lrb;

map tianch3.sbb_cwbb_zqgs_syzqybdb, target sjcktb.sbb_cwbb_zqgs_syzqybdb;

map tianch3.sbb_cwbb_zqgs_xjllb, target sjcktb.sbb_cwbb_zqgs_xjllb;

map tianch3.sbb_cwbb_zqgs_zcfzb, target sjcktb.sbb_cwbb_zqgs_zcfzb;

map tianch3.sbb_dkdjccs_zb, target sjcktb.sbb_dkdjccs_zb;

map tianch3.sbb_dkdj_dsdj_bgb_fb, target sjcktb.sbb_dkdj_dsdj_bgb_fb;

map tianch3.sbb_dkdj_dsdj_bgb_zb, target sjcktb.sbb_dkdj_dsdj_bgb_zb;

map tianch3.sbb_dkdj_skbgb, target sjcktb.sbb_dkdj_skbgb;

map tianch3.sbb_dkdj_zqjyyhs_bgb, target sjcktb.sbb_dkdj_zqjyyhs_bgb;

map tianch3.sbb_dsdj_ccs_jm_fb, target sjcktb.sbb_dsdj_ccs_jm_fb;

map tianch3.sbb_dsdj_ccs_mx_fb, target sjcktb.sbb_dsdj_ccs_mx_fb;

map tianch3.sbb_dsdj_ccs_ws_fb, target sjcktb.sbb_dsdj_ccs_ws_fb;

map tianch3.sbb_dsdj_ccs_wws_fb, target sjcktb.sbb_dsdj_ccs_wws_fb;

map tianch3.sbb_fcs, target sjcktb.sbb_fcs;

map tianch3.sbb_fspjgtjjj, target sjcktb.sbb_fspjgtjjj;

map tianch3.sbb_ghjf, target sjcktb.sbb_ghjf;

map tianch3.sbb_grsds_grdz_hhqy_nd, target sjcktb.sbb_grsds_grdz_hhqy_nd;

map tianch3.sbb_grsds_grdz_hhqy_nd_mxxx, target sjcktb.sbb_grsds_grdz_hhqy_nd_mxxx;

map tianch3.sbb_grsds_grdz_hhqy_yj, target sjcktb.sbb_grsds_grdz_hhqy_yj;

map tianch3.sbb_grsds_grdz_hhqy_yj_mxxx, target sjcktb.sbb_grsds_grdz_hhqy_yj_mxxx;

map tianch3.sbb_grsds_hz, target sjcktb.sbb_grsds_hz;

map tianch3.sbb_grsds_qnycxjj, target sjcktb.sbb_grsds_qnycxjj;

map tianch3.sbb_grsds_scjysd, target sjcktb.sbb_grsds_scjysd;

map tianch3.sbb_grsds_scjysd_a, target sjcktb.sbb_grsds_scjysd_a;

map tianch3.sbb_grsds_scjysd_b, target sjcktb.sbb_grsds_scjysd_b;

map tianch3.sbb_grsds_zxsb_12w, target sjcktb.sbb_grsds_zxsb_12w;

map tianch3.sbb_grsds_zxsb_12w_mxxx, target sjcktb.sbb_grsds_zxsb_12w_mxxx;

map tianch3.sbb_grsds_zxsb_nd, target sjcktb.sbb_grsds_zxsb_nd;

map tianch3.sbb_grsds_zxsb_nd_mxxx, target sjcktb.sbb_grsds_zxsb_nd_mxxx;

map tianch3.sbb_grsds_zxsb_yf, target sjcktb.sbb_grsds_zxsb_yf;

map tianch3.sbb_grsds_zxsb_yf_mxxx, target sjcktb.sbb_grsds_zxsb_yf_mxxx;

map tianch3.sbb_jyffj, target sjcktb.sbb_jyffj;

map tianch3.sbb_qysds_2008_jd_a_fb01, target sjcktb.sbb_qysds_2008_jd_a_fb01;

map tianch3.sbb_qysds_2008_jd_a_zb, target sjcktb.sbb_qysds_2008_jd_a_zb;

map tianch3.sbb_qysds_2008_jd_b, target sjcktb.sbb_qysds_2008_jd_b;

map tianch3.sbb_qysds_2012_jd_a_zb, target sjcktb.sbb_qysds_2012_jd_a_zb;

map tianch3.sbb_qysds_2012_jd_b, target sjcktb.sbb_qysds_2012_jd_b;

map tianch3.sbb_qysds_2012_nd_b, target sjcktb.sbb_qysds_2012_nd_b;

map tianch3.sbb_qysds_nd_a_cbfymxb, target sjcktb.sbb_qysds_nd_a_cbfymxb;

map tianch3.sbb_qysds_nd_a_fqy_srmxb, target sjcktb.sbb_qysds_nd_a_fqy_srmxb;

map tianch3.sbb_qysds_nd_a_fqy_zcmxb, target sjcktb.sbb_qysds_nd_a_fqy_zcmxb;

map tianch3.sbb_qysds_nd_a_gkxc_nstzb, target sjcktb.sbb_qysds_nd_a_gkxc_nstzb;

map tianch3.sbb_qysds_nd_a_gyjz_nstzb, target sjcktb.sbb_qysds_nd_a_gyjz_nstzb;

map tianch3.sbb_qysds_nd_a_jrqy_cbmxb, target sjcktb.sbb_qysds_nd_a_jrqy_cbmxb;

map tianch3.sbb_qysds_nd_a_jrqy_srmxb, target sjcktb.sbb_qysds_nd_a_jrqy_srmxb;

map tianch3.sbb_qysds_nd_a_mbksmxb, target sjcktb.sbb_qysds_nd_a_mbksmxb;

map tianch3.sbb_qysds_nd_a_nstzxmmxb, target sjcktb.sbb_qysds_nd_a_nstzxmmxb;

map tianch3.sbb_qysds_nd_a_srmxb, target sjcktb.sbb_qysds_nd_a_srmxb;

map tianch3.sbb_qysds_nd_a_ssyhmxb, target sjcktb.sbb_qysds_nd_a_ssyhmxb;

map tianch3.sbb_qysds_nd_a_tzsdmxb, target sjcktb.sbb_qysds_nd_a_tzsdmxb;

map tianch3.sbb_qysds_nd_a_tzssbczl, target sjcktb.sbb_qysds_nd_a_tzssbczl;

map tianch3.sbb_qysds_nd_a_tzssbczl_bc, target sjcktb.sbb_qysds_nd_a_tzssbczl_bc;

map tianch3.sbb_qysds_nd_a_ywsds_dmmxb, target sjcktb.sbb_qysds_nd_a_ywsds_dmmxb;

map tianch3.sbb_qysds_nd_a_zb, target sjcktb.sbb_qysds_nd_a_zb;

map tianch3.sbb_qysds_nd_a_zcjz_tzmxb, target sjcktb.sbb_qysds_nd_a_zcjz_tzmxb;

map tianch3.sbb_qysds_nd_a_zczj_tzmxb, target sjcktb.sbb_qysds_nd_a_zczj_tzmxb;

map tianch3.sbb_qysds_nd_b, target sjcktb.sbb_qysds_nd_b;

map tianch3.sbb_qysds_nd_b_ssyhmxb, target sjcktb.sbb_qysds_nd_b_ssyhmxb;

map tianch3.sbb_sljsjj, target sjcktb.sbb_sljsjj;

map tianch3.sbb_tdzzs_one, target sjcktb.sbb_tdzzs_one;

map tianch3.sbb_whsyjsf, target sjcktb.sbb_whsyjsf;

map tianch3.sbb_wtdz, target sjcktb.sbb_wtdz;

map tianch3.sbb_wtdz_fb, target sjcktb.sbb_wtdz_fb;

map tianch3.sbb_wtdz_zb, target sjcktb.sbb_wtdz_zb;

map tianch3.sbb_yhs, target sjcktb.sbb_yhs;

map tianch3.sbb_yyes_fb, target sjcktb.sbb_yyes_fb;

map tianch3.sbb_yyes_zb, target sjcktb.sbb_yyes_zb;

map tianch3.sbb_yys_fwy_fb, target sjcktb.sbb_yys_fwy_fb;

map tianch3.sbb_yys_fwy_jcxm_fb, target sjcktb.sbb_yys_fwy_jcxm_fb;

map tianch3.sbb_yys_jrbx_fb, target sjcktb.sbb_yys_jrbx_fb;

map tianch3.sbb_yys_jtysy_fb, target sjcktb.sbb_yys_jtysy_fb;

map tianch3.sbb_yys_jzy_fb, target sjcktb.sbb_yys_jzy_fb;

map tianch3.sbb_yys_qy_fb, target sjcktb.sbb_yys_qy_fb;

map tianch3.sbb_yys_whty_fb, target sjcktb.sbb_yys_whty_fb;

map tianch3.sbb_yys_xsbdc_fb, target sjcktb.sbb_yys_xsbdc_fb;

map tianch3.sbb_yys_ydjzy_fb, target sjcktb.sbb_yys_ydjzy_fb;

map tianch3.sbb_yys_yly_fb, target sjcktb.sbb_yys_yly_fb;

map tianch3.sbb_yys_yzdx_fb, target sjcktb.sbb_yys_yzdx_fb;

map tianch3.sbb_yys_zb, target sjcktb.sbb_yys_zb;

map tianch3.sbb_yys_zrwxzc_fb, target sjcktb.sbb_yys_zrwxzc_fb;

map tianch3.sbb_zdsy_fdcqybb, target sjcktb.sbb_zdsy_fdcqybb;

map tianch3.sbb_zdsy_gyqycpybb, target sjcktb.sbb_zdsy_gyqycpybb;

map tianch3.sbb_zdsy_qycwxxbb, target sjcktb.sbb_zdsy_qycwxxbb;

map tianch3.sbb_zdsy_qyssybb, target sjcktb.sbb_zdsy_qyssybb;

map tianch3.sbb_zdsy_qywjdc, target sjcktb.sbb_zdsy_qywjdc;

map tianch3.sbb_zhsf, target sjcktb.sbb_zhsf;

map tianch3.sbb_zys, target sjcktb.sbb_zys;

五、start源端程式，使用資料泵初始化載入

GGSCI>start mgr

GGSCI>start extract ext1

GGSCI>start extract dpext1

GGSCI>info all

確認程式全部running

初始化載入架構：

clip_image003

上圖中，顯示了初始化載入啟用了兩條同步路線：上面一條是真正的initial load，負責將源資料端的資料一次性傳送到目標資料庫；下面一條，其實就是普通的GoldenGate同步程式，負責抓取初始化載入時源端資料庫進行的線上資料變化。因為在實際應用中，往往需要在生產庫（源資料庫）不停機的狀態下，將資料載入到備用資料庫（目標資料庫）中並應用實時同步，在資料初始化的過程中，生產庫將繼續進行正常的事務操作，所以此時需要有抓取程式在初始化時開始將這些變化捕獲，以免資料丟失。

實際部署時需要注意正確的執行順序，大致可以分為以下幾步：

（1） 源端和目標端建立配置各個同步程式。

（2） 開啟源端同步抓取程式（圖上的Change Extract），開始捕獲變化。

（3） 開啟初始化程式（圖上的Initial-Load Extract），開始資料初始化載入。

（4） 等初始化載入結束，開啟目標端複製應用程式（圖上的Change Replicat），開始實時同步應用。

在目標端複製應用程式（圖上的Change Replicat）中，需要在引數檔案中配置HANDLECOLLISIONS引數，以避免重複應用第2和第3步之間的資料變化，因為這部分資料已經包含在初始化載入中傳到目標資料庫中了。

在這裡需要特別提醒的一個概念上的問題，GoldenGate的初始化同步不會也不需要去初始化target端的SCN號。

這裡的初始化載入，完全可以使用其他資料庫工具來實現，比如說exp/imp、SQL*Loader、RMAN複製資料庫等。一般情況下，儘量使用其他高效的資料庫傳輸工具來完成初始化載入，如果資料量大，建議不要用GoldenGate提供的初始化功能。

本專案使用資料泵進行初始化。

5.1 expdp匯出資料

源端先建立directory，並grant read,write，步驟略

源端獲取資料庫當前的SCN

SQL> select dbms_flashback.get_system_change_number from dual;

expdp xxxxx/xxxxx directory=dir_dump dumpfile=xxxx.dmp logfile=expdp20131212.log PARALLEL=2 tables=sbbtdzzsqs, sbb_ccs_zb, sbb_cl_normal_cbj, sbb_cl_normal_cbj_cjr, sbb_cl_ns_cbj, sbb_cl_ns_cbj_cjr, sbb_cl_y_cbj, sbb_cl_y_cbj_cjr, sbb_csjs, sbb_cstd, sbb_cwbb_bxgs_lrb, sbb_cwbb_bxgs_syzqybdb, sbb_cwbb_bxgs_xjllb, sbb_cwbb_bxgs_zcfzb, sbb_cwbb_qykjzd_lrb, sbb_cwbb_qykjzd_syzqyzjbdb, sbb_cwbb_qykjzd_xjllb, sbb_cwbb_qykjzd_zcfzb, sbb_cwbb_syyh_lrb, sbb_cwbb_syyh_syzqybdb, sbb_cwbb_syyh_xjllb, sbb_cwbb_syyh_zcfzb, sbb_cwbb_xqykjzzzcfzb, sbb_cwbb_xqy_kjzd_lrb, sbb_cwbb_xqy_kjzd_xjllb, sbb_cwbb_ybqy_lrb, sbb_cwbb_ybqy_syzqybdb, sbb_cwbb_ybqy_xjllb, sbb_cwbb_ybqy_zcfzb, sbb_cwbb_zqgs_lrb, sbb_cwbb_zqgs_syzqybdb, sbb_cwbb_zqgs_xjllb, sbb_cwbb_zqgs_zcfzb, sbb_dkdjccs_zb, sbb_dkdj_dsdj_bgb_fb, sbb_dkdj_dsdj_bgb_zb, sbb_dkdj_skbgb, sbb_dkdj_zqjyyhs_bgb, sbb_dsdj_ccs_jm_fb, sbb_dsdj_ccs_mx_fb, sbb_dsdj_ccs_ws_fb, sbb_dsdj_ccs_wws_fb, sbb_fcs, sbb_fspjgtjjj, sbb_ghjf, sbb_grsds_grdz_hhqy_nd, sbb_grsds_grdz_hhqy_nd_mxxx, sbb_grsds_grdz_hhqy_yj, sbb_grsds_grdz_hhqy_yj_mxxx, sbb_grsds_hz, sbb_grsds_qnycxjj, sbb_grsds_scjysd, sbb_grsds_scjysd_a, sbb_grsds_scjysd_b, sbb_grsds_zxsb_12w, sbb_grsds_zxsb_12w_mxxx, sbb_grsds_zxsb_nd, sbb_grsds_zxsb_nd_mxxx, sbb_grsds_zxsb_yf, sbb_grsds_zxsb_yf_mxxx, sbb_jyffj, sbb_qysds_2008_jd_a_fb01, sbb_qysds_2008_jd_a_zb, sbb_qysds_2008_jd_b, sbb_qysds_2012_jd_a_zb, sbb_qysds_2012_jd_b, sbb_qysds_2012_nd_b, sbb_qysds_nd_a_cbfymxb, sbb_qysds_nd_a_fqy_srmxb, sbb_qysds_nd_a_fqy_zcmxb, sbb_qysds_nd_a_gkxc_nstzb, sbb_qysds_nd_a_gyjz_nstzb, sbb_qysds_nd_a_jrqy_cbmxb, sbb_qysds_nd_a_jrqy_srmxb, sbb_qysds_nd_a_mbksmxb, sbb_qysds_nd_a_nstzxmmxb, sbb_qysds_nd_a_srmxb, sbb_qysds_nd_a_ssyhmxb, sbb_qysds_nd_a_tzsdmxb, sbb_qysds_nd_a_tzssbczl, sbb_qysds_nd_a_tzssbczl_bc, sbb_qysds_nd_a_ywsds_dmmxb, sbb_qysds_nd_a_zb, sbb_qysds_nd_a_zcjz_tzmxb, sbb_qysds_nd_a_zczj_tzmxb, sbb_qysds_nd_b, sbb_qysds_nd_b_ssyhmxb, sbb_sljsjj, sbb_tdzzs_one, sbb_whsyjsf, sbb_wtdz, sbb_wtdz_fb, sbb_wtdz_zb, sbb_yhs, sbb_yyes_fb, sbb_yyes_zb, sbb_yys_fwy_fb, sbb_yys_fwy_jcxm_fb, sbb_yys_jrbx_fb, sbb_yys_jtysy_fb, sbb_yys_jzy_fb, sbb_yys_qy_fb, sbb_yys_whty_fb, sbb_yys_xsbdc_fb, sbb_yys_ydjzy_fb, sbb_yys_yly_fb, sbb_yys_yzdx_fb, sbb_yys_zb, sbb_yys_zrwxzc_fb, sbb_zdsy_fdcqybb, sbb_zdsy_gyqycpybb, sbb_zdsy_qycwxxbb, sbb_zdsy_qyssybb, sbb_zdsy_qywjdc, sbb_zhsf, sbb_zys flashback_scn=

5.2 target端匯入資料

target端先建立directory，並grant read,write，把expdp匯出的資料copy進來，步驟略

impdp xxxx/xxxx directory=dir_dump dumpfile=xxxx.dmp logfile=impdp20131212.log

REMAP_SCHEMA=tianch3: sjcktb PARALLEL=2

如果這些表有外來鍵，在目標端檢查這些外來鍵並禁止它們

5.3 target端start replicat程式

GGSCI>start replicat rep1, aftercsn 13652544851176

注：13652544851176為5.1節源端查到的scn，必須加aftercsn，要特別注意

5.4 golden gate測試

源端進行一些dml操作，兩邊對比結果，注意同步時間，一般為1秒鐘即可同步。

5.5 turn off initial load error handling

初始化工作完成，初始化程式即自動停止，需要移除HANDLECOLLISIONS

GGSCI> SEND REPLICAT repq, NOHANDLECOLLISIONS

Remove initial load error handling from the parameter file

GGSCI> EDIT PARAMS rep1

Remove the HANDLECOLLISIONS parameter.

GGSCI>stop rep1

GGSCI>start rep1

GGSCI>info all

六、golden gate維護

1、啟動源端管理程式

GGSCI > start mgr // 啟動 manager程式

2、啟動所有程式

GGSCI > start ext * //啟動所有抽取程式

3、檢視程式狀態是否為Running（表示已經啟動）;

GGSCI > info ext * //檢視所有程式資訊

4、SCI > start rep * //啟動所有投遞程式

5、GGSCI>info extXX,showch //查詢extXX程式回滾檢查點

6、GGSCI > info all //查詢所有程式狀態

7、GGSCI > view report rep1 //檢視複製程式報告

8、GGSCI > view ggsevt //就是我們在goldengate目錄下看到的ggserr.log

……………………..

參考文件

《Oracle GoldenGate Oracle Installation and Setup Guide》

《Oracle GoldenGate Administrator's Guide》

《Oracle GoldenGate Reference Guide》

《Oracle GoldenGate Troubleshooting and Tuning Guide》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/642366/viewspace-1069910/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： 資料從Oracle向Mysql資料遷移例項
下一篇： MySQL引擎
Golodengate 實施步驟
請登入後發表評論 登入
全部評論
zzy020128

註冊時間：2009-03-06

  • 博文量
    22
  • 訪問量
    133981

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號：11010802021510

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章