【背景】
　　Oracle Database 11.2.0.3，單機，測試環境
　　測試證實Oracle GoldenGate的命令ADD SCHEMATRANDATA能否自動處理附加日志，而無需人工干預

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

【結論一】 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查看的結果
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’;
–不改動表結構信息
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

【結論二】 ADD SCHEMATRANDATA能自動因應表結構的變更而快速地自動調整，多個唯一鍵的情況下，則記錄多個唯一鍵
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’;

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

總結

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

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。