Materialize View 的 GoldenGate 同步测试

1,源端刷新方式：refresh force on commit

源库修改master table，commit后查询该物化视图结果已经刷新，随后目标库REPLICAT进程ABENDED,报错如下：

2012-12-05 03:04:21  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  No unique key is defined for table 'MV3'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2012-12-05 03:04:22  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732). DELETE FROM "SCOTT"."MV3"  WHERE "ENAME" = :b0 AND "DNAME" = :b1 AND ROWNUM = 1.
2012-12-05 03:04:22  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Aborted grouped transaction on 'SCOTT.MV3', Database error 1732 (OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732). DELETE FROM "SCOTT"."MV3"  WHERE "ENAME" = :b0 AND "DNAME" = :b1 AND ROWNUM = 1).
2012-12-05 03:04:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Repositioning to rba 16644 in seqno 4.
2012-12-05 03:04:22  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  SQL error 1732 mapping SCOTT.MV3 to SCOTT.MV3 OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732). DELETE FROM "SCOTT"."MV3"  WHERE "ENAME" = :b0 AND "DNAME" = :b1 AND ROWNUM = 1.
2012-12-05 03:04:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Repositioning to rba 16644 in seqno 4.
2012-12-05 03:04:22  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Error mapping from SCOTT.MV3 to SCOTT.MV3.
2012-12-05 03:04:22  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  PROCESS ABENDING.

通过logdump可以看到SQL语句为 delete mv3 ..  insert mv3 ..
在REPLICAT进程添加如下参数：
MAPEXCLUDE SCOTT.MV3
重启进程正常，在目标库查询SCOTT.MV3结果与源库保持同步


2,源端刷新方式：refresh force on demand （dbms_mview.REFRESH_ALL_MVIEWS）

源库修改master table，commit后查询该物化视图结果并未刷新，目标库REPLICAT进程正常。执行过程 dbms_mview.REFRESH_ALL_MVIEWS(:x) 后查询所有物化视图结果已经刷新，随后目标库REPLICAT进程ABENDED,报错如下：

2012-12-05 03:31:52  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL found, operation [/* QSMQ VALIDATION */ ALTER SUMMARY "SCOTT"."MV1" COMPILE  (size 58)].
2012-12-05 03:31:52  INFO    OGG-00491  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL operation is of default scope.
2012-12-05 03:31:52  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL operation included [INCLUDE ALL], optype [ALTER], objtype [SUMMARY], objowner [SCOTT], objname [MV1].
2012-12-05 03:31:52  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Setting current schema for DDL operation to [SCOTT].
2012-12-05 03:31:52  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Executing DDL operation.
2012-12-05 03:31:52  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Fatal error executing DDL replication: error [Error code [940], ORA-00940: invalid ALTER command SQL /* QSMQ VALIDATION */ ALTER SUMMARY "SCOTT"."MV1" COMPILE  /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
2012-12-05 03:31:52  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  PROCESS ABENDING.

在REPLICAT参数文件中增加MAPEXCLUDE 及 DDL INCLUDE MAPED 排除所有物化视图及其DDL，启动进程成功，但是目标库并未刷新物化视图


3,源端刷新方式：refresh force on demand （dbms_mview.REFRESH）

源库修改master table，commit后查询该物化视图结果并未刷新，目标库REPLICAT进程正常。执行过程 dbms_mview.REFRESH(‘MV2’) 后查询该物化视图结果已经刷新，随后目标库REPLICAT进程ABENDED,报错如下：

2012-12-05 04:35:34  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL found, operation [/* QSMQ VALIDATION */ ALTER SUMMARY "SCOTT"."MV2" COMPILE  (size 58)].
2012-12-05 04:35:34  INFO    OGG-00491  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL operation is of default scope.
2012-12-05 04:35:34  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  DDL operation included [INCLUDE ALL], optype [ALTER], objtype [SUMMARY], objowner [SCOTT], objname [MV2].
2012-12-05 04:35:34  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Setting current schema for DDL operation to [SCOTT].
2012-12-05 04:35:34  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Executing DDL operation.
2012-12-05 04:35:34  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  Fatal error executing DDL replication: error [Error code [940], ORA-00940: invalid ALTER command SQL /* QSMQ VALIDATION */ ALTER SUMMARY "SCOTT"."MV2" COMPILE  /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
2012-12-05 04:35:34  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rads_1.prm:  PROCESS ABENDING.