oracle goldengate 添加新表

1. 源端，添加标记日志


cd /u01/ggs
./ggsci


dblogin USERID ogg, PASSWORD welcome1


add trandata CISADM.CI_REG
add trandata CISADM.CI_ACCT_CHAR
add trandata CISADM.CI_SP_CHAR
add trandata CISADM.CI_PER_ID




SQL> select to_char(dbms_flashback.get_system_change_number)  from dual;


TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
----------------------------------------
5985028487633


SQL> 


2. 源端，lag为0 停掉抽取进程


stop EPCCB01


编辑抽取进程参数


edit params EPCCB01    


添加


table CISADM.CI_REG       ,   TOKENS (UPDATE_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_ACCT_CHAR ,   TOKENS (UPDATE_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_SP_CHAR   ,   TOKENS (UPDATE_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_PER_ID    ,   TOKENS (UPDATE_DATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));


3. 源端， lag为0，停掉投递进程


stop PUMP1


编辑进程参数


edit params PUMP1


添加


table CISADM.CI_REG       ,TOKENS (TKN-COMMITTIME = @GETENV("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_ACCT_CHAR ,TOKENS (TKN-COMMITTIME = @GETENV("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_SP_CHAR   ,TOKENS (TKN-COMMITTIME = @GETENV("GGHEADER", "COMMITTIMESTAMP"));
table CISADM.CI_PER_ID    ,TOKENS (TKN-COMMITTIME = @GETENV("GGHEADER", "COMMITTIMESTAMP"));


4. 目标端， lag为0停止REBI01


编辑进程参数


edit params REBI01


添加


map CISADM.CI_REG       , target  DW_IDL.DW_CIS_CI_REG ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME"));
map CISADM.CI_ACCT_CHAR , target  DW_IDL.DW_CIS_CI_ACCT_CHAR ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME"));
map CISADM.CI_SP_CHAR   , target  DW_IDL.DW_CIS_CI_SP_CHAR ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME"));
map CISADM.CI_PER_ID    , target  DW_IDL.DW_CIS_CI_PER_ID ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME"));








map CISADM.CI_REG       , target  DW_IDL.DW_CIS_CI_REG ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME")), filter ( @getenv("TRANSACTION", "CSN") > 5985028487633);
map CISADM.CI_ACCT_CHAR , target  DW_IDL.DW_CIS_CI_ACCT_CHAR ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME")), filter ( @getenv("TRANSACTION", "CSN") > 5985028487633);
map CISADM.CI_SP_CHAR   , target  DW_IDL.DW_CIS_CI_SP_CHAR ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME")), filter ( @getenv("TRANSACTION", "CSN") > 5985028487633);
map CISADM.CI_PER_ID    , target  DW_IDL.DW_CIS_CI_PER_ID ,  COLMAP (USEDEFAULTS, UPDATE_DATE=@TOKEN("TKN-COMMITTIME")), filter ( @getenv("TRANSACTION", "CSN") > 5985028487633);


 
5. 源端，生成source文件


cd /u01/ggs/dirprm


vi DEPCCB01.prm


添加


table CISADM.CI_REG;
table CISADM.CI_ACCT_CHAR;
table CISADM.CI_SP_CHAR;
table CISADM.CI_PER_ID;


生成表结构定义文件:


cd /u01/ggs
./defgen paramfile /u01/ggs/dirprm/DEPCCB01.prm


拷贝/u01/ggs/dirdef/DEPCCB01.def文件到目标端目录/u01/ggs/dirdef，注意修改权限






5. 源端，启动各个进程


start EPCCB01
start PUMP1




6. 数据初始化


在目标端DW_IDL用户下新建表，并增加UPDATE字段（已经完成）


导出数据


expdp parfile=/u01/dmp/ogg_exp_20160428.par


USERID      = "/ AS SYSDBA"
DIRECTORY   = dmp
DUMPFILE    = ogg_20160428.dmp
LOGFILE     = ogg_20160428_exp.log
PARALLEL    = 4
EXCLUDE     = STATISTICS
CLUSTER     = N
FLASHBACK_SCN = 5985028487633
TABLES      = CISADM.CI_REG,CISADM.CI_ACCT_CHAR,CISADM.CI_SP_CHAR,CISADM.CI_PER_ID




导入数据


impdp parfile=/u01/dmp/ogg_imp_20160428.par


USERID      = "/ AS SYSDBA"
DIRECTORY   = dmp
DUMPFILE    = ogg_20160428.dmp
LOGFILE     = ogg_20160428_imp.log
PARALLEL    = 4
CLUSTER     = N
remap_schema= cisadm:DW_IDL
remap_tablespace = CISTSX:TBS_IDL_DATA,CISTS_01:TBS_IDL_DATA
remap_table =  CISADM.CI_REG:DW_CIS_CI_REG,CISADM.CI_ACCT_CHAR:DW_CIS_CI_ACCT_CHAR,CISADM.CI_SP_CHAR:DW_CIS_CI_SP_CHAR,CISADM.CI_PER_ID:DW_CIS_CI_PER_ID


7. 目标端，启动进程


start REBI01