Tutorial: Configuring Latest Time Conflict Resolution for a Table

Conflict resolution automatically resolves conflicts in a replication environment. See "About Conflicts and Conflict Resolution" for more information about conflict resolution.

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.

The example in this topic configures latest time conflict resolution for the hr.departments table by completing the following actions:

• Adds a time column of the TIMESTAMP WITH TIME ZONE data type to the table

• Configures a trigger to update the time column in a row with the current time when the row is changed

• Adds supplemental logging for the columns in the table

• Runs the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package to configure conflict resolution for the table

You can use the steps in this topic to configure conflict resolution for any table. To do so, substitute your schema name for hr and your table name for departments. Also, substitute the columns in your table for the columns in the hr.departments table when you run the SET_UPDATE_CONFLICT_HANDLER procedure.

To configure latest time conflict resolution for the hr.departments table: 

1. Add a time column to the table.

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

      See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

   2. Use the ALTER TABLE SQL statement to add the time column to the table. In this example, run the following statement to add the time column to the hr.departments table.

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

2. 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 as an administrative user, such as the Oracle Streams administrator or SYSTEM.

   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.

   6. Enter the name of the trigger in the Name field. In this example, enter insert_departments_time.

   7. Enter the schema that owns the table in the Schema field. In this example, enter hr in the Schema field.

   8. Enter the following in the Trigger Body field:

      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;
      

   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. In this example, enter hr.departments.

   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 to open the Advanced subpage.

   16. Select Trigger for each row.

   17. Click OK to create the trigger.

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

3. In SQL*Plus, connect to the database as the Oracle Streams administrator.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

4. Add supplemental logging for the columns in the table:

   ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   Supplemental logging is required for conflict resolution during apply.

5. Run the SET_UPDATE_CONFLICT_HANDLER procedure to configure latest time conflict resolution for the table.

   For example, run the following procedure to configure latest time conflict resolution for the hr.departments table:

   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;
   /
   

   Include all of the columns in the table in the cols column list.

6. 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.

   If you are completing an example that configures or extends a replication environment, then configure latest time conflict resolution for the appropriate tables:

   • For "Tutorial: Configuring Two-Database Replication with Local Capture Processes", configure conflict resolution for all of the tables in the hr schema at the db1.example.com and db2.example.com databases. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables.

   • For "Tutorial: Configuring Hub-and-Spoke Replication with Local Capture Processes", configure conflict resolution for all of the tables in the hr schema at the hub1.example.com, spoke1.example.com, and spoke2.example.com databases. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables.

   • For "Tutorial: Configuring Two-Database Replication with Synchronous Captures", configure conflict resolution for the hr.departments and hr.employees tables at the sync1.example.com and sync2.example.com databases.

   • For "Tutorial: Adding Database Objects to a Replication Environment", configure conflict resolution for the oe.orders and oe.order_items tables at the hub.example.com, spoke1.example.com, and spoke2.example.com databases.

   • For "Tutorial: Adding Databases to a Replication Environment", configure conflict resolution for all of the tables in the hr schema at the spoke3.example.com database. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables.

If you were directed to this section from an example, then go back to the example now.