异构同步——mysql-oracle ogg同步

最新推荐文章于 2024-08-22 17:25:34 发布

liuzhilongDBA

最新推荐文章于 2024-08-22 17:25:34 发布

阅读量4.4k

点赞数 1

分类专栏： OGG 文章标签： ogg 异构同步 mysql oracle

无版权，随便玩儿

本文链接：https://blog.csdn.net/qq_40687433/article/details/88821928

版权

OGG 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本篇使用ogg同步mysql表到oracle数据库，mysql为源端，oracle为目标端。

处于测试目的，表结构手动创建的（后面的会研究sql developer）

使用intial load初始化表数据

实验环境	软件版本
操作系统	Rhel 7.3
源端	Mysql 8.0
目标端	Oracle 18c
ogg	18.1.0.0.0

1.环境准备（mysql和oracle数据库安装略过）

mysql -uroot -poracle

chown mysql:mysql 181000_ggs_Linux_x64_MySQL_64bit.zip
chown 777 181000_ggs_Linux_x64_MySQL_64bit.zip
tar -xf ggs_Linux_x64_MySQL_64bit.tar
rm ggs_Linux_x64_MySQL_64bit.tar

./ggsci
GGSCI (cs-db) 1> create subdirs

环境变量：
export OGG_HOME=/oracle/soft/myogg
path后加:$OGG_HOME

2.mysql源端数据库配置

2.1.检查是否开启binlog

mysql> show binary logs;
mysql> show variables like '%log_bin%';
没有则需要开启binlog，略

2.2.创建mysql数据库用户并授权

mysql> CREATE USER 'ggs'@'%' IDENTIFIED BY "oracle";
mysql> ALTER USER 'ggs'@'%' IDENTIFIED BY 'oracle' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'ggs'@'%' IDENTIFIED WITH mysql_native_password BY 'oracle';
mysql> GRANT all ON mydb.* TO 'ggs';
mysql> flush privileges;

2.3.测试登陆

[mysql@cs-db ~] mysql -uggs -p"oracle" -h 133.37.126.38 -P 3306
GGSCI (cs-db) 1> dblogin SOURCEDB mydb@133.37.126.38:3306, USERID ggs, PASSWORD "oracle"

3.源端ogg配置

3.1.mgr进程：
edit param mgr

PORT 7811
DYNAMICPORTLIST 7840-7908
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3,,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 15
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

3.2.抽取进程：

EXTRACT extmy
SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8MB4")
sourcedb mydb@133.37.126.38:3306,userid ggs, password "oracle"
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE ./dirrpt/mydb.dsc, APPEND, MEGABYTES 1024
tranlogoptions altlogdest "/oracle/soft/my_data/binlog.index"
EXTTRAIL ./dirdat/my
table mydb.t1;
table mydb.t2;

添加抽取进程
ADD EXTRACT extmy, tranlog, begin now
ADD EXTTRAIL ./dirdat/my, EXTRACT extmy, megabytes 100

3.3 投递进程
edit param dpmy

EXTRACT dpmy
RMTHOST 133.37.126.38, MGRPORT 7810,COMPRESS
RMTTRAIL /oracle/soft/ogg18/dirdat/fk
PASSTHRU
NUMFILES 5000
TABLE mydb.*;

添加投递进程
ADD EXTRACT dpmy, EXTTRAILSOURCE ./dirdat/my
ADD RMTTRAIL /oracle/soft/ogg18/dirdat/fk, EXTRACT dpmy, MEGABYTES 100

3.4 defgen --其实不需要
defgen用于表结构不一致的同步，defgen文件记录了表的layouts，被collector和replicat进程使用。
不要更改defgen txt文件

edit param defgen

defsfile /oracle/soft/myogg/dirdef/defmydb.p
SOURCEDB mydb@133.37.126.38:3306, USERID ggs, PASSWORD "oracle"
TABLE mydb.*;

defgen是操作系统命令，注意目录或者文件名
defgen paramfile /oracle/soft/myogg/dirprm/defgen.prm
然后将defmydb.p拷贝到目标端,repinit ,repmy进程都要使用
cp /oracle/soft/myogg/dirdef/defmydb.p /oracle/soft/ogg/dirdef/.
chown ora18:oinstall /oracle/soft/ogg/dirdef/defmydb.p
chmod 755 /oracle/soft/ogg/dirdef/defmydb.p

4.数据初始化

4.1 目标端建表
show tables
desc tab
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
目标端建表
create table lzl.t1(id number(33),name varchar2(30));
create table lzl.t2(id number(33),name varchar2(30));
4.2.源端extinit
EXTRACT extinimy
SOURCEDB mydb@133.37.126.38:3306, USERID ggs, PASSWORD "oracle"
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE ./dirrpt/extinimy.dsc, APPEND, MEGABYTES 1024
tranlogoptions altlogdest "/oracle/soft/my_data/binlog.index"
RMTHOST 133.37.126.38,MGRPORT 7810, compress
RMTTASK replicat,GROUP repinimy
TABLE mydb.*;

添加extinit进程
ADD EXTRACT extinimy, SOURCEISTABLE

4.3.目标端repinit
REPLICAT repinimy
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SOURCEDEFS /oracle/soft/ogg/dirdef/defmydb.p
USERID c##ggs@jam, password "admin123##"
MAP mydb.*,target jam.lzl.*;

添加repinit进程
ADD REPLICAT repinimy, SPECIALRUN

5.目标端

5.1 checkpoint
dblogin userid c##ggs@jam,password admin123##
add checkpointtable c##ggs.checkpointtb1

5.1.replicat
REPLICAT repmy
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_SID="orcl")
setenv (ORACLE_HOME="/oracle/soft/u01/app/oracle/product/18.0.0/dbhome_1")
USERID c##ggs@jam, password admin123##
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/jmmy.dsc, append, megabytes 1024
ALLOWNOOPUPDATES
--ASSUMETARGETDEFS

--SOURCEDEFS /oracle/soft/ogg/dirdef/defmydb.p
MAP mydb.t1,target jam.lzl.t1;
MAP mydb.t2,target jam.lzl.t2;

添加复制进程
add replicat repmy,exttrail /oracle/soft/ogg18/dirdat/fk, checkpointtable c##ggs.checkpointtb1

mysql常用命令

show databases;
use db;
show tables;
show variables like '%log_bin%';
show variables like '%character%';

问题

问题1：

GGSCI (cs-db) 1> dblogin SOURCEDB mydb@133.37.126.38:3306, USERID ggs, PASSWORD "oracle"

2019-03-05 15:49:50 WARNING OGG-00769 MySQL Login failed: . SQL error (2059). Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory.
ERROR: Failed to connect to MySQL database engine for HOST 133.37.126.38, DATABASE mydb, USER ggs, PORT 3306.
mysql登陆成功，ogg登陆失败
#更新密码（mysql_native_password模式）
ALTER USER 'ggs'@'%' IDENTIFIED WITH mysql_native_password BY 'oracle';

问题2：
GGSCI (cs-db DBLOGIN as ggs) 9> start mgr
Process creation error: Cannot find executable file './mgr'

没有进入ogg根目录 start mgr导致

问题3：mysql没有集成模式,oracle 12c pdb必须使用集成模式
GGSCI (cs-db) 7> ADD EXTRACT extmy,integrated tranlog, begin now
ERROR: Invalid parameter specified for ADD EXTRACT.

问题4：extinit启动报错
WARNING OGG-01194 EXTRACT task repinimy abended : Source wildcard specification JAM.my.* includes a catalog name, but the source table name mydb.t1 does not include a catalog name.
不能写SOURCECATALOG JAM
正确写法如下：
MAP mydb.*,target jam.lzl.*;

问题5：
2019-03-06 16:15:17 WARNING OGG-02760 ASSUMETARGETDEFS is ignored because trail file contains table definitions.
2019-03-06 15:53:40 WARNING OGG-02761 Source definitions file, /oracle/soft/ogg/dirdef/defmydb.p, is ignored because trail file contains table definitions.
ogg18不支持mysql8.0

问题6：
如果map写错了，会有没有report报错，但是没有同步数据的情况（init进程也同样会这样）

问题7：

2019-03-25 10:55:12 WARNING OGG-02761 Source definitions file, /oracle/soft/ogg/dirdef/defmydb.p, is ignored because trail file /oracle/soft/ogg18/dirdat/fk000000001 contains table definitions.
2019-03-25 11:00:53 WARNING OGG-02760 ASSUMETARGETDEFS is ignored because trail file /oracle/soft/ogg18/dirdat/fk000000001 contains table definitions.
defgen文件和ASSUMETARGETDEFS参数在18 ogg中不可用

问题8：

GGSCI (cs-db) 9> stats extmy --无信息（insert 后）
如果不是参数错误，delete进程，然后重启

日志

repinimy report：
GGSCI (cs-db) 10> !
view report extinimy

2019-03-25 10:43:39 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
Oracle GoldenGate Capture 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:48:02

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

Starting at 2019-03-25 10:43:39
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Oct 19 11:24:13 EDT 2016, Release 3.10.0-514.el7.x86_64
Node: cs-db
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: 18109

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2019-03-25 10:43:39 INFO OGG-03059 Operating system character set identified as UTF-8.

2019-03-25 10:43:39 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.

2019-03-25 10:43:39 INFO OGG-01360 EXTRACT is running in Initial Load mode.

2019-03-25 10:43:39 INFO OGG-01889 Flush size (max message size) is set to 27,985.
EXTRACT extinimy
SOURCEDB mydb@133.37.126.38:3306, USERID ggs, PASSWORD ***
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE ./dirrpt/extinimy.dsc, APPEND, MEGABYTES 1024
tranlogoptions altlogdest "/oracle/soft/my_data/binlog.index"
RMTHOST 133.37.126.38,MGRPORT 7810, compress
RMTTASK replicat,GROUP repinimy
TABLE mydb.t1;
TABLE mydb.t2;

2019-03-25 10:43:39 INFO OGG-01851 filecaching started: thread ID: 140647706429184.

2019-03-25 10:43:39 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/oracle/soft/myogg/dirtmp.

Database Version:
MySQL
Server Version: 8.0.13
Client Version: 5.6.14
Host Connection: 133.37.126.38 via TCP/IP
Protocol Version: 10

Database Language and Character Set:
CLIENT CHARACTER SET = "utf8mb4"
SERVER CHARACTER SET = "utf8mb4"
DATABASE CHARACTER SET = "utf8mb4"
LOCALE INFORMATION = "en"
DATABASE COLLATION = "casesensitive"

2019-03-25 10:43:40 INFO OGG-06509 Using the following key columns for source table mydb.t1: ID, NAME.

2019-03-25 10:43:40 INFO OGG-06509 Using the following key columns for source table mydb.t2: ID, NAME.

2019-03-25 10:43:45 INFO OGG-01890 Compression level is set to 1.

2019-03-25 10:43:45 INFO OGG-02911 Processing table mydb.t1.

2019-03-25 10:43:54 INFO OGG-02911 Processing table mydb.t2.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2019-03-25 10:43:54 (activity since 2019-03-25 10:43:40)

Output to repinimy:

From Table mydb.t1:
# inserts: 1
# updates: 0
# deletes: 0
# discards: 0