goldengate 学习系列8–当主键遇上keycols
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: goldengate 学习系列8–当主键遇上keycols
—源端主库
说明：
源端数据库: 11.2.0.4 ogg版本12.1.2
目标端数据库:10.2.0.5 ogg版本11.2.1.0.1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL> create table s1 (a number primary key, b number, c char(32)); Table created. SQL> create table s3 (a number, b number); Table created. SQL> insert into s1 values (1,1,1); 1 row created. SQL> insert into s3 values(1,1); 1 row created. SQL> commit; Commit complete. SQL> select a,b,c,rowid from s1; A B C ROWID ---------- ---------- -------------------------------- ------------------ 1 1 1 AAAVViAAEAAAAC1AAA SQL> select a,b,rowid from s3; A B ROWID ---------- ---------- ------------------ 1 1 AAAVVjAAEAAAADFAAA |
—目标端数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
www.killdb.com>create table s1 (a number primary key, b number, c char(32)); Table created. www.killdb.com>create table s3 (a number, b number); Table created. www.killdb.com>select * from s1; A B C ---------- ---------- -------------------------------- 1 1 1 www.killdb.com>select * from s3; A B ---------- ---------- 1 1 www.killdb.com>insert into s1 values (2,1,1); 1 row created. www.killdb.com>insert into s3 values(2,1); 1 row created. www.killdb.com>commit; Commit complete. www.killdb.com>select a,b,c,rowid from s1; A B C ROWID ---------- ---------- -------------------------------- ------------------ 1 1 1 AAAObvAAEAAAADkAAA 2 1 1 AAAObvAAEAAAADlAAA www.killdb.com>select a,b,rowid from s3; A B ROWID ---------- ---------- ------------------ 1 1 AAAObwAAEAAAAD0AAA 2 1 AAAObwAAEAAAAD1AAA |
—-源端进行delete操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<pre class="brush:plain">SQL> delete from s1; 1 row deleted. SQL> commit; Commit complete. SQL> delete from s3; 1 row deleted. SQL> commit; Commit complete. |
—目标端查询数据
|
1 2 3 4 5 6 7 8 9 10 11 12 |
www.killdb.com> select * from s1; A B C ---------- ---------- -------------------------------- 1 1 1 2 1 1 www.killdb.com>select * from s3; A B ---------- ---------- 2 1 |
我们可以看到，这里Oracle 默认情况下，并没有对s1表进行删除操作？ 为什么？
—通过logminer分析源端redo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'/home/oracle/oradata/roger/redo02.log'); PL/SQL procedure successfully completed. SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); PL/SQL procedure successfully completed. SQL> col sql_redo for a80 SQL> select TIMESTAMP ,sql_redo FROM v$logmnr_contents WHERE sql_redo like '%S1%'; TIMESTAMP SQL_REDO ------------ -------------------------------------------------------------------------------- 09-JUN-15 insert into "ROGER"."S1"("A","B","C") values ('1','1','1'); 09-JUN-15 delete from "ROGER"."S1" where "A" = '1' and "B" = '1' and "C" = '1 ' and ROWID = 'AAAVViAAEAAAAC1AAA'; SQL> select TIMESTAMP ,sql_redo FROM v$logmnr_contents WHERE sql_redo like '%S3%'; TIMESTAMP SQL_REDO ------------ -------------------------------------------------------------------------------- 09-JUN-15 insert into "ROGER"."S3"("A","B") values ('1','1'); 09-JUN-15 delete from "ROGER"."S3" where "A" = '1' and "B" = '1' and ROWID = 'AAAVVjAAEAAA ADFAAA'; |
—通过logminer分析目标端redo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
www.killdb.com>execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'/home/ora10g/oradata/roger/redo03.log'); PL/SQL procedure successfully completed. www.killdb.com>EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); PL/SQL procedure successfully completed. www.killdb.com>set lines 120 www.killdb.com>col sql_redo for a90 www.killdb.com>select TIMESTAMP ,sql_redo FROM v$logmnr_contents WHERE sql_redo like '%S1%'; TIMESTAMP SQL_REDO ---------- ----------------------------------------------------------- 10-JUN-15 insert into "ROGER"."S1"("A","B","C") values ('1','1','1'); 10-JUN-15 insert into "ROGER"."S1"("A","B","C") values ('2','1','1'); www.killdb.com>select TIMESTAMP ,sql_redo FROM v$logmnr_contents WHERE sql_redo like '%S3%'; TIMESTAMP SQL_REDO ---------- --------------------------------------------------------------------------------------- 10-JUN-15 insert into "ROGER"."S3"("A","B") values ('1','1'); 10-JUN-15 insert into "ROGER"."S3"("A","B") values ('2','1'); 10-JUN-15 delete from "ROGER"."S3" where "A" = '1' and "B" = '1' and ROWID = 'AAAObwAAEAAAAD0AAA'; |
既然源端数据库redo已经记录了相关DML的操作，那么ogg是否抓取了呢？
通过logdump分析源端trail文件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Logdump 1 >open ./dirdat/ex000004 Current LogTrail is /opt/oracle/ggs/12.1.2.1/dirdat/ex000004 Logdump 2 >ghdr on Logdump 3 >detail on Logdump 4 >detail data Logdump 5 >usertoken on Logdump 6 >FILTER include filename ROGER.S1; Logdump 7 >next ...... Logdump 21 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 56 (x0038) IO Time : 2015/06/09 22:22:22.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 32 AuditPos : 22032 Continued : N (x00) RecCount : 1 (x01) 2015/06/09 22:22:22.000.000 Insert Len 56 RBA 5095 Name: ROGER.S1 After Image: Partition 4 G b 0000 0005 0000 0001 3100 0100 0500 0000 0131 0002 | ........1........1.. 0022 0000 3120 2020 2020 2020 2020 2020 2020 2020 | ."..1 2020 2020 2020 2020 2020 2020 2020 2020 | Column 0 (x0000), Len 5 (x0005) 0000 0001 31 | ....1 Column 1 (x0001), Len 5 (x0005) 0000 0001 31 | ....1 Column 2 (x0002), Len 34 (x0022) 0000 3120 2020 2020 2020 2020 2020 2020 2020 2020 | ..1 2020 2020 2020 2020 2020 2020 2020 | Filtering suppressed 1 records Logdump 22 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 9 (x0009) IO Time : 2015/06/09 22:27:18.000.000 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 32 AuditPos : 178704 Continued : N (x00) RecCount : 1 (x01) 2015/06/09 22:27:18.000.000 Delete Len 9 RBA 5366 Name: ROGER.S1 Before Image: Partition 4 G s 0000 0005 0000 0001 31 | ........1 Column 0 (x0000), Len 5 (x0005) 0000 0001 31 | ....1 |
从trial文件的dump信息来看，确实是抽取了delete操作. 其中IOType 3表示delete，IOType 5表示insert.表明我们对S1表进行的insert 和delete操作都是被抓取了的。到这里来看，貌似一切都是正常的，但是为什么会出现s1 表数据不同步的情况呢？
对应ogg如果存在异常，那么我们可以查看相关进程的discard文件，内容如下：
|
1 2 3 4 5 6 7 |
Operation failed at seqno 7 rba 1907 Discarding record on action DISCARD on error 0 Problem replicating ROGER.S1 to ROGER.S1 Mapping problem with delete record (target format)... * A = 1 * |
很明显，goldengate这里在对s1表进行delete操作的时候，map失败了。因此实际上在目标端针对s1表的delete操作根本就没有执行.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
GGSCI (killdb.com) 2> view param rep1124 replicat rep1124 userid ggs@roger,password ggs reperror default, discard DISCARDROLLOVER AT 20:30 discardfile ./dirrpt/rep1124.dsc, append, megabytes 50 handlecollisions assumetargetdefs allownoopupdates numfiles 3000 map roger.t_ogg, target roger.t_ogg; map roger.s1, target roger.s1, keycols (b); map roger.s3, target roger.s3, keycols (b); GGSCI (killdb.com) 3> stop rep1124 Sending STOP request to REPLICAT REP1124 ... Request processed. GGSCI (killdb.com) 4> edit param rep1124 GGSCI (killdb.com) 5> view param rep1124 replicat rep1124 userid ggs@roger,password ggs reperror default, discard DISCARDROLLOVER AT 20:30 discardfile ./dirrpt/rep1124.dsc, append, megabytes 50 handlecollisions assumetargetdefs allownoopupdates numfiles 3000 map roger.t_ogg, target roger.t_ogg; map roger.s1, target roger.s1; map roger.s3, target roger.s3, keycols (b); ----modify rba GGSCI (killdb.com) 6> alter rep rep1124,extrba 1907 REPLICAT altered. GGSCI (killdb.com) 7> start rep1124 Sending START request to MANAGER ... REPLICAT REP1124 starting |
—再次check
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
www.killdb.com>select a,b,c,rowid from s1; A B C ROWID ---------- ---------- -------------------------------- ------------------ 2 1 1 AAAObvAAEAAAADlAAA www.killdb.com>select TIMESTAMP ,sql_redo FROM v$logmnr_contents WHERE sql_redo like '%S1%'; TIMESTAMP SQL_REDO ------------ ---------------------------------------------------------------------- 10-JUN-15 insert into "ROGER"."S1"("A","B","C") values ('1','1','1'); 10-JUN-15 insert into "ROGER"."S1"("A","B","C") values ('2','1','1'); 10-JUN-15 delete from "ROGER"."S1" where "A" = '1' and "B" = '1' and "C" = '1 ' and ROWID = 'AAAObvAAEAAAADkAAA'; |
这里严格上来讲是keycols参数配置不当导致。 该参数的含义是指制定一个可以表示数据唯一性的列，这样以便于goldengate可以完成同步，例如delete和update.
之前之所以不能同步，报错的原因是因为目标端的s1表 b=1的结果有2条，而原端删除的是一条，很明显是无法进行map的.
下面我们将replicat进程的keycols列修改为a，进行测试发现ok,测试过程如下：
—-原端
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> insert into s1 values (1,1,1); 1 row created. SQL> insert into s3 values(1,1); 1 row created. SQL> commit; Commit complete. SQL> delete from s1; 1 row deleted. SQL> commit; Commit complete. |
—修改目标端replicat配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
GGSCI (killdb.com) 7> view param rep1124 replicat rep1124 userid ggs@roger,password ggs reperror default, discard DISCARDROLLOVER AT 20:30 discardfile ./dirrpt/rep1124.dsc, append, megabytes 50 handlecollisions assumetargetdefs allownoopupdates numfiles 3000 map roger.t_ogg, target roger.t_ogg; map roger.s1, target roger.s1, keycols (a); map roger.s3, target roger.s3, keycols (b); |
—目标端
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
www.killdb.com>truncate table s1; Table truncated. www.killdb.com>insert into s1 values(3,1,1); 1 row created. www.killdb.com>commit; Commit complete. www.killdb.com>select * from s1; A B C ---------- ---------- -------------------------------- 1 1 1 3 1 1 www.killdb.com> www.killdb.com> www.killdb.com>select * from s1; A B C ---------- ---------- -------------------------------- 3 1 1 |
可以看到，当调整keycols的列之后，一切正常，这是因为目标端s1表的a列的数据本身就是唯一的，因为目前只有2条数据，数值为1，3. 对应不存在主键或unique index的情况之下，如果进行update会导致目标端可能产生重复数据吗？很多人都说ogg 11.2版本不存在这个问题。包括原厂的工程师。稍后将进行相关测试！
Leave a Reply
You must be logged in to post a comment.