暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

OGG 2端主键不一致

原创 章芋文 2014-05-20
1332

源端和目标端数据库的表逻辑结构相同,但物理结构(分区结构不同)。因此表在源端和目标端的主键列是不同的。
比如源端是PRIMARY KEY (A, B),而目标端为PRIMARY KEY (A),由于主键的结构不同,因此直接复制会有问题。

这里可以在添加附加日志时指定字段,如下:

add trandata awen.ogg_tab_ab,cols(a),nokey
并在抽取进程中指定相应字段:

TABLE awen.ogg_tab_ab, keycols (a);

如果抽取进程中未指定 keycols (a)参数,
源端UPDATE SET C = 1 WHERE A = 'A'
在目标端变成
UPDATE SET B=NULL, C = 1 WHERE A = 'A’
也就是会将b字段置空。

针对加和不加keycols (a)发现了是会存在将字段b置空的问题。部分测试过程如下:

--添加日志:

[code]GGSCI (localhost.localdomain) 28> add trandata awen.ogg_tab_ab ,cols(a),nokey

Logging of supplemental redo data enabled for table AWEN.OGG_TAB_AB.

GGSCI (localhost.localdomain) 29> info trandata awen.ogg_tab_ab

Logging of supplemental redo log data is enabled for table AWEN.OGG_TAB_AB.

Columns supplementally logged for table AWEN.OGG_TAB_AB: A.[/code]

--抽取进程加 keycols (a)

源端

[code]SQL> conn awen/oracle

Connected.

SQL> create table ogg_tab_ab(a number(5),b number(5),name varchar2(15),addrs varchar2(200));

Table created.

SQL> alter table ogg_tab_ab add constraint ogg_tab_prk primary key (a,b);

Table altered.

SQL> insert into ogg_tab_ab values(1001,2001,'Steven','Wuhan');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into ogg_tab_ab values(1002,2002,'Jobs','USA');

1 row created.

SQL> insert into ogg_tab_ab values(1003,2003,'Eygle','Beijing');

1 row created.

SQL> commit;

Commit complete.

SQL> update ogg_tab_ab set addrs='California' where a=1002 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> update ogg_tab_ab set addrs='Hubei' where a=1001;

1 row updated.

SQL> update ogg_tab_ab set addrs='Shenyang' where a=1003;

1 row updated.

SQL> commit;

Commit complete.[/code]

目标端

[code]SQL> conn scott/oracle

Connected.

SQL> create table ogg_tab_ab(a number(5),b number(5),name varchar2(15),addrs varchar2(200));

Table created.

SQL> alter table ogg_tab_ab add constraint ogg_tab_prk primary key (a);

Table altered.

SQL> /

A B NAME ADDRS

---------- ---------- --------------- ---------------

1001 2001 Steven Wuhan

1002 2002 Jobs California

1003 2003 Eygle Beijing

SQL> /

A B NAME ADDRS

---------- ---------- --------------- ---------------

1001 2001 Steven Hubei

1002 2002 Jobs California

1003 2003 Eygle Shenyang[/code]

--不加keycols(a)

源端

[code]SQL> insert into ogg_tab_ab values(1004,2004,'Kamus','Chian');

1 row created.

SQL> commit;

Commit complete.

SQL> update ogg_tab_ab set addrs='Beijing' where a=1004;

1 row updated.

SQL> commit;[/code]

目标端

[code]SQL> /

A B NAME ADDRS

---------- ---------- --------------- ---------------

1001 2001 Steven Hubei

1002 2002 Jobs California

1003 2003 Eygle Shenyang

1004 2004 Kamus Chian

SQL> /

A B NAME ADDRS

---------- ---------- --------------- ---------------

1001 2001 Steven Hubei

1002 2002 Jobs California

1003 2003 Eygle Shenyang

1004 Kamus Beijing[/code]

另外ogg各进程配置如下:
[code]GGSCI (localhost.localdomain) 41> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING AWEN_EXT 00:02:19 00:00:02
EXTRACT RUNNING AWEN_PMP 00:00:00 00:00:01
REPLICAT RUNNING AWEN_REP 00:00:00 00:00:00


GGSCI (localhost.localdomain) 42> view params awen_ext

EXTRACT awen_ext
USERID ogguser,PASSWORD ogguser
EXTTRAIL ./dirdat/va
TABLE awen.ogg_tab_ab,keycols(a);


GGSCI (localhost.localdomain) 43> view params awen_pmp

EXTRACT awen_pmp
USERID ogguser,PASSWORD ogguser
RMTHOST 127.0.0.1,MGRPORT 7848,COMPRESS
RMTTRAIL ./dirdat/vb
TABLE awen.*;


GGSCI (localhost.localdomain) 44> view params awen_rep

replicat awen_rep
ASSUMETARGETDEFS
USERID ogguser, PASSWORD ogguser
DISCARDFILE ./dirrpt/awen_rep.dsc, PURGE
map awen.ogg_tab_ab, target scott.ogg_tab_ab;[/code]
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论