ogg初始化数据+增量同步(表必须有主键或是唯一性索引)

同步先做增量通过,然后再做数据初始化,增量和数据初始化的复制进程都需要加上handlecollisions，下面以oracle同步到mysql为例

#####################先做增量同步############################

主库

抽取进程

GGSCI (host02) 15> EDIT PARAMS extep

添加如下内容:

 

EXTRACT extep
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate, PASSWORD goldengate
EXTTRAIL ./dirdat/ep
TABLE hxl.metric;

 

执行如下命令

GGSCI (host01) 2> ADD EXTRACT extep, TRANLOG, BEGIN NOW
EXTRACT added.

 

GGSCI (host01) 3> add EXTTRAIL ./dirdat/ep, EXTRACT extep, MEGABYTES 100
EXTTRAIL added.

启动抽取进程

 

GGSCI (host01) 4> START EXTRACT extep

 

 

配置pump(传递)进程

 

GGSCI (host01) 8>  EDIT PARAMS dpep

 

EXTRACT dpep
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST 192.168.1.135, MGRPORT 7809
RmtTrail ./dirdat/ep
TABLE hxl.metric;

 

GGSCI (host01) 8> ADD EXTRACT dpep, EXTTRAILSOURCE ./dirdat/ep
EXTRACT added.

 

GGSCI (host01) 10> add rmttrail ./dirdat/ep, EXTRACT dpep, MEGABYTES 100
RMTTRAIL added.

启动

GGSCI (host01) 11> START EXTRACT dpep

Sending START request to MANAGER ...
EXTRACT PORA_1 starting

 

从库

GGSCI > add replicat repep, exttrail ./dirdat/ep,checkpointtable db_oggadmin.checkpoint
REPLICAT added.

GGSCI (host02) 3>EDIT PARAM repep

 

replicat repep
--sourcedefs /u01/goldengate19c_for_mysql/dirdef/defgen.prm
SETENV (MYSQL_UNIX_PORT=/opt/mysql5727/mysql.sock)
targetdb db_hxl@localhost:3306 userid goldengate, password mysql
reperror default, discard
gettruncates
assumetargetdefs
handlecollisions
discardfile ./dirrpt/repep.dsc,APPEND,MEGABYTES 1000
MAP hxl.metric,TARGET db_hxl.metric;

 

启动

GGSCI (host02) 4> START REPLICAT repep

 

#####################初始化同步(数据落地方式)############################

初始化同步会包含增量之前的数据以及增量同步的数据，这样的话同步是有重复的，加上handlecollisions参数的目的是遇到重读的不处理

主库

1.编辑初始化抽取进程参数

GGSCI>edit params ext_init

EXTRACT ext_init
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate, PASSWORD goldengate
RMTHOST 192.168.1.135, MGRPORT 7809
rmtfile ./dirdat/ei, maxfiles 999999, megabytes 1500, append
TABLE hxl.metric;

 

2.添加抽取进程并启动

GGSCI>add extract ext_init, SOURCEISTABLE

 GGSCI>start ext_init

 

从库

 

1.创建初始化复制进程

 

GGSCI>edit params rep_init

 

replicat rep_init
--sourcedefs /u01/goldengate19c_for_mysql/dirdef/defgen.prm
SETENV (MYSQL_UNIX_PORT=/opt/mysql5727/mysql.sock)
targetdb db_hxl@localhost:3306 userid goldengate, password mysql
reperror default, discard
handlecollisions
discardfile ./dirrpt/rep_init.dsc,APPEND,MEGABYTES 1000
MAP hxl.metric,TARGET db_hxl.metric;

 

 

2.添加checkpoint

GGSCI > dblogin sourcedb db_hxl@192.168.1.135:3306 userid goldengate password mysql

Successfully logged into database.

 

GGSCI (host135 DBLOGIN as goldengate) 47> add checkpointtable db_oggadmin.checkpoint

 

Successfully created checkpoint table db_oggadmin.checkpoint.

 

加入到全局文件

GGSCI > edit params ./GLOBALS

checkpointtable db_oggadmin.checkpoint

 

 3.添加复制进程

GGSCI > add replicat rep_init, exttrail ./dirdat/ei,checkpointtable db_oggadmin.checkpoint

REPLICAT added.

 

4.启动

GGSCI>start rep_init

  

检查数据应用完成后需要手工停掉复制进程并删除

 

GGSCI>stop rep_init

GGSCI>delete replicat rep_init