环境介绍

• 环境信息

• 表字段对应

• 测试表-含LOB字段

-- Oracle
create table t1
(
  id            VARCHAR2(32) not null,
  filename      VARCHAR2(200),
  content       BLOB,
  ctime         TIMESTAMP(6),
  flag          NUMBER,
  remark        VARCHAR2(200),
  operator_time DATE
)tablespace two_dat,
LOB (content) STORE AS (TABLESPACE two_lob_dat);

alter table t1
  add constraint PK_UNIONPAY_FILE primary key (ID)
  using index tablespace two_ind_dat;
-- 数据量 : 367817 rows（测试迁移情况）
-- 后续增量 ：6925 rows（测试同步情况）

-- Mysql
-- 创建数据库及用户
create database paydb default character set utf8 ;
CREATE USER 'two'@'%' IDENTIFIED BY 'two';
GRANT all privileges ON paydb.* TO 'two'@'%';
flush privileges;
-- 建测试表
CREATE TABLE `t1` (
  `id` VARCHAR(32) NOT NULL,
  `filename` VARCHAR(200),
  `content` LONGBLOB,
  `ctime` TIMESTAMP(6),
  `flag` DECIMAL(38,0),
  `remark` VARCHAR(200),
  `operator_time` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

部署

Oracle 端

1、安装软件及初始化

[root@Oracle_DB ~]# mkdir /data/u01/ogg12c
[root@Oracle_DB ~]# chown oracle. -R /data/u01/ogg12c
[oracle@Oracle_DB u01]$ unzip ogg_12.3.0.1.4_for_oracle_on_Linux_x86-64.zip
[oracle@Oracle_DB ogg12.3.0.1.4]$ chmod +x runInstaller
[oracle@Oracle_DB ogg12.3.0.1.4]$ chmod +x -R install/
[oracle@Oracle_DB response]$ cat oggcore.rsp | grep -Ev "^#|^$"
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g              < == 版本
SOFTWARE_LOCATION=/data/u01/ogg12c < == ogg 安装路径
START_MANAGER=false                < == mgr 是否启动，false 后面的参数忽略
MANAGER_PORT=
DATABASE_LOCATION=
INVENTORY_LOCATION=
UNIX_GROUP_NAME=

• 静默安装

[oracle@Oracle_DB ogg12.3.0.1.4]$ ./runInstaller -silent -responseFile /data/u01/ogg12.3.0.1.4/response/oggcore.rsp
Successfully Setup Software.

• 设置环境变量及初始化

[oracle@Oracle_DB ~]$ cat .bash_profile
export OGG_HOME=/data/u01/ogg12c
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
alias ggsci='cd $OGG_HOME ; ggsci'
[oracle@Oracle_DB ~]$ source .bash_profile -- 初始化
[oracle@Oracle_DB ~]$ ggsci
GGSCI (Oracle_DB.linux.17usoft.com) 1> create subdirs
Creating subdirectories under current directory /data/u01/ogg12c

Parameter file                 /data/u01/ogg12c/dirprm: created.-- 存放OGG参数各的配置信息 
Report file                    /data/u01/ogg12c/dirrpt: created.-- 存放进程报告文件 
Checkpoint file                /data/u01/ogg12c/dirchk: created.-- 存放检查点文件
Process status files           /data/u01/ogg12c/dirpcs: created.-- 存放进程状态文件
SQL script files               /data/u01/ogg12c/dirsql: created.-- 存放SQL脚本文件
Database definitions files     /data/u01/ogg12c/dirdef: created.-- 存放DEFGEN工具生成的数据定义文件
Extract data files             /data/u01/ogg12c/dirdat: created.-- 存放Trail文件，也就是Capture进程捕获的日志文件 
Temporary files                /data/u01/ogg12c/dirtmp: created.-- 当事物需要的内存超过已分配内存时，默认存储在这个目录
Credential store files         /data/u01/ogg12c/dircrd: created.
Masterkey wallet files         /data/u01/ogg12c/dirwlt: created.
Dump files                     /data/u01/ogg12c/dirdmp: created.

2、OGG管理用户创建-GGM

• （1）、建立OGG用户和表空间

create tablespace ogg_dat datafile '/data/u01/oradata/pay/ggm_dat.dbf' size 500M autoextend on;
create user ggm identified by oracle default tablespace ogg_dat;

• （2）、授权：

grant connect,resource to ggm;      
grant execute on utl_file to ggm;
grant select any dictionary,select any table to ggm;  
grant alter any table to ggm;
grant flashback any table to ggm;
grant execute on DBMS_FLASHBACK to ggm; 
grant insert any table to ggm;      
grant delete any table to ggm;      
grant update any table to ggm;
GRANT CREATE TABLE,CREATE SEQUENCE TO ggm;
---oracle 11.2.0.4
grant restricted session to ggm;

• (3)、配置DDL同步

cd $OGG_HOME
sqlplus / as sysdba
    start marker_setup.sql
 Enter Oracle GoldenGate schema name:ggm
    start ddl_setup
 Enter Oracle GoldenGate schema name:ggm
    start role_setup
  Enter Oracle GoldenGate schema name:ggm
    GRANT GGS_GGSUSER_ROLE TO ggm;
    start ddl_enable
@?/rdbms/admin/dbmspool
    start ddl_pin ggm;

3、配置TNS

[oracle@Oracle_DB ogg]$ vim /data/u01/oracle/11.2.0.3/product/network/admin/tnsnames.ora 
PAY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.89)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pay)
    )
  )
-- 验证
[oracle@Oracle_DB ogg]$ sqlplus ggm/oracle@pay
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 15:24:38 2025
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

4、配置MGR管理进程

>edit params mgr
port 7809
DYNAMICPORTLIST 7810-7900
PURGEOLDEXTRACTS /data/u01/ogg12c/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 2
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3  
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
-- 启动mgr进程
> start mgr 
> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING 

mgr：参数解析

AUTORESTART EXTRACT

• 自动重启所有抽取进程(EXTRACT)
• 当任意抽取进程异常终止时，系统会自动尝试重新启动
• 星号(*)表示应用于所有抽取进程

‌RETRIES 5, WAITMINUTES 3‌

• 重试机制配置：最多重试5次
• 每次重试前等待3分钟
• 提供了进程恢复的容错能力
  数据延迟监控参数：

‌LAGREPORTHOURS 1‌

• 延迟报告阈值：1小时
• 当数据延迟超过1小时时生成报告

‌LAGINFOMINUTES 30‌

• 信息级延迟警告：30分钟
• 延迟达到30分钟时记录信息级别日志

‌LAGCRITICALMINUTES 45‌

• 严重延迟警报：45分钟
• 延迟超过45分钟时触发严重警告，可能需要立即干预

5、配置extract - 迁移

1>add extract ext1,sourceistable  --迁移
2>edit params ext1
EXTRACT ext1
SETENV (ORACLE_HOME = "/data/u01/oracle/11.2.0.3/product")
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV (NLS_LANGUAGE=AMERICAN_AMERICA.UTF8)
SETENV (NLS_TERRITORY=AMERICAN_AMERICA.UTF8)
SETENV (LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib)
SETENV (NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS")
SETENV (NLS_TIMESTAMP_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF6")
USERID GGM@pay, PASSWORD oracle
rmthost 192.168.5.74,mgrport 7809
rmttask replicat,group rep1
TABLE two.t1;

-- 查看配置
3> view params ext1

-- 启动
4> start ext1

-- 查看
5> info ext1

6> view report ext1

6 、生成define文件

• 在异构的同步复制；需要生成define文件。用于数据兼容不同数据库。
  该文件记录了源库需要复制的表的表结构定义信息，在源库生成该文件后需要拷贝到目标库的dirdef目录，当目标库的replica进程将传输过来的数据apply到目标库时需要读写该文件

[oracle@Oracle_DB ogg12c]$ vim dirprm/two_defgen.prm
defsfile ./dirdef/two_defgen.def
userid ggm@pay, password oracle
TABLE two.t1;

• 生成文件

[oracle@Oracle_DB ogg12c]$  defgen paramfile ./dirprm/two_defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
      Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 15 2018 10:11:00
 
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2025-12-10 16:50:11
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Thu Mar 21 00:20:37 CST 2024, Release 5.10.109-1.el7.x86_64
Node: Oracle_DB.linux.17usoft.com
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: 2126426

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirdef/two_defgen.def
userid ggm@pay, password ***
TABLE two.t1;
Retrieving definition for TWO.T1.


Definitions generated for 1 table in ./dirdef/two_defgen.def.

• 传送到目标端

[oracle@Oracle_DB ogg12c]$ scp /data/u01/ogg12c/dirdef/two_defgen.def root@192.168.5.74:/data/ogg/dirdef/

Mysql 端

1、安装软件及初始化

[root@Mysql_DB~]# mkdir -p /data/ogg
[root@Mysql_DB~]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C /data/ogg/
[root@Mysql_DB~]# vim .bash_profile 
export OGG_HOME=/data/ogg/
export PATH=$OGG_HOME:$PATH
alias ggsci='cd $OGG_HOME ; ggsci'
[root@Mysql_DB~]# source .bash_profile 
[root@Mysql_DB~]# ggsci 
Oracle GoldenGate Command Interpreter for MySQL
Version 12.3.0.1.5 OGGCORE_12.3.0.1.0_PLATFORMS_180501.2124
Linux, x64, 64bit (optimized), MySQL Enterprise on May  2 2018 10:58:16
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (Mysql_DB.localdomain) 1> create subdirs
Creating subdirectories under current directory /data/ogg
Parameter file                 /data/ogg/dirprm: created.
Report file                    /data/ogg/dirrpt: created.
Checkpoint file                /data/ogg/dirchk: created.
Process status files           /data/ogg/dirpcs: created.
SQL script files               /data/ogg/dirsql: created.
Database definitions files     /data/ogg/dirdef: created.
Extract data files             /data/ogg/dirdat: created.
Temporary files                /data/ogg/dirtmp: created.
Credential store files         /data/ogg/dircrd: created.
Masterkey wallet files         /data/ogg/dirwlt: created.
Dump files                     /data/ogg/dirdmp: created.

2、创建OGG用户

-- 提前创建好ogg存放checkpoint表的数据库
mysql> create database oggdb;
mysql> create user ogg@'%' identified by 'oracle';
mysql> grant all privileges on *.* to ogg@'%';
mysql> flush privileges;

3、配置mgr

1> edit param mgr
port 7809
dynamicportlist 7810-7900
accessrule, prog *, ipaddr 192.168.5.89, allow
purgeoldextracts /data/ogg/dirdat/*,usecheckpoints, minkeepdays 1

2> start mgr
Manager started.

3> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING  

4、配置replicat复制进程

• 当ext启动时会自动启动rep1进程

-- 创建一次性运行的 REPLICAT 进程
>add replicat rep1,specialrun
>edit params rep1
REPLICAT rep1
targetdb oggdb@192.168.5.74:3306,userid ogg,password oracle
SOURCEDEFS /data/ogg/dirdef/defgen.def
DISCARDFILE ./rep1.dsc, APPEND, MEGABYTES 100 --  错误处理文件,追加，文件大小：100
sqlexec "SET FOREIGN_KEY_CHECKS=0"
MAP TWO.T1, TARGET paydb.t1,
COLMAP (
  id            =  id           ,
  filename      =  filename     ,
  content       =  content      ,
  ctime         =  ctime        ,
  flag          =  flag         ,
  remark        =  remark       ,
  operator_time =  operator_time
);

• 操作命令

-- 停止
> STOP REPLICAT rep1
-- 删除
> DELETE REPLICAT rep1
-- 查看
> INFO REPLICAT rep1

5、数据完成迁移

• Oracle

SQL> select count(*) from t1;
  COUNT(*)
----------
    367817

• mysql

[paydb]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|   367817 |
+----------+
1 row in set (0.037 sec)

配置同步

Oracle 端

1、配全局参数

> edit params ./globals
GGSCHEMA ggm
CHECKPOINTTABLE ggm.checkpoint
> dblogin userid ggm,password oracle
> add checkpointtable ggm.checkpoint

2、添加表级TRANDATA

1> dblogin userid ggm,password oracle
Successfully logged into database.
2> add trandata two.t1
Logging of supplemental redo data enabled for table TWO.T1.
3> INFO TRANDATA two.t1
Logging of supplemental redo log data is enabled for table TWO.T1.
Columns supplementally logged for table TWO.T1: TID.

sqlplus / as sysdba
ALTER TABLE two.t1 add SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

3、配置Extract进程

• 负责抓取需要传输数据

# 在源端用 add extract 命令创建 extract 组；
-- threads与RAC节点数相同即可
-- 示例：>add extract ext2,tranlog,begin now,threads 2

>add extract ext2,tranlog,begin now
EXTRACT added.
# 用add exttrail 命令创建本地 trail 文件
>add exttrail /data/u01/ogg12c/dirdat/et,extract ext2
EXTTRAIL added.

>edit params ext2
EXTRACT ext2
SETENV (ORACLE_HOME = "/data/u01/oracle/11.2.0.3/product")
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
USERID GGM@pay, PASSWORD oracle
-- ASM存储redo log或archive log，需要添加
-- 示例：TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle 
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL  /data/u01/ogg12c/dirdat/et
DISCARDFILE /data/u01/ogg12c/dirrpt/ext2.dsc,APPEND,MEGABYTES 1024
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE two.t1;

> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT1        00:00:00      00:01:47  

5、配传输进程 - pump

• extract进程负责将抓取的数据写入本地trail文件；而需要Pump进程把trail文件传输到目标端。

# 用 add extract 指定本地 trail 文件
>add extract pump2,exttrailsource ./dirdat/et,begin now   -- 本地文件目录
EXTRACT added.

# 用 add rmttrail 指定远程 trail 文件
>ADD RMTTRAIL ./dirdat/e2, EXTRACT pump2          -- 远程目录
RMTTRAIL added.

>edit params  pump2
EXTRACT pump2
RMTHOST 192.168.5.74, MGRPORT 7809
RMTTRAIL  ./dirdat/e2       -- 远程目录
PASSTHRU
TABLE two.t1;

-- 启动
23> start ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
24> start pump2
Sending START request to MANAGER ...
EXTRACT PUMP2 starting
 25> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT2        00:00:00      00:04:40    
EXTRACT     RUNNING     PUMP2       00:00:00      00:01:24 

pump：参数解析
RMTHOST 192.168.5.74, MGRPORT 7809‌

• 指定目标端GoldenGate Manager的地址和端口
• 192.168.5.74：目标服务器IP地址
• MGRPORT 7809：目标端Manager进程监听端口
  ‌RMTTRAIL /data/ogg/dirdat/et
• 定义目标端Trail文件路径
• 数据将被写入到目标端的这个位置
• 文件命名格式为 et 前缀的序列文件
  ‌PASSTHRU‌
• ‌重要模式‌：指示Pump进程不对数据进行转换或过滤
• 直接将源端数据原样传输到目标端
• 适用于需要保持数据完整性的场景
  ‌TABLE TWO.T1‌
• 指定要复制的表对象

Mysql端

1、配置检查点

• 为了保证源和目标端在传数据的时候不会重复或者少传，在目标端添加checkpoin表

1>edit params ./GLOBALS
CHECKPOINTTABLE oggdb.ggs_checkpoint
--添加检查点
2> dblogin sourcedb oggdb@192.168.5.74:3306 userid ogg,PASSWORD oracle;
Successfully logged into database.

3> add checkpointtable oggdb.ggs_checkpoint
Successfully created checkpoint table oggdb.ggs_checkpoint;.
-- 异常删除
4> DELETE CHECKPOINTTABLE oggdb.ggs_checkpoint
-- 查看
5> INFO CHECKPOINTTABLE

2、配置Replicat线程

# 在目标端使用 add replicat 添加 Replicat 进程
1 > dblogin sourcedb oggdb@192.168.5.74:3306 userid ogg,PASSWORD oracle;
2 > add replicat rep2,exttrail ./dirdat/e2,begin now,checkpointtable oggdb.ggs_checkpoint
REPLICAT added.
# 配置rep2进程
3 > EDIT PARAMS rep2
REPLICAT rep2
SOURCEDEFS /data/ogg/dirdef/defgen.def
targetdb oggdb@192.168.5.74:3306,userid ogg,password oracle
DISCARDFILE /data/ogg/dirrpt/rep3.rpt, append
sqlexec "SET FOREIGN_KEY_CHECKS=0"
MAP TWO.T1, TARGET paydb.t1,
COLMAP (
  id            =  id           ,
  filename      =  filename     ,
  content       =  content      ,
  ctime         =  ctime        ,
  flag          =  flag         ,
  remark        =  remark       ,
  operator_time =  operator_time
);


# 使用 start replicat 启动 Replicat 进程
4 > start rep2
Sending START request to MANAGER ...
REPLICAT rep2 starting

5 > info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP2        00:00:00      00:00:07    

测试同步

• Oracle 端插入数据：6925

-- 将t2(增量数据表：6925 )插入t1表测试同步情况
SQL> INSERT INTO t1 (id,filename,content,ctime,flag,remark,operator_time) 
SELECT id,filename,content,ctime,flag,remark,operator_time  FROM t2;
6925 rows created.
SQL> commit;
Commit complete.

• Mysql 查询数据，增量：6925 （同步成功）

[paydb]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|   374742 |
+----------+
1 row in set (0.038 sec)

迁移完成数据量：367817
增量数据量：374742 - 367817 = 6925 

– 至此整个迁移同步完成

删除OGG12c

• 删除srcs.lst中的记录

vim /data/u01/oraInventory/oui/srcs.lst

• 删除inventory.xml中的记录

vim /data/u01/oraInventory/ContentsXML/inventory.xml

• 删除ogg软件

rm -rf /data/u01/ogg12c/*

异常报错

Oracle 11G安装Ogg12c 缺少：libnnz12.so,解决改装Ogg11c

[oracle@Oracle_DB ogg12.3.0.1.4]$ ggsci
ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory
[oracle@Oracle_DB ogg12c]$ ldd ggsci
       ..略..
        libantlr3c.so => /data/u01/ogg12c/./libantlr3c.so (0x00007f07ba4c0000)
        libnnz12.so => not found
        libclntsh.so.12.1 => not found
        libons.so => /data/u01/oracle/11.2.0.3/product/lib/libons.so (0x00007f07c0412000)
        libclntshcore.so.12.1 => not found
        libggnnzitp.so => /data/u01/ogg12c/./libggnnzitp.so (0x00007f07b9a4c000)
       ..略..

Mysql 缺少：libnsl.so.1

[root@Mysql_DB ~]# ggsci 
ggsci: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory

[root@Mysql_DB ogg]# ldd ggsci 
       ..略..
        /lib64/ld-linux-x86-64.so.2 (0x00007f00e13e2000)
        libnsl.so.1 => not found
       ..略..
-- 解决：服务器系统里找到文件拷贝过来
 [root@Mysql_DB ogg]# mv libnsl.so.1 /data/ogg/
 [root@Mysql_DB ogg]# ldd ggsci
       ..略..
        libnsl.so.1 => /data/ogg/./libnsl.so.1 (0x00007f094aa1b000)
       ..略..

启动rep1异常：

• OGG-00769

2025-12-11T16:18:11.050+0800  WARNING OGG-00769  Oracle GoldenGate Delivery for MySQL:  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.
2025-12-11T16:18:11.051+0800  ERROR   OGG-00770  Oracle GoldenGate Delivery for MySQL:  Failed to connect to MySQL database engine for HOST 192.168.5.74, DATABASE oggdb, USER ogg, PORT 3306.
2025-12-11T16:18:16.052+0800  ERROR   OGG-01668  Oracle GoldenGate Delivery for MySQL:  PROCESS ABENDING.

• 问题原因：

该报错核心原因是：OGG（Oracle GoldenGate）连接 MySQL 时，MySQL 8.0+ 默认使用 caching_sha2_password 认证插件，但 OGG 的 MySQL 客户端驱动不支持该插件，或服务器端缺少该插件文件，导致认证失败。

• 解决方法一：

-- 先修改认证插件并重置密码（必须重置密码才能生效）
ALTER USER 'ogg'@'%' IDENTIFIED WITH mysql_native_password BY 'oracle';
-- 刷新权限
FLUSH PRIVILEGES;

• 解决方法二：

vim /etc/my.cnf
[mysqld]
# 添加以下配置，设置默认认证插件
default_authentication_plugin = mysql_native_password
-- 重启
# CentOS/RHEL 7+
systemctl restart mysqld
# Ubuntu/Debian
systemctl restart mysql
# 手动启动方式
service mysqld restart
-- 重建ogg用户
-- 删除
DROP USER IF EXISTS ogg@'%'
-- 创建
create user ogg@'%' identified by 'oracle';
grant all privileges on *.* to ogg@'%';
flush privileges;