Oracle GoldenGate 四、数据过滤和数据项匹配

写在开始前

1 问题分析

表结构定义:

  create table OGG_OWNER.STB01(   SID NUMBER(10) PRIMARY KEY,   SNAME VARCHAR2(15) NOT NULL,   SGENDER CHAR(1) CHECK(SGENDER='1' OR SGENDER='2'),   SBRIDATE DATE,   ADETAIL varchar2(70)   ) TABLESPACE TBS01 / 

目标端系统

表结构定义:

  create table OGG_TRG.TTB01(   TID NUMBER(10) PRIMARY KEY,   TNAME VARCHAR2(15) NOT NULL,   TGENDER CHAR(1) CHECK(TGENDER='1' OR TGENDER='2'),   TBRIDATE DATE,   TPROVINCE VARCHAR2(70),   TCITY VARCHAR2(10),   TRANSFERDATE DATE   )TABLESPACE TBS01 / 

1.1 业务需求

同步源端系统表OGG_OWNER.STB01的数据到目标系统表OGG_TRG.TTB01；要求:

• 只同步性别为男的数据(SGENDER=1)
• 源端的地址信息要分别拆分存储到目标端省份字段(TPROVINCE)和城市字段(TCITY)中

2 需求实现

根据需求，分析出大致的处理步骤：

• 源端Extract进程配置抽取表对象
• 源端Data Pump进程配置数据过滤
• 在源端生成表定义文件
• 目标端Replicat进程配置源端表定义信息和源端与目标端字段的对照映射
• 目标端数据库实现省份和城市的数据分拆

2.1 源端Extract进程配置抽取表对象和数据过滤

在主抽取进程(Primary Extract)中配置抽取对象信息

GGSCI (sywu) 2> edit param esydb001  extract ESYDB001 SETENV(ORACLE_SID="sydb") SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB & aes128,ENCRYPTKEY securekey1 ENCRYPTTRAIL aes128,KEYNAME securekey1 EXTTRAIL /u01/app/product/ogg_src/dirdat/es table OGG_OWNER.STB01; 

在Data Pump(Secondly Extract)进程中配置抽取对象和数据过滤

GGSCI (sywu as ogg_owner@sydb) 6> edit param PSYDB001  extract psydb001 SETENV(ORACLE_SID="sydb") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) OBEY /u01/app/product/ogg_src/dirdef/dbConnect.obey ENCRYPTTRAIL aes128 KEYNAME securekey1 RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps table OGG_OWNER.STB01 @filter(SGENDER='1'); 

更多关于数据过滤请参考: GoldenGate Filter

2.2 在源端生成表定义文件

表定义文件记录了交换数据表在数据库中的结构信息，内容包括数据库类型、字符集、时区、数据对象以及数据对象结构的详细信息，对象结构包含的信息如下:

*  Field descriptions for each column entry: * *     1    Name *     2    Data Type *     3    External Length *     4    Fetch Offset *     5    Scale *     6    Level *     7    Null *     8    Bump if Odd *     9    Internal Length *    10    Binary Length *    11    Table Length *    12    Most Significant DT *    13    Least Significant DT *    14    High Precision *    15    Low Precision *    16    Elementary Item *    17    Occurs *    18    Key Column *    19    Sub Data Type *    20    Native Data Type *    21    Character Set *    22    Character Length *    23    LOB Type *    24    Partial Type 

这些信息将帮助GoldenGate实现数据类型转换、匹配以及数据处理，比如源端的数据类型长度可能比目标端长，交换时GoldenGate将根据定义文件截取或转换数据。

2.2.1 什么时候需要定义文件

• 当源端和目标端的表结构不相同时
• 当数据库类型不相同时

2.2.2 生成表定义文件

[oracle@sywu dirdef]$ vim /u01/app/product/ogg_src/dirdef/psydb001.prm   DEFSFILE /u01/app/product/ogg_src/dirdef/psydb001.def, purge  OBEY /u01/app/product/ogg_src/dirdef/dbConnect.obey  table OGG_OWNER.STB01; 

根据定义配置文件生成定义文件

[oracle@sywu ogg_src]$ ./defgen PARAMFILE /u01/app/product/ogg_src/dirdef/psydb001.prm  ***********************************************************************         Oracle GoldenGate Table Definition Generator for Oracle      Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1    Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 05:45:39 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.                     Starting at 2015-09-01 21:49:48 *********************************************************************** Operating System Version: Linux Version #1 SMP Thu Jul 31 17:20:51 UTC 2014, Release 2.6.32-431.23.3.el6.x86_64 Node: sywu Machine: x86_64  soft limit   hard limit Address Space Size   :    unlimited    unlimited Heap Size            :    unlimited    unlimited File Size            :    unlimited    unlimited CPU Time             :    unlimited    unlimited Process id: 7099 *********************************************************************** **            Running with the following parameters                  ** *********************************************************************** DEFSFILE /u01/app/product/ogg_src/dirdef/psydb001.def, purge userid ogg_owner,password ******************************************************************************** aes128 encryptkey securekey1 table OGG_OWNER.STB01; Retrieving definition for OGG_OWNER.STB01. Definitions generated for 1 table in /u01/app/product/ogg_src/dirdef/psydb001.def.  

2.3 目标端Replicat进程配置源端表定义信息和源端与目标端字段的对照映射

将源端生成的表定义文件发送到目标端，然后在Replicat进程中配置表定义信息;

GGSCI (sywu) 9> edit param RSYDB001  REPLICAT rsydb001 SETENV(ORACLE_SID="sydb") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg_trg,password AADAAAAAAAAAAAHABDQFVJMADCAFECACYEPIQEJCFGDGMDHBRJXCUBOBQJEGLBPEBDMCOAACDILGAJKA  & aes128,ENCRYPTKEY securekey1 DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512 DECRYPTTRAIL AES128, KEYNAME securekey1 SOURCEDEFS /u01/app/product/ogg_trg/dirdef/psydb001.def ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG; MAP OGG_OWNER.STB01,target OGG_TRG.TTB01, COLMAP(TID=SID, TNAME=SNAME, TGENDER=SGENDER, TBRIDATE=SBRIDATE, TPROVINCE=ADETAIL, TRANSFERDATE=@DATENOW() ); 

更多的函数说明请参考: GoldenGate官方列转换函数文档

2.3 目标端数据库实现省份和城市的数据分拆

因为有些拆分工作涉及复杂的操作，所以拆分字符的工作可以放在数据库的触发器中完成。

 create or replace trigger OGG_trg.trg_tTB01  before insert or update on OGG_trg.tTB01  for each row      declare   v_temp varchar2(100);      begin   if(:new.TPROVINCE is not null)     then      v_temp:=:new.TPROVINCE;      :new.TCITY:=substr(v_temp,instr(v_temp,'_')+1);      :new.TPROVINCE:=substr(v_temp,0,instr(v_temp,'_')-1);         end if;      end; /  

3 业务测试

启动源端和目标端的进程后在源端插入测试数据

insert into OGG_OWNER.STB01(   SID   ,  SNAME    ,  SGENDER  ,  SBRIDATE ,  ADETAIL ) select  1,'sywu','1',sysdate,'云南省_临沧' from dual     union all select  2,'xiaohong','2',sysdate,'云南省_昆明' from dual / commit; 

检验数据

SYS@sydb>column TPROVINCE format a30 SYS@sydb>select * from OGG_OWNER.STB01;        SID SNAME   S SBRIDATE   ADETAIL ---------- --------------- - ------------------ ----------------------------------------------------------------------  1 sywu    1 08-SEP-15  云南省_临沧  2 xiaohong        2 08-SEP-15  云南省_昆明 Elapsed: 00:00:00.00 SYS@sydb>select * from OGG_trg.ttb01;        TID TNAME   T TBRIDATE   TPROVINCE      TCITY      TRANSFERDATE ---------- --------------- - ------------------ ------------------------------ ---------- ------------------  1 sywu    1 08-SEP-15  云南省_临沧       08-SEP-15 Elapsed: 00:00:00.01  

4 GoldenGate支持触发器

通过以上测试查询发现，源端的数据可以交换到目标端了，但是目标端并没有实现将地址字段数据分拆，为什么？

SYS@sydb>column OWNER format a10 SYS@sydb>column TRIGGER_NAME format a20 SYS@sydb>column TRIGGER_TYPE  format a20 SYS@sydb>column TRIGGERING_EVENT format a20 SYS@sydb>column TABLE_NAME format a20 SYS@sydb>select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME,STATUS from dba_triggers where trigger_name='TRG_TTB01';  OWNER      TRIGGER_NAME         TRIGGER_TYPE         TRIGGERING_EVENT     TABLE_NAME           STATUS ---------- -------------------- -------------------- -------------------- -------------------- -------- OGG_TRG    TRG_TTB01            BEFORE EACH ROW      INSERT OR UPDATE     TTB01                ENABLED  Elapsed: 00:00:00.09 

但查询触发器是可用的哦，于是在官方文档上发现这样一段描述: 官方文档DBOPTIONS参数 ；

SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)

SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.

To allow a specific trigger to fire, you can use the following SQLEXEC statement in the Replicat parameter file, where trigger_owner is the owner of the trigger and trigger_name is the name of the trigger.

SQLEXEC 'dbms_ddl.set_trigger_firing_property(trigger_owner "trigger_name", FALSE);' 

你应该注意到了fire是默认值，so 要启用触发器只要设置NOSUPPRESSTRIGGERS即可；

GGSCI (sywu) 16> edit param RSYDB001 REPLICAT rsydb001 SETENV(ORACLE_SID="sydb") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg_trg,password AADAAAAAAAAAAAHABDQFVJMADCAFECACYEPIQEJCFGDGMDHBRJXCUBOBQJEGLBPEBDMCOAACDILGAJKA  & aes128,ENCRYPTKEY securekey1 DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512 DECRYPTTRAIL AES128, KEYNAME securekey1 SOURCEDEFS /u01/app/product/ogg_trg/dirdef/psydb001.def ALLOWNOOPUPDATES ASSUMETARGETDEFS DBOPTIONS NOSUPPRESSTRIGGERS MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG; MAP OGG_OWNER.STB01,target OGG_TRG.TTB01, COLMAP(TID=SID, TNAME=SNAME, TGENDER=SGENDER, TBRIDATE=SBRIDATE, TPROVINCE=ADETAIL, TRANSFERDATE=@DATENOW() ); 

现在就可以了

truncate table OGG_OWNER.STB01; truncate table OGG_trg.ttb01; insert into OGG_OWNER.STB01(   SID   ,  SNAME    ,  SGENDER  ,  SBRIDATE ,  ADETAIL ) select  1,'sywu','1',sysdate,'云南省_临沧' from dual     union all select  2,'xiaohong','2',sysdate,'云南省_昆明' from dual / commit; SYS@sydb>select * from OGG_OWNER.STB01;        SID SNAME   S SBRIDATE   ADETAIL ---------- --------------- - ------------------ ----------------------------------------------------------------------  1 sywu    1 08-SEP-15  云南省_临沧  2 xiaohong        2 08-SEP-15  云南省_昆明 Elapsed: 00:00:00.00 SYS@sydb>select * from OGG_trg.ttb01;        TID TNAME   T TBRIDATE   TPROVINCE      TCITY      TRANSFERDATE ---------- --------------- - ------------------ ------------------------------ ---------- ------------------  1 sywu    1 08-SEP-15  云南省         临沧       08-SEP-15 Elapsed: 00:00:00.00  

5 总结

数据过滤和数据匹配是数据交换中经常会涉及到的工作，每个工作都会有不同的处理和实现方式，但总的需求是不变的，对于GoldenGate重要的时去了解它的工作原理和处理数据时与其它工具不同的微妙之处，比如去了解和思考它如何通过某些自带的参数实现交换数据时禁用触发器和在不同的数据类型、长度转换间它截取放大数据的方式。

--The end(2015-09-09)