GOLDENGATE FILTER row data with Operation-System environment variable
GOLDENGATE FILTER row data with Operation-System environment variable(通过操作系统环境变量Filter数据)
昨天有个朋友在网上问如何在用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进程启动前的变量信息。