Chat now with support
Chat with Support

SharePlex 10.2.1 - 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 Set SharePlex parameters Configure data replication Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Configure Replication to maintain a central data store

These instructions show you how to set up SharePlex for the purpose of consolidated replication: replicating from multiple source systems to one central target system. This strategy supports business requirements such as the following:

  • Real-time reporting and data analysis
  • The accumulation of big data into a central datastore/mart or warehouse

Supported sources

Oracle

Supported targets

Oracle and Open Target

Capabilities

This replication strategy supports the following:

  • Identical or different source and target names
  • Use of vertically partitioned replication
  • Use of horizontally partitioned replication
  • Use of named export and post queues

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.

  • No DML or DDL should be performed on the target tables except by SharePlex. Tables on the target system that are outside the replication configuration can have DML and DDL operations without affecting replication.
  • Each source system must replicate a different set of data to the central target. If any source systems replicate the same data to the central target system, it is considered to be active-active replication. For more information, see Configure peer-to-peer replication.

  • If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.

Deployment options

You have two options for deploying SharePlex to replicate from many source systems to one target system.

In either deployment, if any source system cannot make a direct connection to the target system, you can use cascading replication for that route to enable SharePlex to cascade the data an intermediary system that allows connection to the target. For more information, see Configure replication through an intermediary system.

Note: The SharePlex compare and repair commands cannot be used in a cascading configuration.

Deploy with one instance of SharePlex on the target system

You can use one instance of SharePlex to process all incoming data on the target. For each source system, SharePlex creates an Import process on the central target system when replication starts. That, in turn, creates post queues and Post processes for each source-target replication stream, all controlled by one sp_cop process. You can control each source-target stream separately, but the post queues all share the same SharePlex variable-data directory on the target system.

A deployment with a single variable-data directory has the following potential risks:

  • An event that interrupts processing to and from the disk that contains the variable-data directory will affect all replication streams.
  • Any cleanup utilities that you use will affect all of the replication streams, because the cleanup is performed across the entire variable-data directory.
  • A purge config command that is issued on one source system also deletes the data that is replicated from the other source systems, because the purge affects the entire variable-data directory. The use of named post queues eliminates this risk, but adds complexity to the naming, monitoring and management of the SharePlex objects in the deployment.

To use this deployment

  • Install SharePlex in the normal manner, with one port number and one variable-data directory on each system (sources and target).
  • Make certain that when you install SharePlex, you create a database account for SharePlex for each installation.
  • Important! Use the same port number for SharePlex on all systems.

Deploy with multiple instances of SharePlex on the target system

You can deploy multiple instances of SharePlex on the target, one for each source system. A SharePlex instance is composed of the following elements:

  • A unique sp_cop process
  • A unique variable-data directory
  • A unique port number on which sp_cop runs
  • A unique database account that the processes of that instance use to interact with the database.

By running multiple, distinct instances of SharePlex, you can isolate each source-target replication stream from the others. It enables you to:

  • Avoid contention problems that can occur if multiple processes must compete for access to the same queues in a single variable-data directory.
  • Purge one configuration, or clean up and resynchronize one replication stream, while allowing the others to continue processing data.
  • Place the variable-data directories on separate disks so that problems with one disk do not affect the variable-data directories on the other disks.

To use this deployment

Install on the target system first, if possible. This enables you to establish a port number for each variable-data directory, which you can then refer to when you set up SharePlex on the corresponding source system.

Steps on the target system

Select either of the setup options presented in Run multiple instances of SharePlex. These procedures will guide you through the steps to establish independent instances of SharePlex on the target. If you already installed SharePlex on the target, a variable-data directory, database account, and port number already exist. You can dedicate that SharePlex instance to one of the source systems, and then create additional instances on the target per those instructions.

Steps on the source systems

Install one instance of SharePlex on each source system, as directed in the SharePlex Installation Guide. Match the port numbers of those instances to the port numbers of their associated target variable-data directories. If you already installed SharePlex on the source systems, you can change the port numbers as needed. For more information, see Set the SharePlex port number.

Configuration

Create a configuration file on each source system that replicates the objects from that system to the central target. For more information about how to create a configuration file, see Configure data replication.

datasource_specification

   
source_specification target_specification central_host[@db]

where:

  • source_specification is the fully qualified name of a source object (owner.object) or a wildcarded specification.
  • target_specification is the fully qualified name of a target object (owner.object) or a wildcarded specification.
  • central_host is the target system.
  • db is a database specification. The database specification consists of either o. or r. prepended to the Oracle SID, TNS alias, or database name, as appropriate for the connection type. A database identifier is not required if the target is JMS, Kafka or a file.
Example

This example shows data from datasource oraA on hostA and datasource oraB on hostB replicating to oraC on system hostC.

Data from hostA
Datasource:o.oraA    
hr.* hr.* hostC@o.oraC
fin.* fin* hostC@o.oraC
Data from hostB
Datasource:o.oraA    
cust.* hr.* hostC@o.oraC

mfg.*

mfg.* hostC@o.oraC

Recommended target configuration

Each source system in a consolidated configuration sends a discrete data stream that flows to its own Post process on the target. You can assign a unique identifier of your choosing to each source system, and then configure the Post process to include that identifier in each insert or update that it posts on the target.

By identifying rows in this manner, your environment is prepared to support the SharePlex compare and repair commands (which require a source ID) as well as any other work that may require the selection or identification of rows by their source. The compare and repair processes will use the source ID value to select only the rows that are valid for that source.

To configure each Post to write a source ID

  1. Create or alter the target table to include a column named SHAREPLEX_SOURCE_ID. This is the column that will contain the source ID value.

    Note: You can change this name by running the target command with the set metadata option, before continuing further. See the SharePlex Reference Guide for more information.

  2. Choose a unique ID for each of the source systems. Any single alphanumeric string is permitted.
  3. On the target, run sp_ctrl for each Post process.
  4. For each Post process, issue the target command with the set source option. This command sets the source ID that will be posted by that Post process. The following example shows the command for three Post processes:

    sp_ctrl> target sys4 queue Q1 set source east

    sp_ctrl> target sys4 queue Q2 set source central

    sp_ctrl> target sys4 queue Q3 set source west

Configure peer-to-peer replication

These instructions show you how to set up SharePlex for the purpose of maintaining multiple databases, where applications on each system can make changes to the same data, while SharePlex keeps all of the data synchronized through replication. This is known as peer-to-peer, or active-active, replication. In this strategy, the databases are usually mirror images of each other, with all objects existing in their entirety on all systems. Although similar in benefit to a high-availability strategy, the difference between the two is that peer-to-peer allows concurrent changes to the same data, while high availability permits changes to the secondary database only in the event that the primary database goes offline.

This strategy supports the following business requirements:

  • Maintain the availability of mission-critical data by operating multiple instances in different locations.
  • Distribute heavy online transaction processing application (OLTP) loads among multiple points of access.
  • Limit direct access to an important database, while still enabling users outside a firewall to make updates to their own copies of the data.

An example of peer-to-peer replication is an e-commerce company with three identical databases. When users access the application from a web browser, the web server connects to any of those databases sequentially in a round-robin configuration. If one of the databases is unavailable, the server connects to a different available database server. Thus the configuration serves not only as a failover resource, but also as a means of distributing the load evenly among all the peers. Should the company need to produce business reports, user access to one of the databases can be stopped temporarily, and that database can be used to run the reports.

Note: Data changes made in peer-to-peer replication are prevented from looping back from one machine to another because Capture ignores transactions performed on the local system by the Post process.

Peer-to-peer replication is not appropriate for all replication environments. It requires a major commitment to database design that might not be practical when packaged applications are in use. It also requires the development of conflict resolution routines to prioritize which transaction SharePlex posts to any given database if there are multiple changes to the same data at or near the same time.

Supported source-target combinations

Oracle to Oracle

Capabilities

This replication strategy supports the following:

  • Use of named export and post queues

This replication strategy does not support the following:

  • Replication of LOBs. If tables with LOBs are included in replication the LOBs will be bypassed by conflict resolution, causing the potential for data to be out of synchronization.
  • Column mapping and partitioned replication is not appropriate in a peer-to-peer configuration.

Requirements

  • Every table involved in peer-to-peer replication must have a primary key or a unique key with no nullable columns. Each key must uniquely identify the same owner.table.row among all of the databases that will be involved in replication, and the logging of the key columns must be enabled in the database. See additional requirements in this topic.
  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.

  • Enable supplemental logging for primary keys, unique keys, and foreign keys on all databases in the peer-to-peer configuration.
  • Enable archive logging on all systems.
  • You must understand the concepts of synchronization. For more information, see Understand the concept of synchronization.

Overview

In peer-to-peer replication, DML changes are allowed on copies of the same tables in different databases, usually on different systems, while SharePlex keeps them all current through replication. If a record is changed in more than one database at (or near) the same time, conflicts can occur, and conflict-resolution logic must be applied to resolve the discrepancy.

What is a conflict?

A conflict is defined as an out-of-sync condition — source and target tables are not identical. You can predict that out-of-sync (conflict) situations will occur when a DML statement constructed by SharePlex fails to execute on a row in the target table because of the following reasons:

  • 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.

What causes a conflict in peer-to-peer replication?

To understand how SharePlex determines a conflict, refer to the following examples of normal and conflict situations. In the examples, three systems (SysA, SysB and SysC) are used.

The following tables are used in the example:

Scott.employee_source

jane.employee_backup

The column names and definitions are identical:

EmpNo number(4) not null,
SocSec number(11) not null,
EmpName char(30),
Job char(10),
Salary number(7,2),
Dept number(2)

The values for both tables in a synchronized state are:

EmpNo (key) SocSec EmpName Job Salary Dept
1 111-22-3333 Mary Smith Manager 50000 1
2 111-33-4444 John Doe Data Entry 20000 2
3 000-11-2222 Mike Jones Assistant 30000 3
4 000-44-7777 Dave Brown Manager 45000 3
Example of peer-to-peer replication without a conflict
  1. At 9:00 in the morning, UserA on SysA changes the value of the Dept column to 2, where EmpNo is 1. SharePlex replicates that change to SysB and SysC, and both databases remain synchronized.
  2. At 9:30 that same morning, UserB on SysB changes the value of Dept to 3, where EmpNo is 1. SharePlex replicates that change to SysA and SysC, and the databases are still synchronized.

Now the row looks like this:

EmpNo (key) SocSec EmpName Job Salary Dept
1 111-22-3333 Mary Smith Manager 50000 3
Example of peer-to-peer replication with an UPDATE conflict
  1. At 11:00 in the morning, UserA on SysA updates the value of Dept to 1, where EmpNo is 1. At 11:02 that morning, the network fails. Captured changes rest in the export queues on all systems.
  2. At 11:05 that morning, before the network is restored, UserB on SysB updates the value of Dept to 2, where EmpNo is 1. The network is restored at 11:10 that morning. Replication data transmission resumes.
  3. When SharePlex attempts to post the change from UserA to the database on SysB, it expects the value in the Dept column to be 3 (the pre-image), but the value is 2 because of the change made by UserB. Because the pre-images do not match, SharePlex generates an out-of-sync error.
  4. When SharePlex attempts to post the change from UserB to SysA, it expects the value of the column to be 3, but the value is 1 because of the change made by UserA. SharePlex generates an out-of-sync error.
  5. When SharePlex attempts to post the changes made by UserA and User B to the database on SysC, both of those statements fail because the pre-images do not match. SharePlex generates an out-of-sync error.

Note: For more information, see Appendix A: Peer-To-Peer Diagram.

Deployment

To deploy peer-to-peer replication, perform the following tasks:

  1. Evaluate the data for suitability to a peer-to-peer environment. Make any recommended alterations. For more information, see Evaluate the data.
  2. Configure SharePlex so that data from each system replicates to all other systems in the peer-to-peer environment. For more information, see Configure replication.
  3. Develop conflict resolution routines that provide rules for how Post handles conflicts. For more information, see Develop conflict resolution routines.
  4. Create a conflict resolution file. SharePlex refers to this file to determine the correct procedure to use when a conflict occurs. For more information, see List the routines in conflict_resolution.SID.

Evaluate the data

To successfully deploy SharePlex in a peer-to-peer configuration, you must be able to:

  • isolate keys
  • prevent changes to keys
  • control sequence generation
  • control trigger usage
  • eliminate cascading deletes
  • designate a trusted host
  • define priorities

These requirements must be considered during the architectural phase of the project, because they demand cooperation with the application. Consequently, many packaged applications are not suitable for a peer-to-peer deployment because they were not created within those guidelines.

Following are more detailed explanations of each of the requirements.

Keys

The only acceptable key in peer-to-peer replication is a primary key. If a table has no primary key but has a unique, not-NULL key, you can convert that key to a primary key. LONG columns cannot be part of the key.

If you cannot assign a primary key, and you know all rows are unique, you can create a unique index on all tables.

The primary key must be unique among all of the databases in the peer-to-peer replication network, meaning:

  • it must use the same column(s) in each corresponding table in all databases.
  • key columns for corresponding rows must have the same values.

The primary key must be created to contain enough information about a row so there can be no question about the uniqueness of that row, and so that there will be a conflict if a replicated operation would violate uniqueness.

The primary key value cannot be changed.

Supplemental logging of primary and unique keys must be enabled in the database.

Using only a sequence as the primary key probably will not suffice for peer-to-peer replication. For example, suppose the sample table uses sequences to generate values for key column EmpNo. Suppose UserA gets the next sequence value on SysA and inserts a row for “Jane Wilson.” UserB gets the next sequence value on SysB and also inserts a row for “Jane Wilson.” Even if the sequence numbers are different on each system, so there are no unique key violations on the replicated INSERTs, data integrity is compromised because there are now two entries for “Jane Wilson” in the databases, each with a different key. Subsequent UPDATEs will fail. The solution is to include other unique columns in the key, so that there is enough information to ensure uniqueness and ensure a conflict that can then be resolved through resolution logic.

Sequences

SharePlex does not support peer-to-peer replication of sequences. If the application uses sequences to generate all or part of a key, there must be no chance for the same range of values to be generated on any other system in the peer-to-peer configuration. You can use a sequence server or you can maintain sequences separately on each server and make sure you partition a unique range to each one. Quest recommends using n+1 sequence generation (where n = the number of systems in replication). Depending on the type of application, you can add a location identifier such as the system name to the sequence value in the primary key to enforce uniqueness.

Triggers

DML changes resulting from triggers firing on a source system enter the redo log and are replicated to the target system by SharePlex. If the same triggers fire on the target system, they return out-of-sync errors.

To handle triggers in a peer-to-peer configuration, you can do either of the following:

  • Disable the triggers.
  • Keep them enabled, but alter them to ignore the SharePlex user on all instances in the peer-to-peer configuration. SharePlex provides the sp_add_trigger.sql script for this purpose. This script puts a WHEN clause into the procedural statement of the trigger that tells it to ignore the Post process. For more information, see Set up Oracle database objects for replication.

ON DELETE CASCADE constraints

ON DELETE CASCADE constraints can remain enabled on all instances in the peer-to-peer replication configuration, but you must set the following parameters to direct Post to ignore those constraints:

  • SP_OPO_DEPENDENCY_CHECK parameter to 2
  • SP_OCT_REDUCED_KEY parameter to 0
  • SP_OPO_REDUCED_KEY parameter to 0 (although in other replication scenarios this parameter can be set to different levels, it must be set to 0 in a peer-to-peer configuration)

Balance values maintained by using UPDATEs

Applications that use UPDATE statements to record changes in quantity, such as inventory or account balances, pose a challenge for peer-to-peer replication. The following example of an online bookseller explains the reason why.

The bookseller’s Inventory table contains the following columns.

Book_ID (primary key)

Quantity

Suppose the following sequence of events takes place:

  1. A customer buys a book through the database on one server. The quantity on hand reduces from 100 books to 99. SharePlex replicates that UPDATE statement to the other server. (UPDATE inventory SET quantity = 99 WHERE book_ID = 51295).
  2. Before the original UPDATE arrives, another customer buys two copies of the same book on another server (UPDATE inventory SET quantity = 98 WHERE book_ID = 51295), and the quantity on that server reduces from 100 books to 98.
  3. When the Post process attempts to post the first transaction, it determines that the pre-image (100 books) on the first system does not match the expected value on the second system (it is now 98 as a result of the second transaction). Post returns an out-of-sync error.

A conflict resolution procedure could be written, but how would the correct value be determined? The correct value in both databases after the two transactions should be 97 books, but no matter which of the two UPDATE statements is accepted, the result is incorrect.

For this reason, peer-to-peer replication is not recommended for applications maintaining account or inventory balances using UPDATEs. If you can use a debit/credit method of maintaining balances, you can use INSERT statements (INSERT into inventory values “n”,...) instead of UPDATE statements. INSERT statements do not require a before-and-after comparison with a WHERE clause, as do UPDATE statements.

If your application must use UPDATE statements, you can write a conflict resolution procedure to determine the absolute (or net) change resulting from different UPDATE statements on different systems. For example, in the case of the preceding online bookseller example, when the first customer’s purchase is replicated to the second system, the following conflict resolution procedure fires:

if existing_row.quantity <> old.quantity then old.quantity - new.quantity = quantity_change; update existing_row set quantity = existing_row.quantity - quantity_change;

The conflict resolution logic tells SharePlex that, if the quantity value of the existing row in the target database (98) does not equal the old value (pre-image of 100), then subtract the new value (the replicated value of 99) from the pre-image to get the net change (1). Then, issue an UPDATE statement that sets the Quantity column to 98-1, which equals 97.

When the second user’s change is replicated to the first system, the same conflict resolution procedure fires. In this case, the net change (pre-image of 100 minus the new value of 98) is 2. The UPDATE statement on this system also results in a value of 97, which is 99 (the existing row value after the first customer’s purchase) minus the net change of 2. The result of this procedure’s logic is that the Quantity columns on each system are updated to 97 books, the net effect of selling three books.

The following example illustrates this concept using an account balance within a financial record:

account_number (primary key)

balance

  1. Suppose a row (an account) in the example table has a balance of $1500 on SysA. CustomerA makes a deposit of $500 on that system. The application uses an UPDATE statement to change the balance to $2000. The change is replicated to SysB as an UPDATE statement (such as UPDATE...SET balance=$2000 WHERE account_number=51295).
  2. Before the change arrives, CustomerA’s spouse makes a withdrawal of $250 on SysB, and the application updates the database on that system to $1250. When CustomerA’s transaction arrives from SysA and Post attempts to post it to SysB, there is a conflict, since the pre-image from the source system is $1500, but the pre-image on the target is $1250 because of the spouse’s transaction — not a match.

You can write a conflict resolution routine to accommodate this kind of transaction by calculating the absolute (or net) change in the account, then using that value to resolve the conflict. For example:

if existing_row.balance <> old.balance then old.balance - new.balance = balance_change; update existing_row set balance = existing_row.balance - balance_change;

The result of this procedure would be to update the account balance to $1750, the net effect of depositing $500 and withdrawing $250. On SysB, the routine directs SharePlex to subtract the new (replicated) balance of 2000 from the old balance of 1500 for a net change of -500. The UPDATE statement sets the balance value to 1250 - (-500) = 1750, the correct value.

On SysA, the replicated value of 1250 is subtracted from the old balance of 1500 to get the net change of 250. The UPDATE statement subtracts that value from the existing balance of 2000 to get the correct value of 1750.

Priority

When the environment is established to avoid or resolve conflict when SharePlex searches for the correct row to change, the only remaining conflict potential is on fact data — which change to accept when the values for the same column in the same row differ on two or more systems. For this, your application must be able to accept the addition of timestamp and source columns, with source being the name of the local system for the table.

The following explains how those columns play a vital role when using a conflict resolution routine to establish priority.

Trusted source

You must assign a particular database or server to be the prevailing, or trusted, source for two reasons:

  • The conflict resolution routine has the potential to get quite large and complex the more systems you have. There are bound to be failures that require resynchronization at some point. One of the systems in the configuration must be considered the true source from which all other systems will be resynchronized if necessary.
  • You can write your conflict resolution routines so that operations from the trusted source system take priority over conflicting operations from other systems. For example, changes on the server at corporate headquarters could take priority over the same changes made by a branch office.
Timestamp

It is recommended that you include a timestamp column in the tables and assign priority in the conflict resolution routine to the earliest or latest timestamp. However, the timestamp must not be part of a key, or it will cause conflicts. SharePlex cannot locate rows if a key value changes — and the key value will change if one of the columns is a timestamp.

For timestamp priority to work, you must make sure all of the servers involved agree on the date and time. Tables on servers in different time zones can use Greenwich Mean Time (GMT).

To handle the situation where servers involved are in different time zones, you can specify a 'TIMESTAMP WITH LOCAL TIME ZONE' column in tables to be used by the routine, and make sure that the 'DBTIMEZONE' of databases in peer to peer replication is the same.

The default date format for SharePlex conflict resolution is MMDDYYYY HH24MISS. Tables with default dates must use that format, or conflict resolution 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'

Configure replication

The configuration files on the systems in a peer-to-peer configuration are identical with the exception of the datasource specification and the routing.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following items in the environment. This documentation assumes three systems, but there can be more.

  • hostA is the first system.

  • hostB is the second system.
  • hostC is the third system.
  • ownerA.object is the fully qualified name of an object on hostA or a wildcarded specification.
  • ownerB.object is the fully qualified name of an object on hostB or a wildcarded specification.
  • ownerC.object is the fully qualified name of an object on hostC or a wildcarded specification.
  • oraA is the Oracle instance on hostA.
  • oraB is the Oracle instance on hostB.
  • oraC is the Oracle instance on hostC.

Important!

Configuration on hostA

Datasource:o.oraA

ownerA.object ownerB.object hostB@o.oraB
ownerA.object ownerB.object hostB@o.oraB
ownerA.object ownerC.object hostC@o.oraC
ownerA.object ownerC.object hostC@o.oraC

Note: If all owner names and table names are the same on all systems, you can use a compound routing map for each of these configuration files. For example, the compound routing for replication from hostA is as follows:

Datasource:o.oraA
owner.object owner.object hostB@o.oraB+hostC@o.oraC

Configuration on hostB

Datasource:o.oraB

ownerB.object ownerA.object hostA@o.oraA
ownerB.object ownerA.object hostA@o.oraA
ownerB.object ownerC.object hostC@o.oraC
ownerB.object ownerC.object hostC@o.oraC

Configuration on hostC

Datasource:o.oraC

ownerC.object ownerA.object hostA@o.oraA
ownerC.object ownerA.object hostA@o.oraA
ownerC.object ownerB.object hostB@o.oraB
ownerC.object ownerB.object hostB@o.oraB
Example
Datasource:o.oraA
hr.emp hr.emp hostB@o.oraB
hr.sal hr.sal hostB@o.oraB
cust.% cust.% hostB@o.oraB

Develop conflict resolution routines

To create conflict resolution routines, you write PL/SQL procedures that direct the action of SharePlex when a conflict occurs. Business rules vary widely from company to company, so it is impossible to create a standard set of conflict resolution rules and syntax that apply in every situation. You will probably need to write your own routines. It is good practice to write more than one procedure, such as making site or system priority the primary routine and timestamp a secondary routine. SharePlex invokes one routine after another until one succeeds or there are no more procedures available.

SharePlex provides the following tools that can be used as a basis for your routines:

Important!

  • This documentation provides guidelines, examples and templates to assist you, but do not use them as your own routines.
  • Test your conflict resolution 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.
  • By default, SharePlex does not stop for out-of-sync conditions. If failed attempts at conflict resolution are not resolved, the databases can become more and more out of synchronization. Check the Event Log frequently to monitor for out-of-sync warnings by using the show log command in sp_ctrl. See the SharePlex Reference Guide for more information about show log and other SharePlex commands.
  • Updates are occasionally made to the conflict resolution logic, so refer to the Release Notes and documentation for your version of SharePlex for any additional information that augments or supersedes these instructions.

How to write a routine using the SharePlex generic interface

SharePlex provides a generic conflict resolution PL/SQL package that can be used to pass information to and from the procedural routines that you write.

Note: Custom routines are supported for Oracle to Oracle source-target combinations only.

Before you get started, understand the following guidelines:

  • 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, see Configure data transformation.
  • Conflict resolution cannot be used for DDL changes.
  • Any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.
  • Conflict resolution does not support changes to LONG or LOB columns.

Note: If you ran the SharePlex conflict resolution demonstration in the SharePlex Installation and Setup Guide, you can view a sample generic conflict resolution routine by viewing the od_employee_gen routine that was installed in the database used for the demonstration.

Procedure interface

Follow this template to create your procedure.

(table_info in outsplex.sp_cr.row_typ, col_values insplex.sp_cr.col_def_tabtyp)

where:

  • splex is the SharePlex schema.
  • sp_cr is the name of the package that contains the PL/SQL record and table structures.
  • row_typ is the name of the PL/SQL record that passes in/out variables (see Package definition).
  • col_def_type is the name of the PL/SQL table that stores column information (see col_def_type table).

Package definition

SharePlex defines PL/SQL record and table structures in a public package named sp_cr in the SharePlex database schema. The package uses the following parameters.

CREATE SCHEMA IF NOT EXISTS sp_cr;
CREATE TYPE sp_cr.row_typ AS
(src_host VARCHAR(32),
src_db VARCHAR(32),
src_time VARCHAR(20),
statement_type VARCHAR(6),
source_table VARCHAR(128),
target_table VARCHAR(128),
native_error INTEGER,
sql_state VARCHAR(10)
);
CREATE TYPE sp_cr.col_def_typ AS
(column_name VARCHAR,
datatype VARCHAR,
is_key BOOLEAN,
is_changed BOOLEAN,
old_value VARCHAR ,
new_value VARCHAR
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
 
IN variables

For each row operation that causes a conflict, SharePlex passes this metadata information to your procedure.

Variable Description
src_host The name of the source system (where the operation occurred). It is case-sensitive and is passed using the same case as on the source system, for example SysA. If there are named post queues in use on the target system, this variable consists of the name of the post queue, for example postq1. Note: Maximum length is 32 characters. A host name longer than 32 will be truncated to 32 characters.
src_ora_sid The ORACLE_SID of the source database. It is case-sensitive and is passed in the same case as in the oratab file, Windows Registry or V$PARAMETER table.
src_ora_time The timestamp of the change record in the source redo log.
source_rowid The row ID of the source row. It is passed as a literal within single quotes, for example ‘123456’.
target_rowid The row ID of the corresponding row in the target database. SharePlex obtains the row ID by querying the target database. It is passed as a literal within single quotes, for example ‘123456’. If the row cannot be found using the PRIMARY key, the value is NULL.
statement_type A letter, either I, U or D, indicating whether the operation is an INSERT, UPDATE or DELETE statement.
source_table The owner and name of the source table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp."
target_table The owner and name of the target table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp."
oracle_err

This is different, depending on whether the procedure is being used for conflict resolution or transformation.

Transformation: SharePlex passes a value of 0 for this variable. This variable is only used for conflict resolution.

Conflict resolution: The Oracle error number that caused the conflict.

OUT variables

These variables direct the action of SharePlex based on whether the procedure succeeded or failed).

Variable Description
status

Defines whether or not the procedure succeeded. You must specify a value for this parameter.

  • A value of 0 implies successful execution. It acts differently, depending on whether the procedure is used for conflict resolution or transformation.

    Transformation: Post does not write any SQL. SharePlex does not write any error messages to the Event Log when transformation succeeds. It continues its processing by reading the next replicated operation in the post queue.

  • Conflict resolution: A value of 0 directs SharePlex to proceed with the SQL statement. SharePlex does not write any log entries to the Event Log when conflict resolution succeeds.
  • A value of 1 implies that the procedure was unsuccessful. In this case, the action SharePlex takes depends on what you specified as the action variable.
  • (Transformation only) A value of 7 implies unsuccessful execution and instructs the Post process to stop.
action

Defines the action that you want SharePlex to take. This is different, depending on whether the procedure is used for transformation or conflict resolution.

Transformation: You must specify a value of 0 for this parameter, which directs SharePlex NOT to post the SQL statement. Your transformation routine is responsible for posting the results of the transformation either to the target table or another table. The outcome of this action depends on what you specify for the reporting variable

Conflict resolution: Specifies the action to take as a result of an unsuccessful conflict resolution procedure. You must specify a value for this parameter.

  • A value of 0 directs SharePlex NOT to post the SQL statement. The outcome of this action depends on what you specify for the reporting variable.

    In addition, it directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.

  • The value of 1 is reserved for internal SharePlex use. Do not use it.
  • A value of 2 directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.
reporting

Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter.

  • A value of 0 directs SharePlex NOT to report an error or write the failed SQL statement to the SID_errlog.sql log.
  • Values 1 and 2 are reserved for internal SharePlex use. Do not use them.
  • A value of 3 directs SharePlex to write the failed SQL statement to the SID_errlog.sql log and report an error to the Event Log.

col_def_type table

SharePlex creates a col_def_tabtyp PL/SQL table for each replicated operation. This table stores column information. It is different depending on whether the procedure is used for transformation or conflict resolution.

  • Transformation: For each row operation, SharePlex writes column information to col_def_type.
  • Conflict resolution: For each row operation that causes a conflict, SharePlex writes column information to col_def_tabtyp.

All fields are passed by SharePlex to your routine, although not all will have values if SharePlex cannot locate the row.

Following is the data type that is used to populate the col_def_tabtyp table.

CREATE SCHEMA IF NOT EXISTS sp_cr;
CREATE TYPE sp_cr.row_typ AS
(src_host VARCHAR(32),
src_db VARCHAR(32),
src_time VARCHAR(20),
statement_type VARCHAR(6),
source_table VARCHAR(128),
target_table VARCHAR(128),
native_error INTEGER,
sql_state VARCHAR(10)
);
CREATE TYPE sp_cr.col_def_typ AS
(column_name VARCHAR,
datatype VARCHAR,
is_key BOOLEAN,
is_changed BOOLEAN,
old_value VARCHAR ,
new_value VARCHAR
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
Description of col_def_tabtyp
Column Description
column_name Tells your procedure the name of the column that was replicated from the source table, for example emp_last_name. This value is not case-sensitive.
data type Tells your procedure the data type of the data in the replicated column, for example VARCHAR2. This value is always in capital letters.
is_key Tells your procedure whether or not the column is a key column. If it is a key column, SharePlex passes a value of TRUE. If the column is not part of a key, SharePlex passes a value of FALSE.
is_changed

Tells your procedure whether or not the column value has changed. If it is changed, SharePlex passes a value of TRUE. If the column is not changed, SharePlex passes a value of FALSE.

  • For INSERTs, is_changed is TRUE for non-NULL values, because none of the columns existed in the database. If a NULL value is inserted, is_changed is FALSE.
  • For UPDATEs, is_changed is TRUE for non-key columns. For key columns, is_changed normally is FALSE, but SharePlex will pass a value for a changed key column.

    Conflict resolution only: If a key value also was changed on the target system, SharePlex will not be able to locate the correct row, and conflict resolution could fail.

  • For DELETEs, is_changed is always FALSE, because SharePlex replicates only the key values for a DELETE statement.
old_value

Tells your procedure the old value of the replicated column, before it was changed on the source system. This column is NULL for INSERTs, because the row did not exist in the target database before the INSERT.

Conflict resolution only: This is the pre-image against which SharePlex compared the source and target columns as part of its synchronization check for UPDATEs and DELETEs. If the old value passed by SharePlex does not match the current_value value obtained from the target row, then there is a conflict.

new_value Tells your procedure the new value of the replicated column, as changed on the source system.
current_value Tells your procedure the current value of the column in the target table. If SharePlex cannot locate the target row, the value is NULL.
Example entries in col_def_tabtyp table per operation type

The following tables illustrate the possible outcomes of each type of operation.

INSERT operation
column_name is_changed old_value new_value current_value1 is_key
C1 TRUE NULL bind NULL FALSE
C2 TRUE NULL bind NULL TRUE
C3 FALSE NULL NULL NULL TRUE | FALSE

1 When an INSERT fails, it is because a row with the same PRIMARY key already exists in the target database. SharePlex does not return the current value for INSERTs.

UPDATE operation
column_name is_changed old_value new_value current_value1, 2 is_key
C1 TRUE bind bind NULL | target_value FALSE
C2 FALSE bind NULL NULL | target_value TRUE
C3 TRUE bind bind NULL | target_value TRUE

1 (Conflict resolution) When an UPDATE fails, it is because SharePlex cannot find the row by using the PRIMARY key and the pre-image. If the row cannot be found, SharePlex searches for the row by using only the PRIMARY key. If SharePlex finds the row, it returns the current value for the key column as well as the changed columns. If SharePlex cannot find the row by using just the PRIMARY key, then SharePlex returns a NULL.

2 (Transformation) For an UPDATE, SharePlex cannot locate a row using the PRIMARY key and the pre-images, because the pre-images are different due to transformation. As an alternative, it searches for the row using just the PRIMARY key. If it finds it, SharePlex returns the current value for the key column as well as the changed columns. If it cannot locate the row using just the PRIMARY key, then current_value is NULL

DELETE operation
column_name is_changed old_value new_value current_value1 is_key
C1 FALSE bind NULL NULL TRUE

1 When a DELETE fails, it is because SharePlex could not find the row by using the PRIMARY key. Therefore, SharePlex returns a NULL.

How to use the SharePlex prepared routines

SharePlex provides optional prepared routines for use in conjunction with custom routines. These options can be used with basic and generic conflict resolution formats. There are no limitations on column types.

Supplemental logging of primary and unique keys must be enabled in the database.

Considerations

Review the following considerations before implementing SharePlex prepared routines.

!UpdateUsingKeyOnly

This routine works for UPDATE operations. It provides conflict resolution that relies solely on the key value of the changed row.

Note: This routine can be used only with an Oracle to Oracle source-target combination.

Supported source-target: This routine can be used only with Oracle to Oracle source-target combinations.

Normally, when SharePlex builds a SQL statement to post data, the WHERE clause uses both the key and the pre-image of the columns that changed to ensure synchronization. The !UpdateUsingKeyOnly routine directs SharePlex to post the data even though the pre-image values do not match, assuming the keys match.

If appropriate, this routine can be used as the sole routine for UPDATEs, but with the understanding that it does not include logic that assigns priority, such as system or time priority, in case of multiple concurrent UPDATEs. To avoid out-of-sync errors, Quest recommends using !UpdateUsingKeyOnly in conjunction with other, more specific routines, relying on !UpdateUsingKeyOnly as a final option if the custom routines fail.

Important: !UpdateUsingKeyOnly must be the last entry in the list of routines, thus assigning it last priority.

In the following example, when there is a conflict for owner.table1 during an UPDATE, SharePlex calls the two custom routines first (in order of priority) and then calls the !UpdateUsingKeyOnly routine.

owner.table1 u owner.procedure_up_A
owner.table1 u owner.procedure_up_B
owner.table1 u !UpdateUsingKeyOnly

The !UpdateUsingKeyOnly name is case sensitive. It must be typed exactly as shown in these instructions, with no spaces between words. Do not list an owner name with this routine in the configuration file. For more information, see List the routines in conflict_resolution.SID.

For INSERT and DELETE operations, custom logic must be used.

!HostPriority

This prepared conflict resolution routine works for INSERT, UPDATE, and DELETE operations. It provides host-based conflict resolution by assigning priority to the row change that originated on the trusted source system. To define the trusted source, set the SP_OPO_TRUSTED_SOURCE  or SP_OPX_TRUSTED_SOURCE parameter to the name of the source system.

Resolution logic
Operation Resolution Action

INSERT

If the source is the one specified with SP_OPO_TRUSTED_SOURCE or SP_OPX_TRUSTED_SOURCE, convert the INSERT to an UPDATE and overwrite the existing row.

Otherwise, discard the change record and do nothing to the target row.

UPDATE

(Oracle only) If the source is the one specified with SP_OPO_TRUSTED_SOURCE, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause. Otherwise, discard the change record and do nothing to the target row.

DELETE Ignore the out-of-sync error and do nothing to the target row.
Syntax in conflict resolution file
owner.table   {I | U | D}   !HostPriority

For more information, see List the routines in conflict_resolution.SID.

!MostRecentRecord

This prepared routine works for INSERT, UPDATE, and DELETE operations. It provides time-based conflict resolution by assigning priority to the most recent row change, as determined by a timestamp.

Note: This routine can be used only with an Oracle to Oracle source-target combination.

To capture the timestamp, tables using this routine must have a non-NULL timestamp column that is updated with every INSERT and UPDATE on the table. If the timestamp column in the DML, or in the existing row, is NULL, this routine cannot resolve the conflict.

This routine requires the SP_OCT_REDUCED_KEY parameter to be set to 0 on the source system, so that all of the pre-image values of UPDATES are available to the Post process.

Resolution logic
Operation Resolution Action

INSERT

and

UPDATE

  • If the timestamp of the replicated operation is greater than the timestamp of the row in the target, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause.
  • If the timestamp of the replicated operation is less than or equal to the timestamp of the row in the target, discard the change record and do nothing to the target row.
DELETE Ignore the conflict (out-of-sync message).
Syntax in conflict resolution file
owner.table   {I | U | D}   !MostRecentRecord(col_name)

Where col_name is the timestamp column to be used by the routine.

See List the routines in conflict_resolution.SID.

!LeastRecentRecord

This prepared routine works for INSERT, UPDATE, and DELETE operations. It provides time-based conflict resolution by assigning priority to the least recent row change, as determined by a timestamp.

Note: This routine can be used only with an Oracle to Oracle source-target combination.

To capture the timestamp, tables using this routine must have a non-NULL timestamp column that is updated with every INSERT and UPDATE on the table. If the timestamp column in the DML, or in the existing row, is NULL, this routine cannot resolve the conflict.

Resolution logic
Operation Resolution Action

INSERT

and

UPDATE

  • If the value of the timestamp column of the replicated operation is greater than or equal tothe timestamp column of the row in the target, discard the replicated operation and do nothing to the target row.
  • If the timestamp column of the replicated operation is less than the timestamp column of the row in the target, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause.
DELETE Ignore the conflict (out-of-sync message).
Syntax in conflict resolution file
owner.table   {I | U | D}   !LeastRecentRecord(col_name)

Where col_name is the timestamp column to be used by the routine.

See List the routines in conflict_resolution.SID.

List the routines in conflict_resolution.SID

After you create the conflict resolution procedure(s), construct the conflict resolution file. This file tells SharePlex which procedures to use for which objects and operation types, and in which order.

Where to find the conflict resolution file

A blank conflict_resolution.SID file, where SID is the ORACLE_SID of the target instance, was included in the data sub-directory of the SharePlex variable-data directory when SharePlex was installed. Use the file on the target system.

If this file does not exist, you can create one in ASCII format in an ASCII text editor. It must be named conflict_resolution.SID, where SID is the ORACLE_SID of the target instance. Note: the SID is case-sensitive.

Important! There can be only one conflict_resolution.SID file per active configuration.

How to make entries in the conflict resolution file

Use the following template to link a procedure to one or more objects and operation types.

owner.object {i | u | d | iud} owner.procedure

where:

  • owner.object is the owner and name of a target object, or a wildcarded entry. (See Syntax rules)
  • i| u | d is the type of operation that creates the conflict that is resolved with the specified procedure. You can specify any or all operation types, for example id or iud. Upper or lower case are both valid.
  • owner.procedure is the owner and name of the conflict resolution procedure that will handle the specified object and operation type.

Syntax rules

  • There must be at least one space between the object specification, the operation type specification, and the procedure specification.
  • You can use the LIKE operator and a SQL wildcard (%) to specify multiple objects by using a search string. (See the Example.)
  • You can use an underscore (_) to denote a single-character wildcard. For table names that contain an underscore character (for example emp_sal), SharePlex recognizes the backslash (\) as an escape character to denote the underscore as a literal and not a wildcard, for example: like:scott.%\_corp\_emp. If you are not using the LIKE operator, the backslash escape character is not required if an object name contains an underscore.
  • The order in which you list the procedures in the conflict resolution file determines their priority of use (in descending order). If you list a table-specific procedure, SharePlex uses it before procedures that are specified with a wildcarded object name.
  • You a comment line anywhere in the file. Start a comment line with the pound symbol (#).

Example conflict resolution file

scott.sal IUD scott.sal_cr
like:scott.%\_corp\_emp IUD scott.emp_cr1
like:scott.%\_corp\_emp IUD scott.emp_cr2
like:scott% IUD scott.emp_cr3
scott.cust U scott.sal_cr

How it works:

  • The scott.sal_cr routine is used for the scott.sal table before the scott.emp_cr1 procedure is used for that table.
  • The scott.emp_cr1 procedure is used before the scott.emp_cr2 procedure for all tables meeting the search criteria, and so forth.
  • For scott.cust, a procedure is called for UPDATEs before the other routines are used for all operations.

How to specify SharePlex prepared routines in the conflict resolution file

To use the SharePlex prepared routines for all tables in the replication configuration, use the !DEFAULT parameter instead of specifying an owner and object name.

A custom routine takes priority over a SharePlex prepared routine. A prepared routine is used only if the custom routine fails. This is true regardless of the order in which the !DEFAULT-associated routine appears in the file.

The !DEFAULT parameter is case-sensitive. It must be typed in all capital letters.

In the following example, the !UpdateUsingKeyOnly procedure is used for UPDATEs and DELETEs for all tables, including james.table1, although the user-defined procedures listed for james.table1 take precedence.

!DEFAULT U !UpdateUsingKeyOnly
!DEFAULT D !UpdateUsingKeyOnly
james.table1 U james.procedure_upd
james.table1 I james.procedure_ins
james.table1 D james.procedure_del

How to change the conflict resolution file while replication is active

You can change the conflict resolution file any time during replication to add and remove tables and procedures. After you change the conflict resolution file, stop and re-start the Post process.

Log information about resolved conflicts

You can configure the Post process to log information about successful conflict resolution operations if you are using the SharePlex prepared routines. This feature is disabled by default.

Note: This feature can be used only with Oracle to Oracle source-target combinations.

To enable the logging of conflict resolution

  1. Run sp_ctrl on the target system.
  2. Issue the following command:

    sp_ctrl> set param SP_OPO_LOG_CONFLICT {1 | 2}

    • A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table.

      Note: A setting of 1 will not update the columns EXISTING_TIMESTAMP and TARGET_ROWID (when existing data is not replaced) in the SHAREPLEX_CONF_LOG table.

    • A setting of 2 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table with Post query for additional meta data.

      Using LeastRecentRecord or MostRecentRecord prepared routines Post will query the target database for the timestamp column of the existing record. The query result is logged into the EXISTING_TIMESTAMP column of the SHAREPLEX_CONF_LOG table.

      For any prepared routines, on rows that aren't replaced by the incoming record, Post will query the TARGET_ROWID of the existing row that could have been replaced. Otherwise the ROWID of the existing row will not be logged.

      Note: A setting of 2 may affect the performance of Post as a result of making the query.

  3. Restart Post.

Post logs the information to a table named SHAREPLEX_CONF_LOG. The following describes this table.

Column Column Definition Information that is logged
CONFLICT_NO NUMBER NOT NULL The unique identifier of the resolved conflict. This value is generated from the shareplex_conf_log_seq sequence.
CONFLICT_TIME TIMESTAMP DEFAULT SYSTIMESTAMP The timestamp of the conflict resolution
CONFLICT_TABLE VARCHAR2(100) The name of the target table that was involved in the conflict
CONFLICT_TYPE VARCHAR2(1) The type of conflict, either I for insert, U for update, or D for delete
CONFLICT_RESOLVED VARCHAR2(1) NOT NULL

Indicator of whether the conflict was resolved or not.

Y = yes, the conflict was resolved

N = no, the conflict was not resolved. Unresolved conflicts are logged to the ID_errlog.sql file, where ID is the source database identifier.

TIMESTAMP_COLUMN VARCHAR2(50) The name of the column that contains the timestamp that Post compared to determine which record was most recent.
INCOMING_TIMESTAMP DATE The timestamp that the row was inserted, updated, or deleted on the source system
EXISTING_TIMESTAMP DATE The current timestamp of the row in the target database. This applies only if the SP_OPO_LOG_CONFLICT parameter is set to 2, which directs Post to query the target database to get this value.
PRIMARY_KEYS VARCHAR2(4000) The names of the primary key columns
MESSAGE VARCHAR2(400)

A message that states which row won in the conflict. The row that wins depends on which conflict resolution routine was used. For example, the following message is returned when the !MostRecentRecord routine is used and the most recent record is the source record:

Incoming timestamp > existing timestamp. Incoming wins, overwrite existing.

If the target record was the most recent one or has the same timestamp as the source record, then the message would be:

Incoming timestamp <= existing timestamp. Existing wins, discard incoming.

SQL_STATEMENT LONG The final SQL statement that got executed as a result of the conflict resolution
CONFLICT_CHECKED VARCHAR2(1) Indicates whether or not someone reviewed the conflict. The default is N for No. The person who reviews the conflict can change this value to Y.

Configure Replication through an intermediary system

These instructions show you how to set up cascading replication, also known as multi-tiered replication. This strategy replicates data from a source system to an intermediary system, and then from the intermediary system to one or more remote target systems.

Cascading replication can be used to support various replication objectives as a workaround in such conditions as the following:

  • Your replication strategy exceeds the 1024 routes that are allowed directly from a given source system: You can send data to the intermediary system and then broadcast to the additional targets from there.
  • The source has no direct connection to the ultimate target, because of firewall restrictions or other factors. You can cascade to a system that does allow remote connection from the source system.

To use a cascading strategy, the source machine must be able to resolve the final target machine name(s), although the ability to make a direct connection is not required.

Supported sources

Oracle

Supported targets

Oracle

Oracle and Open Target (final target)

Capabilities

This replication strategy supports the following:

  • Replication to one or more target systems
  • Identical or different source and target names
  • Use of vertically partitioned replication
  • Use of horizontally partitioned replication
  • Use of named export and post queues

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.

    Important! Create the same SharePlex user on all systems if you will be using SharePlex to post to a database on the intermediary system.

  • Disable triggers that perform DML on the target objects.

  • No DML or DDL should be performed on the target tables except by SharePlex. Tables on the target system that are outside the replication configuration can have DML and DDL operations without affecting replication.
  • If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.

DDL Replication Support

DDL replication from source to target through an intermediary system is supported in accordance with the information found in the DDL that SharePlex supports chapter of the Administration Guide, with the following exceptions:

  • DDL initiated on the intermediary system, as opposed to the source, will cause inconsistencies leading to Post errors and should be avoided unless the DDL is synchronized across all systems.
  • All systems must be monitored to ensure that latency or errors on the intermediary system do not cause inconsistencies.

Important! These instructions assume you have a full understanding of SharePlex configuration files. They use abbreviated representations of important syntax elements. For more information, see Configure data replication.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following:

  • source_specification[n] is the fully qualified name of a source object (owner.object) or a wildcarded specification.
  • target_specification[n] is the fully qualified name of a target object or a wildcarded specification.
  • host is the name of a system where SharePlex runs. Different systems are identified by appending a letter to the names, like hostB.
  • db is a database specification. The database specification consists of either o. or r. prepended to the Oracle SID, TNS alias, or database name, as appropriate for the connection type. A database identifier is not required if the target is JMS, Kafka, or a file.

Important!

Deployment options

To cascade data, you have the following options:

  • If there is a database on the intermediary system, you can configure SharePlex to post to that database and then capture the data again to replicate it to one or more remote targets.

  • If there is not a database on the intermediary system, you can configure SharePlex to import, queue, and then export the data to one or more remote targets. There is no Capture process on the system. This is known as a pass-through configuration. It passes the data directly from the source system to the target(s).

Cascade with posting on intermediate system

To use this configuration:

  • SharePlex database accounts must exist on all systems and must be the same name on all systems. This account is usually created when SharePlex is installed. See the SharePlexInstallation Guide for more information.
  • Triggers must be disabled in the intermediary database, as well as on the target system.
  • Oracle DDL replication is not supported from an Oracle database on the intermediary system to the target systems. It is supported only from the source system to the intermediary system.

  • You create two configuration files: one on the source system, and one on the intermediary system.
  • Enable archive logging on the source and intermediary systems in case the redo logs wrap before Capture is finished with them.

Configuration options on source system

This configuration replicates from the source system to the database on the intermediary system.

Note: In this template, hostB is the intermediary system.

datasource_specification

   
source_specification1 target_specification1 hostB@o.SID
source_specification2 target_specification2 hostB@o.SID
Example on source system
Datasource:o.oraA    
hr.emp hr.emp2 hostB@o.oraB
hr.sal hr.sal2 hostB@o.oraB
cust.% cust.% hostB@o.oraB

Note: In this same configuration, you could route data from other source objects directly to other targets, without cascading through the intermediary system. Just specify the appropriate routing on a separate line.

Configuration options on intermediary system

This configuration captures the data from the database on the intermediary system, then replicates it to the target system(s). The tables that were the target tables in the source configuration are the source tables in this configuration. The target can be any supported SharePlex target.

datasource_specification

   
source_specification1 target_specification1 hostC[@db][+...]
source_specification2 target_specification2 hostD[@db][+...]
Example on intermediary system
Datasource:o.oraB    
hr.emp hr.emp2 hostC@o.oraC
hr.sal hr.sal2 hostD@o.oraD+hostE@r.mssE
cust.% cust.% !cust_partitions

Note: The last entry in this example shows the use of horizontally partitioned replication to distribute different data from the sales.accounts table to different regional databases. For more information, see Configure horizontally partitioned replication.

Required parameter setting on intermediary system

(Oracle intermediary database) Set the SP_OCT_REPLICATE_POSTER parameter to 1 if the intermediary database is Oracle. This instructs the Capture process on the intermediary system to capture the changes posted by SharePlex and replicate them to the target system. (The default is 0, meaning that Capture ignores Post activity on the same system.)

In sp_ctrl, issue the following command. The change takes effect the next time Capture starts.

set param SP_OCT_REPLICATE_POSTER 1

Cascade with pass-through on intermediary system

To use this configuration:

  • Create an Oracle instance and an ORACLE_SID specification in the Windows Registry. The database can be empty.

  • DDL replication is not supported.

  • You create one configuration file, which is on the source system.

Configuration options on source system

Note: In this template, hostB is the intermediary system.

datasource_specification

source_specification1 target_specification1 hostB*hostC[@db]
source_specification2 target_specification2 hostB*hostD[@db][+hostB*hostE[@db][+...]
source_specification3 target_specification3 hostB*hostX[@db]+hostY[@db]
  • The hostB*host syntax configures the pass-through behavior.
  • If using a compound routing map where all data passes through the intermediary system first, make certain to use the hostB* component in each target route.
  • You can also use a compound routing map where data from a source object is replicated directly to one target, and also through the intermediary system to another target, as in the third line of this configuration file.
Example
Datasource:o.oraA    
hr.emp hr.emp2 hostB*hostC@o.oraC
hr.emp hr."Emp_3" hostB*hostC@r.mssB
cust.% cust.% hostB*hostD@o.oraD+hostE@o.oraE

Configure Replication to maintain high availability

These instructions show you how to set up SharePlex for the purpose of high availability: replicating to a secondary Oracle database that is a mirror of the source database. This strategy uses bi-directional replication with two SharePlex configurations that are the reverse of each other. The configuration on the secondary (standby) machine remains in an activated state with the Export process on that system stopped in readiness for failover if the primary machine fails.

This strategy supports business requirements such as the following:

  • Disaster recovery
  • Continuous operation of business applications throughout maintenance cycles or mechanical failures

In this strategy, SharePlex operates as follows:

  • Under normal conditions, SharePlex replicates changes from the primary database to the secondary database.
  • When the primary system or database is offline and users are transferred to the secondary system, SharePlex captures their changes and queues the data on that system until the primary system is restored.
  • When the primary system is restored, SharePlex updates it with those changes and then resumes capture and replication from the primary database.

Supported sources

Oracle

Supported targets

Oracle

Capabilities

This replication strategy supports the use of named export and post queues.

Note: Column mapping and partitioned replication is not appropriate in a high availability configuration. Source and target objects can have different names but this makes the management of a high-availability structure more complicated.

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.
  • All objects must exist in their entirety on both systems.
  • The target objects must have the same structure and qualified names as their source objects.
  • Enable archive logging on all systems.

  • Create a script that denies INSERT, UPDATE and DELETE operations to all users except SharePlex.

For failover purposes, the following are required:

  • Make the applications used on the primary system available on the secondary system.
  • Copy non-replicated database objects and critical files outside the instance to the secondary system.
  • Create a script that grants INSERT, UPDATE and DELETE privileges to all users, which can be run during a failover procedure.
  • Create a script that enables constraints on the secondary system to be used during a failover procedure.
  • Develop a failover procedure for relocating users to the secondary system.

Note: If you use an Oracle hot backup to create the secondary instance, keep the script. It can be modified to re-create the primary instance.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following:

  • hostA is the primary system.

  • hostB is the secondary system.
  • ownerA.object is the fully qualified name of an object on hostA or a wildcarded specification.
  • ownerB.object is the fully qualified name of an object on hostB or a wildcarded specification.
  • oraA is the Oracle instance on hostA.
  • oraB is the Oracle instance on hostB.

Important!

Configuration

A high availability configuration uses two configurations that are the reverse of each other. To replicate all objects in the database, you can use the config.sql script to simplify the configuration process. For more information, see Configuration Scripts.

Configuration on the source system (primary system)

Datasource:o.oraA

   
ownerA.object ownerB.object hostB@o.oraB

Configuration on the target system (secondary system)

Datasource:o.oraB

   
ownerB.object ownerA.object hostA@o.oraA

Make the system ready for failover

  1. On the secondary system (the one that will initially be the passive system) run sp_ctrl and then issue the following command to stop the Export process on the secondary system so that nothing accidentally happening on the secondary system (such as a scheduled job changing data) gets replicated back to the primary system. This is the required state of SharePlex on that system until there is a need for a role switch between systems.
  2. Perform initial synchronization and startup. You will activate the source configuration during this procedure. For more information, see Start replication on your production systems.
  3. Making sure the Export process is stopped on the secondary system, activate the configuration on that system. The configuration on the secondary machine remains in an activated state, but the stopped Export process and lack of user activity ensure that the system remains static in readiness for failover.
  4. Monitor the SharePlex instance that is linked to the secondary Oracle instance to make sure no non-SharePlex DDL or DML changes were performed. You can do this as follows: View the status of the export queue on the secondary system using the qstatus command in sp_ctrl. The queue should be empty, because the Capture process on a system ignores the Post process on that system. If there are any messages in the export queue, it means those transactions originated on the secondary system or the SP_OCT_REPLICATE_POSTER parameter was mistakenly enabled. See the SharePlex Reference Guide for more information about SharePlex commands and parameters.
  5. Maintain backups of replication files.

Perform recovery procedures

If a system fails in your high-availability environment, you can move replication to a secondary system and then move it back to the primary system when it is restored. For more information, see Recover replication after Oracle failover .

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating