Oracle GoldenGate 4. Data Filtering and data item matching

Oracle GoldenGate 4. Data Filtering and data item matching
Read navigation write in before start 1 problem analysis 1.1 Business Requirements 2 requirements implement 2.1 source Extract Process configuration Extract table objects and data filtering 2.2 generate table definition file on source 2.2.1 when need definition file 2.2.2 generate table definition file 2.3 target end Replicat Process configuration source end table definition information source end and target end field comparison ing 2.3 target end database implementation provincial and city data split 3 Business Test 4 GoldenGate support trigger 5 Summary: I spent a lot of time reading GoldenGate official documents two weeks ago, learn about GoldenGate based on the practice and further. The following is a summary of GoldenGate based on the official documents: oracle GoldenGate 1. Introduction and installation of Oracle GoldenGate 2. Configuration and use of Oracle GoldenGate 3. Encryption in [us] motimer J. how to Read A Book emphasizes that if you cannot describe or remember the knowledge you have learned every day in your own language, it means that you have not actually learned and understood what you have learned, so after that, I changed my learning style and changed my new learning style to describing and memorizing it with my own language and understanding. There is something wrong with the description. Please forgive me, thank you for your guidance! 1. The problem analysis involves the following two STB01 and TTB01 tables. The STB01 tables are stored in the source, and the TTB01 tables are stored in the target. The table structure is as follows: the field name of the source system indicates the gender of the SID user IDSNAME user name SGENDER. The value 1 indicates male, and 2 indicates the ADETAIL address of the female SBRIDATE birth date. The storage format is "province _ City", for example: table Structure Definition in Yunnan Province and Kunming City:

 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/

 

The field name of the target system indicates the TGENDER Gender of the IDTNAME username of the TID user. The value 1 indicates male, and 2 indicates the TBRIDATE birth date. The TPROVINCE province TCITY city TRANSFERDATE interchange date table structure definition:

  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 business needs to synchronize data from the source system table OGG_OWNER.STB01 to the target system table OGG_TRG.TTB01; Requirement: Only data with male gender is synchronized (SGENDER = 1) the source address information should be split and stored into the target province field (TPROVINCE) and the City field (TCITY). The following steps are analyzed based on the requirements: source end Extract Process configuration extraction table object source end Data Pump Process configuration Data filtering source end generation table definition file target end Replicat Process configuration source end table definition information source end and target end field comparison map the target database to split data between provinces and cities. 2.1 configure the source Extract process to Extract table objects and filter data in the master Extraction Process (Primary Extract) configure extracted Object Information

GGSCI (sywu) 2> edit param esydb001extract ESYDB001SETENV(ORACLE_SID="sydb")SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB &aes128,ENCRYPTKEY securekey1ENCRYPTTRAIL aes128,KEYNAME securekey1EXTTRAIL /u01/app/product/ogg_src/dirdat/estable OGG_OWNER.STB01;

 

Configure the extraction object and Data Filtering in the Data Pump (Secondly Extract) Process

GGSCI (sywu as ogg_owner@sydb) 6> edit param PSYDB001extract psydb001SETENV(ORACLE_SID="sydb")SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)OBEY /u01/app/product/ogg_src/dirdef/dbConnect.obeyENCRYPTTRAIL aes128 KEYNAME securekey1RMTTRAIL /u01/app/product/ogg_trg/dirdat/pstable OGG_OWNER.STB01 @filter(SGENDER='1');