OGG工具实现MySQL同步到Oracle数据库

OGG工具实现MySQL同步到Oracle数据库

内部数据库环境类型比骄傲多，有MySQL ，Oracle，SQL server等数据库类型，有时候出现异构数据库之间的同步就比较平凡的事情了。OGG工具可以实现异构数据库之前的同步，但是只能支持DML，不支持DDL，还是比较麻烦的。只要源端对同步的表进行了DDL操作，同步进程就ABEND的了。

所以针对这种情况，可以对源端同步的表做一个表结构更新的监控。当表结构出现变化的时候即时发出告警，即时处理，避免数据同步延迟太长时间影响业务。
表字段变更监控可参考如下文章：
https://blog.csdn.net/bing_yuan/article/details/129244757

下面开始正题：
环境介绍：

源端	目标端
MySQL 5.7.35	Oracle11.2.0.4
同步的库：testdb	目标端账号：testuser
同步的表：test_log	目标表：test_log
OGG版本：OGG18	OGG版本：OGG18

ogg的安装比较简单，此处忽略安装部分。

1.源端MGR进程配置

GGSCI (8cf308baef7d) 2> view param mgr

port 7810
dynamicportlist 7810-8011
autorestart extract *, waitminutes 2, resetminutes 5
PURGEOLDEXTRACTS ./dirdat/e*, USECHECKPOINTS,MINKEEPDAYS 7
accessrule, prog *, ipaddr 172.22.*, allow

2.源端抽取进程配置

GGSCI (8cf308baef7d) 3> view param elog

extract elog
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/master-bin.index
sourcedb testdb@localhost:3306, userid root, password q1w2e3r4
exttrail ./dirdat/e3
gettruncates
table testdb.test_log;


GGSCI (6f1fffa70457) 11> add extract elog, tranlog, begin now
EXTRACT added.

GGSCI (6f1fffa70457) 12> add exttrail ./dirdat/e3, extract elog
EXTTRAIL added.

3.源端投递进程配置

GGSCI (8cf308baef7d) 4> view param plog

extract plog
rmthost 172.22.11.xx, mgrport 7810
rmttrail /u01/app/ogg18/dirdat/e3
passthru
gettruncates
table testdb.test_log;

GGSCI (6f1fffa70457) 16> add extract plog, exttrailsource ./dirdat/e3
EXTRACT added.


GGSCI (6f1fffa70457) 17> add rmttrail /u01/app/ogg18/dirdat/e3,extract plog
RMTTRAIL added.

4.源端编辑异构文件

GGSCI (6f1fffa70457) 19> view param defgen

defsfile ./dirdef/defgen.prm
sourcedb testdb@localhost:3306, userid root, password q1w2e3r4
table testdb.test_log;

root@6f1fffa70457:/ogg18# ./defgen paramfile dirprm/defgen.prm 
***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
      Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432
 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 28 2018 19:46:45
 
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2022-06-06 15:42:55
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Oct 19 11:24:13 EDT 2016, Release 3.10.0-514.el7.x86_64
Node: 6f1fffa70457
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 154

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirdef/defgen.prm
sourcedb testdb@localhost:3306, userid root, password ***
table cargo.*;
Expanding wildcard table specification testdb.*:
......
Definitions generated for 113 tables in ./dirdef/defgen.prm.

把文件传送到目标端

scp ./dirdef/defgen.prm oracle@192.168.56.10:/ogg18/dirdef

5.源端编辑数据初始化进程

GGSCI (8cf308baef7d) 2> view param einit_2

extract einit_2
sourcedb testdb@localhost:3306, userid root, password q1w2e3r4
rmthost 172.22.11.xx, mgrport 7810
rmttask replicat, group rinit_2
table testdb.test_log;

GGSCI (6f1fffa70457) 6> add extract einit_2, sourceistable
EXTRACT added.

6.目标端MGR进程配置

GGSCI (cdc01) 2> view param mgr

port 7810
dynamicportlist 7810-7815
autorestart extract *, waitminutes 2, resetminutes 5
PURGEOLDEXTRACTS ./dirdat/e*, USECHECKPOINTS,MINKEEPDAYS 7
PURGEOLDEXTRACTS ./dirdat/c*, USECHECKPOINTS,MINKEEPDAYS 7
accessrule, prog *, ipaddr 172.22.*, allow
accessrule, prog *, ipaddr 10.1.4.*, allow

7.目标端应用进程配置

GGSCI (cdc01) 3> view param rlog

replicat rlog
sourcedefs ./dirdef/defgenlog.prm
userid ogg@cdc,password pwd
reperror default, discard
NODISCARDFILE
gettruncates
map testdb.test_log, target testuser.test_log;

GGSCI (cdc01 as ogg@cdc) 7> add checkpointtable ogg.checkpoint2

GGSCI (cdc01 as ogg@cdc) 8> add replicat rlog,exttrail ./dirdat/e3,checkpointtable ogg.checkpoint2

8.目标端初始化应用进程配置

GGSCI (cdc01) 4> view param rinit_2

replicat rinit_2
userid ogg@cdc, password pwd
sourcedefs ./dirdef/defgenlog.prm
discardfile ./dirrpt/rinit_2.dsc, purge
map testdb.test_log, target testuser.test_log;


GGSCI (cdc01) 5> add replicat rinit_1,specialrun

至此参数已经全部配置完成。

9.源端启动初始化进程

start einit_2

等待初始化完成之后分别在源端和目标端启动抽取进程和应用进程即可。

源端：
start elog
start plog

目标端：
start rlog

10.查看状态

源端：
GGSCI (8cf308baef7d) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     ELOG        00:00:02      00:00:01    
EXTRACT     RUNNING     PLOG        00:00:00      00:00:06    

目标端：
GGSCI (cdc01) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                            
REPLICAT    RUNNING     RLOG        00:00:00      00:00:05    

完成！！！