SharePlex 9.2.6 - Administration Guide

Map source columns to target columns

When source and target column names are different, you can specify an explicit column mapping in the configuration file, to ensure that Post applies row data to the correct target columns.

Guidelines for using column mapping

• To use column mapping, you must map every column in a source table to a column in the target table, even if only some source and target names are different. When some columns are mapped but not others, the entry is considered to be a column partition for vertically partitioned replication, and data changes for non-listed columns are not replicated.

• If the spelling case is different between the source and target column names, enclose them within quotes.
• You can use horizontally partitioned replication and column mapping for the same source table, but you cannot combine column mapping with vertically partitioned replication.
• A target table can have more columns than the source table, but there must at least be a target column for every source column.
• ALTER TABLE to add a column to a table that is configured with column mapping is not supported.

Configure column mapping

The following syntax creates a column map. For more information, see Configure data replication.

Configuration example

This example contains no case-sensitive columns.

This example contains case-sensitive columns.

Build a configuration file using a script

SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.

• config.sql: configure all tables and optionally all sequences in the database.
• build_config.sql: configure multiple or all tables in a schema

Supported databases

Oracle

Use config.sql

The config.sql script enables you to build a configuration that lists all of the tables, and optionally all of the sequences, in all of the schemas of a database. This script saves time when establishing a high-availability replication strategy or other scenario where you want the entire database to be replicated to an identical secondary database.

Conditions for using config.sql

• Source and target table names must be the same.

• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration file that the script builds, then use the edit config command to add entries for tables that use partitioned replication. Activate the new configuration file, not the original one.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/config.sql

Refer to the following table when following the prompts.

Note: The name assigned by SharePlex to the configuration is config.file. If you run the script again to create another configuration file, it overwrites the first file. To preserve the original file, rename it before you create the second one.

Next steps:

• If any tables or owners are case-sensitive, open the configuration file with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

Use build_config.sql

The build_config.sql script enables you to build a configuration that contains multiple (or all) tables in a schema. It is an interactive script that prompts for each component of the configuration step by step. Instead of entering the information for each object and the routing individually, you can use a wildcard to select certain tables at once, or you can select all of the tables in the schema.

Conditions for using build_config.sql

• Source and target table names must be the same.
• The script does not support sequences. Before you activate the configuration that the script builds, you can use the edit config command in sp_ctrl to add entries for sequences.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration that the script builds, then use the edit config command to add entries for the tables that use partitioned replication. Activate the new configuration, not the original.
• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• You can run build_config.sql for different schemas, then combine those configurations into one configuration by using a text editor. Make certain to eliminate all but one Datasource:o.SID line, which is the first non-commented line of the file. Do not move the file out of the config sub-directory.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run build_config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The build_config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run build_config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/build_config.sql

Refer to the following table when following the prompts.

Next steps:

• If any tables or owners are case-sensitive, open the configuration with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

Configure replication to and from a container database

SharePlex supports replication to and from Pluggable Databases (PDB) in Oracle multitenant container databases (CDB). This support is available on Unix and Linux platforms only.

Contents

Replicate to and from a PDB

Configure capture and delivery

SharePlex can replicate data from one PDB to:

• another PDB in the same CDB
• a PDB in a different CDB
• a regular (non-PDB) target

SharePlex can replicate data from a regular source database to a PDB in a target Oracle CDB.

In one configuration file, you can replicate to any number of target PDBs in the same CDB or a different CDB.

To capture from a PDB

• In the configuration file, specify the TNS alias of a PDB as the datasource. For example, if the TNS alias is pdb1, the datasource specification is:

  Datasource: o.pdb1

• You can replicate from as many pluggable databases (PDBs) in the same CDB as desired: Create a separate configuration file for each PDB. Because each PDB is a different datasource, all configurations can be active at the same time.
• If replicating from more than one PDB on a system, use named export queues to separate the data streams from each one. This allows you to issue SharePlex commands that affect configurations, such as purge config or abort config, for one configuration without affecting the other configurations. For more information, see Configure named export queues

To replicate to a PDB

Specify the TNS alias of the target PDB in the routing map, as shown in the following example where pdb2 is the target:

sys02@o.pdb2

PDB configuration examples

Example 1: This example shows two configuration files, one replicating from pdb1 and the other replicating from pdb2, both replicating data to pdb3.

Datasource: o.pdb1
hr.emp    hr2.emp2    sys02@o.pdb3

Datasource: o.pdb2 	 
sales.cust    sales2.cust2    sys02@o.pdb3

Example 2: This example shows one configuration file replicating from pdb1 to pdb2 and pdb3, both targets being on different systems.

Datasource: o.pdb1	 	 
hr.sal    hr2.sal2    sys02@o.pdb2
hr.sal    hr3.sal3    sys03@o.pdb3