在业务上，对golendgate有这样的需求，源端的表的结构与目标端的结构不相同，表名称相同，但是表的字段不一样.我这里的举例是源端表的字段个数与目标端的字段个数不相同，目标端的表的字段个数是源端表的字段的子集.比如，源端表的字段为30个字段，而目标端的字段为源端的子集，有10个字段，只同步这10个字段的数据。其实，就是对某些敏感的数据不进行同步而已。今天因为业务上有这个需求，就翻照golendgate文档进程操作，操作成功，满足了业务的需求。我这里分享一下这个案例，创建一个表进行举例：
[@more@]defgen
源端：
1.表结构
SQL> desc gis_test.baobao14;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
PXID NUMBER
FANG VARCHAR2(10) Y
TDD VARCHAR2(20) Y
AWR VARCHAR2(15) Y
alter table gis_test.baobao14 add constraint pk_pxid primary key (pxid);
目标端：
1. 表结构
SQL> desc gis_test.baobao14;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
PXID NUMBER
FANG VARCHAR2(10) Y
alter table gis_test.baobao14 add constraint pk_pxid primary key (pxid);
源端与目标端表gis_test.baobao14结构不相同
如何同步数据！
使用defgen生成源端定义文件来同步数据。
源端操作：
GGSCI (oggmiddle) 19> edit params defgen
DEFSFILE /u01/app/oracle/ggs/dirdef/baobao14.def
USERID ggs@dbking, PASSWORD ggs
TABLE GIS_TEST.BAOBAO14;
保存.
GGSCI (oggmiddle) 20>exit
[oracle@oggmiddle ggs]$ pwd
/u01/app/oracle/ggs
[oracle@oggmiddle ggs]$ defgen paramfile /u01/app/oracle/ggs/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 05:00:20
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-07-22 21:47:45
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5
Node: oggmiddle
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 31907
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/ggs/dirdef/baobao14.def
USERID ggs@dbking, PASSWORD ***
TABLE GIS_TEST.BAOBAO14;
Retrieving definition for GIS_TEST.BAOBAO14
Definitions generated for 1 table in /u01/app/oracle/ggs/dirdef/baobao14.def
生成def文件baobao14.def
oracle@oggmiddle dirdef]$ cat baobao14.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2013-07-22 21:47
*
* 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 GIS_TEST.BAOBAO14
Record length: 118
Syskey: 0
Columns: 4
PXID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2
FANG 64 10 56 0 0 1 0 10 10 0 0 0 0 0 1 0 0 0
TDD 64 20 72 0 0 1 0 20 20 0 0 0 0 0 1 0 0 0
AWR 64 15 98 0 0 1 0 15 15 0 0 0 0 0 1 0 0 0
End of definition
上面的内容是biaobao14的源端定义文件的内容.
以ASCII格式ftp到目标端，在目标端replicat定义.
抽取进程配置：
GGSCI (oggmiddle) 6> view params s_ex_mid
EXTRACT s_ex_mid
-- SETENV (NLS_LANG = CHINESE_CHINA.ZHS16GBK)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
USERID ggs@dbking, PASSWORD ggs
TRANLOGOPTIONS ARCHIVEDLOGONLY
-- TRANLOGOPTIONS ASMUSERsys@asm, ASMPASSWORD grid sysdba
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE dbking /arch/archivelog
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/cc
TABLE gis_test.baobao14, COLSEXCEPT (tdd,awr);
or
TABLE gis_test.baobao14;
GGSCI (oggmiddle) 6>add trandata gis_test.baobao14
投递进程配置：
GGSCI (oggmiddle) 7> view params pump_un
EXTRACT pump_un
USERID ggs@dbking, PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/pp
PASSTHRU
TABLE gis_test.baobao14;
复制进程配置：
GGSCI (primary) 5> view params rep_li
REPLICAT rep_li
USERID ggs, PASSWORD ggs
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
SOURCEDEFS /u01/app/oracle/ggs/dirdef/baobao14.def
MAP gis_test.baobao14, TARGET gis_test.baobao14;
or
MAP gis_test.baobao14, TARGET gis_test.baobao14,
colmap (
pxid=pxid, fang=fang)
;
重启OGG进程.
测试：
源端插入数据：
insert into gis_test.baobao14 values (1000,'a','b','c');
insert into gis_test.baobao14 values (2000,'b','c','e');
insert into gis_test.baobao14 values (3000,'c','d','f');
commit;
PXID FANG TDD AWR
1000 a b c
2000 b c e
3000 c d f
目标端查看：
select * from gis_test.baobao14;
PXID FANG
1000 a
2000 b
3000 c
成功复制PXID,FANG两列的数据，其他的列没有复制.