OGG How to handle / replicate tables with no (without) primary key (PK) or unique index (UI) (UPI) [

msdnchina發表於2011-09-28
IndexUI

Applies to:

Oracle GoldenGate - Version: 5.0.0 and later [Release: 5.0.0 and later ]
Information in this document applies to any platform.

Goal

To handle / replicate tables with no (without) primary key (PK) or unique index (UI) or UPI

Solution

By default, all the columns (except LOB, XML, UDT et al) will be used as the key. Supplemental logging of the table is required.

For batch updates and deletes, one update/delete statement in source will becomes multiple ones in target. if each has to do a full-table scan, it will slow down the replication. an index (even not unique) may be added to target to speed up the replication.

However, if a subset of columns is known to be unique, even without unique index, it may be used as key with KEYCOLS parameter.

here is an example of the setup:

let's use a simple example to explain the case:

source: create table s1 (a number, b number, c number);
target: create table t1 (a number, b number, c number);

there is no pk/ui.

ggsci> dblogin user id , password
ggsci> add trandata .s1 <========= this will add all 3 columns to supplemental log group

extract: table .s1;
replicat: map ,s1, target .t1;

insert into s1 values (1,2,3);
commit;

the row will be replicated to t1.

update s1 set c=4;
commit;
the column 'c' in T1 will be updated.

delete s1;
commit;
the row in T1 will be deleted.

if (only if) you know column A is unique even there is no pk/ui.
extract: table .s1, keycols (a);replicat: map .s1, target .t1, keycols (a);
This is covered in version 11 installation guide for oracle db, page 42.
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/161195/viewspace-1055386/，如需轉載，請註明出處，否則將追究法律責任。

相關文章