This chapter contains an overview of the SharePlex Compare and Repair feature. SharePlex provides this feature as built-in support for Oracle tables to help you maintain data that is synchronized between the source and target systems.
In addition to regularly monitoring the health and performance of replication, it is good practice to compare the source and target data on a regular schedule to ensure that all of the data is still synchronized. Post detects out-of-sync conditions for the rows that it is processing, but there can be hidden out-of-sync conditions. Examples of these are DML applied on the target or an incomplete backup restore. These conditions can go undetected until Post applies an operation that affects the out-of-sync row. The SharePlex Compare and Repair feature enables you to detect hidden out-of-sync conditions and then repair them.
SharePlex provides the following commands for comparing and repairing out-of-sync data:
The compare and repair commands are always issued on the source system. The command spawns a server process on the source system and then sends a message through the SharePlex queues to spawn a client process on the target system.
The server and client processes then begin communication with each other. Depending on the syntax options included in the command, the processes may be multithreaded on the target. The two processes compare the source and target tables and then write the results to a log file.
During a comparison, SharePlex obtains a brief exclusive lock on the source table to get read consistency for its row selection. On the target system, SharePlex obtains an exclusive lock on the target table and retains the lock for the duration of the comparison of that table. This prevents the table from being modified while SharePlex is processing it.
After locking the tables, the rows are read and sorted in identical fashion on both source and target. Next, a batch of rows is read and a check sum is performed. If the check sums match, another batch of rows is processed the same way. If any check sums do not match, the processes determine which rows are out-of-synchronization and then they create the SQL statements to repair them. If a repair command is issued, SharePlex repairs the rows.
Before running the compare or repair commands, review these guidelines.
The following are commonly modified compare and repair parameters. Do not increase the values unless necessary. For details about these parameters, see their documentation in the SharePlex Reference Guide.
|SP_DEQ_MALLOC||This parameter is used on the source side to control fetch batch size. The batch size controls the number of rows that SharePlex selects at once for comparison. Larger batch sizes increase processing speed but require more memory.|
This parameter manages the select statement Degree of Parallelism hint. The parallelism option of the command overrides this setting.
|SP_DEQ_PART_TABLE_UPDATE||This parameter controls how the repair commands work on Oracle partitioned tables, depending on whether row movement is possible.|
|SP_DEQ_READ_BUFFER_SIZE||This parameter controls the size of the buffer that holds fetched LONG and LOB data and can be adjusted based on available system memory.|
|SP_DEQ_ROW_LOCK_THRESHOLD||This parameter sets a threshold that controls whether SharePlex uses row-level or table-level locking when a where option is used.|
|SP_DEQ_SKIP_LOB||This parameter determines whether or not LOBs are included in the compare/repair processing. If LOBs are not modified once inserted, you can speed up processing by setting this parameter to 0 so that only non-LOB columns are included in the processing.|
|SP_DEQ_TIMEOUT||This parameter sets a queue backlog threshold. High backlogs delay the establishment of a connection between the source and target compare/repair processes. If the backlog meets or exceeds this value, any compare or repair command that is issued on the source will exit and return an error. If this happens, consider running the compare or repair when the system is less busy.|
The recommended procedure for maintaining synchronized data through the comparison and repair commands is to run the compare or compare using command first, then view the results with the repair status command. This command shows any rows that are out-of-sync and the possible cause. Unless the cause of the out-of-sync condition is corrected, replication will go out of synchronization again, even if you repair the rows this time. After the problem is fixed, issue the repair or repair using command.
You can run the repair or repair using command without doing a preliminary comparison. The command performs a comparison first, to identify the out-of-sync rows, and then it repairs those rows. However, the underlying cause of the out-of-sync condition must be corrected to prevent future out-of-sync conditions.
To view the status or results of a comparison, use the compare status command in sp_ctrl.
To view the status or results of a repair, use the repair status command in sp_ctrl.
The best time to repair a target table depends on its size, the cause of the problem, the extent of out-of-sync rows, and how long you are willing to tolerate users being locked out. Before you initiate a repair, consider the following:
If you must repair a table immediately, but cannot tolerate locks or replication latency, you can use the where option to limit the repair to certain rows. An alternative is to use the key option, but this option may cause the repair to miss some out-of-sync rows.
To get additional information and syntax for the compare and repair commands, see the command documentation in the SharePlex Reference Guide.