Archive for the 'SharePlex' Category

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 Security Senior SharePlex DBA

Posted by bc-admin | 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 Security Senior SharePlex DBA





Posted by bc-admin | in SharePlex | Comments Off on Shareplex Conflict Resoloution

SharePlex – Repairing a Table with Copy

Jan. 2nd 2014

This procedure is generally used only for very large tables when standard compare and repair can not be executed due to time or system resource constraints.  The instructions below will allow you to execute this procedure with the least amount of impact to the database, by minimizing the lock time required on the table.

  • Open 3 windows on the source system to expedite the process. This will enable you to minimize the amount of time the full table lock is held. With proper execution, the table lock can be less than 10 seconds.
  • In one window, create an export parameter file to export the table you want to synchronize. When complete, enter the export command but don’t hit the return key. The basic export command or data pump can be used.
    • noup / expdp parfile=exp.par &
  • In your second window, enter sqlplus from the command line. Enter the lock table command but do not hit return.
    • sqlplus / as sysdba
    • SQL>  lock table <table name> in exclusive mode;
  • In your third window, enter sp_ctrl and type in the flush command but do not hit return.
    • sp_ctrl
    • > flush <datasource>
  • You are now ready to start the procedure. All steps should be done as quickly as possible to reduce the lock time.
  1. Execute the lock table command in window two. If this times out, retry until successful.
  2. Execute flush command in window three.
  3. Start export command in window one.
  4. Return to window two and execute a commit.
  5. When export is completed, transfer dump file to target server, truncate the table, and import it.
  6. Start the post process.


Author: Mark Bochinski, Senior SharePlex DBA, LeadThem Security





Posted by bc-admin | in SharePlex | Comments Off on SharePlex – Repairing a Table with Copy

SharePlex Compare and Repair

Jan. 2nd 2014


The COMPARE and REPAIR commands are essential components of the Shareplex toolset. The COMPARE command, started on the source system, will compare one table with the corresponding table on the target. The COMPARE USING <config file name> command will compare the entire list of tables in the config file. The COMPARE command creates one log file on the source and two files on the target, one log file and one SQL file. The log file records the steps taken and errors if they occur. The SQL file contains comments plus any SQL statements needed to bring the table back in sync. However, these SQL statements are not executed. During the execution of the COMPARE command, a brief exclusive table lock is required on the source system. The table is immediately unlocked once Shareplex starts reading the table. However, on the target system the exclusive table lock is held for the duration of the compare on that table. This prevents the table from being modified during the compare. The REPAIR command works identically to the COMPARE command with the exception that it does execute the SQL statements and synchronizes the OOS (out-of-sync) table.

Before starting the COMPARE or REPAIR commands, the TEMP tablespace and the UNDO tablespace may need to be made larger. Also, the undo_retention database parameter may need to be increased. At a bare minimum, the TEMP tablespace will need to be at least as large as the largest table. Depending on the setting of SP_DEQ_THREADS (default 2), the size of the TEMP tablespace would need to be larger than the sum of bytes of the two largest tables. If SP_DEQ_THREADS is set to a larger number, increase the size of the TEMP tablespace accordingly. Similarly the UNDO tablespace may need to be increased. Based on transaction volume and the length of time it takes to compare the largest table increase the size of the UNDO tablespace and increase the undo_retention database parameter to avoid an ORA-1555 Snapshot too old error. Tables with LOBs take much longer to compare or repair than tables without them.

The Shareplex COMPARE and REPAIR commands work as follows. After locking the table, the table is read and sorted in identical fashion on both source and target. If the table is large, it will probably need to be sorted in the TEMP tablespace. As this writes to disk, it will take longer than if it was sorted in RAM. Next, 10000 rows are read on the source and target systems, a UNIX check sum is performed. If the check sums match, the next 10000 rows are read, etc. If the check sums do not match, the COMPARE and REPAIR processes determine which rows are out of synch and creates the SQL statements to repair them. The REPAIR process executes the SQL statements.

Commonly modified COMPARE/REPAIR parameters

SP_DEQ_BATCHSIZE – Default 10000.This parameter determines how many rows are read on source and target before executing the UNIX check sum command. Larger batch sizes increase the processing speed but require more RAM. The range of values is from 1 to 32767.

SP_DEQ_THREADS – Default 2. This parameter controls the number of parallel compare or repair processes. It only impacts the COMPARE USING <config file name> command. A common occurrence when this parameter is set to a high value is multiple large tables comparing at once. If the database has 1000 tables in replication and 20 of them are large, Shareplex will quickly compare the small tables while the large tables will take longer as they sort to the TEMP tablespace. Eventually, many large tables could be comparing at the same time. This can cause a huge load on the OS. Setting SP_DEQ_THREADS larger than the number of available CPUs is unadvisable.

SP_DEQ_SKIP_LOB – Default 1. The default value causes LOBs to be included in the compare/repair process. Setting it to 0 will cause only the non-LOB columns to be included in the compare repair process. This will greatly speed up comparing or repairing LOB tables, especially useful if the LOB columns are never modified after insert.


Author: Mark Bochinski, LeadThem Security Senior SharePlex DBA





Posted by bc-admin | in SharePlex | Comments Off on SharePlex Compare and Repair