Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpii\tdpii_reppit016.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Configuring Replication of Read/Write Data Using Materialized Views</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-06-04T17:1:2Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Configuring Replication of Read/Write Data Using Materialized Views" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10703-01" /> <link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="prev" href="tdpii_reppit015.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpii_reppit017.htm" title="Next" type="text/html" /> <script src="./callback.js" type="text/javascript"></script> <noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript> </head> <body> <div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="tdpii_reppit015.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_reppit017.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="BEHBDEHH" name="BEHBDEHH"></a><a id="TDPII060" name="TDPII060"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1>Configuring Replication of Read/Write Data Using Materialized Views<a id="sthref456" name="sthref456"></a><a id="sthref457" name="sthref457"></a><a id="sthref458" name="sthref458"></a><a id="sthref459" name="sthref459"></a></h1> <a name="BEGIN" id="BEGIN"></a> <p>This topic provides instructions for configuring read/write data replication using materialized views. Specifically, this example configures:</p> <ul> <li> <p>A master group at a master site that contains master tables. See <a href="tdpii_reppit013.htm#BEHIGFCF">"About Replication Groups and Updatable Materialized Views"</a>.</p> </li> <li> <p>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 <a href="tdpii_reppit013.htm#BEHIGFCF">"About Replication Groups and Updatable Materialized Views"</a>.</p> </li> <li> <p>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 <a href="tdpii_reppit015.htm#CHDJHGAD">"About Conflicts and Updatable Materialized Views"</a>.</p> <p>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.</p> </li> </ul> <p class="orderedlisttitle">To configure updatable materialized views: </p> <ol> <li> <p>Complete the actions described in the following topics:</p> <ul> <li> <p><a href="tdpii_reppit005.htm#CHDIHIFJ">"Preparing for Materialized View Replication"</a></p> </li> <li> <p><a href="tdpii_reppit006.htm#CHDBCGBG">"Configuring Materialized View Sites"</a></p> </li> <li> <p><a href="tdpii_reppit007.htm#CHDCDIAG">"Configuring Materialized View Logs at the Master Site"</a> 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.</p> </li> </ul> </li> <li><a id="CHDFAIDH" name="CHDFAIDH"></a> <p><a id="sthref461" name="sthref461"></a><a id="sthref462" name="sthref462"></a>Configure the master site. The master site contains the master tables on which the updatable materialized views are based.</p> <ol> <li> <p>In Oracle Enterprise Manager, log in to the database that will be the master site as an administrative user, such as <code>SYSTEM</code>.</p> </li> <li> <p>Go to the Database Home page.</p> </li> <li> <p>Click <span class="bold">Data Movement</span> to open the Data Movement subpage.</p> </li> <li> <p>Click <span class="bold">Setup</span> in the Advanced Replication section.</p> </li> <li> <p>On the Advanced Replication: Setup page, expand <span class="bold">Updateable Materialized View Replication</span>.</p> </li> <li> <p>Select <span class="bold">Configure Master Sites for Replication</span>.</p> </li> <li> <p>Click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('ADVRP_CONFIG_MASTER');"><span class="bold">Continue</span></a> to open the Configure Master Sites for Replication Wizard.</p> <p>Complete the pages in the wizard to configure the master site. For more information about using the wizard, click <span class="bold">Help</span> for each page of the wizard.</p> <p>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 <code>repadmin</code>. You specified the password for this user when you completed the wizard.</p> </li> </ol> </li> <li><a id="CHDEDIJI" name="CHDEDIJI"></a> <p><a id="sthref463" name="sthref463"></a><a id="sthref464" name="sthref464"></a>Add a <code>time</code> column to each master table for latest timestamp conflict resolution.</p> <ol> <li> <p>In SQL*Plus, connect to the database as an administrative user, such as <code>SYSTEM</code>. Alternatively, you can connect as the user who owns the table to which the time column will be added.</p> <p>See <a href="topicid:ADMQS0361">Starting SQL*Plus and Connecting to the Database</a> for more information about starting SQL*Plus.</p> </li> <li><a id="CIHJDEBB" name="CIHJDEBB"></a> <p>Use the <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF01001','newWindow').focus()"><code>ALTER</code> <code>TABLE</code></a> SQL statement to add the <code>time</code> column to the table. For example, the following SQL statement adds the <code>time</code> column to the <code>hr.departments</code> table.</p> <pre xml:space="preserve" class="oac_no_warn"> ALTER TABLE hr.departments ADD (time TIMESTAMP WITH TIME ZONE); </pre></li> </ol> </li> <li><a id="CHDDJJDH" name="CHDDJJDH"></a> <p><a id="sthref465" name="sthref465"></a><a id="sthref466" name="sthref466"></a>Create a trigger to update the <code>time</code> column in each master table with the current time when a change occurs.</p> <div class="helpinfonote"> <p><span class="bold">Tip: </span>Instead of using a trigger to update the <code>time</code> column, an application can populate the <code>time</code> column each time it modifies or inserts a row into a table.</p> </div> <ol> <li><a id="CHDCDACI" name="CHDCDACI"></a> <p>In Oracle Enterprise Manager, log in to the database that contains the master tables as an administrative user, such as <code>SYSTEM</code>.</p> <p>If you did not log out of Enterprise Manager after completing Step <a href="#CHDEDIJI">3</a>, then you can remain logged in to the master site as an administrative user.</p> </li> <li><a id="CHDDGEAC" name="CHDDGEAC"></a> <p>Go to the Database Home page.</p> </li> <li> <p>Click <span class="bold">Schema</span> to open the Schema subpage.</p> </li> <li> <p>Click <span class="bold">Triggers</span> in the Programs section.</p> </li> <li><a id="CHDEICCC" name="CHDEICCC"></a> <p>On the Triggers page, click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('TRIGGER_CREATE');"><span class="bold">Create</span></a>.</p> <p>The Create Trigger page appears, showing the General subpage.</p> </li> <li> <p>Enter the name of the trigger in the Name field.</p> </li> <li> <p>Retain the administrative user name in the Schema field.</p> </li> <li> <p>Enter the following in the Trigger Body field:</p> <pre xml:space="preserve" class="oac_no_warn"> 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; </pre></li> <li> <p>Click <span class="bold">Event</span> to open the Event subpage.</p> </li> <li> <p>Ensure that <span class="bold">Table</span> is selected in the Trigger On list.</p> </li> <li> <p>Enter the table name in the form <code><span class="codeinlineitalic">schema.table</span></code> in the Table (Schema.Table) field, or use the flashlight icon to find the database object.</p> </li> <li> <p>Ensure that <span class="bold">Before</span> is selected for Fire Trigger.</p> </li> <li> <p>Select <span class="bold">Insert</span> and <span class="bold">Update of Columns</span> for Event.</p> <p>The columns in the table appear.</p> </li> <li> <p>Select every column in the table except for the new <code>time</code> column.</p> </li> <li> <p>Click <span class="bold">Advanced</span>.</p> </li> <li> <p>Select <span class="bold">Trigger for each row</span>.</p> </li> <li><a id="CHDFDJFH" name="CHDFDJFH"></a> <p>Click <span class="bold">OK</span> to create the trigger.</p> </li> <li> <p>Repeat Steps <a href="#CHDEICCC">e</a> through <a href="#CHDFDJFH">q</a> to create a trigger for each master table.</p> </li> <li> <p>Log out of Enterprise Manager.</p> </li> </ol> <div class="helpinfonote"> <p><span class="bold">Note: </span>You can also use the <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF01405','newWindow').focus()"><code>CREATE</code> <code>TRIGGER</code></a> SQL statement to create a trigger.</p> </div> </li> <li> <p><a id="sthref467" name="sthref467"></a><a id="sthref468" name="sthref468"></a>Create the master group.</p> <ol> <li> <p>In Enterprise Manager, log in to the master site as the replication administrator. By default, the user name of the replication administrator is <code>repadmin</code>.</p> </li> <li> <p>Go to the Database Home page.</p> </li> <li> <p>Click <span class="bold">Data Movement</span> to open the Data Movement subpage.</p> </li> <li> <p>Click <span class="bold">Setup</span> in the Advanced Replication section.</p> <p>The Advanced Replication: Setup page appears.</p> </li> <li> <p>Expand <span class="bold">Multi-master Replication</span>.</p> </li> <li> <p>Select <span class="bold">Create Master Group</span>.</p> </li> <li> <p>Click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('MASTER_GROUP_CREATE');"><span class="bold">Continue</span></a> to open the Create Master Group Wizard.</p> <p>Complete the pages in the wizard to configure the master group. For more information about using the wizard, click <span class="bold">Help</span> for each page of the wizard.</p> <div class="helpinfonote"> <p><span class="bold">Tip: </span><ul> <li> <p>On the Add Objects page, add the tables that will be master tables for the materialized views.</p> </li> <li> <p>On the Add Master Sites page, do not add any additional master sites.</p> </li> </ul> </p> </div> <p>When you finish the wizard, an Enterprise Manager job is scheduled to configure the master group.</p> </li> </ol> </li> <li><a id="CHDBBDHI" name="CHDBBDHI"></a> <p><a id="sthref469" name="sthref469"></a><a id="sthref470" name="sthref470"></a>Configure conflict resolution for each master table:</p> <ol> <li><a id="CHDGFCHI" name="CHDGFCHI"></a> <p><a id="sthref471" name="sthref471"></a><a id="sthref472" name="sthref472"></a>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.</p> </li> <li><a id="CHDIJAGD" name="CHDIJAGD"></a> <p>Click <span class="bold">Data Movement</span> to open the Data Movement subpage.</p> </li> <li> <p>Click <span class="bold">Manage</span> in the Advanced Replication section.</p> <p>The Advanced Replication: Administration page appears, showing the Overview subpage.</p> </li> <li> <p>Click the number associated with <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('MASTER_GROUPS');">Master Groups</a> in the Multimaster Replication section.</p> </li> <li> <p>On the Master Groups page, if the master group status is <code>NORMAL</code>, then click <span class="bold">Quiesce</span>. If the status is <code>QUIESCED</code>, then move on to the next step.</p> </li> <li> <p>On a command line, open SQL*Plus and connect to the master site as the replication administrator.</p> <p>For example, if the replication administrator is <code>repadmin</code> and the master site is <code>ii1.example.com</code>, then enter the following:</p> <pre xml:space="preserve" class="oac_no_warn"> sqlplus repadmin@ii1.example.com Enter password: <span class="italic">password</span> </pre> <p>See <a href="topicid:ADMQS0361">Starting SQL*Plus and Connecting to the Database</a> for more information about starting SQL*Plus.</p> </li> <li><a id="CHDGDDCC" name="CHDGDDCC"></a> <p><a id="sthref473" name="sthref473"></a><a id="sthref474" name="sthref474"></a>In SQL*Plus, run the <code>MAKE_COLUMN_GROUP</code> procedure in the <code>DBMS_REPCAT</code> package to create a column group for a replicated table.</p> <p>For example, to create a column group for the <code>hr.departments</code> table, run the following procedure:</p> <pre xml:space="preserve" class="oac_no_warn"> 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; / </pre> <p>Include all of the table columns in the <code>list_of_columns</code> parameter.</p> <p>See <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=REPMA019','newWindow').focus()"><span class="italic">Oracle Database Advanced Replication Management API Reference</span></a> for more information about the <code>MAKE_COLUMN_GROUP</code> procedure.</p> </li> <li><a id="CHDIBICI" name="CHDIBICI"></a> <p><a id="sthref475" name="sthref475"></a><a id="sthref476" name="sthref476"></a>In SQL*Plus, run the <code>ADD_UPDATE_RESOLUTION</code> procedure in the <code>DBMS_REPCAT</code> package to specify <code>LATEST</code> <code>TIME</code> conflict resolution for the table.</p> <p>For example, to specify <code>LATEST</code> <code>TIME</code> conflict resolution using the column group you created in Step <a href="#CHDGDDCC">g</a> for the <code>hr.departments</code> table, run the following procedure:</p> <pre xml:space="preserve" class="oac_no_warn"> 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; / </pre> <p>Specify the column group you created in Step <a href="#CHDGDDCC">g</a> for the <code>column_group</code> parameter, and specify the time column for the <code>parameter_column_name</code> column.</p> <p>See <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=REPMA019','newWindow').focus()"><span class="italic">Oracle Database Advanced Replication Management API Reference</span></a> for more information about the <code>ADD_UPDATE_RESOLUTION</code> procedure.</p> </li> <li> <p>Repeat Steps <a href="#CHDGDDCC">g</a> through <a href="#CHDIBICI">h</a> for each table in the master group.</p> </li> <li> <p>In Enterprise Manager, log in to the master site as the replication administrator. By default, the user name of the replication administrator is <code>repadmin</code>.</p> </li> <li> <p><a id="sthref477" name="sthref477"></a><a id="sthref478" name="sthref478"></a><a id="sthref479" name="sthref479"></a>Go to the Database Home page.</p> </li> <li> <p>Click <span class="bold">Data Movement</span> to open the Data Movement subpage.</p> </li> <li> <p>Click <span class="bold">Manage</span> in the Advanced Replication section.</p> <p>The Advanced Replication: Administration page appears, showing the Overview subpage.</p> </li> <li> <p>Click the number associated with <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('MASTER_GROUPS');">Master Groups</a> in the Multimaster Replication section.</p> </li> <li> <p>Select the master group.</p> </li> <li> <p>Click <span class="bold">Edit</span> to open the General subpage of the Edit Master Group page.</p> </li> <li> <p>Click <span class="bold">Objects</span> to open the Objects subpage.</p> </li> <li> <p>Ensure that <span class="bold">Generate Replication Support</span> is selected for each object to which you added conflict resolution.</p> </li> <li><a id="CHDIGHEB" name="CHDIGHEB"></a> <p>Click <span class="bold">Apply</span> to save your changes.</p> </li> <li> <p>Click <span class="bold">Master Groups</span> at the top of the page to return to the Master groups page.</p> </li> <li> <p>Click <span class="bold">Resume</span> for the master group.</p> </li> </ol> </li> <li> <p><a id="sthref480" name="sthref480"></a><a id="sthref481" name="sthref481"></a>Create the materialized view group.</p> <ol> <li> <p>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 <code>mvadmin</code>.</p> </li> <li> <p>Go to the Database Home page.</p> </li> <li> <p>Click <span class="bold">Data Movement</span> to open the Data Movement subpage.</p> </li> <li> <p>Click <span class="bold">Setup</span> in the Advanced Replication section.</p> </li> <li> <p>On the Advanced Replication: Setup page, expand <span class="bold">Updateable Materialized View Replication</span>.</p> </li> <li> <p>Select <span class="bold">Create Materialized View Group</span>.</p> </li> <li> <p>Click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('MV_GROUP_CREATE');"><span class="bold">Continue</span></a> to open the Create Materialized View Group Wizard.</p> <p>Complete the pages in the wizard to configure the materialized group. For more information about using the wizard, click <span class="bold">Help</span> for each page of the wizard.</p> <div class="helpinfonote"> <p><span class="bold">Tip: </span>For each materialized view on the Customize Materialized Views page: <ul> <li> <p>Select <span class="bold">Min. Communications</span> for each table that uses conflict resolution at the master site. This option reduces the amount of data required to support conflict resolution mechanisms.</p> </li> <li> <p>Select <span class="bold">Updatable</span> for each materialized view that you want to be updatable.</p> </li> <li> <p>Select <span class="bold">Fast Refresh</span> for each materialized view on which you want to perform fast refreshes.</p> </li> </ul> </p> </div> <p>When you finish the wizard, an Enterprise Manager job is scheduled to configure the materialized view group.</p> </li> </ol> </li> <li> <p>Create a trigger to update the <code>time</code> column in each materialized view with the current time when a change occurs. The trigger is required for latest timestamp conflict resolution.</p> <ol> <li> <p>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 <code>mvadmin</code>.</p> </li> <li> <p>Complete Steps <a href="#CHDDJJDH">4</a><a href="#CHDDGEAC">b</a> through <a href="#CHDDJJDH">4</a><a href="#CHDFDJFH">q</a> for each materialized view.</p> </li> </ol> </li> <li> <p>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 <a href="tdpii_reppit017.htm#CHDJEFDH">"Configuring a Refresh Group"</a>.</p> </li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdpii_intro2ii005.htm#CFHBIEHG">When to Replicate Data with Materialized Views</a></p> <p><a href="tdpii_reppit012.htm#BEHCEEHJ">About Replicating Read/Write Data Using Materialized Views</a></p> <p><a href="tdpii_reppit013.htm#BEHIGFCF">About Replication Groups and Updatable Materialized Views</a></p> <p><a href="tdpii_reppit015.htm#CHDJHGAD">About Conflicts and Updatable Materialized Views</a></p> <p><a href="tdpii_adpit.htm#BABFHCBC">Administering a Materialized View Replication Environment</a></p> </div> </div> <!-- class="sect2" --> <!-- class="sect1" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2007, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="tdpii_reppit015.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_reppit017.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de