SharePlex 11.4 - Administration Guide

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.

Overview of Compare and Repair

Before you use Compare and Repair

How to use the repair and compare commands

Overview of Compare and Repair

Repair out-of-sync data > Overview of Compare and Repair

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.

Note: To understand how hidden out-of-sync conditions can occur, see Understand the Concept of Synchronization.

SharePlex provides the following commands for comparing and repairing out-of-sync data:

compare: Compares an individual source table to its target table or compares a wildcarded set of tables in the same schema.
compare using: Takes input from a file to compare some or all of the tables in the active replication configuration.
repair: Repairs an individual target table or a wildcarded set of tables in the same schema.
repair using: Takes input from a file to repair some or all of the tables in the active replication configuration.

Supported source and targets

Oracle to Oracle, PostgreSQL to PostgreSQL

Overview of the server and client processes

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.

How locks are managed

During a comparison, SharePlex obtains a brief exclusive lock on the source and target tables to get read consistency for row selection. This ensures the consistency of row data while SharePlex is processing it. After lock is released, with read consistent view the rows are read and sorted in an identical fashion on both the source and target. Next, a batch of rows is read, and a checksum is performed. If the checksums match, another batch of rows is processed the same way. If any checksums do not match, the processes determine which rows are out of synchronization, and then they create the SQL statements to repair them. The target table is locked during the repair process to prevent any other process from modifying its data.

If a WHERE clause, targetWHERE, or sourceWHERE options are provided, then only that set of rows matching the condition will be locked. (This applies only to PostgreSQL databases)

Before you use Compare and Repair

Repair out-of-sync data > Before you use Compare and Repair

Before running the compare or repair commands during Oracle to Oracle replication, review these guidelines.

All of the SharePlex processes (Capture, Read, Export, Import, Post) must be running when you run a comparison or repair command.
The tables that you want to compare or repair must be part of an active configuration file.
Uncommitted transactions on a source table prevent the compare and repair processes from obtaining the brief locks they need to obtain read consistency. Make certain that all transactions are committed before you run a comparison or repair.

If a table is large, it will probably need to be sorted in the TEMP tablespace. Before running the compare or repair commands, the TEMP tablespace may need to be made larger. The size depends on the setting of the SP_DEQ_THREADS parameter or the threads option within the command syntax, both of which controls the number of processing threads used by SharePlex on the target. Each thread processes a table. At the default of two threads, the size of the tablespace should be larger than the sum of the sizes of the two largest tables. If you set the number of threads higher, then increase the size of the tablespace to accommodate a proportionate number of the largest tables. However,
The UNDO tablespace may also 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.

Before running the compare or repair commands during PostgreSQL to PostgreSQL replication, review these guidelines.

All of the SharePlex processes (Capture, Read, Export, Import, Post) must be running when you run a comparison or repair command.
The tables that you want to compare or repair must be part of an active configuration file.
Uncommitted transactions on a source table prevent the compare and repair processes from obtaining the brief locks they need to obtain read consistency. Make certain that all transactions are committed before you run a comparison or repair.

If the size of a table row (sum of data in each column) is large, it will likely require more memory, as the PostgreSQL database posts table data to the calling process that invokes the SELECT query. Before running the compare or repair commands, ensure that sufficient memory is available. The size depends on the setting of the SP_DEQ_THREADS parameter or the threads option within the command syntax, and the SP_DEQ_MALLOC value, which determines how many tables are processed at a time and the memory allocated for each thread. The value assigned to the SP_DEQ_MALLOC parameter is divided among the number of running threads.

Compare and repair parameters:

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.

Parameter	Description
SP_DEQ_MALLOC	This parameter controls the 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. The value is divided equally by the number of compare threads to be used, and then the batch size is recalculated based on all column sizes added together.
SP_DEQ_PARRALLISM	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_PG_DECLARE_FETCH_SIZE	This parameter determines whether the driver attempts to return a result set in a single fetch or across multiple fetches. This parameter is applicable only for PostgreSQL database.
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. When the parameter is set to the default of 0, the compare processes include LOBs in their processing. When the parameter is set to 1, only non-LOB columns are compared and repaired. If LOBs are not modified once inserted, you can speed up processing by setting this parameter to 1. Set this parameter on the source system.
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.

How to use the repair and compare commands

Repair out-of-sync data > How to use the repair and compare commands

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.

See for causes and solutions for 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.

When to run a repair

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:

Although the users of the tables are not usually affected by the brief locks that are applied when tables are compared, they are locked out of the target table for the duration of the repair process. For a small table, this might not be disruptive, but for a large table needing extensive repairs, the wait can be significant.
Locks on a target table can reduce posting performance if Post must wait for the repair to finish before it can apply changes to that table and move on to other tables. This increases the latency of the target data and causes operations to accumulate in the post queue. If the objects that Post needs to change are different from those being repaired, the two processes run simultaneously.
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.
If the repair can wait, correct the cause of the problem immediately and then do the repair during non-peak hours.

Replication latency can slow down the compare and repair processing. The message sent from the source to spawn the command processes on the target is sent through the queues along with regular replicated data. Delays caused by a data backlog will delay the spawn message and cause the process on the source to lose its read consistency, which results in errors. If possible, perform comparisons and repairs during off-peak hours.

How to run the compare and repair commands

To get additional information and syntax for the compare and repair commands, see the command documentation in theSharePlex Reference Guide.

製品を選択してください:

サービス向上のために、「Purpose of your Chat（チャットの目的）」を記入してください。

お客様の不具合に推奨されるソリューション

SharePlex 11.4 - Administration Guide

Repair out-of-sync data

Contents

Overview of Compare and Repair

Supported source and targets

Overview of the server and client processes

How locks are managed

Before you use Compare and Repair

How to use the repair and compare commands

When to run a repair

How to run the compare and repair commands