SharePlex 9.2.9 - Installation and Setup for Oracle Source

Understand the concept of synchronization

The concept of synchronization applies mainly to table-to-table replication, where Post performs integrity checks to make certain that only one row in the target matches the row change that is being replicated. It does not apply to file, messaging targets, and change-history targets, which contain a record of every operation replicated by Post, some of which may be identical over time. The Post process does not perform integrity checks on those targets.

Characteristics of synchronized tables

The basic characteristics of synchronized source and target tables are as follows (unless the transformation feature is used).

• If a row exists in the source database, it exists in the target.
• Corresponding columns in source and target tables have the same structure and data types.
• Data values in corresponding rows are identical, including the values of the key.

Ensuring data integrity is the responsibility of the Post process. Post applies a WHERE clause to compare the key values and the before values of the SQL operations that it processes. Post uses the following logic to validate synchronization between source and target tables:

• Post applies a replicated INSERT but a row with the same key already exists in the target. Post applies the following logic:

  • If all of the current values in the target row are the same as the INSERT values, Post considers the rows to be in-sync and discards the operation.
  • If any of the values are different from those of the INSERT, Post considers this an out-of-sync condition.

  Note: You can configure Post so that it does not consider non-key values when posting an INSERT. See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.

• Post applies a replicated UPDATE but either cannot find a row in the target with the same key value as the one in the UPDATE or Post finds the correct row but the row values do not match the before values in the UPDATE. Post applies the following logic:

  • If the current values in the target row match the after values of the UPDATE, Post considers the rows to be in-sync and discards the operation.
  • If the values in the target row do not match the before or after values of the UPDATE, Post considers this an out-of-sync condition.

  Note: You can configure Post so that it returns an out-of-sync message if the current values in the target row match the after values of the UPDATE. See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.

• A DELETE is performed on the source data, but Post cannot locate the target row by using the key. When Post constructs its DELETE statement, it includes only the key value in its WHERE clause. If the row does not exist in the target, Post discards the operation.

Hidden out-of-sync conditions

Post only verifies the integrity of the rows that are being changed by its current SQL operation. It does not verify whether other rows in that table, or in other tables, are out of synchronization in the target database. A hidden out-of-sync condition may not show up until much later, when a change to the affected row is eventually replicated by SharePlex or a discrepancy is detected in the course of using that data.

Example of a detectable out-of-sync condition

Someone logs into the target and updates the COLOR column in the target table from “blue” to “red” in Row1. Then, an application user on the source system makes the same change to the source table, and SharePlex replicates it to the target. In the WHERE clause used by Post, the pre-image for the target table is “blue,” but the current value in the target row is “red.” Post generates an out-of-sync error alerting you to the out-of-sync condition.

Example of a hidden out-of-sync condition

Someone logs into the target and updates the COLOR column in the target table from “blue” to “red” in Row2, but the change is not made to the source table and is not replicated. The two tables are now out-of-sync, but Post does not return an error message, because there is no replication performed on that row. No matter how many subsequent updates are made to other columns in the row (SIZE, WEIGHT), the hidden out-of-sync condition for the COLOR column persists (and users on the target have inaccurate information) until someone updates the COLOR column in the source table. When that change is replicated, only then does Post compare the pre-images and return an error message.

The majority of time, the cause of out-of-sync data is not anything done wrong by replication, but rather DML applied on the target, an incomplete backup restore, or some other hidden out-of-sync condition, which goes undetected until replication affects the row. Solving out-of-sync conditions can be time-consuming and disruptive to user activity. Once replication is started, it is recommended that you:

• Prevent write access to the target tables, so that DML and DDL cannot be applied to them.
• Use the compare command to compare source and target data regularly to verify synchronization and detect hidden out-of-sync conditions. You can use the repair command to repair any out-of-sync rows. For more information about these commands, see the SharePlex Reference Guide.

How SharePlex responds to an out-of-sync condition

You can decide how you want SharePlex to respond to transactions that generate an out-of-sync error:

• The default Post behavior when a transaction contains an out-of-sync operation is to continue processing other valid operations in the transaction to minimize latency and keep targets as current as possible. Latency is the amount of time between when a source transaction occurs and when it is applied to the target. Different factors affect the amount of latency in replication, such as unusually high transaction volumes or interruptions to network traffic.

  Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, where ID is the database identifier. This file is in the log sub-directory of the variable-data directory on the target system.

• You can configure Post to stop when it encounters an out-of-sync condition by setting the following parameter to 1:

  • Oracle targets: SP_OPO_OUT_OF_SYNC_SUSPEND
  • Open Target targets: SP_OPX_OUT_OF_SYNC_SUSPEND

• You can configure Post to roll back and discard a transaction if any operation in that transaction generates an out-of-sync error. The entire transaction is logged to a SQL file, but not applied to the target.You can edit the SQL file to fix the invalid DML and then run the SQL file to apply the transaction. This feature is enabled by setting the SP_OPO_SAVE_OOS_TRANSACTION to 1.