测试:OGG初始化同步表,源端抽取进程scn<源端事务的start_scn时,这个变化是否会同步到目标库中？

时间：2021-03-04 00:49:38

一、测试目标

疑问,OGG初始化同步表,源端抽取进程开始抽取的scn<源端事务的start_scn时,这个变化是否会同步到目标库中？

二、实验测试

如下进行测试！

session 1

SQL> conn yz/yz

SQL> create table t1(id int,cc_name varchar2(20));

SQL> insert into t1 values(1,'test');

1 row created.

OGG源端开始抽取t1表;

GGSCI (t1 as ogg@t1) 10> add trandata yz.t1

2020-11-18 10:49:21  WARNING OGG-06439  No unique key is defined for table T1. All viable columns will be used to represent the key,
 but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-11-18 10:49:21  INFO    OGG-15130  No key found for table YZ.T1.  All viable columns will be logged.

2020-11-18 10:49:21  WARNING OGG-00706  Failed to add supplemental log group on table YZ.T1 due to ORA-00054: resource busy and
 acquire with NOWAIT specified or timeout expired

 SQL ALTER TABLE "YZ"."T1" ADD SUPPLEMENTAL LOG GROUP "GGS_89400" ("ID","CC_NAME") ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.

!测试有变！本次测试需要测试3个场景！已测试add trandata 是否存在影响！

session1  源端非add trandata,目标端启动时不限制;

session2  源端add trandata,目标端启动时不限制;

session3  源端add trandata,目标端启动时限制;

session 2

SQL> conn yz/yz

SQL> create table t2(id int,cc_name varchar2(20));

GGSCI (t1 as ogg@t1) 11> add  trandata yz.t2

SQL> insert into t2 values(1,'test');

1 row created.

session 3

SQL> conn yz/yz

SQL> create table t3(id int,cc_name varchar2(20));

GGSCI (t1 as ogg@t1) 11> add  trandata yz.t3

SQL> insert into t3 values(1,'test');

1 row created.

查询事务启动scn

select  s.sid,s.username,s.event,sql.sql_text,v.status,v.START_TIME,v.START_SCN

 from v$session s,v$TRANSACTION v,v$sql sql

  where s.SADDR=v.SES_ADDR and s.PREV_SQL_ID=sql.sql_id;

       SID USERNAME   EVENT                         SQL_TEXT                                 STATUS           START_TIME            START_SCN

---------- ---------- ----------------------------- ---------------------------------------- ---------------- -------------------- ----------

         1 YZ         SQL*Net message from client   insert into t1 values(1,'test')          ACTIVE           11/18/20 10:31:29       3571762

        43 YZ         SQL*Net message from client   insert into t2 values(1,'test')          ACTIVE           11/18/20 11:00:25       3572598

        46 YZ         SQL*Net message from client   insert into t3 values(1,'test')          ACTIVE           11/18/20 11:01:03       3572788

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    3573095

OGG源端开始抽取

重启抽取进程

投递一样追加！重启

复制进程注意：本次测试需要！

map yz.t1,target bak_yz.t1;

map yz.t2,target bak_yz.t2;

map yz.t3,target bak_yz.t3,filter(@GETENV('TRANSACTION','CSN')>3573095);

目标端同步表结构

[oracle@t2 ~]$ sqlplus bak_yz/yz

SQL> create table t1(id int,cc_name varchar2(20));

SQL> create table t2(id int,cc_name varchar2(20));

SQL> create table t3(id int,cc_name varchar2(20));

启动复制进程

GGSCI (t2) 9> start repa

此时,session 1,2,3都进行commit提交！

SQL> select * from t1;

        ID CC_NAME

---------- --------------------

         1 test

SQL> select * from t2;

        ID CC_NAME

---------- --------------------

         1 test

SQL> select * from t3;

        ID CC_NAME

---------- --------------------

         1 test

？？？得出如下结论：

1.OGG这个版本Version 19.1.0.0.4,OGG是根据事务结束scn来判断这个事务是否需要同步,不存在早期版本初始化同步scn < 源端start_scn时，存在数据不一致的问题！

2.OGG同步的表即使不加add trandata 例如insert操作还是能正常捕获到记录并进行同步;当然某些情况无法获取数据同步update操作！(测试insert or delete正常)

2020-11-18 11:26:36  WARNING OGG-01004  Aborted grouped transaction on BAK_YZ.T1, Database error 1403 (No data found).

相关文章