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.
About this Guide
Conventions used in this guide
Revision History
Overview of SharePlex
Advantages of SharePlex
About source and target data
About SharePlex architecture
How SharePlex replication works
Understanding the concept of synchronization
Strategies for information availability
Run SharePlex
Run multiple instances of SharePlex
Run multiple instances of SharePlex from separate installations
Run multiple instances of SharePlex from one installation
Execute commands in sp_ctrl
How to run sp_ctrl
Define a default port for sp_ctrl
Define a default host for sp_ctrl
Set a default editor for sp_ctrl
Command guidelines
Issue commands on a remote system
Issue commands for a clustered system
Set SharePlex parameters
Configure data replication
Ensure compatible source-target mapping
Create a configuration file
How to qualify object names
How to specify case-sensitive names
Database specifications in a configuration file
Target specifications in a configuration file
Routing specifications in a configuration file
Configuration examples
Capture from multiple local datasources
Use Wildcards to specify multiple objects
Use Wildcards to Specify Multiple Tables for PostgreSQL
Define a Unique Key for Oracle Database
Filter DML operations for Oracle Database
Filter DML Operations for PostgreSQL Database
Map source columns to target columns
Build a configuration file using a script
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 Replication to share or distribute data
Configure Replication to maintain a central data store
Configure peer-to-peer replication
Configure DDL replication
Configure PostgreSQL to PostgreSQL Replication
Configure PostgreSQL or PostgreSQL Database as a Service to Oracle replication
Develop Conflict Resolution Routines
User defined conflict resolution routines for Oracle to Oracle
User defined conflict resolution routines for PostgreSQL or PostgreSQL Database as a Service to PostgreSQL
User defined conflict resolution routines for PostgreSQL or PostgreSQL Database as a Service to Oracle
SharePlex Prepared Routines
Log information about resolved conflicts for Oracle database
Configure Replication through an intermediary system
Configure Replication to maintain high availability
DDL that SharePlex supports
Enable DDL replication
Filter DDL Replication
Best practices for ALTER TABLE DDL
DDL logging and error handling
Configure error handling
Continue to Post when there is an error
Continue to Post on DDL errors
Increase the number of retries on error
Handle transactions that contain out-of-sync operations
Configure data transformation
Configure security features
Secure data with SSL/TLS
Host Authentication
Secure data with SSH
Encrypt data between Export and Import
FIPS Compliance
Assign SharePlex users to security groups
Start replication on your production systems
What is activation?
Activation commands
Requirements for activating a configuration
Test the configuration before activation
Frequently Asked Questions about activation
How to activate multiple configuration files
Activate replication with an Oracle hot backup on an active database
Activate replication with PostgreSQL hot backup on an active database
Activate replication with an Oracle hot backup on a quiet database
Activate replication with Oracle transportable tablespaces
Activate replication with cold copy/transfer methods
Activate replication from Oracle to Open Target
Monitor SharePlex
View and kill SharePlex processes
View events and errors
Monitor with sp_ctrl commands
Run monitor scripts on UNIX
Prevent and solve replication problems
Monitor Oracle capture with sp_logmon
Monitor events with sp_eventmon
Monitor processes with sp_ps
Monitor queues with sp_qstatmon
Monitor with SNMP
Find the problem in the SharePlex Knowledge Base
Solve database setup problems
Solve configuration file problems
Solve activation problems
Solve replication problems
Solve Oracle DDL replication problems
Solve queue problems
Solve synchronization problems
Solve compare command errors
Solve other problems
How to resynchronize source and target objects
How to restore Oracle archive logs
How to release semaphores after process failure
How to resolve disk space shortage
How to find the ORACLE_SID and ORACLE_HOME
Repair out-of-sync data
Overview of Compare and Repair
Before you use Compare and Repair
How to use the repair and compare commands
Tune the Capture process
Tune the Post process
Use INDEX hints
Tune SQL Caching
Adjust open cursors
Skip maintenance DML
Make small transactions faster
Split a large transaction into a smaller one
Tune queue performance
Tune hash-based horizontally partitioned replication
Recover replication after Oracle failover
Recover replication if the primary system fails
Recover replication if the secondary Oracle instance fails
Move replication during planned failover and failback
Resume replication after failure and recovery
Make changes to an active replication environment
Change a configuration file
Add or change table specifications in an active replication configuration
Change Partitioned Replication
Add Oracle sequences to an active replication configuration
Remove objects from replication
Make DDL changes in an active replication configuration
Make Oracle changes that affect replication
Change the SharePlex database account
Change the name or IP address of a replication host
Set the SharePlex port number
Initial Database Synchronization for PostgreSQL to PostgreSQL Replication
Apply an Oracle application patch or upgrade
Before you patch or upgrade an application
Apply patch/upgrade to source then copy it to target
Apply patch/upgrade to source and target
Apply patch to source and replicate it to the target
Back up Oracle data on the source or target
Troubleshooting Tips
Appendix A: Peer-To-Peer Diagram
Appendix B: SharePlex environment variables
Configure replication to and from a container database
Configure Replication to and from a Container Database
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.
Configure named queues
Configure Named Queues
This chapter contains instructions for using the advanced SharePlex configuration options of named queues. These options provide an additional level of flexibility to divide and parallelize data to meet specific processing and routing requirements. Before proceeding, make certain you understand the concepts and processes in Configure SharePlex to Replicate Data.
Contents
Configure named export queues
A named export queue is an optional, user-defined export queue that is attached to its own Export process. SharePlex creates each named Export queue and associated Export process in addition to the default export queue-process pair. When SharePlex creates a named export queue-process pair, it also creates a dedicated Import process, post queue, and Post process on the target to contain that data stream.
You direct SharePlex to create one or more named export queues when you create your configuration file. Any data that is not configured for processing through a named export queue is processed through the default export queue.
Supported sources and targets
-
PostgreSQL to PostgreSQL, Oracle, SQL Server, and Kafka
-
Oracle to all targets
Benefits of named export queues
Use named export queues to isolate the replication of:
- Individual configurations: By default, SharePlex sends data from all active configurations through one export queue-process pair per target system, but the use of named Export queues enables you to separate each of those replication streams into its own export queue and Export process. In this way, you ensure that purge config or abort config commands that are issued for one configuration do not affect any of the others.
- Selected database objects: You can use a named export queue to isolate certain objects such as tables that contain LOBs. Because each named export queue has its own Import process, post queue, and Post process on the target, you are able to isolate the data the entire way from source to target. For more information about the benefits of named post queues, see Configure Named Post Queues.
Additional benefits:
- You can stop the Export or Import process for one data stream, while allowing the others to continue processing.
- You can set SharePlex parameters to different settings for each export queue-process pair. This enables you to tune the performance of the Export processes based on the objects replicating through each one.
Considerations when using named export queues
- Make certain that each queue name is unique.
-
You can combine named export queues with default export queues. Tables in the configuration with a standard routing map (targetsys@database_spec without a named queue specification) are replicated through a default export queue.
- All tables with referential integrity to one another must be in the same export queue.
- SharePlex has a maximum number of allowed queues. For more information, see Routing Specifications in a Configuration File.
Configure a named export queue: Oracle to all targets
Use the following syntax to define a routing map that includes a named export queue.
source_host:export_queuename*target_host[@database]
Configuration with named export queue in routing map
| Datasource: o.SID | ||
| src_owner.table | tgt_owner.table |
source_host:export_queue*target_host[@database_specification] |
| Routing component | Description |
|---|---|
| source_host | The name of the source system. |
| export_queue |
The name of the export queue. Queue names are case-sensitive on all platforms. Use one word only. Underscores are permissible, for example: sys1:export_q1*sys2@o.myora |
| target_host | The name of the target system. |
| database specification |
One of the following for the datasource: o.oracle_SID r.database_name
One of the following if the target is a database: o.oracle_SID o.tns_alias o.PDBname r.database_name c.oracle_SID |
NoteS:
|
Examples
The following configuration files show two different datasources that are being replicated to two different databases on the same target system. Each datasource is routed through a named export queue.
| Datasource:o.oraA | ||
| scott.emp | scott.emp | sysA:QueueA*sysB@o.oraC |
| scott.sales | scott.sales | sysA:QueueA*sysB@o.oraC |
| Datasource:o.oraB | ||
| scott.prod | scott.prod | sysA:QueueB*sysB@o.oraD |
| scott.cust | scott.cust | sysA:QueueB*sysB@o.oraD |
The following shows how to separate a table that contains LOBs from the rest of the tables by using named export queues.
| Datasource:o.oraA | ||
| scott.cust | scott.cust | sysA:QueueA*sysB@o.oraC |
| scott.sales | scott.sales | sysA:QueueA*sysB@o.oraC |
| scott.prod | scott.prod | sysA:QueueA*sysB@o.oraC |
| scott.emp_LOB | scott.emp_LOB | sysA:QueueB*sysB@o.oraC |
Alternatively, you could simply define a named export queue for the LOB table and allow the remaining tables to be processed through the default export queue.
| Datasource:o.oraA | ||
| scott.cust | scott.cust | sysB@o.oraC |
| scott.sales | scott.sales | sysB@o.oraC |
| scott.prod | scott.prod | sysB@o.oraC |
| scott.emp_LOB | scott.emp_LOB | sysA:lobQ*sysB@o.oraC |
Configure a named export queue for PostgreSQL
Use the following syntax to define a routing map that includes a named export queue.
source_host:export_queuename*target_host[@database]
Supported targets
PostgreSQL, Oracle, SQL Server, and Kafka
Configuration with named export queue in routing map
| Datasource:r.dbname | ||
| src_schema.table | tgt_schema.table |
source_host:export_queue*target_host[@database_specification] |
| Routing component | Description |
|---|---|
| source_host | The name of the source system. |
| export_queue |
The name of the export queue. Queue names are case-sensitive on all platforms. Use one word only. Underscores are permissible, for example: sys1:export_q1*sys2@r.dbname |
| target_host | The name of the target system. |
| database specification |
r.database_name |
Note: Allow no spaces between any components in the syntax of the routing map.
Examples
The following configuration files show two different datasources that are being replicated to two different databases on the same target system. Each datasource is routed through a named export queue.
| Datasource:r.dbnameA | ||
| scott.emp | scott.emp | sysA:QueueA*sysB@r.dbnameC |
| scott.sales | scott.sales | sysA:QueueA*sysB@r.dbnameC |
| Datasource:r.dbnameB | ||
| scott.prod | scott.prod | sysA:QueueB*sysB@r.dbnameD |
| scott.cust | scott.cust | sysA:QueueB*sysB@r.dbnameD |
The following shows how to separate a table that contains LOBs from the rest of the tables by using named export queues.
| Datasource:r.dbnameA | ||
| scott.cust | scott.cust | sysA:QueueA*sysB@r.dbnameC |
| scott.sales | scott.sales | sysA:QueueA*sysB@r.dbnameC |
| scott.prod | scott.prod | sysA:QueueA*sysB@r.dbnameC |
| scott.emp_LOB | scott.emp_LOB | sysA:QueueB*sysB@r.dbnameC |
Alternatively, you could simply define a named export queue for the LOB table and allow the remaining tables to be processed through the default export queue.
| Datasource:r.dbnameA | ||
| scott.cust | scott.cust | sysB@r.dbnameC |
| scott.sales | scott.sales | sysB@r.dbnameC |
| scott.prod | scott.prod | sysB@r.dbnameC |
| scott.emp_LOB | scott.emp_LOB | sysA:lobQ*sysB@r.dbnameC |
How to identify named export queues
You can view named export queues through sp_ctrl:
- Use the qstatus command to view all queues on a system.
- Use the show command to view all Export processes with their queues.
See the SharePlex Reference Guide for more infomation about theses commands.