演示OGG 使用COLMAP进行列的映射功能
[@more@]COLMAP--列映射
源端表结构如下：
SQL> desc gis_test.baobao14;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
PXID NUMBER
FANG VARCHAR2(10) Y
TDD VARCHAR2(20) Y
AWR VARCHAR2(15) Y
TNAME VARCHAR2(20) Y
目标端表结构如下：
SQL> desc gis_test.baobao14;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
PXID NUMBER
FANG VARCHAR2(10) Y
DNAME VARCHAR2(20) Y
表的结构定义不相同，golendgate如何同步数据？
1.源端生成该表的定义文件
查看本博客的golendgate (32) --defgen
2.目标端使用COLMAP方式进行映射。
COLMAP官方语法定义：
COLMAP availability
The COLMAP option is available with the following parameters:
Syntax TABLE COLMAP ([USEDEFAULTS, ] Or... MAP COLMAP ([USEDEFAULTS, ] 我这里在目标端使用COLMAP 解释一下： USEDEFAULTS：源端的列名与目标端的列名相同，使用USEDEFAULTS target column:目标列 source expression：源端表达式或列 COLMAP官方使用定义： When using COLMAP for source and target tables that are not identical in structure, you must: ● generate data definitions for the source tables, the target tables, or both, depending on the Oracle GoldenGate configuration and the databases that are being used. ● transfer the definitions file to the system where they will be used. ● use the SOURCEDEFS parameter to identify the definitions file for Replicat on a target system or use the TARGETDEFS parameter to identify the definitions file for Extract or a data pump on a source system or intermediary system. 当使用COLMAP为了源端与目标端的表的结构不相同的时候，必须使用defgen生成定义文件. When using COLMAP for source and target tables that are identical in structure, and you are only using COLMAP for other functions such as conversion, a source definitions file is not needed. When a definitions file is not being used, you must use the ASSUMETARGETDEFS parameter instead. See the Oracle GoldenGate Windows and UNIX Reference Guide 当使用COLMAP为了源端与目标端的表的结构相同的时候，仅使用COLMAP进行其他的功能，例如转换，不需要定义文件，但是必须使用ASSUMETARGETDEFS参数。 Using table-level column mapping Use the COLMAP option of the MAP and TABLE parameters to: ● explicitly map source columns to target columns that have different names. ● specify default column mapping when an explicit column mapping is not needed. COLMAP只能是完成表级别的映射功能。 （COLMATCH能完成全局映射，有兴趣可以了解一下。） 抽取进程配置： 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); -- TABLE gis_test.baobao14; 投递进程配置： 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; 复制进程配置： 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.test1, TARGET gis_test.test1 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 9116587); MAP gis_test.baobao14, TARGET gis_test.baobao14, colmap (USEDEFAULTS, dname = tname); -- MAP gis_test.baobao14, TARGET gis_test.baobao14, -- colmap ( -- pxid=pxid, fang=fang) -- ; 源端查看： select * from gis_test.baobao14; PXID FANG TDD AWR TNAME 1 11000 nn aa hh 2 12000 bb cc hh 3 13000 bb cc hh 4 14000 bb cc hh 5 15000 bb cc hh xiangyang 6 16000 bb cc hh xiangyang 7 17000 bb cc hh xiangyang 8 1000 a b c 9 2000 b c e 10 3000 c d f 11 4000 d e g 12 5000 e e g 13 6000 g e g 14 7000 f e g 15 8000 kk yy mm 16 9000 xx jj hh 17 10000 uu jj hh 目标端查看： select * from gis_test.baobao14; PXID FANG DNAME 4 1000 a 5 2000 b 15 12000 bb 16 15000 bb xiangyang 17 16000 bb xiangyang 3 17000 bb xiangyang 1 13000 bb 2 14000 bb 6 3000 c 7 4000 d 8 5000 e 10 7000 f 9 6000 g 11 8000 kk 14 11000 nn 13 10000 uu 12 9000 xx , TARGET
,
, TARGET
,