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 /
目標端系統 字段名 說明 TID 用戶ID TNAME 用戶名 TGENDER 性別,值為1表示男,2表示女 TBRIDATE 出生日期 TPROVINCE 省份 TCITY 城市 TRANSFERDATE 交換日期 表結構定義:
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 生成表定義文件 生成表定義文件需要先創建定義配置文件,配置文件中指定定義文件存儲路徑和文件名(DEFSFILE)、數據庫連接信息、需要生成表定義信息的對象(table);最後再根據定義配置文件通過GoldenGate自帶的defgen命令生成表定義文件。 創建定義配置文件 [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() );
SOURCEDEFS 定義源端的表結構定義文件信息; COLMAP 定義源端表和目標表的數據項對照映射; 常用的GoldenGate函數: 函數名 說明 @DATE 返回轉換日期格式 @DATENOW 返回當前系統時間 @GETENV 返回GoldenGate環境變量值 @STRFIND 返回指定字符在字符串中的位置 更多的函數說明請參考: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重要的時去了解它的工作原理和處理數據時與其它工具不同的微妙之處,比如去了解和思考它如何通過某些自帶的參數實現交換數據時禁用觸發器和在不同的數據類型、長度轉換間它截取放大數據的方式。