Chat now with support
Chat with Support

SharePlex 9.0 - 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 SharePlex to replicate data Configure replication to and from a container database Configure named queues Configure partitioned replication Configure SharePlex to maintain a change history target Configure a replication strategy Configure SharePlex to replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Database specifications in a configuration file

A database specification is required in the following components of the configuration file:

  • the datasource (source data store) specification
  • routing map (target data store and location) specification
Database Database type notation* Database Identifiers
Oracle source or target

o.

Depending on the Oracle database configuration, use one of the following. This is the string that SharePlex will use to connect to the database.

  • The Oracle SID of a regular (non-CDB) Oracle database, as in o.ora12.
  • The tns_alias of a pluggable database (PDB) in an Oracle container database (CDB), as in o.pdb1.
  • The global tns_alias of an Oracle RAC cluster, as in o.rac1. SharePlex connects to an Oracle RAC instance through this tns_alias, which is mapped locally on each node to the Oracle SID of the local Oracle instance. For more information about creating this alias, see Installation and setup for Oracle cluster in the SharePlex Installation Guide.

SQL Server source or target

Other Open Target targets

 r.

Use to specify the name of a SQL Server source database or an Open Target (non-Oracle) target database, as in r.mydb. Important! Use the actual database name. Do not use the ODBC datasource name (DNS) or database instance name. If the database name is case-sensitive, specify it that way.

Oracle change-history target c.

Use in a routing map to specify the Oracle SID, tns_alias, or global RAC tns_alias of an Oracle change history database, as in c.ORA12CH. In this configuration, SharePlex applies all source transactions as INSERTs to the target tables, to maintain a history of every operation performed.

For more information, see Configure replication to maintain a change history target.

* Note: The dot is required.

Target specifications in a configuration file

The following table shows how to specify a target table or non-table target in a configuration file.

Target Target Specification Description
Database table tgt_owner.table

The fully qualified name of a database table. For more information, see How to qualify object names.

Database sequence

tgt_owner.sequence

The fully qualified name of a sequence. For more information, see How to qualify object names

File !file[:tgt_owner.table]

The !file designator directs Post to write change operations to a file in SQL or SML format. The file name is applied internally by SharePlex.

Optionally, you can specify the fully qualified name of a target table if the data will be consumed by a process that ultimately applies it to a database table.

JMS !jms[:tgt_owner.table]

The !jms designator directs Post to write change operations to a JMS queue or topic in XML format. The queue or topic name can be defined by using the target command.

Optionally, you can specify the fully qualified name of a target table if the data will be consumed by a process that ultimately applies it to a database table.

Kafka

!kafka[:tgt_owner.table]

The !Kafka designator directs Post to write change operations to a Kafka topic in XML format. The topic name can be defined by using the target command.

Optionally, you can specify the fully qualified name of a target table if the data will be consumed by a process that ultimately applies it to a database table.

Change history table

!cdc:tgt_owner.table

The !cdc designator directs Post to insert every data change to the table as a new row, rather than overlay the old data with new data. Specify the fully qualified name of the change history table.

For more information, see Configure replication to maintain a change history target.

Routing specifications in a configuration file

The following instructions show you how to build a routing map based on where you want to send the source data. A routing map sends replicated data to the correct target on the correct target system, or systems.

For details about the components of these configurations, see:

Database specifications in a configuration file

Target specifications in a configuration file

Routing to one target

A simple routing map sends replicated data from one source object to one target object.

datasource_specification
src_owner.table tgt_owner.table2 host2[@database_specification]
src_owner.table tgt_owner.table3 host3[@database_specification]

Routing to a cloud service

There are special routing requirements for database targets that are hosted by a cloud service such as EC2 and RDS on Amazon AWS and Marketplace and Azure SQL on Microsoft Azure. Whether the service is Infrastructure as a Service (IaaS) or Platform as a Service (PaaS) makes a difference in how you install and configure SharePlex. The following explains these requirements.

IaaS targets

If replicating to a database target hosted in an IaaS cloud service, specify the full endpoint URL as the target host in the routing map.

datasource_specification
src_owner.table tgt_owner.table2 endpointURL@database_specification
src_owner.table tgt_owner.table3 endpointURL@database_specification

For example, the following routing map routes to an Oracle cloud database on Amazon EC2:

ec2-12-345-678-910.compute-1.amazonaws.com@o.myora

Alternately, you can map the private IP address of the cloud service to a short name in the local hosts file, and then specify that name as the host in the routing map, for example:

shortname@o.myora

PaaS targets

If replicating to a database target hosted in a PaaS cloud service, there are special installation, setup, and routing requirements. Because SharePlex cannot be installed directly on a PaaS cloud server, you must install SharePlex on either the source server or an intermediary server, from which Post connects to the target cloud database. For more information, see Installation and setup for cloud-hosted databases in the SharePlex Installation and Setup Guide.

Routing to multiple targets

A compound routing map sends replicated data from one source object to multiple target objects. It enables you to specify the source and target objects once for all routes, rather than type a separate configuration entry for each route. Only one target specification can be used in a compound routing map, so all of the target objects must be identical as follows:

  • All are of one type: All the same database object type or all a JMS queue or all a JMS topic or all a Kafka topic, or all a file (but no combination of these).
  • All have the same fully qualified name, including any table specifications in a JMS, Kafka, or file target specification.

  • All have identical column mappings or key mappings, if used. For more information about these mappings, see:

NoteS:

datasource_specification
src_owner.table tgt_owner.table

host1[@database_specification]+host2[@database_specification][...]

Routing between objects on the same system

You can replicate between the following:

  • For Oracle, you can replicate between objects that are in the same database or in different databases on the same system. You can replicate between objects that have the same name, so long as their owners are different.
  • For SQL Server, you can replicate between objects in different databases on the same system.

When SharePlex replicates between objects on the same system, it does not create Import and Export processes. You can force SharePlex to create Import and Export processes by using the following routing map. If you do not need the Import or Export processes, omit the host* portion of the routing map.

Configuration with replication to objects in the same or different database on the same system
datasource_specification
src_owner.table tgt_owner.table

host*host[@database_specification]

Routing Limitations

  • By default, SharePlex supports replication to a maximum of 19 direct target systems. That is the maximum number of processes that can read the export queue. To replicate to more than 19 targets, use named export queues. With each additional queue that you add, you can replicate to 19 additional targets. For more information, see Configure named export queues.
  • Each instance of sp_cop on a system permits a maximum of 1024 different routes. This limitation includes each route that uses a different named post queue (see Configure named post queues.) If your replication strategy requires more than 1024 routes, consider using one or more intermediary systems to divide the routes among multiple sp_cop instances. For more information, see Configure replication to share or distribute data.
  • By default, each sp_cop instance allows a total of 25 queues on a system. There will always be one capture queue on a source system and one post queue on a target. Therefore, you can have as many as 24 named export queues on a source system and 24 named post queues on a target system. If a system serves as both a source and target, you will have both a capture queue and a post queue. That allows you to create up to 23 named queues of either type (or a mix of both). If system memory permits, you can change the number of allowed queues by setting the SP_QUE_MAX_QUEUES parameter. See the SharePlex Reference Guide for more information about this parameter.

Configuration examples

These are examples of basic configuration files according to each possible datasource type and target type.

Replicate from a regular Oracle instance to a regular Oracle instance

This configuration applies to on-premise and IaaS cloud deployments. See the System Requirements in the SharePlex Installation and Setup Guide for supported database versions and platforms.

Datasource:o.SID
src_owner.table tgt_owner.table

host@o.SID

Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to target table SCOTT.EMP2 in Oracle instance oraB on target system sysprod.

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP2    sysprod@o.oraB

Replicate from Oracle to target Oracle in PaaS cloud

To replicate from an on-premise or IaaS-based Oracle source to a target Oracle database hosted in a PaaS cloud, the SharePlex target components (Import and Post) must run on the source server or on an intermediary server. Post connects through a remote connection using a tns_alias. To set up this topology, see Installation and setup for cloud-hosted databases in the SharePlex Installation and Setup Guide.

Datasource:o.SID
src_owner.table tgt_owner.table

source_or_intermediary_host@o.SID

Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to target table SCOTT.EMP2 in the PaaS cloud Oracle instance oraB. Post runs on intermediary target system sysprod2.

datasource:o.oraA
SCOTT.EMP     SCOTT.EMP2    sysprod2@o.oraB

Replicate from a regular Oracle instance to an Open Target database

This configuration applies to on-premise and IaaS cloud deployments. See the System Requirements in the SharePlex Installation and Setup Guide for supported database versions and platforms.

Datasource:o.SID
src_owner.table tgt_owner.table host@r.database_name
Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to target table Scott2.Emp2 in Open Target database mydb on target system sys2. The target table is case-sensitive.

Datasource:o.oraA
SCOTT.EMP    "Scott2"."Emp2"    sys2@r.mydb

Replicate from a regular Oracle instance to a file in XML or SQL format

Datasource:o.SID
src_owner.table !file host
Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to a file on target system sysprod.

Datasource:o.oraA
SCOTT.EMP   !file   sysprod

Replicate from a regular Oracle instance to a JMS queue or topic

Datasource:o.SID
src_owner.table !jms host
Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to a JMS queue on target system sysprod.

Datasource:o.oraA
SCOTT.EMP   !jms   sysprod

Replicate from a regular Oracle instance to a Kafka topic

Datasource:o.SID
src_owner.table !kafka host
Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to a Kafka topic on target system sysprod.

Datasource:o.oraA
SCOTT.EMP   !kafka  sysprod

Replicate from and to an Oracle pluggable database (PDB) in a container database (CDB)*

This configuration applies to on-premise and IaaS cloud deployments. See the System Requirements in the SharePlex Installation and Setup Guide for supported database versions and platforms.

Datasource:o.PDBalias
src_owner.table tgt_owner.table

host@o.PDBalias

Example

This example replicates table SCOTT.EMP from an Oracle PDB that uses the tns_alias of aliasA to target table SCOTT.EMP in an Oracle PDB that uses the tns_alias of aliasB on target system sysprod.

Datasource:o.aliasA
SSCOTT.EMP    SCOTT.EMP    sysprod@o.aliasB

* You can also replicate data from an Oracle PDB to any other supported target. For more information, see Configure capture and delivery.

Replicate to maintain a change history target

This configuration applies to on-premise and IaaS cloud deployments. See the System Requirements in the SharePlex Installation and Setup Guide for supported database versions and platforms.

Datasource:{o.SID | r.database}

src_owner.table

!cdc:tgt_owner.table

host@c.SID

Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to change-history target table SCOTT.EMP2 in Oracle instance oraB on target system sysprod.

Datasource:o.oraA
SCOTT.EMP     !cdc:SCOTT.EMP2     sysprod@c.oraB

For more information, see Configure replication to maintain a change history target.

Replicate from a SQL Server source to an Oracle or SQL Server target

This configuration applies to on-premise and IaaS cloud deployments. See the System Requirements in the SharePlex Installation and Setup Guide for supported database versions and platforms.

Datasource:r.database

# Replicate to Oracle target
src_owner.table tgt_owner.table

host@o.SID

# Replicate to SQL Server target
src_owner.table tgt_owner.table

host@r.database

Example

The following example replicates table MSS.EMP from SQL Server database MSS1 to target table MSS.EMP2 in SQL Server database MSS2 on target system sysprod. In this example, the databases are collated for case-sensitivity, so quotes are placed around the names.

Datasource:r."MSS1"
"MSS.EMP"    "MSS.EMP2"    sysprod@r."MSS2"

Replicate from SQL Server to target SQL Server in PaaS cloud

To replicate from an on-premise or IaaS-based SQL Server source to a target SQL Server database hosted in a PaaS cloud, the SharePlex target components (Import and Post) must run on the source server or on an intermediary server. Post connects through a remote connection using ODBC. To set up this topology, see Installation and setup for cloud-hosted databases in the SharePlex Installation and Setup Guide.

Datasource:r.database
src_owner.table tgt_owner.table

source_or_intermediary_host@r.database

Example

The following example replicates table mss.emp from SQL Server database mss1 to target table mss.emp2 in the PaaS cloud database mss2. Post runs on the intermediary system sysprod2. In this example, the databases are not collated for case-sensitivity.

Datasource:r.mss1
mss.emp    mss.emp2    sysprod2@r.mss2
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating