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:
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:
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 18.104.22.168.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.
- Oracle GoldenGate 12.1.2 Documentation: 22.214.171.124 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)