Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpii\tdpii_repcont025.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>Tutorial: Configuring Latest Time Conflict Resolution for a Table</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="Tutorial: Configuring Latest Time Conflict Resolution for a Table" /> <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_repcont024.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpii_adcont.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_repcont024.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_adcont.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CIHIACBB" name="CIHIACBB"></a><a id="TDPII136" name="TDPII136"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1>Tutorial: Configuring Latest Time Conflict Resolution for a Table<a id="sthref267" name="sthref267"></a><a id="sthref268" name="sthref268"></a><a id="sthref269" name="sthref269"></a><a id="sthref270" name="sthref270"></a></h1> <a name="BEGIN" id="BEGIN"></a> <p>Conflict resolution automatically resolves conflicts in a replication environment. See <a href="tdpii_repcont010.htm#BABGAACC">"About Conflicts and Conflict Resolution"</a> for more information about conflict resolution.</p> <p>The most common way to resolve update conflicts is to keep the change with the most recent time stamp and discard the older change. With this method, when a conflict is detected during apply, the apply process applies the change if the time-stamp column for the change is more recent than the corresponding row in the table. If the time-stamp column in the table is more recent, then the apply process discards the change.</p> <p>The example in this topic configures latest time conflict resolution for the <code>hr.departments</code> table by completing the following actions:</p> <ul> <li> <p>Adds a <code>time</code> column of the <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> data type to the table</p> </li> <li> <p>Configures a trigger to update the time column in a row with the current time when the row is changed</p> </li> <li> <p>Adds supplemental logging for the columns in the table</p> </li> <li> <p>Runs the <code>SET_UPDATE_CONFLICT_HANDLER</code> procedure in the <code>DBMS_APPLY_ADM</code> package to configure conflict resolution for the table</p> </li> </ul> <p>You can use the steps in this topic to configure conflict resolution for any table. To do so, substitute your schema name for <code>hr</code> and your table name for <code>departments</code>. Also, substitute the columns in your table for the columns in the <code>hr.departments</code> table when you run the <code>SET_UPDATE_CONFLICT_HANDLER</code> procedure.</p> <p class="orderedlisttitle">To configure latest time conflict resolution for the hr.departments table: </p> <ol> <li> <p><a id="sthref272" name="sthref272"></a><a id="sthref273" name="sthref273"></a>Add a <code>time</code> column to the table.</p> <ol> <li> <p>In SQL*Plus, connect to the database as an administrative user, such as the Oracle Streams administrator or <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> <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. In this example, run the following statement to add 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> <p><a id="sthref274" name="sthref274"></a><a id="sthref275" name="sthref275"></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> <p>In Oracle Enterprise Manager, log in to the database as an administrative user, such as the Oracle Streams administrator or <code>SYSTEM</code>.</p> </li> <li> <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> <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. In this example, enter <code>insert_departments_time</code>.</p> </li> <li> <p>Enter the schema that owns the table in the Schema field. In this example, enter <code>hr</code> 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 -- Consider time synchronization problems. The previous update to this -- row might have originated from a site with a clock time ahead of -- the local clock time. IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; 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. In this example, enter <code>hr.departments</code>.</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> to open the Advanced subpage.</p> </li> <li> <p>Select <span class="bold">Trigger for each row</span>.</p> </li> <li> <p>Click <span class="bold">OK</span> to create the trigger.</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>In SQL*Plus, connect to the database as the Oracle Streams administrator.</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> <p>Add supplemental logging for the columns in the table:</p> <pre xml:space="preserve" class="oac_no_warn"> ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; </pre> <p>Supplemental logging is required for conflict resolution during apply.</p> </li> <li><a id="CIHHCHEI" name="CIHHCHEI"></a> <p><a id="sthref276" name="sthref276"></a><a id="sthref277" name="sthref277"></a>Run the <code>SET_UPDATE_CONFLICT_HANDLER</code> procedure to configure latest time conflict resolution for the table.</p> <p>For example, run the following procedure to configure latest time conflict resolution for the <code>hr.departments</code> table:</p> <pre xml:space="preserve" class="oac_no_warn"> DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'department_id'; cols(2) := 'department_name'; cols(3) := 'manager_id'; cols(4) := 'location_id'; cols(5) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.departments', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / </pre> <p>Include all of the columns in the table in the <code>cols</code> column list.</p> </li> <li> <p>Repeat these steps for any tables that require conflict resolution in your replication environment. You might need to configure conflict resolution for the tables at several databases.</p> <p>If you are completing an example that configures or extends a replication environment, then configure latest time conflict resolution for the appropriate tables:</p> <ul> <li> <p>For <a href="tdpii_repcont021.htm#CIHDBCGD">"Tutorial: Configuring Two-Database Replication with Local Capture Processes"</a>, configure conflict resolution for all of the tables in the <code>hr</code> schema at the <code>db1.example.com</code> and <code>db2.example.com</code> databases. This schema includes the <code>countries</code>, <code>departments</code>, <code>employees</code>, <code>jobs</code>, <code>job_history</code>, <code>locations</code>, and <code>regions</code> tables.</p> </li> <li> <p>For <a href="tdpii_repcont023.htm#BABBIBCD">"Tutorial: Configuring Hub-and-Spoke Replication with Local Capture Processes"</a>, configure conflict resolution for all of the tables in the <code>hr</code> schema at the <code>hub1.example.com</code>, <code>spoke1.example.com</code>, and <code>spoke2.example.com</code> databases. This schema includes the <code>countries</code>, <code>departments</code>, <code>employees</code>, <code>jobs</code>, <code>job_history</code>, <code>locations</code>, and <code>regions</code> tables.</p> </li> <li> <p>For <a href="tdpii_repcont024.htm#BABDEBBA">"Tutorial: Configuring Two-Database Replication with Synchronous Captures"</a>, configure conflict resolution for the <code>hr.departments</code> and <code>hr.employees</code> tables at the <code>sync1.example.com</code> and <code>sync2.example.com</code> databases.</p> </li> <li> <p>For <a href="tdpii_repextend002.htm#CIHCDDIJ">"Tutorial: Adding Database Objects to a Replication Environment"</a>, configure conflict resolution for the <code>oe.orders</code> and <code>oe.order_items</code> tables at the <code>hub.example.com</code>, <code>spoke1.example.com</code>, and <code>spoke2.example.com</code> databases.</p> </li> <li> <p>For <a href="tdpii_repextend003.htm#CIHIDHEH">"Tutorial: Adding Databases to a Replication Environment"</a>, configure conflict resolution for all of the tables in the <code>hr</code> schema at the <code>spoke3.example.com</code> database. This schema includes the <code>countries</code>, <code>departments</code>, <code>employees</code>, <code>jobs</code>, <code>job_history</code>, <code>locations</code>, and <code>regions</code> tables.</p> </li> </ul> </li> </ol> <p>If you were directed to this section from an example, then go back to the example now.</p> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdpii_repcont010.htm#BABGAACC">About Conflicts and Conflict Resolution</a></p> <p><a href="tdpii_adcont025.htm#CHDCBHGC">Displaying the Configured Update Conflict Handlers</a></p> <p><a href="tdpii_repcont019.htm#BABDHIIH">Preparing for Oracle Streams Replication</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_repcont024.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_adcont.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