ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

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

Golodengate 实施步骤

Oracle 作者：zzy020128 时间：2014-01-13 10:33:49 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软件安装

下安装

在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

创建目录

使用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进程配置

在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

端添加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权限的。

增加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提供的初始化功能。

本项目使用数据泵进行初始化。

导出数据

源端先创建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博客 ” ，链接：https://blog.itpub.net/642366/viewspace-1069910/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 数据从Oracle向Mysql数据迁移实例
下一篇： MySQL引擎
请登录后发表评论 登录
全部评论
zzy020128

注册时间：2009-03-06

  • 博文量
    22
  • 访问量
    142831

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593