SharePlex 9.2.6 - Installation and Setup for Oracle Source

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 or SQL Server (intermediary system)

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!

• Configure data replication.

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.

Example on source system

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.

Example on intermediary system

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

Changes posted by SharePlex to a SQL Server intermediary database are automatically captured and sent to the target systems.

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.

  Or...

  Create SQL Server instance and install SQL Server replication. A database is not needed.

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

• 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