8 Administering a Materialized View Replication Environment

Refreshing Materialized Views

Refreshing materialized views synchronizes them with their master tables. Changes made to a master table since the last refresh of a materialized view are applied to the materialized view during refresh. If a materialized view is updatable, then changes made to the materialized view since its last refresh are also applied to the master table during refresh.

If a group of materialized views is used for a specific purpose, such as an application, then it is usually best to place these materialized views in a refresh group. When you refresh the refresh group, the materialized views in the refresh group are refreshed to a consistent point in time. You can also refresh materialized views individually, but then the materialized views are not consistent to a point in time.

The following topics describe refreshing materialized views:

• Refreshing a Refresh Group

• Refreshing a Materialized View

Adding Materialized Views to a Refresh Group

When two or more materialized views must be consistent to a point in time, the materialized views should belong to the same refresh group.

To add materialized views to a refresh group:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Refresh Groups in the Materialized View Site section.

6. On the Refresh Groups page, use the search tool to list the refresh group that you want to modify.

7. Select the refresh group.

8. Click Edit.

9. On the Edit Refresh Group page, click Materialized Views.

10. On the Materialized Views subpage, click Add.

11. On the Search and Select: Materialized View page, use the search tool to list the materialized views that you want to add to the refresh group.

    
    Description of the illustration tdpii_select_mv.gif
    
    

12. Select the materialized views that you want to add to the refresh group.

13. Click OK.

14. On the Edit Refresh Group page, click Apply to finish adding the materialized views to the refresh group.

Dropping a Materialized View

When a materialized view is no longer needed at a materialized view site, you can drop the materialized view. If possible, then you should drop the materialized view when the materialized view site can connect to the master site. Information about the materialized view is removed automatically from the master site when a network connection is established.

If you must drop a materialized view when the materialized view site cannot connect to the master site, then information about the materialized view must be removed from the master site manually. See "Example: Cleaning Up Materialized View Support at a Master Site" to learn how to remove this information after you drop the materialized view.

To drop a materialized view:

1. On Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Materialized Views in the Materialized View Site section.

6. On the Materialized Views page, use the search tool to list the materialized view that you want to drop.

   To list all materialized views, leave the Schema and Object Name fields blank and click Go.

7. Select the materialized view in the list.

   
   Description of the illustration tdpii_mvs.gif
   
   

8. Click Delete.

9. On the confirmation page, click Yes to delete the materialized view.

Example: Cleaning Up Materialized View Support at a Master Site

If you dropped a materialized view while the materialized view site was not connected to the master site over the network, then you should clean up materialized view support at the master site. Cleaning up materialized view support includes the following actions:

• Unregistering the materialized view

  Unregistering the materialized view removes information about the materialized view in the data dictionary at the master site.

• Either purging the materialized view log or dropping the materialized view log

  If the master table for the materialized view is the master table for other materialized views, then you should purge the materialized view log of information for the materialized view that was dropped. If the master table for the materialized view is not a master table for any other materialized views, then you can drop the materialized view log.

Oracle Database automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log size does not increase endlessly. Because multiple materialized views can use the same materialized view log, rows already used to refresh one materialized view might still be needed to refresh another materialized view. Oracle Database does not delete rows from the log until all materialized views have used them. If you drop a materialized view without cleaning up the master site, then the materialized view log for the materialized view can become very large.

The example in this topic cleans up the following materialized view support:

• The master site is ii1.net.

• The materialized view site is ii2.net.

• The name of the materialized view is employees_mvr.

• The owner of the materialized view is hr.

To clean up support for this materialized view at the master site:

1. On a command line, connect to the master site ii1.net in SQL*Plus as an administrative user, such as the replication administrator or SYSTEM. By default, the user name of the replication administrator is repadmin.

   sqlplus system/user-password@ii1.net
   

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Either purge or drop the materialized view log for the master table of the materialized view:

   • If the master table is also the master table for other materialized views in addition to the one that was dropped, then purge the materialized view log of information for the materialized view that was dropped:

     BEGIN
        DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
           mviewowner => 'hr',
           mviewname  => 'employees_mvr', 
           mviewsite  => 'ii2.net');
     END;
     /
     

   • If the master table is not a master table for any materialized views other than the one that was dropped, then drop the materialized view log on the master table:

     DROP MATERIALIZED VIEW LOG ON hr.employees;
     

3. Unregister the materialized view:

   BEGIN
      DBMS_MVIEW.UNREGISTER_MVIEW (
         mviewowner => 'hr',
         mviewname  => 'employees_mvr',
         mviewsite  => 'ii2.net');
   END;
   /
   

If you are not sure about the materialized view owner, name, or materialized view site, then you can query the DBA_REGISTERED_MVIEWS data dictionary view to obtain this information.

Viewing an Overview of the Replication Components at a Database

The Advanced Replication: Administration page in Enterprise Manager provides an overview of the replication components at a database.

To view an overview of the replication components at a database:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

Each replication component is associated with a number that shows the quantity of the component at the current database. Click a number to view more information about these components and to manage these components.

If you are viewing a materialized view site, then you can monitor the following components:

• Scheduled Links in the General section shows the number of scheduled links at the materialized view site. Scheduled links are used to push deferred transactions automatically. See "About Scheduled Links and Deferred Transactions".

• Materialized View Groups in the Materialized View Site section shows the number of materialized view groups at the materialized view site. Organizing materialized views into groups makes it easier to manage them. In addition, materialized views must be placed in a materialized view group for them to be updatable. See "About Replication Groups and Updatable Materialized Views" for information about materialized view groups.

• Materialized Views in the Materialized View Site section shows the number of materialized views at the materialized view site. See "About Materialized View Replication" for information about materialized views.

• Refresh Groups in the Materialized View Site section shows the number of refresh groups at the materialized view site. When a refresh group is refreshed, the materialized views in the refresh group are consistent to the same point in time. See "About Refresh Groups".

If you are viewing a master site for one or more materialized view sites, then you can monitor the following components:

• Master Groups in the Multimaster Replication section shows the number of master groups at the master site. A materialized view group at a materialized view site can be based on a master group. See "About Replication Groups and Updatable Materialized Views" for information about master groups and materialized view groups.

• Materialized View Logs in the Master Site section shows the number of materialized view logs at a master site. Materialized view logs enable a fast refresh of materialized views. See "About Materialized View Refresh" for information about materialized view logs.

• Error Transactions in the General section shows the number of error transactions at the master site. Error transactions can occur when updatable materialized views are refreshed and conflicts are encountered that cannot be resolved. See "About Conflicts and Updatable Materialized Views".

Click Help for information about the other replication components listed on the Overview subpage of the Advanced Replication: Administration page.

Viewing Information About Materialized Views

A materialized view contains a complete or partial copy of a table from a single point in time. In a replication environment, a materialized view is typically in a different database than the table on which it is based.

To view information about materialized views:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Materialized Views in the Materialized View Site section to open the Materialized Views page.

6. Use the search tool to list the materialized views that you want to view. To list all materialized views, leave the Schema and Object Name fields blank and click Go.

   
   Description of the illustration tdpii_mvs.gif
   
   

   The Materialized Views page contains important information about each materialized view listed, including its master table, master site (in the Master Link columns), and the last time it was refreshed. Click Help for information about the columns on this page.

7. For detailed information about a materialized view, select it and click View to open the View Materialized View page.

The View Materialized View page contains detailed information about the materialized view. The information is organized into the following sections: General, Refresh, Storage, Index Storage, Options, and Master Information. Click Help for information about the sections on this page.

Determining Which Materialized Views Are Currently Refreshing

Refreshing a materialized view synchronizes data in the materialized view with data in its master table. You can query the V$MVIEWREFRESH dynamic performance view to determine which materialized views are currently refreshing.

Before shutting down a database that contains materialized views, it is best to check if any materialized views are currently refreshing. If any materialized views are refreshing, then wait until all of the refreshes are complete before shutting down the database.

To determine which materialized views at a materialized view site are currently refreshing:

1. Open SQL*Plus and connect to the materialized view site in SQL*Plus as the materialized view administrator:

   sqlplus mvadmin/user-password
   

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Run the following query:

   COLUMN CURRMVOWNER HEADING 'Owner' FORMAT A15
   COLUMN CURRMVNAME HEADING 'Materialized|View' FORMAT A25
    
   SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH;
   

   The output will be similar to the following:

                   Materialized
   Owner           View
   --------------- -------------------------
   HR              COUNTRIES_MV
   HR              EMPLOYEES_MV
   

The V$MVREFRESH dynamic performance view does not contain information about updatable materialized views when the deferred transactions of the materialized views are being pushed to their masters.

Viewing Information About Materialized View Groups

Materialized view groups make it easy to manage materialized views that are logically related. For example, you might add all of the materialized views in a schema or all of the materialized views used by an application to a materialized view group. Also, for materialized views to be updatable, they must belong to a materialized view group.

To view information about the materialized view groups at a materialized view site:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Materialized View Groups in the Materialized View Site section.

   The Materialized View Groups page appears, listing the names of the materialized view groups at the materialized view site.

6. To view detailed information about a materialized view group, click the name of the materialized view group to open the View Materialized View Group page.

The View Materialized View Group page includes the following information:

• In the Link To Master field, the name of the database link to the master site. The master site contains the master tables of the materialized views in the materialized view group.

• In the Propagation Mode field, Asynchronous is displayed because the propagation sends messages according to a propagation schedule.

• In the Description field, a description of the materialized view group if the group has a description.

• In the Objects section, a list of the database objects in the materialized view group and information about each database object

Click Help for more information about this page.

Viewing Information About Deferred Transactions for Updatable Materialized Views

At a materialized view site, deferred transactions include changes to updatable materialized views. The deferred transactions are stored at the materialized view site so that they can be sent to the master site and applied to master tables. Deferred transactions for a materialized view are always pushed when a materialized view is refreshed. A scheduled link determines when the deferred transactions are pushed to the master site independent of the refresh process.

To view information about deferred transactions for updatable materialized views:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Deferred Transactions in the General section to open the Administration: Deferred Transactions page.

   
   Description of the illustration tdpii_def_trans.gif
   
   

   The Administration: Deferred Transactions page contains the transaction ID and the following information for each deferred transaction:

   • The Destination field shows the master site to which the deferred transaction will be pushed.

   • The Start Time field shows when the deferred transaction was placed in the deferred transaction queue at the materialized view site.

   • The Number of Calls field typically shows the number of individual rows changed in the transaction.

   Click Help for more information about this page.

6. Click a transaction ID in the Transaction ID column to open the Deferred Transaction page and view more information about the transaction.

   
   Description of the illustration tdpii_def_tran.gif
   
   

   The Deferred Transaction page shows the materialized view and the type of operation that changed it. For example, REP_UPDATE designates an update operation.

7. To view detailed information about a specific change in the deferred transaction, select the change and click Details.

   
   Description of the illustration tdpii_def_change.gif
   
   

   This page shows each column in the materialized view, along with its original value and, where appropriate, its new value. When there is a new value for a column, then the change modified the column value from the original value to the new value.

Viewing Information About Refresh Groups

When you refresh a refresh group, all of the materialized views in the refresh group are refreshed to a consistent point in time.

To view information about the refresh groups at a materialized view site:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Refresh Groups in the Materialized View Site section.

   The Refresh Groups page appears.

6. Use the search tool to list the refresh groups that you want to view. To list all refresh groups, ensure that the Schema and Object Name fields are blank, and click Go.

   
   Description of the illustration tdpii_refresh_groups.gif
   
   

   The Refresh Groups page lists the names of the refresh groups at the materialized view site. It also contains the following information for each refresh group:

   • The Broken column shows whether the refresh job is working properly. No means that the job is working properly. Yes means that the job is broken. If 16 attempts to refresh a refresh group fail, then the refresh job is considered broken. If the refresh job is broken, see "Correcting Problems with Materialized View Refresh".

   • The Next Date column shows the next date and time when the refresh group will be automatically refreshed.

   • The Interval column shows the time interval between automatic refreshes of the refresh group.

7. For detailed information about a refresh group, select it and click View to open the View Refresh Group page.

The View Refresh Group page contains detailed information about the refresh group. The information is organized into the following sections: General, Refresh, and Materialized Views. Click Help for information about all of the sections on this page.

Viewing Materialized View Logs at a Master Site

Materialized view logs keep track of changes to master tables so that a fast refresh of materialized views is possible.

To view information about the materialized view logs at a master site:

1. In Enterprise Manager, log in to the master site as an administrative user, such as the replication administrator or SYSTEM. By default, the user name of the replication administrator is repadmin.

2. Click Data Movement to open the Data Movement subpage.

3. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

4. Click the number associated with Materialized View Logs in the Master Site section.

   The Materialized View Logs page appears.

5. Use the search tool to list the materialized view logs that you want to view.

   To list all materialized view logs, ensure that the Schema and Object Name fields are blank, and click Go.

   The Materialized View Logs page lists the schema, underlying log table, and master table for each materialized view log listed.

6. For detailed information about a materialized view log, select it and click View to open the View Materialized View Log page.

The View Materialized View Log page contains detailed information about the materialized view log. The information is organized into the following sections: General, Options, and Storage. Click Help for information about the sections on this page.

Viewing the Materialized Views for a Master Site

When you configure a materialized view at a materialized view site, it registers the materialized view at the master site. You can query the DBA_REGISTERED_MVIEWS data dictionary view to show the following information about each materialized view registered at a master site:

• The owner of the materialized view

• The name of the materialized view

• The materialized view site that contains the materialized view

• Whether the materialized view is updatable or read-only

To show this information:

1. Open SQL*Plus and connect to the master site in SQL*Plus as an administrative user, such as the replication administrator or SYSTEM. By default, the user name of the replication administrator is repadmin.

   sqlplus system/user-password
   

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Run the following query:

   COLUMN OWNER HEADING 'Owner' FORMAT A20
   COLUMN NAME HEADING 'Name' FORMAT A20
   COLUMN MVIEW_SITE HEADING 'Materialized|View Site' FORMAT A15
   COLUMN UPDATABLE HEADING 'Updatable?' FORMAT A10
    
   SELECT OWNER, NAME, MVIEW_SITE, UPDATABLE
     FROM DBA_REGISTERED_MVIEWS;
   

   The output will be similar to the following:

                                            Materialize               
   Owner                Name                 View Site       Updatable?
   -------------------- -------------------- --------------- ----------
   HR                   COUNTRIES_MV         II2.NET         YES
   HR                   DEPARTMENTS_MV       II2.NET         YES
   HR                   EMPLOYEES_MV         II2.NET         YES
   HR                   JOBS_MV              II2.NET         YES
   HR                   JOB_HISTORY_MV       II2.NET         YES
   HR                   LOCATIONS_MV         II2.NET         YES
   HR                   REGIONS_MV           II2.NET         YES
   

Correcting Problems with Materialized View Refresh

If one or more materialized views at a materialized view site cannot refresh, then the following are typical causes and solutions:

• A network problem is preventing one or more materialized views from refreshing. If you have multiple refresh groups and multiple materialized views at a materialized view site, and none of them can refresh, then a network problem is the most likely cause. In this case, correct the network problem, and refresh the relevant materialized views. See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

• A master site for the materialized views is down. A materialized view cannot refresh if its master site is not open. If you have a materialized view site with materialized views that use multiple master sites, and only the materialized views for a particular master site cannot refresh, then a master site problem is the most likely cause. In this case, check the database at the master site. If it is down, then start it, and refresh the relevant materialized views. See Oracle Database 2 Day DBA for information about starting a database.

• A database link is broken. If all of the materialized views in a materialized view group cannot refresh, then the database link used to refresh the materialized views might be broken. If a database link is broken, then you can correct the problem by deleting the database link and re-creating it.

To re-create a broken database link:

1. Log in to Enterprise Manager as an administrative user, such as SYSTEM.

2. Go to the Database Home page.

3. Click Schema to open the Schema subpage.

4. Click Database Links in the Database Objects section.

   The Database Links page appears.

5. Use the search tool to list the database link used by the materialized views.

   The database link name should be the same as the global name of the master site for the materialized views.

6. Select the database link.

7. Click View.

8. On the View Database Link page, click Test to see if the database link is active. If it is active, then the materialized view refresh problems are caused by a different issue. If the database link is not active, then follow Steps 9 through 13 to delete it and re-create it.

9. Click OK to return to the Database Links page.

10. Select the database link.

11. Click Delete.

12. Click Yes on the confirmation page to delete the database link.

13. Follow the instructions in "Example: Creating a Database Link" to re-create the database link.

Preventing Materialized View Logs From Becoming Too Large

Materialized view logs keep track of changes to master tables at master sites. When a fast refresh is performed on a materialized view, the changes in the materialized view log are applied to the materialized view. After changes are applied to all of the materialized views that use the materialized view log, the changes are deleted from the materialized view log.

A materialized view log can become too large when one or more materialized views that use the materialized view log do not refresh for a relatively long period of time. This situation can slow refresh performance for other materialized views. The following are typical causes and solutions:

• A network problem is preventing one or more materialized views that use the materialized view log from refreshing. In this case, correct the network problem and refresh the relevant materialized views. See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

• A materialized view was dropped at a materialized view site when the materialized view site was not connected to the network. When a materialized view is dropped and a network connection is available, information about the materialized view is removed from the master site automatically. However, when a network connection is not available, information about the materialized view remains at the master site and must be cleaned up manually. See "Example: Cleaning Up Materialized View Support at a Master Site".

• Materialized views are not being refreshed often enough. Materialized views can be refreshed manually, or they can be refreshed automatically when they are in a refresh group.

  If you refresh materialized views manually, then you might need to refresh them more often if materialized view logs are becoming too large. See "Refreshing a Materialized View".

  If materialized views are in a refresh group that is not refreshing often enough, then you can make the refresh interval shorter for the refresh group.

To modify the refresh interval for a refresh group:

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Refresh Groups in the Materialized View Site section.

6. On the Refresh Groups page, use the search tool to list the refresh group that you want to modify.

7. Select the refresh group.

8. Click Edit.

   The Edit Refresh Group page appears, showing the General subpage.

   
   Description of the illustration tdpii_edit_ref_grp.gif
   
   

9. In the Refresh section, click Change for the Interval field.

10. Shorten the interval using the Every field and list.

11. Click OK.

12. On the Edit Refresh Group page, click Apply to save your changes.