SharePlex Compare and Repair

02/01/14 3:13 PM


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





Posted by LeadThem Consulting | in SharePlex | Comments Off on SharePlex Compare and Repair

Comments are closed.


LeadThem Consulting
20418 SE Hwy 212
Damascus, OR 97089