SharePlex and Index Criticality

May. 30th 2014

Shareplex Index Criticality

 

The Dell Shareplex database replication software performs best when the tables in the databases have proper indexes. Every table in replication should have a primary key or unique index. A table having a primary key or unique index will post more quickly than one without.

Oracle supplemental logging must be enabled for Shareplex to work. Though minimal supplemental logging is adequate, enabling the primary key and unique index logging is best. This is done with the following statement: alter database add supplemental log data (primary key, unique index) columns. It increases the amount of information written to the online and archive redo logs, but it is well worth it. It prevents extra hits to the database by the Shareplex READ process, reducing overhead on the primary or source database.

On the secondary or target database, Shareplex posts the INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK statements. Each statement logged in the redo log files on the source database is posted to the target database to keep them in synch. The two statements which need indexes are the UPDATE and DELETE. Shareplex must find each individual row before performing the UPDATE or DELETE operation. With a primary key or unique index, the UPDATE or DELETE statements execute very quickly. If a table does not have a primary key or unique index, Shareplex finds the row by putting all the columns of the table in the WHERE clause of the UPDATE or DELETE statement. On a small table this can be OK, but on a large table, it will usually cause a full table scan, slowing down the POST process and causing large backlogs. If a table has too many indexes, the INSERT command can be slow, but this usually does not cause a backlog to occur.

The Shareplex COMPARE/REPAIR process compares a table on the source system with a table on the target system. Having a primary key or unique index is good as Shareplex must sort the tables in the same order to do the COMPARE or REPAIR. Recently, I ran into a rather unusual case where a table had 580 columns and no primary key or unique index. As there were so many columns, the programmer created the table with the columns alphabetized for ease of use. Unfortunately, most of the columns were NULL. Additionally, Shareplex only sorted on the first 256 columns as the COMPARE/REPAIR process was originally written for Oracle 7. Each time I ran the REPAIR process, the NULL columns sorted in a different order on the source and target databases. It would INSERT and DELETE rows to make them in synch. I would immediately run it again and the same thing would happen. Shareplex development patched the COMPARE/REPAIR process to sort on over 1000 columns and it finally worked. The table was never out of synch but it appeared to be due to poor indexing and a sub-optimal feature in Shareplex.

Another way a primary key can be used in the Shareplex COMPARE/REPAIR process is by using the WHERE clause option. Tables which are very large, 100s of millions of rows, often take a very long time as they need to sort in the TEMP tablespace on source and target before starting the actual comparison of the rows. Smaller tables often sort in memory if the PGA is set large enough. Using the WHERE clause option (especially easy if the primary key is based on a sequence) the table can be broken into manageable chunks which ran sort in memory. The individual compare commands can be put in to a file and processed with a script if desired.

Sometimes a table gets corrupted, truncated, or horribly out of synch on the target database. In these cases, the Shareplex COPY command or a manual Oracle datapump export/import in tandem with the FLUSH command can be used to re-synch the table. Before starting, check the number of indexes on the table. If there are more than a couple, it will probably be faster if you drop all the indexes on the target table except the primary key, then rebuild them after the COPY or datapump export/import is completed.

 

Author: Mark Bochinski, LeadThem Consulting Senior SharePlex DBA

Posted by LeadThem Consulting | in SharePlex | Comments Off on SharePlex and Index Criticality

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