How to set up Host Priority based conflict resolution with SharePlex 8.6.4 and above? (4274141)

Starting SharePlex 8.6.4 and above, a default conflict resolution based on host priority is introduced for peer to peer replication

RESOLUTION:

Prerequisite: Enable supplemental logging for pk, uk, fk on both servers and each table which needs conflict resolution must have primary key defined.

1. activate config on both servers

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 <priority hostname>

6. start Post on both servers

To test conflict resolution, stop export on both servers and insert data with same PK to invoke conflict and restart export on both servers to verify results.

See attached document for example on how to set up default conflict resolution based on Host 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 SharePlex.

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