Conflict Resolution in Advanced Replication:
In replication, 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 deferred transaction from one site was successfully propagated to the other sites.
One method to avoid 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 conflicts that can occur when the same row receives two or more updates.
Now we need to avoid this error in replication. Next question is; How to avoid.
There are several methods to avoid this issue in replication but before we need to know how to replication data transfer working? If you understand this concept you never face any error in replication.
How working data transfer between two or more site:
Once you stat data transfer using dbms_refresh.refresh. First of all checking old values in my object.
For example:
I have one table like “EMP” it’s replicated (updatable). Once start data transfer what’s the old values in EMP objects? Those values available in both site or not. If available means next that data checking current values I mean new values. Suppose both end old records not available sense you should be face “ORA-12034: materialized view log younger than last refresh”. If you face this error you need complete refresh other than no never solve this issue.
Some time in both site updating same row. This time you should be face “ORA-23402: refresh was aborted because of conflicts caused by deferred txns” If you face this error you need to implement conflict resolution.
For example:
SQL> show user;
USER is "BABU@WASIM" – Master Site
SQL> select * from master;
FIRSTNAME LASTNAME ID
-------------------- -------------------- ----------
BABU SRIKANT 101
WASIM SRI 102
SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';
1 row updated.
SQL> commit;
Commit complete.
SQL> CONNECT BABU@MV - Materialized view site.
Enter password:
Connected.
SQL> SELECT * FROM MASTER;
FIRSTNAME LASTNAME ID
-------------------- -------------------- ----------
BABU SRIKANT 101
WASIM SRI 102
SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';
1 row updated.
SQL> commit;
Data Transfer between master and materialized view database:
MviewAdmin@mv.
SQL> exec dbms_refresh.refresh('mviewadmin.tst_rep');
BEGIN dbms_refresh.refresh('mviewadmin.tst_rep'); END;
*
ERROR at line 1:
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
Configure conflict resolution:
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'BABU');
END;
/
/*Create Column group for target table*/
BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => 'BABU',
oname => 'MASTER',
column_group => 'BABU',
list_of_column_names => 'firstname,lastname'); //this is the column available
for both end
END;
/
/*Define conflict resolution method*/
BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => 'BABU',
oname => 'MASTER',
column_group => 'BABU',
sequence_no => 1,
method => 'DISCARD',
parameter_column_name => 'firstname,lastname');
END;
/
/*regenerate replication support for conflict resolution method*/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'BABU',
oname => 'MASTER',
type => 'TABLE',
min_communication => TRUE);
END;
/
/*start replication activity*/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'BABU');
END;
/
Commit;
Conflict Resolution Methods:
There are several method to configure conflict resolutions. The above example I explained “DISCARD” methods.
- Discard – Already explained.
- Minimum and Maximum :
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 conflict 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 from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. Otherwise the rows remain unchanged.
- Timestamp Conflict resolution method.
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 from 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 and trigger to automatically record timestamp
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
/* Altering my based using alter_master_repobject procedure */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 DDL_TEXT => 'ALTER TABLE BABU.MENUMASTER ADD (MODIFYED_ON DATE )'
8 );
9* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
/* This is called creating new column group */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.MAKE_COLUMN_GROUP
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 LIST_OF_COLUMN_NAMES => '*'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
/* This is latest timestamp method */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ADD_UPDATE_RESOLUTION
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 SEQUENCE_NO => 1,
8 METHOD => 'LATEST TIMESTAMP',
9 PARAMETER_COLUMN_NAME => 'MODIFYED_ON'
10 );
11* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
- Addtive and Average conflict methods.
The additive and average methods work with column groups consisting 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 following steps to create an additive or average conflict resolution method. This example averages the commission percentage for an employee in the event of a conflict.
- Priority Groups Conflict Resolution Methods:
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.
- 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.
Monitoring Conflict Resolution:
· DBA_REPCONFLICT describes the conflict resolution method for all the tables in the database on which a conflict resolution method has been defined.
· USER_REPCONFLICT describes the conflict resolution method for all the tables owned by the current user on which a conflict resolution method has been defined.DBA_REPPARAMETER_COLUMN - Using this data dictionary we can able to find what are they column groups, which method using and what are they parameter colunm used those infomarion we can find using this data dictionary table.
DBA_REPRESOLUTION - Using this data dictionary we can find conflict resolution method type and table name
DBA_REPRESOLUTION_METHOD - Using this data dictionary we can find method name.
DBA_REPRESOLUTION_STATISTICS - View lists information about successfully resolved update, uniqueness, and delete conflicts for all replicated tables in the database.
DBA_REPSITES view lists the members of each replication group in the database.
DBA_REPCOLUMN - List of column name using group
DBA_REPCOLUMN_GROUP - List of group name using per column;
DBA_REPDDL -contains the DDL for each replication object in the database. Its columns are the same as those in ALL_REPDDL.
DBA_REPCONFLICT - view displays the name of each table in the database on which a conflict resolution method has been defined and the type of conflict that the method is used to resolve.
No comments:
Post a Comment