Return
-
Title
Peer to Peer Replication and how to set up Conflict Resolution -
Description
Peer to Peer Replication and how to set up Conflict Resolution
-
Cause
This is a brief overview, see Admin Guide for more details
-
Resolution
ÂPeer-to-peer replication allows users on multiple databases to make changes concurrently to the same data, while SharePlex replication keeps all of the databases synchronized.It requires a major commitment to database design that may not be practical when packaged applications are in use.ÂPL/SQL procedure need to be created to handle conflict when users making changes to the same row at or near the same time or when the network is down.If you do not have PL/SQL procedure in place, conflicts will not be handled unless your application is designed such that no conflict is anticipated.ÂConsiderationsSequencesShareplex can not replicate sequences in p2p environment. Use odd/even or different ranges of sequence values on each master.KeysShareplex requires primary key on all replicated tables.LONG/LONG RAW, LOBConflict resolution does not support table with LONG/LONG RAW, LOB columns.Delete CascadeYou can either code it in CR to ignore delete conflict or change all delete cascade to triggers and use trigger wrapper.ÂÂÂMost customers use either Host Based priority or Time Based priority or a combination of both to resolve conflictHost BasedYou need to choose one server as the winning host during conflict.Time BasedEach table need to have a timestamp column which will be used to determine which side wins during conflict. It would be either the latest time wins (typically update) or the earliest time wins (typically insert) based on customer requirement. If timestamps happen to be the same (if you use date), then we need to use host based as a secondary resolution method or use timestamp data type to avoid this issue.ÂÂÂGeneric CR allows customer to code dynamic sql in procedure to handle multiple tablesTo use Generic CR, SP_OPO_GENERIC_CR must be set to 1 in paramdbBasic CR allows customer to code specific procedure to handle one table at a time.This is the default SP_OPO_GENERIC_CR=0ÂBest Practice is to use Generic CR.Transformation only works with Generic CRÂÂÂInsert --- record with same key already exists, you get unique constraint violationÂNormally it should not happen if PK columns are based on sequences and if you have set up sequences to different values on each server. However you may get conflict if there is a unique key in addition to PK. In this case, when user enters the same value for the unique key on each server around the same time may cause conflict.ÂIn this case, CR will need some extra code to handle this since the key column pass into the CR procedure is based on primary key column and not the unique key column. Such insert will need to be either transformed into update statement or discarded based on conflict resolution criteria.ÂÂUpdate --- preimage of the changed column does not match because the record was changed by users on both sides to different values around the same time or when the network breaks down.In this case, CR need to resolve the conflict by either updating the records on the losing side or discard the message on the winning side.ÂDelete - shareplex is trying to delete a record which is already being deleted by user on the other side. Or this is due to delete cascade is turned on.Typically CR would ignore this error and discard this message.ÂÂMost customers will have a table that logs all conflict resolution results. This is an audit table which tells customer which row, which table, at what time conflict occurs, whether the conflict is resolved, and how the conflict is resolved.Typically the table is owned by the shareplex user and is called conf_logÂSQL> desc conf_logName Null? Type----------------------------------------- -------- ----------------------------CONFLICT_NO NOT NULL NUMBERCONFLICT_TIME DATESRC_HOST VARCHAR2(32)SRC_ORA_SID VARCHAR2(32)SOURCE_ROWID VARCHAR2(20)TARGET_ROWID VARCHAR2(20)ORACLE_ERR NUMBERCONFLICT_TABLE VARCHAR2(200)CONFLICT_TYPE VARCHAR2(1)CONFLICT_RESOLVED NOT NULL VARCHAR2(1)PRIMARY_KEYS VARCHAR2(4000)MESSAGE VARCHAR2(400)SQL_STATEMENT LONGCONFLICT_CHECKED VARCHAR2(1)ÂÂÂÂYou need to set the following two debug parameters when encountering CR related post error.ÂSp_ctrl> set param SP_OPO_DEBUG_FLAG 0x0f0001ffSp_ctrl> set param SP_OPO_DEBUG_CR 0xffffÂConflict Resolution Procedure includes commits inside the procedure. Including specific commit inside the procedure can cause OOS as transaction may not appropriately rollback when the procedure encounters error.The procedure is coded in generic format and SP_OPO_GENERIC_CR=0 (basic) or opposite scenario.ÂORA-1403 - Data not found.Can be many reasons.If the target row does not exist and procedure is basic format CR, that's a bug (sol25212).Work around: use generic format CR.ORA-1458 - invalid length inside variable character stringwork around: Turn off sql cacheORA-6550 - line.. PLS-00306: wrong number or types of arguments in call (sol 25426)-Check to see if procedure is in basic format or generic format and what is the SP_OPO_GENERIC_CR setting-upgrade from 4.0 to 4.5 plus, make sure they run ora_setup since there is a new variable called src_ora_time. Check sp_cr package and make sure this variable is there.ÂORA-942 - table or view does not existCheck privileges, if CR is owned by splex, you need to explicitly grant select insert,update,delete on the replicated tables to splex user. Also check O7_DICTIONARY_ACCESSIBILITY, if it is FALSE make sure you grant select any dictionary to splex user.Post core dumpIf this is on table with LOB calling conflict resolution, it is not supportedÂCR procedure not workingCheck conflict_resolution.SID file and make sure avoid using !Default with customer CR procedure. Each table that requires CR needs to be explicitly listed in conflict_resolution.SID file.ÂTransformationTransformation is similar to conflict resolution which uses PL/SQL procedureTransformation can not be used together with conflict resolution. Only one can be used at one timeNew enhancement has been made to transformation to use more bind variables thus reduce parsing. This is included in 5.3.3Transformation typically slows down post a lot.Transformation only works with generic CR format.Â