Goldengate source and target table differ in column structure part-1(ogg同步不同的表结构数据)

首页 » OGG » Goldengate source and target table differ in column structure part-1(ogg同步不同的表结构数据)

Goldengate source and target table differ in column structure part-1(ogg同步不同的表结构数据)

2013/09/18
OGG
1,438 views
1 条评论

昨天一朋友问如果用GOLDENGATE同步数据(ETL)，如果目标端比源端多出一个时间字段表示最后的修改日期，OGG可以实现么？可以的，下面是我step-by-step 的演示.耗时两天做个总结，在这特殊日子也祝各位库友中秋团圆！

source db
sid:icme
tablename:test_t
-------------
|id ,name   |
-------------

target db 
sid:ggs
tablename:test_t
----------------------------------------
|id ,name, create_date, last_update_date|
-----------------------------------------

create_date 取记录的生成时间
last_update_date 取最后DML的时间

Source DB

— 建立新表

icme@ICME>create table test_s(id int,name varchar2(20));

— ogg 的配置文件，这是我之前配置源和目标库表结构一样的环境，有启用DDL同步,后面我会陆续修改，OGG安装参考以前的文章

GGSCI (icme-db) 2> view params eicmec

extract eicmec
------------------------------------------
-- local extract for icme schema change
-- date:2012-10-16
-- zhangweizhao
-----------------------------------------
userid ggsmgr, password "AACAAAAAAAAAAAKAEJMJUCZILBNFVEYDOCBENFFEMGGFYGGD", encryptkey default

reportcount every 30 minutes, rate
report at 01:00
reportRollover at 01:20
discardFile dirrpt/eicmec.dsc, append
discardRollover at 02:00 on sunday
exttrail dirdat/l1
-- support DDL
DDL include mapped
table icme.*;
TABLEEXCLUDE icme.cme2icme*
TABLEEXCLUDE icme.add_*
TABLEEXCLUDE icme.tmp_*
TABLEEXCLUDE icme.summar*
TABLEEXCLUDE ICME.TOAD_PLAN_TABLE

–才用最典型的方法，local extract eicmec 抽取日志生成trail,再有eicmed data pump extract 到目标端,目标端replicat 应用变化。

GGSCI (icme-db) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EICMEC 00:00:00 00:00:07
EXTRACT RUNNING EICMED 00:00:00 00:00:00

Target DB

–ogg replicat 配置文件

GGSCI (ggsdb) 2> view params ricme

replicat ricme
-----------------------------------------
-- replicat capture to icme
-- 2012-10-16
-- zhangweizhao
-----------------------------------------
userid ggsmgr, password "AACAAAAAAAAAAAKAEJMJUCZILBNFVEYDOCBENFFEMGGFYGGD", encryptkey default
--handlecollisions
AssumeTargetDefs
ReportCount Every 30 Minutes, Rate
Report at 01:00
ReportRollover at 01:15
-- if error not abending,only write discard file 
reperror default, discard   
DiscardFile dirrpt/ricme.dsc, Append, megabytes 400
DiscardRollover at 02:00 ON SUNDAY
-- trunning batch DML ON a large table
-- BATCHSQL
-- support DDL here
DDL

DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20 
MAPEXCLUDE icme.SYS_EXPORT_SCHEMA_01
MAPEXCLUDE icme.PLAN_TABLE
Map icme.*, Target icme.* ;

因为启用了DDL 同步，所以在源端建立了TEST_S以后，目标端也自动同步了TEST_S表，现在在目标端再创建一个不同表结构的表test_t

icme@GGS>create table test_t as select * from test_s where 1=0;
Table created.
icme@GGS>alter table test_t add create_date date;
Table altered.
icme@GGS>alter table test_t add last_update_date date;
Table altered.
icme@GGS>desc test_t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)
 CREATE_DATE                                        DATE
 LAST_UPDATE_DATE                                   DATE

Source DB

生成表定义文件

GGSCI (icme-db) 1> edit params deficme
DEFSFILE ./dirsql/deficme.prm
userid ggsmgr, password "AACAAAAAAAAAAAKAEJMJUCZILBNFVEYDOCBENFFEMGGFYGGD", encryptkey default

table icme.*;

[oracle@icme-db ogg11r2]$ ./defgen paramfile dirprm/deficme.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
   Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 05:09:39

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
                    Starting at 2013-09-17 15:14:33
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Nov 16 12:49:06 EST 2007, Release 2.6.9-67.ELsmp
Node: icme-db
Machine: x86_64

Retrieving definition for ICME.YDYF_PATIENT_RESULT
...
Definitions generated for 135 tables in ./dirsql/deficme.prm

[oracle@icme-db ogg11r2]$ ll dirsql/deficme.prm 
-rw-rw-rw-  1 oracle oracle 130209 Sep 17 15:14 dirsql/deficme.prm

-- 传送表定义文件到目标端
We now go and make a change to the original replicat parameter file and change the parameter ASSUMEDEFS to SOURCEDEFS which provides GoldenGate with the location of the definitions file.

[oracle@icme-db ogg11r2]$ scp dirsql/deficme.prm 192.168.212.44:/home/oracle/

Target DB

vi deficme.prm

*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified  2013-09-17 15:14
*
*  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
*
Database type: ORACLE
Character set ID: windows-936
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
...
Definition for table ICME.TEST_S
Record length: 82
Syskey: 0
Columns: 2
ID     64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NAME   64     20       56  0  0 1 0     20     20      0 0 0 0 0 1    0 1 0
End of definition
...

[oracle@ggsdb ogg11r2]$ cp ~/deficme.prm ./dirsql/
[oracle@ggsdb ogg11r2]$ ll dirsql/deficme.prm
-rw-r–r– 1 oracle oinstall 130209 Sep 17 15:10 dirsql/deficme.prm

GGSCI (ggsdb) 1> edit params ricme
replicat ricme
—————————————–
— replicat capture to icme
— 2012-10-16
— zhangweizhao
—————————————–
userid ggsmgr, password “AACAAAAAAAAAAAKAEJMJUCZILBNFVEYDOCBENFFEMGGFYGGD”, encryptkey default

–handlecollisions
–AssumeTargetDefs

SOURCEDEFS dirsql/deficme.prm

ReportCount Every 30 Minutes, Rate
Report at 01:00
ReportRollover at 01:15
— if error not abending,only write discard file
reperror default, discard

DiscardFile dirrpt/ricme.dsc, Append, megabytes 400

DiscardRollover at 02:00 ON SUNDAY
— trunning batch DML ON a large table
— BATCHSQL
— support DDL here
–DDL
–DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
map icme.test_s, target icme.test_t,
COLMAP (usedefaults,
create_date = @datenow(),
last_update_date=@datenow());

mapexclude icme.test_s

Map icme.*, Target icme.* ;

GGSCI (ggsdb) 2> stop ricme
GGSCI (ggsdb) 2> start ricme

Tip:
Use the ASSUMETARGETDEFS parameter when the source and target tables specified with a MAP statement have identical column structure
It directs Oracle GoldenGate not to look up source structures from a source-definitions file.
For source and target column structures to be identical, they must:
● have identical column names (including case, if applicable)
● have identical data types
● have identical column sizes
● have the same column length semantics for character columns (bytes versus characters)
● appear in the same order in each table
If source and target tables do not have the same structure, use the SOURCEDEFS parameter instead of ASSUMETARGETDEFS

Verify Data Synchronization

Source DB

SQL> insert into icme.test_s values(1,’a’);
1 row created.
SQL> commit;
Commit complete.

Target DB

icme@GGS>select * from test_s;
ID NAME
——————– ——————–
1 a
icme@GGS>select * from test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
——————– ——————– ——————- ——————-
1 a 2013-09-17 15:18:51 2013-09-17 15:18:51

Notice:
可以看到数据是同步的，问题是现在同步了两张表，同名的表和MAP的表，接下来解决如何只同步到MAP表test_t

Target DB

–修改replicat的配置文件

map icme.test_s, target icme.test_t,
COLMAP (usedefaults,
create_date = @datenow(),
last_update_date=@datenow());

mapexclude icme.test_s
Map icme.*, Target icme.* ;

修改为

NODYNAMICRESOLUTION
WILDCARDRESOLVE IMMEDIATE
 
map icme.test_s, target icme.test_t,
COLMAP (usedefaults,
create_date = @datenow(),
last_update_date=@datenow());

DYNAMICRESOLUTION
mapexclude icme.test_s

Map icme.*, Target icme.* ;

TIP:
Use the DYNAMICRESOLUTION and NODYNAMICRESOLUTION parameters to control how table names are resolved.DYNAMICRESOLUTION, the default, enables fast process startup when there are numerous tables specified in TABLE or MAP statements. DYNAMICRESOLUTION causes the record to be built one table at a time,instead of all at once. DYNAMICRESOLUTION is the same as WILDCARDRESOLVE DYNAMIC. NODYNAMICRESOLUTION causes the entire object record (for all tables) to be built at startup,which can be time-consuming if the database is large.

Verify

— source
SQL> insert into icme.test_s values(9,’h’);
1 row created.
SQL> commit;

— target
icme@GGS>select * from test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
——————– ——————– ——————- ——————-
1 a 2013-09-17 15:18:51 2013-09-17 15:18:51
9 h 2013-09-17 16:19:33 2013-09-17 16:19:33

icme@GGS>select * from test_s;
ID NAME
——————– ——————–
1 a

ok. 到这里发现数据已实现了从target的test_s到test_t，字段不同时的数据同步。下篇演示如何实现ogg map的两个字段create_time为记录生成时的时间，last_update_date为记录最后更新时间。

打赏

Goldengate

目前这篇文章有1条评论(Rss)评论关闭。

fake nfl jerseys | #1
2013-10-12 at 15:09

I would like to show my thanks to this writer just for rescuing me from this particular crisis. Just after searching through the world-wide-web and finding techniques that were not pleasant, I figured my well being was done. Living without the presence of st.

上一篇： Goldengate source and target table differ in column structure part-2(ogg同步不同的表结构数据)

下一篇： Dynamically Changing shared_pool_size fail!(动态修改Shared pool size失败)

ANBOB™