手把手教你 OGG 新增表同步配置

青年数据库学习互助会 2025-07-08

101

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是JiekeXu,江湖人称“强哥”，青学会MOP技术社区联合创始人，荣获Oracle ACE Pro称号，墨天轮MVP，墨天轮年度“墨力之星”，拥有Oracle OCP/OCM 认证,MySQL 5.7/8.0 OCP认证以及KCA、KCP、KCSM、PCA、PCTA、OBCA等众多国产数据库认证证书,今天和大家一起来看看手把手教你 OGG 新增表同步配置方案。欢迎点击下方“JiekeXu DBA之路”公众号名片可关注我的微信公众号，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送给你！后台回复【加群】，添加我个人微信拉你进群交流学习。

前言

有一套运行了好久的 OGG 同步环境应业务要求，下游需要同步几张表数据进行相关业务的展示，但在同步的表中缺少一张，现需要使用 OGG 同步到下游使用。

环境说明：源端目标端均为 Oracle 19c 数据库，使用 ogg19c 进行数据同步，两边表空间和用户名相同，有几十张表同步到下游数据库。

源端表添加补充日志

那么说干就干，直接开始了，首先添加补充日志，然后编辑抽取进程配置文件添加新增表；

jieke-19crac1:/home/oracle(JiekeXu1)$ ggsci

Oracle 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 US-ASCII.

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

GGSCI (jieke-19crac1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPU1        00:00:00      00:00:05    
EXTRACT     RUNNING     DPU2        00:00:00      00:00:09    
EXTRACT     RUNNING     EXT1        00:00:03      00:00:07    
EXTRACT     RUNNING     EXT2        00:00:03      00:00:07

GGSCI (jieke-19crac1) 2> view params ext2

GGSCI (jieke-19crac1) 3> dblogin USERIDALIAS alias_ogg19c

GGSCI (jieke-19crac1 as OGG@JiekeXu1) 4> add trandata JiekeXu_sz.t_partner

2025-05-23 17:02:03  INFO    OGG-15131  Logging of supplemental redo log data is already enabled for table JiekeXu_sz.t_partner.

2025-05-23 17:02:03  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table JiekeXu_sz.t_partner.

2025-05-23 17:02:03  INFO    OGG-10471  ***** Oracle Goldengate support information on table JiekeXu_sz.t_partner ***** 
Oracle Goldengate support native capture on table JiekeXu_sz.t_partner.
Oracle Goldengate marked following column as key columns on table JiekeXu_sz.t_partner: SEQUENCE_NO.

GGSCI (jieke-19crac1 as OGG@JiekeXu1) 5> info trandata JiekeXu_sz.t_partner

2025-05-23 17:09:25  INFO    OGG-10471  ***** Oracle Goldengate support information on table JiekeXu_sz.t_partner ***** 
Oracle Goldengate support native capture on table JiekeXu_sz.t_partner.
Oracle Goldengate marked following column as key columns on table JiekeXu_sz.t_partner: SEQUENCE_NO.

Logging of supplemental redo log data is enabled for table JiekeXu_sz.t_partner.

Columns supplementally logged for table JiekeXu_sz.t_partner: "SEQUENCE_NO".

Prepared CSN for table JiekeXu_sz.t_partner: 116974557731

修改源端配置文件

GGSCI (jieke-19crac1 as OGG@JiekeXu1) 6> edit params ext2
--添加如下表 :wq!
TABLE JiekeXu_sz.t_partner;

GGSCI (jieke-19crac1 as OGG@JiekeXu1) 7> edit params dpu2
--添加如下表 :wq!
TABLE JiekeXu_sz.t_partner;

重启抽取进程，停止投递进程，查看当前 SCN.

select dbms_flashback.get_system_change_number from dual;
select to_char(to_number(CURRENT_SCN)) from V$DATABASE;
--哪个 SCN 小就用哪个

17:21:33 SYS@JiekeXu1> select to_char(current_scn) from v$database; 

TO_CHAR(CURRENT_SCN)
----------------------------------------
116975769463

 col DIRECTORY_NAME for a30
 col DIRECTORY_PATH for a65
 col owner for a30
 set line 149 pages 345
 select * from dba_directories;

使用数据泵导出新增表

[oracle@jieke-19crac1 dump_dir]$ expdp "'/ as sysdba'" directory=DUMP_DIR parallel=2 cluster=n compression=ALL FLASHBACK_SCN=116975769463 dumpfile=t_partner_%U.dmp logfile=t_partner.log tables=JiekeXu_sz.t_partner

Export: Release 19.0.0.0.0 - Production on Fri May 23 17:22:08 2025
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_02":  "/******** AS SYSDBA" directory=DUMP_DIR parallel=2 cluster=n compression=ALL FLASHBACK_SCN=116975769463 dumpfile=t_partner_%U.dmp logfile=t_partner.log tables=JiekeXu_sz.t_partner 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "JiekeXu_sz"."t_partner"              478.4 MB  935020 rows
Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_02 is:
  home/oracle/dump_dir/t_partner_01.dmp
  home/oracle/dump_dir/t_partner_02.dmp
Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at Fri May 23 17:27:11 2025 elapsed 0 00:05:01

目标端导入数据表

dwogg-162:/home/oracle/tmp(dwdb)$ impdp "'/ as sysdba'" directory=DUMP_DIR  parallel=2 cluster=n dumpfile=t_partner_%U.dmp logfile=imp_t_partner.log tables=JiekeXu_sz.t_partner

Import: Release 19.0.0.0.0 - Production on Fri May 23 17:44:19 2025
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=DUMP_DIR parallel=2 cluster=n dumpfile=t_partner_%U.dmp logfile=imp_t_partner.log tables=JiekeXu_sz.t_partner 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JiekeXu_sz"."t_partner"              478.4 MB  935020 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'MOBILE_CC' does not exist

Failing sql is:
GRANT SELECT ON "JiekeXu_sz"."t_partner" TO "MOBILE_CC"

Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri May 23 17:49:46 2025 elapsed 0 00:05:25

目标端 OGG 进程配置

在目标端 OGG 应用进程中添加表配置，注意 csn 应该对应最开始主库查到的 to_char(current_scn) 值。

GGSCI (dwogg-162) 2> edit params rep3
  

map JiekeXu_sz.t_partner,target JiekeXu_sz.t_partner,filter (@GETENV ('TRANSACTION', 'CSN') > 116975769463);

重启应用进程

然后重启目标端应用进程，启动源端投递进程。

start rep3
info rep3

--源端
start dpu2
info all

上面修改配置文件后也遇到了一个小小的错误，查看日志报错 ERROR OGG-01296 如下所示。这里也简单的记录说明下，因为配置文件中仅写了一个通配所有表的配置：MAP JiekeXu_sz.*, target JiekeXu_sz.*; 由于新增表的配置放到这一行的后面了，导致 MAPPING 错误，发现后将新增表的 MAP 放到这个配置之前，重启后数据同步正常。

2025-05-23 17:59:52  INFO    OGG-06506  Wildcard MAP resolved (entry JiekeXu_sz.*): MAP "JiekeXu_sz"."t_partner" TARGET JiekeXu_sz."t_partner".

2025-05-23 17:59:52  INFO    OGG-02756  The definition for table JiekeXu_sz.t_partner is obtained from the trail file.

2025-05-23 17:59:52  INFO    OGG-06511  Using following columns in default map by name: SEQUENCE_NO, FK_PARTNER, PK_CREDIT_PARTNER, FK_CREDIT_CC, FK_CREDIT_PARENT_PARTNER, FK_CREDIT_PARENT_CC, FK_CREDIT_ROOT_PARTNER, FK_CREDIT_ROOT_CC, MATURITY_AMOUNT,SHARE_PRICE_INFO.

2025-05-23 17:59:52  INFO    OGG-06510  Using the following key columns for target table JiekeXu_sz.t_partner: SEQUENCE_NO.


2025-05-23 17:59:52  INFO    OGG-06505  MAP resolved (entry JiekeXu_sz.t_partner): MAP "JiekeXu_sz"."t_partner",target JiekeXu_sz.t_partner,filter(@GETENV('TRANSACTION','CSN') > 116975769463).

2025-05-23 17:59:52  WARNING OGG-02081  Detected duplicate TABLE/MAP entry for source table JiekeXu_sz.t_partner and target table JiekeXu_sz.t_partner. Using prior TABLE/MAP specification.

2025-05-23 17:59:52  WARNING OGG-01004  Aborted grouped transaction on JiekeXu_sz.t_partner, Database error 1 (OCI Error ORA-00001: unique constraint (JiekeXu_sz.P_t_partner) violated (status = 1), 
SQL <INSERT *+ RESTRICT_ALL_REF_CONS */ INTO "JiekeXu_sz"."t_partner" ("SEQUENCE_NO","FK_PARTNER","PK_CREDIT_PARTNER","FK_CREDIT_CC","FK_CREDIT_PARENT_PARTNER","FK_CREDIT_PARENT_CC","FK_CREDIT_RO
OT_PARTNER","FK_CREDIT_ROOT_CC","SHARE_PRICE_INFO") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9) RETURNING "DATA_CREATE" INTO :dl0>).

2025-05-23 17:59:52  WARNING OGG-01003  Repositioning to rba 2290370 in seqno 2020.

2025-05-23 17:59:52  WARNING OGG-01154  SQL error 1 mapping JiekeXu_sz.t_partner to JiekeXu_sz.t_partner OCI Error ORA-00001: unique constraint (JiekeXu_sz.P_t_partner) violated (status = 1), 
SQL <INSERT *+ RESTRICT_ALL_REF_CONS */ INTO "JiekeXu_sz"."t_partner" ("SEQUENCE_NO","FK_PARTNER","PK_CREDIT_PARTNER","FK_CREDIT_CC","FK_CREDIT_PARENT_PARTNER","FK_CREDIT_PARENT_CC","FK_CREDIT_RO
OT_PARTNER","FK_CREDIT_ROOT_CC","SHARE_PRICE_INFO") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9) RETURNING "DATA_CREATE" INTO :dl0>.

Source Context :
  SourceModule            : [er.replicat.errors]
  SourceID                : [er/replicat/reperrors.cpp]
  SourceMethod            : [repError]
  SourceLine              : [1754]
  ThreadBacktrace         : [15] elements
                          : [/goldengate/ogg19c/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/goldengate/ogg19c/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/goldengate/ogg19c/libgglog.so(_MSG_QualTableName_QualTableName(CSourceContext*, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBOb
jType)1> const&, CMessageFactory::MessageDisposition))]
                          : [/goldengate/ogg19c/replicat(ggs::er::ReplicatContext::repError(short, int, char const*, extr_ptr_def*, ggs::gglib::gglcr::CommonLCR const*, std_rec_hdr_def*, char*, ObjectMetadata*,
 bool))]
                          : [/goldengate/ogg19c/replicat()]
                          : [/goldengate/ogg19c/replicat(ggs::er::ReplicatContext::processRecord(ggs::gglib::gglcr::CommonLCR const*, ggs::gglib::gglcr::CommonLCR*, extr_ptr_def*&, extr_ptr_def*&, bool&, int&, 
bool, RepCsn&))]
                          : [/goldengate/ogg19c/replicat(ggs::er::ReplicatContext::processReplicatLoop())]
                          : [/goldengate/ogg19c/replicat(ggs::er::ReplicatContext::run())]
                          : [/goldengate/ogg19c/replicat()]
                          : [/goldengate/ogg19c/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/goldengate/ogg19c/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/goldengate/ogg19c/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/goldengate/ogg19c/replicat(main)]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/goldengate/ogg19c/replicat()]

2025-05-23 17:59:52  ERROR   OGG-01296  Error mapping from JiekeXu_sz.t_partner to JiekeXu_sz.t_partner.

********************************************************************

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友或同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！

——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.co/developer/user/5645107
——————————————————————————