source源端 oracle 11.2.0.4 ogg12c
ip:10.200.3.193 系统centos 7.1

1. 创建source表和一些初始化数据
su - oracle
sqlplus / as sysdba
SQL>alter database add supplemental log data;
SQL>select supplemental_log_data_min from v$database;

SQL>alter database forcelogging;

SQL> select supplemental_log_data_min from v$database;
NO

SQL> alter database add supplemental log data;

Database altered.
SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;
YES
SQL> create tablespace ggs;
Tablespace created.
SQL> create user ggs identified by ggs default tablespace ggs;
User created.
SQL> grant connect,resource,dba to ggs;
SQL> alter system set undo_retention=86400;
System altered.

SQL> grant flashback any table to ggs;

先配置参数进行静默安装---参考centos7.1之goldengate12c的安装

2.在source源端上创建Manager参数文件并指定端口和一些需要的参数选项:

cd /u01/app/ggs

./ggsci
创建相关目录:GGSCI (testtech3-193) 1> create subdirs

配置mgr
view params mgr

port 7809
dynamicportlist 7810-7820,7830

启动start mgr

GGSCI (testtech3-193) 1> dblogin userid ggs password ggs
Successfully logged into database.
GGSCI (testtech3-193) 1> REGISTER EXTRACT ext1 DATABASE
GGSCI (testtech3-193) 2> add trandata workflow.act_ru_execution

3.编辑defgen文件
vi /u01/app/ggs/dirprm/defgen.prm

defsfile /u01/app/ggs/dirdef/table_del,purge
userid ggs, password ggs
table WORKFLOW.act_ru_execution;

cd /u01/app/ggs

defgen paramfile /u01/app/ggs/dirprm/defgen.prm

cd /app/ggs/dirdef/
把文件复制到目标端的/app/ggs/dirdef/

4.在source源端上执行以下命令添加一个Extract进程叫做init进行数据初始化:

cd /u01/app/ggs
GGSCI (testtech3-193)>./ggsci
GGSCI (testtech3-193)>add extract init,sourceistable
GGSCI (testtech3-193)>info extract *, task
GGSCI (testtech3-193)>edit params init

extract init
userid ggs password ggs
rmthost 10.200.3.178,mgrport 8809
rmttask replicat,group repinit
table WORKFLOW.act_ru_execution;

目标端target mysql5.6 ogg 12c

ip:10.200.3.178 系统centos 7.1

1.创建目标端target数据库和相应的表
mysql -uroot -p
创建用于OGG使用登陆的数据库,例如需要存放checkpoint table

mysql> create database ogg;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on . to ogg@10.200.3.178 identified by 'ggs';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
2.创建目标端target应用进程
GGSCI (techtest3-178)>dblogin sourcedb ogg@10.200.3.178:3306 userid ogg password ggs
GGSCI (techtest3-178)>add checkpointtable ogg.ck_table

GGSCI (techtest3-178)>add replicat repdel,exttrail /u01/app/ggs/dirdat/dl,checkpointtable ogg.ck_table
GGSCI (techtest3-178)>edit params repdel

replicat repdel
targetdb ogg@10.200.3.178:3306 userid ogg password ggs
handlecollisions
SOURCECHARSET zhs16gbk ----源端字符集,一定要设置,特别是源端和目标端字符集不一致的情况,此参数必设置。
sourcedefs /u01/app/ggs/dirdef/table_def
discardfile /u01/app/ggs/dirrpt/repdel.dsc,purge
map WORKFLOW.act_ru_execution, target activiti.act_ru_execution;

由于是异构环境,所以要指定sourcedefs参数,使用source数据库的数据定义文件。
handlecollisions参数来处理初始化加载的结果与实时改变同步捕获数据的冲突。

目标端:view params mgr

port 8809
DYNAMICPORTLIST 8840-8850
ACCE×××ULE, PROG REPLICAT, IPADDR 10.200.3.139 ALLOW

3.目标端target创建初始化加载交付参数文件

GGSCI (techtest3-178)>add replicat repinit,specialrun

GGSCI (techtest3-178)>edit params repinit

replicat repinit

targetdb ogg@10.200.3.178:3306 userid ogg password ggs

sourcedefs /u01/app/ggs/dirdef/table_def

discardfile /u01/app/ggs/dirrpt/repinit.dsc,purge

sqlexec "SET FOREIGN_KEY_CHECKS=0"

map WORKFLOW.act_ru_execution, target activiti.act_ru_execution;

4.在source源端添加Extract组
GGSCI (testtech3-193) > ADD EXTRACT extdel,TRANLOG, BEGIN NOW
GGSCI (testtech3-193) >edit params extdel
EXTRACT extdel
userid ggs, password ggs
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extdel.dsc, APPEND, MEGABYTES 1000
WARNLONGTRANS 2h, CHECKINTERVAL 3m
EXTTRAIL ./dirdat/dl
TRANLOGOPTIONS CONVERTUCS2CLOBS
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
table WORKFLOW.act_ru_execution;
GGSCI (testtech3-193) >add exttrail ./dirdat/dl, extract extdel, megabytes 500

GGSCI (testtech3-193) >ADD EXTRACT dpedel, EXTTRAILSOURCE ./dirdat/dl

GGSCI (testtech3-193) >edit params dpedel

EXTRACT dpedel
RMTHOST 10.200.3.178, MGRPORT 8809, compress
PASSTHRU
numfiles 50000
RMTTRAIL ./dirdat/dl
DYNAMICRESOLUTION
table WORKFLOW.act_ru_execution;

GGSCI (testtech3-193) >ADD RMTTRAIL ./dirdat/dl, EXTRACT dpedel, megabytes 500

以下是启动抽取和投递进程,由于oracle到mysql初始化无法做到像oracle一样的利用scn等。
大致思路是,先进程抽取数据,然后进行初始化操作。等初始化完了,启动应用进程会有冲突,处理冲突,解决问题。最后做数据对比。
GGSCI (testtech3-193) >start extdel
GGSCI (testtech3-193) >start dpedel

5.源端source启动初始化加载数据
在source源端启动之前配置的初始化加载提取任务init:
GGSCI (testtech3-193) >start init

在目标端target端看初始化数据加载复制进程的报告确认加载成功:

GGSCI (techtest3-178)> view report repinit

init初始化结束以后,进程会自动重启

6.最后目标端target启动repdel

确认初始化数据加载完成后关闭改变同步Replicat进程repdel的handlecollisions。

GGSCI (techtest3-178) 1> send repdel,nohandlecollisions

并将repdel参数文件中的handlecollisions参数去除,防止repdel下次启动时handlecollisions被再次启用。

最后验证数据:初始化数据,变更数据等