轉自：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(注意版本哦)

     來增加物件級的補充日誌!

Supplemental log VS add trandata on OGG for oracle

相關文章