| Oracle® Database Advanced Replication Management API Refere
nce 10g Release 1 (10.1) Part Number B10733-01 |
|
|
View PDF< /font> |
6
Configure Conflict Resolu
tion
This chapter illustrates how to define conflict resolution methods for your replication environment.
This chapter contains these topics:
- Preparing for Conflict Resolution
- Creating Conflict Resolution Methods for Update Conflicts< /li>
- Creating Conflict Resolution Methods for Uniqueness Conflicts
- Creating Conflict Avoidance Methods for Delete Conflicts
- Using Dynamic Ownership Co nflict Avoidance
- Auditing Successful Conflict R esolution
Preparing for Conflict Resolution
Though you may take great car e in designing your database and front-end application to avoid conflicts that may arise between multiple sites in a replication envi ronment, you may not be able to completely eliminate the possibility of conflicts. One of the most important aspects of replication i s to ensure data convergence at all sites participating in the replication environment.
When data conflicts occur, you need a mechanism to ensure that the conflict is resolved in accordance with your business rules and that th e data converges correctly at all sites.
Advanced Replication lets you define a conflict reso lution system for your database that resolves conflicts in accordance with your business rules. If you have a unique situation that O racle's pre-built conflict resolution methods cannot resolve, then you have the option of building and using your own conflict resolu tion methods.
Before you begin implementing conflict resolution methods for your replicated t ables, analyze the data in your system to determine where the most conflicts may occur. For example, static data such as an employee number may change very infrequently and is not subject to a high occurrence of conflicts. An employee's customer assignments, however , may change often and would therefore be prone to data conflicts.
After you have determined where the conflicts are most likely to occur, you need to determine how to resolve the conflict. For example, do you want the latest change to have precedence, or should one site have precedence over another?
As you read each of the sections describing the different conflict resolution methods, you will learn what each method is best suited for. So, read ea ch section and then think about how your business would want to resolve any potential conflicts.
After you have identified the potential problem areas and have determined what business rules would resolve the problem, use Orac le's conflict resolution methods (or one of your own) to implement a conflict resolution system.
Oracle Database Advanced Replication for conceptual information about conflict resolution methods and detailed information about data convergence for each method |
Creating Conflict Resolution Methods for Update Conflicts
The most common data conflict occurs when the same row at two or more different sites are updated at nearly the same time, or before the defer red transaction from one site was successfully propagated to the other sites.
One method to a void update conflicts is to implement a synchronous replication environment, though this solution requires large network resource.
The other solution is to use the Oracle conflict resolution methods to deal with update conflic ts that may occur when the same row receives two or more updates.
< a name="REPMA113">Overwrite and Discard Conflict Resolution Methods< /a>
The overwrite and discard methods ignore the values from either the origi nating or destination site and therefore can never guarantee convergence with more than one master site. These methods are designed t o be used by a single master site and multiple materialized view sites, or with some form of a user-defined notification facility.
The overwrite method replaces the current value at the destination site with the new value from the originating site. Conversely, the discard method ignores the new value from the originating site.
| See Also:
"ADD_conflicttype_RESOLUTION Procedure" and Oracle Database Advanced Replication for more inform ation about overwrite and discard |
Complete the following steps to cre ate an overwrite or discard conflict resolution method. This example illustrates the use of the discard conflict resolution method at the master site. Therefore, in the event of a conflict, the data from a materialized view site is discarded and the master site data remains.
/************************* BEGINNING OF SCRI PT ******************************
Step 1 Conne ct as the replication administrator.
The procedures in the following steps must be ex ecuted by the replication administrator.
*/ SET ECHO ON SPOOL discard_conflictres.out CONNECT repadmin/ repadmin@orc1.world /*
Step 2 Quiesce the master group that contains the table to which you want to apply the conflict resolution method.< /font>
Before you define overwrite or discard conflict resolution methods, quiesce the maste r group that contains the table to which you want to apply the conflict resolution method. In a single master replication environment , quiescing the master group may not be required. See "Creating Conflict Resolution Methods and Quiescin g" for more information.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Create a column group for your target table.
All Oracle conflict resolution methods are based on logical column groupings called column gro ups.
*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', oname => 'dep artments', column_group => 'dep_cg', list_of_column_names => 'manager_id,lo cation_id'); END; / /*
Step 4 Define the conflict resolution method for a specified table.< /h5>
This example creates an OVERWRITE conflict resolution method.
*/ BEGIN DBMS_REPCAT .ADD_UPDATE_RESOLUTION ( sname => 'hr', oname => 'departments', column_group => 'dep_cg', sequence_no => 1, method => 'DISCARD', parameter_column_name => 'manager_id,location_id'); END; / /*
Step 5 Regenerate replication support for the table that received the conflict resolution method.
*/ BEGIN DBMS_REPCAT.GEN ERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'departments', type => 'TABLE', min_communication => TRUE); END; / /*
Step 6 Resume master activity after replication support has been regenerated.
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_AC TIVITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPOOL OFF /********* **************** END OF SCRIPT **********************************/
< a name="REPMA115">Minimum and Maximum Conflict Resolution Methods
When Advanced Replication detects a conflict with a column group and calls either the minimum or maximum value conflict resolution methods, 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 define your con flict resolution method.
If the new value of the designated column is less than or greater than (depending on the method used) the current value, then the column group values fro m the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. Ot herwise the rows remain unchanged.
Complete the following steps to create an maximum or minim um conflict resolution method.
/********************** *** BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
The procedures in the follo wing steps must be executed by the replication administrator.
*/ SET ECHO ON SPOOL min_conflictres.out CONNECT repadmin/repadmin@orc1.world /*
Step 2 Quiesce the master group that contains the table to which you want to apply the conflict re solution method.
Before you define maximum or minimum conflict resolution methods, qu iesce the master group that contains the table to which you want to apply the conflict resolution method. In a single master replicat ion environment, quiescing the master group may not be required. See "Creating Conflict Resolution Metho ds and Quiescing" for more information.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Create a column group for your target t able.
All Oracle conflict resolution methods are based on logical column groupings ca lled column groups.
*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', o name => 'jobs', column_group => 'job_minsal_cg', list_of_column_names => 'min_salary'); END; / /*
Step 4 Define the conflict resolution method for a specified table.
This example creates a MINIMUM conflict resolution method.
*/ BEGIN DBMS_REPCAT .ADD_UPDATE_RESOLUTION ( sname => 'hr', oname => 'jobs', column_group => 'job_minsal_cg', sequence_no => 1, method => 'MINIMUM', parameter_column_name => 'min_salary'); END; / /*
Step 5 Regenerate replication support for the table that received the conflict resolution method.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLIC ATION_SUPPORT ( sname => 'hr', oname => 'jobs', type => 'TABLE', min_communication => TRUE); END; / /*
Step 6 Resume replication activity.
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg') ; END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT ****************************** ****/
Timestamp Conflict Resolution Methods
The earliest
timestamp and latest timestamp methods are variations on the minimum and maximum value methods. 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 f
rom another site, the timestamp value should be set to the timestamp value from the originating site.
Two elements are needed to make timestamp conflict resolution work well:
- Synchronized time settings between computers
- Timestamp field a nd trigger to automatically record timestamp
Complete the following steps to create a t imestamp conflict resolution method.
/**************** ********* BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
The procedures in the following steps must be executed by the replication administrator.
*/ < a name="26827">SET ECHO ON SPOOL timestamp_conflictres.out CONNECT repadmin/repadmin@orc1.world /*
Step 2 Quiesce the master group that contains the table to which you want to apply the conflict resolution method.
Before defining timestamp conflict resolution methods, q uiesce the master group that contains the table to which you want to apply the conflict resolution method. In a single master replica tion environment, quiescing the master group may not be required. See "Creating Conflict Resolution Meth ods and Quiescing" for more information.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Add an additional column to your table to record the timestamp value when a row is inserted or updated.
If the target table
does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is
inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issu
ing the DDL may cause the replicated object to become invalid.
*/ BEGIN DBMS_REPCAT.ALTER_MASTER_REPOBJECT ( sname => 'hr', oname => 'countries', type => 'TABLE', ddl_text => 'ALTER TABLE hr.countries ADD (timestamp DATE)'); END; / /*
Step 4 Regen erate replication support for the altered table.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'countries', type => 'TABLE', min_communication => TRUE); END; / /*
Step 5 Create a trigger that records the ti mestamp when a row is either inserted or updated.
This recorded value is used in the
resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CR
EATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.
|
Note: You cannot use columns of <
code>datetime and |
Step 6 Create a column group for your target table.
All Oracle conflict resolut ion methods are based on logical column groupings called column groups.
*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', oname => 'countries', column_group => 'countries_t imestamp_cg', list_of_column_names => 'country_name,region_id,timestamp'); END; / /*
Step 7 Define the conflict resolution method for a specified table.
T
his example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp colu
mn that you created earlier.
*/ BEGIN DBMS_REPCAT.ADD_UPDATE_RESOLUTION ( sname => 'hr', oname => 'countries', column_group => 'countries_timestamp_cg', sequence_no => 1, method => 'LATEST TIMESTAMP', parameter_column_name => ; 'timestamp'); END; / /*
Step 8 Regenerate replication support for the table that received the co nflict resolution method.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', < a name="24564">oname => 'countries', type => 'TABLE', min_commun ication => TRUE); END; / /*
Step 9 Resume replication activity.
*/ BEGIN DBMS_REPCAT.RESU ME_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
Additive and Average Conflict Resolution Meth ods
The additive and average methods work with column groups consi sting of a single numeric column only. Instead of "accepting" one value over another, this conflict resolution method either adds the two compared values together or takes an average of the two compared values.
Complete the fo llowing steps to create an additive or average conflict resolution method. This example averages the commission percentage for an emp loyee in the event of a conflict.
/******************* ****** BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
The procedures in the fo llowing steps must be executed by the replication administrator.
*/ SET ECHO ON SPOOL average_conflictres.out CONNECT repadmin/repadmin@orc1.world /*
Step 2 Quiesce the master group that contains the table to which you want to apply the conf lict resolution method.
Before you define additive and average conflict resolution me thods, quiesce the master group that contains the table to which you want to apply the conflict resolution method. In a single master replication environment, quiescing the master group may not be required. See "Creating Conflict Resolut ion Methods and Quiescing" for more information.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Create a column group for your target table.
All Oracle conflict resolution methods are based on logical column gro upings called column groups.
*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', oname => 'employees', column_group => 'commission_average_cg', list _of_column_names => 'commission_pct'); END; / /*
Step 4 Define the conflict resolution method f or a specified table.
This example specifies the AVERAGE conflict resolu
tion method using the sal column.
*/ BE GIN DBMS_REPCAT.ADD_UPDATE_RESOLUTION ( sname => 'h r', oname => 'employees', column_group => 'commission_average_cg', sequence_no => 1, method => 'AVERAGE', parameter_column_n ame => 'commission_pct'); END; / /*
Step 5 Regenerate replication support for the table that re ceived the conflict resolution method.
*/ BE GIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'employees', type => 'TABLE', min_communication => TRUE); END; / /*
Step 6 Resume replication activity.
*/ BEGIN DBM S_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
Priority Groups Conflict Resolut ion Methods
Priority groups allow you to assign a priority level t o 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.
Complete the fol lowing steps to create a priority groups conflict resolution method.
/************************* BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
The procedures in the following steps must be executed by the replication administrator.
*/ SET ECHO ON SPOOL priority_groups_conflic tres.out CONNECT repadmin/repadmin@orc1.world /*Step 2 Quiesce the master group that contains th e table to which you want to apply the conflict resolution method.
Before you define a priority groups conflict resolution method, quiesce the master group that contains the table to which you want to apply the conflic t resolution method. In a single master replication environment, quiescing the master group may not be required. See "Creating Conflict Resolution Methods and Quiescing" for more information.
*/ BEGIN DB MS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*Step 3 Make sure that the job_id column is part of the column group for which your site priority conflict resolution mechanism is u sed.
Use the
ADD_GROUPED_COLUMNprocedure to add this column to an exist ing column group. If you do not already have a column group, then you can create a new column group using theDBMS_REPCAT.MAKE_ COLUMN_GROUPprocedure.*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', oname => 'employees', column_group => 'employees_priority_cg', li st_of_column_names => 'manager_id,hire_date,salary,job_id'); END; / < a name="23280">/*Step 4 Before you begin assigning a priority value to the values in your table, create a priority group that holds the values you defined.
*/ BEGIN DBMS _REPCAT.DEFINE_PRIORITY_GROUP ( gname => 'hr_repg', pgroup => 'job_pg', datatype => 'VARCHAR2'); END; / /*Step 5 Define a priority value fo r all possible table values.
The
DBMS_REPCAT.ADD_PRIORITY_data typeprocedure is available in several different versions. There is a version for each available datatype (NUMBER,VARCHAR2, and so on). Execute this procedure as often as necessary until you have defined a priority value for al l possible table values.
See Also: "ADD_PRIORITY_datatype Procedure" for more information
*/ BEGIN DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'hr_repg', pgroup => 'job_pg', value => 'ad_pres', priority => 10 0); END; / BEGIN DBMS_REPCAT. ADD_PRIORITY_VARCHAR2( gname => 'hr_repg', pgroup => 'job_pg', value => 'sa_man', priority => 80); END; / BEGIN DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'hr_repg', pgroup => 'job_pg', value => 'sa_rep', priority => 60); END; / BEGIN DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'hr_repg', pgroup => 'job_pg', value => 'pu_clerk', priority => 40); END; < a name="24630">/ BEGIN DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'hr_repg', pgroup => 'job_pg', value => 'st_ clerk', priority => 20); END; / /*Step 6 Add the PRIORITY GROUP reso lution method to your replicated table.
The following example shows that it is the se cond conflict resolution method for the specified column group (
sequence_noparameter).*/ BEGIN DBMS_REPCAT.ADD_UPDATE_RES OLUTION ( sname => 'hr', oname => 'employees', column_group => 'employees_priority_cg', sequence_no => 2, method => ' PRIORITY GROUP', parameter_column_name => 'job_id', priority_group => 'job_ pg'); END; / /*Step 7 Regenerate replication support for the table that received the conflict res olution method.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'employees', type => 'TABLE', min_communication =& gt; TRUE); END; / /*< h5 class="LST">Step 8 Resume replication activity.*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ ACTIVITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPOOL OFF /******* ****************** END OF SCRIPT **********************************/Site Priority Conflict Resolution Methods
Site priority is a specialized form of a priority group. Therefore, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving a conflict based on the priority of a field's value, the conflict is resolved based on the priority of the sites involved.
For example, if you assign
orc2.worlda higher priority value thanorc1.worlda nd a conflict arises between these two sites, then the value fromorc2.worldis used.Complete the following steps to create a site priority conflict resolution method.
< pre class="CE">/************************* BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
The procedures in the following steps must be executed by the replication administrator.
*/ SET ECHO ON SPOOL sit e_priority_conflictres.out CONNECT repadmin/repadmin@orc1.world /*
Step 2 Quiesce the master grou p that contains the table to which you want to apply the conflict resolution method.
Before you define a site priority conflict resolution method, quiesce the master group that contains the table to which you want to a pply the conflict resolution method. In a single master replication environment, quiescing the master group may not be required. See "Creating Conflict Resolution Methods and Quiescing" for more infor mation.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Add a site column to your table to store the site value.
Use
the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause
the replicated object to become invalid.
*/ BEGIN < a name="17940">DBMS_REPCAT.ALTER_MASTER_REPOBJECT ( sname => 'hr', oname => 'regions', type => 'TABLE', ddl_text =&g t; 'ALTER TABLE hr.regions ADD (site VARCHAR2(20))'); END; / /*
Step 4 Regenerate replication supp ort for the affected object.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr' , oname => 'regions', type => 'TABLE', min_commu nication => TRUE); END; / /*
Step 5 Create a trigger that records the global name of the site when a row is either inserted or updated.
This recorded value is used in the resoluti
on of conflicts based on the site priority method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE
_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.
*/ BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'hr_repg', type => 'TRIGGER', oname => 'insert_site', sname => 'hr', ddl_text => 'CREATE TRIGGER hr.insert_ site BEFORE INSERT OR UPDATE ON hr.regions FOR EA CH ROW BEGIN IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN SELECT global_name INTO :NEW.SITE FROM GLOBAL_NAME; END IF; END;'); END; / /*
Step 6 Make sure t he new column is part of the column group for which your site priority conflict resolution mechanism is used.
Use the ADD_GROUPED_COLUMN procedure to add this column to an existing column group. If you do
not already have a column group, then you can create a new column group using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedur
e.
*/ BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP ( sname => 'hr', oname => 'regio ns', column_group => 'regions_sitepriority_cg', list_of_column_names => 're gion_id,region_name,site'); END; / /*
Step 7 Before assigning a site priority value to the sites i n your replicated environment, create a site priority group that holds the values you defined.
*/ BEGIN DBMS_REPCAT.DEFINE_SITE_ PRIORITY ( gname => 'hr_repg', name => 'regions_sitepriority_pg'); END; / /*
< font face="Arial, Helvetica, sans-serif">Step 8 Define the priority value for each of the sites in your replication environment.
Execute this procedure as often as necessary until you have defined a site priority value for each of the sites in our replication environment.
*/ BEGIN DBMS_REPCAT.ADD_SITE_PRIORITY_SITE ( gname => 'hr_repg', name => 'regions_sitepriority_pg', site => 'orc1.world', priority => 100); END; / BEGIN DBMS_REPCAT.ADD_SITE_PRIORITY_SITE ( gname =&g t; 'hr_repg', name => 'regions_sitepriority_pg', site => 'orc2.world', priority => 50); END; / BEG IN DBMS_REPCAT.ADD_SITE_PRIORITY_SITE ( gname => 'h r_repg', name => 'regions_sitepriority_pg', site => 'orc3.world', priority => 25); END; / /*
Step 9 Add the SITE PRIORITY resolution method to y our replicated table.
The following example shows that it is the third conflict resol
ution method for the specified column group (sequence_no parameter).
*/ BEGIN DBMS_REPCAT.ADD_UPDATE_RESOLUTION ( sname => 'hr', oname => 'regions', column_group => 'regions_sitepriority_cg', sequence_no => 1, method => 'SITE PRIORITY', parameter_column_name => 'site', priority_group => 'regions_sitepriority_pg'); END; / /*
Step 10 Regenerate replication support for the table that received the conflict resolut ion method.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'regions', type => 'TABLE', min_communication => TR UE); END; / /*
Step 11 Resume replication activity.
< /a>*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIV ITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPOOL OFF /************ ************* END OF SCRIPT **********************************/
Creating Conflict Resolution Methods for Uniqueness Conflicts
In a replication environment, you may have situations where you encounter a conflict on a unique constraint, often resulting from an insert. If your business rules allow you to delete the duplicate row, then you can defi ne a resolution method with Oracle's pre-built conflict resolution methods.
More often, howev er, you probably want to modify the conflicting value so that it no longer violates the unique constraint. Modifying the conflicting value ensures that you do not lose important data. Oracle's pre-built uniqueness conflict resolution method can make the conflicting value unique by appending a site name or a sequence number to the value.
An additional compon ent that accompanies the uniqueness conflict resolution method is a notification facility. The conflicting information is modified by Oracle so that it can be inserted into the table, but you should be notified so that you can analyze the conflict to determine wheth er the record should be deleted, or the data merged into another record, or a completely new value be defined for the conflicting dat a.
A uniqueness conflict resolution method detects and resolves conflicts encountered on colu
mns with a UNIQUE constraint. The example in this section uses the employees table in the hr s
ample schema, which has the unique constraint emp_email_uk on the email column.
Complete the following steps to create a uniqueness c onflict resolution method.
/************************* BEGINNING OF SCRIPT ******************************
Step 1 Connect as the replication administrator.
*/ SET ECHO ON SPOOL unique_conflictres.out < /a>CONNECT repadmin/repadmin@orc1.world /*
Step 2 Quiesce the master group that contains the table to which you want to apply the conflict
resolution method.
Before you define a uniqueness conflict resolution method, make s ure the master group that contains the table to which you want to apply the conflict resolution method is quiesced.
*/ BEGIN DBMS_REPCAT .SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
Step 3 Create a table that stores the messages received from your notification facility.
In
this example, the table name is conf_report.
*/ BEGIN DBMS_REPCAT.EXECUTE_DDL ( gname => ' hr_repg', ddl_text => 'CREATE TABLE hr.conf_report ( line NUMBER( 2), txt VARCHAR2(80), timestamp DATE, < /a> table_name VARCHAR2(30), table_owner VARCHAR2(30), conflict_type VARCHAR2(7))'); END; / < /a>/*
Step 4 Connect as the owner of the table you created in Step 3.
*/ CONNECT hr/hr@orc1.world /*
Step 5 Create a package that sends a notification to the conf_report table when a conflict is
detected.
In this example, the package name is notify.
| See Also:
Appendix B, "User-Defined Conflict Resolution Methods" describes the conflict resolution no tification package that is created in this script |
*/ CREATE OR REPLACE PACKAGE notify AS FUNCTION emp_unique_violation (email IN OUT VARCH AR2, discard_new_values IN OUT BOOLEAN) RETURN BOOLEAN; END noti fy; / CREATE OR REPLACE PACKAGE BODY notify AS TY PE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER; PROCEDURE report_conflict(conflict_report I N MESSAGE_TABLE, report_length IN NUMBER, conflict_time IN DATE, conflict_table IN VARCHAR2, table_owner IN VARCHAR2, conflict_type IN V ARCHAR2) IS BEGIN FOR idx IN 1..report_length LOOP BEGIN INSERT INTO hr.conf_report (line, txt, timestamp, tabl e_name, table_owner, conflict_type) VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time, conflict_table, table_owner, conflict_type); EXCEPTION WHEN others THEN NULL; END; END LOOP; END report_conflict; FUNCTION emp_unique_violation(email IN OUT VARCHAR2, discard_new_values IN OUT BOOL EAN) RETURN BOOLEAN IS local_node VARCHAR2(128); conf_repo rt MESSAGE_TABLE; conf_time DATE := SYSDATE; BEGIN BEGIN < a name="24961"> SELECT global_name INTO local_node FROM global_name; EXCEPTION WHEN others THEN local_node := '?'; END; conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN EMPLO YEES ON ' || TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS'); conf_report(2) := ' AT NODE ' || local_node; conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING' || ' APPEND SITE NAME METHOD'; conf_report(4) := 'EMAIL: ' || email; conf_rep ort(5) := NULL; report_conflict(conf_report,5,conf_time,'employees','hr','UNIQUE'); discard_new_values := FALSE; RETURN FALSE; END emp_unique_violation; END notify; / /*
Step 6 Connect as the replication administrator.
*/ CONNECT repadmin/repadmin@orc1.world /*
Step 7 Replicate the package you created in Step 5 to all of the master sites in your replication environment.
This step ensures that the notification facility is available at all master sites.
*/ BEGIN DBMS_REPCAT.CR EATE_MASTER_REPOBJECT ( gname => 'hr_repg', type => 'PACKAGE', oname => 'notify', sname => 'hr'); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'hr_repg', type => 'PACKAGE BODY', oname => 'notify', sname => 'hr'); END; / /*
Step 8 Add the notif ication facility as one of your conflict resolution methods.
Add it even though it on
ly notifies of a conflict. The following example demonstrates adding the notification facility as a USER FUNCTION<
/code>.
*/ BEGIN DBMS_REPCAT.ADD_UNIQUE_RESOLUTION( sname => 'hr', oname =&g t; 'employees', constraint_name => 'emp_email_uk', sequence_no => 1, method => 'USER FUNCTION', comment => 'Notify DBA', p arameter_column_name => 'email', function_name => 'hr.notify.emp_unique_violation'); END; / /*
Step 9 Add the actual conflict resolution method to your table.
The following example demonstrates adding the APPEND SITE NAME u
niqueness conflict resolution method to your replicated table.
*/ BEGIN DBMS_REPCAT.ADD_UNIQUE_RESOLUTION( sname => 'hr', oname => 'employees', constraint_name => 'emp_email_uk', sequence_no => 2, method => 'APPEND SITE NAME', parameter_column_name => 'email'); END; / /*
Step 10 Regenerate replicatio n support for the table that received the conflict resolution methods.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'employees', type => 'TABLE', min_communication => TRUE); END; / /*
Step 11 Resume replic ation activity.
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / SET ECHO OFF SPO OL OFF /************************* END OF SCRIPT **********************************/
Creating Conflict Avoidance Methods for Delete Conflicts
Unlike u pdate conflicts, where there are two values to compare, simply deleting a row makes the update conflict resolution methods described in the previous section ineffective because only one value would exist.
The best way to deal with deleting rows in a replication environment is to avoid the conflict by marking a row for deletion and periodically purging the t able of all marked records. Because you are not physically removing this row, your data can converge at all master sites if a conflic t arises because you still have two values to compare, assuming that no other errors have occurred. After you are sure that your data has converged, you can purge marked rows using a replicated purge procedure.
When developing
the front-end application for your database, you probably want to filter out the rows that have been marked for deletion, because do
ing so makes it appear to your users as though the row was physically deleted. Simply exclude the rows that have been marked for dele
tion in the SELECT statement for your data set.
For example, a select statement for a current employee listing might be similar to the following:
SELECT * FROM hr.locations WHERE remove_date IS NULL;
This section describes how to prepare your replicated table to avoid delete conflicts. You also learn how to use procedural replication to purge those records that have been m arked for deletion.
Complete the following steps to create a conflict avoidance method for de lete conflicts.
/************************* BEGINNING O F SCRIPT ******************************
Step 1 Connect as the replication administrator at the master definition site.
*/ SET ECHO ON SPOOL delete_conflictres.out CONNECT repadmin/repadmin@orc1.world /*< h5 class="LST">Step 2 Quiesce the master group that contains the table to which you want to apply the conflict resolution method.
*/ BE GIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*< h5 class="LST">Step 3 Add a column to the replicated table that stores the mark for deleted records.
It is advisable to use a timestamp to mark your records for deletion (times
tamp reflects when the record was marked for deletion). Because you are using a timestamp, the new column can be a DATE
datatype. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to add the remove_date column to your existi
ng replicated table.
*/ BEGIN DBMS_REPCAT.ALTER_MASTER_REPOBJECT ( sname => 'hr', oname => 'locations', type => 'TABLE', ddl_text => 'ALTER TABLE hr. locations ADD (remove_date DATE)'); END; / /*
Step 4 Regenerate replication support for the altere d table.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'locations', type => 'TABLE', min_communication => TRU E); END; / /*
Step 5 Create a package that is replicated to all of the master sites in your repli cation environment.
This package purges all marked records from the specified table.< /p>
*/ BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'hr_repg', type => 'PACKAGE', oname => 'purge', sname => 'hr', ddl_ text => 'CREATE OR REPLACE PACKAGE hr.purge AS PROCEDURE remove_locations(purge_date DAT E); END;'); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gnam e => 'hr_repg', type => 'PACKAGE BODY', oname => 'purge', sname => 'hr', ddl_text => 'CREATE OR REPLACE PACKAGE BODY hr.purge AS PROCEDURE remove_locations(purge_date IN DATE) IS BEGIN DBMS_REPUTIL.REPLICATION_OFF; LOCK TABLE hr.locations IN EXCLUSIVE MODE; DELETE hr.locations WHERE remove_date IS NOT NULL AND remove_date < purge_date; DBMS_REPUTIL.REPLICATION_ ON; EXCEPTION WHEN others THEN DBMS_REPUTIL.RE PLICATION_ON; END; END;'); END; / /*
Step 6 Generate replication support for each package and package body.
After generating replication support, a synonym is created for you and added to your master group as a replicated object. This
synonym is labeled as defer_purge.remove_locations.
*/ BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'purge', type => 'PACKAGE', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr', oname => 'purge', type => 'PACKAGE BODY', min_communication => TRUE); END; / /*
Step 7 In a separate terminal window, manu ally push any administrative requests at all other master sites.
You may need to exec
ute the DO_DEFERRED_REPCAT_ADMIN procedure in the DBMS_REPCAT package several times, because some administr
ative operations have multiple steps. The following is an example:
*/ BEGIN DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN ( < /a> gname => 'hr_repg', all_sites => FALSE); END; / */ PAUSE Press <RETURN> to continue when you have verified that there are no pending administrative requests in the DBA_REPCATLOG data dictionary view. /*
Step 8 Resume replication activ ity.
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg'); END ; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
Using Dyna mic Ownership Conflict Avoidance
This section describes a more adv anced method of designing your applications to avoid conflicts. This method, known as token passing, is similar to the workflow method described in the following sections. Although this section describes how to use this method t o control the ownership of an entire row, you can use a modified form of this method to control ownership of the individual column gr oups within a row.
Both workflow and token passing allow dynamic ownership of data. With dyna mic ownership, only one site at a time is allowed to update a row, but ownership of the row can be passed from site to site. Both wor kflow and token passing use the value of one or more "identifier" columns to determine who is currently allowed to update the row.
Workflow
With workflow partitioning, you can think of data ownership as being "pushed " from site to site. Only the current owner of the row is allowed to push the ownership of the row to another site, by changing the v alue of the "identifier" columns.
Take the simple example of separate sites for ordering, shi
pping, and billing. Here, the identifier columns are used to indicate the status of an order. The status determines which site can up
date the row. After a user at the ordering site has entered the order, the user updates the status of this row to ship.
Users at the ordering site are no longer allowed to modify this row -- ownership has been pushed to the shipping site.
After shipping the order, the user at the shipping site updates the status of this row to bill
, thus pushing ownership to the billing site, and so on.
To successfully avoid conflicts, app lications implementing dynamic data ownership must ensure that the following conditions are met:
- Only the owner of the row can update the row.
- T he row is never owned by more than one site.
- Ordering conflicts can be successfu lly resolved at all sites.
With workflow partitioning, only the current owner of the ro w can push the ownership of the row to the next site by updating the "identifier" columns. No site is given ownership unless another site has given up ownership; thus ensuring there is never more than one owner.
Because the fl ow of work is ordered, ordering conflicts can be resolved by applying the change from the site that occurs latest in the flow of work . Any ordering conflicts can be resolved using a form of the priority conflict resolution method, where the priority value increases with each step in the work flow process. The priority conflict resolution method successfully converges for more than one master site as long as the priority value is always increasing.
Token Passing
Token passing us es a more generalized approach to meeting these criteria. To implement token passing, instead of the "identifier" columns, your repli cated tables must have owner and epoch columns. The owner column stores the global database name of the site currently believed to ow n the row.
Once you have designed a token passing mechanism, you can use it to implement a va riety of forms of dynamic partitioning of data ownership, including workflow.
You should desi gn your application to implement token passing for you automatically. You should not allow the owner or epoch columns to be updated o utside this application.
Whenever you attempt to update a row, your application should:
< a name="21652">Step 1 Locate the current owner of the row.
Step 2 Lock the row to prevent updates while owners hip is changing.
Step 3 Establish ownersh ip of the row.
Step 4 Perform the update.
Oracle releases the lock when you commit your transaction.
For example, Figure 6-1 illustrates how ownership of employee 100 p
asses from the acct_sf database to the acct_ny database.
Figure 6-1 Grabbing the Token
Text description of the illustratio n repma009.gif
Locating the Owner of a Row
To obtain ownership, the
acct_ny database uses a simple recursive algorithm to locate the owner of the row. The sample code for this algorithm is
shown as follows:
-- Sample code for locating the token owner. -- This i s for a table TABLE_NAME with primary key PK. -- Initial call should initialize loc_epoch to 0 and loc_owner -- to the local global name. get_owner(PK IN primary_key_type, loc_epoch IN OUT NUMBER, loc_owner IN OUT VARCHAR2) { -- use dynamic SQL (dbms_sql) to perform a select similar to -- the following: SELECT owner, epoch into rmt_owner, rmt_epoch FROM TABLE_NAME@loc_owner WHERE primary_key = PK FOR UPDATE; IF r mt_owner = loc_owner AND rmt_epoch >= loc_epoch THEN loc_owner := rmt_owner; loc_epo ch := rmt_epoch; RETURN; ELSIF rmt_epoch >= loc_epoch THEN ge t_owner(PK, rmt_epoch, rmt_owner); loc_owner := rmt_owner; loc_epoch := rmt_epoch; RETURN; ELSE raise_application_error(-20000, 'No owner for row'); END IF; }
Obtaining Ownership
After locat
ing the owner of the row, the acct_ny site gets ownership from the acct_sf site by completing the following
steps:
Step 1 Lock the row at the sf site to pre vent any changes from occurring while ownership is being exchanged.
Step 2 Synchronously update the owner information at both the sf and ny sites.
This operation ensures that only one site considers itself to be the owner at all times. The update at the sf site should not be replicated using DBMS_REPUTIL.REPLICATION_OFF. The replicated change of ownership at the <
code>ny site in Step 4 will ultimately be propagated to all other sites in the replication environment, including the
Step 3 Update the row information at the new owner site, ny, with the information from the current owner site, sf.
This data is guaranteed to be the most recent. This time, the change at the ny site should
not be replicated. Any queued changes to this data at the sf site are propagated to all other sites in the usual manner
. When the sf change is propagated to ny, it is ignored because of the values of the epoch numbers, as desc
ribed in the next bullet point.
Step 4 Update the epoch number at the new owner site to be one greater than the value at the previous site.
Perform this update at the new owner only, and then asynchronously propagate this update to the other master sites. Incrementin g the epoch number at the new owner site prevents ordering conflicts.
When the As another example, suppose the You should design your application to implement this method of token passing for you automatically whenever you perform an upda
te. You should not allow the owner or epoch columns to be updated outside this application. The lock that you grab when you change ow
nership is released when you apply your actual update. The changed information, along with the updated owner and epoch information, a
re asynchronously propagated to the other sites in the usual manner. Whenever Oracle detects and successfully resolves an update, delete, or uniqueness conflict, you can view i
nformation about what method was used to resolve the conflict by querying the The Use the After calling Use the If you registered a table to log information about the successful resolution of update, delete, and uniqueness conflicts, then you
can remove this information from the The following example purges
the statistics gathered about conflicts resolved due to inserts, updates, and deletes on the sf changes (that were in the deferred queue in Step 2 preceding) are ultimately propagated to the ny site, the ny site ignores them because they have a lower epoch number than the epoch number at the ny site for the same data.
hq site received the sf changes afte
r receiving the ny changes, the hq site would ignore the sf changes because the changes applie
d from the ny site would have the greater epoch number.ALL_REPRESOLUTION_STATISTICS data dictiona
ry view. This view is updated only if you have enabled conflict resolution statistics gathering for the table involved in the conflic
t.
See Also:
ALL_REPRESOLUTION_STATISTICS
view for more informationREGISTER_STATISTICS procedure in the DBMS_REPCAT package
to collect information about the successful resolution of update, delete, and uniqueness conflicts for a table. The following exampl
e gathers statistics for the employees table in the hr schema:BEGI
N
DBMS_REPCAT.REGISTER_STATISTICS (
sname => 'hr',
oname => 'employees');
END;
/
Viewing Conflict Resolution Statistics
REGISTER_STATISTICS for a table, each conflict t
hat is successfully resolved for that table is logged in the ALL_REPRESOLUTION_STATISTICS data dictionary view. Informat
ion about unresolved conflicts is always logged in the DEFERROR view, whether the object is registered or not.
<
/table>
See Also:
<
p class="NB">The ALL_REPRESOLUTION_STATISTICS view and
the DEFERROR view for more informationCan
celing Conflict Resolution Statistics
CANCEL_STATISTICS<
/code> procedure in the DBMS_REPCAT package if you no longer want to collect information about the successful resolution
of update, delete, and uniqueness conflicts for a table. The following example cancels statistics gathering on the employees table in the hr schema:BEGIN
DBMS_REPCAT.CANCEL_STATISTICS (
sname => 'hr',
oname => 'employees
');
END;
/
Clearing Statistics Information
DBA_REPRESOLUTION_STATISTICS data dictionary view by calling the PURGE_STATI
STICS procedure in the DBMS_REPCAT package.employees table between Jan
uary 1 and March 31:BEGIN
DBMS_REPCAT.
PURGE_STATISTICS (
sname => 'hr',
oname => 'employees',
start_date => '01-JAN-2001',
end_date => '31-MAR-2001');
END;
/
<
/td>
Next<
img src="../../dcommon/gifs/oracle.gif" alt="Oracle">
Copyright © 1996
, 2003 Oracle Corporation
All Rights Reserved.
Home
Contents
Index
Master Index
Feedback