GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)

首页 » OGG » GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)

GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)

2014/07/24
OGG
648 views
GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)已关闭评论

GOLDENGATE FILTER row data with Operation-System environment variable
http://www.anbob.com/archives/2344.html

昨天有个朋友在网上问如何在用GOLDENGATE 一对多的同步模式下，简化配置，到达统一的OGG Replicat配置文件同时过滤数据的目的。好比总部更新数据，分销商只同步自己的数据，但是如果有400个分销商，为了简化安装不重复性的修改OGG replicat配置文件，所以OGG TARGET(分销商)使用相同的配置文件，但是在OGG 的配置文件中使用比较灵活的操作系统变量，也许是出于管理成本考虑。
使用典型的OGG 传输模式 Extract, Pump, Replicat , 理想的做法可能是在pump时做过滤, 这样 OGG Replicat无需过滤，但本案例是在replicat时，所以就有了上面的需求，先不讨论设计上的问题，先测试一下技术上能否实现？下面是我的完整的测试过程。

NOTE:配置OGG前期工作已省略，含配置目标端和源端的MGR 进程。请参照本站以前的笔记。

1, 在source and target DB 创建用户和表做为初始化。

sys>create user anbob identified by anbob1234;

sys>grant connect ,resource to anbob;

anbob>create table test(id int,name varchar2(10));

2，配置Extract 进程 on source db

GGSCI > edit params eanbobc

extract eanbobc

userid ggsmgr, password "AACAAAAAAAAAAAKAEJMJUCZILBNFVEYDOCBE*******", encryptkey default
exttrail dirdat/t1
table anbob.test;

GGSCI > dblogin userid ggsmgr, password ********

GGSCI> add trandata anbob.test

GGSCI> add extract eanbobc, tranlog, begin now

GGSCI> add exttrail dirdat/t1, extract eanbobc
EXTTRAIL added.

GGSCI> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     STOPPED     EANBOBC     00:00:00      00:00:51    

GGSCI> start eanbobc

Sending START request to MANAGER ...
EXTRACT EANBOBC starting

GGSCI> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EANBOBC     00:00:59      00:00:02

3, 配置Pump 进程 on source db

GGSCI> view params eicmed

GGSCI> view params eanbobd

extract eanbobd
passthru
rmthost dbserver57, mgrport 7809
rmttrail dirdat/t2
table anbob.*;

GGSCI (dbserver56) 6> add rmttrail dirdat/t2, extract eanbobd
RMTTRAIL added.

note:
如果目标端mgr进程已启动，此时可以启动pump进程,如start ext eanbobd

4, 配置replicat 进程 on target db

GGSCI> edit params ranbob

replicat ranbob
userid ggsmgr, password "AACAAAAAAAAAAAKAEJMJUCZILBNFV************", encryptkey default

AssumeTargetDefs
Map anbob.*, Target anbob.* ;

Note:
此处先不做过滤，测试通先

GGSCI> add replicat ranbob, exttrail dirdat/t2 checkpointtable ggsmgr.chkpt
REPLICAT added.

GGSCI > start ranbob

GGSCI > info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
REPLICAT    RUNNING     RANBOB      00:00:00      00:00:02

5, 开始测试

sys@Source>insert into anbob.test values(1,'anbob');
sys@Source>insert into anbob.test values(1,'weejar');

comit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                   1 anbob
                   1 weejar

a,修改Replicat 配置文件并重启replicat 进程，增加where 过虑数据

Map anbob.test, Target anbob.test where ( NAME = "anbob" );

sys@Source>insert into anbob.test values(2,'anbob');
sys@Source>insert into anbob.test values(3,'tom');

sys@Source>commit;

sys@Target>select * from anbob.test;

                  ID NAME
-------------------- ----------
                   1 anbob
                   1 weejar
                   2 anbob

b,修改Replicat 配置文件并重启replicat 进程，增加FILTER过虑数据

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );

sys@Source>insert into anbob.test values(4,'jack');
sys@Source>insert into anbob.test values(5,'anbob');
sys@Source>commit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob

c,修改Replicat 配置文件并重启replicat 进程，增加FILTER+ getenv过虑数据,
”
Operating system information type
(“OSVARIABLE”, “”) Returns the string value of a specified operatingsystem environment variable.
”

# export STORANAME=anbob

# env|grep STORANAME
STORANAME=anbob

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );

sys@Source>insert into anbob.test values(6,'anbob');
sys@Source>insert into anbob.test values(7,'weejar');
sys@Source>commit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob

Note:
全部被过滤了，显然是不正确的，于是又尝试修改了bash的profile配置文件，再次insert 还是未成功，测试过程省略.下面我测试一下@getenv(“OSVARIABLE”,”STORANAME”) 是否有取到变量

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );

sys@Source>insert into anbob.test values(11,'anbob');
sys@Source>commit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob

Note:
这里显示getenv并未取到操作系统的环境变量值, 通常我会尝试一下其它变量，下面我换一下其它变量看是否可以取到值？

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );
Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("GGENVIRONMENT","HOSTNAME") );

“Oracle GoldenGate information types
(“GGENVIRONMENT”, “
”) Returns Oracle GoldenGate environment information.

” 详细请查看OGG 官方文档 e29399

sys@Source>insert into anbob.test values(13,'anbob');
sys@Source>commit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob
                  13 dbserver57

Note:
显然GGENVIRONMENT是没有问题的，难道OSVARIABLE变量不好使，MOS,GOOGLE找不到用户信息，只能再接着尝试

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("GGENVIRONMENT","HOSTNAME") );
Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","SHELL") );

sys@Source>insert into anbob.test values(14,'anbob');
sys@Source>commit;

sys@Target>select * from anbob.test;

                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob 
                  13 dbserver57
                  14 /bin/bash

Note:
看到了希望，OSVARIABLE也是好使的，SHELL是系统变量，再使用个以前用户定义的系统变量试试

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("GGENVIRONMENT","HOSTNAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","SHELL") );
Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","ORACLE_BASE") );

sys@Source>insert into anbob.test values(15,'anbob');
sys@Source>commit;

sys@Target>select * from anbob.test;
                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob
                  13 dbserver57
                  14 /bin/bash
                  15 /oracle

Note:
用户定义的系统环境变量ORACLE_BASE也是可以取到的，而且getenv OSVARIABLE就相关于env|grep ，区别是getenv是等值，而不是包含，到这里就是找ORACLE_BASE和STORANAME这两个变量的区别，

ORACLE_BASE配置时间是GOLDENGATE启动之前，数据库启动之前的，更是本次OS启动之前，突然想到了MGR进程，这几次测试都是修改了配置文件，和环境变量后，只重启了Replicat进程，下面重启MGR进

程试试。

GGSCI > stop ranbob

GGSCI > stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI > start mgr

GGSCI > edit params ranbob

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("GGENVIRONMENT","HOSTNAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","SHELL") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","ORACLE_BASE") );

GGSCI > start ranbob

sys@Source>insert into anbob.test values(17,'weejar');
1 row created.

sys@Target>select * from anbob.test;

                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob
                  13 dbserver57
                  14 /bin/bash
                  15 /oracle
                  17 anbob

Note:
oops! I did it.

GGSCI > edit params ranbob

-- Map anbob.test, Target anbob.test where ( NAME = "anbob" );
-- Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, "anbob") = 0 );
Map anbob.test, Target anbob.test FILTER ( @STRCMP (NAME, @getenv("OSVARIABLE","STORANAME")) = 0 );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","STORANAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("GGENVIRONMENT","HOSTNAME") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","SHELL") );
-- Map anbob.test, Target anbob.test colmap( id=id, NAME= @getenv("OSVARIABLE","ORACLE_BASE") );

sys@Source>insert into anbob.test values(18,'anbob');
sys@Source>commit;

sys@Source>insert into anbob.test values(19,'weejar');
sys@Source>commit;

sys@Target>select * from anbob.test;

                  ID NAME
-------------------- ----------
                  11
                   5 anbob
                   1 anbob
                   1 weejar
                   2 anbob
                  13 dbserver57
                  14 /bin/bash
                  15 /oracle
                  17 anbob
                  18 anbob

Summary:
最终多次测试，实现了上面的需求。filter row data 在ogg中可以使用where和filter, getenv 可以取General information types、Table-level statistics information types、Oracle GoldenGate information typesDatabase information types、Operating system information type的信息，取OS变量时，读取的是MGR进程启动前的变量信息。

打赏

Goldengate

对不起，这篇文章暂时关闭评论。

上一篇： ORA-00600: internal error code [kkmupsViewDestFro_4] in oracle 11.2.0.1

下一篇： Some interesting about oracle, did you know? (一)

ANBOB™

GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)

Related Posts:

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号/Wechat