7 Replicating Data Using Materialized Views

About Master Sites, Master Tables, and Materialized View Sites

In a replication environment, a materialized view shares data with a table in a different database called a master site. The table associated with the materialized view at the master site is called the master table. A materialized view contains a complete or partial copy of a master table from a single point in time. The database in which the materialized view resides is called the materialized view site.

About Materialized View Refresh

Refreshing a materialized view synchronizes data in the materialized view with data in its master table. The materialized view controls when the refresh is performed, either on a fixed schedule or on demand. During a refresh, row data from the master table is pulled down and applied at the materialized view site. Oracle Database provides the following refresh methods:

• Fast refresh pulls down only the rows that have changed since the last refresh.

• Complete refresh updates the entire materialized view.

• Force refresh performs a fast refresh when possible. When a fast refresh is not possible, force refresh performs a complete refresh.

A fast refresh is more efficient than a complete refresh. A fast refresh of a materialized view is possible only if the master table has a materialized view log. A materialized view log is a table at the master site that records all of the insert, update, and delete operations performed on the master table.

A materialized view log is associated with a single master table, and each master table has only one materialized view log, regardless of how many materialized views refresh from the master table. When a fast refresh is performed on a materialized view, entries in the materialized view log that have appeared since the materialized view was last refreshed are applied to the materialized view.

About Refresh Groups

When it is important for two or more materialized views to be transactionally consistent with each other, you can organize them into refresh groups. By refreshing a refresh group, you can ensure that the data in all of the materialized views in the refresh group correspond to the same transactionally consistent point in time. A materialized view in a refresh group still can be refreshed individually. However, doing so nullifies the benefits of the refresh group because refreshing the materialized view individually does not refresh the other materialized views in the refresh group.

About Replicating Read-Only Data Using Materialized Views

Read-only materialized views provide read-only access to the table data that originates from a master site. Applications can query data from read-only materialized views to avoid network access to the master site, regardless of network availability. However, applications throughout the system must access data at the master site to perform insert, update, and delete operations.

Figure 7-1 shows how a read-only materialized view works.

Figure 7-1 Read-Only Materialized View

Description of "Figure 7-1 Read-Only Materialized View"

Figure 7-1 shows that client applications can perform local queries on a read-only materialized view at the materialized view site. These applications can update data at the remote master. The materialized view is updated with the changes at the master when the materialized view refreshes over the network.

Tutorial: Configuring Read-Only Data Replication Using Materialized Views

The example in this topic configures read-only data replication using materialized views. Specifically, this example creates the following read-only materialized views at a materialized view site:

• The mvadmin.employees_mvr materialized view based on the hr.employees table at the master site.

• The mvadmin.departments_mvr materialized view based on the hr.departments table at the master site.

To configure these read-only materialized views: 

1. Complete the actions described in the following topics:

   • "Preparing for Materialized View Replication"

   • "Configuring Materialized View Sites"

   • "Configuring Materialized View Logs at the Master Site" if you want to perform a fast refresh of the materialized views. For this example, create a materialized view log for each of the following tables: hr.departments and hr.employees.

2. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The materialized view site is the database in which you want to create materialized views. The default user name for the materialized view administrator is mvadmin.

3. Go to the Database Home page.

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

5. Click Setup in the Advanced Replication section.

6. On the Advanced Replication: Setup page, expand Read-only Materialized Views.

7. Select Create Materialized View.

8. Click Continue to open the Create Materialized View page.

   
   Description of the illustration tdpii_create_mv.gif
   
   

9. Complete the following actions to create the hr.employees_mvr materialized view:

   1. Enter employees_mvr in the Name field.

   2. Enter hr in the Schema field.

   3. If you have a specific tablespace for the materialized view you are creating, then enter the tablespace name in the Tablespace field. Otherwise, leave <Default> in the field.

   4. Deselect Enable the materialized view for query rewrite.

   5. Ensure that the following are not selected:

      • Make the materialized view updatable

      • Build From Existing Table

   6. Enter the following in the Materialized View Query field:

      SELECT employee_id, 
              first_name, 
              last_name, 
              email, 
              phone_number, 
              hire_date, 
              job_id,
              salary, 
              commission_pct, 
              manager_id, 
              department_id
      FROM hr.employees@master_site
      

      Replace master_site with the global name of the master site that contains the master table.

   7. Deselect Analyze the materialized view after it is created.

   8. If necessary, then set options for the materialized view on the other subpages.

   9. Click OK to create the materialized view.

   The hr.employees_mvr materialized view is a complete copy of the master hr.employees table. To create a materialized view that contains a subset of the data, you can include a WHERE clause in your SELECT statement.

10. On the Advanced Replication: Setup page, expand Read-only Materialized Views.

11. Select Create Materialized View.

12. Click Continue to open the Create Materialized View page.

13. Complete the following actions to create the hr.departments_mvr materialized view:

    1. Enter departments_mvr in the Name field.

    2. Enter hr in the Schema field.

    3. If you have a specific tablespace for the materialized view you are creating, then enter the tablespace name in the Tablespace field. Otherwise, leave <Default> in the field.

    4. Deselect Enable the materialized view for query rewrite.

    5. Ensure that the following are not selected:

       • Make the materialized view updatable

       • Build From Existing Table

    6. Enter the following in the Materialized View Query field:

       SELECT department_id, 
               department_name, 
               manager_id, 
               location_id 
       FROM hr.departments@master_site
       

       Replace master_site with the master site that contains the master table.

    7. Deselect Analyze the materialized view after it is created.

    8. If necessary, then set options for the materialized view on the other subpages.

    9. Click OK to create the materialized view.

14. Create a refresh group and add the materialized views to it so that they are consistent with a single point in time when they are refreshed. In this example, add the hr.employees_mvr and hr.departments_mvr materialized views to the refresh group. See "Configuring a Refresh Group".

About Replicating Read/Write Data Using Materialized Views

Updatable materialized views enable users to insert, update, and delete rows in the materialized views. When an updatable materialized view is refreshed, these changes are pushed to the master site and applied to the master table.

Figure 7-2 shows how an updatable materialized view works.

Figure 7-2 Updatable Materialized View

Description of "Figure 7-2 Updatable Materialized View"

Figure 7-2 shows that client applications can query and update an updatable materialized view at the materialized view site. These applications can also update data at the remote master site. When the materialized view initializes a refresh over the network, the refresh includes two key phases. First, transactions that have been performed on the materialized view are pushed to the master site and applied as appropriate. Next, after resolving any conflicting updates, the materialized view pulls the changed row data from the master site and applies it.

The following topics provide more information about replicating read/write data using materialized views:

• About Replication Groups and Updatable Materialized Views

• About Scheduled Links and Deferred Transactions

• About Conflicts and Updatable Materialized Views

About Replication Groups and Updatable Materialized Views

A replication group is a collection of replication objects that are logically related. Organizing related database objects within a replication group makes it easier to administer many objects together. At a master site, a replication group is called a master group. At a materialized view site, a replication group is called a materialized view group.

For changes made to an updatable materialized view to be pushed to the master during refresh, the following conditions must be met:

• The updatable materialized view must belong to a materialized view group.

• The materialized view group must be based on a master group at the master site.

The materialized view group can contain materialized views for all of the tables in the master group or for a subset of the tables.

Figure 7-3 shows two materialized view groups. Materialized view group A contains materialized views that correspond to a subset of the tables in the master group, while materialized view group B contains a materialized view for each table in the master group.

Figure 7-3 Materialized View Groups Correspond with Master Groups

Description of "Figure 7-3 Materialized View Groups Correspond with Master Groups"

If you are using materialized view groups and refresh groups at the same site, then a single refresh group can contain multiple materialized view groups. A materialized view group is different from a refresh group, although it can contain the same materialized views.

Figure 7-4 shows a refresh group that contains two materialized view groups.

Figure 7-4 Refresh Groups Can Contain Objects from Multiple Materialized View Groups

Description of "Figure 7-4 Refresh Groups Can Contain Objects from Multiple Materialized View Groups"

See Also:

• "About Refresh Groups"

• "Configuring Replication of Read/Write Data Using Materialized Views"

Configuring Replication of Read/Write Data Using Materialized Views

This topic provides instructions for configuring read/write data replication using materialized views. Specifically, this example configures:

• A master group at a master site that contains master tables. See "About Replication Groups and Updatable Materialized Views".

• A materialized view group at a materialized view site that contains updatable materialized views that are based on the master tables at the master site. For materialized views to be updatable, they must be in a materialized view group at the materialized view site, and the materialized view group must correspond with a master group at the master site. See "About Replication Groups and Updatable Materialized Views".

• Latest timestamp conflict resolution at the master site for the master tables. Conflict resolution ensures that the master tables and materialized views remain consistent if changes are made to the same rows in the master table and its corresponding updatable materialized view at nearly the same time. See "About Conflicts and Updatable Materialized Views".

  When a conflict occurs, latest timestamp conflict resolution means that the most recent change is retained and the older change is discarded. So, if a row is updated in a materialized view at one point in time, and then the row is updated in the master table at a later time, then the row in the master table replaces the row in the materialized view automatically when the materialized view is refreshed. If the row in the materialized view was updated more recently than the row in the master table, then the row in the materialized view replaces the row in the master table during refresh.

To configure updatable materialized views: 

1. Complete the actions described in the following topics:

   • "Preparing for Materialized View Replication"

   • "Configuring Materialized View Sites"

   • "Configuring Materialized View Logs at the Master Site" if you want to perform a fast refresh of the materialized views. Configure a materialized view log for each table that will be a master table.

2. Configure the master site. The master site contains the master tables on which the updatable materialized views are based.

   1. In Oracle Enterprise Manager, log in to the database that will be the master site as an administrative user, such as SYSTEM.

   2. Go to the Database Home page.

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

   4. Click Setup in the Advanced Replication section.

   5. On the Advanced Replication: Setup page, expand Updateable Materialized View Replication.

   6. Select Configure Master Sites for Replication.

   7. Click Continue to open the Configure Master Sites for Replication Wizard.

      
      Description of the illustration tdpii_setup_master_sites.gif
      
      

      Complete the pages in the wizard to configure the master site.

      When you finish the wizard, an Enterprise Manager job is scheduled to configure the master site. After the job runs successfully, the master site is configured, and it has a replication administrator. By default, the user name of the replication administrator is repadmin. You specified the password for this user when you completed the wizard.

3. Add a time column to each master table for latest timestamp conflict resolution.

   1. In SQL*Plus, connect to the database as an administrative user, such as SYSTEM. Alternatively, you can connect as the user who owns the table to which the time column will be added.

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

   2. Use the ALTER TABLE SQL statement to add the time column to the table. For example, the following SQL statement adds the time column to the hr.departments table.

      ALTER TABLE hr.departments ADD (time TIMESTAMP WITH TIME ZONE);
      

4. Create a trigger to update the time column in each master table with the current time when a change occurs.

   Tip:

   Instead of using a trigger to update the time column, an application can populate the time column each time it modifies or inserts a row into a table.

   1. In Oracle Enterprise Manager, log in to the database that contains the master tables as an administrative user, such as SYSTEM.

      If you did not log out of Enterprise Manager after completing Step 3, then you can remain logged in to the master site as an administrative user.

   2. Go to the Database Home page.

   3. Click Schema to open the Schema subpage.

   4. Click Triggers in the Programs section.

   5. On the Triggers page, click Create.

      The Create Trigger page appears, showing the General subpage.

      
      Description of the illustration tdpii_create_trigger_system.gif
      
      

   6. Enter the name of the trigger in the Name field.

   7. Retain the administrative user name in the Schema field.

   8. Enter the following in the Trigger Body field:

      BEGIN
         -- The IF/THEN statement ensures that the trigger does not fire during
         -- materialized view refresh.
         IF (DBMS_REPUTIL.FROM_REMOTE = FALSE AND
             DBMS_SNAPSHOT.I_AM_A_REFRESH = FALSE) 
         THEN
            :NEW.TIME := SYSTIMESTAMP;
         END IF;
      END;
      

   9. Click Event to open the Event subpage.

   10. Ensure that Table is selected in the Trigger On list.

   11. Enter the table name in the form schema.table in the Table (Schema.Table) field, or use the flashlight icon to find the database object.

   12. Ensure that Before is selected for Fire Trigger.

   13. Select Insert and Update of Columns for Event.

       The columns in the table appear.

   14. Select every column in the table except for the new time column.

   15. Click Advanced.

   16. Select Trigger for each row.

   17. Click OK to create the trigger.

   18. Repeat Steps e through q to create a trigger for each master table.

   19. Log out of Enterprise Manager.

   Note:

   You can also use the CREATE TRIGGER SQL statement to create a trigger.

5. Create the master group.

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

   2. Go to the Database Home page.

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

   4. Click Setup in the Advanced Replication section.

      The Advanced Replication: Setup page appears.

   5. Expand Multi-master Replication.

   6. Select Create Master Group.

   7. Click Continue to open the Create Master Group Wizard.

      
      Description of the illustration tdpii_create_mg_gen.gif
      
      

      Complete the pages in the wizard to configure the master group.

      Tip:

      • On the Add Objects page, add the tables that will be master tables for the materialized views.

      • On the Add Master Sites page, do not add any additional master sites.

      When you finish the wizard, an Enterprise Manager job is scheduled to configure the master group.

6. Configure conflict resolution for each master table:

   1. While still logged in as the replication administrator at the master site, go to the Database Home page of the database that contains the new master group.

   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 Master Groups in the Multimaster Replication section.

   5. On the Master Groups page, if the master group status is NORMAL, then click Quiesce. If the status is QUIESCED, then move on to the next step.

   6. On a command line, open SQL*Plus and connect to the master site as the replication administrator.

      For example, if the replication administrator is repadmin and the master site is ii1.example.com, then enter the following:

      sqlplus repadmin@ii1.example.com
      Enter password: password
      

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

   7. In SQL*Plus, run the MAKE_COLUMN_GROUP procedure in the DBMS_REPCAT package to create a column group for a replicated table.

      For example, to create a column group for the hr.departments table, run the following procedure:

      BEGIN
         DBMS_REPCAT.MAKE_COLUMN_GROUP(
            sname                => 'hr',
            oname                => 'departments',
            column_group         => 'dep_time_cg',
            list_of_column_names => 'department_id,
                                     department_name,
                                     manager_id,
                                     location_id,
                                     time');
      END;
      /
      

      Include all of the table columns in the list_of_columns parameter.

      See Oracle Database Advanced Replication Management API Reference for more information about the MAKE_COLUMN_GROUP procedure.

   8. In SQL*Plus, run the ADD_UPDATE_RESOLUTION procedure in the DBMS_REPCAT package to specify LATEST TIME conflict resolution for the table.

      For example, to specify LATEST TIME conflict resolution using the column group you created in Step g for the hr.departments table, run the following procedure:

      BEGIN
         DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
            sname                 => 'hr',
            oname                 => 'departments',
            column_group          => 'dep_time_cg',
            sequence_no           => 1,
            method                => 'LATEST TIMESTAMP',
            parameter_column_name => 'time');
      END;
      /
      

      Specify the column group you created in Step g for the column_group parameter, and specify the time column for the parameter_column_name column.

      See Oracle Database Advanced Replication Management API Reference for more information about the ADD_UPDATE_RESOLUTION procedure.

   9. Repeat Steps g through h for each table in the master group.

   10. In Enterprise Manager, log in to the master site as the replication administrator. By default, the user name of the replication administrator is repadmin.

   11. Go to the Database Home page.

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

   13. Click Manage in the Advanced Replication section.

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

   14. Click the number associated with Master Groups in the Multimaster Replication section.

   15. On the Master Groups page, select the master group.

   16. Click Edit to open the General subpage of the Edit Master Group page.

   17. Click Objects to open the Objects subpage.

   18. Ensure that Generate Replication Support is selected for each object to which you added conflict resolution.

   19. Click Apply to save your changes.

   20. Click Master Groups at the top of the page to return to the Master groups page.

   21. Click Resume for the master group.

7. Create the materialized view group.

   1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The materialized view site is the database in which you want to create materialized views. 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 Setup in the Advanced Replication section.

   5. On the Advanced Replication: Setup page, expand Updateable Materialized View Replication.

   6. Select Create Materialized View Group.

   7. Click Continue to open the Create Materialized View Group Wizard.

      
      Description of the illustration tdpii_create_mvg_gen.gif
      
      

      Complete the pages in the wizard to configure the materialized group.

      Tip:

      For each materialized view on the Customize Materialized Views page:

      • Select Min. Communications for each table that uses conflict resolution at the master site. This option reduces the amount of data required to support conflict resolution mechanisms.

      • Select Updatable for each materialized view that you want to be updatable.

      • Select Fast Refresh for each materialized view on which you want to perform fast refreshes.

      When you finish the wizard, an Enterprise Manager job is scheduled to configure the materialized view group.

8. Create a trigger to update the time column in each materialized view with the current time when a change occurs. The trigger is required for latest timestamp conflict resolution.

   1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The materialized view site is the database in which you want to create materialized views. The default user name for the materialized view administrator is mvadmin.

   2. Complete Steps 4b through 4q for each materialized view.

9. If you did not specify an existing refresh group during materialized view group configuration, then create a refresh group and add the materialized views to it so that they are consistent to a single point in time when they are refreshed. See "Configuring a Refresh Group".