源端Oracle表结构:
[id] [numeric](18, 0) NOT NULL,
[full_name] [varchar](300) NULL,
[wages] [numeric](18, 4) NULL,
[first_name] [varchar](200) NULL,
[last_name] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
源端extract配置:
GGSCI (chenyj-WorkPC) 7> view param oraext1
extract oraext1
userid ggs, password ggs
GETTRUNCATES
exttrail D:\ggora-ms\ggsora\dirdat\oa
table scott.customer;
table scott.myemps;
GGSCI (chenyj-WorkPC) 8> view param pumpdb
EXTRACT pumpdb
USERID ggs, PASSWORD ggs
GETTRUNCATES
RMTHOST 127.0.0.1, MGRPORT 7700
RMTTRAIL D:\ggora-ms\ggsms\dirdat\or
table scott.customer;
table scott.myemps;
目标端replicat配置:
GGSCI (chenyj-WorkPC) 9> view param msrep1
REPLICAT MSREP1
targetdb ggs_nc
GETTRUNCATES
APPLYNOOPUPDATES
SOURCEDEFS dirdef/source.def
MAP scott.customer, TARGET dbo.customer;
map scott.myemps, target dbo.myemps,
COLMAP (USEDEFAULTS,
WAGES = @COMPUTE(SALARY * 12)
FULL_NAME = @STRCAT(LAST_NAME,",",FIRST_NAME));
源端插入数据然后更新:
insert into myemps(id,first_name,last_name,salary) values(2, 'A', 'B', 100000);
commit;
update myemps set first_name = 'CCCC' where id = 2;
commit;
同步之后, SQLServer里看到的full_name应该是B,CCCC,而不应该是B,A。
在OTN发帖:https://forums.oracle.com/thread/2561302
得到的提示是因为supplemental log的设置问题,更新last_name时,如果要使目标端的full_name的结果正确,应该同时将first_name与last_name写入redo log.
通过在oracle里执行:
SQL> alter table scott.myemps add supplemental log data(all) columns;
表已更改。
再做相同的测试,发现full_name结果正确了。
There are several types of supplemental logging:
- Minimal
- Primary Key
- Unique Key
- Foreign Key
- All
- Procedural Replication
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-766663/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-766663/