Use data define file and colmap to map different column

最新推荐文章于 2022-03-23 11:40:27 发布
最新推荐文章于 2022-03-23 11:40:27 发布
阅读量1.5k 收藏
点赞数 1
分类专栏： GoldenGate 文章标签： data file define colmap goldengate
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zbdba/article/details/44055991
版权

通常我们使用OGG同步一个表的时候，遇见两个表的列不一样，这时候我们就需要使用数据定义文件和colmap将行进行映射

下面我们来看看源表的结构：

SQL> desc test3;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
OWNER                                                 NOT NULL VARCHAR2(30)
TABLE_NAME                                            NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                                VARCHAR2(30)
CLUSTER_NAME                                                   VARCHAR2(30)
IOT_NAME                                                       VARCHAR2(30)
STATUS                                                         VARCHAR2(8)
PCT_FREE                                                       NUMBER
PCT_USED                                                       NUMBER
INI_TRANS                                                      NUMBER
MAX_TRANS                                                      NUMBER
INITIAL_EXTENT                                                 NUMBER
NEXT_EXTENT                                                    NUMBER
MIN_EXTENTS                                                    NUMBER
MAX_EXTENTS                                                    NUMBER
PCT_INCREASE                                                   NUMBER
FREELISTS                                                      NUMBER
FREELIST_GROUPS                                                NUMBER
LOGGING                                                        VARCHAR2(3)
BACKED_UP                                                      VARCHAR2(1)
NUM_ROWS                                                       NUMBER
BLOCKS                                                         NUMBER
EMPTY_BLOCKS                                                   NUMBER
AVG_SPACE                                                      NUMBER
CHAIN_CNT                                                      NUMBER
AVG_ROW_LEN                                                    NUMBER
AVG_SPACE_FREELIST_BLOCKS                                      NUMBER
NUM_FREELIST_BLOCKS                                            NUMBER
DEGREE                                                         VARCHAR2(20)
INSTANCES                                                      VARCHAR2(20)
CACHE                                                          VARCHAR2(10)
TABLE_LOCK                                                     VARCHAR2(8)
SAMPLE_SIZE                                                    NUMBER
LAST_ANALYZED                                                  DATE
PARTITIONED                                                    VARCHAR2(3)
IOT_TYPE                                                       VARCHAR2(12)
TEMPORARY                                                      VARCHAR2(1)
SECONDARY                                                      VARCHAR2(1)
NESTED                                                         VARCHAR2(3)
BUFFER_POOL                                                    VARCHAR2(7)
FLASH_CACHE                                                    VARCHAR2(7)
CELL_FLASH_CACHE                                               VARCHAR2(7)
ROW_MOVEMENT                                                   VARCHAR2(8)
GLOBAL_STATS                                                   VARCHAR2(3)
USER_STATS                                                     VARCHAR2(3)
DURATION                                                       VARCHAR2(15)
SKIP_CORRUPT                                                   VARCHAR2(8)
MONITORING                                                     VARCHAR2(3)
CLUSTER_OWNER                                                  VARCHAR2(30)
DEPENDENCIES                                                   VARCHAR2(8)
COMPRESSION                                                    VARCHAR2(8)
COMPRESS_FOR                                                   VARCHAR2(12)
DROPPED                                                        VARCHAR2(3)
READ_ONLY                                                      VARCHAR2(3)
SEGMENT_CREATED                                                VARCHAR2(3)
RESULT_CACHE                                                   VARCHAR2(7)


SQL> select count(1) from test3;

  COUNT(1)
----------
      2822


目标表的结构：

SQL> desc test3;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                    VARCHAR2(30)

SQL>
SQL>
SQL>
SQL> select count(1) from test3;

  COUNT(1)
----------
         0

SQL>

具体操作如下：
前期安装配置就不详细介绍了，请参考前面的文章 。
这里先使用ogg的initload 方法同步源表已有的数据：
Source：

add extract initload,sourceistable;

edit param initload

extract initload
userid ogg, password ogg
rmthost 192.168.56.61,mgrport 7839
rmttask replicat, group repload
table zbdba.test3;

创建数据定义文件： 

GGSCI (zbdba1) 7> edit param defgen

defsfile ./dirsql/convert.sql
userid ogg,password ogg
table zbdba.test3;

[ogg@zbdba1 ogg]$ ./defgen paramfile /ogg/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, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2015-03-02 15:59:20

***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Jun 13 18:24:36 EDT 2012, Release 2.6.32-279.el6.x86_64
Node: zbdba1
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 7481

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirsql/convert.sql
userid ogg,password ***
table zbdba.test3;
Retrieving definition for ZBDBA.TEST3

2015-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: 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 ZBDBA.TEST3
Record length: 1704
Syskey: 0
Columns: 55
OWNER                       64     30        0  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
TABLE_NAME                  64     30       36  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
TABLESPACE_NAME             64     30       72  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
CLUSTER_NAME                64     30      108  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
IOT_NAME                    64     30      144  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
STATUS                      64      8      180  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
PCT_FREE                    64     50      194  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
PCT_USED                    64     50      250  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
INI_TRANS                   64     50      306  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
MAX_TRANS                   64     50      362  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
INITIAL_EXTENT              64     50      418  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NEXT_EXTENT                 64     50      474  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
MIN_EXTENTS                 64     50      530  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
MAX_EXTENTS                 64     50      586  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
PCT_INCREASE                64     50      642  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
FREELISTS                   64     50      698  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
FREELIST_GROUPS             64     50      754  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
LOGGING                     64      3      810  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
BACKED_UP                   64      1      818  0  0 1 0      1      1      0 0 0 0 0 1    0 1 0
NUM_ROWS                    64     50      824  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
BLOCKS                      64     50      880  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
EMPTY_BLOCKS                64     50      936  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
AVG_SPACE                   64     50      992  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
CHAIN_CNT                   64     50     1048  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
AVG_ROW_LEN                 64     50     1104  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
AVG_SPACE_FREELIST_BLOCKS   64     50     1160  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NUM_FREELIST_BLOCKS         64     50     1216  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
DEGREE                      64     20     1272  0  0 1 0     20     20      0 0 0 0 0 1    0 1 0
INSTANCES                   64     20     1298  0  0 1 0     20     20      0 0 0 0 0 1    0 1 0
CACHE                       64     10     1324  0  0 1 0     10     10      0 0 0 0 0 1    0 1 0
TABLE_LOCK                  64      8     1340  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
SAMPLE_SIZE                 64     50     1354  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
LAST_ANALYZED              192     19     1410  0  0 1 0     19     19     19 0 5 0 0 1    0 1 0
PARTITIONED                 64      3     1432  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
IOT_TYPE                    64     12     1440  0  0 1 0     12     12      0 0 0 0 0 1    0 1 0
TEMPORARY                   64      1     1458  0  0 1 0      1      1      0 0 0 0 0 1    0 1 0
SECONDARY                   64      1     1464  0  0 1 0      1      1      0 0 0 0 0 1    0 1 0
NESTED                      64      3     1470  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
BUFFER_POOL                 64      7     1478  0  0 1 0      7      7      0 0 0 0 0 1    0 1 0
FLASH_CACHE                 64      7     1490  0  0 1 0      7      7      0 0 0 0 0 1    0 1 0
CELL_FLASH_CACHE            64      7     1502  0  0 1 0      7      7      0 0 0 0 0 1    0 1 0
ROW_MOVEMENT                64      8     1514  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
GLOBAL_STATS                64      3     1528  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
USER_STATS                  64      3     1536  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
DURATION                    64     15     1544  0  0 1 0     15     15      0 0 0 0 0 1    0 1 0
SKIP_CORRUPT                64      8     1564  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
MONITORING                  64      3     1578  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
CLUSTER_OWNER               64     30     1586  0  0 1 0     30     30      0 0 0 0 0 1    0 1 0
DEPENDENCIES                64      8     1622  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
COMPRESSION                 64      8     1636  0  0 1 0      8      8      0 0 0 0 0 1    0 1 0
COMPRESS_FOR                64     12     1650  0  0 1 0     12     12      0 0 0 0 0 1    0 1 0
DROPPED                     64      3     1668  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
READ_ONLY                   64      3     1676  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
SEGMENT_CREATED             64      3     1684  0  0 1 0      3      3      0 0 0 0 0 1    0 1 0
RESULT_CACHE                64      7     1692  0  0 1 0      7      7      0 0 0 0 0 1    0 1 0
End of definition

Target： 

GGSCI (zbdba2) 6> add replicat repload,specialrun;
REPLICAT added.


GGSCI (zbdba2) 7>

GGSCI (zbdba2) 7>

GGSCI (zbdba2) 7> edit param repload

REPLICAT repload
USERID ogg, PASSWORD ogg
SOURCEDEFS ./dirsql/convert.sql
MAP zbdba.test3, TARGET zbdba.test3,
colmap (
owner=owner, table_name=table_name,tablespace_name=tablespace_name)
;
开启抽取进程：

GGSCI (zbdba1) 6> start initload


Sending START request to MANAGER ...
EXTRACT INITLOAD starting

查看日志：

2015-03-02 16:11:12  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, initload.prm:  EXTRACT INITLOAD started.
2015-03-02 16:11:42  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, initload.prm:  EXTRACT INITLOAD stopped normally.

Target：
2015-03-03 02:44:33  INFO    OGG-00973  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started replicat task process (Port 7840).
2015-03-03 02:44:48  INFO    OGG-01229  Oracle GoldenGate Delivery for Oracle:  Connected to :37943.
2015-03-03 02:44:48  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, repload.prm:  REPLICAT REPLOAD starting.
2015-03-03 02:44:48  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, repload.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2015-03-03 02:44:48  INFO    OGG-01815  Oracle GoldenGate Delivery for Oracle, repload.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /ogg/dirtmp.
2015-03-03 02:44:48  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, repload.prm:  REPLICAT REPLOAD started.
2015-03-03 02:44:52  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, repload.prm:  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.
2015-03-03 02:44:57  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, repload.prm:  REPLICAT REPLOAD stopped normally.


已经同步完成：
查看目标库数据： 
SQL> select count(1) from test3;

  COUNT(1)
----------
      2822

SQL>

自此已完成初始化





zbdba
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle add key map,Oracle GoldenGate增加字段问题（下） replicat OGG-00918 Key column xx is missing from map...
weixin_33342041的博客
04-06 1207
queness. KEYCOLS may be used to define the key.2020-06-23 23:31:31 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, rep_zwa.prm: The definition for table ANBOB_COM.TEST_TABLE1 is obtained f...
基于OGG 实现Oracle到Kafka增量数据实时同步
袋鼠云的博客
11-13 2382
背景 在大数据时代，存在大量基于数据的业务。数据需要在不同的系统之间流动、整合。通常，核心业务系统的数据存在OLTP数据库系统中，其它业务系统需要获取OLTP系统中的数据。传统的数仓通过批量数据同步的方式，定期从OLTP系统中抽取数据。但是随着业务需求的升级，批量同步无论从实时性，还是对在线OLTP系统的抽取压力，都无法满足要求。需要实时从OLTP系统中获取数据变更，实时同步到下游业务系统。 ...
Android系统分区介绍
ljj342018214的博客
09-09 2333
Android系统分区介绍 hboot——系统开机引导类似电脑BIOS，这块刷错手机就会变成砖 radio——通讯模块、基带、WIFI、Bluetooth等衔接硬件的驱动软件 model分区 recovery——系统故障时负责恢复 boot——Linux嵌入式系统内核 system——系统文件、应用 cache——系统运行时产生的缓存 userdata——用户使用APP产生的缓存数据 几个关键分区： /boot 它由 kernel 和 ramdisk 构成，内核就这这里 /system 这个分区包含了整个
ORACLE GOLDENGATE报错问题汇总
ogg2kafka的博客
10-26 4493
OGG报错问题汇总 1.源端抽取进程ext1无法启动： OGG-00446：error 2 (No such file or directory) opening redo log +DATA_DG/cspt….dbf for sequence 12Not able to establish initial position for begin time 2017-08-24 15:07:04. Cause：源端数据库是asm自动存储管理 Solution：Select name,state from v$
gg oracle tjs 同步_配置Goldengate从Oracle到PostgreSQL的同步复制
weixin_29147045的博客
12-29 194
1、平台环境Oracle：rhel6.7+Oracle11.2.0.4 ip:192.168.56.2PostgreSQL：rhel7.2+Pg9.6.1 ip:192.168.56.25Goldengate：Goldengate12.2.0.1 for oracle和Goldengate 12.2.0.1 for PostgreSQL2、Ogg配置Oracle端：直接安装goldengate...
Ctrl- Go to define
12-25
VS2010上 按住Ctrl键+左键能转到定义的插件，十分方便！
Use TOGAF to define SOA.pdf
03-05
TOGAF SOA
How-to-Define-and-Use-Formatted-Search_SAPB1FormatSearch_
10-04
How to Define and Use Formatted Search in SAP business one
Big.Data.Using.SMART.Big.Data.Analytics.and.Metrics.To.Make.Better.Decisions
08-09
* Discusses how companies need to clearly define what it is they need to know * Outlines how companies can collect relevant data and measure the metrics that will help them answer their most ...
Docker for Data Science: Building Scalable and Extensible Data Infrastructure
07-29
Learn Docker "infrastructure as code" technology to define a system for performing standard but non-trivial data tasks on medium- to large-scale data sets, using Jupyter as the master controller. ...
#define 的使用
gdjason的博客
04-03 2761
前言 1、本人QQ – 330952038 欢迎一起学习交流。 2、#define 的用法，相信大家有用过，本文抱着学习的态度来重新理解#define(宏定义)正文 一、不带参数的宏定义 #define BUF_MAX_LEN 100 // 定义 BUF_MAX_LEN为 100，此时就可以把BUF_MAX_LEN当做100使用。 eg: unsigned cha
KunlunBase指导手册（四）之从Oracle实时同步数据
KunLunDB_Linda的博客
03-23 440
在OGG中，异构数据库之间的数据同步需要为源端创建定义文件。32x86_64HeapSizeFileSize\*7Nullneutral最后，将定义文件从oracle服务器/dirdef/KUNLUNTEST.def复制到KunlunBase服务器的./dirdef/KUNLUNTEST.def。
COLMAP学习使用笔记
Ocean_waver的博客
11-27 5014
基于Python脚本的database创建 可用于构建带有已知的相机内外参数的database，而不需要在gui界面手动修改参数，具体参考COLMAP官方文档。 由上述文档知，除了使用GUI界面，可以使用 COLMAP build 版本中的colmap\scripts\python\database.py手动创建database文件。 使用时注意，database.py文件中给出了example_usage()函数作为使用示范，可以自行从外部添加图像、相机特征点、匹配点、等信息。 使用方法为： pytho
mysql中use database_MySQL数据库之mysql5.7基础 切换到现有的一个数据库use database命令不用写分号...
weixin_42516007的博客
01-20 5596
本文主要向大家介绍了MySQL数据库之mysql5.7基础 切换到现有的一个数据库use database命令不用写分号 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。OperatingSystem：UbuntuKylin16.04LTS64bitmysql:Ver14.14Distrib5.7.17,forLinux(x86_64)usingEd...
Oracle GoldenGate for Oracle 11g to PostgreSQL 9.2.4 Configuration
Zhong Weicheng Study Notes
09-04 4715
Installing and setting up Oracle GoldenGate connecting to an Oracle database Also please make sure the Oracle database is in archive log mode [oracle@ggos ~]$ sqlplus / as sysdba SQL*Plus: Releas
【ogg遇错】无主键表OGG-01296
viviliving的专栏
10-26 2988
Opened trail file ./dirdat/si001435 at 2019-10-26 02:59:17 Wildcard MAP resolved (entry LZJBSI.*): MAP LZJBSI.BZ13, TARGET ZB_LZJBSI.BZ13,filter (@GETENV ("TRANSACTION", "CSN")>16737036956600);...
Oracle GoldenGate 将 SQLServer 数据实时同步到 ORACLE
热门推荐
12-01 1万+
Oracle GoldenGate  （简称 OGG）使在不同关系型数据库之间能进行实时同步复制可谓非常强大。 OGG 支持 oracle、sql server、mysql、db2、Sybase 等关系数据库直接的数据复制。OGG  这种灵活特性能够支持多种业务场景。
GoldenGate表异构的解决方法
06-27 633
首先必须创建一个数据定义文件，本例中，数据定义文件在源端生成1. 创建一个参数文件 edit param rep1.defs defsfile ./dirdef/rep1.defs userid ggs...
OGG-COLMAP-列映射
rgb_rgb的博客
12-07 1万+
OGG-COLMAP-列映射 COLMAP官方语法定义： COLMAP availability The COLMAP option is available with the following parameters: Syntax TABLE , TARGET , COLMAP ([USEDEFAULTS, ] = ); Or... MAP , TARGET , CO
How to use unordered map in c++
最新发布
03-30
2. Define an unordered_map variable with the desired key and value types. ```c++ std::unordered_map, int> myMap; ``` 3. Add elements to the unordered_map using the insert() function. ```c++ myMap....

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值