ITPub博客

首页 > 数据库 > Oracle > Golodengate 实施步骤

Golodengate 实施步骤

Oracle 作者:lpwebnet 时间:2014-02-08 11:16:36 0 删除 编辑

一、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/17172228/viewspace-1077969/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论