-
Title
How to set up time based default conflict resolution in 8.6.4 -
Description
In SharePlex 8.6.4, a new default conflict resolution based on time priority is introduced for peer to peer replication.In earlier versions, !MostRecentRecord is used to resolve conflict based on the latest timestamp as the winner.In SharePlex 8.6.4, !LeastRecentRecord is introduced to resolve conflict based on the earliest timestamp as the winner. -
Cause
New Feature -
Resolution
RESOLUTION:Prerequisite: Ensure supplemental logging for pk, uk and fk enabled on both servers1. activate config on both servers2. set up these parameters on both serverssp_ctrl> stop capturesp_ctrl> stop postsp_ctrl> set param SP_OCT_REDUCED_KEY 0sp_ctrl> set param SP_OPO_DEPENDENCY_CHECK 2 ---- for support of delete cascadesp_ctrl> set param SP_OPO_REDUCED_KEY 0sp_ctrl> set param SP_OPO_SUPPRESSED_OOS 0sp_ctrl> start capturesp_ctrl> start post3. stop post on both servers4. Create same entries in conflict_resolution.SID file under var dir/data on both servers, make sure the timestamp column is specified in UPPER CASE. You must choose the same method (either !MostRecentRecord or !LeastRecentRecord) on both servers.Example:for latest timestamp as winner:owner.table_name IUD !MostRecentRecord(TIMESTAMPCOL)OR for earliest timestamp as winneronwer.table_name IUD !LeastRecentRecord(TIMESTAMPCOL)OR you can specify a DML type such as insert with one of the time based pre-built routine such as earliest timestamp as winner for insertonwer.table_name I !LeastRecentRecord(TIMESTAMPCOL)5. start post on both serversTo test conflict resolution, stop export on both servers and insert or update data with same PK to invoke conflict and restart export on both servers to verify results. For time based conflict resolution, the timestamp column must be updated during update or having value during insert as it is used to determine the priority for resolving conflict.See attached document for example on how to set up default conflict resolution based on Time Priority.LOGGING of Conflict Resolution (SharePlex 8.6.6):
To log details on how SharePlex resolves conflict resolution, install or patch to SharePlex 8.6.6 which has a new parameter called SP_OPO_LOG_CONFLICT that controls logging and information is stored in shareplex_conf_log table.
SP_OPO_LOG_CONFLICT
This parameter enables the logging of information about successful conflict resolution procedures to the SHAREPLEX_CONF_LOG table. This feature applies only to the prepared routines that are provided by .
This parameter offers two active settings:
A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table.
A setting of 2 enables the logging and also directs Post to query the target database to get the current timestamp of the target row. Note: A setting of 2 may affect the performance of Post as a result of making the query. It populates EXISTING_TIMESTAMP column in shareplex_conf_log table.
Default: 0 (Disabled)
Range of valid values: 0, 1, or 2
Takes effect: when Post is restarted
Here is the describe on shareplex_conf_log table.
SQL> desc shareplex_conf_log
Name Null? Type
----------------------------------------- -------- ----------------------------
CONFLICT_NO NOT NULL NUMBER
CONFLICT_TIME TIMESTAMP(6)
SRC_HOST VARCHAR2(32)
CURR_HOST VARCHAR2(32)
TRUSTED_HOST VARCHAR2(32)
SRC_ORA_SID VARCHAR2(32)
SOURCE_ROWID VARCHAR2(20)
TARGET_ROWID VARCHAR2(20)
CONFLICT_TABLE VARCHAR2(100)
CONFLICT_TYPE VARCHAR2(1)
CONFLICT_RESOLVED NOT NULL VARCHAR2(1)
ORACLE_ERR VARCHAR2(10)
TIMESTAMP_COLUMN VARCHAR2(50)
INCOMING_TIMESTAMP TIMESTAMP(6)
EXISTING_TIMESTAMP TIMESTAMP(6)
PRIMARY_KEYS VARCHAR2(4000)
MESSAGE VARCHAR2(400)
SQL_STATEMENT LONG
CONFLICT_CHECKED VARCHAR2(1)
SEQNO NUMBER
OFFSET NUMBER
SCN NUMBER
USERID NUMBER
-
Defect ID
SPO-1330 -
Attachments
-
Additional Information
see attachment for examples, additional logging information on conflict resolution in shareplex_conf_log table will be in 8.6.6 through SPO-2449