5
Conflict Resolution Concepts and Architecture

Some replication environments must create conflict resolution methods to resolve possible data conflicts that can result from replicating data between multiple sites. This chapter contains these topics:

• Conflict Resolution Concepts
• Conflict Resolution Architecture

Conflict Resolution Concepts

Replication conflicts can occur in a replication environment that permits concurrent updates to the same data at multiple sites. For example, when two transactions originating from different sites update the same row at nearly the same time, a conflict can occur. When you configure a replication environment, you must consider whether replication conflicts can occur. If your system design permits replication conflicts and a conflict occurs, then the system data does not converge until the conflict is resolved in some way.

In general, your first choice should always be to design a replication environment that avoids the possibility of conflicts. Using several techniques, most system designs can avoid conflicts in all or a large percentage of the data that is replicated. However, many applications require that some percentage of data be updatable at multiple sites at any time. If this is the case, then you must address the possibility of replication conflicts.

The next few sections introduce the following topics relating to replication conflicts:

• How to design a replication system with replication conflicts in mind
• How to determine the types of conflicts that are possible in your replication environment
• How you can avoid replication conflicts in designing your replication environment
• How Oracle can detect and resolve conflicts in designs where conflict avoidance is not possible

Understanding Your Data and Application Requirements

When you design any type of database application and its supporting database, it is critical that you understand the requirements of the application before you begin to build the database or the application itself. For example, each application should be modular, with clearly defined functional boundaries and dependencies, such as order-entry, shipping, billing, and so on. Furthermore, you should normalize supporting database data to reduce the amount of hidden dependencies between modules in the application system.

In addition to basic database design practices, you must investigate additional requirements when building a database that operates in a replication environment. Start by considering the general requirements of the applications that will work with the replicated data. For example, some applications might work fine with read-only materialized views, and as a result, can avoid the possibility of replication conflicts altogether. Other applications might require that most of the replicated data be read-only and a small fraction of the data (for example, one or two tables or even one or two columns in a specific table) be updatable at all replication sites. In this case, you must determine how to resolve replication conflicts when they occur so that the integrity of replicated data remains intact.

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.

Types of Replication Conflicts

You may encounter these types of data conflicts in a replicated database environment:

• Update Conflicts
• Uniqueness Conflicts
• Delete Conflicts

You will most likely encounter update conflicts in your replication environment, although you should always prepare to handle uniqueness and delete conflicts. Oracle Corporation recommends that your database design works to avoid these types of conflicts.

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.

Data Conflicts and Transaction Ordering

Conflicts Ordering conflicts can occur in replication environments with three or more master sites. If propagation to master site X is blocked for any reason, then updates to replicated data can continue to be propagated among other master sites. When propagation resumes, these updates may be propagated to site X in a different order than they occurred on the other masters, and these updates may conflict. By default, the resulting conflicts are recorded in the error log and can be re-executed after the transactions they depend upon are propagated and applied. See Table 5-1 to see an example of an ordering conflict. To guarantee data convergence in replication environments with three or more master sites, you must select a conflict resolution method that can guarantee data convergence with any number of master sites (latest timestamp, minimum, maximum, priority group, additive). The minimum, maximum, priority group, and additive conflict resolution methods guarantee data convergence with any number of master sites, as long as certain conditions exist. See the appropriate conflict resolution method in the "Conflict Resolution Architecture" section for more information Referential Integrity In addition to receiving a data conflict, replicated transactions that are applied out-of-order might experience referential integrity problems at a remote site if supporting data was not successfully propagated to that site. Consider the scenario where a new customer calls an order department; a customer record is created and an order is placed. If the order data is propagated to a remote site before the customer data, then a referential integrity error is raised because the customer that the order references does not exist at the remote site. If a referential integrity error is encountered, then you can easily resolve the situation by re-executing the transaction in error after the supporting data has been propagated to the remote site.

Conflict Detection

Each master site in a replication system automatically detects and resolves replication conflicts when they occur. For example, when a master site pushes its deferred transaction queue to another master site in the system, the remote procedures being called at the receiving site can automatically detect if any replication conflicts exist.

When a materialized view site pushes deferred transactions to its corresponding master site or master materialized view site, the receiving site performs conflict detection and resolution. A materialized view site refreshes its data by performing materialized view refreshes. The refresh mechanism ensures that, upon completion, the data at a materialized view is the same as the data at the corresponding master table or master materialized view, including the results of any conflict resolution. Therefore, it is not necessary for a materialized view site to perform work to detect or resolve replication conflicts.

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.

  Note: To detect and resolve an update conflict for a row, the propagating site must send a certain amount of data about the new and old versions of the row to the receiving site. For maximum performance, tune the amount of data that Oracle uses to support update conflict detection and resolution. For more information, see "Performance Mechanisms and Conflict Resolution".

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.

Conflict Resolution

After a conflict has been detected, resolve the conflict with the goal of data convergence across all sites. Oracle provides several prebuilt conflict resolution methods to resolve update conflicts and in many situations can guarantee data convergence across a variety of replication environments. Oracle also offers several conflict resolution methods to handle uniqueness conflicts, though these methods cannot guarantee data convergence.

Oracle does not provide any prebuilt conflict resolution methods to handle delete or ordering conflicts. Oracle does, however, allow you to build your own conflict resolution method to resolve data conflicts specific to your business rules. If you do build a conflict resolution method that cannot guarantee data convergence, which is likely for uniqueness and delete conflicts, then you should also build a notification facility to notify the database administrator so that data convergence can be manually achieved.

Whether you use an Oracle prebuilt or user-defined conflict resolution method, it is applied as soon as the conflict is detected. If the defined conflict resolution method cannot resolve the conflict, then the conflict is logged in the error queue.

To avoid a single point of failure for conflict resolution, you can define additional conflict resolution methods to backup the primary method. For example, in the unlikely event that the latest timestamp conflict resolution method cannot resolve a conflict because the timestamps are identical, you may want to define a site priority conflict resolution method, which breaks the timestamp tie and resolves the data conflict.

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 may 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

Text description of the illustration rep81094.gif

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. See "What's New in Advanced Replication?" at the beginning of this book for more information about when quiesce is necessary.
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 Replication Management tool.
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 Replication Management tool
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 timestamp 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.

Insert Statements	Delete Statements	Update Statements
Inserts new rows into storage table (assuming nested table value is neither null nor empty) Inserts new row into parent table, with the NESTED_TABLE_ID value referring to previously inserted storage rows.	Deletes any storage table rows associated with the parent table being deleted. Deletes parent table row.	Inserts new rows into storage table (assuming the nested table column is set to a value that is neither null nor an empty table). Updates the parent table row. Deletes old storage table rows.

Example of Nested Table Conflicts

The following example illustrates how DML statements on nested table columns can lead to conflicts that are difficult to resolve. Following the example is information about ways to minimize conflicts.

Suppose there is a university which stores information about its departments in a department table containing a nested table column that stores information about each department's courses:

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

The university has campuses across the United States and uses multimaster replication to support its different locations. Each location can update the department table, which is replicated. On univ1.world, one of the master sites, information is inserted about the Psychology department.

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

Advanced Replication propagates the insert to all masters.

Then, change information arrives about the Psychology class offerings. That is, a class is added. The information is updated on univ1.world.

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

After univ1.world has committed the update, but before the change is propagated to other master sites, another master site, univ2.world, receives information that two more class have been added, both 4320 and 4410.

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

Both the update on univ1.world and the update on univ2.world are pushed.

There will be an update conflict on department table. Each user who made an update expects that it is the first update since the insert statement. But actually, the local update has taken place first, and therefore the NESTED_TABLE_ID has changed, because these are updates on the parent table. It is only updates on the nested table column (changing the storage table rows and NESTED_TABLE_ID) which are problematic. There is no problem updating other columns in the parent table.

Suppose this conflict is resolved by keeping the local table update. Delete conflict resolution would be required on the storage table to ignore the missing rows, which were already deleted by the local update. The new rows inserted into the storage table, due to the update at the remote site, now have no reference in the parent table. These new storage table rows must also be dealt with. Otherwise, they will be orphaned. The storage table would grow with course rows which are not accessible from the department table.

Resolving conflicts by manipulating the storage table rows while updating the parent table is very difficult with two master sites in a multimaster replication environment and becomes nearly impossible as the number of master sites increases. If this type of update is necessary, then it may be best to not define any conflict resolution methods on the nested table and resolve conflicts manually. Incorrect conflict resolution could lead to divergence. That is, tables on different masters may no longer match.

Recommendations for Avoiding Problematic Updates

The following recommendations enable you to avoid the problematic updates described in the preceding section:

• Use a foreign key constraint, initially deferred, on the nested table. This constraint prevents dangling rows in the storage table. The following is an example of such a foreign key constraint:

  ALTER TABLE courses_tab add CONSTRAINT courses_fk 
    FOREIGN KEY(NESTED_TABLE_ID) REFERENCES department(courses) 
    INITIALLY DEFERRED;
  
  

• Make sure all inserts on the parent table insert an empty nested table. Do not use a null nested table value. This practice helps to create a reusable NESTED_TABLE_ID. The following is an example of an insert that included an empty nested table:

  INSERT INTO department (name, director, office, courses) 
     VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133', CourseList());
  
  

• Make sure all inserts, deletes, and updates are performed directly on the nested table rather than through DML on the parent table. This practice helps to reuse the present NESTED_TABLE_ID value.

  The following is an example of deleting rows directly from a nested table:

  DELETE FROM TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology');
  
  

  Consider an example where the following rows are inserted directly into the nested table on univ1.world:

  INSERT INTO TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology') 
     VALUES (Course(5000, 'Social Psychology', 5));
  
  INSERT INTO TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology') 
     VALUES (Course(5100, 'Psychology of Personality', 4));
  
  

  Then, the following rows are inserted directly into the nested table on univ2.world before the preceding inserts on univ1.world are pushed:

  INSERT INTO TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology') 
     VALUES (Course(5000, 'Social Psychology', 5));
  
  INSERT INTO TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology') 
     VALUES (Course(5100, 'Psychology of Personality', 4));
  
  INSERT INTO TABLE 
     (SELECT courses FROM department WHERE name = 'Psychology') 
     VALUES (Course(5500, 'Cognitive Neuroscience', 5));
  
  

  Here, primary key conflicts will occur on the inserted rows in the storage table for courses 5000 and 5100), but a conflict resolution on the storage table which allows the inserts from one site to fail should provide the proper results. However, these inserts will not result in the more complicated problem involving multiple tables described in "Example of Nested Table Conflicts", but the NESTED_TABLE_ID value is not lost, because this value has not changed.

• Consider using a trigger on the parent table that prevents inserts and updates that include manipulation of the nested table column. The following is an example of such a trigger:

  CREATE OR REPLACE TRIGGER depart_trig
    AFTER INSERT OR UPDATE ON department
    FOR EACH ROW
    DECLARE
    new_ntid raw(100);
    old_ntid raw(100);
    BEGIN
  -- obtain the nested table ids
    SELECT sys_op_tosetid(:new.courses) INTO new_ntid from dual;
    SELECT _tosetid(:old.courses) INTO old_ntid from dual;
    IF INSERTING THEN
  -- raise error on insert of a null nested table column
      IF :new.courses IS NULL THEN
        raise_application_error(-20011, 'inserting null nested table ref');
      END IF;
  -- raise error if new rows are inserted in the storage table
  -- this is not strictly necessary, but it does enforce DML access
  -- semantics of separate DMLS on parent table and storage table
      IF :new.courses.count != 0 THEN
        raise_application_error(-20012, 
        'inserting rows into storage table while inserting parent table row');
      END IF;
    ELSE
  -- raise error if update has caused the NESTED_TABLE_ID to change
      IF new_ntid != old_ntid THEN
        raise_application_error(-20013, 
          'updating storage table reference while updating parent table row');
      END IF;
    END IF;
  END;
  /
  
  

These recommendations continue to apply with multilevel nesting, where the storage table row becomes a parent to another storage table's rows. All of these recommendations are good strategies at each level of nesting.

Techniques for Avoiding Conflicts

Although Oracle provides powerful methods for resolving data conflicts, one of your highest priorities when designing a replicated database and front-end application should be to avoid data conflicts. The next few sections briefly suggest several techniques that you can use to avoid some or all replication conflicts.

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.world and la.world:

• User wsmith updates column a1 in a row at sf.world.
• At exactly the same time, user mroth updates column b2 in the same row at la.world.

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.

Primary Site Ownership

Primary ownership is the replicated data model that the read-only replication environments support. Primary ownership prevents all replication conflicts, because only a single server permits update access to a set of replicated data.

Rather than control the ownership of data at the table level, applications can employ row and column subsetting to establish more granular static ownership of data. For example, applications might have update access to specific columns or rows in a replicated table on a site-by-site basis.

Dynamic Site Ownership

The dynamic ownership replicated data model is less restrictive than primary site ownership. With dynamic ownership, capability to update a data replica moves from site to site, still ensuring that only one site provides update access to specific data at any given point in time. A workflow system clearly illustrates the concept of dynamic ownership. For example, related departmental applications can read the status code of a product order, for example, enterable, shippable, billable, to determine when they can and cannot update the order.

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.

Avoiding Uniqueness Conflicts

It is quite easy to configure a replication environment to prevent the possibility of uniqueness conflicts. For example, you can create sequences at each site so that each sequence at each site generates a mutually exclusive set of sequence numbers. This solution, however, can become problematic as the number of sites increase or the number of entries in the replicated table grows.

Alternatively, you can append a unique site identifier as part of a composite primary key.

Finally, you can select a globally unique value using the SYS_GUID function. Using the selected value as the primary key (or unique) value will globally avoid uniqueness conflicts.

Avoiding Delete Conflicts

Always avoid delete conflicts replicated data environments. In general, applications that operate within an asynchronous, shared ownership data model should not delete rows using DELETE statements. Instead, applications should mark rows for deletion and then configure the system to periodically purge logically deleted rows using procedural replication.

Avoiding Update Conflicts

After trying to eliminate the possibility of uniqueness and delete conflicts in a replication system, you should also try to limit the number of update conflicts that are possible. However, in a shared ownership data model, update conflicts cannot be avoided in all cases. If you cannot avoid all update conflicts, then you must understand exactly what types of replication conflicts are possible and then configure the system to resolve conflicts when they occur.

Avoiding Ordering Conflicts

Whenever possible, avoid or automatically resolve ordering conflicts. For example, select conflict resolution methods that ensure convergence in multimaster configurations where ordering conflicts are possible.

The example in Table 5-1 shows how having three master sites can lead to ordering conflicts. Master Site A has priority 30; Master Site B has priority 25; and Master Site C has priority 10; x is a column of a particular row in a column group that is assigned the site-priority conflict resolution method. The highest priority is given to the site with the highest priority value. Priority values can be any Oracle number and do not have to be consecutive integers.

Table 5-1 Example: Ordering Conflicts With Site Priority Conflict Resolution

Time	Action	Site A	Site B	Site C
1	All sites are up and agree that x = 2.	2	2	2
2	Site A updates x = 5.	5	2	2
3	Site C becomes unavailable.	5	2	down
4	Site A pushes update to Site B. Site A and Site B agree that x = 5. Site C is still unavailable. The update transaction remains in the queue at Site A.	5	5	down
5	Site C becomes available with x = 2. Sites A and B agree that x = 5.	5	5	2
6	Site B updates x = 5 to x = 7.	5	7	2
7	Site B pushes the transaction to Site A. Sites A and B agree that x = 7. Site C still says x = 2.	7	7	2
8	Site B pushes the transaction to Site C. Site C says the old value of x = 2; Site B says the old value of x = 5. Oracle detects a conflict and resolves it by applying the update from Site B, which has a higher priority level (25) than Site C (10). All site agree that x = 7.	7	7	7
9	Site A successfully pushes its transaction (x = 5) to Site C. Oracle detects a conflict because the current value at Site C (x = 7) does not match the old value at Site A (x = 2). Site A has a higher priority (30) than Site C (10). Oracle resolves the conflict by applying the outdated update from Site A (x = 5). Because of this ordering conflict, the sites no longer converge.	7	7	5

Conflict Resolution Architecture

Very few architectural mechanisms and processes are visible when implementing conflict resolution into your replication environment. This section describes the few supporting mechanisms involved in conflict resolution and describes different aspects of Oracle's prebuilt conflict resolution methods.

Support Mechanisms

The most important mechanism involved in Oracle conflict resolution is the column group because it is the basis for all update conflict detection and resolution. Additionally, the error queue can provide you with important information to monitor the conflict detection activity of your replication environment.

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.

  See Also: "Use Column Groups" for information about using column groups to avoid conflicts

Ensuring Data Integrity with Multiple Column Groups

Having column groups enables you to designate different methods of resolving conflicts for different types of data. For example, numeric data is often suited for an arithmetical resolution method, and character data is often suited for a timestamp resolution method. However, when selecting columns for a column group, it is important to group columns wisely. If two or more columns in a table must remain consistent with respect to each other, then place the columns within the same column group to ensure data integrity.

For example, if the postal code column in a customer table uses one resolution method while the city column uses a different resolution method, then the sites could converge on a postal code that does not match the city. Therefore, all components of an address should typically be within a single column group so that conflict resolution is applied to the address as a unit.

Shadow Column Groups

By default, every replicated table has a shadow column group. The shadow column group of a table contains all columns that are not within a specific column group. You cannot assign conflict resolution methods to a table's shadow group. Therefore, make sure to include a column in a column group when conflict resolution is necessary for the column. Oracle detects conflicts that involve columns in the shadow column group but does not attempt to apply any conflict resolution methods to resolve these conflicts.

Column Objects and Column Groups

An Oracle object based on a user-defined type that occupies a single column in a table is a column object. A column object cannot span column groups. That is, given a column group and a column object, either the column object and all of its attributes must be within the column group, or the column object and all of its attributes must be excluded from a column group.

Oracle's prebuilt conflict resolution methods cannot resolve conflicts based on undefined column object attribute values. If a column object is NULL, then its attributes are undefined.

Object Tables and Column Groups

An object table is a special kind of table in which each row represents an object based on a user-defined type. You can specify column groups that include a subset of the columns in an object table.

Nested Tables and Column Groups

A nested table's storage table is treated as an independent table in conflict resolution. Therefore, you can create a column group based on a subset of the columns in a storage table.

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.

Common Update Conflict Resolution Methods

Although Oracle provides eight prebuilt update conflict resolution methods, the latest timestamp and the overwrite conflict resolution methods are the most commonly implemented resolution methods.

These methods are the most common because they are easy to use and, in the proper environments, can guarantee data convergence. The latest timestamp and the overwrite conflict resolution methods are described in detail in the following two sections.

Table 5-2 Convergence Properties of Common Update Conflict Resolution Methods

Resolution Methods	Convergence with Multiple Master Sites
Latest timestamp	YES (with backup method)
Overwrite	NO

Latest Timestamp

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

The following example demonstrates an appropriate application of the latest timestamp 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 timestamp method, Oracle selects the most recent update, and applies the address with the correct postal code.

Target Environments

The latest timestamp 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 timestamp 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 timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.

Timestamp Configuration Issues

When you use timestamp resolution, you must carefully consider how time is measured on the different sites managing replicated data. For example, if a replication environment crosses time zones, then applications that use the system should convert all timestamps to a common time zone such as Greenwich Mean Time (GMT). Furthermore, if two sites in a system do not have their system clocks synchronized reasonably well, then timestamp comparisons might not be accurate enough to satisfy application requirements. You can maintain timestamp columns if you use the EARLIEST or LATEST timestamp update conflict resolution methods in the following ways: Each application can include logic to synchronize timestamps. You can create a trigger for a replicated table to synchronize timestamps automatically for all applications. A clock counts seconds as an increasing value. Assuming that you have properly designed your timestamping mechanism and established a backup method in case two sites have the same timestamp, the latest timestamp method (like the maximum value method) guarantees convergence. The earliest timestamp method, however, cannot guarantee convergence for more than one master site.

Implement Latest Timestamp

See the Replication Management tool's online help to learn how to define a latest timestamp conflict resolution method with the Replication Management tool.

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 Replication Management tool's online help to learn how to define an overwrite conflict resolution method with the Replication Management tool.

Additional Update Conflicts Resolution Methods

If the latest timestamp or the overwrite conflict resolution methods do not meet your needs to resolve data conflicts that are encountered in your replication environment, then Oracle offers six additional prebuilt update conflict resolution methods.

Table 5-3 Convergence Properties of Additional Update Conflict Resolution Methods

Resolution Methods	Convergence with Multiple Master Sites
Additive	YES
Average	NO
Discard	NO
Earliest timestamp	NO
Maximum	YES (column values must always increase)
Minimum	YES (column values must always decrease)
Priority group	YES (with ordered update values)
Site priority	NO

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 may 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 Replication Management tool's online help to learn how to define an additive conflict resolution method with the Replication Management tool.

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 Replication Management tool's online help to learn how to define an average conflict resolution method with the Replication Management tool.

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 Replication Management tool's online help to learn how to define a discard conflict resolution method with the Replication Management tool.

Earliest Timestamp

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

Target Environments

The earliest timestamp 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 timestamp 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 timestamp.

Support Mechanisms

To use the timestamp 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 timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site. Be sure to review the "Timestamp Configuration Issues" discussion.

Implement Earliest Timestamp

See the Replication Management tool's online help to learn how to define an earliest timestamp conflict resolution method with the Replication Management tool.

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 datatypes 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 Replication Management tool's online help to learn how to define a maximum conflict resolution method with the Replication Management tool.

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 datatypes 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 Replication Management tool's online help to learn how to define a minimum conflict resolution method with the Replication Management tool. Or, see the information on the minimum and maximum methods in Oracle9i Replication Management API Reference book to learn how to define this type of conflict resolution method with the replication management API.

Priority Groups

Priority groups allow 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

Text description of the illustration rep81080.gif

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 Replication Management tool 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 Replication Management tool's online help to learn how to define a priority group conflict resolution method with the Replication Management tool.

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.world site (priority value = 2) is corporate headquarters, while the houston.world 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 timestamp ties.

Similar to priority groups, you must complete several preparatory steps before using the Replication Management tool 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 Replication Management tool's online help and in the Oracle9i 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 Replication Management tool's online help to learn how to define a site priority conflict resolution method with the Replication Management tool.

Uniqueness Conflicts Resolution Methods

Oracle provides three prebuilt methods for resolving uniqueness conflicts:

• Append the global site name of the originating site to the column value from the originating site.
• Append a generated sequence number to the column value from the originating site.
• Discard the row value from the originating site.

The following sections explain each uniqueness conflict resolution method in detail.

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 may be 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.world 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 may be 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.

Delete Conflict Resolution Methods

Oracle does not provide any prebuilt methods for resolving delete conflicts. As discussed in the "Avoiding Delete Conflicts" section, you should design your database and front-end application to avoid delete conflicts. You can achieve this goal by marking rows for deletion and at regular intervals, using procedural replication to purge such marked rows.

Performance Mechanisms and Conflict Resolution

To detect and resolve an update conflict for a row, the propagating site must send a certain amount of data about the new and old versions of the row to the receiving site. Depending on your environment, the amount of data that Oracle propagates to support update conflict detection and resolution can be different.

Minimum Communication

When you create a replicated table and all participating sites are Oracle8 or higher databases, you can choose to minimize the amount of data that must be communicated to determine conflicts for each changed row in the table by enabling the minimum communication feature. When minimum communication is enabled, Oracle propagates:

• The old value of the primary key and each column in the column group (the value before the modification)
• The new value of each updated column in the column group

  Note: For an inserted row, the row has no old value. For a deleted row, the row has no new value.

  Note: A nested table's storage table is treated as an independent column group.

In general, you should choose to minimize data propagation in Oracle8 or higher replication environments to reduce the amount of data that Oracle transmits across the network. As a result, you can help to improve overall system performance.

Alternatively, when a replication environment uses both Oracle7 and Oracle8 or higher sites, you cannot minimize the communication of row data for update conflict resolution. In this case, Oracle must propagate the entire old and new versions of each changed row to perform conflict resolution.

Reduced Quiesce and Materialized Views in a Single Master Site Environment

In a single master site environment, if all of the updatable materialized views do not use minimum communication in the detection of conflicts, then quiesce is no longer required for certain administration operations to the master group.

If at least one updatable materialized view uses minimum communication to detect conflicts, quiesce is not required if you:

• Propagate all of the deferred transactions at the databases containing updatable materialized views using minimum communication before you perform the administration operations to the master group.
• Do not allow any database manipulation language (DML) changes on the updatable materialized views that use minimum communication until you have finished the administration operation on the master site and regenerated replication support for the materialized view.

If you do not perform these actions, then quiesce the master group before you perform the administration operations on it.

Send and Compare Old Values

If you have minimized propagation using the method described previously, then you can further reduce data propagation in some cases by using the DBMS_REPCAT.SEND_OLD_VALUES procedure and the DBMS_REPCAT.COMPARE_OLD_VALUES procedure to send old values only if they are needed to detect and resolve conflicts. For example, the latest timestamp conflict detection and resolution method does not require old values for nonkey and non timestamp columns.

To further reduce data propagation, execute the following procedures:

DBMS_REPCAT.SEND_OLD_VALUES(
   sname           IN  VARCHAR2,
   oname           IN  VARCHAR2,
   { column_list   IN  VARCHAR2,
   | column_table  IN  DBMS_UTILITY.VARCHAR2s | DBMS_UTILITY.LNAME_ARRAY,}
   operation       IN  VARCHAR2 := 'UPDATE',
   send            IN  BOOLEAN  := true );
 
DBMS_REPCAT.COMPARE_OLD_VALUES(
   sname           IN  VARCHAR2,
   oname           IN  VARCHAR2,
   { column_list   IN  VARCHAR2,
   | column_table  IN  DBMS_UTILITY.VARCHAR2s | DBMS_UTILITY.LNAME_ARRAY,}
   operation       IN  VARCHAR2 := 'UPDATE',
   compare         IN  BOOLEAN := true );

After executing these procedures, you must use the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure to generate replication support with min_communication set to true for this change to take effect.

The specified behavior for old column values is exposed in two columns in the DBA_REPCOLUMN data dictionary view: COMPARE_OLD_ON_DELETE (Y or N) and COMPARE_OLD_ON_UPDATE (Y or N).

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

Text description of the illustration rep81050.gif

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