OGG DELETEROWEXISTS UPDATEROWEXISTS 冲突说明

OGG DELETEROWEXISTS UPDATEROWEXISTS 冲突说明

COMPARECOLS and RESOLVECONFLICT DELETEROWEXISTS UPDATEROWEXISTS

RESOLVECONFLICT 解决ogg数据同步时遇到的数据不一致性冲突：

数据强一制性需求：

我们不能在replicate中配置遇到同步错误时忽略跳过，这会导致源端和目标端的数据不一致：
reperror default,discard
REPERROR (-1, IGNORE)

对于OGG的RESOLVECONFLICT的几种情况：

INSERTROWEXISTS UPDATEROWMISSING DELETEROWMISSING 这三种冲突的情况很好理解

对于DELETEROWEXISTS UPDATEROWEXISTS这两种冲突的情况，第一次解读时带着疑问：删除的时候行存在，更新的时候行存在不是正常的情况吗？怎么会有冲突？

正常情况下，删除的时候行存在，更新的时候行存在是没有冲突的，执行操作就是了，那有没有异常的情况呢？答案是有的，就是数据的强一致性要求但是数据不一致的情景：

情景1：数据一致，不存在冲突

对于主键ID=1的update和delete操作，不存在删除或更新时的冲突(id是主键)

-- 源表COG.T1
id num date 
1  99  2021-11-11 17:26:47
-- 目标表gz.b1
id num date 
1  99  2021-11-11 17:26:47

情景2：数据不一致，没有进行比较，就不会有冲突

对于主键ID=1的update和delete操作，如果replicate时没有配置COMPARECOLS，会忽略掉目标表的值，不会进行检测，不会发生删除或更新时的冲突(id是主键)

-- 源表COG.T1
id num date 
1  99  2021-11-11 17:26:47
-- 目标表gz.b1
id num date 
1  88  2021-09-09 17:26:47

情景3：数据不一致，进行比较了，就有冲突

对于主键ID=1的update和delete操作，如果replicate时配置了COMPARECOLS，会检测num、date的值是否有冲突，如果不一致发生删除或更新时会有冲突(id是主键)

-- 源表COG.T1
id num date 
1  99  2021-11-11 17:26:47
-- 目标表gz.b1
id num date 
1  88  2021-09-09 17:26:47

官方的解释如下：

UPDATEROWEXISTS
An updated row exists on the target, but one or more columns have a before image in the trail that is different from the current value in the database.

目标上存在更新的行，但一列或多列在跟踪中有一个不同于数据库中当前值的before image。
before image：解释为源表update之前的值，会和目标表未更新之前的值做比较，执行数据强一致性检查。
例：在情景3：执行SQL: update cog.t1 set num=200 where id=1;
在目标表上执行检查：
1 是否存在id=1的主键对应的行的记录，
2 检查目标表num是否等于99 num=99?(before image)
3 (目标表num=88),会报发生冲突
总结：UPDATEROWEXISTS冲突就是行存在，但是其它列值的数据不一致(强一致性检查)

DELETEROWEXISTS
A deleted row exists in the target, but one or more columns have a before image in the trail that is different from the current value in the database.

目标中存在被删除的行，但一列或多列在跟踪中有一个不同于数据库中当前值的before image

RESOLVECONFLICT 语法：

Syntax
RESOLVECONFLICT (
{INSERTROWEXISTS | UPDATEROWEXISTS | UPDATEROWMISSING | 
 DELETEROWEXISTS | DELETEROWMISSING} 
( {DEFAULT | resolution_name},
 {USEMAX (resolution_column) | USEMAXEQ (resolution_column) | 
  USEMIN (resolution_column) | USEMINEQ (resolution_column) | 
  USEDELTA | DISCARD | OVERWRITE | IGNORE} 
 ) 
[, COLS (column[,...])]
)

主要解决以下三种情况：

 ,RESOLVECONFLICT(INSERTROWEXISTS,(DEFAULT,OVERWRITE))
 ,RESOLVECONFLICT(DELETEROWMISSING,(DEFAULT,DISCARD))
 ,RESOLVECONFLICT(UPDATEROWMISSING,(DEFAULT,OVERWRITE))

在COMPARECOLS条件的时候 必须定义DELETEROWEXISTS，UPDATEROWEXISTS 主键行存在，但是compare的列存在冲突的情况的处理方法，否则会replicate进程会abend终止

-- replicate中配置
,COMPARECOLS (ON UPDATE KEYINCLUDING(I2,D1),ON DELETE KEYINCLUDING(I2,D1) )
,RESOLVECONFLICT(UPDATEROWEXISTS,(max_d1_method,USEMAX(D1),COLS(D1)),(max_I2_method,USEMAX(I2),COLS(I2)),(DEFAULT,OVERWRITE))
,RESOLVECONFLICT(DELETEROWEXISTS,(DEFAULT,OVERWRITE))

COMPARECOLS UPDATEROWEXISTS DELETEROWEXISTS生效条件(重要)：

-- 1，源端的(列的)原值(update之前的值，不论是不是要update的列)，和目标端的当前值不一致（冲突）；
-- 2，如果compare的列有update： 队列文件中的值(要update的值)，和当前目标端的值，取最大值；
-- 3，如果compare的列没有update： 源表的值，和当前目标端的值，取最大值；

配置使用COMPARECOLS时注意事项(重要)：

1 源端抽取进程：附加日志必须最少附加compare的列
alter table cog.t1 add supplemental log group group_a(OBJECT_ID) always;
or
alter table cog.t1 add supplemental log data(all) columns;
2 源端抽取进程：必须最少配置GETBEFORECOLS compare的列
table cog.t1,GETBEFORECOLS ( ON UPDATE KEYINCLUDING (I2,D1),ON DELETE KEYINCLUDING (I2,D1));
or
table cog.t1,GETBEFORECOLS ( ON UPDATE ALL,ON DELETE ALL);

USEMAX 等说明：

USEMAX | USEMAXEQ | USEMIN | USEMINEQ | USEDELTA | DISCARD | OVERWRITE | IGNORE 对应的操作说明：
USEMAX (resolution_column) 

USEMAX 取最大值
 for INSERTROWEXISTS and UPDATEROWEXISTS 
 ,RESOLVECONFLICT(UPDATEROWEXISTS,(use_max_i2,USEMAX(I2)))
 -- #COMPARECOLS的时候，至少compare的列要加入附加日志传输 
 -- #可以用于比较的列的类型: NUMERIC DATE TIMESTAMP CHAR/NCHAR VARCHAR/ NVARCHAR
 ,COMPARECOLS (ON UPDATE ALL,ON DELETE ALL)
 ,RESOLVECONFLICT(UPDATEROWEXISTS,(DEFAULT,USEMAX(I2)))
 ,COMPARECOLS (ON UPDATE KEYINCLUDING (I2,D1,D2,N1,N2),ON DELETE KEYINCLUDING (I2,D1,D2,N1,N2))
 ,RESOLVECONFLICT(UPDATEROWEXISTS,(DEFAULT,USEMAX(I2)))

USEMAXEQ --取最大或者等于的值 

USEDELTA -- 仅用于数值类型，取在源表的差值增量运算
USEDELTA 使用说明：假设源表是100 目标表是200  
源表的值更新为95，源表的差值是95-100=-5
应用到目标表的时候：值是200+(-5)=195

配置示例：

说明：部署的配置(抽取，pump，复制进程部署到同一主机)：

– 抽取进程 ex0

extract ex0
userid c##ggs@CDB,password ggs
setenv(ORACLE_HOME=/u01/app/oracle/product/db)
setenv(ORACLE_SID=ORCL)
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/ex0.dsc,APPEND,MEGABYTES 1000
WARNLONGTRANS 2h,CHECKINTERVAL 5m
DBOPTIONS ALLOWUNUSEDCOLUMN
exttrail ./dirdat/e0
SOURCECATALOG pdb2
table cog.t1,GETBEFORECOLS ( ON UPDATE ALL,ON DELETE ALL);

– pump进程 pd0

extract pd0 
passthru
rmthost 1.1.1.91,mgrport 7500,compress
rmttrail ./dirdat/p0
SOURCECATALOG pdb2
table cog.t1;

– 复制进程 rp0

replicat rp0
userid c##ggs@pdb2,password ggs
setenv(ORACLE_HOME=/u01/app/oracle/product/db)
setenv(ORACLE_SID=ORCL)
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
NOHANDLECOLLISIONS
reperror (default,abend)
discardfile ./dirrpt/rp0.dsc,append,megabytes 50
ALLOWNOOPUPDATES
GETTRUNCATES
map pdb2.cog.t1,target pdb2.gz.b1
 ,RESOLVECONFLICT(INSERTROWEXISTS,(DEFAULT,OVERWRITE))
 ,RESOLVECONFLICT(DELETEROWMISSING,(DEFAULT,DISCARD))
 ,RESOLVECONFLICT(UPDATEROWMISSING,(DEFAULT,OVERWRITE))
 ,COMPARECOLS (ON UPDATE KEYINCLUDING(I2,D1),ON DELETE KEYINCLUDING(I2,D1) )
 ,RESOLVECONFLICT(UPDATEROWEXISTS,(max_d1_method,USEMAX(D1),COLS(D1)),(max_I2_method,USEMAX(I2),COLS(I2)),(DEFAULT,OVERWRITE))
;

实验日志

DELETEROWMISSING INSERTROWEXISTS UPDATEROWMISSING 日志：
– 1 DELETEROWMISSING 测试日志

-- 删除时 目标表记录不存在 
-- ogg 告警日志：
WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26787: The row with key ("I1") = (6510) does not exist in table GZ.B1
        ORA-01403: no data found) while processing the record at SEQNO 1, RBA 2322 in Integrated mode. REPLICAT will retry in Direct mode.
WARNING OGG-01004 rp0.prm:  Aborted grouped transaction on PDB2.GZ.B1, Database error 1403 (No data found).
WARNING OGG-01003 rp0.prm:  Repositioning to rba 2322 in seqno 1.
WARNING OGG-01154 rp0.prm:  SQL error 1403 mapping PDB2.COG.T1 to PDB2.GZ.B1 No data found.
ERROR   OGG-01296 rp0.prm:  Error mapping from PDB2.COG.T1 to PDB2.GZ.B1.
INFO    OGG-02333 rp0.prm:  Reading /u11/ogg/dirdat/p0000000001, current RBA 2,322, 1 records, m_file_seqno = 1, m_file_rba = 2,579.
ERROR   OGG-01668 rp0.prm:  PROCESS ABENDING.

view report rp0 报告显示错误：
WARNING OGG-02544  Unhandled error (ORA-26787: The row with key ("I1") = (6510) does not exist in table GZ.B1
        ORA-01403: no data found) while processing the record at SEQNO 1, RBA 2322 in Integrated mode. REPLICAT will retry in Direct mode.
WARNING OGG-01004  Aborted grouped transaction on PDB2.GZ.B1, Database error 1403 (No data found).
WARNING OGG-01003  Repositioning to rba 2322 in seqno 1.
WARNING OGG-01154  SQL error 1403 mapping PDB2.COG.T1 to PDB2.GZ.B1 No data found.

– 2 INSERTROWEXISTS 测试日志

-- 插入时，目标表已经存在行
WARNING OGG-02544  Unhandled error (ORA-26799: unique constraint GZ.SYS_C0011037 violated for table GZ.B1 with column values ("I1") = (6502)
ORA-00001: unique constraint (GZ.SYS_C0011037) violated) while processing the record at SEQNO 1, RBA 9757 in Integrated mode. REPLICAT will retry in Direct mode.

– 3 UPDATEROWMISSING 测试日志

WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26787: The row with key ("I1") = (6501) does not exist in table GZ.B1
ORA-01403: no data found) while processing the record at SEQNO 1, RBA 11890 in Integrated mode. REPLICAT will retry in Direct mode.
WARNING OGG-01919 rp0.prm:  Missing RESOLVECONFLICT for SQL error 1,403.
WARNING OGG-01004 rp0.prm:  Aborted grouped transaction on PDB2.GZ.B1, Database error 1403 (No data found).
WARNING OGG-01003 rp0.prm:  Repositioning to rba 11890 in seqno 1.
WARNING OGG-01154 rp0.prm:  SQL error 1403 mapping PDB2.COG.T1 to PDB2.GZ.B1 No data found.
ERROR   OGG-01296 rp0.prm:  Error mapping from PDB2.COG.T1 to PDB2.GZ.B1.
INFO    OGG-02333 rp0.prm:  Reading /u11/ogg/dirdat/p0000000001, current RBA 11,890, 16 records, m_file_seqno = 1, m_file_rba = 12,059.
ERROR   OGG-01668 rp0.prm:  PROCESS ABENDING.

– 4 UPDATEROWEXISTS DELETEROWEXISTS 测试日志：

WARNING OGG-01919 rp0.prm:  Missing RESOLVECONFLICT for SQL error 1,403.
WARNING OGG-01004 rp0.prm:  Aborted grouped transaction on PDB2.GZ.B1, Database error 1403 (No data found).
WARNING OGG-01003 rp0.prm:  Repositioning to rba 6727 in seqno 0.
WARNING OGG-01154 rp0.prm:  SQL error 1403 mapping PDB2.COG.T1 to PDB2.GZ.B1 No data found.
ERROR   OGG-01296 rp0.prm:  Error mapping from PDB2.COG.T1 to PDB2.GZ.B1.
INFO    OGG-02333 rp0.prm:  Reading /u11/ogg/dirdat/p0000000000, current RBA 6,727, 13 records, m_file_seqno = 0, m_file_rba = 6,979.
ERROR   OGG-01668 rp0.prm:  PROCESS ABENDING.

WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26951: table "GZ"."B1" has column data type not supported by conflict resolution
 ORA-26786: A row with key ("I1") = (6501) exists but has conflicting column(s) "D1", "I2" in table GZ.B1
 ORA-01403: no data found) while processing the record at SEQNO 0, RBA 5859 in Integrated mode. REPLICAT will retry in Direct mode.
INFO    OGG-08549  Oracle GoldenGate Capture for Oracle, ex0.prm:  17 records processed as of 2021-04-11 18:25:41.235068 (rate 0, delta 0).
WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26951: table "GZ"."B1" has column data type not supported by conflict resolution
 ORA-26786: A row with key ("I1") = (6501) exists but has conflicting column(s) "I2" in table GZ.B1
 ORA-01403: no data found) while processing the record at SEQNO 0, RBA 6230 in Integrated mode. REPLICAT will retry in Direct mode.
INFO    OGG-08549  Oracle GoldenGate Capture for Oracle, ex0.prm:  19 records processed as of 2021-04-11 20:31:01.611752 (rate 0, delta 0).
WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26951: table "GZ"."B1" has column data type not supported by conflict resolution
 ORA-26786: A row with key ("I1") = (6501) exists but has conflicting column(s) "D1", "I2" in table GZ.B1
 ORA-01403: no data found) while processing the record at SEQNO 0, RBA 6600 in Integrated mode. REPLICAT will retry in Direct mode.
INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param rp0.
INFO    OGG-08549  Oracle GoldenGate Capture for Oracle, ex0.prm:  21 records processed as of 2021-04-11 20:36:49.705521 (rate 0, delta 0).
WARNING OGG-02544 rp0.prm:  Unhandled error (ORA-26786: A row with key ("I1") = (6501) exists but has conflicting column(s) "D1", "I2" in table GZ.B1
 ORA-01403: no data found) while processing the record at SEQNO 0, RBA 6727 in Integrated mode. REPLICAT will retry in Direct mode.