(Created 7/15/2015, Last Updated 4/1/2016)
Unlike standard views in database, Materialized View (MV) has a logical existence and a physical existence. Therefore, it can be indexed, analyzed, and managed like other database tables. Let's talks about how to handle materialized views in Oracle GoldenGate data replications.
DML Replication Approach A (Recommended)
In data replication, if the source database defines materialized views, the best approach is to replicate the underlining tables and then create the same materialized view on the target.
DML Replication Approach B (Be Aware of the Limitations)
If you do want to capture from a materialized view and replicat the DML/DDL operations to a materialized view on the target, please follow the steps described as follows:
DDL with Materialized View
Oracle GoldenGate supports capturing DDLs from materialized views. So, if you don't want to capture DDL from materialized views and materialized view log, you can exclude it in the extract parameter file with the following setup:
DML Replication Approach A (Recommended)
In data replication, if the source database defines materialized views, the best approach is to replicate the underlining tables and then create the same materialized view on the target.
DML Replication Approach B (Be Aware of the Limitations)
If you do want to capture from a materialized view and replicat the DML/DDL operations to a materialized view on the target, please follow the steps described as follows:
- (Source) Check the DML operations in the source.
GoldenGate can capture DMLs including INSERT, UPDATE, DELETE and FULL REFRESH on materialized views. But it can't replicat the TRUNCATE operation (You can use a DELETE FROM statement instead). - (Source) To capture from a materialized view, you also need to have the followings:
(1) The source table must have a primary key.
(2) Materialized views can't be created WITH ROWID. However, the materialized view log can be created WITH ROWID. - (Target) To deliver to a materialized view, the materialized view on the target must be updatable (defined with the FOR UPDATE clause).
DDL with Materialized View
Oracle GoldenGate supports capturing DDLs from materialized views. So, if you don't want to capture DDL from materialized views and materialized view log, you can exclude it in the extract parameter file with the following setup:
DDL INCLUDE all, exclude objtype 'snapshot, & exclude objtype 'snapshot log'; DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10, REPORT;
For an Oracle materialized view and materialized views log, the correct types are snapshot and snapshot log, respectively. [4]
Frequently Asked Questions
1. Does Oracle GoldenGate for Oracle support DDL replication for materialized views?
Yes, Oracle GoldenGate for Oracle DDL replication supports materialized views (MV). This means if you modify the metadata of an MV or create a new MV, Oracle GoldenGate can perform updates and create new MVs on the target.
2. How can I setup the replication if my MV on target are not updatable?
If the MV on target is not updatable, you can't replication DML directly from the source MV. For DML operations you can replicate the underling tables instead.
3. How to make sure new MVs are added to the target database?
You can enable DDL replication for MVs so that the new MV additions are not missed in the future. The following is an example setup:
Frequently Asked Questions
1. Does Oracle GoldenGate for Oracle support DDL replication for materialized views?
Yes, Oracle GoldenGate for Oracle DDL replication supports materialized views (MV). This means if you modify the metadata of an MV or create a new MV, Oracle GoldenGate can perform updates and create new MVs on the target.
2. How can I setup the replication if my MV on target are not updatable?
If the MV on target is not updatable, you can't replication DML directly from the source MV. For DML operations you can replicate the underling tables instead.
3. How to make sure new MVs are added to the target database?
You can enable DDL replication for MVs so that the new MV additions are not missed in the future. The following is an example setup:
DDL INCLUDE OBJECTNAME SCOTT.ABC_MV MAPEXCLUDE SCOTT.ABV_MV MAP SCOTT.* TARGET SCOTT.*
You can use exclude materialized views in the Extract or Replicat, using TABLEEXCLUDE or MAPEXCLUDE.
4. Does GoldenGate support MV with DBLINK, such as:
SQL> create materialized view mv_custmer for update as select * from custmer@sharedb; ?
Yes, this is supported with GoldenGate version 11.1.1.1.1 and above versions.
4. When replicating to an MV, why do I get the following error "ORA-01732: data manipulation operation not legal on this view" and the replicat abends?
This is is normally because the target MV is not updatable.
Reference
4. Does GoldenGate support MV with DBLINK, such as:
SQL> create materialized view mv_custmer for update as select * from custmer@sharedb; ?
Yes, this is supported with GoldenGate version 11.1.1.1.1 and above versions.
4. When replicating to an MV, why do I get the following error "ORA-01732: data manipulation operation not legal on this view" and the replicat abends?
This is is normally because the target MV is not updatable.
Reference
- Oracle GoldenGate 12.1.2 Documentation: 1.7.2.4 Limitations of Support for Materialized Views
- What is, or is not, Supported for Materialized View by GoldenGate? (Doc ID 1291445.1)
- Which kind of objects are included by Oracle GoldenGate Extract Parameter DDL INCLUDE MAPPED? (Doc ID 1674973.1)
- DDL - Oracle GoldenGate Reference Guide (12.1.2)