【转】GolenGate实现历史表

ALLSTARS_ORACLE

有些客户需要记录关键表（源表）的修改历史，即，将源表什么数据何时做了什么修改记录到历史表，以便将来恢复、审计、追踪或作为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

ALLSTARS_ORACLE

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" )