Chat now with support
Chat with Support

SharePlex 8.6.6 - Upgrade Guide

Configure replication through an intermediary system

These instructions show you how to set up cascading replication, also known as multi-tiered replication.This stragegy 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 targets

Oracle (intermediary system)

Oracle and Open Target (final target)


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


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

  • Prepare the environment for replication. See Prepare an Oracle environment for replication.
  • 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.

Important! These instructions assume you have a full understanding of SharePlex configuration files. They use abbreviated representations of important syntax elements. See Create configuration files for more information.

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


Deployment options

To cascade data, you have the following options:

  • If there is an Oracle 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. To disable the replication of DDL on the intermediary system, disable the SP_OCT_REPLICATE_DDL and SP_OCT_REPLICATE_ALL_DDL parameters.
  • 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.


source_specification1 target_specification1 hostB@o.SID
source_specification2 target_specification2 hostB@o.SID
Example on source system
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.


source_specification1 target_specification1 hostC[@db][+...]
source_specification2 target_specification2 hostD[@db][+...]
Example on intermediary system
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

Set the SP_OCT_REPLICATE_POSTER parameter to 1. 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.


Cascade with pass-through on intermediary system

To use this configuration:

  • Create an Oracle instance and an ORACLE_SID specification in the oratab file (Unix and Linux systems) or 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.


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.
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
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating