Shareplex Conflict Resoloution

Feb. 22nd 2014

Shareplex Conflict Resolution

 

Conflict resolution PL*SQL procedures are defined and written by the customer. Dell provides a couple of templates on how to accomplish the task but the customer must take ownership of the routines and actions taken when a conflict occurs. Additionally, there are some prepared routines to resolve conflicts but they do not provide the flexibility of a customer-written conflict resolution procedure. Typically, conflict resolution is used in ACTIVE-ACTIVE configurations. An ACTIVE-ACTIVE configuration is when two or more databases are being kept in synch by Shareplex and all the databases allow concurrent transactions to occur. Conflicts occur when the same row is modified on two different databases at the same time. As Shareplex moves messages from source to target very quickly (typically a few seconds), the chances of this happening are minimal. However, a network outage will cause Shareplex messages to queue up on the source servers. This dramatically increases the chance for out-of-sync (OOS) conditions. Lack of proper indexing can cause backlogs in the POST queues which increases the chance of OOS conditions.

As Shareplex operates by sending data extracted from the Oracle redo logs from one database to another, there is always some lag time, no matter how minor. As the databases in an ACTIVE-ACTIVE configuration operate independently, there is a chance that transactions could occur on the same row at nearly the same time on multiple databases. The customer should always have some sort of conflict resolution procedure when using ACTIVE-ACTIVE configuration.

What happens when a conflict occurs? If a conflict resolution routine is not in place, an OOS condition will occur and be recorded in the event_log file. If the PL*SQL conflict resolution routine is in place, the conflict resolution routine needs to take the data supplied by Shareplex and successfully resolve the conflict.

 

 

The conflict resolution routine must be installed in the Shareplex schema and run by the Shareplex user. It is not supported by Dell support as it must be customized to the customer needs. A table is created in the Shareplex schema to log each conflict. The conflict resolution routine is operating on a set of assumptions. Even though it successfully resolves a conflict, I recommend that the contents of the log table be reviewed. This could easily be done by setting up a cron job to read the table and send an email containing the OOS messages to the Shareplex Administrator.

Note the SP_CR package:

It defines the input coming from Shareplex when a conflict occurs. The conflict resolution routine will parse these fields to construct the appropriate SQL statement needed to resolve the conflict.

The first task for the conflict resolution procedure is to determine whether or not the incoming transaction should be applied to the target database or be ignored. There are two demo conflict resolution routines provided by Shareplex Support. One is based on the host and the other is based on time. When the host based routine is installed, it prompts for the winning host name. When the procedure is executed during a conflict, it access host_name in v$instance and compares it to the previously input value. Based on this information, the routine determines whether or not to update the target database. The time based conflict resolution routine requires a timestamp column to be present in the table and the column is updated with the current time on INSERT or UPDATE. During the installation of the conflict resolution routine, it will prompt for the timestamp column. The customer will determine whether the first or last updated record will be kept.

There are three basic types of conflicts. An INSERT statement with a duplicate primary key or unique index will cause the first type of conflict. An UPDATE statement or DELETE statements when the corresponding row is not found make up the other two conflicts.

The conflict on a DELETE statement is the easiest to handle. No action is taken as the row cannot be found. The conflict resolution routine will write a row in the conflict logging table to indicate a conflict has occurred.

The conflict on an INSERT statement is generally resolved by changing it to an UPDATE statement. The data passed to the conflict resolution routine as defined in the SP_CR package will be parsed and assembled into the appropriate UPDATE statement. The UPDATE statement is executed and a row is written to the conflict logging table.

The conflict on an UPDATE statement is resolved by updating the row based on the primary key or unique index using the data passed from Shareplex. If the row cannot be found then an INSERT statement is assembled in the same fashion. Finally a row is written to the conflict logging table.

It is critical that the customer test every aspect of the conflict resolution routine. Keeping data in sync and making sure the resolution happens properly are critical to data integrity. Each type of conflict should be generated on each database to verify that the conflicts are resolved as intended. As stated previously, all conflicts resolved by the routine should be reviewed by the customer for accuracy.

 

Author: Mark Bochinski, LeadThem Consulting Senior SharePlex DBA

 

________________________________________________________________________________________________________________

 

 

Posted by LeadThem Consulting | in SharePlex | Comments Off on Shareplex Conflict Resoloution