OGG之COLMAP实验讲解系列

xfxyxh 2019-05-09

展开全文

实验一. 表结构不一致情况下的使用COLMAP进行结构映射
环境准备：
实验环境简单化一些，本机模拟源-目标端，无需DATA PUMP进程

GGSCI (tianbaobao.ht8888.ht3 as ogg@dbsource) 30> view param ext2

extract ext2
SETENV (ORACLE_SID="dbsource")
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/le
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
TRANLOGOPTIONS DBLOGREADER
TABLE scott.*;

GGSCI (tianbaobao.ht8888.ht3 as ogg@dbsource) 31> view param rep2

REPLICAT rep2
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
REPERROR DEFAULT, ABEND
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep2.dsc, APPEND, MEGABYTES 100
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
MAP scott.*, TARGET tianbaobao.*;

GGSCI (tianbaobao.ht8888.ht3 as ogg@dbsource) 32> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2        00:00:00      00:00:04
REPLICAT    RUNNING     REP2        00:00:00      00:00:02

DEMO（一）：
Step1. 首先源和目标创建测试表：
SYS@SQL> create table scott.tb02(col1 int, col2 int, col3 int);

Table created.

SYS@SQL> create table tianbaobao.tb02(col1 int, col3 int, col2 int);

Table created.

SQL>
Step2. 参数不做任何调整后，源插入记录，并测试。
SCOTT@SQL> insert into scott.tb02 values(1,2,3);

1 row created.

SCOTT@SQL> commit;

Commit complete.

SCOTT@SQL>

TIANBAOBAO@SQL> select * from tb02;

COL1 COL3 COL2
---------- ---------- ----------
1 2 3

TIANBAOBAO@SQL>
这里发现已经MAP错误了，说明是按照列的顺序来的，而不是按照列名来对应的。

Step3. 尝试生成定义文件处理。

GGSCI (tianbaobao.ht8888.ht3 as ogg@dbsource) 33> edit param defgen

GGSCI (tianbaobao.ht8888.ht3) 34> view param defgen

DEFSFILE ./dirdef/test.def
USERID ogg, PASSWORD ogg
TABLE SCOTT.tb02;

GGSCI (tianbaobao.ht8888.ht3 as ogg@dbsource) 35> quit
[oracle@tianbaobao ogg]$ ./defgen paramfile dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
     Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
   Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 05:45:39

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

Starting at 2015-02-23 14:41:07
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: tianbaobao.ht8888.ht3
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: 6440

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/test.def
USERID ogg, PASSWORD ***
TABLE SCOTT.tb02;
Retrieving definition for SCOTT.TB02.

2015-02-23 14:41:10 WARNING OGG-06439 No unique key is defined for table TB02. 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 ./dirdef/test.def.

生成test.def文件，ftp传输到目标的dirdef文件目录下。

编辑REP2参数文件后，重启REP2进程。
GGSCI (tianbaobao.ht8888.ht3) 6> edit param rep2

GGSCI (tianbaobao.ht8888.ht3) 7> view param rep2

REPLICAT rep2
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
REPERROR DEFAULT, ABEND
--ASSUMETARGETDEFS
SOURCEDEFS ./dirdef/test.def
DISCARDFILE ./dirrpt/rep2.dsc, APPEND, MEGABYTES 100
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
MAP scott.*, TARGET tianbaobao.*;

GGSCI (tianbaobao.ht8888.ht3) 8> stop rep2

Sending STOP request to REPLICAT REP2 ...
Request processed.

GGSCI (tianbaobao.ht8888.ht3) 9> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

再次插入记录，并验证，结果一切OK。方法可行。
SCOTT@SQL> insert into scott.tb02 values(2,3,4);

1 row created.

SCOTT@SQL> commit;

Commit complete.

SCOTT@SQL>

TIANBAOBAO@SQL> select * from tb02;

      COL1 COL3     COL2
---------- ---------- ----------
1     2        3
2     4        3

TIANBAOBAO@SQL>

DEMO（二）：
当然也可以使用COLMAP字句关联。
Step1. 编辑rep2参数文件，不适用定义文件。
GGSCI (tianbaobao.ht8888.ht3) 153> view param rep2

GGSCI (tianbaobao.ht8888.ht3) 154> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2        00:00:00      00:00:06
REPLICAT    RUNNING     REP2        00:00:00      00:00:04

Step2. 测试1：
SCOTT@SQL> insert into scott.tb02 values(3,4,5);

1 row created.

SCOTT@SQL> commit;

Commit complete.

SCOTT@SQL>

TIANBAOBAO@SQL> select * from tb02;

      COL1 COL3     COL2
---------- ---------- ----------
1     2        3
2     4        3
3     5        4

TIANBAOBAO@SQL>

Step3. 测试2：
SCOTT@SQL> insert into tb02(col1,col2,col3) values(5,6,7);

1 row created.

SCOTT@SQL> commit;

Commit complete.

SCOTT@SQL>

TIANBAOBAO@SQL> select * from tb02;

      COL1 COL3     COL2
---------- ---------- ----------
1     2        3
2     3        4
3     5        4
5     7        6

TIANBAOBAO@SQL>

结论：colmap正确的映射或者配置定义文件（此时不需要colmap都可以实现，但是很多时候如果结构不一致时，还是需要的定义文件的)。
实际生产上，如果要配置colmap，一定要注意进行测试，确保正确的结果再上线。

实验二. 模拟SQLExec测试，数据转换
数据转换 – 使用SQLEXEC
SQLEXEC：在OGG中调用SQL查询语句或者存储过程

需求：需要实现源端数据库中主从两张表到目标库的筛选复制
具体描述：源库中有主从关系的两张表，其中主表中有一个筛选字段，而子表中没有筛选字段，主表和子表都需要满足主表的筛选条件才能向目标数据库中复制。
表结构（源库和目标库中主从表定义一致）：
主表：create table scott.province(PROVINCE_ID number primary key,PROVINCE_NAME varchar(10));
主表字段说明：PROVINCE_ID 省份编号；PROVINCE_NAME 省份名称
子表：create table scott.city(CITY_ID number primary key,CITY_NAME varchar2(10),PROVINCE_ID number references scott.province(PROVINCE_ID));
子表字段说明：CITY_ID 城市编号；CITY_NAME 城市名称
-- PROVINCE_ID 城市所在省的编号，引用自主表对应列
复制筛选说明：主表中PROVINCE_NAME是筛选字段，比如：只有省份名称是“江苏”的才复制到目标表中；子表没有筛选字段，但是同样要符合主表的筛选要求，比如：只有在“江苏”省内

的城市才可以复制到目标表。这样的复制要求可能会用在基于筛选条件的一对多的数据分发的复制环境中。

分析：
（1）主表是有筛选字段的，所以直接调用Goldengate内置的STREQ函数配合FILTER就可以实现筛选了；
（2）子表没有PROVINCE_NAME这个字段，所以只有通过PROVINCE_ID来回查出该条记录在主表中对应的PROVINCE_NAME值，然后使用STREQ函数配合FILTER子句来进行过滤。这里就需要在

goldengate中调用自己写的SQL语句或存储过程来实现，即SQLEXEC来实现。
（3）SQLEXEC可以在extract进程（包括pump进程）和replicat进程都可以使用，在本例中因为在源端就要进行筛选，需要配置extract进程参数，同时不影响源端日志抽取的效率，所以决定

配置源端的pump进程来实现。

DEMO--SQLExec数据转换
Step1. 源-目标创建测试表
SCOTT@SQL> create table province(PROVINCE_ID number primary key,PROVINCE_NAME varchar(10));

Table created.

SCOTT@SQL> create table city(CITY_ID number primary key,CITY_NAME varchar2(10),PROVINCE_ID number references province(PROVINCE_ID));

Table created.

TIANBAOBAO@SQL> create table province(PROVINCE_ID number primary key,PROVINCE_NAME varchar(10));

Table created.

TIANBAOBAO@SQL> create table city(CITY_ID number primary key,CITY_NAME varchar2(10),PROVINCE_ID number references province(PROVINCE_ID));

Table created.

TIANBAOBAO@SQL>

Step2. 配置进程参数,并启动
GGSCI (tianbaobao.ht8888.ht3) 68> view param ext2

GGSCI (tianbaobao.ht8888.ht3) 69> view param pmp2

extract pmp2
SETENV (ORACLE_SID="dbsource")
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
--PASSTHRU
RMTHOST 192.168.0.77, MGRPORT 7809
EXTTRAIL ./dirdat/el
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
TRANLOGOPTIONS DBLOGREADER
TABLE scott.province, FILTER (@STREQ (PROVINCE_NAME, "LIAONING"));
TABLE scott.city, &
SQLEXEC ( ID lookup1 , &
QUERY "select a.PROVINCE_NAME from scott.province a,scott.city b where a.PROVINCE_ID=b.PROVINCE_ID and b.CITY_ID =:param1", &
PARAMS ( param1 = CITY_ID ), BEFOREFILTER), &
filter (@STREQ (@GETVAL (lookup1.PROVINCE_NAME), "LIAONING") );

GGSCI (tianbaobao.ht8888.ht3) 2> view param rep2

GGSCI (tianbaobao.ht8888.ht3) 10> start ext2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (tianbaobao.ht8888.ht3) 10> start pmp2

Sending START request to MANAGER ...
EXTRACT PMP2 starting

GGSCI (tianbaobao.ht8888.ht3) 11> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (tianbaobao.ht8888.ht3) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2        00:00:00      00:00:01
REPLICAT    RUNNING     REP2        00:00:00      00:00:08
REPLICAT    RUNNING     PMP2        00:00:00      00:00:01

说明：
（1）因为在pump进程调用了FILTER字句，所以注释掉PASSTHRU；
（2）因为在pump进程中使用了SQLEXEC来回库查询了，所以在pump进程中需要配置数据库的用户名和密码；
（3）SQLEXEC中调用了sql语句来进行回库查询，在SQLEXEC有四个部分的参数：
?ID : 为后续的查询定义一个逻辑名称，使用这个逻辑名称来获取sql查询语句返回的列值，比如：后续sql查询的是PROVINCE_NAME，我们就在FILTER语句中调用

lookup1.PROVINCE_NAME来获取返回的值；
?QUERY 部分：就是需要调用的sql查询语句，这里根据具体要求来写；
?PARAMS部分：前面sql查询部分的输入参数，如果没有传入参数，必须写成NOPARAMS；
?BEFOREFILTER部分：是本例中用到的值，在第四部分的参数是可选参数，可以配置的值有很多，比如：BEFOREFILTER、AFTERFILTER、ALLPARAMS、DBOP、EXEC…等等，我们这里配置

BEFOREFILTER是为了在确保在FILTER之前执行sql查询语句，这样查询的结果才可以在FILTER中使用。

在goldengate官方手册中，SQLEXEC调用sql查询语句的语法：
SQLEXEC (
ID
, QUERY “”
{, PARAMS | NOPARAMS}
[,

] [, ...]
)
正好对应于我们这个例子中的四个部分，其中option部分是可选项。

Step3. 测试数据转换
源端执行了以下sql：
SCOTT@SQL> insert into scott.province values(1,'LIAONING');
SCOTT@SQL> insert into scott.province values(2,'ZHEJIANG');
SCOTT@SQL> commit;
SCOTT@SQL> insert into scott.city values(1,'HANGZHOU',2);
SCOTT@SQL> insert into scott.city values(2,'DALIAN',1);
SCOTT@SQL> insert into scott.city values(3,'SHENYANG',1);
SCOTT@SQL> insert into scott.city values(4,'BENXI',1);
SCOTT@SQL> commit;

源端表检查：
SCOTT@SQL> select * from province;

PROVINCE_ID PROVINCE_N
----------- ----------
1 LIAONING
2 ZHEJIANG

SCOTT@SQL> select * from city;

   CITY_ID CITY_NAME PROVINCE_ID
---------- ---------- -----------
2 DALIAN  1
3 SHENYANG  1
1 HANGZHOU  2
4 BENXI  1

SCOTT@SQL>

目标表检查：
TIANBAOBAO@SQL> select * from province;

PROVINCE_ID PROVINCE_N
----------- ----------
1 LIAONING

TIANBAOBAO@SQL> select * from city;

   CITY_ID CITY_NAME PROVINCE_ID
---------- ---------- -----------
2 DALIAN  1
3 SHENYANG  1
4 BENXI  1

这样目标DB上就只有和辽宁省相关的记录了。

3、模拟审计测试
数据转换 – 使用@GETENV和@TOKEN
需求：在目标数据库记录源端的数据操作记录，实现审计功能
具体描述：在数据复制过程中，目标端上需要实现两个需求
（1）目标表和源表相比多一个列，这个列用来表示源表记录更新的时间戳；
（2）目标上单独有一张审计表，用于表上的操作信息的审计，比如：源端提交时间、目标端提交时间、操作类型、操作用户等

DEMO---审计测试
Step1. 前期准备：
源表：
SYS@SQL> create table scott.TB11(id number primary key,name varchar(10));

Table created.

SYS@SQL>

目标表：
SYS@SQL> create table tianbaobao.TB11(id number primary key,name varchar(10), src_time date);

Table created.

SYS@SQL> create table tianbaobao.TB11_audit(id number,name varchar(10),src_time date, trg_time date,op_type varchar2(20),src_user varchar2(10));

Table created.

SYS@SQL>

GGSCI (tianbaobao.ht8888.ht3) 1> view param ext2

说明：在源端extract进程中使用TOKEN在trail文件头部用户TOKEN部分定义了一个变量：TKN-USERNAME，这个变量的值是通过@GETENV来获得当前Goldengate运行环境中和数据库事务相关源端数据库提交事务的用户信息

GGSCI (tianbaobao.ht8888.ht3) 2> view param rep2

说明：此时目标有2个replicat进程，这两个进程读的trail文件是同一份，这里是第一个进程：ggs_rep1，这个进程负责正常的数据复制，即从源端的TB11表复制到目标的TB11表，只不过在

目标的TB11表上多了一个字段SRC_TIME（记录源端事务提交的时间），因为多了一个字段，这里就要调用COLMAP来做映射了，在COLMAP字句中使用@GETENV函数，该函数配置的参数是GGHEADER

类别下的COMMITTIMESTAMP这个属性，而这个属性就是用来获取时间戳，即记录事务提交的时间，格式为YYY-MM-DD HH:MI:SS.FFFFFF。

GGSCI (tianbaobao.ht8888.ht3) 3> view param rep3

REPLICAT rep3
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
REPERROR DEFAULT, ABEND
ASSUMETARGETDEFS
INSERTALLRECORDS
DISCARDFILE ./dirrpt/rep3.dsc, APPEND, MEGABYTES 100
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
MAP SCOTT.TB11, TARGET tianbaobao.TB11_AUDIT,
COLMAP (USEDEFAULTS,
SRC_TIME = @GETENV("GGHEADER","COMMITTIMESTAMP"),
TRG_TIME = @DATENOW (),
OP_TYPE = @GETENV("GGHEADER", "OPTYPE"),
SRC_USER = @TOKEN ("TKN-USERNAME"));

说明：这是目标第二个replicat进程，这个进程负责审计表的复制，即从源端的TB11表复制到目标审计表TB11_AUDIT，前面也看到需求中的内容了，审计表多了4个字段，分别用于记录源端每

个事务操作的时间、目标端提交的时间、源端的操作类型、源端数据库用户名。该进程参数文件中使用了一个非常重要的参数：INSERTALLRECORDS ，这个参数会让复制进程插入对于目标端一

条记录所有的变化修改，即能起到审计的作用。同时在这个进程中，使用@GETENV函数来获取goldengate运行环境中的GGHEADER下的COMMITTIMESTAMP（源端时间）和OPTYPE（操作类型）；使

用@DATENOW ()来获取目标端提交时间；使用@TOKEN函数来获取源端定义在trail文件头部定义的用户令牌部分的变量TKN-USERNAME的值，即源端的数据库用户信息。

GGSCI (tianbaobao.ht8888.ht3) 4> start ext2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (tianbaobao.ht8888.ht3) 5> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (tianbaobao.ht8888.ht3) 6> start rep3

Sending START request to MANAGER ...
REPLICAT REP3 starting

GGSCI (tianbaobao.ht8888.ht3) 7> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT2        00:01:55      00:00:07
REPLICAT    RUNNING     REP2        00:00:00      00:00:05
REPLICAT    RUNNING     REP3        00:00:00      00:00:02

用@DATENOW ()来获取目标端提交时间；使用@TOKEN函数来获取源端定义在trail文件头部定义的用户令牌部分的变量TKN-USERNAME的值，即源端的数据库用户信息。

Step2. 测试审计：
源端使用SCOTT用户执行了以下sql：
SCOTT@SQL> insert into scott.TB11 values(1,'a');
SCOTT@SQL> commit;
SCOTT@SQL> insert into scott.TB11 values(2,'a');
SCOTT@SQL> commit;
SCOTT@SQL> update scott.TB11 set name='c' where id=2;
SCOTT@SQL> commit;
SCOTT@SQL> delete from scott.TB11 where id=2;
SCOTT@SQL> commit;
SCOTT@SQL> update scott.TB11 set id=2 where id=1;
SCOTT@SQL> commit;
源端使用SYS用户执行了以下sql：
SYS@SQL> insert into scott.TB11 values(3,'c');
SYS@SQL> commit;

目标端检查
TIANBAOBAO@SQL> select * from tb11;

ID NAME       SRC_TIME
---------- ---------- -------------------
2 a       2015-02-23 18:50:26
3 c       2015-02-23 18:57:26

TIANBAOBAO@SQL> select * from tb11_audit;

ID NAME       SRC_TIME    TRG_TIME       OP_TYPE     SRC_USER
---------- ---------- ------------------- ------------------- -------------------- ----------
1 a       2015-02-23 18:33:50 2015-02-23 18:34:45 INSERT     SCOTT
2 a       2015-02-23 18:42:06 2015-02-23 18:42:10 INSERT     SCOTT
2 c       2015-02-23 18:47:11 2015-02-23 18:49:41 SQL COMPUPDATE    SCOTT
2       2015-02-23 18:50:21 2015-02-23 18:50:25 DELETE     SCOTT
2       2015-02-23 18:50:26 2015-02-23 18:50:29 PK UPDATE     SCOTT
3 c       2015-02-23 18:57:26 2015-02-23 18:57:28 INSERT     SYS