oracle 2 mysql使用oracle goldengate（ogg）工具
oracle 2 mysql使用oracle goldengateogg工具在前面的多期文章中提到了使用ogg工具同步数据的场景。在这篇文章中将测试Oracle同步数据到MySQL。话不多说下面开始正文。环境说明源端Oracle 11.2.0.4目标端MySQL 8.0.44Oracle goldengate19.1.0.4前期遇到的问题主要有1.初始化表结构的过程中使用navicat工具转化成MySQL格式的DDL语句Oracle的blob字段转化成的是clob字段但是MySQL没有这个数据类型需要手工修改成text或者longtext。2.Oracle goldengate 21.3版本在抽取进程启动的时候提示如下信息换成Oracle goldengate 19.1版本后启动正常。2026-07-17T07:10:49.6320800 WARNING OGG-02064 Oracle GoldenGate Capture for Oracle, ecfpsng1.prm: Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support. 2026-07-17T07:10:49.7130800 ERROR OGG-02022 Oracle GoldenGate Capture for Oracle, ecfpsng1.prm: Logmining server does not exist on this Oracle database. 2026-07-17T07:10:49.7130800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ecfpsng1.prm: PROCESS ABENDING.3.初始化数据过程中目标端一直提示主键冲突但是其实出现错误的时候查看目标端表是没有写入数据的有时候又会插入重复数据最后是把目标端的相关表的主键和外键全部删掉后恢复正常2026-07-16 22:36:57 WARNING OGG-01004 Canceled grouped transaction on table use1.custab. Database error 1062, ([SQL error 1062]Duplicate entry HH for key custab.PRI MARY ).4.在用navicat转化DDL语句的时候发现所有的主键都会创建一个唯一键/*** --配置全局参数 edit param ./GLOBALS GGSCHEMA ogg --创建用户凭证别名 add credentialstore --创建到源端的登录别名 --登录源端主库别名 alter credentialstore add user oggorcl148, password BYwl2021 alias orcl148 --登录源端备库别名 alter credentialstore add user oggorcl1593, password BYwl2021 alias orcl1593 --测试验证 dblogin useridalias orcl148源端MGR进程port 7000 dynamicportlist 7001-7090 autorestart extract *, waitminutes 2, resetminutes 5 PURGEOLDEXTRACTS ./dirdat/e*, USECHECKPOINTS,MINKEEPDAYS 7 ACCESSRULE, PROG *, IPADDR 10.10.*, ALLOW源端配置抽取进程--添加trandata add trandata maintain.tabt --源端配置抽取进程 add extract etest,tranlog,begin now add exttrail /data/ogg213/dirdat/et,extract etest edit param etest EXTRACT ETEST SETENV (NLS_LANGAMERICAN_AMERICA.ZHS16GBK) SETENV (ORACLE_HOME/data/oradb/app/oracle/product/11.2.0.3/db_1) SETENV (ORACLE_SIDorcl) userid ogg10.10.x.x:1521/orcl , password pwd1111 discardfile /data/ogg213/dirrpt/ETEST.dsc,append,megabytes 200 exttrail /data/ogg213/dirdat/et statoptions reportfetch reportcount every 1 minutes,rate warnlongtrans 1H,checkinterval 5m TABLE maintain.tabt;源端配置投递进程add extract ptest,exttrailsource /data/ogg213/dirdat/et add rmttrail /data/ogg213/dirdat/et,extract ptest #### 增加配置将抽取进程抽取的文件数据传输到远程对应目录下 #### 注意rmttrail参数指定的是目标端的存放目录需要目标端存在该目录路径 #### 创建传输进程配置文件 edit params ptest extract ptest SETENV (NLS_LANGAMERICAN_AMERICA.ZHS16GBK) SETENV (ORACLE_HOME/data/oradb/app/oracle/product/11.2.0.3/db_1) SETENV (ORACLE_SIDorcl) userid ogg10.10.x.x:1521/orcl , password pwd1111 RMTHOST 10.12.x.x,MGRPORT 7000 RMTTRAIL /data/ogg213/dirdat/et discardfile /data/ogg213/dirrpt/ptest.dsc,append,megabytes 200 TABLE maintain.tabt;配置异构文件--异构mapping文件defgen生成 edit param defgentab defsfile /data/ogg213/dirdef/defgentab.prm userid ogg10.10.x.x.:1521/orcl , password pwd1111 TABLE maintain.tabt; /data/ogg213/defgen paramfile /data/ogg213/dirprm/defgentab.prm [oracleoracle01 ogg213]$ /data/ogg213/defgen paramfile /data/ogg213/dirprm/defgentab.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054 Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 14:27:47 Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. Starting at 2026-07-08 22:36:14 *********************************************************************** Operating System Version: Linux Version #1 SMP Mon Oct 19 16:18:59 UTC 2020, Release 3.10.0-1160.el7.x86_64 Node: oracle01 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: 108162 *********************************************************************** ** Running with the following parameters ** *********************************************************************** defsfile /data/ogg213/dirdef/defgentab.prm userid ogg10.120.14.8:1521/orcl , password *** TABLE maintain.tabt; Retrieving definition for MAINTAIN.TABT. Definitions generated for 1 table in /data/ogg213/dirdef/defgentab.prm. [oracleoracle01 ogg213]$ 把生成的文件传送到目标端目标端MGRport 7000 dynamicportlist 7001-7090 autorestart extract *, waitminutes 2, resetminutes 5 PURGEOLDEXTRACTS ./dirdat/e*, USECHECKPOINTS,MINKEEPDAYS 7 ACCESSRULE, PROG *, IPADDR 10.10.*, ALLOW目标端应用进程--目标端配置应用进程 --创建检查点 dblogin sourcedb testdblocalhost:23306, userid ogg, password pwd1111 add checkpointtable testdb.ggs_checkpoint edit param ./GLOBALS checkpointtable testdb.ggs_checkpoint dblogin sourcedb testdb10.12.x.x:23306, userid ogg, password pwd1111 add replicat rtest,exttrail /data/ogg213/dirdat/et,checkpointtable testdb.ggs_checkpoint #### 增加/编辑回放进程配置文件 edit params rtest replicat rtest targetdb testdb10.12.x.x:23306, userid ogg, password pwd1111 sourcedefs /data/ogg213/dirdef/defgentab.prm discardfile /data/ogg213/dirrpt/rtest.dsc,append,megabytes 512 HANDLECOLLISIONS MAP maintain.tabt,target testdb.tabt;源端初始化进程配置--添加初始化进程 add extract einit1 ,sourceistable edit params einit1 extract einit1 SETENV (NLS_LANGAMERICAN_AMERICA.ZHS16GBK) SETENV (ORACLE_HOME/data/oradb/app/oracle/product/11.2.0.3/db_1) SETENV (ORACLE_SIDorcl) userid ogg10.10.x.x.:1521/orcl , password pwd1111 RMTHOST 10.120..x.x,MGRPORT 7000 RMTTASK replicat,GROUP rinit1 table maintain.tabt;目标端初始化进程---初始化应用进程 add replicat rinit1 ,specialrun edit params rinit1 replicat rinit1 sourcedefs /data/ogg213/dirdef/defgentab.prm targetdb testdb10.120..x.x:23306, userid ogg, password pwd111 DISCARDFILE /data/ogg213/dirrpt/rinit1.dsc,purge map maintain.tabt, target testdb.tabt;
