ORACLE 19C PDB FOR MYSQL 5.7 部署ogg

标签：5.7 C# grant -- GGSCI MYSQL ORACLE ogg GOLDENGATE

一、软件配置

角色	数据库/软件版本	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.com

安装 略..很简单

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;

 

1）生成定义文件

cd /home/oracle/ogg/

 

./defgen paramfile dirprm/mysql.prm

 

3)拷贝生成的oracle定义文件mysql.def到mysql目标端目录/home/db/mysql/ogg/dirprm下

注意：搭建过程完成

 

标签：5.7,C#,grant,--,GGSCI,MYSQL,ORACLE,ogg,GOLDENGATE
From： https://www.cnblogs.com/dbahrz/p/17103621.html