Oracle GoldenGate有众多解决方案,其中有一个可用来实现交易跟踪.比如有的业务系统需要实时的记录某些数据的各种变化,通过OGG可以轻松的实现
这一需求.下面通过一个example来看一下OGG是如何实现这个场景的.
需求概述 某业务系统中有一张emp_sal表,客户希望记录表中所有的交易信息,比如记录变化时间,变化内容,帐户建立时的信息,帐户删除时的信息等等. create table toms.emp_sal ( empno number, sal number(8,2), constraint pk_emp_sal primary key(empno) using index ); 在History System中有一张对应的交易跟踪表 create table toms.emp_sal_his ( empno number, sal number(8,2), before_after varchar2(32), deal_date timestamp, op_flag varchar2(32) ); 实现方法 在Source system上,配置一个Extract Group:extsq,参数如下,其中粗体部分是必须的参数,参数的含义请参阅doc. EXTRACT EXTSQ USERID goldengate@oragg1, PASSWORD goldengate RMTHOST zhangrp-cn, MGRPORT 9001 RMTTRAIL ./dirdat/sq GETUPDATEBEFORES TABLE TOMS.EMP_SAL; 启动Extract进程extsq GGSCI> start extract extsq 再在Target system上配置一个Replicat Group:repsq,参数如下,其中粗体部分是必须的参数,参数的含义请参阅doc. REPLICAT REPSQ SOURCEDEFS ./dirdef/MYDEFS.DEF USERID goldengate@oragg2, PASSWORD goldengate ASSUMETARGETDEFS INSERTALLRECORDS DISCARDFILE ./dirrpt/REPSQ.DSC, APPEND, MEGABYTES 10M MAP TOMS.EMP_SAL, TARGET TOMS.EMP_SAL_HIS, COLMAP (empno=empno, sal=sal, BEFORE_AFTER=@getenv("GGHEADER", "BEFOREAFTERINDICATOR"), DEAL_DATE=@getenv ("GGHEADER", "COMMITTIMESTAMP"), OP_FLAG=@getenv ("GGHEADER", "OPTYPE") ); 启动Replicat进程repsq GGSCI> start replicat repsq 测试验证 1)验证insert操作 Source system 插入几条记录 SQL> select * from emp_sal; no rows selected SQL> insert into emp_sal values(1,100); 1 row created. SQL> insert into emp_sal values(2,200); 1 row created. SQL> insert into emp_sal values(3,300); 1 row created. SQL> commit; Commit complete. SQL> insert into emp_sal values(4,400); 1 row created. SQL> commit; Commit complete. Target system SQL> select * from emp_sal_his; EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG ----- --------- -------------- ----------------------------- ------- 1 100.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 2 200.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 3 300.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 4 400.00 AFTER 12-DEC-09 03.32.03.000000 PM INSERT 2)验证update操作 Source system 修改记录,让每个empno的sal增加100. SQL> update emp_sal set sal=sal+100; 4 rows updated. SQL> select * from emp_sal; EMPNO SAL ---------- ---------- 1 200 2 300 3 400 4 500 SQL> commit; Commit complete. Target system 在到target系统上看看是不是捕获了修改操作信息 SQL> select * from emp_sal_his; EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG ----- ---------- ---------------- ------------------------------ -------- 1 100.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 2 200.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 3 300.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 4 400.00 AFTER 12-DEC-09 03.32.03.000000 PM INSERT 1 100.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 1 200.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 2 200.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 2 300.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 3 300.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 3 400.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 4 400.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 4 500.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 12 rows selected 注,对于Srouce系统上update的每条记录,都捕获到了该记录的修改前和修改后的信息. 3)验证delete操作 Source system 最后再在Source系统上做个delete操作看看 SQL> delete from emp_sal; 4 rows deleted. SQL> commit; Commit complete. SQL> Target system 再到target系统上看看是不是捕获了删除操作信息 SQL> select * from emp_sal_his; EMPNO SAL BEFORE_AFTER DEAL_DATE OP_FLAG ----- ---------- ---------------- ------------------------------ -------- 1 100.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 2 200.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 3 300.00 AFTER 12-DEC-09 03.30.15.000000 PM INSERT 4 400.00 AFTER 12-DEC-09 03.32.03.000000 PM INSERT 1 100.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 1 200.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 2 200.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 2 300.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 3 300.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 3 400.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 4 400.00 BEFORE 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 4 500.00 AFTER 12-DEC-09 03.32.42.000000 PM SQL COMPUPDATE 1 BEFORE 12-DEC-09 03.33.28.000000 PM DELETE 2 BEFORE 12-DEC-09 03.33.28.000000 PM DELETE 3 BEFORE 12-DEC-09 03.33.28.000000 PM DELETE 4 BEFORE 12-DEC-09 03.33.28.000000 PM DELETE 16 rows selected 注意,这里我发现没有跟踪到delete前的sal字段的信息,这是为什么呢? 这是因为默认情况下,extract进程只会把pk/uk的字段信息写入到trail file中,如果我们需要捕获所有的column,那么需要增加一个Extract参数 COMPRESSDELETES | NOCOMPRESSDELETES
联系客服
