ogg同步异构表的步骤.doc

Ogg异构表的同步:第一步：使用defgen建立源端表定义文件(源端)1、编辑defgen文件(文件目录在./ dirprm下) -edit params defgen 编辑命令 -defgen 为文件名DEFSFILE /u01/app/ogg/11.0/dirdef/ru.def -ru为自定义的文件名称userid ogguser,password ogguser -登录数据库的用户名和密码TABLE test.CK_RUKMX; -需要同步到目标端的表TABLE test.CK_UCBQ;TABLE test.CK_FANXD;TABLE test.CK_ZHENGCTK;TABLE test.CK_PANDCY;TABLE test.CK_JIUFD; 2、执行defgen命令在ogg中的目录下运行以下命令 -与运行./ggsci为同一目录./defgen paramfile ./dirprm/defgen.prm -在dirdef目录下生成ru.def3、把源端生成的def文件ftp传输到目标端dirdef 目录下第二步：在源端新建抽取和传输二个进程，步骤与之前非异构相同1、在源端新建ogg抽取进程 EXTRACT EORA_2SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogguser, PASSWORD ogguser EXTTRAIL ./dirdat/a2 TABLE test.CK_RUKMX;TABLE test.CK_UCBQ;TABLE test.CK_FANXD;TABLE test.CK_ZHENGCTK;TABLE test.CK_PANDCY;TABLE test.CK_JIUFD;2、在源端新建ogg传输进程 EXTRACT PORA_2 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) PASSTHRU RMTHOST mrhathdt02, MGRPORT 7809 RMTTRAIL /u01/app/ogg/11.0/dirdat/r2TABLE test.CK_RUKMX;第三步：在目标端新建入库进程，步骤与之前非异构相同，参数有所改变REPLICAT RORA_2 SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogguser, PASSWORD ogguser HANDLECOLLISIONS -这个是异构的关键参数，ru.def是之前源端ftp传输过来的文件；SOURCEDEFS /u01/app/ogg/11.0/dirdef/ru.def DISCARDFILE /u01/app/ogg/11.0/dirrpt/RINIa2.dsc, PURGEALLOWNOOPUPDATES-由于异构之间的同步，所以列标不同，因此必需写明双方字段之的映射MAP test.CK_RUKMX, TARGET test001.CK_LIUSZ, COLMAP(CAOZM = IF(RUKLX = 1),A01,COLSTAT(NULL),LINGJBH = LINGJBH,GONGYSDM = GONGYSDM, YAOHLLX = COLSTAT(NULL),XIEHZT = COLSTAT(NULL),LINGJSL = LINGJSL, DANW = Danw,BL = BLH,RUKSJ = RUKSJ,JIUFSJ = COLSTAT(NULL), CHENGYSDM = CHENGYSDM,CANGKBH = CANGKBH,ZICKBH = ZICKBH,PICH = PICH, DINGDH = DINGDH,USERCENTER = USERCENTER,UTH = UTH,ELH = ELH,CHEJ = CHANX, CAIWRQ = EDIT_TIME,UAH = COLSTAT(NULL),ZHANGH = COLSTAT(NULL), KEHBH = COLSTAT(NULL), MUDD = COLSTAT(NULL),UCH = COLSTAT(NULL),XINCKBM = COLSTAT(NULL), XINZCKBM = COLSTAT(NULL),YIKID = COLSTAT(NULL),USH = COLSTAT(NULL),XINUSH = COLSTAT(NULL), KUWBH = COLSTAT(NULL),XINKWBM = COLSTAT(NULL),CREATOR = interface, CREATE_TIME = COLSTAT(NULL),EDITOR = COLSTAT(NULL),EDIT_TIME = COLSTAT(NULL);MAP test.CK_USBQ, TARGET test001.CK_LIUSZ, COLMAP(CAOZM = A01S ,LINGJBH = LINGJBH,GONGYSDM = GONGYSDM,YAOHLLX = COLSTAT(NULL),XIEHZT = COLSTAT(NULL),LINGJSL = LINGJSL,DANW = Danw,BL = BLH,RUKSJ = RUKRQ,JIUFSJ = COLSTAT(NULL),CHENGYSDM = CHENGYSDM,CANGKBH = CANGKBH,ZICKBH = ZICKBH,PICH = PICH,DINGDH = COLSTAT(NULL) ,USERCENTER = USERCENTER,UTH = UTH,ELH = ELH,CHEJ = CHANX,CAIWRQ = EDIT_TIME,UAH = COLSTAT(NULL),ZHANGH = COLSTAT(NULL), KEHBH = COLSTAT(NULL),MUDD = COLSTAT(NULL),UCH = COLSTAT(NULL),XINCKBM = COLSTAT(NULL),XINZCKBM = COLSTAT(NULL),YIKID = COLSTAT(NULL),USH = COLSTAT(NULL),XINUSH = COLSTAT(NULL),KUWBH = COLSTAT(NULL),XINKWBM = COLSTAT(NULL),CREATOR = interface,CREATE_TIME = COLSTAT(NULL),EDITOR = COLSTAT(NULL),EDIT_TIME = COLSTAT(NULL);注意的几点：1.由于异构同步，数据的同步将是难点；2.对于数据更新，删除最好进行避免;请在目标端执行以下DDL: (请注意修改表空间)- Create tablecreate table CK_LIUSZ( CAOZM VARCHAR2(5), LINGJBH VARCHAR2(10), GONGYSDM VARCHAR2(10), YAOHLLX CHAR(2), XIEHZT VARCHAR2(10), LINGJSL NUMBER(13,3), DANW VARCHAR2(2), BL VARCHAR2(17), RUKSJ DATE, JIUFSJ DATE, CHENGYSDM VARCHAR2(10), CANGKBH VARCHAR2(3), ZICKBH VARCHAR2(3), PICH VARCHAR2(12), DINGDH VARCHAR2(12), USERCENTER VARCHAR2(3), UTH VARCHAR2(10), ELH VARCHAR2(10), CHEJ VARCHAR2(3), CAIWRQ DATE, UAH VARCHAR2(12), ZHANGH VARCHAR2(11), KEHBH VARCHAR2(10), MUDD VARCHAR2(13), UCH VARCHAR2(12), XINCKBM VARCHAR2(3), XINZCKBM VARCHAR2(3), YIKID VARCHAR2(12), USH VARCHAR2(10), XINUSH VARCHAR2(10), KUWBH VARCHAR2(6), XINKWBM VARCHAR2(6), CREATOR VARCHAR2(32), CREATE_TIME DATE, EDITOR VARCHAR2(32), EDIT_TIME DATE)tablespace TBS_GGUSER pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 1 minextents 1 maxextents unlimited );- Add comments to the columns comment on column CK_LIUSZ.CAOZM is 操作码;comment on column CK_LIUSZ.LINGJBH is 零件编号;comment on column CK_LIUSZ.GONGYSDM is 供应商代码;comment on column CK_LIUSZ.YAOHLLX is 供货模式;comment on column CK_LIUSZ.XIEHZT is 卸货点;comment on column CK_LIUSZ.LINGJSL is 零件数量;comment on column CK_LIUSZ.DANW is 单位;comment on column CK_LIUSZ.BL is BL号;comment on column CK_LIUSZ.RUKSJ is 入库日期;comment on column CK_LIUSZ.JIUFSJ is 纠纷时间;comment on column CK_LIUSZ.CHENGYSDM is 承运商代码;comment on column CK_LIUSZ.CANGKBH is 仓库代码;comment on column CK_LIUSZ.ZICKBH is 子仓库代码;comment on column CK_LIUSZ.PICH is 批次号;comment on column CK_LIUSZ.DINGDH is 订单号;comment on column CK_LIUSZ.USERCENTER is 用户中心;comment on column CK_LIUSZ.UTH is UT号;comment on column CK_LIUSZ.ELH is EL号;comment on column CK_LIUSZ.CHEJ is 车间;comment on column CK_LIUSZ.CAIWRQ is 财务日日期;comment on column CK_LIUSZ.UAH is UA号;comment on column CK_LIUSZ.ZHANGH is 成本中心账户;comment on column CK_LIUSZ.KEHBH is 客户码;comment on column CK_LIUSZ.MUDD is 消耗点;comment on column CK_LIUSZ.UCH is UC号;comment on column CK_LIUSZ.XINCKBM is 新仓库代码;comment on column CK_LIUSZ.XINZCKBM is 新子仓库代码;comment on column CK_LIUSZ.YIKID is 移库单号;comment on column CK_LIUSZ.USH is 原US号;comment on column CK_LIUSZ.XINUSH is 现US号;comment on column CK_LIUSZ.KUWBH is 原库位编号;comment on column CK_LIUSZ.XINKWBM is 现库位编号;comment on column CK_LIUSZ.