简单说明:
本篇博文实验使用OGG的FILTER子句实现筛选数据复制的功能
依据《11. Setting up a simple GoldenGate replication confguration between two single node databases》
部署试验环境模拟本次实验,将该博文实验步骤全部执行完毕
实验步骤:
1° 目标库依据EMP表结构创建表 EMP_FILTER
su - oracle
cd /ggs
echo 'STOP REPLICAT RGGTEST1'|./ggsci
echo 'CREATE TABLE EMP_FILTER AS SELECT * FROM EMP;'|sqlplus scott/tiger
2° 源端抽取和发送进程配置保持不变,进程保持启动状态
3° 目标端改写应用进程配置文件,重启应用进程:
su - oracle
cd /ggs/dirprm
cat>RGGTEST1.prm<<EOF
REPLICAT RGGTEST1
USERID ggs, PASSWORD ggs
DISCARDFILE /ggs/dirrpt/RGGTEST1.dsc, append, MEGABYTES 500
ASSUMETARGETDEFS
MAP SCOTT.EMP, TARGET SCOTT.EMP;
MAP SCOTT.EMP, TARGET SCOTT.EMP_FILTER, FILTER (EMPNO > 7934);
EOF
cd /ggs
echo 'START REPLICAT RGGTEST1'|./ggsci
echo 'STATUS REPLICAT RGGTEST1'|./ggsci
4° 测试验证:
# 源端操作
su - oracle
sqlplus scott/tiger
insert into emp values(9999,'vincent','vincent',7788,sysdate,100,100,10);
insert into emp values(7000,'vincent','vincent',7788,sysdate,100,100,10);
commit;
exit
# 目标端验证
sqlplus scott/tiger
select * from emp_filter;
exit
# 表emp_filter中没有EMPNO<=7934的记录,实验成功
实验总结:
一些使用FILTER参数进行过滤的例子:
FILTER (@COMPUTE (SAL + COMM) > 1000);
FILTER (@STREQ ("ENAME", 'SMITH') > 0);
-- 匹配ENAME列值为SMITH的记录
FILTER (COMM <> @NULL);
FILTER ((COMM <> @NULL) AND (SAL > 10000));
FILTER (ON INSERT, SAL > 10000);
-- 只复制SAL > 10000的所有INSERT语句
FILTER (IGNORE INSERT, SAL > 10000);
-- 忽略所有SAL > 10000的INSERT语句
Using the Where clause
使用WHERE子句和使用FILTER的方法一样,但是WHERE子句不能筛选SQL类型:
WHERE (SAL > 10000);
WHERE (SAL = @PRESENT, SAL > 10000);
Filtering in the source environment
除了应用进程外,也可以在抽取或发送进程中过滤数据
在源端过滤数据会减少网络负载:
TABLE scott.emp, FILTER (SAL > 1000);
Filtering the records into parallel groups
可以使用FILTER子句做负载的切分,详细可参见
《12. Setting up a GoldenGate replication with multiple process groups》
[TOC]