goldengate 学习系列8--当主键遇上keycols

原创 Roger 2015-06-10

2786

---源端主库
说明：
源端数据库: 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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}S1{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}S3{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}S1{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}S3{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';    

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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}S1{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

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版本不存在这个问题。包括原厂的工程师。稍后将进行相关测试！

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

goldengate 学习系列8--当主键遇上keycols

评论