ITPub博客

首页 > 数据库 > Oracle > Supplemental log VS add trandata on OGG for oracle

Supplemental log VS add trandata on OGG for oracle

原创 Oracle 作者:fanhongjie 时间:2012-01-12 16:51:54 0 删除 编辑

转自:http://tomszrp.itpub.net/post/11835/520322

Oracle的补充日志分为三个级别
Database level
Schema Level(注意版本哦)
Table Level

[@more@]Database 级别的补充日志测试

Oracle补充日志(Supplemental logging)特性因其作用的不同可分为以下几种:最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique index),支持外键(foreign key)。
LOBs, LONGS, and ADTs等类型的列无法使用补充日志。
最小(Minimal)补充日志开启后可以使得logmnr工具支持链式行,簇表和索引组织表。使用Goldnegate也必须要求打开最小补充日志,可以通过以下SQL检查最小补全日志是否已经开启:
SELECT supplemental_log_data_min FROM v$database;
若结果返回YES或IMPLICIT则说明已开启最小补全日志,当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

下面分别针对如下几种场景测试一些打开primary key,unique index两种补充日志后, Oracle redo 中记录的信息
(1) 场景一:有PK
(2) 场景二:无PK,UI
(3) 场景三:无PK,有1个限定not null的唯一索引
(4) 场景四:无PK,有1个不限定not null的唯一索引
(5) 场景五:无PK,有2个限定not null的唯一索引
(6) 场景六:无PK,有1个限定not null的唯一索引、1个不限定not null的唯一索引、1个普通索引
(7) 场景七:无PK,UK,有普通index(等同场景2)

准备工作
打开支持主键(primary key),支持唯一键(unique index)的补充日志
SQL> alter database add supplemental log data (primary key,unique index) columns;
Database altered.
切换一组日志让其生效
SQL> alter system switch logfile;
System altered.
确认补充日志是否打开
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui,
supplemental_log_data_fk, supplemental_log_data_all
from v$database;

SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
IMPLICIT YES YES NO NO

SQL>
节下来启用Logminer进行redo 挖掘(过程略)

场景一:有PK
create table test1( a int, b int, c varchar2(32), d date, e char(1), f int);
alter table test1 add constraint pk_test1 primary key (a);
insert into test1 values(1,100,'a',sysdate,'1',1000);
commit;
update test1 set b=b+1;
commit;

使用LOGMNR工具分析针对表test1的DML操作,可以看到REDO中记录的SQL形式如下:
insert into "STUDY"."TEST1"("A","B","C","D","E","F") values ('1','100','a',TO_DATE('2011-07-07 11:18:54', 'yyyy-mm-dd hh24:mi:ss'),'1','1000');
update "STUDY"."TEST1" set "B" = '101' where "A" = '1' and "B" = '100' and ROWID = 'AAAM87AAGAAAAOuAAA';

其中针对update语句where字句后分别记录了主键值、被修改字段的值和原行的ROWID。

接着我针对某有PK,UI的表做个测试,参阅场景二
(2) 场景二:无PK,UI
create table test2( a int, b int, c varchar2(32), d date, e char(1), f int);
insert into test2 values(2,200,'b',sysdate,'2',2000);
commit;
update test2 set b=b+1;
commit;

create table test2( a int, b int, c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST2"("A","B","C","D","E","F") values ('2','200','b',
TO_DATE('2011-07-07 11:27:34', 'yyyy-mm-dd hh24:mi:ss'),'2','2000');
update "STUDY"."TEST2" set "B" = '201' where "A" = '2' and "B" = '200' and "C" = 'b' and
"D" = TO_DATE('2011-07-07 11:27:34', 'yyyy-mm-dd hh24:mi:ss') and "E" = '2' and "F" = '2000'
and ROWID = 'AAAM9IAAGAAAAQWAAA';
当没有主键和唯一约束的情况下,where子句后记录了所有列值和ROWID。
显然,当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下,开启补全日志可能导致重做日志总量大幅提高

(3) 场景三:无PK,只有一个non-null unique index
create table test3( a int not null, b int not null, c varchar2(32), d date, e char(1), f int);
create unique index ui_test3 on test3(a, b);
insert into test3 values(3,300,'c',sysdate,'3',3000);
commit;
update test3 set b=b+1,c='C';
commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:
create table test3( a int not null, b int not null, c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST3"("A","B","C","D","E","F") values ('3','300','c',TO_DATE('2011-07-07 11:20:41', 'yyyy-mm-dd hh24:mi:ss'),'3','3000');
update "STUDY"."TEST3" set "B" = '301', "C" = 'C' where "A" = '3' and "B" = '300' and "C" = 'c' and ROWID = 'AAAM89AAGAAAAO+AAA';
可以看到,在有唯一索引并且限定not null的情况,在where字句后分别记录了唯一索引列值、被修改字段的值和原行的ROWID。这个情况基本和有主键是一样的。


(4) 场景四:无PK,有1个不限定not null的唯一索引
在场景四中,是在场景三的基础上,假设表上无PK,但有一个唯一索引,但不限定列not null,看看会是什么情况
create table test4( a int , b int , c varchar2(32), d date, e char(1), f int);
create unique index ui_test4 on test4(a, b);
insert into test4 values(4,400,'d',sysdate,'4',4000);
commit;
update test4 set b=b+1,c='D';
commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:
create table test4( a int , b int , c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST4"("A","B","C","D","E","F") values ('4','400','d',TO_DATE('2011-07-07 11:22:43', 'yyyy-mm-dd hh24:mi:ss'),'4','4000');
update "STUDY"."TEST3" set "B" = '401', "C" = 'D' where "A" = '4' and "B" = '400' and "C" = 'd' and ROWID = 'AAAM86AAGAAAAO+AAA';
可以看到,如以上SQL所示,在存在唯一索引(不限定not null)的情况下where子句后仍记录了所有列和ROWID

(5) 场景五:无PK,有2个限定not null的唯一索引
create table test5( 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_test51 on test5(a, b);
create unique index ui_test52 on test5(a, c, f);
insert into test5 values(51,501,'e1',sysdate,'5',5100);
insert into test5 values(52,502,'e2',sysdate,'5',5200);
commit;
update test5 set d=sysdate;
commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:
create table test5( a int not null, b int not null, c varchar2(32) not null, d date, e char(1), f int not null);
insert into "STUDY"."TEST5"("A","B","C","D","E","F") values ('51','501','e1',TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss'),'5','5100');
insert into "STUDY"."TEST5"("A","B","C","D","E","F") values ('52','502','e2',TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss'),'5','5200');
update "STUDY"."TEST5" set "D" = TO_DATE('2011-07-07 11:23:40', 'yyyy-mm-dd hh24:mi:ss') where "A" = '51' and "B" = '501'
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博客 ” ,链接:http://blog.itpub.net/73920/viewspace-1057102/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-22

  • 博文量
    112
  • 访问量
    657475