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 被再次启用.
最后验证数据: 初始化数据, 变更数据等
来源: http://blog.51cto.com/3048449/2133388