Following the blog post about fast materialized views during transportable tablespace migrations, here’s a similar one for migrations using Oracle GoldenGate.

You can migrate using Oracle GoldenGate and avoid a complete refresh during downtime. Recent versions of Oracle GoldenGate can replicate materialized views and materialized view logs and in some cases a simple GoldenGate configuration will work fine.

However, if you are faced with a complex migration and the extract or replicat processes become a bottleneck, the below approach offers a fairly simple way to reduce the load on extract and replicat without having to perform a complete refresh during downtime.

The Basics

Like in the previous blog post, I will use an example based on a master table named SALES. If you need to catch up on materialized views, you can also check the previous blog post.

Remote Master Table, Local Materialized View

In this example, you are migrating a database from source to target. This database holds the materialized view, and a remote database acts as the master database, where the master table and materialized view log reside.

1. In the source database, register the extract process and exclude the materialized view:

   TABLEEXCLUDE <schema>.SALES_MV
   

2. If you configure DDL replication, I recommend excluding the materialized view and handling such changes in a different way:

   DDL EXCLUDE objtype 'snapshot'
   

3. Perform the initial load on the target database.
   • This creates the materialized view and the database link to the remote database.
   • Start the replicat process.
4. In the target database, perform a complete refresh of the materialized view:

   exec dbms_mview.refresh ('sales_mv','c');
   

   • This is before downtime, so who cares how long it takes to perform the complete refresh.
   • Although a fast refresh might be enough, it is better to be safe and avoid any problems with missing data.
   • You can configure a period fast refresh of the materialized view.
5. In the remote database, both source and target databases have registered as materialized views.

   select owner, mview_site
   from dba_registered_mviews
   where name='SALES_MV';
   

6. Downtime starts.
7. Complete the migration tasks needed to move over to the target database. This is out of scope of this blog post.
8. Shut down the source database.
9. In the remote database, purge materialized view log entries that are related to the materialized view in the source database:

   exec dbms_mview.purge_mview_from_log('<mview-owner>', 'SALES_MV', '<source-db>');
   

10. Unregister the materialized view in the source database:

    exec dbms_mview.unregister_mview('<mview-owner>', 'SALES_MV', '<source-db>');
    

If you want to reverse the application after moving to the target database and preserve the source database as a fallback, you postpone tasks 8-10.

Local Master Table, Remote Materialized View

In this example, you are migrating a database from source to target. This database holds the master table and materialized view log, while a remote database contains the materialized view.

1. In the source database, register the extract process and include the master table, but exclude the materialized view log:

   TABLE <schema>.SALES
   TABLEEXCLUDE <schema>.MLOG$_SALES
   

2. If you configure DDL replication, Oracle GoldenGate should automatically exclude the materialized view log. However, you can explicitly exclude it to be safe:

   DDL EXCLUDE objtype 'snapshot log'
   

3. Perform the initial load on the target database.
   • This creates the master table and the materialized view log.
4. In the target database, no remote database is using the master table yet. But replicat is keeping it up-to-date. However, the materialized view log might have orphan rows from the source database.
   • Drop and recreate the materialized view log.
5. In the remote database, create a new database link to the target database and a new materialized view based on the master table in the target database.

   create database link ... using '<target-tns-alias>';
   create materialized view sales_mv2 ... ;
   

   • SALES_MV2 should look exactly like SALES_MV except that it fetches from the target database instead of the source database.
6. Perform an initial complete refresh of SALES_MV2:

   exec dbms_mview.refresh ('sales_mv2','c');
   

   • The materialized view is not used by queries yet, so who cares how long it takes to perform the complete refresh.
   • You can configure a periodic refresh of the materialized view.
7. Create a synonym that initially points to SALES_MV – the materialized view based on the source database. You will change it later on.

   create synonym sales_syn for sales_mv;
   

8. Change your queries to reference SALES_SYN instead of SALES_MV directly.
   • You do this in a controlled manner ahead of the downtime window.
   • You can use auditing to detect usages of the materialized view (SALES_MV) and change all of them to use the synonym (SALES_SYN).
9. Downtime starts.
10. Complete the migration tasks needed to move over to the target database. This is out of scope of this blog post.
11. In the remote database, change the synonym to point to the materialized view that accesses the target database.

    create or replace synonym sales_syn for sales_mv2;
    

    • No application changes are needed because you made the applications use the synonym instead.
    • When you change the synonym to point to the new materialized view, this change is completely transparent to the application.
12. Drop the materialized view that accesses the source database.

    drop materialized view sales_mv;
    

13. Shut down the source database.

If you want to reverse the application after moving to the target database and preserve the source database as a fallback, you postpone tasks 12-13.

If you can’t change the application to use the synonym (with a different name), then there’s another approach:

• Keep accessing the SALES_MV until the downtime window. Don’t create the synonym yet.
• Drop the original materialized view: drop materialized view sales_mv.
• Create the synonym: create synonym sales_mv for sales_mv2.

Local Master Table, Local Materialized View

In this example, you are migrating a database from source to target. This database holds the master table, the materialized view log, and the materialized view. There is no remote database involved.

1. In the source database, register the extract process and include the master table, but exclude the materialized view and materialized view log:

   TABLE <schema>.SALES
   TABLEEXCLUDE <schema>.SALES_MV
   TABLEEXCLUDE <schema>.MLOG$_SALES
   

2. If you configure DDL replication, I recommend excluding the materialized view and materialized view log and handling such changes in a different way:

   DDL EXCLUDE objtype 'snapshot', EXCLUDE objtype 'snapshot log'
   

3. Perform the initial load on the target database.
   • This creates the master table, the materialized view, and the materialized view log.
4. In the target database, the replicat process replicates changes to the master table. No replication takes place on the materialized view or the materialized view log.
5. Perform an initial complete refresh of SALES_MV:

   exec dbms_mview.refresh ('sales_mv','c');
   

   • The refresh uses the master table in the target database. The replicat process is keeping the master table up-to-date.
   • The materialized view is not used by queries yet, so who cares how long it takes to perform the complete refresh.
   • You can configure a periodic refresh of the materialized view.
6. Downtime starts.
7. Complete the migration tasks needed to move over to the target database. This is out of scope of this blog post.
8. Shut down the source database.
9. Keep an eye on the materialized view log (MLOG$_SALES) and ensure it doesn’t grow beyond reason.

If you want to reverse the application after moving to the target database and preserve the source database as a fallback, you postpone task 8.

Further Reading

• Documentation, Microservices Architecture Documentation 23ai, Tables, Views, and Materialized Views
• Documentation, Database Administrator’s Guide 23ai, Managing Read-Only Materialized Views
• Documentation, SQL Language Reference 23ai, CREATE MATERIALIZED VIEW
• MOS note, Example For GoldenGate Replication Setup For Materialized View (Doc ID 2452979.1)
• MOS note, DataPump Export/Import – How To Exclude Materialized Views From Multiple Schemas? (Doc ID 1370060.1)