Chat now with support
Chat with Support

SharePlex 12.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 data replication Configure replication to and from a container database 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 Assign SharePlex users to security groups 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 Recover replication after Oracle failover 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

Configuration examples

Configuration Examples by Data Source and Target

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-premises 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-premises 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-premises 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 using SharePlex target system sysprod.

Datasource:o.oraA
SCOTT.EMP   !kafka  sysprod

Replicate from a regular Oracle instance to multiple instances of Kafka using single SharePlex instance

Datasource:o.SID
src_owner.src_table target_owner.target_table source_host:export_queue*target_host:post1_queue@x.kafka
src_owner.src_table target_owner.target_table source_host:export_queue*target_host:post2_queue@x.kafka

Or

Datasource:o.SID
src_owner.src_table target_owner.target_table source_host:export_queue*target_host:post1_queue@x.kafka + source_host:export_queue*target_host:post2_queue@x.kafka

Example

The following example replicates table SCOTT.EMP from Oracle instance oraA to multiple Kafka instances using single SharePlex target system sysprod2.

Datasource:o.oraA  
SCOTT.SCOTT.EMP sysprod:EXP_Q*sysprod2:POST_Q1@x.kafka
SCOTT.EMP SCOTT.EMP sysprod:EXP_Q*sysprod2:POST_Q2@x.kafka

or

Datasource:o.oraA  
SCOTT.SCOTT.EMP sysprod:EXP_Q*sysprod2:POST_Q1@x.kafka+sysprod:EXP_Q*sysprod2:POST_Q2@x.kafka

Target configurations on sysprod2:

Configure the Kafka target with the following parameters to post data to multiple instances of Kafka using single SharePlex instance:

  • sp_ctrl target x.kafka POST_Q1 set kafka broker= host1:9092,host2:9092,host3:9092

  • sp_ctrl target x.kafka POST_Q2 set kafka broker= host4:9092,host5:9092,host6:9092

Note: Hosts 1, 2, and 3 are part of the first Kafka instance, while hosts 4, 5, and 6 are part of the second Kafka instance.

Replicate from Oracle to Kafka using SSL encryption

This configuration is applicable for the Kafka target.

Pre-requisites:

Configure the Kafka target with the following parameters to post data onto Kafka before starting the replication with SSL encryption:

  • sp_ctrl target x.kafka set kafka broker = <kafka-server-hostname>:<Kafka-server-port>
  • sp_ctrl target x.kafka set kafka security.protocol = SSL
  • sp_ctrl target x.kafka set kafka ssl.ca.location = <ca-cert-file-path>
  • sp_ctrl target x.kafka set kafka ssl.certificate.location = <.pem-file-path>
  • sp_ctrl target x.kafka set kafka ssl.key.location = <ssl-key-file-path>
  • sp_ctrl target x.kafka set kafka ssl.key.password = <key-password>
  • sp_ctrl target x.kafka set kafka sasl.mechanisms = PLAIN
  • sp_ctrl target x.kafka set kafka api.version.request = true
Notes:

  • All the values in the <> need to be replaced with actual parameters from the Kafka producer.

  • Before setting the target Kafka replication parameters, you need to first stop Poster, set the parameters, and then start the Poster.

This configuration applies to on-premises 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 !kafka host

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Kafka server using SharePlex target system sysprod

Datasource:o.oraA
SCOTT.EMP    !kafka    sysprod

Replicate from Oracle to Kafka using SASL authentication

This configuration is applicable for the Kafka target.

Pre-requisites:

Configure the Kafka target with the following parameters to post data onto Kafka before starting the replication with SASL authentication.

  • sp_ctrl target x.kafka set kafka api.version.request=true
  • sp_ctrl target x.kafka set kafka sasl.mechanisms=PLAIN
  • sp_ctrl target x.kafka set kafka sasl.username=<username>
  • sp_ctrl target x.kafka set kafka sasl.password=<password>
  • sp_ctrl target x.kafka set kafka security.protocol=SASL_PLAINTEXT

  • sp_ctrl target x.kafka set kafka ssl.endpoint.identification.algorithm = none

  • sp_ctrl target x.kafka set kafka enable.ssl.certificate.verification = false

Notes:

  • All the values in the <> need to be replaced with actual parameters from the Kafka producer.

  • Before setting the target Kafka replication parameters, you need to first stop Poster, set the parameters, and then start the Poster.

This configuration applies to on-premises 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 !kafka host

NOTE: The "Host" value should be the IP address or hostname of the Kafka server (target). Make sure the Kafka server entry is updated in the /etc/hosts file.

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Kafka server using SharePlex target system sysprod.

Datasource:o.oraA
SCOTT.EMP    !kafka    sysprod

Replicate from Oracle to Kafka using Kerberos authentication

This configuration is applicable for the Kafka target.

Pre-requisites:

Configure the Kafka target with the following parameters to post data onto Kafka before starting the replication with kerberos authentication

  • sp_ctrl target x.kafka set kafka sasl.kerberos.keytab = <kerberos-keytab-file>
  • sp_ctrl target x.kafka set kafka sasl.kerberos.kinit.cmd = <kerberos-kinit-cmd>

Note: The {broker.name} property is no longer supported for the sp_ctrl target x.kafka set kafka sasl.kerberos.kinit.cmd = <kerberos-kinit-cmd> parameter.

  • sp_ctrl target x.kafka set kafka sasl.kerberos.min.time.before.relogin = <relogin-time>
  • sp_ctrl target x.kafka set kafka sasl.kerberos.principal = <kerberos-principal>
  • sp_ctrl target x.kafka set kafka sasl.kerberos.service.name = <kerberos-service-name>
  • sp_ctrl target x.kafka set kafka sasl.mechanisms = GSSAPI

  • sp_ctrl target x.kafka set kafka security.protocol = SASL_PLAINTEXT

  • sp_ctrl target x.kafka set enable.ssl.certificate.verification = false

Notes:

  • All the values in the <> need to be replaced with actual parameters from the Kafka producer.

  • Before setting the target Kafka replication parameters, you need to first stop Poster, set the parameters, and then start the Poster.

This configuration applies to on-premises 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 !kafka host

NOTE: The "Host" value should be the hostname of the Kafka server (target). Make sure the Kafka server entry is updated in the /etc/hosts file.

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Kafka server using SharePlex target system sysprod

Datasource:o.oraA
SCOTT.EMP    !kafka    sysprod

Replicate from Oracle to Kafka using mTLS authentication

This configuration is applicable for the Kafka target.

Note: Before setting the target Kafka replication parameters, you need to first stop Poster, set the parameters, and then start the Poster.

This configuration applies to on-premises 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 !kafka host

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Kafka server using SharePlex target system sysprod

Datasource:o.oraA
SCOTT.EMP    !kafka    sysprod

Replicate data from Oracle to Azure Event Hubs

These configurations need to be done for replicating data from source to Azure Event Hubs. SharePlex for Kafka is used to communicate with Azure Event Hubs through the Kafka Event Hubs connectors.

Pre-requisites:

Configure the SharePlex source machine with the following Kafka parameters to post data onto Azure Event Hubs before starting the replication.

  • sp_ctrl Target x.kafka set kafka api.version.request = true
  • sp_ctrl target x.kafka set kafka broker = <Azure Event Hubs namespace>:<Kafka-server-port>
  • sp_ctrl Target x.kafka set kafka sasl.mechanisms = PLAIN
  • sp_ctrl Target x.kafka set kafka sasl.username = $ConnectionString
  • sp_ctrl Target x.kafka set kafka sasl.password =<Primary key generated in Event Hubs namespace>
  • sp_ctrl Target x.kafka set kafka security.protocol = SASL_SSL
  • sp_ctrl Target x.kafka set kafka topic = <Kafka Event Hubs topic generated inside Event Hubs namespace>
Notes:

  • All the values in the <> need to be replaced with actual parameters from Azure Event Hubs.

  • Before setting the target Kafka replication parameters, you need to first stop Poster, set the parameters, and then restart the Poster.

This configuration applies to on-premises 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 !kafka src_hostname

 

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Azure Event Hub using SharePlex target system sysprod.

Datasource:o.oraA
SCOTT.EMP    !kafka    sysprod

Replicate data from Oracle to SQL Server

This configuration applies to on-premises 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 dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the MS SQL Server on host sysprod

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP   sysprod@r.sp_ss

Replicate Data from Oracle to Azure SQL database

This configuration applies to on-premises 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 dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to the Azure SQL database on host sysprod

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP   sysprod@r.azuresqldb

Replicate data from Oracle to PostgreSQL database

This configuration applies to on-premises 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 dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to PostgreSQL target server on host sysprod:

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP   sysprod@r.sp_ss

Replicate data from Oracle to MariaDB

This configuration applies to on-premises 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 dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to MariaDB target server on host sysprod:

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP   sysprod@r.sp_ss

Replicate data from Oracle to MySQL database

This configuration applies to on-premises 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 dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from Oracle instance oraA to MySQL target server on host sysprod:

Datasource:o.oraA
SCOTT.EMP    SCOTT.EMP   sysprod@r.sp_ss

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

This configuration applies to on-premises 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-premises 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

!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 a Change History Target.

Replicate data from Oracle to Oracle using Extended Data Types

These configurations need to be done for replicating data from Oracle to Oracle using extended data types.

Pre-requisite:

Your Oracle database should be supporting the Extended Data Type.

Limitation:

SharePlex does not replicate data with Extended Data Type when target type is SQL, JMS, File, or Kafka.

This configuration applies to on-premises 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 data from Oracle to Snowflake

This configuration applies to on-premises 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_schema.table dst_schema.table dst_hostname

Example:

The following example replicates table scott.emp from Oracle instance oraA to the Snowflake on host sysprod

Datasource:o.oraA
"scott"."emp"    "SCOTT"."EMP"   sysprod@r.dbname

Replicate data from PostgreSQL to PostgreSQL database

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

Datasource:r.dbname
src_schema.table dst_schema.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from a PostgreSQL instance dbnameA to a PostgreSQL target server on host hostB:

Datasource:r.dbnameA
scott.emp    scott.emp   hostb@r.mydb

Replicate data from PostgreSQL to Oracle database

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

Datasource:r.dbname
src_schema.table dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from a PostgreSQL instance dbnameA to an Oracle target server on host hostB:

Datasource:r.dbnameA
"scott"."emp"    "scott"."emp"   hostB@o.mydb

Replicate from a PostgreSQL instance to a Kafka topic

Datasource:r.dbname
src_schema.table !kafka host
Example

The following example replicates table SCOTT.EMP from PostgreSQL instance testdbA to a Kafka topic using SharePlex target system targetHost.

Datasource:r.testdbA
scott.emp   !kafka  targetHost

Replicate data from PostgreSQL to SQL Server

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

Datasource:r.dbname
src_schema.table dst_owner.table dst_hostname

Example:

The following example replicates table SCOTT.EMP from PostgreSQL instance testdbA to MS SQL Server on host sysprod

Datasource: r.testdbA
SCOTT.EMP    SCOTT.EMP   sysprod@r.dbname

Replicate data from PostgreSQL to Snowflake

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

Datasource:r.dbname
src_schema.table dst_schema.table dst_hostname

Example:

The following example replicates table scott.emp from PostgreSQL instance testdbA to Snowflake on host sysprod

Datasource: r.testdbA
"scott"."emp"    "SCOTT"."EMP"   sysprod@r.dbname

Capture from multiple local datasources

You can use one instance of SharePlex to capture from multiple datasources on a system. All of the configurations can be active at the same time.

Note: SharePlex does not support multiple active configuration files for the same datasource, but it does support multiple active configuration files if each replicates a different datasource.

To capture from multiple datasources:

  1. Create a configuration file for the first datasource. In each routing map, include a named export queue. For more information, see Configure Named Export Queues.

  2. Create a configuration file for the second datasource. In each routing map, specify a named export queue, but make certain it is different from any of the queues named in the first configuration file. It is important that data from one datasource does not process through the export queues of the other datasource.

  3. Create additional configurations with dedicated named export queues, if needed.
  4. When you activate the configuration files, use a separate sp_ctrl session for each one. For more information, see How to Activate Multiple Configuration Files.

Use Wildcards to specify multiple objects

You can use wildcard characters to specify multiple objects of a schema in one entry of the configuration file. SharePlex replicates any objects that satisfy the wildcard, except those that you explicitly exclude.

Note: Only object names can be wildcarded. Owner names cannot be wildcarded.

Requirements and limitations of wildcard support

Supported wildcard syntax

SharePlex supports the following SQL wildcards

  • Percent (%) wildcard to specify a string. (See the Use Wildcards to Specify Multiple Objects.)
  • Underscore (_) wildcard to specify a single-character.
  • For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.

Specify wildcarded names in the configuration file

Use this template for help when specifying a wildcarded name in the configuration file.

Configuration with wildcarded object names
datasource_specification
expand src_owner.wildcard_name [not (list)]

tgt_owner.wildcard_name

 routing_map
Description of syntax elements
Component Description
expand

Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all objects that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed.

Note: Leave a space between expand and the start of the source object specification.

src_owner.wildcard_name
  • src_owner is the owner of the source objects. Owner names cannot be wildcarded. If wildcards are used in the owner name, SharePlex assumes that they are part of the owner (schema) name.
  • wildcard_name is the wildcarded name of the source objects.

Rules:

Oracle: The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners.

not (list)

An exclusion list that defines objects to omit from the wildcard expansion. Use this option to exclude objects that you do not want to be replicated.

Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.

  • The not keyword and parentheses are required elements.
  • list is a comma-separated list of tables owned by the same owner, either wildcarded or explicit. Example: not (spo%, gen%, product).

Leave a space before and after the not keyword. A space is allowed after each comma in the list.

Note: If an object that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples.

tgt_owner.wildcard_name
  • tgt_owner is the owner of the target objects.
  • wildcard_name is the wildcarded name of the target objects.

The target specification must be in the form of owner.%. Partially expanded target wildcarded names are not supported, such as owner.tab%.

routing_map Any valid routing map. For more information, see Routing Specifications in a Configuration File

Validate a wildcard specification

To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the objects that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see the SharePlex Reference Guide.

Examples of valid wildcard specifications

Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. Note that SharePlex automatically upshifts the names, so that it actually activates all tables where the table name is like 'PROD%' but not like '%TEMP%'.

Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. For more information, see Configure Named Post Queues.

Datasource:o.sidA	 	 
cust.%        cust.%        hostB@o.oraB
cust.photo    cust.photo    hostB:lobQ@o.oraB

The following are additional examples of valid wildcard specifications

Datasource:o.sidA	 	 
expand scott.%test%    scott.%    sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.%t__t%    fred.%    sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.% not (spo%, gen%, prodct)    scott.%     sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Examples of invalid wildcard specifications

The following example contains a wildcarded schema, which is not permitted.

Datasource:o.sidA	 	 
expand rob%.%test%    scott.%    sysa@o.sidB

The following example contains a partially wildcarded target object name, which is not permitted.

Datasource:o.sidA	 	 
expand scott.%test%    scott.%obj%    sysa@o.sidB

Use Wildcards to Specify Multiple Tables for PostgreSQL

You can use wildcard characters to specify multiple tables of a schema in one entry of the configuration file. SharePlex replicates any tables that satisfy the wildcard, except those that you explicitly exclude.

Note: Only table names can be wildcarded. Schema names cannot be wildcarded.

Requirements and limitations of wildcard support

The schemas that contain wildcarded table names must exist on the source and target before the configuration is activated.

Supported wildcard syntax

SharePlex supports the following PostgreSQL wildcards:

  • Percent (%) wildcard to specify a string. (See the Examples)
  • Underscore (_) wildcard to specify a single-character.
  • For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.

Specify wildcarded names in the configuration file

Use this template for help when specifying a wildcarded name in the configuration file.

Configuration with wildcarded table names
datasource_specification
expand src_schema.wildcard_name [not (list)]

tgt_schema.wildcard_name

 routing_map
Description of syntax elements
Component Description
expand

Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all tables that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit table name, and no wildcard expansion is performed.

Note: Leave a space between expand and the start of the source table specification.

src_schema.wildcard_name
  • src_schema is the schema of the source tables. Schema names cannot be wildcarded. If wildcards are used in the schema name, SharePlex assumes that they are part of the schema name.
  • wildcard_name is the wildcarded name of the source tables.

PostgreSQL: The names of the target tables must be identical to those of the source tables, but the tables may belong to different schemas.

not (list)

An exclusion list that defines tables to omit from the wildcard expansion. Use this option to exclude tables that you do not want to be replicated. Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.

  • The not keyword and parentheses are required elements.
  • list is a comma-separated list of tables owned by the same schema, either wildcarded or explicit. Example: not (spo%, gen%, product).

Leave a space before and after the not keyword. A space is allowed after each comma in the list.

Note: If an table that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples.

tgt_schema.wildcard_name
  • tgt_schema is the schema of the target tables.
  • wildcard_name is the wildcarded name of the target tables.

The target specification must be in the form of schema.%. Partially expanded target wildcarded names are not supported, such as schema.tab%.

routing_map Any valid routing map.

Validate a Wildcard Specification

To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the tables that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see SharePlex Reference Guide.

Examples
Examples of valid wildcard specifications

Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. 

Datasource:r.dbname	 	 
expand scott.prod% not (%temp%)    hal.%    hostB@r.dbname

Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. 

Datasource:r.dbname	 	 
cust.%        cust.%        hostB@r.dbname
cust.photo    cust.photo    hostB:queuename@r.dbname

The following are additional examples of valid wildcard specifications for PostgreSQL to PostgreSQL replication:

Datasource:r.dbname	 	 
expand scott.%test%    scott.%    hostB@r.dbname
Datasource:r.dbname	 	 
expand scott.%t__t%    fred.%    hostB@r.dbname
Datasource:r.dbname	 	 
expand scott.% not (spo%, gen%, prodct)    scott.%     hostB@r.dbname
Datasource:r.dbname	 	 
expand scott.prod% not (%temp%)    hal.%    hostB@r.dbname

The following is an example of valid wildcard specifications for PostgreSQL to Oracle replication:

Datasource:r.dbname	 	 
expand "scott"."%test%"    "scott"."%"    hostB@o.target_dbname

The following is an example of valid partial wildcard specifications for PostgreSQL to SQL Server replication:

Datasource:r.dbname	 	 
expand scott.%test%    scott.%test%     hostB@r.target_dbname
Examples of invalid wildcard specifications

The following example contains a wildcarded schema, which is not permitted.

Datasource:r.dbname	 	 
expand rob%.%test%    scott.%    hostB@r.dbname

The following example contains a partially wildcarded target table name, which is not permitted.

Datasource:r.dbname	 	 
expand scott.%test%    scott.%obj%    hostB@r.dbname
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating