【新炬網路名師大講堂】GoldenGate的ADD SCHEMATRANDATA命令研究

背景】
　　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’;