5 Conflict Resolution Concepts and Architecture

Examples of Conflict Detection and Resolution

To better understand how to design a replicated database system with conflicts in mind, consider the following environments where conflict detection and resolution is feasible in some cases but not possible in others:

• Conflict resolution is often not possible in reservation systems where multiple bookings for the same item are not allowed. For example, when reserving specific seats for a concert, different agents accessing different replicas of the reservation system cannot book the same seat for multiple customers because there is no way to resolve such a conflict.

• Conflict resolution is often possible in customer management systems. For example, salespeople can maintain customer address information at different databases in a replication environment. Should a conflict arise, the system can resolve the conflicting updates by applying the most recent update to a record.

Update Conflicts

An update conflict occurs when the replication of an update to a row conflicts with another update to the same row. Update conflicts can happen when two transactions originating from different sites update the same row at nearly the same time.

Uniqueness Conflicts

A uniqueness conflict occurs when the replication of a row attempts to violate entity integrity, such as a PRIMARY KEY or UNIQUE constraint. For example, consider what happens when two transactions originate from two different sites, each inserting a row into a respective table replica with the same primary key value. In this case, replication of the transactions causes a uniqueness conflict.

Delete Conflicts

A delete conflict occurs when two transactions originate from different sites, with one transaction deleting a row and another transaction updating or deleting the same row, because in this case the row does not exist to be either updated or deleted.

How Oracle Detects Different Types of Conflicts

The receiving master site or master materialized view site in a replication system detects update, uniqueness, and delete conflicts as follows:

• The receiving site detects an update conflict if there is any difference between the old values of the replicated row (the values before the modification) and the current values of the same row at the receiving site.

• The receiving site detects a uniqueness conflict if a uniqueness constraint violation occurs during an INSERT or UPDATE of a replicated row.

• The receiving site detects a delete conflict if it cannot find a row for an UPDATE or DELETE statement because the primary key of the row does not exist.

Identifying Rows During Conflict Detection

To detect replication conflicts accurately, Oracle must be able to uniquely identify and match corresponding rows at different sites during data replication. Typically, Advanced Replication uses the primary key of a table to uniquely identify rows in the table. When a table does not have a primary key, you must designate an alternate key—a column or set of columns that Oracle can use to uniquely identify rows in the table during data replication.

Multitier Materialized Views and Conflict Resolution

When you have a master table and an updatable materialized view based on that master table, a refresh of the materialized view pushes its changes to the master site, where the master site handles any conflicts resulting from the push with its configured conflict resolution methods. Then, the materialized view pulls changes at the master down when the materialized view completes the refresh. The refresh is always initiated at the materialized view site.

Similarly, the master materialized view of an updatable materialized view behaves in the same way as a master table. However, to handle conflicts resulting from a push from a materialized view, the master materialized view uses conflict resolution methods that it has pulled from its master. Here, the master can either be a master table at a master site or a master materialized view at another materialized view site. Conflict resolution methods cannot be configured directly at a materialized view site. Instead, the conflict resolution methods are pulled down from the immediate master automatically when you create an updatable materialized view and when you generate replication support for a materialized view. A read-only materialized view does not pull down conflict resolution methods from its master.

For example, suppose a level 3 materialized view pushes its changes to its level 2 master materialized view. This push might cause a conflict at the level 2 materialized view. To handle the conflict, the level 2 materialized view uses the conflict resolution methods that it previously pulled from its level 1 master materialized view. Similarly, the level 1 materialized view handles conflicts with the conflict resolution methods that it previously pulled from its master site. Figure 5-1 illustrates this configuration.

Figure 5-1 Conflict Resolution and Multitier Materialized Views

Description of "Figure 5-1 Conflict Resolution and Multitier Materialized Views"

Notice that each updatable materialized view pulls-down conflict resolution methods from its master, even if the updatable materialized view does not have any materialized views based on it. Notice also that a read-only materialized view does not pull down conflict resolution methods from its master.

If you plan to change the conflict resolution methods for a master table in an environment with multitier materialized views, then complete the following general procedure:

1. If you are modifying either column groups or key columns and you are using minimum communication for any of the updatable materialized views based on the master table, then complete the following sub-steps:

   1. Refresh the materialized views that are the farthest removed from the master table you are altering. By refreshing, you push all the deferred transactions from each materialized view to its master. For example, if you have three levels of materialized views, then refresh the level 3 materialized views.

   2. Stop all data manipulation language (DML) changes at the materialized views you refreshed in Step a.

   3. Repeat Step a and Step b for each materialized view level until you complete these steps for the level 1 materialized views, which are based on a master table at a master site.

2. If necessary, then quiesce the master group.

3. Change the conflict resolution configuration at the master definition site.

4. Regenerate replication support for the affected objects at the master definition site using either the GENERATE_REPLICATION_SUPPORT procedure in the DBMS_REPCAT package or the Advanced Replication interface in Oracle Enterprise Manager.

5. If you quiesced the master group in Step 2, then resume replication activity for the master group.

6. Regenerate replication support for the materialized views with the smallest level number that have not yet regenerated replication support. The current conflict resolution methods are pulled down from the immediate master during regeneration. The first time you complete this step, it is for the level 1 materialized views, the second time for the level 2 materialized views, and so on. You regenerate replication support for a materialized view using either the GENERATE_MVIEW_SUPPORT procedure in the DBMS_REPCAT package or the Advanced Replication interface in Oracle Enterprise Manager.

7. If you completed the sub-steps in Step 1, then allow DML changes at the materialized views with the smallest level number that do not currently allow DML changes. The first time you complete this step, it is for the level 1 materialized views, the second time for the level 2 materialized views, and so on.

8. Repeat Step 6 and Step 7 for each level of materialized views until you complete these steps for the materialized views that are farthest removed from the master table. For example, if you have three levels of materialized views, then the last time you complete these steps it is for the level 3 materialized views.

This regeneration of replication support is not performed automatically. In an environment where different database administrators administer master sites and materialized view sites, the database administrator at the master sites must notify the database administrators at all of the affected materialized view sites of the changes in conflict resolution methods. Then, it is the responsibility of all of the database administrators to coordinate the previous procedure.

Column Subsetting of Updatable Materialized Views and Conflict Resolution

Column subsetting enables you to exclude columns in master tables from materialized views by identifying specific columns in the SELECT statement during materialized view creation. If only a subset of the columns in a column group are included in an updatable materialized view, then do not create a conflict resolution method on this column group, unless the conflict resolution method is either discard or site priority. If the conflict resolution method is site priority, then column subsetting should only be used in single master replication environments where the master site has a higher priority number than the materialized view site.

For any type of conflict resolution method other than discard and the variant of site priority described previously, the updatable materialized view sends information about changes for some of the columns in the column group but not others, causing Oracle to return an error when it tries to apply the conflict resolution method. Because discard and this variant of site priority do not depend on column information, you can use these methods along with column subsetting.

For example, suppose the employees master table has a column group that contains the employee_id, manager_id, department_id, and timestamp columns. You define a latest time stamp conflict resolution method on the column group at the master site. Then, you create an updatable materialized view called employees_mv based on the employees master table, but you use column subsetting to exclude the department_id column from the materialized view. When an update is made to the employee_id or manager_id column at the materialized view, information about these changes are sent to the master site during a subsequent refresh. An error is returned at the master site because no information about the remaining column in the column group, department_id, is found when Oracle tries to apply the conflict resolution method.

Keep this in mind if you are using multitier materialized views. Because the conflict resolution methods are pulled down from the master site to a master materialized view, the same rules apply to master materialized view sites and updatable materialized views based on them.

Nested Tables and Conflict Resolution

For each nested table column, Oracle creates a hidden column in the table called the NESTED_TABLE_ID column. Oracle also creates a separate table called a storage table to store the elements of the nested table. The storage table stores a row for each element of the nested table for each parent table row. The storage table also contains a NESTED_TABLE_ID column, which corresponds to the parent table's NESTED_TABLE_ID column and is used to identify the elements of the nested table for a particular parent row. Nested table columns require special consideration in Advanced Replication.The underlying storage tables require as much consideration for conflict resolution as the parent table, and there are additional issues to consider.

Replication handles data manipulation language (DML) statements on nested tables as separate DML statements on the parent table and storage table. When DML statements are executed on nested table columns, the actions performed by Oracle depend on the type of DML statement. The following table shows the actions performed by Oracle for each type of DML statement.

CREATE TYPE Course AS OBJECT (
    course_no NUMBER(4),
    title VARCHAR2(35),
    credits NUMBER(1));
/

CREATE TYPE CourseList AS TABLE OF Course;
/

CREATE TABLE department (
      name VARCHAR2(20) primary key,
      director VARCHAR2(20),
      office VARCHAR2(20),
      courses CourseList)
      NESTED TABLE courses STORE AS courses_tab(
        (PRIMARY KEY(nested_table_id,course_no)));

INSERT INTO department
      VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
      CourseList(Course(1000, 'General Psychology', 5),
      Course(2100, 'Experimental Psychology', 4),
      Course(2200, 'Psychological Tests', 3),
      Course(2250, 'Behavior Modification', 4),
      Course(3540, 'Groups and Organizations', 3),
      Course(3552, 'Human Factors in Business', 4),
      Course(4210, 'Theories of Learning', 4)));

UPDATE department SET courses = CourseList(
      Course(1000, 'General Psychology', 5),
      Course(2100, 'Experimental Psychology', 4),
      Course(2200, 'Psychological Tests', 3),
      Course(2250, 'Behavior Modification', 4),
      Course(3540, 'Groups and Organizations', 3),
      Course(3552, 'Human Factors in Business', 4),
      Course(4210, 'Theories of Learning', 4),
      Course(4320, 'Cognitive Processes', 4)) 
      WHERE name = 'Psychology';

UPDATE department SET courses = CourseList(
      Course(1000, 'General Psychology', 5),
      Course(2100, 'Experimental Psychology', 4),
      Course(2200, 'Psychological Tests', 3),
      Course(2250, 'Behavior Modification', 4),
      Course(3540, 'Groups and Organizations', 3),
      Course(3552, 'Human Factors in Business', 4),
      Course(4210, 'Theories of Learning', 4),
      Course(4320, 'Cognitive Processes', 4),
      Course(4410, 'Abnormal Psychology', 4)) 
      WHERE name = 'Psychology';

Use Column Groups

Column groups can help you avoid conflicts even if you do not apply any conflict resolution methods to the column groups. When your replicated table contains multiple column groups, each group is viewed independently when analyzing updates for conflicts.

For example, consider a replicated table with column group a_cg and column group b_cg. Column group a_cg contains the following columns: a1, a2, and a3. Column group b_cg contains the following columns: b1, b2, and b3.

The following updates occur at replication sites sf.example.com and la.example.com:

• User wsmith updates column a1 in a row at sf.example.com.

• At exactly the same time, user mroth updates column b2 in the same row at la.example.com.

In this case, no conflicts result because Oracle analyzes the updates separately in column groups a_cg and b_cg. If, however, column groups a_cg and b_cg did not exist, then all of the columns in the table would be in the same column group, and a conflict would have resulted. Also, with the column groups in place, if user mroth had updated column a3 instead of column b2, then a conflict would have resulted, because both a1 and a3 are in the a_cg column group.

Use Primary Site and Dynamic Site Ownership Data Models

One way that you can avoid the possibility of replication conflicts is to limit the number of sites in the system with simultaneous update access to the replicated data. Two replicated data ownership models support this approach: primary site ownership and dynamic site ownership.

Avoiding Specific Types of Conflicts

When both primary site ownership and dynamic ownership data models are too restrictive for your application requirements, you must use a shared ownership data model. Even so, typically you can use some simple strategies to avoid specific types of conflicts.

Column Groups

Oracle uses column groups to detect and resolve update conflicts. A column group is a logical grouping of one or more columns in a replicated table. Every column in a replicated table is part of a single column group. When configuring replicated tables at the master definition site, you can create column groups and then assign columns and corresponding conflict resolution methods to each group.

Column groups have the following characteristics:

• A column can belong only to one column group.

• A column group can consist of one or more columns of a table.

• Conflict resolution is applicable only to columns in a column group.

Error Queue

If a conflict resolution method fails to resolve a data conflict, or if you have not defined any conflict resolution methods, then the error queue contains information about the data conflict.

Latest Timestamp

The latest time stamp method resolves a conflict based on the most recent update, as identified by the time stamp of when the update occurred.

The following example demonstrates an appropriate application of the latest time stamp update conflict resolution method:

1. A customer in Phoenix calls the local salesperson and updates her address information.

2. After hanging up the phone, the customer realizes that she gave the local salesperson the wrong postal code.

3. The customer tries to call the local salesperson with the correct postal code, but the salesperson cannot be reached.

4. The customer calls the headquarters, which is located in New York. The New York site, rather than the Phoenix site, correctly updates the address information.

5. The network connecting New York headquarters with the local Phoenix sales site goes down temporarily.

6. When the New York/Phoenix network connection comes back up, Oracle sees two updates for the same address, and detects a conflict at each site.

7. Using the latest time stamp method, Oracle selects the most recent update, and applies the address with the correct postal code.

Target Environments The latest time stamp conflict resolution method works to converge replication environments with two or more master sites. Because time is always increasing, it is one of the few conflict resolution methods that can guarantee data convergence with multiple master sites. This resolution also works well with any number of materialized views.

Support Mechanisms To use the time stamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated time stamp column with the local SYSDATE. For a change applied from another site, the time stamp value should be set to the time stamp value from the originating site.

Overwrite

The overwrite method replaces the current value at the destination site with the new value from the originating site, and therefore can never guarantee convergence with more than one master site. This method is designed to be used by a single master site and multiple materialized view sites. You can also use this form of conflict resolution with multiple master sites, though it does not guarantee data convergence and should be used with some form of a user-defined notification facility.

For example, if you have a single master site that you expect to be used primarily for queries, with all updates being performed at the materialized view sites, then you might select the overwrite method. The overwrite method is also useful if:

• Your primary concern is data convergence.

• You have a single master site.

• No particular business rule exists for selecting one update over the other.

• You have multiple master sites and you supply a notification facility to notify the person who ensures that data is correctly applied, instead of logging the conflict in the DEFERROR data dictionary view and leaving the resolution to your local database administrator.

Target Environments The overwrite conflict resolution method ensures data convergence for replication environments that have a single master site with any number of materialized views. With this in mind, the overwrite conflict resolution method is ideal for mass deployment environments.

If a conflict is detected, then the value originating from the materialized view site is used, which means that priority is given to the most recently refreshed materialized views.

Support Mechanisms No additional support mechanisms are required for the overwrite conflict resolution method.

Implement Overwrite See the Advanced Replication interface's online Help to learn how to define an overwrite conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Additive

The additive method works with column groups consisting of a single numeric column only. If a conflict arises, instead of choosing one value over another, then the difference of the two values is added to the current value.

The additive method adds the difference between the old and new values at the originating site to the current value at the destination site according to this formula:

current value = current value + (new value - old value)

The additive conflict resolution method provides convergence for any number of master sites and materialized view sites.

Target Environments The additive conflict resolution method is designed to conserve data rather than choose the most appropriate data. This method might be useful in a financial environment where deposits and withdrawals happen so frequently that conflicts can arise; with a balance, it is important to conserve data rather than choose one value over another (though we might wish that deposits would always be chosen over withdrawals).

Support Mechanisms No additional support mechanisms are required for the additive conflict resolution method.

Implement Additive See the Advanced Replication interface's online Help to learn how to define an additive conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Average

Like the additive method, the average method works with column groups consisting of a single numeric column only. Instead of adding the difference to the current value, the average method resolves the conflict by computing the average of the current and the new value.

The average conflict resolution method averages the new column value from the originating site with the current value at the destination site.

current value = (current value + new value)/2

The average method cannot guarantee convergence if your replication environment has more than one master site.

Target Environments Because the average method cannot guarantee data convergence for replication environments with more than one master site, the average method is ideally implemented in mass deployment environment with a single master site and any number of updatable materialized views.

The average method might be useful for scientific applications that would rather average two values than choose one value over another (for example, to compute the average temperature or weight).

Support Mechanisms No additional support mechanisms are required for the average conflict resolution method.

Implement Average See the Advanced Replication interface's online Help to learn how to define an average conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Discard

The discard method ignores the values from the originating site and therefore can never guarantee convergence with more than one master site. The discard method ignores the new value from the originating site and retains the value at the destination site. This method is designed to be used by a single master site and multiple materialized view sites, or with some form of a user-defined notification facility.

For example, if you have a single master site and multiple materialized view sites based on it, and you expect the materialized view sites to be used primarily for queries with all updates being performed at the master site, then you might select the discard method. The discard methods is also useful if:

• Your primary concern is data convergence.

• You have a single master site.

• There is no particular business rule for selecting one update over the other.

• You have multiple master sites and you supply a notification facility to notify the person who ensures that data is correctly applied, instead of logging the conflict in the DEFERROR view and leaving the resolution to your local database administrator.

Target Environments The discard conflict resolution method is best suited for a mass deployment model having a single master site with any number of materialized view sites. If a conflict is detected, then the value originating from the materialized view site is ignored, which means that priority is given to materialized views that refresh first.

Support Mechanisms No additional support mechanisms are required for the discard conflict resolution method.

Implement Discard See the Advanced Replication interface's online Help to learn how to define a discard conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Earliest Timestamp

The earliest time stamp methods resolves a conflict based on the earliest (oldest) update, as identified by the time stamp of when the update occurred.

Target Environments The earliest time stamp conflict resolution method works to converge replication environments with a single master site and any number of materialized views. Because time is always increasing, the earliest time stamp conflict resolution cannot guarantee data convergence in replication environments with more than one master site. This resolution also works well with any number of materialized views, if you have a backup conflict resolution method in the event that two transactions have the same time stamp.

Support Mechanisms To use the time stamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated time stamp column with the local SYSDATE. For a change applied from another site, the time stamp value should be set to the time stamp value from the originating site. Be sure to review "Timestamp Configuration Issues".

Implement Earliest Timestamp See the Advanced Replication interface's online Help to learn how to define an earliest time stamp conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Maximum

When Advanced Replication detects a conflict with a column group and calls the maximum value conflict resolution method, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you select the maximum value conflict resolution method.

If the new value of the designated column is greater than the current value, then the column group values from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. If the new value of the designated column is less than the current value, then the conflict is resolved by leaving the current values of the column group unchanged.

There are no restrictions on the data types of the columns in the column group. Convergence for more than one master site is only guaranteed if the column value is always increasing.

Target Environments If you have defined the maximum conflict resolution method and the target column that is used to resolve the conflict is always increasing across all sites, then this method guarantees data convergence with any number of master sites and materialized view sites.

Support Mechanisms No additional support mechanisms are required for the maximum conflict resolution method.

Implement Maximum See the Advanced Replication interface's online Help to learn how to define a maximum conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Minimum

When Advanced Replication detects a conflict with a column group and calls the minimum value conflict resolution method, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you select the minimum value conflict resolution method.

If the new value of the designated column is less than the current value, then the column group values from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. If the new value of the designated column is greater than the current value, then the conflict is resolved by leaving the current values of the column group unchanged.

There are no restrictions on the data types of the columns in the column group. Convergence for more than one master site is only guaranteed if the column value is always decreasing.

Target Environments If you have defined the minimum conflict resolution method and the target column that is used to resolve the conflict is always decreasing across all sites, then this method guarantees data convergence with any number of master sites and materialized view sites.

Support Mechanisms No additional support mechanisms are required for the minimum conflict resolution method.

Implement Minimum See the Advanced Replication interface's online Help to learn how to define a minimum conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Priority Groups

Priority groups enable you to assign a priority level to each possible value of a particular column. If Oracle detects a conflict, then Oracle updates the table whose "priority" column has a lower value using the data from the table with the higher priority value. Therefore, a higher value means a higher priority.

You can guarantee convergence with more than one master site when you are using priority groups if the value of the priority column is always increasing. That is, the values in the priority column correspond to an ordered sequence of events; for example: ordered, shipped, billed.

As shown in Figure 5-2, the DBA_REPPRIORITY view displays the priority level assigned to each priority group member (value that the "priority" column can contain). You must specify a priority for all possible values of the "priority" column.

Figure 5-2 Using Priority Groups

Description of "Figure 5-2 Using Priority Groups"

The DBA_REPPRIORITY view displays the values of all priority groups defined at the current location. In the example shown in Figure 5-2, there are two different priority groups: site-priority and order-status. The customer table is using the site-priority priority group. In the order-status priority group in this example, billed (priority 3) has a higher priority than shipped (priority 2), and shipped has a higher priority than ordered (priority 1).

Before you use the Advanced Replication interface in Oracle Enterprise Manager to select the priority group method of update conflict resolution, you must designate which column in your table is the priority column.

Target Environments The priority group conflict resolution method is useful for replication environments that have been designed for a work flow environment. For example, once an order has reached the shipping status, updates from the order entry department are always over-written.

Support Mechanisms You need to define the priority of the values contained in the target column. This priority definition is required so that Oracle knows how to resolve a conflict based on the priority of the column value that has been designated to resolve a conflict. The priority definitions are stored in a priority group.

Implement Priority Groups See the Advanced Replication interface's online Help to learn how to define a priority group conflict resolution method with the Advanced Replication interface in Enterprise Manager.

Site Priority

Site priority is a special kind of priority group. With site priority, the priority column you designate is automatically updated with the global database name of the site where the update originated. The DBA_REPPRIORITY view displays the priority level assigned to each database site.

Site priority can be useful if one site is considered to be more likely to have the most accurate information. For example, in Figure 5-2, the new_york.example.com site (priority value = 2) is corporate headquarters, while the houston.example.com site (priority value = 1) is an updatable materialized view at a sales office. Therefore, the headquarters office is considered more likely than the sales office to have the most accurate information about the credit that can be extended to each customer.

When you are using site priority alone, convergence with more than one master site is not guaranteed, but site priority can be a good backup method in a multimaster environment, especially for breaking latest time stamp ties.

Similar to priority groups, you must complete several preparatory steps before using the Advanced Replication interface in Oracle Enterprise Manager to select site priority conflict resolution for a column group.

Target Environments As with priority groups, site priority conflict resolution is commonly implemented in a work-flow environment. Additionally, when the site priority conflict resolution method is used in a mass deployment environment (which is a single master site and any number of materialized views), data convergence can be guaranteed.

The site priority conflict resolution method is also a good backup conflict resolution method should a primary conflict resolution method fail in a multimaster environment.

Support Mechanisms A column must be designated to store site information when a row is updated. Additionally, you need to create a trigger that populates this site column with the global name of the updating site when a row is either updated or inserted. A sample of this trigger is contained in the Oracle Database Advanced Replication Management API Reference book.

You also need to define the priority of the sites that participate in your replication environment. This priority definition is required so that Oracle knows how to resolve a conflict based on the priority of the site that performed the update/insert. The site priority definitions are stored in a priority group.

Implement Site Priority See the Advanced Replication interface's online Help to learn how to define a site priority conflict resolution method with the Advanced Replication interface in Oracle Enterprise Manager.

Append Site Name

The append site name method works by appending the global database name of the site originating the transaction to the replicated column value that is generating a dup_val_on_index exception. Although this method allows the column to be inserted or updated without violating a unique integrity constraint, it does not provide any form of convergence between multiple master sites. The resulting discrepancies must be manually resolved; therefore, this method is meant to be used with some form of a notification facility.

This method can be useful when the availability of the data is more important than the complete accuracy of the data. To allow data to be available as soon as it is replicated

• Select append site name.

• Use a notification scheme to alert the appropriate person to resolve the duplication, instead of logging a conflict.

When a uniqueness conflict occurs, the append site name method appends the global database name of the site originating the transaction to the replicated column value. The name is appended to the first period (.). For example, houston.example.com becomes houston.

Append Sequence

The append sequence methods works by appending a generated sequence number to the column value that is generating a dup_val_on_index exception. Although this method allows the column to be inserted or updated without violating a unique integrity constraint, it does not provide any form of convergence between multiple master sites. The resulting discrepancies must be manually resolved; therefore, this method is meant to be used with some form of a notification facility.

This method can be useful when the availability of the data is more important than the complete accuracy of the data. To allow data to be available as soon as it is replicated:

• Select append sequence.

• Use a notification scheme to alert the appropriate person to resolve the duplication, instead of logging a conflict.

The append sequence method appends a generated sequence number to the column value. The column value is truncated as needed. If the generated portion of the column value exceeds the column length, then the conflict method does not resolve the error.

Discard

The discard uniqueness conflict resolution method resolves uniqueness conflicts by simply discarding the row from the originating site that caused the error. This method does not guarantees convergence with multiple master sites and should be used with a notification facility.

Unlike the append methods, the discard uniqueness method minimizes the propagation of data until data accuracy can be verified.

Send and Compare Example

The following example shows how you can further reduce data propagation by using these procedures. Consider a table called rsmith.reports with three columns. Column 1 is the primary key and is in its own column group (column group 1). Column 2 and column 3 are in a second column group (column group 2).

Figure 5-3 Column Groups and Data Propagation

Description of "Figure 5-3 Column Groups and Data Propagation"

The conflict resolution strategy for the second column group is site priority. Column 2 is a VARCHAR2 column containing the site name. Column 3 is a LOB column. Whenever you update the LOB, you must also update column 2 with the global name of the site at which the update occurs. Because there are no triggers for piecewise updates to LOBs, you must explicitly update column 2 whenever you do a piecewise update on the LOB.

Suppose you use the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure to generate replication support for rsmith.reports with min_communication set to TRUE and then use an UPDATE statement to modify column 2 (the site name) and column 3 (the LOB). The deferred remote procedure call (RPC) contains the new value of the site name and the new value of the LOB because they were updated. The deferred RPC also contains the old value of the primary key (column 1), the old value of the site name (column 2), and the old value of the LOB (column 3).

To ensure that the old value of the LOB is not propagated when either column C2 or column C3 is updated, make the following calls:

BEGIN
  DBMS_REPCAT.SEND_OLD_VALUES(
     sname           =>  'rsmith',
     oname           =>  'reports',
     column_list     =>  'c3',
     operation       =>  'UPDATE',
     send            =>  FALSE );
END;
/
 
BEGIN
  DBMS_REPCAT.COMPARE_OLD_VALUES(
     sname           =>  'rsmith',
     oname           =>  'reports',
     column_list     =>  'c3',
     operation       =>  'UPDATE',
     compare         =>  FALSE);
END;
/

You must use the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure to generate replication support for rsmith.reports with min_communication set to TRUE for this change to take effect. Suppose you subsequently use an UPDATE statement to modify column 2 (the site name) and column 3 (the LOB). The deferred RPC contains the old value of the primary key (column 1), the old and new values of the site name (column 2), and just the new value of the LOB (column 3). The deferred RPC contains nulls for the new value of the primary key and the old value of the LOB.

Send and Compare When Using Column Objects

You can specify leaf attributes of a column object when you send and compare old values if the attributes are not replication key columns. For example, suppose you create the following cust_address_typ object type.

CREATE TYPE cust_address_typ AS OBJECT
     (street_address     VARCHAR2(40), 
      postal_code        VARCHAR2(10), 
      city               VARCHAR2(30), 
      state_province     VARCHAR2(10), 
      country_id         CHAR(2));
/

You create the customers table using this type as a column object:

CREATE TABLE customers
     (customer_id        NUMBER(6), 
      cust_first_name    VARCHAR2(20), 
      cust_last_name     VARCHAR2(20), 
      cust_address       cust_address_typ, 
      phone_numbers      phone_list_typ);

If you want to send and compare old values for the street_address attribute of the cust_address_typ type in the customers table, then you run the following procedures to specify that you do want to send or compare the attribute value:

BEGIN
  DBMS_REPCAT.SEND_OLD_VALUES(
     sname           =>  'oe',
     oname           =>  'customers',
     column_list     =>  'cust_address.street_address', -- object attribute
     operation       =>  'UPDATE',
     send            =>   TRUE );
END;
/
 
BEGIN
  DBMS_REPCAT.COMPARE_OLD_VALUES(
     sname           =>  'oe',
     oname           =>  'customers',
     column_list     =>  'cust_address.street_address', -- object attribute
     operation       =>  'UPDATE',
     compare         =>   TRUE);
END;
/

You can also specify that you want to send and compare an entire column object. For example, the following procedures specify the entire cust_address column object:

BEGIN
  DBMS_REPCAT.SEND_OLD_VALUES(
     sname           =>  'oe',
     oname           =>  'customers',
     column_list     =>  'cust_address', -- entire column object
     operation       =>  'UPDATE',
     send            =>   TRUE );
END;
/
 
BEGIN
  DBMS_REPCAT.COMPARE_OLD_VALUES(
     sname           =>  'oe',
     oname           =>  'customers',
     column_list     =>  'cust_address', -- entire column object
     operation       =>  'UPDATE',
     compare         =>   TRUE);
END;
/