To resolve conflicts for SharePlex you need to setup the conflict resolution methods. In additions all tables involved must have a primary key. Also it is commonly a good idea for each table to have a last updated and last site field. These can be used by the replication to work out what is the latest or best update to take.
Triggers can be used including to update the last updated and site fields. However every trigger that could be touched by the replication requires a wrapper e.g. when user !=’SPLEX’
For deletes you may need to set the SP_OPO_DEPENDENCY_CHECK to 2 which will not report out of sync when delete cascades are involved.
Setting up the latest timestamp as a winner
1. activate config on both servers
2. set up these parameters on both servers
sp_ctrl> stop capture
sp_ctrl> stop post
sp_ctrl> set param SP_OCT_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_DEPENDENCY_CHECK 2 —- for support of delete cascade
sp_ctrl> set param SP_OPO_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_SUPPRESSED_OOS 0
sp_ctrl> start capture
sp_ctrl> start post
3. stop post on both servers
4. 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 winner
onwer.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 insert
onwer.table_name I !LeastRecentRecord(TIMESTAMPCOL)
5. start post on both servers
To 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.
Setting up a site as winner
You can also setup a site as the winner in a situation where there is a conflict. This can also be setup with the timestamp as well so you can tell Shareplex to check timestamp and if the same use the site. This is my usual pattern.
1. activate config on both servers
2. set up these parameters on both servers
sp_ctrl> stop capture
sp_ctrl> stop post
sp_ctrl> set param SP_OCT_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_DEPENDENCY_CHECK 2
sp_ctrl> set param SP_OPO_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_SUPPRESSED_OOS 0
sp_ctrl> start capture
sp_ctrl> start post
3. stop Post on both servers
4. create entries in conflict_resolution.SID file under var dir/data on both servers
owner.table_name IUD !HostPriority
5. set this parameter to the same host server that has the priority, meaning data sent from this host will always be the winner for conflict resolution, enter exactly the host_name from querying v$instance on the priority host and name is case sensitive.
sp_ctrl> set param SP_OPO_TRUSTED_SOURCE