使用OGG/Kettle实现Oracle到MySQL数据平滑迁移

jnrjian

已于 2022-03-31 23:36:09 修改

阅读量2.4k

点赞数

文章标签： dba

于 2022-03-31 22:42:53 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/123885364

版权

本文目录：
一、OGG概述
（一）OGG逻辑架构
二、迁移方案
（一）环境信息
（二）表结构迁移
（三）数据迁移
1.源端OGG配置
（1）Oracle数据库配置
（2）Oracle数据库OGG用户创建
（3）源端OGG 管理进程(MGR)配置
（4）源端OGG 表级补全日志(trandata)配置
（5）源端OGG 抽取进程(extract)配置
（6）源端OGG 传输进程(pump)配置
（7）源端OGG 异构mapping文件(defgen)生成
2.目标端OGG配置
（1）目标端MySQL数据库配置
（2）目标端OGG 管理进程(MGR)配置
（3）目标端OGG 检查点日志表(checkpoint)配置
（4）目标端OGG 回放线程(replicat)配置
3.全量同步配置
（1）源端OGG 全量抽取进程(extract)配置
（2）目标端OGG 全量回放进程(replicat)配置
三、数据校验
四、迁移问题处理
（一）MySQL限制
（二）全量与增量衔接
（三）OGG版本选择
（四）无主键表处理
（五）OGG安全规则
（六）数据抽取方式
五、OGG参考资料

一、OGG概述

OGG全称为Oracle GoldenGate,是由Oracle官方提供的用于解决异构数据环境中数据复制的一个商业工具。相比于其它迁移工具OGG的优势在于可以直接解析源端Oracle的redo log，因此能够实现在不需要对原表结构做太多调整的前提下完成数据增量部分的迁移。本篇文章将重点介绍如何使用OGG实现Oracle到MySQL数据的平滑迁移，以及讲述个人在迁移过程中所碰到问题的解决方案。

（一）OGG逻辑架构

参照上图简单给大家介绍下OGG逻辑架构，让大家对OGG数据同步过程有个简单了解，后面章节会详细演示相关进程的配置方式，在OGG使用过程中主要涉及以下进程及文件：

Manager进程：需要源端跟目标端同时运行，主要作用是监控管理其它进程，报告错误，分配及清理数据存储空间，发布阈值报告等
Extract进程：运行在数据库源端，主要用于捕获数据的变化，负责全量、增量数据的抽取
Trails文件：临时存放在磁盘上的数据文件
Data Pump进程：运行在数据库源端，属于Extract进程的一个辅助进程，如果不配置Data Pump，Extract进程会将抽取的数据直接发送到目标端的Trail文件，如果配置了Data Pump，Extract进程会将数据抽取到本地Trail文件，然后通过Data Pump进程发送到目标端，配置Data Pump进程的主要好处是即使源端到目标端发生网络中断，Extract进程依然不会终止
Collector进程：接收源端传输过来的数据变化，并写入本地Trail文件中
Replicat进程：读取Trail文件中记录的数据变化，创建对应的DML语句并在目标端回放

二、迁移方案

（一）环境信息
软件名称源端目标端

OGG版本OGG 12.2.0.2.2 For Oracle

OGG 12.2.0.2.2 For MySQL

数据库版本Oracle 11.2.0.4

MySQL 5.7.21

OGG_HOME/home/oracle/ogg/opt/ogg

IP地址17X.1X.84.124 17X.1X.84.121

数据库cmscms

（二）表结构迁移
表结构迁移属于难度不高但内容比较繁琐的一步，我们在迁移表结构时使用了一个叫sqlines的开源工具，对于sqlines工具在MySQL端创建失败及不符合预期的表结构再进行特殊处理，以此来提高表结构转换的效率。
注意：OGG在Oracle迁移MySQL的场景下不支持DDL语句同步，因此表结构迁移完成后到数据库切换前尽量不要再修改表结构。

-------缺点

1、 5.6版本需要12.1.2版本的OGG才支持

2、异构数据库之间不支持DDL复制

从Oracle同步到MySQL，属于异构架构，不支持DDL同步，包括添加和删除字段，添加和删除索引，重命名表，表分析统计数据。
若是涉及到源端和目标端DDL操作，需要进行源端和目标端同时手工操作。

3、必须要配置defgen，且文件必须放在相同的目录。

4、如果要是双向的话，就必须把MySQL端的binglog设置成row

binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Any other log format (MIXED or STATEMENT) causes Extract to abend.

5、GoldenGate对MySQL只支持InnoDB引擎。所以，在创建MySQL端的表的时候，要指定表为InnoDB引擎。

create table MySQL (name char(10)) engine=innodb;

所有的帮助可以online help里去看

http://docs.Oracle.com/goldengate/c1221/gg-winux/GIMYS/system_requirements.htm#GIMYS122

（三）数据迁移
数据同步的操作均采用OGG工具进行，考虑数据全量和增量的衔接，OGG需要先将增量同步的抽取进程启动，抓取数据库的redo log，待全量抽取结束后开启增量数据回放，应用全量和增量这段期间产生的日志数据，OGG可基于参数配置进行重复数据处理，所以使用OGG时优先将增量进行配置并启用。此外，为了避免本章节篇幅过长，OGG参数将不再解释，有需要的朋友可以查看官方提供的Reference文档查询任何你不理解的参数。

1.源端OGG配置
（1）Oracle数据库配置
针对Oracle数据库，OGG需要数据库开启归档模式及增加辅助补充日志、强制记录日志等来保障OGG可抓取到完整的日志信息
查看当前环境是否满足要求，输出结果如下图所示：

SQL> SELECT NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

如果条件不满足则实行该部分，

#### 开启归档(开启归档需要重启数据库)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

#### 开启附加日志和强制日志
SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> alter system switch logfile;

#### 启用OGG支持
SQL> show parameter enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true;

#### 再次查看当前环境是否满足要求
SQL> SELECT NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

（2）Oracle数据库OGG用户创建

OGG需要有一个用户有权限对数据库的相关对象做操作，以下为涉及的权限，该示例将创建一个用户名和密码均为ogg的Oracle数据库用户并授予以下权限

#### 查看当前数据库已存在的表空间，可使用已有表空间或新建单独的表空间
SQL> SELECT TABLESPACE_NAME, CONTENTS FROM DBA_TABLESPACES;

#### 查看当前表空间文件数据目录
SQL> SELECT NAME FROM V$DATAFILE;

#### 创建一个新的OGG用户的表空间
SQL> CREATE TABLESPACE OGG_DATA DATAFILE '/u01/app/oracle/oradata/cms/ogg_data01.dbf' SIZE 2G;

#### 创建ogg用户且指定对应表空间并授权
SQL> CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE OGG_DATA;
SQL> grant connect,resource,unlimited tablespace to ogg;
SQL> grant create session,alter session to ogg;
SQL> grant execute on utl_file to ogg;
SQL> grant select any dictionary, select any table to ogg;
SQL> grant alter any table to ogg;
SQL> grant flashback any table to ogg;
SQL> grant select any transaction to ogg;
SQL> grant sysdba to ogg;
SQL> grant execute on dbms_streams_adm to ogg;
SQL> grant execute on dbms_flashback to ogg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGG');

（3）源端OGG 管理进程(MGR)配置

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 编辑/创建mgr配置文件
ggsci> edit params mgr

PORT 7809
DYNAMICPORTLIST 8000-8050
-- AUTOSTART extract
-- AUTORESTART extract,retries 4,waitminutes 4
STARTUPVALIDATIONDELAY 5
ACCESSRULE, PROG *, IPADDR 17X.1X.*, ALLOW
ACCESSRULE, PROG SERVER, ALLOW
PURGEOLDEXTRACTS /home/oracle/ogg/dirdat/*, USECHECKPOINTS,MINKEEPFILES 3

#### 启动并查看mgr状态
ggsci> start mgr
ggsci> info all
ggsci> view report mgr

（4）源端OGG 表级补全日志(trandata)配置
表级补全日志需要在最小补全日志打开的情况下才起作用,之前只在数据库级开启了最小补全日志(alter database add supplemental log data;)，redolog记录的信息还不够全面，必须再使用add trandata开启表级的补全日志以获得必要的信息。这应该不用了的

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 使用ogg用户登录appdb实例(TNS配置)对cms所有表增加表级补全日志
ggsci> dblogin userid ogg@appdb,password ogg
ggsci> add trandata cms.*

（5）源端OGG 抽取进程(extract)配置
Extract进程运行在数据库源端，负责从源端数据表或日志中捕获数据。Extract进程利用其内在的checkpoint机制，周期性地检查并记录其读写的位置，通常是写入到本地的trail文件。这种机制是为了保证如果Extract进程终止或者操作系统宕机，我们重启Extract进程后，GoldenGate能够恢复到以前的状态，从上一个断点处继续往下运行，而不会有任何数据损失。

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 创建一个新的增量抽取进程，从redo日志中抽取数据
ggsci> add extract e_cms,tranlog,begin now

#### 创建一个抽取进程抽取的数据保存路径并与新建的抽取进程进行关联
ggsci> add exttrail /home/oracle/ogg/dirdat/ms,extract e_cms,megabytes 1024

#### 创建抽取进程配置文件
ggsci> edit params e_cms
extract e_cms
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME = "/data/oracle/11.2/db_1")
setenv (ORCLE_SID = "cms")
userid ogg@appdb,password ogg
discardfile /home/oracle/ogg/dirrpt/e_cms.dsc,append,megabytes 1024
exttrail /home/oracle/ogg/dirdat/ms
statoptions reportfetch
reportcount every 1 minutes,rate
warnlongtrans 1H,checkinterval 5m
table cms.*;

#### 启动源端抽取进程
ggsci> start e_cms
ggsci> info all
ggsci> view report e_cms

（6）源端OGG 传输进程(pump)配置
pump进程运行在数据库源端，其作用非常简单。如果源端的Extract抽取进程使用了本地trail文件，那么pump进程就会把trail文件以数据块的形式通过TCP/IP协议发送到目标端，Pump进程本质上是Extract进程的一种特殊形式，如果不使用trail文件，那么Extract进程在抽取完数据后，直接投递到目标端。
补充：pump进程启动时需要与目标端的mgr进程进行连接，所以需要优先将目标端的ogg mgr提前配置好，否则会报错连接被拒绝，无法传输抽取的日志文件到目标端对应目录下

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 增加一个传输进程与抽取进程抽取的文件进行关联
shell> add extract p_cms,exttrailsource /home/oracle/ogg/dirdat/ms

#### 增加配置将抽取进程抽取的文件数据传输到远程对应目录下
#### 注意rmttrail参数指定的是目标端的存放目录，需要目标端存在该目录路径
shell> add rmttrail /opt/ogg/dirdat/ms,extract p_cms 


#### 创建传输进程配置文件
shell>edit params p_cms
extract p_cms 
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME = "/data/oracle/11.2/db_1")
setenv (ORCLE_SID = "cms")
userid ogg@appdb,password ogg
RMTHOST 17X.1X.84.121,MGRPORT 7809
RMTTRAIL /opt/ogg/dirdat/ms
discardfile /home/oracle/ogg/dirrpt/p_cms.dsc,append,megabytes 1024
table cms.*;

#### 启动源端抽取进程
#### 启动前确保目标端mgr进程已开启
ggsci> start p_cms
ggsci> info all
ggsci> view report p_cms

（7）源端OGG 异构mapping文件(defgen)生成
该文件记录了源库需要复制的表的表结构定义信息，在源库生成该文件后需要拷贝到目标库的dirdef目录，当目标库的replica进程将传输过来的数据apply到目标库时需要读写该文件，同构的数据库不需要进行该操作。

#### 创建mapping文件配置
shell> cd $OGG_HOME
shell> vim ./dirprm/mapping_cms.prm 
defsfile ./dirdef/cms.def,purge
userid ogg@appdb,password ogg
table cms.*;

#### 基于配置生成cms库的mapping文件
#### 默认生成的文件保存在$OGG_HOME目录的dirdef目录下
shell> ./defgen paramfile ./dirprm/mapping_cms.prm

#### 将该文件拷贝至目标端对应的目录
shell> scp ./dirdef/cms.def root@17X.1X.84.121:/opt/ogg/dirdef

Use the PASSTHRU and NOPASSTHRU parameters to control whether a data-pump Extract processes tables in pass-through mode or in normal mode. In pass-through mode (configured by PASSTHRU), the Extract process does not look up table definitions from the database or from a data-definitions file. Normally, Extract logs into the database to retrieve data definitions and, if the target is NonStop, read a data-definitions file. Extract refers to the definitions when it performs mapping and conversion.

By using pass-through mode, you can cascade captured data to a data pump on an intermediary system that does not host a database. To use pass-through mode, the source and target table names and structures must be identical. Filtering, column mapping, SQLEXEC functions, transformation, or other functions that require data manipulation or translation cannot be used. The WILDCARDRESOLVE parameter must be set to DYNAMIC (the default). If the Oracle GoldenGate installation uses a master-key wallet for data security, PASSTHRU causes the data pump to pass the data to its output trail in its encrypted form automatically.

至此源端环境配置完成

2.目标端OGG配置

（1）目标端MySQL数据库配置

确认MySQL端表结构已经存在
MySQL数据库OGG用户创建

mysql> create user 'ogg'@'%' identified by 'ogg';
mysql> grant all on *.* to 'ogg'@'%';

#### 提前创建好ogg存放checkpoint表的数据库
mysql> create database ogg;

（2）目标端OGG 管理进程(MGR)配置
目标端的MGR进程和源端配置一样，可直接将源端配置方式在目标端重复执行一次即可，该部分不在赘述
（3）目标端OGG 检查点日志表(checkpoint)配置
checkpoint表用来保障一个事务执行完成后，在MySQL数据库从有一张表记录当前的日志回放点，与MySQL复制记录binlog的GTID或position点类似。

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

ggsci> edit param ./GLOBALS
checkpointtable ogg.ggs_checkpoint


ggsci> dblogin sourcedb ogg@17X.1X.84.121:3306 userid ogg
ggsci> add checkpointtable ogg.ggs_checkpoint

（4）目标端OGG 回放线程(replicat)配置
Replicat进程运行在目标端，是数据投递的最后一站，负责读取目标端Trail文件中的内容，并将解析其解析为DML语句，然后应用到目标数据库中。

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 添加一个回放线程并与源端pump进程传输过来的trail文件关联，并使用checkpoint表确保数据不丢失
ggsci> add replicat r_cms,exttrail /opt/ogg/dirdat/ms,checkpointtable ogg.ggs_checkpoint

#### 增加/编辑回放进程配置文件
ggsci> edit params r_cms
replicat r_cms
targetdb cms@17X.1X.84.121:3306,userid ogg,password ogg
sourcedefs /opt/ogg/dirdef/cms.def
discardfile /opt/ogg/dirrpt/r_cms.dsc,append,megabytes 1024
HANDLECOLLISIONS
MAP cms.*,target cms.*;

注意：replicat进程只需配置完成，无需启动，待全量抽取完成后再启动。

至此源端环境配置完成
待全量数据抽取完毕后启动目标端回放进程即可完成数据准实时同步。

3.全量同步配置

全量数据同步为一次性操作，当OGG软件部署完成及增量抽取进程配置并启动后，可配置1个特殊的extract进程从表中抽取数据，将抽取的数据保存到目标端生成文件，目标端同时启动一个单次运行的replicat回放进程将数据解析并回放至目标数据库中。

sourceistable

（1）源端OGG 全量抽取进程(extract)配置

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

#### 增加/编辑全量抽取进程配置文件
#### 其中RMTFILE指定抽取的数据直接传送到远端对应目录下
#### 注意：RMTFILE参数指定的文件只支持2位字符，如果超过replicat则无法识别
ggsci> edit params ei_cms
SOURCEISTABLE
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_SID=cms)
SETENV (ORACLE_HOME=/data/oracle/11.2/db_1)
USERID ogg@appdb,PASSWORD ogg
RMTHOST 17X.1X.84.121,MGRPORT 7809
RMTFILE /opt/ogg/dirdat/ms,maxfiles 100,megabytes 1024,purge
TABLE cms.*;

#### 启动并查看抽取进程正常
shell> nohup ./extract paramfile ./dirprm/ei_cms.prm reportfile ./dirrpt/ei_cms.rpt &

## 查看日志是否正常进行全量抽取
shell> tail -f ./dirrpt/ei_cms.rpt

（2）目标端OGG 全量回放进程(replicat)配置

#### 切换至ogg软件目录并执行ggsci进入命令行终端
shell> cd $OGG_HOME
shell> ggsci

ggsci> edit params ri_cms
SPECIALRUN
END RUNTIME
TARGETDB cms@17X.1X.84.121:3306,USERID ogg,PASSWORD ogg
EXTFILE /opt/ogg/dirdat/ms
DISCARDFILE ./dirrpt/ri_cms.dsc,purge
MAP cms.*,TARGET cms.*;

#### 启动并查看回放进程正常
shell> nohup ./replicat paramfile ./dirprm/ri_cms.prm reportfile ./dirrpt/ri_cms.rpt &

#### 查看日志是否正常进行全量回放
shell> tail -f ./dirrpt/ri_cms.rpt

三、数据校验

数据校验是数据迁移过程中必不可少的环节，本章节提供给几个数据校验的思路共大家参数，校验方式可以由以下几个角度去实现：
1.通过OGG日志查看全量、增量过程中discards记录是否为0来判断是否丢失数据；
2.通过对源端、目标端的表执行count判断数据量是否一致；
3.编写类似于pt-table-checksum校验原理的程序，实现行级别一致性校验，这种方式优缺点特别明显，优点是能够完全准确对数据内容进行校验，缺点是需要遍历每一行数据，校验成本较高；
4.相对折中的数据校验方式是通过业务角度，提前编写好数十个返回结果较快的SQL，从业务角度抽样校验。

四、迁移问题处理

本章节将讲述迁移过程中碰到的一些问题及相应的解决方式。
（一）MySQL限制
在Oracle到MySQL的表结构迁移过程中主要碰到以下两个限制：
1. Oracle端的表结构因为最初设计不严谨，存在大量的列使用varchar(4000)数据类型，导致迁移到MySQL后超出行限制，表结构无法创建。由于MySQL本身数据结构的限制，一个16K的数据页最少要存储两行数据，因此单行数据不能超过65,535 bytes，因此针对这种情况有两种解决方式：

根据实际存储数据的长度，对超长的varchar列进行收缩；
对于无法收缩的列转换数据类型为text，但这在使用过程中可能导致一些性能问题；

2. 与第一点类似，在Innodb存储引擎中，索引前缀长度限制是767 bytes，若使用DYNAMIC、COMPRESSED行格式且开启innodblargeprefix的场景下，这个限制是3072 bytes，即使用utf8mb4字符集时，最多只能对varchar(768)的列创建索引；
3. 使用ogg全量初始化同步时，若存在外键约束，批量导入时由于各表的插入顺序不唯一，可能子表先插入数据而主表还未插入，导致报错子表依赖的记录不存在，因此建议数据迁移阶段禁用主外键约束，待迁移结束后再打开。

mysql>set global foreign_key_checks=off;

（二）全量与增量衔接

----------最好别用

1 对于没有主键以及唯一索引的表
对于update缺失行情况，目标库会转化为insert语句进行插入。
对于delete缺失行情况，目标会会忽略操作。
对于insert发现重复行情况，目标库会仍然进行插入操作。（导致数据重复) ----不受HANDLECOLLISIONS参数影响，继续插入

2 对于存在主键
对于update缺失行情况，且抓取进程不使用fetchoptions fetchpkupdatecols参数，更新主键则目标库转化为insert操作，如果未更新主键则忽略。
对于delete缺失情况，则忽略，
对于insert发现重复行情况，转化成update语句，将重复列更新主键值为原来插入主键值。

3 对于存在唯一值索引
对于update缺失行情况，目标库会转化为insert语句进行插入。
对于delete缺失行情况，目标会会忽略操作。
对于insert发现重复行情况，目标库执行先delete操作然后再insert。

HANDLECOLLISIONS参数是实现OGG全量数据与增量数据衔接的关键，其实现原理是在全量抽取前先开启增量抽取进程，抓去全量应用期间产生的redo log，当全量应用完成后，开启增量回放进程，应用全量期间的增量数据。使用该参数后增量回放DML语句时主要有以下场景及处理逻辑：

目标端不存在delete语句的记录，忽略该问题并不记录到discardfile
目标端丢失update记录
- 更新的是主键值，update转换成insert
- 更新的键值是非主键，忽略该问题并不记录到discardfile
目标端重复insert已存在的主键值，这将被replicat进程转换为UPDATE现有主键值的行

（三）OGG版本选择
在OGG版本选择上我们也根据用户的场景多次更换了OGG版本，最初因为客户的Oracle 数据库版本为11.2.0.4，因此我们在选择OGG版本时优先选择使用了11版本，但是使用过程中发现，每次数据抽取生成的trail文件达到2G左右时，OGG报错连接中断，查看RMTFILE参数详细说明了解到trail文件默认限制为2G，后来我们替换OGG版本为12.3，使用MAXFILES参数控制生成多个指定大小的trail文件，回放时Replicat进程也能自动轮转读取Trail文件，最终解决该问题。但是如果不幸Oracle环境使用了Linux 5版本的系统，那么你的OGG需要再降一个小版本，最高只能使用OGG 12.2。

（四）无主键表处理
在迁移过程中还碰到一个比较难搞的问题就是当前Oracle端存在大量表没有主键。在MySQL中的表没有主键这几乎是不被允许的，因为很容易导致性能问题和主从延迟。同时在OGG迁移过程中表没有主键也会产生一些隐患，比如对于没有主键的表，OGG默认是将这个一行数据中所有的列拼凑起来作为唯一键，但实际还是可能存在重复数据导致数据同步异常，Oracle官方对此也提供了一个解决方案，通过对无主键表添加GUID列来作为行唯一标示，具体操作方式可以搜索MOS文档ID 1271578.1进行查看。

Oracle GoldenGate 针对表没有主键或唯一索引的解决方案_ITPUB博客

（五）OGG安全规则

报错信息

2019-03-08 06:15:22  ERROR   OGG-01201 Error reported by MGR : Access denied.

错误信息含义源端报错表示为该抽取进程需要和目标端的mgr进程通讯，但是被拒绝，具体操作为：源端的extract进程需要与目标端mgr进行沟通，远程将目标的replicat进行启动，由于安全性现在而被拒绝连接。

报错原因

在Oracle OGG 11版本后，增加了新特性安全性要求，如果需要远程启动目标端的replicat进程，需要在mgr节点增加访问控制参数允许远程调用

解决办法

在源端和目标端的mgr节点上分别增加访问控制规则并重启

## 表示该mgr节点允许(ALLOW)10.186网段(IPADDR)的所有类型程序(PROG *)进行连接访问
ACCESSRULE, PROG *, IPADDR 10.186.*.*, ALLOW

（六）数据抽取方式

报错信息

2019-03-15 14:49:04  ERROR   OGG-01192 Trying to use RMTTASK on data types which may be written as LOB chunks (Table: 'UNIONPAYCMS.CMS_OT_CONTENT_RTF').

报错原因

根据官方文档说明，当前直接通过Oracle数据库抽取数据写到MySQL这种initial-load方式，不支持LOBs数据类型，而表 UNIONPAYCMS.CMSOTCONTENT_RTF 则包含了CLOB字段，无法进行传输,并且该方式不支持超过4k的字段数据类型

解决方法

将抽取进程中的RMTTASK改为RMTFILE参数官方建议将数据先抽取成文件，再基于文件数据解析进行初始化导入

五、OGG参考资料
阅读OGG官方文档，查看http://support.oracle.com上Oracle官方给予的问题解决方案是学习OGG非常有效的方式，作者这里也打包了一些文档供大家下载查看，其中个人感觉比较重要并且查看比较多的文档包括： How to Replicate Data Between Oracle and MySQL Database? (文档 ID 1605674.1).pdf 这个文档的作用相当于OGG快速开始，能够帮助用户快速的配置并跑通OGG全量、增量数据同步的流程，非常适合初学者上手 Administering Oracle GoldenGate for Windows and UNIX.pdf Administering文档内容较多，其中详细介绍了各种OGG的使用配置场景，大家可以根据自己需要选择重点章节浏览 Reference for Oracle GoldenGate for Windows and UNIX.pdf Reference文档算是我查看最多的一个文档，其中包含了OGG所有参数的描述、语法及使用示例，非常的详细，这也是前面未对参数进行展开讲解的原因。

------------------

KETTLE 迁移

它是一款纯Java编写的软件，就像它的名字（水壶）一样，是用来把各种数据放到一个壶里，然后以一种指定的格式流出。当然你也可以使用DS(datastage)或者Informatica。不过这两个是收费的，而kettle是免费开源的。

这里只介绍它最简单的能满足我们把数据从Oracle迁移到MySQL的功能。

同理，第一步把jar包load进去，不同的是，这次要load的是MySQL的jar包。需要注意的是，如果你的MySQL版本不同可能需要load不同的jar包。第二步同也是配置连接信息，保证你的源和目标都连接成功，最后一步就是简单的拖拖拽拽。最后run一下就可以了。

它的优点就是配置起来比OGG快，但是同样可以通过job做到实时同步，处理速度和Python旗鼓相当，却不用自己来写mapping关系，并且提供了图形化界面。也能和Migration Toolkit一样同时创建表（新增一个Java脚本），进行类型转换，但日志更详细。只是可能学习成本高一点，要看的懂一些Java报错方便调试。

接下来我们简单看一个demo：

我们运行spoon.sh之后可以打开这个界面。view一界显示了这个转换的名字、数据源、处理步骤等，中间区域是你拖拽出来的操作，一个输入，一个输出。这就是一个简单的数据迁移的所有步骤。

打开input的内容，就是很简单的一条SQL在哪个源数据库上抽取数据，当然这条SQL也可以是拖拽生成出来，类似于congos的拖拽生成报表。千万要注意的是，不要加分号！

output的内容就显示丰富了很多，选择目标数据源，以及会自动的mapping列的信息，还有在迁移之间要不要先清空，迁移过程中如果遇到问题了会不会中止。

这里就是显示了它超越Migration tools的log最细粒度到行级别，可以更快地分析出问题。

这里则是详细的日志输出。一般如果定时跑批处理的话，把它重定向到具体的log里，然后当做发送邮件。

---------------------------------不支持对象的迁移

上面用了很长的篇幅介绍了一下几种迁移的工具，每种迁移的方式都是各有千秋，在合适的场景下选择适合自己的方法进行操作。不过刚刚迁移的都是表和数据对象。我们都知道在数据库还有一些其它的对象，像视图、物化视图、存储过程、函数、包，或者一个索引，同样的SQL是不是也需要改写，都是我们需要考虑到的一个因素。

接下来我们来看一下其它对象怎么迁移。

1、view

在MySQL里view是不可以嵌套子查询的：

create view v_test as select * from (select * from test) t;

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

解决方法就是view的嵌套：

create view v_sub_test as select * from test;

Query OK, 0 rows affected (0.02 sec)

create view v_test as select * from v_sub_test;

Query OK, 0 rows affected (0.00 sec)

2、物化视图

物化视图用于预先计算并保存表连接或聚集等耗时较多的操作结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速得到结果。但是MySQL里没有这个功能。通过事件调度和存储过程模拟物化视图，实现的难点在于更新物化视图，如果要求实时性高的更新，并且表太大的话，可能会有一些性能问题。

3、Trigger、存储过程、package

1）Oracle创建触发器时允许or，但是MySQL不允许。所以迁移时如果有需要写两个。

2）两种数据库定义变量的位置不同，而且MySQL里不支持%type。这个在Oracle中用得太频繁了，是个好习惯。

3）elseif的逻辑分支语法不同，并且MySQL里也没有for循环。

4）在MySQL中不可以返回cursor，并且声明时就要赋对象。

5）Oracle用包来把存储过程分门别类，而且在package里可以定义公共的变量/类型，既方便了编程，又减少了服务器的编译开销。可MySQL里根本没有这个概念。所以MySQL的函数也不可以重载。

6）预定义函数。MySQL里没有to_char() to_date()之类的函数，也并不是所有的Oracle都是好的，就像substring()和load_file()这样的函数，MySQL有，Oracle却没有。

7）MySQL里可以使用set和=号给变量赋值，但不可以使用：=。而且在MySQL里没 || 来拼接字符串。

8）MySQL的注释必须要求-- 和内容之间有一个空格。

9）MySQL存储过程中只能使用leave退出当前存储过程，不可以使用return。

10）MySQL异常对象不同，MySQL同样的可以定义和处理异常，但对象名字不一样。

4､分页语句

MySQL中使用的是limit关键字，但在Oracle中使用的是rownum关键字。所以每有的和分页相关的语句都要进行调整。

5､JOIN

如果你的SQL里有大量的（+），外连接这绝对是一个很头疼的问题。需要改写。

6､group by语句

Oracle里在查询字段出现的列一定要出现在group by后面，而MySQL里却不用。只是这样出来的结果可能并不是预期的结果。造成MySQL这种奇怪的特性的归因于sql_mode的设置，一会会详细说一下sql_mode。不过从Oracle迁移到MySQL的过程中，group by语句不会有跑不通的情况，反过来迁移可能就需要很长的时间来调整了。

7､bitmap位图索引

在Oracle里可以利用bitmap来实现布隆过滤，进行一些查询的优化，同时这一特性也为Oracle一些数据仓库相关的操作提供了很好的支持，但在MySQL里没有这种索引，所以以前在Oracle里利于bitmap进行优化的SQL可能在MySQL会有很大的性能问题。

目前也没有什么较好的解决方案，可以尝试着建btree的索引看是否能解决问题。要求MySQL提供bitmap索引在MySQL的bug库里被人当作一个中级的问题提交了上去，不过至今还是没有解决。

8､分区表（Partitioned table）

需要特殊处理，与Oracle的做法不同，MySQL会将分区键视作主键和唯一键的一部分。为确保不对应用逻辑和查询产生影响，必须用恰当的分区键重新定义目标架构。

9､角色

MySQL8.0以前也没有role的对象。在迁移过程中如果遇到的角色则是需要拼SQL来重新赋权。不过MySQL更好的一点是MySQL的用户与主机有关。

10､表情和特殊字符

在Oracle里我们一般都选择AL32UTF8的字符集，已经可以支付生僻字和emoji的表情了，因为在迁移的时候有的表包含了大量的表情字符，在MySQL里设置了为utf8却不行，导过去之后所有的都是问号，后来改成了utf8mb4才解决问题，所以推荐默认就把所有的DB都装成utf8mb4吧。

Oracle和MySQL差异远远不止这些，像闪回、AWR这些有很多，这里只谈一些和迁移工作相关的。

----------数据校验

当数据迁移完成后，如何确保数据的正确迁移、没有遗漏和错误是一个很难的问题。这里的难不是实现起来困难，而是要把它自动化，达到节省人力的目标有点难，因为两者的数据类型不同，数据量偏大，写一些脚本去做检查效果不大。

我们的数据校检工作主要分为在导入过程中的log和警告，在load的时候SHOW WARNINGS和errors，在使用Python、OGG、Kettle等工具时详细去看每个errors信息。

1、count(*)

迁移或增量操作完成以后，用最简单的count(*)去检查，在MySQL和Oracle上检查进行比对。如果数据量一致，再进行数据内容的验证。由于数据量太大，只进行了抽样检测。人工的手动检验如果没有问题了，可以使用应用程序对生产数据库的副本进行测试，在备库上进行应用程序的测试，从而进行再一次的验检。

2、etl工具

另外推荐的一种方式就是使用etl工具配置好MySQL和Oracle的数据源，分别对数据进行抽取，然后生成cube，进行多纬度的报表展现。数据是否有偏差，可以一目了然看清。

数据的完整性验证是十分重要的，千万不要怕验证到错误后要花好长时候去抽取同步的操作这一步。因为一旦没有验证到错误，让数据进行了使用却乱掉了，后果将更严重。

3、SQL_MODE

https://dev.MySQL.com/doc/refman/5.5/en/sql-mode.html

MySQL服务器能够工作在不同的SQL模式下，针对不同的客户端，以不同的方式应用这些模式。这样应用程序就能对服务器操作进行量身定制，以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。

TRADITIONAL

设置“严格模式”，限制可接受的数据库输入数据值（类似于其它数据库服务器），该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。

ONLY_FULL_GROUP_BY

在MySQL的sql_mode=default的情况下是非ONLY_FULL_GROUP_BY语义，也就是说一条select语句，MySQL允许target list中输出的表达式是除聚集函数、group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined，无法确定(实际上MySQL的表现是分组内第一行对应列的值)

select list中的所有列的值都是明确语义。

简单来说，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

Without Regard to any trailing spaces

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

MySQL校对规则属于PADSPACE，MySQL对CHAR和VARCHAR值进行比较都忽略尾部空格，和服务器配置以及MySQL版本都没关系。

explicit_defauls_for_timestamp

MySQL中TIMESTAMP类型和其它的类型有点不一样(在没有设置explicit_defaults_for_timestamp=1的情况下），在默认情况下，如果TIMESTAMP列没有显式的指明null属性，那么该列会被自动加上not null属性(而其他类型的列如果没有被显式的指定not null，那么是允许null值的)，如果往这个列中插入null值，会自动设置该列的值为current timestamp值，表中的第一个TIMESTAMP列，如果没有指定null属性或者没有指定默认值，也没有指定ON UPDATE语句，那么该列会自动被加上DEFAULT 。

CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。第一个TIMESTAMP列之后的其它的TIMESTAMP类型的列，如果没有指定null属性，也没有指定默认值，那该列会被自动加上DEFAULT '0000-00-00 00:00:00'属性。如果insert语句中没有为该列指定值，那么该列中插入'0000-00-00 00:00:00'，并且没有warning。

如果我们启动时在配置文件中指定了explicit_defaults_for_timestamp=1，MySQL会按照如下的方式处理TIMESTAMP列。

此时如果TIMESTAMP列没有显式的指定not null属性，那么默认的该列可以为null，此时向该列中插入null值时，会直接记录null，而不是current timestamp。并且不会自动的为表中的第一个TIMESTAMP列加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP属性，除非你在建表时显式的指明。

六、一些性能参数

我们可以在导入数据的时候预先的修改一些参数，来获取最大性能的处理，比如可以把自适应hash关掉，Doublewrite关掉，然后调整缓存区，log文件的大小，把能变大的都变大，把能关的都关掉来获取最大的性能，我们接下来说几个常用的：

innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下，在事务提交时，不会主动触发写入磁盘的操作。

如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去。

如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file。但是flush(刷到磁盘)的操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog

sync_binlog 的默认值是0，像操作系统刷其它文件的机制一样，MySQL不会同步到磁盘中去，而是依赖操作系统来刷新binary log。

当sync_binlog =N (N>0) ，MySQL 在每写N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

max_allowed_packet

在导大容量数据特别是CLOB数据时，可能会出现异常：“Packets larger than max_allowed_packet are not allowed”。这是由于MySQL数据库有一个系统参数max_allowed_packet，其默认值为1048576(1M)，可以通过如下语句在数据库中查询其值：show VARIABLES like '%max_allowed_packet%';

修改此参数的方法是在MySQL文件夹找到my.cnf文件，在my.cnf文件[MySQLd]中添加一行：max_allowed_packet=16777216

innodb_log_file_size

InnoDB日志文件太大，会影响MySQL崩溃恢复的时间，太小会增加IO负担，所以我们要调整合适的日志大小。在数据导入时先把这个值调大一点。避免无谓的buffer pool的flush操作。但也不能把 innodb_log_file_size开得太大，会明显增加 InnoDB的log写入操作，而且会造成操作系统需要更多的Disk Cache开销。

innodb_log_buffer_size

InnoDB用于将日志文件写入磁盘时的缓冲区大小字节数。为了实现较高写入吞吐率，可增大该参数的默认值。一个大的log buffer让一个大的事务运行，不需要在事务提交前写日志到磁盘，因此，如果你有事务比如update、insert或者delete 很多的记录，让log buffer 足够大来节约磁盘I/O。

innodb_buffer_pool_size

这个参数主要缓存InnoDB表的索引、数据、插入数据时的缓冲。为InnoDN加速优化首要参数。一般让它等于你所有的innodb_log_buffer_size的大小就可以，

innodb_log_file_size要越大越好。

innodb_buffer_pool_instances

InnoDB缓冲池拆分成的区域数量。对于数GB规模缓冲池的系统，通过减少不同线程读写缓冲页面的争用，将缓冲池拆分为不同实例有助于改善并发性。

总结

一定要选择合适你的迁移工具，没有哪一个工具是最好的。
数据的检验非常重要，有的时候我们迁过去很开心，校验时发生错误，这个时候必须要重来。
重复地迁移是很正常的，合乎每次迁移可能需要很长时间，总会是有错误的，要做好再迁的心态。

jnrjian

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
使用OGG/Kettle实现Oracle到MySQL数据平滑迁移

本文目录：一、OGG概述（一）OGG逻辑架构二、迁移方案（一）环境信息（二）表结构迁移（三）数据迁移1.源端OGG配置（1）Oracle数据库配置（2）Oracle数据库OGG用户创建（3）源端OGG 管理进程(MGR)配置（4）源端OGG 表级补全日志(trandata)配置（5）源端OGG 抽取进程(extract)配置（6）源端OGG 传输进程(pump)配置（7）源端OGG 异构mapping文件(defgen)生成2.目标端OGG配置（1）目标端MySQL数据库配...
复制链接

扫一扫