DB / Oracle / 备份与迁移

Oracle GoldenGate（OGG）实时数据复制

2026.09.06

Oracle GoldenGate（OGG）实时数据复制

适用版本：Oracle GoldenGate 12c / 19c / 21c 文档版本：v1.0 / 2026-07

1. 概述

Oracle GoldenGate（OGG） 是 Oracle 的实时数据复制软件[1]：

核心特性：

  • 实时复制：基于日志的 CDC
  • 异构支持：Oracle/MySQL/SQL Server 等
  • 双向复制：Active-Active
  • 低影响：不依赖触发器
  • 过滤转换：灵活数据流

典型用途：

  • 实时数据集成
  • 灾难恢复
  • 报表系统
  • 零停机迁移

2. OGG 架构

2.1 经典架构

+-----------+    +-----------+    +-----------+    +-----------+
| Source DB | -> | Extract   | -> | Trail     | -> | Pump      |
+-----------+    +-----------+    +-----------+    +-----------+
                                                        |
                                                        v
+-----------+    +-----------+    +-----------+    +-----------+
| Target DB | <- | Replicat  | <- | Trail     | <- | Collector |
+-----------+    +-----------+    +-----------+    +-----------+

2.2 组件

组件作用
Manager管理进程
Extract抽取源库变更
Pump传输 Trail
Collector接收数据
Replicat应用到目标
Trail数据文件
Checkpoint同步位点

3. 安装 OGG

3.1 下载安装

# 1. 下载 OGG
# Oracle 官网下载对应平台版本

# 2. 解压
unzip V100000-01.zip
tar -xvf fbo_ggs_Linux_x64_shiphome.tar

# 3. 安装
./ggsci
GGSCI> CREATE SUBDIRS

3.2 配置环境

# .bashrc
export OGG_HOME=/u01/ogg
export PATH=$OGG_HOME:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH

4. 源库配置

4.1 启用补充日志

-- 数据库级
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 表级
ALTER TABLE scott.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 切换日志
ALTER SYSTEM ARCHIVE LOG CURRENT;

4.2 启用 Force Logging

ALTER DATABASE FORCE LOGGING;

4.3 创建 OGG 用户

-- 创建表空间
CREATE TABLESPACE ogg_ts DATAFILE '/u01/oradata/orcl/ogg_ts01.dbf' SIZE 1G;

-- 创建用户
CREATE USER oggadmin IDENTIFIED BY ****** 
  DEFAULT TABLESPACE ogg_ts 
  TEMPORARY TABLESPACE temp;

-- 授权
GRANT DBA TO oggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('oggadmin');

4.4 配置 tnsnames

# tnsnames.ora
OGG_SRC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = src-host)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl))
  )

5. Manager 配置

5.1 创建 Manager 参数文件

GGSCI> EDIT PARAMS MGR

# mgr.prm
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

5.2 启动 Manager

GGSCI> START MGR
GGSCI> INFO MGR
GGSCI> STATUS MGR

6. Extract 配置

6.1 创建 Extract

GGSCI> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT ext1, MEGABYTES 50

6.2 Extract 参数

GGSCI> EDIT PARAMS ext1

# ext1.prm
EXTRACT ext1
USERID oggadmin@OGG_SRC, PASSWORD pwd
EXTTRAIL ./dirdat/et
TABLE scott.employees;
TABLE scott.departments;
TABLE scott.jobs;

6.3 启动 Extract

GGSCI> START EXTRACT ext1
GGSCI> INFO EXTRACT ext1
GGSCI> STATS EXTRACT ext1

7. Pump 配置

7.1 创建 Pump

GGSCI> ADD EXTRACT pump1, EXTTRAILSOURCE ./dirdat/et
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT pump1, MEGABYTES 50

7.2 Pump 参数

GGSCI> EDIT PARAMS pump1

# pump1.prm
EXTRACT pump1
USERID oggadmin@OGG_SRC, PASSWORD pwd
RMTHOST tgt-host, MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE scott.employees;
TABLE scott.departments;
TABLE scott.jobs;

7.3 启动 Pump

GGSCI> START EXTRACT pump1
GGSCI> INFO EXTRACT pump1

8. 目标库 Replicat

8.1 创建 Checkpoint 表

GGSCI> EDIT PARAMS ./GLOBALS

# GLOBALS
CHECKPOINTTABLE oggadmin.checkpoint
-- 创建 checkpoint 表
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('oggadmin');

8.2 创建 Replicat

GGSCI> ADD REPLICAT rep1, EXTTRAIL ./dirdat/rt

8.3 Replicat 参数

GGSCI> EDIT PARAMS rep1

# rep1.prm
REPLICAT rep1
USERID oggadmin@OGG_TGT, PASSWORD pwd
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP scott.employees, TARGET scott.employees;
MAP scott.departments, TARGET scott.departments;
MAP scott.jobs, TARGET scott.jobs;

8.4 启动 Replicat

GGSCI> START REPLICAT rep1
GGSCI> INFO REPLICAT rep1
GGSCI> STATS REPLICAT rep1

9. 数据初始化

9.1 方法 1：Data Pump

# 1. 源库导出
expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp SCHEMAS=scott

# 2. 传输到目标库
scp scott.dmp target:/u01/dpump/

# 3. 目标库导入
impdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp SCHEMAS=scott

# 4. 启动 Replicat（跳过初始数据冲突）
GGSCI> START REPLICAT rep1, AFTERCSN <scn>

9.2 方法 2：OGG Initial Load

# 创建 Initial Load Extract
GGSCI> ADD EXTRACT initext, SOURCEISTABLE

# 参数
GGSCI> EDIT PARAMS initext
EXTRACT initext
USERID oggadmin@OGG_SRC, PASSWORD pwd
RMTHOST tgt-host, MGRPORT 7809
RMTTASK REPLICAT, GROUP initrep
TABLE scott.*;

# 创建 Initial Load Replicat
GGSCI> ADD REPLICAT initrep, SPECIALRUN

GGSCI> EDIT PARAMS initrep
REPLICAT initrep
USERID oggadmin@OGG_TGT, PASSWORD pwd
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;

# 启动
GGSCI> START EXTRACT initext

10. 数据过滤与转换

10.1 过滤

-- ext1.prm
TABLE scott.employees, WHERE (dept_id = 10);

10.2 列映射

-- rep1.prm
MAP scott.employees, TARGET scott.employees,
  KEYCOLS (emp_id),
  COLMAP (
    USEDEFAULTS,
    full_name = first_name || ' ' || last_name
  );

10.3 表映射

-- rep1.prm
MAP scott.employees, TARGET hr.emp_new;

11. 监控 OGG

11.1 进程状态

GGSCI> INFO ALL

# 输出：
# Program     Status      Group       Lag at Chkpt  Time Since Chkpt
# MANAGER     RUNNING
# EXTRACT     RUNNING     EXT1        00:00:00      00:00:03
# EXTRACT     RUNNING     PUMP1       00:00:00      00:00:02
# REPLICAT    RUNNING     REP1        00:00:00      00:00:05

11.2 详细信息

GGSCI> INFO EXTRACT ext1, DETAIL
GGSCI> STATS EXTRACT ext1
GGSCI> STATS REPLICAT rep1
GGSCI> LAG EXTRACT ext1

11.3 查看 Trail

GGSCI> INFO ./dirdat/et

12. 常见坑与排错

12.1 Extract 停止

修复：

# 1. 查看状态
GGSCI> INFO EXTRACT ext1

# 2. 查看日志
GGSCI> VIEW GGSEXT1

# 3. 重启
GGSCI> START EXTRACT ext1

12.2 ORA-01291: missing logfile

修复：

-- 1. 检查归档
SELECT sequence#, archived FROM v$archived_log ORDER BY sequence# DESC;

-- 2. 启用补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

12.3 Lag 过大

修复：

# 1. 检查进程状态
GGSCI> INFO ALL

# 2. 检查网络
ping tgt-host

# 3. 调整并行
-- ext1.prm
EOFDELAYCSECS 1
FLUSHCSECS 1

12.4 数据冲突

修复：

-- rep1.prm
HANDLECOLLISIONS
-- 或
RESOLVECONFLICT

12.5 字符集问题

修复：

-- mgr.prm
SOURCECHARSET AL32UTF8
TARGETCHARSET AL32UTF8

13. 最佳实践

  1. 生产用 OGG 19c+：特性完善
  2. 启用 Force Logging：避免数据丢失
  3. 启用 Supplemental Logging：完整捕获
  4. 专用 OGG 用户：权限隔离
  5. 监控 Lag：及时发现延迟
  6. 定期清理 Trail：节省空间
  7. 多 Pump 分流：大并发场景
  8. 双向复制用 Conflict Resolution：避免冲突
  9. 初始数据用 Data Pump：快速初始化
  10. 结合 GoldenGate Veridata：数据校验

14. 参考资料

[1] Oracle GoldenGate Documentation 19c https://docs.oracle.com/en/middleware/goldengate/

[2] Oracle GoldenGate Tutorial https://docs.oracle.com/en/middleware/goldengate/19.1/index.html