【OGG 学习篇】从Oracle 11.2.0.4 复制到19c 单向复制

一、环境描述

DB环境：

 
ogg 进程说明

 
ogg 兼容性说明
参考链接：
https://www.oracle.com/middleware/technologies/fusion-certification.html
https://www.oracle.com/integration/goldengate/certifications/

我们这里使用的环境：ogg版本：21.3、DB：11.2.0.4 + 19c + rhel7 符合兼容性。

二、安装配置

1. 安装ogg 软件（源端、目标端）

• 安装ogg，版本：21.3.0.0.0。安装过程略。。。。

-- 创建子目录
GGSCI (ora11204) 3> create subdirs

Creating subdirectories under current directory /home/oracle/ogg

Parameter files                /home/oracle/ogg/dirprm: already exists
Report files                   /home/oracle/ogg/dirrpt: created
Checkpoint files               /home/oracle/ogg/dirchk: created
Process status files           /home/oracle/ogg/dirpcs: created
SQL script files               /home/oracle/ogg/dirsql: created
Database definitions files     /home/oracle/ogg/dirdef: created
Extract data files             /home/oracle/ogg/dirdat: created
Temporary files                /home/oracle/ogg/dirtmp: created
Stdout files                   /home/oracle/ogg/dirout: created


--各目录用途如下表：
名字	   用途
dirprm	   存放OGG参数各的配置信息
dirrpt	   存放进程报告文件
dirchk	   存放检查点文件
dirpcs	   存放进程状态文件
dirsql	   存放SQL脚本文件
dirdef	   存放DEFGEN工具生成的数据定义文件
dirdat	   存放Trail文件，也就是Capture进程捕获的日志文件
dirtmp	   当事物需要的内存超过已分配内存时，默认存储在这个目录	

2. ogg准备阶段

2.1 配置数据库用户（源端、目标端）

--在两个数据库上创建数据库用户：ogg
create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/tbs_ogg01.dbf' size 1000M autoextend on;
create user ogg identified by ogg default tablespace tbs_ogg;
grant dba,connect,resource,create table,create sequence to ogg;

-- 已经赋予DBA权限后，下面的可以不用执行
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg','*',TRUE);

PL/SQL procedure successfully completed.

2.2 配置数据库日志模式（源端）

--开启强制日志，否则nologging 的表无法同步
alter database force logging;

--开启数据库级别附加日志
alter database add supplemental log data;

--检查
select force_logging,supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

2.3 修改 GoldenGate 参数（源端、目标端）

alter system set enable_goldengate_replication = true scope = both;

2.4 配置 DDL 复制（源端）

• 不再需要执行ddl_setup 等一大堆脚本，只需要在抽取进程中配置DDL参数即可。

--在目录$OGG_HOME 下，创建文件 GLOBALS
[oracle@test-db160 ogg]$ vi GLOBALS
GGSCHEMA OGG

2.5 配置 sequence（源端、目标端）

--源端运行 sequence
[oracle@ora11204 ~]$ sqlplus / as sysdba
SQL> @sequence.sql
SQL> grant execute on ogg.updateSequence to ogg;
SQL> alter table sys.seq$ add supplemental log data (primary key) columns;

--添加目标端的 sequence
SQL> @sequence.sql
SQL> grant execute on ogg.replicateSequence to ogg;
SQL> grant execute on DBMS_STREAMS_ADM_UTL_INVOK to ogg;

2.6 配置 MGR（源端、目标端）

--1) 配置mgr.prm（源端）
[oracle@ora11204 ogg]$ ggsci
GGSCI (ora11204) 1> edit params mgr

PORT 7809
DYNAMICPORTLIST 7840-7910
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepdays 5

--以下参数只需在源端配置
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
USERID ogg@ORCL,PASSWORD ogg

--定义数据延迟的预警机制
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45


--2) 启动mgr
GGSCI (ora11204) 4> start mgr
Manager started.

GGSCI (ora11204) 5> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

3. GoldenGate 配置

• 整个复制可分为三大阶段：启动抓取、初始化数据、数据应用。

3.1 配置 Extract（源端）

--1) 开启存量表的附加日志
GGSCI > dblogin userid ogg@ORCL password ogg
ADD TRANDATA SXC.*

--查看表是否添加了附加日志
GGSCI> info trandata SXC.*
GGSCI> info trandata SXC.T1

--2) Extract 参数文件
GGSCI > edit params ext_tf

extract ext_tf
SETENV (ORACLE_SID="ORCL")
SETENV (NLS_LANG="AMERICAN_CHINA.AL32UTF8")
USERID ogg@ORCL, PASSWORD ogg
NUMFILES 5000
EXTTRAIL ./dirdat/tf
DISCARDFILE ./dirrpt/ext_tf.dsc , APPEND
DISCARDROLLOVER AT 2:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m
REPORTCOUNT EVERY 30 MINUTES, RATE
TRANLOGOPTIONS EXCLUDEUSER ogg
DYNAMICRESOLUTION
DDL &
INCLUDE OBJNAME SXC.*;
DDLOPTIONS ADDTRANDATA, GETAPPLOPS, GETREPLICATES, REPORT
TABLE SXC.*;
SEQUENCE SXC.*;

--3) 添加Extract 进程
GGSCI > ADD EXTRACT ext_tf, TRANLOG, BEGIN NOW
GGSCI > ADD EXTTRAIL ./dirdat/tf, EXTRACT ext_tf, MEGABYTES 100

3.2 配置 Pump（源端）

--1) Pump 参数文件
GGSCI > edit params pump_rf

EXTRACT pump_rf
RMTHOST 172.88.0.2, MGRPORT 7809
RMTTRAIL ./dirdat/rf
NUMFILES 5000
PASSTHRU
TABLE SXC.*;
SEQUENCE SXC.*; 

--2) 添加Pump进程
GGSCI > ADD EXTRACT pump_rf, EXTTRAILSOURCE ./dirdat/tf
GGSCI > ADD RMTTRAIL ./dirdat/rf, EXTRACT pump_rf, MEGABYTES 100

3.3 注册抽取进程

GGSCI > dblogin userid ogg@ORCL password ogg

GGSCI > register EXTRACT EXT_TF database

3.4 启动源端进程

--启动进程
GGSCI > start ext_tf
GGSCI > start pump_rf

--刷新 sequence
GGSCI > dblogin userid ogg@ORCL password ogg
FLUSH SEQUENCE SXC.*

--确认进程状态
GGSCI > info all

3.5 数据初始化

• 这里我们使用数据泵形式，进行数据初始化

--1) 获取并记录源生产库当前的 SCN 号 （1154678）
SQL> col CURRENT_SCN for 99999999999999
SQL> select current_scn from v$database; 

--2) 当前系统是否存在长事务？
col START_SCN for 99999999999999
col SCHEMANAME for a20
col MACHINE for a20
select
p.INST_ID,p.SPID,s.sid,s.SERIAL#,p.username,s.SCHEMANAME,s.sql_id,t.start_scn,t.START_TIME,t.status,MACHINE
from gv$transaction t,gv$process p,gv$session s
where s.taddr=t.addr and p.addr=s.paddr and t.start_scn <= &SCN1; 

--如果有，kill 掉长事务
SQL> ! kill -9 &SPID --操作系统 kill 掉 查到的 SPID 号


--3) 数据导出：以 FLASHBACK_SCN 为参数，使用 expdp 导出生产库数据
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_sxc%U.dmp logfile=expdp_sxc.log filesize=30G schemas=sxc flashback_scn=1154678

-- 拷贝到目标端
scp expdp_sxc01.dmp oracle@172.88.0.2:/home/oracle


--4). 数据导入：在目标库上使用 impdp 导入数据
impdp system/oracle directory=DIR_EXP dumpfile=expdp_sxc%U.dmp logfile=impdp_sxc.log


-- 5. 目标库禁用 job、触发器、外键约束 (也可以通过OGG参数实现)
--job(导入之前就提前设置好)
alter system set job_queue_processes=0 scope=both;

--FK
SELECT 'alter table '||owner||'.'||table_name||' disable constraint '|| constraint_name||';'
from dba_constraints
where constraint_type='R' and owner in ('SXC') AND STATUS<>'DISABLED';

--trigger
SELECT 'alter trigger ' || owner || '.' || trigger_name || ' disable;'
from dba_triggers
where owner in ('SXC') AND STATUS<>'DISABLED';

3.5 配置 Replicate（目标端）

--1) 配置 checkpoint 表
[oracle@test-db160 ogg]./ggsci
GGSCI > DBLOGIN USERID ogg@SXCDB, PASSWORD ogg
GGSCI > ADD CHECKPOINTTABLE OGG.CHECKPOINTTABLE

--如果添加错了，可以删除重建
GGSCI > delete checkpointtable OGG.CHECKPOINTTABLE

--2) 在目录$OGG_HOME 下，创建文件 GLOBALS
[oracle@test-db160 ogg]$ vi GLOBALS
CHECKPOINTTABLE OGG.CHECKPOINTTABLE


--3) 配置进程：rep_rf
GGSCI > edit params rep_rf

REPLICAT rep_rf
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
DDL &
INCLUDE MAPPED
--禁止trigger和外键约束，但是job还是需要手动禁止
DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DISCARDFILE ./dirrpt/rep_rf.dsc , APPEND
DISCARDROLLOVER AT 2:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
NUMFILES 5000
GROUPTRANSOPS 2000
MAXTRANSOPS 3000
ASSUMETARGETDEFS
DYNAMICRESOLUTION
MAP SXC.*,TARGET SXC.*;

--4) 添加Replicate进程：
GGSCI > ADD REPLICAT rep_rf, EXTTRAIL ./dirdat/rf,checkpointtable ogg.CHECKPOINTTABLE
GGSCI > start replicat rep_rf aftercsn 1154678    -- 前期记录的SCN

4. 同步测试

4.1 DML 测试

--源端：
SXC@ORCL> insert into t2 select * from t2;

2 rows created.

SXC@ORCL> commit;

Commit complete.

--目标端段：
SXC@sxcdb> select * from sxc.t2;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
T1                                                           TABLE
T2                                                           TABLE
T1                                                           TABLE
T2                                                           TABLE

4.2 DDL 测试

-- 源端，建表
SXC@ORCL> create table tt (id int);

Table created.

SXC@ORCL> insert into tt values (10);

1 row created.

SXC@ORCL> commit;

Commit complete.

SXC@ORCL> create table t5 as select * from tab;

Table created.


--目标端：
SXC@sxcdb> select * from tt;

     ID
----------
     10

-- CTAS方式，表同步创建了，但是数据并没有同步创建过来，只是复制了一个一模一样的语句,创建表的数据来源于自身库的对象
SXC@sxcdb> select * from t5;       

TNAME                       TABTYPE              CLUSTERID
------------------------------------------------------------------------------
CHECKPOINTTABLE             TABLE
CHECKPOINTTABLE_LOX         TABLE

5. 判断是否为集成模式

5.1 抽取进程

--查看抽取进程，有Oracle Integrated  Redo Logs关键字，说明是集成模式
GGSCI (ora11204) 3> info EXT_TF
Extract    EXT_TF    Last Started 2024-07-23 13:53   Status RUNNING
Checkpoint Lag       00:00:06 (updated 00:00:08 ago)
Process ID           19002
Log Read Checkpoint  Oracle Integrated Redo Logs    
                     2024-07-23 14:51:55
                     SCN 0.1160189 (1160189)

5.2 复制进程

-- 查看replicat进程信息没有 INTEGRATED 关键字，说明是经典模式
GGSCI (ora19c) 7> info REP_RF detail
Replicat   REP_RF    Last Started 2024-07-23 14:18   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           10510
Log Read Checkpoint  File ./dirdat/rf000000000
                     2024-07-23 14:33:09.000261  RBA 9376

总结：ogg 21.3版本，从上面信息可以看到：

1. 创建抽取进程时候，虽然没有加 integrated 关键字，但是仍然是集成模式。
2. 创建复制进程时候，没有加 integrated 关键字，复制进程还是经典模式，区别于抽取进程。

6. 错误统计

6.1 抽取进程启动错误Logmining server does not exist

-- 错误信息
2024-07-23T13:34:01.631+0800  ERROR   OGG-02022  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  Logmining server does not exist on this Oracle database.
2024-07-23T13:34:01.631+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  PROCESS ABENDING.

-- 解决办法：注册抽取进程
GGSCI > dblogin userid ogg@ORCL password ogg
GGSCI > register EXTRACT EXT_TF database

6.2 ogg 21c EXTRACT 进程无法正常启动报错 OGG-02912

-- 错误信息
2024-07-23T13:50:36.171+0800  ERROR   OGG-02912  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.
2024-07-23T13:50:36.171+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  PROCESS ABENDING.

-- 解决办法： 
在 MOS 中，文档 Doc ID 2304095.1 描述中，在 OGG 家目录下存在 prvtlmpg.plb 脚本，可以在源库执行 @prvtlmpg.plb 来解决。

-- 执行过程
SYS@ORCL> @prvtlmpg.plb

Oracle GoldenGate Workaround prvtlmpg

This script provides a temporary workaround for bug 17030189.
It is strongly recommended that you apply the official Oracle
Patch for bug 17030189 from My Oracle Support instead of using
this workaround.

This script must be executed in the mining database of Integrated
Capture. You will be prompted for the username of the mining user.
Use a double quoted identifier if the username is case sensitive
or contains special characters. In a CDB environment, this script
must be executed from the CDB$ROOT container and the mining user
must be a common user.

===========================  WARNING  ==========================
You MUST stop all Integrated Captures that belong to this mining
user before proceeding!
================================================================

Enter Integrated Capture mining user: ogg

Installing workaround...
No errors.
No errors.
No errors.
Installation completed.
SYS@ORCL>

6.3 复制进程SEQUENCE报错

-- 错误信息：从报错上可以看到SEQUENCE参数无效
2024-07-23T14:15:10.388+0800  ERROR   OGG-10144  Oracle GoldenGate Delivery for Oracle, rep_rf.prm:  (rep_rf.prm) line 20: Parameter [SEQUENCE] is not valid for this configuration.

-- 解决办法：删除SEQUENCE映射部分
GGSCI (ora19c) 6> view params REP_RF

REPLICAT rep_rf
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
DDL &
INCLUDE MAPPED
--禁止trigger和外键约束，但是job还是需要手动禁止
DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DISCARDFILE ./dirrpt/rep_rf.dsc , APPEND
DISCARDROLLOVER AT 2:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
NUMFILES 5000
GROUPTRANSOPS 2000
MAXTRANSOPS 3000
ASSUMETARGETDEFS
DYNAMICRESOLUTION
MAP SXC.*,TARGET SXC.*;
--SEQUENCE SXC.*,TARGET SXC.*;


-- 但实际sequence是正常同步的
2024-07-23T14:24:31.795+0800  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, rep_rf.prm:  The definition for table SXC.SEQ1 is obtained from the trail file.
2024-07-23T14:24:31.795+0800  INFO    OGG-06513  Oracle GoldenGate Delivery for Oracle, rep_rf.prm:  Resolving target sequence :SXC.SEQ1.

三、重新添加集成模式进程

• 这里是测试环境，默认主库没有操作，所以只需要删除原来的进程，添加新的进程即可，不需要重新初始化。

1. 清除原来进程

源端

--1) 停止进程
GGSCI (ora11204) 6> stop *
Sending STOP request to Extract group EXT_TF ...
Request processed.

Sending STOP request to Extract group PUMP_RF ...
Request processed.

--2) 取消注册
GGSCI > dblogin userid ogg@ORCL password ogg

GGSCI (ora11204 as ogg@ORCL) 11> unregister EXTRACT EXT_TF database
2024-07-23 15:16:33  INFO    OGG-01750  Successfully unregistered Extract group EXT_TF from database.

--3) 删除进程
GGSCI (ora11204) 7> delete EXT_TF
Extract group EXT_TF deleted.

GGSCI (ora11204) 8> delete PUMP_RF
Extract group PUMP_RF deleted.

--4) 删除队列文件
[oracle@ora11204 ogg21_ma]$ rm -rf dirdat/tf000000000
[oracle@ora11204 ogg21_ma]$ ll dirdat/
total 0
[oracle@ora11204 ogg21_ma]$
[oracle@ora11204 ogg21_ma]$ ll dirchk/
total 0

目标端

--1) 停止进程
GGSCI (ora19c) 9> stop *
Sending STOP request to Replicat group REP_RF ...
Request processed.

--2) 取消注册
GGSCI > dblogin userid ogg@SXCDB password ogg

GGSCI (ora19c as ogg@sxcdb) 14> delete checkpointtable OGG.CHECKPOINTTABLE
This checkpoint table may be required for other installations.  Are you sure you want to delete this checkpoint table? Y
Successfully deleted checkpoint table OGG.CHECKPOINTTABLE.

--3) 删除进程
GGSCI (ora19c as ogg@sxcdb) 16> delete REP_RF
Replicat group REP_RF deleted.

--4) 删除队列文件
[oracle@ora19c ogg21_ma]$ rm -rf dirdat/rf000000000
[oracle@ora19c ogg21_ma]$ ll dirdat/
total 0
[oracle@ora19c ogg21_ma]$ ll dirchk/
total 0
[oracle@ora19c ogg21_ma]$

2. 重新添加进程

源端

--1）添加集成模式的参数 （可选）
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1000, PARALLELISM 2)

GGSCI (ora11204) 4> view params EXT_TF

extract ext_tf
SETENV (ORACLE_SID="ORCL")
SETENV (NLS_LANG="AMERICAN_CHINA.AL32UTF8")
USERID ogg@ORCL, PASSWORD ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1000, PARALLELISM 2)
NUMFILES 5000
EXTTRAIL ./dirdat/tf
DISCARDFILE ./dirrpt/ext_tf.dsc , APPEND
DISCARDROLLOVER AT 2:00
WARNLONGTRANS 2h, CHECKINTERVAL 300s
REPORTCOUNT EVERY 30 MINUTES, RATE
TRANLOGOPTIONS EXCLUDEUSER ogg
DYNAMICRESOLUTION
DDL &
INCLUDE OBJNAME SXC.*;
DDLOPTIONS ADDTRANDATA, GETAPPLOPS, GETREPLICATES, REPORT
TABLE SXC.*;
SEQUENCE SXC.*;

--2) 添加Extract 进程，加上integrated关键字
GGSCI (ora11204) 5> ADD EXTRACT ext_tf,integrated TRANLOG, BEGIN NOW
Integrated Extract added.

GGSCI (ora11204) 6> ADD EXTTRAIL ./dirdat/tf, EXTRACT ext_tf, MEGABYTES 100
EXTTRAIL added.

--3) 添加Pump进程
GGSCI (ora11204) 7> ADD EXTRACT pump_rf, EXTTRAILSOURCE ./dirdat/tf
Extract added.

GGSCI (ora11204) 8> ADD RMTTRAIL ./dirdat/rf, EXTRACT pump_rf, MEGABYTES 100
RMTTRAIL added.

--4) 注册抽取进程
GGSCI (ora11204) 15>  dblogin userid ogg@ORCL password ogg
Successfully logged into database.

GGSCI (ora11204 as ogg@ORCL) 16> register EXTRACT EXT_TF database
2024-07-23 15:30:36  INFO    OGG-02003  Extract group EXT_TF successfully registered with database at SCN 1165239.

--5) 启动抽取、投递进程
GGSCI (ora11204) 11> start EXT_TF
Sending START request to Manager ...
Extract group EXT_TF starting.

GGSCI (ora11204) 12> start PUMP_RF
Sending START request to Manager ...
Extract group PUMP_RF starting.

目标端

--1) 配置 checkpoint 表
GGSCI (ora19c) 2> DBLOGIN USERID ogg@SXCDB, PASSWORD ogg
Successfully logged into database.

GGSCI (ora19c as ogg@sxcdb) 3> ADD CHECKPOINTTABLE OGG.CHECKPOINTTABLE
Successfully created checkpoint table OGG.CHECKPOINTTABLE.

--2) 添加集成复制的参数(可选)
GGSCI (orcl) 8> edit params REP_RF
DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1000, PARALLELISM 2)

GGSCI (ora19c) 4> view params REP_RF

REPLICAT rep_rf
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100, PARALLELISM 2)
DDL &
INCLUDE MAPPED
--禁止trigger和外键约束，但是job还是需要手动禁止
DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DISCARDFILE ./dirrpt/rep_rf.dsc , APPEND
DISCARDROLLOVER AT 2:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
NUMFILES 5000
GROUPTRANSOPS 2000
--MAXTRANSOPS 3000
ASSUMETARGETDEFS
DYNAMICRESOLUTION
MAP SXC.*,TARGET SXC.*;
--SEQUENCE SXC.*,TARGET SXC.*;

--3) 添加复制进程，加上integrated关键字
GGSCI (ora19c as ogg@sxcdb) 4> ADD REPLICAT rep_rf, integrated,EXTTRAIL ./dirdat/rf,checkpointtable ogg.CHECKPOINTTABLE
Integrated Replicat added.

--4) 注册复制进程（可选）
GGSCI (ora19c as ogg@sxcdb) 5> register replicat rep_rf database
2024-07-23 15:27:09  INFO    OGG-02528  Replicat group REP_RF successfully registered with database as inbound server OGG$REP_RF.

--5) 启动复制进程
GGSCI > start replicat rep_rf 
Sending START request to Manager ...
Replicat group REP_RF starting.

--6) 查看复制进程
GGSCI (ora19c as ogg@sxcdb) 9> info rep_rf
Replicat   REP_RF    Initialized  2024-07-23 15:26   Status STOPPED
INTEGRATED                                                  -- 可以看到是 INTEGRATED Replicat
Checkpoint Lag       00:00:00 (updated 00:06:58 ago)
Log Read Checkpoint  File ./dirdat/rf000000000
                     First Record  RBA 0