通常我们使用OGG同步一个表的时候，遇见两个表的列不一样，这时候我们就需要使用数据定义文件和colmap将行进行映射
下面我们来看看源表的结构：
SQL> desc test3;NameNull? Type—————————————————– ——– ————————————OWNERNOT NULL VARCHAR2(30)TABLE_NAMENOT NULL VARCHAR2(30)TABLESPACE_NAMEVARCHAR2(30)CLUSTER_NAMEVARCHAR2(30)IOT_NAMEVARCHAR2(30)STATUSVARCHAR2(8)PCT_FREENUMBERPCT_USEDNUMBERINI_TRANSNUMBERMAX_TRANSNUMBERINITIAL_EXTENTNUMBERNEXT_EXTENTNUMBERMIN_EXTENTSNUMBERMAX_EXTENTSNUMBERPCT_INCREASENUMBERFREELISTSNUMBERFREELIST_GROUPSNUMBERLOGGINGVARCHAR2(3)BACKED_UPVARCHAR2(1)NUM_ROWSNUMBERBLOCKSNUMBEREMPTY_BLOCKSNUMBERAVG_SPACENUMBERCHAIN_CNTNUMBERAVG_ROW_LENNUMBERAVG_SPACE_FREELIST_BLOCKSNUMBERNUM_FREELIST_BLOCKSNUMBERDEGREEVARCHAR2(20)INSTANCESVARCHAR2(20)CACHEVARCHAR2(10)TABLE_LOCKVARCHAR2(8)SAMPLE_SIZENUMBERLAST_ANALYZEDDATEPARTITIONEDVARCHAR2(3)IOT_TYPEVARCHAR2(12)TEMPORARYVARCHAR2(1)SECONDARYVARCHAR2(1)NESTEDVARCHAR2(3)BUFFER_POOLVARCHAR2(7)FLASH_CACHEVARCHAR2(7)CELL_FLASH_CACHEVARCHAR2(7)ROW_MOVEMENTVARCHAR2(8)GLOBAL_STATSVARCHAR2(3)USER_STATSVARCHAR2(3)DURATIONVARCHAR2(15)SKIP_CORRUPTVARCHAR2(8)MONITORINGVARCHAR2(3)CLUSTER_OWNERVARCHAR2(30)DEPENDENCIESVARCHAR2(8)COMPRESSIONVARCHAR2(8)COMPRESS_FORVARCHAR2(12)DROPPEDVARCHAR2(3)READ_ONLYVARCHAR2(3)SEGMENT_CREATEDVARCHAR2(3)RESULT_CACHEVARCHAR2(7)SQL> select count(1) from test3; COUNT(1)———-2822目标表的结构：SQL> desc test3;NameNull? Type—————————————– ——– —————————-OWNERNOT NULL VARCHAR2(30)TABLE_NAMENOT NULL VARCHAR2(30)TABLESPACE_NAMEVARCHAR2(30)SQL>SQL>SQL>SQL> select count(1) from test3; COUNT(1)———-0SQL>
具体操作如下：前期安装配置就不详细介绍了，，请参考前面的文章 。这里先使用ogg的initload 方法同步源表已有的数据：Source：
add extract initload,sourceistable;edit param initloadextract initloaduserid ogg, password oggrmthost 192.168.56.61,mgrport 7839rmttask replicat, group reploadtable zbdba.test3;创建数据定义文件：GGSCI (zbdba1) 7> edit param defgendefsfile ./dirsql/convert.sqluserid ogg,password oggtable zbdba.test3;[ogg@zbdba1 ogg]$ ./defgen paramfile /ogg/dirprm/defgen.prm***********************************************************************Oracle GoldenGate Table Definition Generator for OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.Starting at 2015-03-02 15:59:20***********************************************************************Operating System Version:LinuxVersion #1 SMP Wed Jun 13 18:24:36 EDT 2012, Release 2.6.32-279.el6.x86_64Node: zbdba1Machine: x86_64soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size: unlimited unlimitedFile Size: unlimited unlimitedCPU Time: unlimited unlimitedProcess id: 7481*************************************************************************Running with the following parameters*************************************************************************defsfile ./dirsql/convert.sqluserid ogg,password ***table zbdba.test3;Retrieving definition for ZBDBA.TEST32015-03-02 15:59:24 WARNING OGG-00869 No unique key is defined for table 'TEST3'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Definitions generated for 1 table in ./dirsql/convert.sql[ogg@zbdba1 dirsql]$ cat convert.sql*+- Defgen version 2.0, Encoding UTF-8** Definitions created/modified 2015-03-02 15:59** 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: ORACLECharacter set ID: windows-936National character set ID: UTF-16Locale: neutralCase sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14*Definition for table ZBDBA.TEST3Record length: 1704Syskey: 0Columns: 55OWNER64300 0 0 1 030300 0 0 0 0 1 0 1 0TABLE_NAME643036 0 0 1 030300 0 0 0 0 1 0 1 0TABLESPACE_NAME643072 0 0 1 030300 0 0 0 0 1 0 1 0CLUSTER_NAME6430108 0 0 1 030300 0 0 0 0 1 0 1 0IOT_NAME6430144 0 0 1 030300 0 0 0 0 1 0 1 0STATUS648180 0 0 1 0880 0 0 0 0 1 0 1 0PCT_FREE6450194 0 0 1 0505050 0 0 0 0 1 0 1 2PCT_USED6450250 0 0 1 0505050 0 0 0 0 1 0 1 2INI_TRANS6450306 0 0 1 0505050 0 0 0 0 1 0 1 2MAX_TRANS6450362 0 0 1 0505050 0 0 0 0 1 0 1 2INITIAL_EXTENT6450418 0 0 1 0505050 0 0 0 0 1 0 1 2NEXT_EXTENT6450474 0 0 1 0505050 0 0 0 0 1 0 1 2MIN_EXTENTS6450530 0 0 1 0505050 0 0 0 0 1 0 1 2MAX_EXTENTS6450586 0 0 1 0505050 0 0 0 0 1 0 1 2PCT_INCREASE6450642 0 0 1 0505050 0 0 0 0 1 0 1 2FREELISTS6450698 0 0 1 0505050 0 0 0 0 1 0 1 2FREELIST_GROUPS6450754 0 0 1 0505050 0 0 0 0 1 0 1 2LOGGING643810 0 0 1 0330 0 0 0 0 1 0 1 0BACKED_UP641818 0 0 1 0110 0 0 0 0 1 0 1 0NUM_ROWS6450824 0 0 1 0505050 0 0 0 0 1 0 1 2BLOCKS6450880 0 0 1 0505050 0 0 0 0 1 0 1 2EMPTY_BLOCKS6450936 0 0 1 0505050 0 0 0 0 1 0 1 2AVG_SPACE6450992 0 0 1 0505050 0 0 0 0 1 0 1 2CHAIN_CNT64501048 0 0 1 0505050 0 0 0 0 1 0 1 2AVG_ROW_LEN64501104 0 0 1 0505050 0 0 0 0 1 0 1 2AVG_SPACE_FREELIST_BLOCKS 64501160 0 0 1 0505050 0 0 0 0 1 0 1 2NUM_FREELIST_BLOCKS64501216 0 0 1 0505050 0 0 0 0 1 0 1 2DEGREE64201272 0 0 1 020200 0 0 0 0 1 0 1 0INSTANCES64201298 0 0 1 020200 0 0 0 0 1 0 1 0CACHE64101324 0 0 1 010100 0 0 0 0 1 0 1 0TABLE_LOCK6481340 0 0 1 0880 0 0 0 0 1 0 1 0SAMPLE_SIZE64501354 0 0 1 0505050 0 0 0 0 1 0 1 2LAST_ANALYZED192191410 0 0 1 0191919 0 5 0 0 1 0 1 0PARTITIONED6431432 0 0 1 0330 0 0 0 0 1 0 1 0IOT_TYPE64121440 0 0 1 012120 0 0 0 0 1 0 1 0TEMPORARY6411458 0 0 1 0110 0 0 0 0 1 0 1 0SECONDARY6411464 0 0 1 0110 0 0 0 0 1 0 1 0NESTED6431470 0 0 1 0330 0 0 0 0 1 0 1 0BUFFER_POOL6471478 0 0 1 0770 0 0 0 0 1 0 1 0FLASH_CACHE6471490 0 0 1 0770 0 0 0 0 1 0 1 0CELL_FLASH_CACHE6471502 0 0 1 0770 0 0 0 0 1 0 1 0ROW_MOVEMENT6481514 0 0 1 0880 0 0 0 0 1 0 1 0GLOBAL_STATS6431528 0 0 1 0330 0 0 0 0 1 0 1 0USER_STATS6431536 0 0 1 0330 0 0 0 0 1 0 1 0DURATION64151544 0 0 1 015150 0 0 0 0 1 0 1 0SKIP_CORRUPT6481564 0 0 1 0880 0 0 0 0 1 0 1 0MONITORING6431578 0 0 1 0330 0 0 0 0 1 0 1 0CLUSTER_OWNER64301586 0 0 1 030300 0 0 0 0 1 0 1 0DEPENDENCIES6481622 0 0 1 0880 0 0 0 0 1 0 1 0COMPRESSION6481636 0 0 1 0880 0 0 0 0 1 0 1 0COMPRESS_FOR64121650 0 0 1 012120 0 0 0 0 1 0 1 0DROPPED6431668 0 0 1 0330 0 0 0 0 1 0 1 0READ_ONLY6431676 0 0 1 0330 0 0 0 0 1 0 1 0SEGMENT_CREATED6431684 0 0 1 0330 0 0 0 0 1 0 1 0RESULT_CACHE6471692 0 0 1 0770 0 0 0 0 1 0 1 0End of definitionTarget：GGSCI (zbdba2) 6> add replicat repload,specialrun;REPLICAT added.GGSCI (zbdba2) 7>GGSCI (zbdba2) 7>GGSCI (zbdba2) 7> edit param reploadREPLICAT reploadUSERID ogg, PASSWORD oggSOURCEDEFS ./dirsql/convert.sqlMAP zbdba.test3, TARGET zbdba.test3,colmap (owner=owner, table_name=table_name,tablespace_name=tablespace_name);开启抽取进程：
GGSCI (zbdba1) 6> start initloadSending START request to MANAGER …EXTRACT INITLOAD starting
世上并没有用来鼓励工作努力的赏赐，
