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’;

      新炬网络定期推出“大师讲堂”专业IT技术知识分享，内容涉及Oracle数据库、性能测试、软件自动化测试等，与工作在技术前线的小伙伴们一起探讨实践中出现的技术难题，提供有效解决方案，大家通过交流共同成长。