转自:http://tomszrp.itpub.net/post/11835/520322
Oracle的补充日志分为三个级别
Database level
Schema Level(注意版本哦)
Table Level
and "D" = TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss') and ROWID = 'AAAM8/AAGAAAAPOAAA';
update "STUDY"."TEST5" set "D" = TO_DATE('2011-07-07 11:23:40', 'yyyy-mm-dd hh24:mi:ss') where "A" = '52' and "B" = '502'and "D" = TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss') and ROWID = 'AAAM8/AAGAAAAPOAAB';
这个经过反复测试,发现与UI创建的先后顺序没有关系,根据Oracle write redo 最小化原则,选择的是列最找的(未有官方说明)
(6) 场景六:无PK,有1个限定not null的唯一索引、1个不限定not null的唯一索引、1个普通索引
create table test6( a int not null, b int not null, c varchar2(32), d date, e char(1), f int not null);
create unique index ui_test61 on test6(a);
create unique index ui_test62 on test6(a, c, f);
create index inx_test63 on test5(b);
insert into test6 values(61,601,'f1',sysdate,'6',6100);
insert into test6 values(62,602,'f2',sysdate,'6',6200);
commit;
update test6 set d=sysdate;
commit;
使用LOGMNR分析可以发现,REDO中的SQL记录如下:
create table test6( a int not null, b int not null, c varchar2(32), d date, e char(1), f int not null);
insert into "STUDY"."TEST6"("A","B","C","D","E","F") values ('61','601','f1',TO_DATE('2011-07-07 11:24:42', 'yyyy-mm-dd hh24:mi:ss'),'6','6100');
insert into "STUDY"."TEST6"("A","B","C","D","E","F") values ('62','602','f2',TO_DATE('2011-07-07 11:24:42', 'yyyy-mm-dd hh24:mi:ss'),'6','6200');
update "STUDY"."TEST6" set "D" = TO_DATE('2011-07-07 11:24:45', 'yyyy-mm-dd hh24:mi:ss') where "A" = '61' and "D" = TO_DATE('2011-07-07 11:24:42', 'yyyy-mm-dd hh24:mi:ss') and ROWID = 'AAAM9CAAGAAAAPmAAA';
update "STUDY"."TEST6" set "D" = TO_DATE('2011-07-07 11:24:45', 'yyyy-mm-dd hh24:mi:ss') where "A" = '62' and "D" = TO_DATE('2011-07-07 11:24:42', 'yyyy-mm-dd hh24:mi:ss') and ROWID = 'AAAM9CAAGAAAAPmAAB';
这个情况和场景三是一样
(7) 场景七:无PK,UK,有普通index(等同场景2)
create table test7( a int, b int, c varchar2(32), d date, e char(1), f int);
create unique index inx_test7 on test7 (a, b, c);
insert into test7 values(7,700,'g',sysdate,'7',7000);
commit;
update test7 set d=sysdate;
commit;
使用LOGMNR分析可以发现,REDO中的SQL记录如下:
create table test7( a int, b int, c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST7"("A","B","C","D","E","F") values ('7','700','g',TO_DATE('2011-07-07 11:25:01', 'yyyy-mm-dd hh24:mi:ss'),'7','7000');
update "STUDY"."TEST7" set "D" = TO_DATE('2011-07-07 11:25:04', 'yyyy-mm-dd hh24:mi:ss') where "A" = '7' and "B" = '700' and "C" = 'g' and "D" = TO_DATE('2011-07-07 11:25:01', 'yyyy-mm-dd hh24:mi:ss') and "E" = '7' and "F" = '7000' and ROWID = 'AAAM9GAAGAAAAQGAAA';
这个情况和场景二是一样的。
简单的小结一下,经过测试,上诉测试结果基本上和Oracle官方手册上介绍的一样,只是针对有多个唯一约束的情况下,取的
列最少的唯一索引,是规则还是巧合,需要进一步确认。
下面再看一下GoldenGate add trandata(增加Table Level补充日志的情况)
对于有PK和无PK/UI的情况基本每什么争议,本文主要是针对表上有多个限定not null唯一索引的情况下,GoldenGate到底是选择哪个唯一索引来唯一标识一行呢?
创建一个测试表
create table test8( a int not null, b int not null, c varchar2(32) not null, d date, e char(1), f int not null);
create unique index ui_test81 on test8(a, b);
create unique index ui_test82 on test8(a, c, f);
增加trandata
GGSCI (ZHANGRP-CN) 2> add trandata study.test8
Logging of supplemental redo data enabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 3>
确认一下使用了哪个唯一索引
SQL> select * from dba_log_groups where owner='STUDY' and table_name='TEST8';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
-------- -------------- ------------ ------------------- ----------- ----------
STUDY GGS_TEST8_54159 TEST8 USER LOG GROUP ALWAYS USER NAME
SQL> select * from dba_log_group_columns
2 where log_group_name in (select log_group_name from dba_log_groups where owner='STUDY' and table_name='TEST8')
3 order by position;
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
------- ---------------- ------------ ------------- ---------- ------
STUDY GGS_TEST8_54159 TEST8 A 1 LOG
STUDY GGS_TEST8_54159 TEST8 B 2 LOG
SQL>
这个时候选择的是第一个唯一约束,用的是列少的那个
下面我重建这两个唯一索引,调换一下创建顺序,先创建ui_test82,再建ui_test81
drop index ui_test81;
drop index ui_test82;
create unique index ui_test82 on test8(a, c, f);
create unique index ui_test81 on test8(a, b);
SQL> select object_name,object_id from user_objects where object_name in ('UI_TEST81','UI_TEST82');
OBJECT_NAME OBJECT_ID
------------ ----------
UI_TEST81 54163
UI_TEST82 54162
SQL>
可以看到,确实是先创建的ui_test82
下面重新add trandata看看
GGSCI (ZHANGRP-CN) 4> delete trandata study.test8
Logging of supplemental redo log data disabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 5> add trandata study.test8
Logging of supplemental redo data enabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 6>
确认一下使用了哪个唯一索引
SQL> select * from dba_log_groups where owner='STUDY' and table_name='TEST8';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
-------- -------------- ------------ ------------------- ----------- ----------
STUDY GGS_TEST8_54159 TEST8 USER LOG GROUP ALWAYS USER NAME
SQL> select * from dba_log_group_columns
2 where log_group_name in (select log_group_name from dba_log_groups where owner='STUDY' and table_name='TEST8')
3 order by position;
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
------- ---------------- ------------ ------------- ---------- ------
STUDY GGS_TEST8_54159 TEST8 A 1 LOG
STUDY GGS_TEST8_54159 TEST8 B 2 LOG
SQL>
这个时候选择的还是第一个唯一约束,看来好像和创建顺序没有关系,那么是不是和列的多少有关系呢?
其实也不是,再看一个测试
drop index ui_test81;
drop index ui_test82;
create unique index ui_test82 on test8(f);
create unique index ui_test81 on test8(a, b);
再次重新添加trandata
GGSCI (ZHANGRP-CN) 7> delete trandata study.test8
Logging of supplemental redo log data disabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 8> add trandata study.test8
Logging of supplemental redo data enabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 9>
确认一下使用了哪个唯一索引
SQL> select * from dba_log_groups where owner='STUDY' and table_name='TEST8';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
-------- -------------- ------------ ------------------- ----------- ----------
STUDY GGS_TEST8_54159 TEST8 USER LOG GROUP ALWAYS USER NAME
SQL> select * from dba_log_group_columns
2 where log_group_name in (select log_group_name from dba_log_groups where owner='STUDY' and table_name='TEST8')
3 order by position;
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
------- ---------------- ------------ ------------- ---------- ------
STUDY GGS_TEST8_54159 TEST8 A 1 LOG
STUDY GGS_TEST8_54159 TEST8 B 2 LOG
SQL>
哈哈,还是不变化,还用的索引名为ui_test81,而不是ui_test82.
莫非有索引名有关系?哈哈,让你猜对了。
drop index ui_test81;
drop index ui_test82;
create unique index indx_test82 on test8(a,c,f);
create unique index ui_test81 on test8(a, b);
再次重新添加trandata
GGSCI (ZHANGRP-CN) 10> delete trandata study.test8
Logging of supplemental redo log data disabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 11> add trandata study.test8
Logging of supplemental redo data enabled for table STUDY.TEST8.
GGSCI (ZHANGRP-CN) 12>
确认一下使用了哪个唯一索引
SQL> select * from dba_log_groups where owner='STUDY' and table_name='TEST8';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
-------- -------------- ------------ ------------------- ----------- ----------
STUDY GGS_TEST8_54159 TEST8 USER LOG GROUP ALWAYS USER NAME
SQL> select * from dba_log_group_columns
2 where log_group_name in (select log_group_name from dba_log_groups where owner='STUDY' and table_name='TEST8')
3 order by position;
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
------- ---------------- ------------ ------------- ---------- ------
STUDY GGS_TEST8_54159 TEST8 A 1 LOG
STUDY GGS_TEST8_54159 TEST8 C 2 LOG
STUDY GGS_TEST8_54159 TEST8 F 3 LOG
SQL>
这就和文档上对应起来了。参阅OGG手册中"How Oracle GoldenGate determines the kind of row identifier to use"部分说明当然了,goldengate本身选择哪个唯一索引,对OGG来说不重要!只要他能唯一标识到一行即可。 小结 参考上面的测试,可以看到database level和golendase add trandata针对多个non-null唯一约束的情况处理机制是不一样的,
为了保障goldengate能正确的识别到一行,建议对OGG环境还是通过
ADD TRANDATA或
ADD SCHEMATRANDATA(注意版本哦)
来增加对象级的补充日志!
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/73920/viewspace-1057102/ ,如需转载,请注明出处,否则将追究法律责任。