本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: goldengate 学习系列8–当主键遇上keycols
—源端主库
说明：
源端数据库: 11.2.0.4 ogg版本12.1.2
目标端数据库:10.2.0.5 ogg版本11.2.1.0.1
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
—目标端数据库
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操作
SQL> delete from s1; 1 row deleted. SQL> commit; Commit complete. SQL> delete from s3; 1 row deleted. SQL> commit; Commit complete.
—目标端查询数据
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
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
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文件：
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文件，内容如下：
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操作根本就没有执行.
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
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,测试过程如下：
—-原端
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配置
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);
—目标端
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版本不存在这个问题。包括原厂的工程师。稍后将进行相关测试！
Related posts: