In some databases, fast refresh materialized views are important for good performance. During a migration using transportable tablespaces, how do you ensure the materialized views are kept up-to-date and avoid a costly complete refresh or stale data?

The Basics

The database populates a materialized view with data from a master table. To enable fast refresh on a materialized view, you must first create a materialized view log on the master table. The materialized view log captures changes on the master table. Then, the database applies the changes to the materialized view, thus avoiding a complete refresh.

When a user changes data in the master table (SALES), those changes are recorded in the materialized view log (MLOG$_SALES) and then used to refresh the materialized view (SALES_MV).

The master table and the materialized view log must reside in the same database (known as the master database). Hence, they are depicted in bluish colors. The materialized view is often in a different database, and then uses a database link to get the changes.

You only need materialized view logs if the materialized view is a fast refresh type.

A fast refresh materialized view needs to perform a complete refresh the first time, before it can move on with fast refreshes (thanks to my good friend, Klaus, for leaving a comment).

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.

The materialized view and the underlying segment are stored in a tablespace in the source database. That segment is used to recreate the materialized view in the target database without any refresh needed.

1. You must perform the following in the migration downtime window.
2. In the source database, stop any periodic refresh of the materialized view.
3. Optionally, perform a fast refresh of the materialized view:

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

4. Start the migration using transportable tablespaces.
   • Set the tablespaces read-only.
   • Perform a final incremental backup.
   • Data Pump sets the tablespaces read-write in the target database.
   • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
5. In the target database, you can now perform a fast refresh.
   • Data Pump has already recreated the database link to the remote master database.
   • Although the materialized view is now in a different database, it can fetch the recent changes from the master database starting with the last refresh in the source database.
6. In the master database, both materialized views from the source and target database are now registered:

   select * from dba_registered_mviews where name='SALES_MV';
   

7. 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>');
   

8. Unregister the materialized view in the source database:

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

9. In the target database, re-enable periodic refresh of the materialized view.

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.

The master table and materialized view log are stored in a tablespace in the source database. The migration moves the data to the target database. The materialized view is in the same database, so no remote database or database link is involved.

1. You must perform the following in the migration downtime window.
2. In the remote database, stop any periodic refresh of the materialized view.
3. Optionally, perform a fast refresh of the materialized view:

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

4. Start the migration using transportable tablespaces.
   • Set the tablespaces read-only.
   • Perform a final incremental backup.
   • Data Pump sets the tablespaces read-write in the target database.
   • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
5. In the remote database, ensure that the database link now points to the new target database.
   • If the database link uses a TNS alias, you can update it.
   • Or recreate the database link with a new connect descriptor.
6. Perform a fast refresh.

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

   • If you hit ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed, run the refresh again.
7. Re-enable periodic refresh of the materialized view.
8. In the target database, ensure that the materialized view log is now empty.

   select * from mlog$_sales;
   

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.

The master table and materialized view log are stored in a tablespace in the source database. The migration moves the data to the target database. The materialized view is in the same database, so there is no remote database or database link involved.

1. You must perform the following in the migration downtime window.
2. In the source database, stop any periodic refresh of the materialized view.
3. Perform a fast refresh of the materialized view:

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

4. Ensure the materialized view log is empty, i.e., all rows have been refreshed into the materialized view.

   select count(*) from mlog$_sales;
   

   • The query must return 0 rows. If more rows, then perform an additional fast refresh.
   • If a remote materialized view uses the materialized view log then it is acceptable to move on if you are sure the local materialized view is completely updated.
5. Start the migration using transportable tablespaces.
   • Set the tablespaces read-only.
   • Perform a final incremental backup.
   • Data Pump sets the tablespaces read-write in the target database.
   • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
6. In the target database, perform a fast refresh.

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

7. Re-enable periodic refresh of the materialized view.
8. Ensure that the materialized view log is now empty.

   select * from mlog$_sales;
   

A word of caution here. The materialized view must be completely up-to-date in the source database before the migration. After the migration, the same materialized view won’t be able to refresh the pre-migration rows. That is why you are checking for rows in mlog$_sales.

Any new changes made in the target database will sync fine.

Further Reading

• Documentation, Database Administrator’s Guide 23ai, Managing Read-Only Materialized Views
• Documentation, SQL Language Reference 23ai, CREATE MATERIALIZED VIEW