测试: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).