goldengate 中文表/中文字段处理

ALLSTARS_ORACLE

goldengate 中文表/中文字段处理


create table 测试表(
ID NUMBER,
姓名 VARCHAR2(30),
FLAG CHAR(1),
CONSTRAINT PK_TESTD PRIMARY KEY (ID) USING INDEX);

--源端创建MV LOG和MV：
drop materialized view log on "测试表";
create materialized view log on "测试表" with primary key;
drop materialized view mv_cn_table;
create materialized view mv_cn_table refresh fast on commit as select id,姓名 as en_name,flag from "测试表";

--目标端创建表及view
create or replace view v_cn_table as select id,姓名 as en_name,flag  from 测试表;

--这里NLS_LANG在GG中，抽取和复制必须设置为和目标字符集一致
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

--Extract
extract ODISC
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid custom_src, password custom_src
exttrail D:/goldengate/dirdat/ODISoc/oc
TABLE CUSTOM_SRC.MV_CN_TABLE;
--Datapump
extract ODIT1P
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
PASSTHRU
rmthost localhost, mgrport 7909
rmttrail D:/gg_stg/dirdat/ODIT1op/op
TABLE CUSTOM_SRC.MV_CN_TABLE;

--Replicat
replicat ODIT1A1
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid odi_staging,  password odi_staging
discardfile D:/gg_stg/dirrpt/ODIT1.dsc, purge
ASSUMETARGETDEFS
--这里必须指定此参数，否则update有问题
APPLYNOOPUPDATES
--这里必须指定KEYCOLS，否则删除和更新有问题
map CUSTOM_SRC.MV_CN_TABLE, TARGET ODI_STAGING.V_CN_TABLE, KEYCOLS (ID);