【转】GolenGate实现历史表
有些客户需要记录关键表（源表）的修改历史，即，将源表什么数据何时做了什么修改记录到历史表，以便将来恢复、审计、追踪或作为ETL的源。历史表也称为跟踪表、审计表、historical data或transaction-history table。传统实现方法是打开源表DB审计或在源表上建立insert, update和delete触发器，审计或触发器将DML历史记录到历史表，缺点是影响生产系统，审计或触发器开销大，生产系统不得不添加历史表，而且需要开发。有没有不影响甚产系统，又不需要开发的方法呢？有，就是用GoldenGate的参数InsertAllRecords实现。
历史表通常大致结构是：
源数据，
DML时间，
transaction序号，
DML在transaction中的顺序，
DML类型，
数据是update before image还是after image。
我们以GoldenGate Oracle -> Oracle复制来介绍实现方法。
源端我们建立两张源表：
源表有PK
create table t_pk (
n number primary key,
c char(10),
v varchar2(10)
);
源表无PK
create table t_no_pk (
c char(10),
v varchar2(10)
);
目标端端我们建立两张历史表：
源表有PK对应的历史表
create table t_pk_history (
n number,
c char(10),
v varchar2(10),
COMMITTIMESTAMP timestamp,
CSN number,
seq number,
OPTYPE varchar2(20),
BEFOREAFTERINDICATOR char(6)
);
源表无PK对应的历史表
create table t_no_pk_history (
c char(10),
v varchar2(10),
COMMITTIMESTAMP timestamp,
CSN number,
seq number,
OPTYPE varchar2(20),
BEFOREAFTERINDICATOR char(6)
);
源端
GGSCI > Add TranData scott.t_pk 此命令错误，见下面解释。
GGSCI > Add TranData scott.t_no_pk
t_no_pk没有PK，GG会把所有字段的SUPPLEMENTAL LOG打开。
t_pk有PK，GG只把PK字段n的SUPPLEMENTAL LOG打开，这会加速一般复制，却对update语句在历史表中的表现有影响，即，当修改一部分非PK字段时，例如，两个非PK字段v和c，只修改字段v时，Oracle DB Log只记PK字段n和字段v的修改，不记字段c，这就造成历史表中字段c的before image和after image都是NULL，我们在看历史表时，就不容易确定字段c的NULL是原有的呢，还是没有更新字段c造成的。为了解决这个问题，熟悉GoldenGate的人会想到Extract参数NoCompressUpdates，不幸的是这参数不支持Oracle DB，见《Reference Guide》，不过为了保险，我还是把它写到Extract参数文件里。天无绝人之路，下面命令打开所有字段的SUPPLEMENTAL LOG后，就能保证没有被修改的非PK字段before image和after image记录入历史表：
GGSCI > Add TranData scott.t_pk, Cols (n, c, v), NoKey
上面命令也可以这么写：
GGSCI > Add TranData scott.t_pk, Cols (c, v)
你会发现Cols (c, v)中没有PK字段n，不用担心，GoldenGate会自动把PK字段n加进去。
源端Extract参数exta.prm：
Extract extA
Userid goldengate, Password goldengate
RmtHost 192.168.19.133, MgrPort 7809
RmtTrail ./dirdat/ra
NoCompressDeletes
NoCompressUpdates
GetUpdateBefores
Table scott.*;
参数说明：
NoCompressDeletes：无论所有字段的SUPPLEMENTAL LOG是否打开，默认有PK的表在delete时只记录PK字段的值，造成历史表中其他字段都记为NULL，有时我们希望知道被删除的所有字段的值，解决办法是参数Extract添加参数NoCompressDeletes，这参数支持Oracle DB。
NoCompressUpdates：支持DB2 LUW and DB2 z/OS, Teradata version 12 or later, SQL Server, Sybase，不支持Oracle DB。实际上，此参数对Oracle DB有时有效有时无效，加上这个参数只是为了保险和通用。
GetUpdateBefores：把Update的before image也写入trail file。如果不加这个参数，则只有after imag写入trail file，这会加速一般复制，却造成历史表无法记录before image。
目标端Replicat参数repa.prm：
Replicat repa
UserId goldengate, Password goldengate
AssumeTargetDefs
DiscardFile ./dirrpt/repa.dsc, Purge
GetUpdateBefores
Map scott.t_pk, Target scott.t_pk_history, &
ColMap ( UseDefaults , &
COMMITTIMESTAMP = @Date ( "YYYY-MM-DD HH:MI:SS.FFFFFF", "YYYY-MM-DD HH:MI:SS.FFFFFF", @GetEnv ( "GGHEADER", "COMMITTIMESTAMP" ) ), &
CSN = @GetEnv ( "TRANSACTION", "CSN" ), &
seq = @Compute ( @GetEnv ( "GGHEADER", "LOGRBA" ) * 10000000000 + @GetEnv ( "GGHEADER", "LOGPOSITION" ) ) , &
OPTYPE = @GetEnv ( "GGHEADER", "OPTYPE" ), &
BEFOREAFTERINDICATOR = @GetEnv ( "GGHEADER", "BEFOREAFTERINDICATOR" ) &
) , InsertAllRecords ;
Map scott.t_no_pk, Target scott.t_no_pk_history, &
ColMap ( UseDefaults , &
COMMITTIMESTAMP = @Date ( "YYYY-MM-DD HH:MI:SS.FFFFFF", "YYYY-MM-DD HH:MI:SS.FFFFFF", @GetEnv ( "GGHEADER", "COMMITTIMESTAMP" ) ), &
CSN = @GetEnv ( "TRANSACTION", "CSN" ), &
seq = @Compute ( @GetEnv ( "GGHEADER", "LOGRBA" ) * 10000000000 + @GetEnv ( "GGHEADER", "LOGPOSITION" ) ) , &
OPTYPE = @GetEnv ( "GGHEADER", "OPTYPE" ), &
BEFOREAFTERINDICATOR = @GetEnv ( "GGHEADER", "BEFOREAFTERINDICATOR" ) &
) , InsertAllRecords ;
参数说明：
UseDefaults：历史表前面的字段和源表一样。
InsertAllRecords：把update转换成before image和after image插入历史表；delete转换成被删记录插入历史表。
字段说明：
COMMITTIMESTAMP：DML所属transaction提交的时间。
CSN：DML所属transaction的SCN。
seq：某条DML在事务中顺序无法直接获得，只能用间接方法，将LOGRBA(源端DB Log #)和LOGPOSITION(DML在源端DB Log中的偏移量)合并，合并结果由小到大就表示某条DML在事务中顺序。假设源端最大的DB Log <= 10,000,000,000 byte (10GB)，则计算表达式如下：
seq = LOGRBA * 10000000000 + LOGPOSITION
上面数值表达式不用直接用于ColMap，必须放到函数@Compute()中计算。
您可能注意到了，参数文件中没有GetTruncates，因为GetTruncates会造成truncate源表时，也truncate历史表，我还没有找到把truncate转成insert的办法。:(
下面开始试验。
设置SQL*Plus显式格式：
SQL> set pagesize 9999
SQL> set linesize 150
SQL> column n format 99
SQL> column c format a4
SQL> column v format a4
SQL> alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SQL> column COMMITTIMESTAMP format a26
SQL> column SEQ for 999999999999
各种情况排列组合试验：
1) 源表有PK
1.1) insert
insert into t_pk values (1, 'a', 'a');
insert into t_pk values (2, 'b', 'b');
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
1 a a 2011-05-31 12:41:34 1300233 280011160452 INSERT AFTER
2 b b 2011-05-31 12:41:34 1300233 280011161236 INSERT AFTER
1.2) update 非PK字段
1.2.1) update 一个非PK字段
1.2.1.1) 原值不改
update t_pk set v = 'a' where n = 1;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
1 a a 2011-05-31 13:46:04 1306058 280024771600 SQL COMPUPDATE BEFORE
1 a a 2011-05-31 13:46:04 1306058 280024771600 SQL COMPUPDATE AFTER
1.2.1.2) 原值改
SQL> update t_pk set v = 'aa' where n = 1;
SQL> commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
1 a a 2011-05-31 13:50:14 1306301 280024880656 SQL COMPUPDATE BEFORE
1 a aa 2011-05-31 13:50:14 1306301 280024880656 SQL COMPUPDATE AFTER
1.2.2) update 所有非PK字段
1.2.2.1) 原值不改
update t_pk set c = 'b', v = 'b' where n = 2;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
2 b b 2011-05-31 13:52:39 1306437 280024962576 SQL COMPUPDATE AFTER
2 b b 2011-05-31 13:52:39 1306437 280024962576 SQL COMPUPDATE BEFORE
1.2.2.2) 原值改
update t_pk set c = 'bb', v = 'bb' where n = 2;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
2 b b 2011-05-31 13:53:48 1306478 280024975888 SQL COMPUPDATE BEFORE
2 bb bb 2011-05-31 13:53:48 1306478 280024975888 SQL COMPUPDATE AFTER
1.3) update PK
1.3.1) 原值不改
update t_pk set n = 1 where n = 1;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
1 a aa 2011-05-31 13:54:49 1306520 280024987152 SQL COMPUPDATE BEFORE
1 a aa 2011-05-31 13:54:49 1306520 280024987152 SQL COMPUPDATE AFTER
1.3.2) 原值改
update t_pk set n = 3 where n = 1;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
1 a aa 2011-05-31 13:56:34 1306590 280025007632 SQL COMPUPDATE BEFORE
3 a aa 2011-05-31 13:56:34 1306590 280025007632 PK UPDATE AFTER
1.3.3) 所有字段原值不改
update t_pk set n = 3, c = 'a ', v = 'aa' where n = 3;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
3 a aa 2011-05-31 14:34:10 1308753 280026829840 SQL COMPUPDATE BEFORE
3 a aa 2011-05-31 14:34:10 1308753 280026829840 SQL COMPUPDATE AFTER
1.3.4) 所有字段原值改
update t_pk set n = 4, c = 'aaa ', v = 'aaa' where n = 3;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
3 a aa 2011-05-31 14:35:18 1308796 280026839056 SQL COMPUPDATE BEFORE
4 aaa aaa 2011-05-31 14:35:18 1308796 280026839056 PK UPDATE AFTER
1.4) delete
delete from t_pk where n = 2;
commit;
SQL> select * from t_pk_history order by COMMITTIMESTAMP;
N C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
--- ---- ---- -------------------- ---------- ------------- -------------------- ------
2 bb bb 2011-05-31 14:11:44 1307425 280026034704 DELETE BEFORE
2) 无PK
2.1) insert
insert into t_no_pk values ('a', 'a');
insert into t_no_pk values ('b', 'b');
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
b b 2011-05-31 14:14:23 1307816 280026400580 INSERT AFTER
a a 2011-05-31 14:14:23 1307816 280026400132 INSERT AFTER
2.2) update 一个字段
2.2.1) 原值不改
update t_no_pk set v = 'a' where v = 'a';
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
a a 2011-05-31 14:16:09 1307885 280026415632 SQL COMPUPDATE BEFORE
a a 2011-05-31 14:16:09 1307885 280026415632 SQL COMPUPDATE AFTER
2.2.2) 原值改
update t_no_pk set v = 'aa' where v = 'a';
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
a a 2011-05-31 14:17:39 1307943 280026426896 SQL COMPUPDATE BEFORE
a aa 2011-05-31 14:17:39 1307943 280026426896 PK UPDATE AFTER
2.3) update 所有字段
2.3.1) 原值不改
update t_no_pk set c = 'b ', v = 'b' where c = 'b ' and v = 'b';
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
b b 2011-05-31 14:28:04 1308472 280026686992 SQL COMPUPDATE BEFORE
b b 2011-05-31 14:28:04 1308472 280026686992 SQL COMPUPDATE AFTER
2.3.2) 原值改
update t_no_pk set c = 'bb ', v = 'bb' where c = 'b ' and v = 'b';
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
b b 2011-05-31 14:28:58 1308516 280026704400 SQL COMPUPDATE BEFORE
bb bb 2011-05-31 14:28:58 1308516 280026704400 PK UPDATE AFTER
2.4) delete
delete from t_no_pk where v = 'aa';
commit;
SQL> select * from t_no_pk_history order by COMMITTIMESTAMP;
C V COMMITTIMESTAMP CSN SEQ OPTYPE BEFORE
---- ---- -------------------- ---------- ------------- -------------------- ------
a aa 2011-05-31 14:31:46 1308630 280026758160 DELETE BEFORE
注：OPTYPE中"SQL COMPUPDATE" 就是压缩update (compressed update)。
注：在Oracle DB环境下，容易引起误解的没有返回值函数：
@GetEnv ( "TRANSACTION", "USERID" )
@GetEnv ( "TRANSACTION", "USERNAME" )
@GetEnv ( "TRANSACTION", "TIMESTAMP" )
@GetEnv ( "TRANSACTION", "RSN" )
页:
[1]