New: Logging for Default Conflict Resolution

Starting with SharePlex 8.6.6, post process will be able to log details on how it resolves conflict resolution for default prepared routines such as !HostPriority, !MostRecentRecord and !LeastRecentRecord.  The logging is controlled by SP_OPO_LOG_CONFLICT parameter 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 default prepared routines.

This parameter offers two active settings:

A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table except EXISTING_TIMESTAMP column.

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 addition to other columns 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

 

Here is an example of what the entries look like in the shareplex_conf_log table with SP_OPO_LOG_CONFLICT=1 on time based insert conflict on primary key and post changes sql statement to update to resolve the conflict.

CONFLICT_NO                   : 9

CONFLICT_TIME                 : 20-JAN-17 03.54.41.076954 AM

SRC_HOST                      : alvsupl17

CURR_HOST                     : alvsupl14

TRUSTED_HOST                  :

SRC_ORA_SID                   : ORA11GR2

SOURCE_ROWID                  : AAHPS6AAEAAAW+HAAA

TARGET_ROWID                  : AADejtAAEAAGqYsAAA

CONFLICT_TABLE                : "QUEST"."P2P"

CONFLICT_TYPE                 : I

CONFLICT_RESOLVED             : Y

ORACLE_ERR                    : ORA-00001

TIMESTAMP_COLUMN              : MYDATE

INCOMING_TIMESTAMP            : 20-JAN-17 03.54.13.437611 AM

EXISTING_TIMESTAMP            :

PRIMARY_KEYS                  : "ID" = '01'

MESSAGE                       : Incoming timestamp > existing timestamp.

Incoming wins, overwrite existing

SQL_STATEMENT                 : update   "QUEST"."P2P" set

"ID"='01',"NAME"='alvsupl17',"MYDATE"='01202017035413.437611000' where rownum =

1 AND "ID" = '01' AND "MYDATE" < '01202017035413.437611000';

CONFLICT_CHECKED              : N

SEQNO                         : 48603

OFFSET                        : 4624020

SCN                           : 17043922245

USERID                        : 274

 

Here is example of what's logged in shareplex_confg_log table with SP_OPO_LOG_CONFLICT=2 on time based update conflict with latest timestamp as winning record.

 

CONFLICT_NO                   : 11

CONFLICT_TIME                 : 20-JAN-17 06.08.11.818046 AM

SRC_HOST                      : alvsupl17

CURR_HOST                     : alvsupl14

TRUSTED_HOST                  :

SRC_ORA_SID                   : ORA11GR2

SOURCE_ROWID                  : AAHPTHAAEAAAW+HAAA

TARGET_ROWID                  : AADekJAAEAAGqYsAAA

CONFLICT_TABLE                : "QUEST"."P2P"

CONFLICT_TYPE                 : U

CONFLICT_RESOLVED             : Y

ORACLE_ERR                    : ORA-01403

TIMESTAMP_COLUMN              : MYDATE

INCOMING_TIMESTAMP            : 20-JAN-17 06.07.46.806138 AM

EXISTING_TIMESTAMP            : 20-JAN-17 06.07.56.339147 AM

PRIMARY_KEYS                  : "ID" = '01'

MESSAGE                       : Incoming timestamp < existing timestamp.

Existing wins, discard incoming

SQL_STATEMENT                 : update   "QUEST"."P2P" set

"NAME"='a17',"MYDATE"='01202017060746.806138000' where rownum = 1 AND "ID" =

'01' AND "MYDATE" < '01202017060746.806138000';

CONFLICT_CHECKED              : N

SEQNO                         : 48608

OFFSET                        : 20623888

SCN                           : 17044537548

USERID                        : 274

 

Additional KB articles on default prepared conflict resolution:

KB205846 - How to set up Host Based conflict resolution
https://support.quest.com/shareplex/kb/205846

KB208803 - How to set up Time Based conflict resolution
https://support.quest.com/shareplex/kb/208803

Blog: Setting up pre-built conflict resolution in peer to peer environment

https://www.quest.com/community/b/en/posts/setting-up-pre-built-conflict-resolution-in-peer-to-peer-environment

About the Author
Jessica.Jiang
Jessica is a Senior Technical Product Specialist for SharePlex Support group at Quest Software. She joined Quest Software as a senior PSO (Professional Services Group) consultant for SharePlex in 2000...