ORACLE19CPDBFORMYSQL5.7部署ogg

一、--软件配置 角色        数据库/软件版本     OGG版本                           IP ----------  -----------------   -------------------------------   ----------- 源端服务器  Oracle Datbase 19   Oracle C##GOLDENGATE 19.1.0.0.4   10.10.10.32 目标服务器  MYSQL5.7            19_ggs_Linux_x64_MySQL_64bit      10.10.10.33

二、--源端配置 1、--OGG初始化 --源端oracle数据库操作 1)    打开minimal supplemental logging和force logging SQL> alter database add supplemental log data; SQL> alter database force logging; SQL> alter system set ENABLE_C##GOLDENGATE_REPLICATION=true scope=both;

--修改 streams_pool_size参数，需要分别修改各个实例，不能用sid='*' SQL> alter system set streams_pool_size=512M scope=both sid='instance1'; SQL> alter system set streams_pool_size=512M scope=both sid='instance2';

注意:由于是12c以上oracle数据库 必须使用集成模式必须开启streams

2).--创建C##GOLDENGATE用户 create user C##C##GOLDENGATE identified by "xxxxxxxx"   default tablespace USERS   temporary tablespace TEMP   profile DEFAULT;

-- Grant/Revoke role privileges grant dba to C##GOLDENGATE; grant resource to C##GOLDENGATE;

-- Grant/Revoke system privileges grant alter any index to C##GOLDENGATE; grant alter any table to C##GOLDENGATE; grant alter session to C##GOLDENGATE; grant create any index to C##GOLDENGATE; grant create any sequence to C##GOLDENGATE; grant create any table to C##GOLDENGATE; grant create session to C##GOLDENGATE; grant delete any table to C##GOLDENGATE; grant dequeue any queue to C##GOLDENGATE; grant drop any sequence to C##GOLDENGATE; grant drop any table to C##GOLDENGATE; grant flashback any table to C##GOLDENGATE; grant insert any table to C##GOLDENGATE; grant select any dictionary to C##GOLDENGATE; grant select any table to C##GOLDENGATE; grant select any transaction to C##GOLDENGATE; grant unlimited tablespace to C##GOLDENGATE; grant update any table to C##GOLDENGATE;

exec dbms_goldengate_auth.grant_admin_privilege(‘C##GOLDENGATE’,container=>’all’)   2、--源端ogg操作（10.10.10.32） ogg安装自行安装

--下载地址 http://edelivery.oracle 安装 略..很简单

oracle@vm01:/home/oracle/ogg$ ./ggsci

Oracle C##GOLDENGATE Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29 Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (vm01) 1> create subdirs   3、--创建manager参数文件（10.10.10.32）

GGSCI> edit param mgr

--内容如下: port 7809 AUTOSTART EXTRACT * autorestart er *, waitminutes 3, retries 20 PURGEOLDEXTRACTS /home/oracle/ogg/dirdat/*,usecheckpoints, minkeepdays 3 Lagcriticalminutes 30 lagreportminutes 5 ACCESSRULE, PROG *, IPADDR 10.10.*.*, ALLOW

注意:此处选择使用数据库检查点模式 端口也可以做成DYNAMICPORTLIST 7820-7830, 7833, 7835

GGSCI> start mgr

/*/* 4、--添加抽取进程（10.10.10.32） GGSCI>dblogin userid C##GOLDENGATE@pdb, password "xxxxxxxx" GGSCI>register extract extsxhx1 database container(pdbname) GGSCI>add extract extsxhx1,integrated tranlog, begin now GGSCI>add EXTTRAIL /home/oracle/ogg/dirdat/sx, extract extsxhx1,MEGABYTES 100 GGSCI>add schematrandata slisbase

5、--创建抽取进程参数文件（10.10.10.32） GGSCI (vm01) 1> edit param extsxhx1

extract extsxhx1 setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") userid C##goldengate@10.10.10.32/orc , password "goldengate" TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100) exttrail /home/oracle/ogg/dirdat/sx discardfile /home/oracle/ogg/dirrpt/extsxhx1.dsc,append discardrollover at 08:00 --dynamicresolution GETTRUNCATES LOGALLSUPCOLS GETUPDATEBEFORES NOCOMPRESSDELETES NOCOMPRESSUPDATES ddl include mapped objtype 'TABLE',include mapped objtype 'INDEX' ddloptions addtrandata, report ddloptions report statoptions reportfetch reportrollover at 08:00 SOURCECATALOG PDB table slisbase.*;

注意:SOURCECATALOG参数很重要要指定具体pdbname，--dynamicresolution我把这个参数注释了应该默认就是这个

6、编辑投递进程参数 GGSCI> edit params dpsxbd6  extract dpsxbd6 setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") userid C##goldengate@10.10.10.32/orc , password "goldengate" passthru rmthost 10.10.10.32,mgrport 7810,compress rmttrail /home/db/mysql/ogg/dirdat/sx --dynamicresolution numfiles 3000 SOURCECATALOG PDB table slisbase.hrz; --table slisdata.lacommision ; --table slisdata.lccont ; --table slisdata.lcpol ; --table slisdata.lppol ; --table slisdata.lccontstate ;

GGSCI> start dpsxbd6 GGSCI> info dpsxbd6

三、--目标端篇配置 1、--mysql用户安装ogg 解压就行免安装 自行百度

2、--OGG初始化(10.10.10.33) [mysql@vm01 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for MySQL Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32 Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI> create subdirs

3、--创建manager 参数文件

GGSCI> edit param mgr

内容如下: port 7810 AUTORESTART REPLICAT *,RETRIES 5,WAITMINUTES 3 PURGEOLDEXTRACTS /home/db/mysql/ogg/dirdat/*,usecheckpoints, minkeepdays 3 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45

GGSCI> start mgr GGSCI> info mgr

/*/* 4、--创建goldengate 数据库 mysql>create database goldengate;

注意:这里主要是为了检查点服务的

5、--配置checkpoint GGSCI> dblogin sourcedb sndb@localhost:3306, userid root,password xxxxxxxx GGSCI> add checkpointtable goldengate.checkpoint

6、--编辑GLOBALS参数 EDIT PARAMS ./GLOBALS 添加如下内容: CHECKPOINT TABLE goldengate.checkpoint

注意:我这里没有配置，启动时候报错了告诉我已经有检查点不用在这里添加

7、--创建 replicat进程 GGSCI>add replicat repsx1,exttrail /home/db/mysql/ogg/dirdat/sx,checkpointtable goldengate.checkpoint GGSCI>edit params repsx1

内容如下: REPLICAT repsx1 targetdb sndb@10.10.10.32:3306 userid root password "oracle" sourcedefs /home/db/mysql/ogg/dirprm/mysql.def --reperror 1 discard --reperror 1403 discard --reperror 1062 discard discardfile /home/db/mysql/ogg/dirrpt/repsx1.dsc,megabytes 100 SOURCECATALOG PDB map slisbase.hrz,target sndb.hrz; --map slisdata.lacommision ,target sndb.slisdata_lacommision_rt ; --map slisdata.lccont ,target sndb.slisdata_lccont_rt ; --map slisdata.lcpol ,target sndb.slisdata_lcpol_rt ; --map slisdata.lppol ,target sndb.slisdata_lppol_rt ; --map slisdata.lccontstate ,target sndb.slisdata_lccontstate_rt ;

repsx1进程启动后，确认表goldengate.checkpoint中已正确记录数据.

8、--定义文件生成步骤 1）源端oracle数据库编辑定义文件所需参数文件 cd /home/oracle/ogg/dirprm

vi mysql.prm

写入如下内容: defsfile ./dirdef/mysql.def purge userid C##goldengate@10.10.10.32/pdb , password "goldengate" --defsfile /home/oracle/ogg/dirdef/mysql.def FORMAT RELEASE 12.3 TABLE SLISDATA.hrz;

2）生成定义文件 cd /home/oracle/ogg/ ./defgen paramfile dirprm/mysql.prm

3)拷贝生成的oracle定义文件mysql.def到mysql目标端目录/home/db/mysql/ogg/dirprm下 注意:搭建过程完成