SharePlex Out-of-Sync in Depth

Nov 26, 2024 | Articles | 0 comments

Introduction

SharePlex users often inquire about how the tool detects, processes, and resolves discrepancies between rows in source and target databases. SharePlex is specifically designed to maintain synchronization in replication streams, addressing cases where the data in source and target databases diverges. In this context, two important terms are “out-of-sync”, which applies to any replication setup, and “conflict”, relevant to bi-directional or peer-to-peer replication.

Key Definitions

  1. Out-of-Sync:
    This occurs when the content of a row in the source database does not match the corresponding row in the target database.
  2. Conflict:
    A conflict arises in bi-directional replication when simultaneous conflicting changes are made to the same row in different databases, leading to an out-of-sync condition.

Each replication transaction originates from a single source, regardless of the number of target databases. Additionally, in configurations using named post queues, each post process exclusively handles the tables associated with its respective configuration file.

Out-of-Sync Detection in SharePlex

SharePlex identifies out-of-sync conditions through two methods:

  1. During the Replication Process:
    • SharePlex uses the target database to validate changes.
    • Each replication message includes a WHERE clause to identify the specific row for processing and to detect discrepancies.For example:
      INSERT INTO "DEMO"."DEMO_SRC" ("NAME","ADDRESS","PHONE") VALUES ('CLAY','PROSSER','1234');

      When updating rows without a defined key, SharePlex uses all non-binary columns as a composite key:

      UPDATE DEMO_SRC SET ADDRESS = 'Tacoma' WHERE NAME = 'CLAY' AND ADDRESS = 'PROSSER' AND PHONE = '1234';

      If discrepancies exist (e.g., ADDRESS in the target is TACOMA instead of PROSSER), the target database will return a “Row Not Found” error, and SharePlex will log an out-of-sync condition.

    • Key Columns and Parameters:
      Columns included in the replication stream and WHERE clauses are controlled by parameters such as:

      • SP_OCT_REDUCED_KEY
      • SP_OCT_USE_SUPP_KEYS
      • SP_OPX_REDUCED_KEY
    • Error Handling:
      • Duplicate unique keys trigger a “unique constraint violated” error.
      • Missing rows result in a “row not found” error.
        These errors are logged in event_log and errlog.sql, with replication continuing unless configured otherwise.
  2. Using COMPARE and REPAIR Commands:
    Available for Oracle and PostgreSQL, these commands enable out-of-sync detection and correction by comparing source and target tables.

Addressing Out-of-Sync Scenarios

Behavior Configuration

  • Default Setting: Replication continues despite out-of-sync conditions.
    • SQL statements causing errors are logged in errlog.sql.
    • Set SP_OPO_OUT_OF_SYNC_SUSPEND or SP_OPX_OUT_OF_SYNC_SUSPEND to suspend post-processing upon detection.
  • Oracle-Specific Handling:
    • Entire transactions can be saved for review if SP_OPO_SAVE_OOS_TRANSACTION is enabled.

Corrective Actions

  1. Manual Correction:
    Use SQL statements from logs (errlog.sql or the OOS directory) to synchronize target databases.
  2. Automated Repair:
    Execute the REPAIR command via the SharePlex control interface (sp_ctrl). This synchronizes affected rows automatically.
  3. Post Suspension:
    When OUT_OF_SYNC_SUSPEND is enabled, address discrepancies in the target database before restarting post-processing.

Caution:

Stopping post-processing can increase latency. Ensure discrepancies are resolved promptly or consult support to safely remove transactions from queues.

Hidden Out-of-Sync Conditions

Changes made directly to the target database outside replication streams may cause discrepancies undetected until the same row is updated on the source. Use the COMPARE command to uncover and resolve such conditions.

Summary

SharePlex offers robust tools to detect and resolve out-of-sync conditions in one-way replication setups. By leveraging its parameters, logs, and repair utilities, users can maintain synchronization effectively.

Stay tuned for a follow-up blog exploring SharePlex’s conflict resolution strategies for peer-to-peer and bi-directional replication.

 

About DT Asia

DT Asia began in 2007 with a clear mission to build the market entry for various pioneering IT security solutions from the US, Europe and Israel.

Today, DT Asia is a regional, value-added distributor of cybersecurity solutions providing cutting-edge technologies to key government organisations and top private sector clients including global banks and Fortune 500 companies. We have offices and partners around the Asia Pacific to better understand the markets and deliver localised solutions.

 

How we help

If you need to know more about SharePlex Out-of-Sync in Depth, you’re in the right place, we’re here to help! DTA is Quest Software’s distributor, especially in Singapore and Asia, our technicians have deep experience on the product and relevant technologies you can always trust, we provide this product’s turnkey solutions, including consultation, deployment, and maintenance service.

Click here and here and here to know more: https://dtasiagroup.com/quest/