goldengate（8）---goldengate对物化视图的支持

1.goldengate支持物化视图的刷新机制

Limitations of support for materialized views
● Materialized views created WITH ROWID are not supported.
● The materialized view log can be created WITH ROWID.
● The source table must have a primary key
● Truncates of materialized views are not supported. You can use a DELETE FROMstatement.
● Some GoldenGate initial load methods do not support LOBs in a materialized view.
● For Replicat, the materialized view must be updateable.
● Full refreshes are supported for Oracle 10g and later.

注意，也是有限的支持。

  1.1 支持基于主键的全量刷新（primary key full refresh），oracle版本10.2及以后

示例：

源端操作：

  创建物化视图日志以及物化视图

create materialized view log on test.xy117 with primary key;
create materialized view test.mv_xy121
build immediate
refresh complete on commit with primary key
as
select * from test.xy117;

物化视图日志表：test.mlog$_xy117

物化视图表： test.mv_xy121
基表： test.xy117

源端extract 进程配置显示，注意只是显示配置，其他的步骤在之前已经增加

extract抽取进程配置：

EXTRACT s_ex_hr
--DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
TABLEEXCLUDE test.MV_121, test.mlog$_xy117
TABLE test.*;

extract pump进程配置：

EXTRACT pump_hr
USERID ggs, PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/hr/pp
PASSTHRU
TABLEEXCLUDE test.MV_121, test.mlog$_xy117
TABLE test.*;

增加表级别日志：

GGSCI>dblogin userid ggs, password ggs;

GGSCI>add trandata test.xy117;

目标端操作：

创建物化视图日志以及物化视图

create materialized view log on test.xy117 with primary key;
create materialized view test.mv_xy121
build immediate
refresh complete on commit with primary key
as
select * from test.xy117;

物化视图日志表：test.mlog$_xy117

物化视图表： test.mv_xy121
基表： test.xy117

目标端replicat进程配置显示，注意只是显示配置，其他的步骤在之前已经增加

replicat复制进程配置：

REPLICAT rep_hr
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
MAPEXCLUDE test.MV_121, test.mlog$_xy117
MAP test.*, TARGET test.*;

上述的黑体字replicat复制进程配置是错误的！！！replicat进程因此报错abended状态，因为源端根本就没有把 test.MV_121, test.mlog$_xy117的数据传送到目的端，源端传送的基表的数据，而目标端的test.MV_121, test.mlog$_xy117是目标端自己创建的，与源端毫无关系。当源端基表的数据传送到目标端的基表之后，就没有goldengate的事了，这是时候就是目标端物化视图日志根据主键去复制基表的数据到目标端的物化视图表！因此也就是不存在map（映射）的说法了。故而正确的配置如下：

REPLICAT rep_hr
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
MAP test.xy117, TARGET test.xy117;

好了，我现在在源端的基表TEST.XY117插入一条数据：

SQL> select * from test.xy117;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
   666 ogg                  success    2013-03-16
1  rows selected

源端物化视图表有数据了

  SQL> select * from test.mv_121;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
   666 ogg                  success    2013-03-16
1  rows selected

目标端基表数据过来了：

SQL> select * from test.xy117;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
   666 ogg                  success    2013-03-16
1  rows selected

目标端物化视图表有数据了

  SQL> select * from test.mv_121;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
   666 ogg                  success    2013-03-16
1  rows selected

1.2 支持基于主键的增加刷新（refresh fast）

示例；

源端与目标端操作：

创建物化视图日志以及物化视图

create materialized view log on test.xy100 with primary key;
create materialized view test.mv_xy122
build immediate
refresh complete on commit with primary key
as
select * from test.xy100;

配置文件与上述一样，只是把相关的表修改一下就可以了.

源端插入一条数据:

insert into test.xy100 values(100,'test100_20130316');
commit;

源端与目的端的物化视图表TEST.MV_122都能看到这条数据：

SQL> select * from test.mv_xy122;
 
         ID                          NAME
--------------------------------------- --------------------
        100                          test100_20130316
 
1 rows selected

1.3 不支持物化视图表基于with rowid的刷新（注意，不是物化视图日志，日志可以rowid）

create materialized view log on test.xy101 with rowid;
create materialized view test.mv_xy123
build immediate
refresh fast on commit with rowid
as
select * from test.xy101;

源端插入数据之后， 目标端没有数据过来，但是replicat进程处于running状态。

goldengate对于物化视图也是有限的支持。