Goal
探究SharePlex与Oracle Goldengate事务开始复制还是事务提交之后复制。
1.SharePlex验证
1.1 搭建一个简单SharePlex链路
本地oracle到kafka链路，配置如下：
sp_ctrl (single:12000)> view config kafka datasource:o.messay #source tables target tables routing map expand hr.% not (TEST_TYPE) !kafka single sp_ctrl (single:12000)> show Process Source Target State PID ---------- ------------------------------------ ---------------------- -------------------- ------ Capture o.messay Running 8336 Read o.messay Running 8338 Post o.messay-single x.kafka Running 8836 sp_ctrl (single:12000)> qstatus Queues Statistics for single Name: o.messay (Capture queue) Number of messages: 0 (Age 0 min; Size 2 mb) Backlog (messages): 0 (Age 0 min) Name: single (o.messay-x.kafka) (Post queue) Number of messages: 0 (Age 0 min; Size 1 mb) Backlog (messages): 0 (Age 0 min)
1.2 测试
1.2.1 源端插入数据，并不提交
06:20:55 HR@messay > insert into test values(19,'s(*)*dfsfdsdfsdfs'); 1 row created.
1.2.2 查看队列数据
sp_ctrl (single:12000)> qstatus Queues Statistics for single Name: o.messay (Capture queue) Number of messages: 0 (Age 0 min; Size 2 mb) Backlog (messages): 0 (Age 0 min) Name: single (o.messay-x.kafka) (Post queue) Number of messages: 0 (Age 0 min; Size 1 mb) Backlog (messages): 0 (Age 0 min)
1.2.3 qview查看队列数据
[oracle@single rim]$ qview -i qview> qinit # 初始化qview工具 qview> list # 列出当前所有队列 The following queues exist: o.messay+C WRITER +PA+o.messay+sp_ocap+o.messay READER +PR+o.messay+sp_ordr+o.messay single+P+o.messay-x.kafka subqueues range from 0 to 7 WRITER +PR+o.messay+sp_ordr+o.messay READER +PP+single+sp_xpst+o.messay-x.kafka qview> sque o.messay+C # 查看capture队列所有的sub queue子队列 Queue name: o.messay+C qview> sque single+P+o.messay-x.kafka # 查看子队列消息数量 Queue name: single+P+o.messay-x.kafka qview>
# 可以看到队列中没有任何数据，那是否SharePlex没有复制呢？我们继续验证
1.2.4 继续插入数据，依然不提交
06:23:54 HR@messay > insert into test values(20,'6646dfsfdsdfsdfs'); 1 row created.
1.2.5 查看队列，可以看到post队列中已经有一条message
sp_ctrl (single:12000)> qstatus Queues Statistics for single Name: o.messay (Capture queue) Number of messages: 0 (Age 0 min; Size 2 mb) Backlog (messages): 0 (Age 0 min) Name: single (o.messay-x.kafka) (Post queue) Number of messages: 1 (Age 0 min; Size 1 mb) Backlog (messages): 0 (Age 0 min)
1.2.6 qview验证消息
sp_ctrl (single:12000)> stop post [oracle@single rim]$ ../../prodir/bin/qview -i qview> qinit qview> list The following queues exist: o.messay+C WRITER +PA+o.messay+sp_ocap+o.messay READER +PR+o.messay+sp_ordr+o.messay single+P+o.messay-x.kafka subqueues range from 0 to 7 WRITER +PR+o.messay+sp_ordr+o.messay READER +PP+single+sp_xpst+o.messay-x.kafka qview> set out qview> set detail full qview> sque single+P+o.messay-x.kafka Queue name: single+P+o.messay-x.kafka Subqueue 6: 1 msgs 2:10 mins qview> open p r Current queue single+P+o.messay-x.kafka user +PP+single+sp_xpst+o.messay-x.kafka qview> oread 6 1 Total messages read: 1 qview> exit [oracle@single rim]$ cat qview.out OREAD 6 1 : Sun Aug 23 06:29:07 2020 for single+P+o.messay-x.kafka --------------------------------------------------- Sque 6, seq 0, mid 17, sqmid 1, mflags 0 odr_magic 0x4f445236 odr_op ODR_INSERT1 (1) odr_trans 7(5).3169-6(1145) odr_time 08/23/20 06:23:55 (1049178235) odr_sid messay odr_seq#/offset 26/33896976 odr_scn (high) 0 odr_scn 16534229 odr_userid 84 odr_flags 0x2040000 odr_cdr_flags 0 odr_marker 0 odr_actid 8 odr_obj 89612 odr_oname "HR"."TEST" odr_userid 84 odr_nrow 1 odr_datasize 0 odr_forward 1 odr_forback 0 odr_opnum 0 odr_rid [0] AAAV9qAAEAAAAnDAAB at 0x0x7fffb4744f48 data image columns = 0 data = 0x7fffb4744f5c key image columns = 2 data = 0x7fffb4744f5c [1] length=2 19 [2] length=17 s(*)*dfsfdsdfsdfs
# 根据rowid查询该条数据，发现post队列中数据为我们刚插入的第一条数据，并没有第二条数据
06:26:38 HR@messay > select * from test where rowid='AAAV9qAAEAAAAnDAAB'; ID NAME ---------------------------------------- ---------------------------------------- 19 s(*)*dfsfdsdfsdfs
# SharePlex应该是将最后一条数据作为判断是否提交的依据，如果最后一天消息不是commit消息，则不放入队列，只读取并且将倒数第二条消息写入队列。 # 接下来我们继续验证
1.2.7 继续插入继续查看
06:35:02 HR@messay > insert into test values(21,'6646'); 1 row created. sp_ctrl (single:12000)> show Process Source Target State PID ---------- ------------------------------------ ---------------------- -------------------- ------ Capture o.messay Running 8336 Read o.messay Running 8338 Post o.messay-single x.kafka Running 9572 sp_ctrl (single:12000)> qstatus Queues Statistics for single Name: o.messay (Capture queue) Number of messages: 0 (Age 0 min; Size 2 mb) Backlog (messages): 0 (Age 0 min) Name: single (o.messay-x.kafka) (Post queue) Number of messages: 2 (Age 8 min; Size 1 mb) Backlog (messages): 0 (Age 0 min) sp_ctrl (single:12000)> stop post [oracle@single rim]$ qview -i qview> qinit qview> set out qview> set detail full qview> list The following queues exist: o.messay+C WRITER +PA+o.messay+sp_ocap+o.messay READER +PR+o.messay+sp_ordr+o.messay single+P+o.messay-x.kafka subqueues range from 0 to 7 WRITER +PR+o.messay+sp_ordr+o.messay READER +PP+single+sp_xpst+o.messay-x.kafka qview> open p r Current queue single+P+o.messay-x.kafka user +PP+single+sp_xpst+o.messay-x.kafka qview> sque single+P+o.messay-x.kafka Queue name: single+P+o.messay-x.kafka Subqueue 6: 2 msgs 11:14 mins qview> oread 6 2 Total messages read: 2 qview> exit [oracle@single rim]$ cat qview.out OREAD 6 2 : Sun Aug 23 06:38:01 2020 for single+P+o.messay-x.kafka --------------------------------------------------- Sque 6, seq 0, mid 17, sqmid 1, mflags 0 odr_magic 0x4f445236 odr_op ODR_INSERT1 (1) odr_trans 7(5).3169-6(1145) odr_time 08/23/20 06:23:55 (1049178235) odr_sid messay odr_seq#/offset 26/33896976 odr_scn (high) 0 odr_scn 16534229 odr_userid 84 odr_flags 0x2040000 odr_cdr_flags 0 odr_marker 0 odr_actid 8 odr_obj 89612 odr_oname "HR"."TEST" odr_userid 84 odr_nrow 1 odr_datasize 0 odr_forward 1 odr_forback 0 odr_opnum 0 odr_rid [0] AAAV9qAAEAAAAnDAAB at 0x0x7fffe5d2d268 data image columns = 0 data = 0x7fffe5d2d27c key image columns = 2 data = 0x7fffe5d2d27c [1] length=2 19 [2] length=17 s(*)*dfsfdsdfsdfs Sque 6, seq 393, mid 18, sqmid 2, mflags 0 odr_magic 0x4f445236 odr_op ODR_INSERT1 (1) odr_trans 7(5).3169-6(1145) odr_time 08/23/20 06:26:39 (1049178399) odr_sid messay odr_seq#/offset 26/34054160 odr_scn (high) 0 odr_scn 16534678 odr_userid 84 odr_flags 0x2040000 odr_cdr_flags 0 odr_marker 0 odr_actid 8 odr_obj 89612 odr_oname "HR"."TEST" odr_userid 84 odr_nrow 1 odr_datasize 0 odr_forward 1 odr_forback 0 odr_opnum 0 odr_rid [0] AAAV9qAAEAAAAnDAAC at 0x0x7fffe5d2d268 data image columns = 0 data = 0x7fffe5d2d27c key image columns = 2 data = 0x7fffe5d2d27c [1] length=2 20 [2] length=16 6646dfsfdsdfsdfs 06:35:07 HR@messay > select * from test where rowid='AAAV9qAAEAAAAnDAAC'; ID NAME ---------------------------------------- ---------------------------------------- 20 6646dfsfdsdfsdfs
1.3 结论
SharePlex从事务开始即开始复制，从事务开始即复制并不是所有消息都存储在队列，最后一条未提交消息是不在队列中的。 从事务开始复制，会加快队列复制速度，但是对于大事务回滚来说，确实有一些不太友好。
2.Oracle Goldengate验证
2.1 搭建一个本地到本地的简单ogg环境
$ ./ggsci
GGSCI (single) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP_TAR 00:00:00 00:00:08 EXTRACT RUNNING EX_TAR 00:00:05 00:00:05 REPLICAT RUNNING RP_NEW 00:00:00 00:00:02 GGSCI (single) 4> view param ex_tar extract ex_tar setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") setenv (ORACLE_SID="messay") setenv (ORACLE_HOME="/oracle/app/oracle/product/11.2/db_1") userid ogg,password ogg exttrail ./dirdat/rt GETUPDATEBEFORES GETTRUNCATES --BR BROFF BR BRINTERVAL 2H CACHEMGR CACHESIZE 100MB WARNLONGTRANS 2H,CHECKINTERVAL 5M TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2) --TRANLOGOPTIONS ARCHIVEDLOGONLY NUMFILES 4000 NOCOMPRESSUPDATES EOFDELAYCSECS 10 --TRANLOGOPTIONS DBLOGREADER TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS DYNAMICRESOLUTION discardfile ./dirrpt/ex_oth.dsc,append, megabytes 200 table hr.test;
2.2 源端测试表插入数据，第一条commit，第二条不commit
16:59:16 HR@messay > insert into test values(21,'aaa'); 17:06:50 HR@messay > commit; 17:06:53 HR@messay > insert into test values(22,'aaa');
2.3 使用logdump工具查看源端队列文件内容
$ ./logdump Logdump 16 >ghdr on Logdump 17 >detail on Logdump 18 >filter filename HR.TEST Logdump 20 >open /ogg/dirdat/rt000014 Current LogTrail is /ogg/dirdat/rt000014 Logdump 21 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:51:45.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 16823312 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:51:45.000.000 Insert Len 21 RBA 1091 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3139 0001 0007 0000 0003 6161 | ........19........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Filtering suppressed 2 records Logdump 22 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:59:16.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 17503760 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:59:16.000.000 Insert Len 21 RBA 1222 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3230 0001 0007 0000 0003 6161 | ........20........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 23 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 17:06:53.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 20932112 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 17:06:53.000.000 Insert Len 21 RBA 1355 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3231 0001 0007 0000 0003 6161 | ........21........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 24 >next
# 可以看到只有21，没有22的消息，也就是OGG没有捕获未提交的事务。 # 将22记录提交
19:07:24 HR@messay > commit;
# logdump继续查看
Logdump 25 >position first Reading forward from RBA 0 Logdump 26 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:51:45.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 16823312 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:51:45.000.000 Insert Len 21 RBA 1091 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3139 0001 0007 0000 0003 6161 | ........19........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Filtering suppressed 2 records Logdump 27 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:59:16.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 17503760 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:59:16.000.000 Insert Len 21 RBA 1222 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3230 0001 0007 0000 0003 6161 | ........20........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 28 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 17:06:53.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 20932112 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 17:06:53.000.000 Insert Len 21 RBA 1355 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3231 0001 0007 0000 0003 6161 | ........21........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 29 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 19:10:18.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 31403152 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 19:10:18.000.000 Insert Len 21 RBA 1488 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3232 0001 0007 0000 0003 6161 | ........22........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 30 >next Logdump 31 >
# 可以看到提交之后，trail队列文件中有了22的记录。
2.4 继续插入以及update不commit
2.4.1 继续插入
19:10:18 HR@messay > insert into test values(23,'aaa'); 1 row created. Elapsed: 00:00:00.27 22:49:29 HR@messay > insert into test values(24,'aaa'); 1 row created. Elapsed: 00:00:00.00 22:49:33 HR@messay > insert into test values(25,'aaa'); 1 row created. Elapsed: 00:00:00.00 22:49:37 HR@messay > update test set name='hahahahaha' where id=25; 1 row updated.
2.4.2 logdump查看
Logdump 34 >position first Reading forward from RBA 0 Logdump 35 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:51:45.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 16823312 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:51:45.000.000 Insert Len 21 RBA 1091 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3139 0001 0007 0000 0003 6161 | ........19........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Filtering suppressed 2 records Logdump 36 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 16:59:16.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 17503760 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 16:59:16.000.000 Insert Len 21 RBA 1222 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3230 0001 0007 0000 0003 6161 | ........20........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 37 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 17:06:53.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 20932112 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 17:06:53.000.000 Insert Len 21 RBA 1355 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3231 0001 0007 0000 0003 6161 | ........21........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 38 >next ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2020/08/24 19:10:18.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 33 AuditPos : 31403152 Continued : N (x00) RecCount : 1 (x01) 2020/08/24 19:10:18.000.000 Insert Len 21 RBA 1488 Name: HR.TEST After Image: Partition 4 G s 0000 0006 0000 0002 3232 0001 0007 0000 0003 6161 | ........22........aa 61 | a Column 0 (x0000), Len 6 (x0006) Column 1 (x0001), Len 7 (x0007) Logdump 39 >next Logdump 40 >
#可以看到并没有更多的消息，也就是不存在SharePlex的不记录最后一条未提交事务消息的情况。
2.5 结论
Oracle Goldengate只有在事务提交之后才会复制记录到trail文件中，对于大事务最终回滚的场景很友好，但是对于 大事务提交的场景，Oracle Goldengate确实做的不如SharePlex。
3 总结
（1）SharePlex在事务开始即开始复制 （2）OGG在事务提交之后开始复制（3）对于大事务回滚的场景，SharePlex表现不如OGG，因为OGG如果事务最终回滚，OGG将不复制。（4）对于大事务提交的场景，OGG表现是不如SharePlex的，因为OGG从COMMIT之后才开始写入trail文件，必然造成延迟。（5）综上，两个软件原理设计各有优缺点，Oracle Goldengate最近版本发布更为稳定，对Oracle各个版本支持更为及时并且与Oracle更为深度融合，复制效率越来越高，希望SharePlex未来也能越做越好，希望国产DSG也能越来越棒。
