【背景】
　　Oracle Database 11.2.0.3，單機，測試環(huán)境
　　測試證實Oracle GoldenGate的命令A(yù)DD SCHEMATRANDATA能否自動處理附加日志，而無需人工干預(yù)

【總結(jié)論】
1、ADD SCHEMATRANDATA操作相關(guān)命令即可觸發(fā)Oracle Database自動記錄相應(yīng)schema下對應(yīng)的表
2、ADD SCHEMATRANDATA選擇的邏輯主鍵順序為：主鍵->多個唯一鍵->大對象以外的全字段；多個唯一鍵則全記錄，與是否有非空約束無關(guān)。
3、ADD SCHEMATRANDATA能在不對表作DDL操作的情況下自動觸發(fā)數(shù)據(jù)庫表記錄邏輯主鍵，當(dāng)因索引約束增刪而導(dǎo)致邏輯主鍵變更時，ADD SCHEMATRANDATA能快速自動調(diào)整，無需干預(yù)
4、ADD SCHEMATRANDATA比ADD TRANDATA記錄更多的日志，但多記錄的僅為唯一索引約束對應(yīng)的字段，影響較小
5、ADD SCHEMATRANDATA不會觸發(fā)視圖dba_log_groups以及dba_log_group_columns變更
6、ADD SCHEMATRANDATA預(yù)計能處理絕大部分的情況，除了下述極端情況
時間點1 創(chuàng)建表TAB
時間點2 創(chuàng)建表TAB的非空唯一約束IND_UI
時間點3 對表TAB做INSERT以及UPDATE操作，其中的UPDATE操作不涉及后來主鍵IND_PK對應(yīng)字段，因此不被記錄
時間點4 創(chuàng)建表TAB的主鍵IND_PK
時間點5 GoldenGate抽取進程獲取數(shù)據(jù)字典信息，選擇IND_PK對應(yīng)字段為邏輯主鍵
時間點6 GoldenGate抽取進程處理時間點3的UPDATE記錄，發(fā)現(xiàn)IND_PK對應(yīng)字段不在數(shù)據(jù)庫日志中，導(dǎo)致異常
這種情況及時開啟SUPPLEMENTAL_LOG_DATA_PK也沒用，原因：記錄非空的唯一約束IND_UI已滿足SUPPLEMENTAL_LOG_DATA_PK需求。

【結(jié)論一】 ADD SCHEMATRANDATA能在不對表做DDL操作的情況下自動處理
GGSCI (HAREDBA03) 3> dblogin USERID ogg password AACAAAAAAAAAAAHAKBFJIELJDBFAKFGJ, encryptkey default
Successfully logged into database.
GGSCI (HAREDBA03) 4>
GGSCI (HAREDBA03) 4> ADD SCHEMATRANDATA crabbit
2014-04-01 11:49:42 INFO OGG-01788 SCHEMATRANDATA has been added on schema crabbit.
GGSCI (HAREDBA03) 5> info SCHEMATRANDATA crabbit
2014-04-01 12:20:53 INFO OGG-01785 Schema level supplemental logging is enabled on schema CRABBIT.
GGSCI (HAREDBA03) 6> info trandata crabbit.*
Logging of supplemental redo log data is disabled for table CRABBIT.SSSS.
Logging of supplemental redo log data is disabled for table CRABBIT.TESTSSS.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_A.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_C.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_NPK_NUK.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_PK_UK.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_SEQ_A.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_SEQ_B.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_SEQ_C.
Logging of supplemental redo log data is disabled for table CRABBIT.test_table_seq_g.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_SEQ_E.
Logging of supplemental redo log data is disabled for table CRABBIT.TEST_TABLE_UK1.
–
SQL> select OWNER,TABLE_NAME,LOG_GROUP_NAME,LOG_GROUP_TYPE from dba_log_groups where owner=’CRABBIT’;
no rows selected
SQL> select OWNER,TABLE_NAME,LOG_GROUP_NAME,COLUMN_NAME from dba_log_group_columns where owner=’CRABBIT’;
no rows selected
ora idxdesc CRABBIT.TEST_TABLE_SEQ_E
SQL> desc CRABBIT.TEST_TABLE_SEQ_E
Name Null? Type
—————————————– ——– —————————-
KEY_ID NUMBER
UNI_ID NUMBER
NUM NUMBER
DAT DATE
STR VARCHAR2(10)
SQL> !ora idxdesc TEST_TABLE_SEQ_E CRABBIT
Session altered.
INDEX_NAME INDEX_COL INDEX_TYPE PAR
——————————– —————————— ———————- —
CRABBIT.UK_TEST_TABLE_SEQ_E1 UNI_ID NORMAL-UNIQUE NO
SQL> update CRABBIT.TEST_TABLE_SEQ_E set STR=’upd2′ where STR=’upd1′;
1 row updated.
SQL> commit;
Commit complete.
–通過logmnr查看的結(jié)果
SCN XID SEG_OWNER TABLE_NAME SQL_REDO
———- ————— —————- ——————————– ————————————————————————————————————————
4956911 9.19.1911 CRABBIT TEST_TABLE_SEQ_E update “CRABBIT”.”TEST_TABLE_SEQ_E” set “STR” = ‘upd2′ where “UNI_ID” = ’1′ and “STR” = ‘upd1′ and ROWID = ‘AAASk8AAEAAAAT1AAA’;
–不改動表結(jié)構(gòu)信息
SQL> select created,last_ddl_time,sysdate from dba_objects where owner=’CRABBIT’ and object_name=’TEST_TABLE_SEQ_E’;
CREATED LAST_DDL_TIME SYSDATE
——————- ——————- ——————-
2014-04-01 09:16:16 2014-04-01 09:18:36 2014-04-01 12:27:17

【結(jié)論二】 ADD SCHEMATRANDATA能自動因應(yīng)表結(jié)構(gòu)的變更而快速地自動調(diào)整，多個唯一鍵的情況下，則記錄多個唯一鍵
SQL> !cat test_sql.sql
create table crabbit.test_table_seq_g(
key_id number,
uni_id number,
num number,
dat date,
str varchar(10)
);
insert into crabbit.test_table_seq_g values(1,1,1,sysdate,’upd0′);
commit;
update crabbit.test_table_seq_g set str=’upd1′ where str=’upd0′;
commit;
create unique index crabbit.uk_test_table_seq_g1 on crabbit.test_table_seq_g (“KEY_ID”);
update crabbit.test_table_seq_g set str=’upd2′ where str=’upd1′;
commit;
create unique index crabbit.uk_test_table_seq_g2 on crabbit.test_table_seq_g (“UNI_ID”,”NUM”);
update crabbit.test_table_seq_g set str=’upd3′ where str=’upd2′;
commit;
alter table crabbit.test_table_seq_g modify (key_id number not null);
update crabbit.test_table_seq_g set str=’upd4′ where str=’upd3′;
commit;
alter table crabbit.test_table_seq_g add constraint pk_test_table_seq_g primary key (“UNI_ID”,”NUM”) using index crabbit.uk_test_table_seq_g2;
update crabbit.test_table_seq_g set str=’upd5′ where str=’upd4′;
commit;
SQL> @test_sql
Table created.
1 row created.
Commit complete.
1 row updated.
Commit complete.
Index created.
1 row updated.
Commit complete.
Index created.
1 row updated.
Commit complete.
Table altered.
1 row updated.
Commit complete.
Table altered.
1 row updated.
Commit complete.
SCN XID SEG_OWNER TABLE_NAME SQL_REDO
———- ————— —————- ——————————– ————————————————————————————————————————
4958950 3.4.1978 CRABBIT TEST_TABLE_SEQ_G insert into “CRABBIT”.”TEST_TABLE_SEQ_G”(“COL 1″,”COL 2″,”COL 3″,”COL 4″,”COL 5″) values (HEXTORAW(‘c102′),HEXTORAW(‘c102′),HEXTORAW(‘c102′),HEXTORAW(’787204010e1525′),HEXTORAW(’75706430′));
4958953 7.23.1954 CRABBIT TEST_TABLE_SEQ_G update “CRABBIT”.”TEST_TABLE_SEQ_G” set “COL 5″ = HEXTORAW(’75706431′) where “COL 1″ = HEXTORAW(‘c102′) and “COL 2″ = HEXTORAW(‘c102′) and “COL 3″ = HEXTORAW(‘c102′) and “COL 4″ = HEXTORAW(’787204010e1525′) and “COL 5″ = HEXTORAW(’75706430′) and ROWID = ‘AAASlPAAEAAAAQOAAA’;_seq_g (“KEY_ID”);
4958971 1.26.1897 CRABBIT TEST_TABLE_SEQ_G update “CRABBIT”.”TEST_TABLE_SEQ_G” set “COL 5″ = HEXTORAW(’75706432′) where “COL 1″ = HEXTORAW(‘c102′) and “COL 5″ = HEXTORAW(’75706431′) and ROWID = ‘AAASlPAAEAAAAQOAAA’;
4958988 6.3.2138 CRABBIT TEST_TABLE_SEQ_G update “CRABBIT”.”TEST_TABLE_SEQ_G” set “COL 5″ = HEXTORAW(’75706433′) where “COL 1″ = HEXTORAW(‘c102′) and “COL 2″ = HEXTORAW(‘c102′) and “COL 3″ = HEXTORAW(‘c102′) and “COL 5″ = HEXTORAW(’75706432′) and ROWID = ‘AAASlPAAEAAAAQOAAA’;
4959006 2.33.1982 CRABBIT TEST_TABLE_SEQ_G update “CRABBIT”.”TEST_TABLE_SEQ_G” set “STR” = ‘upd4′ where “KEY_ID” = ’1′ and “UNI_ID” = ’1′ and “NUM” = ’1′ and “STR” = ‘upd3′ and ROWID = ‘AAASlPAAEAAAAQOAAA’;
4959020 10.22.1709 CRABBIT TEST_TABLE_SEQ_G update “CRABBIT”.”TEST_TABLE_SEQ_G” set “STR” = ‘upd5′ where “UNI_ID” = ’1′ and “NUM” = ’1′ and “STR” = ‘upd4′ and ROWID = ‘AAASlPAAEAAAAQOAAA’;

? ? ??新炬網(wǎng)絡(luò)定期推出“大師講堂”專業(yè)IT技術(shù)知識分享，內(nèi)容涉及Oracle數(shù)據(jù)庫、性能測試、軟件自動化測試等，與工作在技術(shù)前線的小伙伴們一起探討實踐中出現(xiàn)的技術(shù)難題，提供有效解決方案，大家通過交流共同成長。

總結(jié)

以上是生活随笔為你收集整理的GoldenGate的ADD SCHEMATRANDATA命令研究的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。