【OGG学习篇】ogg 同步19c alter 改分区表

[TOC]

测试目的

• 从12.2 开始，可以将非分区表，通过alert table modify 直接转换为分区表，大大省略了之前繁琐的步骤。那么如果我们环境中，存在OGG同步链路，那么源端执行该操作，目标端会同步吗？这里我们简单测试一下。

一、环境描述

DB环境：

这里，为了测试方便，我们选择源端、目标端在同一个库，映射不同的schema。

二、安装配置

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

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

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;

exec dbms_goldengate_auth.grant_admin_privilege('ogg','*',TRUE);

复制

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 配置 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@SXCDB,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) 在目录$OGG_HOME 下，创建文件 GLOBALS
[oracle@ora19c ogg]$ vi GLOBALS
GGSCHEMA OGG


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

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

extract ext_tf
SETENV (ORACLE_SID="SXCDB")
SETENV (NLS_LANG="AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
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.*;

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

3.2 不配置 Pump（源端）

• 我们源和目标在同一主机，只需要一个普通extract进程就可以，所以，我们这里不需要配置pump进程。

3.3 注册抽取进程

GGSCI > dblogin userid ogg@SXCDB password ogg

GGSCI > register EXTRACT EXT_TF database
复制

3.4 启动源端进程

--启动进程
GGSCI > start ext_tf

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

3.5 数据初始化

• 目标端的数据初始话常用三种方式：OGG、expdp\impdp、RMAN。当前采用 expdp\impdp 的方式。
• 这里我们仅仅是测试转分区表操作，所以不做初始化了。

4 配置 Replicate（目标端）

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

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

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


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

REPLICAT rep
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
DDL &
INCLUDE MAPPED
-- 设置过滤、忽略DDL的错误
-- DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
--禁止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 YWW.*;

--4) 添加Replicate进程：
GGSCI > ADD REPLICAT rep, EXTTRAIL ./dirdat/tf,checkpointtable ogg.CHECKPOINTTABLE
GGSCI > start replicat rep
复制

5. 同步测试

5.1 同步 测试

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

Table created.

SXC@SXCDB> insert into tt2 values (10);

1 row created.

SXC@SXCDB> commit;

Commit complete.

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

Table created.


--目标端：
YWW@sxcdb> select * from tt2;

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

复制

• 链路同步正常。

5.2 自动分区表

--1) 源端创建自动分区表
SXC@sxcdb> create table test_part2
(
  ID NUMBER(20) not null,
  REMARK VARCHAR2(10),
  create_time DATE
) tablespace users
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
 (partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

Table created.

SXC@sxcdb> insert into test_part2 values (1,'sxc',sysdate);

1 row created.

SXC@sxcdb> commit;

Commit complete.

SXC@sxcdb> select * from test_part2;

        ID REMARK                          CREATE_TIME
---------- -----------------------------------------------------------
         1 sxc                             2024-08-30 18:53:20

SXC@sxcdb> col TABLE_NAME for a10
SXC@sxcdb> col PARTITION_NAME for a30
SXC@sxcdb> select table_name,PARTITION_NAME from dba_tab_partitions where table_owner='SXC';

TABLE_NAME PARTITION_NAME
---------- ------------------------------
TEST_PART2 PART_T01
TEST_PART2 SYS_P381


--2) 目标端检查
YWW@sxcdb> select * from test_part2;

        ID REMARK                          CREATE_TIME
---------- -----------------------------------------------------------
         1 sxc                             2024-08-30 18:53:20

YWW@sxcdb> select table_name,PARTITION_NAME from dba_tab_partitions where table_owner='YWW';

TABLE_NAME PARTITION_NAME
---------- ------------------------------
TEST_PART2 PART_T01
TEST_PART2 SYS_P401

复制

• 目标端同步创建了自动分区表，而且新的数据同步到目标端后，自动分区的名称和源端是不一样的。

5.3 19c alter修改分区表

• 这里，我们先改成传统的普通分区表，不使用自动分区

--1) 源端创建普通表
SXC@sxcdb> CREATE TABLE test1 (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2));

Table created.

SXC@sxcdb> insert into test1 values (1,'30-dec-2022',1000);

1 row created.

SXC@sxcdb> insert into test1 values (2,'30-dec-2023',2000);

1 row created.

SXC@sxcdb> commit;

Commit complete.

SXC@sxcdb> select * from test1;

    INV_NO INV_DATE               INV_AMT
---------- ------------------- ----------
         1 0030-12-20 22:00:00       1000
         2 0030-12-20 23:00:00       2000

SXC@sxcdb> create unique index idx1 on test1(inv_no);

Index created.

SXC@sxcdb> create index idx2 on test1(inv_date);

Index created.


--2) 目标端检查：普通表正常同步
YWW@sxcdb> select * from test1;

    INV_NO INV_DATE               INV_AMT
---------- ------------------- ----------
         1 0030-12-20 22:00:00       1000
         2 0030-12-20 23:00:00       2000

YWW@sxcdb> select index_name from user_indexes where table_name='TEST1';

INDEX_NAME
--------------------------------------------------
IDX1
IDX2


--3) 源端改分区表
SXC@sxcdb> ALTER TABLE sxc.TEST1 MODIFY
PARTITION BY RANGE (inv_date) (
PARTITION test1_P2022 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2023 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2024 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_PMAX VALUES LESS THAN (MAXVALUE)
) ONLINE
UPDATE INDEXES
(
sxc.idx1 GLOBAL,
sxc.idx2 LOCAL
);

Table altered.


--4) 源端检查:已改成分区表，idx1为global index，idx2为local index
SXC@sxcdb> select table_name,partition_name from user_tab_partitions where table_name='TEST1';

TABLE_NAME PARTITION_NAME
---------- ------------------------------
TEST1      TEST1_P2022
TEST1      TEST1_P2023
TEST1      TEST1_P2024
TEST1      TEST1_PMAX

SXC@sxcdb> select index_name,uniqueness,tablespace_name from user_indexes where table_name='TEST1';

INDEX_NAME UNIQUENESS         TABLESPACE_NAME
---------- ------------------ ------------------------------------------------------------
IDX1       UNIQUE             USERS
IDX2       NONUNIQUE


--5) 目标端检查
YWW@sxcdb> select table_name,partition_name from user_tab_partitions where table_name='TEST1';

no rows selected   --> 未同步

--6) ogg 复制进程ABENDED
GGSCI (ora19c) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT_TF      00:00:00      00:00:02
REPLICAT    ABENDED     REP         00:00:18      00:00:08

复制

• 源端转分区的操作，未被同步到目标端，ogg 复制进程ABENDED。

5.4 问题排查

① 查看错误日志

-- 错误信息
2024-08-31T01:37:42.292+0800  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  DDL found, operation [ALTER TABLE sxc.TEST1 MODIFY
                              PARTITION BY RANGE (inv_date) (
                              PARTITION test1_P2022 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
                              PARTITION test1_P2023 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
                              PARTITION test1_P2024 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
                              PARTITION test1_PMAX VALUES LESS THAN (MAXVALUE)
                              ) ONLINE
                              UPDATE INDEXES
                              (
                              sxc.idx1 GLOBAL,
                              sxc.idx2 LOCAL
                              ) (size 463)], start SCN [2757668], commit SCN [2757672] instance [ (1)], DDL seqno [0], marker seqno [0].
2024-08-31T01:37:42.292+0800  INFO    OGG-00487  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  DDL operation included [INCLUDE OBJNAME SXC.*], optype [ALTER], objtype [TABLE], objowner "SXC", objname "TEST1".
2024-08-31T01:37:42.324+0800  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext_tf.prm:  Writing DDL operation to extract trail file.
2024-08-31T01:37:43.708+0800  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rep.prm:  Setting current schema for DDL operation to schema YWW.
2024-08-31T01:37:44.530+0800  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, rep.prm:  Fatal error executing DDL replication: error [Error code [1418], ORA-01418: specified index does not exist
                              ], no error handler present.
2024-08-31T01:37:44.530+0800  INFO    OGG-02932  Oracle GoldenGate Delivery for Oracle, rep.prm:  Reading /opt/app/ogg/ogg21_ma/dirdat/tf000000000, current SEQNO 0, RBA 72,010, 2 records, m_file_seqno = 0, m_file_rba = 73,753.
2024-08-31T01:37:44.530+0800  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep.prm:  PROCESS ABENDING.
2024-08-31T01:37:52.546+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
         
         
  --分析：
  ogg errlog 抛出错误信息，ORA-01418: specified index does not exist，同步执行alert 改分区表操作报错
  
  
                            
复制

② 查看同步报错的SQL

• 这里，我们通过添加SQLEXEC 获取目标端同步的SQL语句

--1) 修改参数文件
GGSCI (ora19c) 2> edit params REP

REPLICAT rep
SETENV (NLS_LANG = "AMERICAN_CHINA.AL32UTF8")
USERID ogg@SXCDB, PASSWORD ogg
SQLEXEC "alter session set sql_trace=true"
DDL &
INCLUDE MAPPED
-- 设置过滤、忽略DDL的错误
-- DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
--禁止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 YWW.*;

GGSCI (ora19c) 3> stop REPLICAT REP
Sending STOP request to Replicat group REP ...
Request processed.


GGSCI (ora19c) 4> start REPLICAT REP
Sending START request to Manager ...
Replicat group REP starting.

--2) 源端重复上面的操作（重新建表，改分区表）

--3) trace 文件中可以看到目标端的SQL语句：
[oracle@ora19c trace]$ tkprof sxcdb_ora_12289.trc sxcdb_ora_12289.txt sys=no

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

 ALTER TABLE "YWW"."TEST1" MODIFY
PARTITION BY RANGE (inv_date) (
PARTITION test1_P2022 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2023 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2024 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_PMAX VALUES LESS THAN (MAXVALUE)
) ONLINE
UPDATE INDEXES
(
sxc.idx1 GLOBAL,
sxc.idx2 LOCAL
) /* GOLDENGATE_DDL_REPLICATION */


--4) 目标端手动执行：同样抛出错误 ORA-01418: specified index does not exist
YWW@sxcdb>
 ALTER TABLE "YWW"."TEST1" MODIFY
PARTITION BY RANGE (inv_date) (
PARTITION test1_P2022 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2023 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_P2024 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION test1_PMAX VALUES LESS THAN (MAXVALUE)
) ONLINE
UPDATE INDEXES
(
sxc.idx1 GLOBAL,
sxc.idx2 LOCAL
 12  ) /* GOLDENGATE_DDL_REPLICATION */
 13  ;
sxc.idx1 GLOBAL,
    *
ERROR at line 10:
ORA-01418: specified index does not exist

复制

5.5 思考？

1. 从上面的trace文件中，可以看到目标端可以同步执行alter 转分区表操作，但是执行报错了，原因应该是ogg 在同步UPDATE INDEXES 部分仍然包含了sxc. 用户信息，而我们同步是remap schema的，目标端没有sxc.用户，所以报错。

   那么，如果我们是两台机器，不remap schema，那么同步转分区表的时候，索引部分是不是就没问题了？？? 是可以的。

2. 源端执行alert 转分区操作，命令中不加上sxc. 用户信息，会是什么结果呢？？？我们测试一下

③ 测试过程：改成自动分区表

--1) 源端删除重建表
SXC@sxcdb> CREATE TABLE test2 (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2));
SXC@sxcdb> insert into test2 values (1,'2024-01-01',1000);
SXC@sxcdb> insert into test2 values (2,'2024-02-01',2000);
SXC@sxcdb> insert into test2 values (3,'2024-03-01',2000);
SXC@sxcdb> commit;
SXC@sxcdb> create unique index idx3 on test2(inv_no);
SXC@sxcdb> create index idx4 on test2(inv_date);

--2) 源端改成分区表，不加schema.
SXC@sxcdb> ALTER TABLE TEST2 MODIFY
PARTITION BY RANGE (inv_date) INTERVAL (numtoyminterval(1, 'month'))
 (partition test1_P202401 values less than(to_date('2024-02-01', 'yyyy-mm-dd')))
  ONLINE
UPDATE INDEXES
(
idx3 GLOBAL,
idx4 LOCAL
);

Table altered.

--3) 目标端再次查看:也同步改成分区表了，idx3为global index，idx4为local index
YWW@sxcdb> select * from TEST2;

    INV_NO INV_DATE               INV_AMT
---------- ------------------- ----------
         1 2024-01-01 00:00:00       1000
         2 2024-02-01 00:00:00       2000
         3 2024-03-01 00:00:00       2000

YWW@sxcdb> select table_name,PARTITION_NAME,INTERVAL from user_tab_partitions where table_name='TEST2';

TABLE_NAME PARTITION_NAME                 INTERV
---------- ------------------------------ ------
TEST2      SYS_P461                       YES
TEST2      SYS_P462                       YES
TEST2      TEST1_P202401                  NO


YWW@sxcdb>  select index_name,uniqueness,tablespace_name from user_indexes where table_name='TEST2';

INDEX_NAME UNIQUENESS         TABLESPACE_NAME
---------- ------------------ ------------------------------------------------------------
IDX3       UNIQUE             USERS
IDX4       NONUNIQUE                       


--4) 目标端新的trace 中可以看到：
ALTER TABLE "YWW"."TEST2" MODIFY
PARTITION BY RANGE (inv_date) INTERVAL (numtoyminterval(1, 'month'))
 (partition test1_P202401 values less than(to_date('2024-02-01', 'yyyy-mm-dd')))
  ONLINE
UPDATE INDEXES
(
idx3 GLOBAL,
idx4 LOCAL
) /* GOLDENGATE_DDL_REPLICATION */
复制

结论：源端alert不加schema.后，目标端同步创建了自动分区表。