Chat now with support
Chat with Support

SharePlex 8.6.6 - Administration Guide

About this Guide Conventions used in this guide Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl SharePlex parameters Prepare an Oracle environment for replication Create a configuration file Configure replication to Open Target targets Configure a replication strategy Configure partitioned replication Configure named queues Configure SharePlex to maintain a change history target Replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Handle transactions that contain out-of-sync operations

Set up error handling > Handle transactions that contain out-of-sync operations

You can configure Post to handle out-of-sync conditions. The following explains the default and alternate behaviors.

Default Post handling of out-of-sync errors

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.

Stop on out-of-sync errors

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

If you use this feature, make certain to monitor replication frequently. If Post stops, latency increases and data accumulates in the queues. For more information, see the parameter documentation in the SharePlex Reference Guide.

Roll back the transaction if it generates out-of-sync errors

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.

For more information, see the parameter documentation in the SharePlex Reference Guide.

Transform data

Transform data

This topic contains instructions for using the transformation feature of SharePlex. Transformation enables SharePlex to manipulate data before, or instead of, posting it to a target.

Contents

Overview of transformation

Transform data > Overview of transformation

Transformation directs the Post process to call a PL/SQL procedure (defined as a transformation routine) instead of applying a SQL operation to the target database. Transformation enables replicated data to be manipulated before, or instead of, posting to a target.

For example, if a source table and its target table are dissimilar in construction — like when a person’s first and last name are in one column in the source table but in separate columns in the target table — you can write a transformation routine to convert the data for those columns so that replication succeeds. You can use transformation routines to convert datatypes, units of measurement, or character sets. You can use them instead of database triggers to reduce I/O overhead, and for many other business requirements.

When you specify transformation for a table, Post takes no action on the replicated data. Instead, it passes data values to your transformation routine, enabling you to control both the form and destination of the data with the procedure. You can post to the target table, post to an alternate location, or both. Therefore, when writing your routine, is your responsibility to include in your procedure the necessary SQL operations for posting.

Supported databases

Oracle

Supported replication strategies

Transformation is a convenient way to use SharePlex to transfer data from one table to another without concern for maintaining identical structure or data. This makes it practical for reporting, broadcast, and data mart and warehousing applications.

Transformation is not suitable for peer-to-peer or high-availability replication environments. High availability requires identical databases that are kept synchronized by replication. For peer-to-peer replication SharePlex must be able to detect and resolve conflicts when there are concurrent changes to the same record. When data is transformed, SharePlex cannot compare before and after values to verify synchronization and detect conflicts.

Supported operations

Transformation supports only INSERT, UPDATE and DELETE operations. You can do the following when developing procedures:

  • You can create one procedure for all three operation types, or you can create a procedure for each operation type.
  • You can use one procedure for all tables, or use different procedures for different tables. SharePlex allows this through the use of wildcards to specify the tables.

If a transformation routine is specified for an individual table, and the table also is part of a group of tables for which another routine is specified, only the table-specific routine is used for that table when the associated DML operation occurs.

Considerations when using transformation

Transform data > Considerations when using transformation

Because transformation changes data and because SharePlex does not post the data, transformation changes the behavior of replication. It is a customization of SharePlex processing. Review the following considerations before implementing transformation to ensure that your transformation procedures succeed.

Privileges

Any table that will be accessed through PL/SQL for transformation requires implicitly granted privileges from the owner of the object to SharePlex.

Keys

A PRIMARY or UNIQUE key is required for all tables using transformation. SharePlex locates the target row for UPDATEs and DELETEs by using the key, which enables it to return values to your transformation routine from the target table for UPDATE operations. Do not allow keys to be changed on the target system, or SharePlex will not be able to locate the row to pass values to your routine.

Test your routines

Test your transformation routines before you put them into production to make sure they work as intended, and to make sure that one routine does not counteract another one. When data is transformed, SharePlex cannot compare before and after values to verify synchronization, which is a measure of whether the routines are performing correctly. The only way to confirm synchronization is to use the compare command with the key option. This option restricts the comparison to just the key values and is not a complete confirmation of synchronization. It only detects missing or extra rows. It does not (and cannot) indicate whether values in non-key columns are properly correlated to those in the source columns, because the target data was transformed.

For those reasons, the repair command cannot be used to resynchronize data. You must devise your own resynchronization procedures based on your company’s business rules and the database environment.

Dates

The default date format for SharePlex transformation is MMDDYYYY HH24MISS. Tables with default dates must use that format, or transformation will return errors. Before creating a table with a default date, use the following command to change the date format in SQL*Plus.

ALTER SESSION SET nls_date_format = 'MMDDYYYYHH24MISS'

Other considerations

  • Transformation does not support changes to LOB and LONG columns.
  • The processing overhead for passing data to your procedure, combined with that of executing the procedure itself, degrades overall performance on the target system compared to normal replication and posting.
  • The same PL/SQL package is used for both generic conflict resolution and transformation (its name is sp_cr). Use either generic conflict resolution or transformation for a table, but not both. Transformed tables cannot be compared by SharePlex and conflict resolution cannot succeed. If both are used, SharePlex only calls the transformation routine. If appropriate, you can use generic conflict resolution and transformation for different tables in the same configuration. For more information about conflict resolution, see Configure replication to maintain multiple peer databases.
Related Documents