SharePlex 11.3 - Installation and Setup Guide

Set up replication from Oracle to JMS

Overview

The SharePlex Post process can connect and write to a JMS (Java Messaging Service) queue or topic. The data is written as XML records that include the data definitions, the operation type, and the changed column values. This data is written as a sequential series of operations as they occurred on the source, which can then be posted in sequential order to a target database or consumed by an external process or program.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Configure SharePlex on the source

You need to setup SharePlex and the database on the Oracle source system. For detailed setup steps, see Configure SharePlex on the source.

Configure SharePlex on the target

These instructions establish the Java provider on the target and configure the SharePlex Post process to connect to a JMS provider.

Prerequisites

Java Runtime Environment 1.8 or later. JRE 1.8 (or later) is required and installed along with SharePlex except on the following operating systems:

• CentOS Linux 7.x and 8.x
• Oracle Linux 7.x and 8.x
• RHEL Linux 7.x and 8.x

Prior to installing SharePlex on one of these systems, ensure JRE 1.8 (or later) is installed. Then after installing SharePlex, modify the bridge configuration file to point to the JRE binary. See the "Custom JRE" steps in each of the following procedures.

Using a JRE version later than 1.8. If you are using JMS Provider libraries compiled with a version of JRE later than 1.8, use that later version of JRE and modify the bridge (Linux/Unix) configuration file to point to that JRE binary. See the "Custom JRE" steps in each of the following procedures.

Configure SharePlex on the JMS target

These instructions configure the SharePlex Post process to post to a JMS queue or topic.

The following directory naming conventions are used in the examples in this procedure:

To configure post to the JMS target:

1. Create a directory for your Provider under SharePlex/lib/providers. For example:

   shareplex/lib/providers/provider_name

   If your Provider is Active MQ, the path may look similar to the following:

   shareplex/lib/providers/amq

2. Copy the client library files you require to the provider_name directory.

   For example, if your Provider is Active MQ, you might copy the following JAR files to the amq directory.

   shareplex/lib/providers/amq/activemq-all.jar

   shareplex/lib/providers/amq/slf4j.jar

3. (Custom JRE) If you are using a JRE version later than 1.8, set the JAVA_HOME and JRE_HOME variable values to the path to your JRE home directory. In addition, add the JAVA_HOME path to the PATH variable.

4. (Custom JRE) If you are using a JRE other than the one installed with SharePlex which is 1.8.0_312 (e.g., SharePlex did not install JRE on your operating system or you must use a JRE version later than 1.8), modify the bridge.ini or bridge file to point to the JRE binary you intend to use.

   Use the following methods:

   • On Linux or Unix:

     1. The bridge file is located in the following directory:

        SharePlex/.app-modules/openbridge

     2. If a JRE was installed during the SharePlex installation, it is located in the following directory:

        SharePlex/.app-modules/openbridge/jre

     3. In the bridge file, locate the _jvm option (for example< _jvm = "jre/bin/java") and replace the current path with the full path to the JRE you intend to use. You must specify the absolute path to your JRE, not the relative path.

5. Start sp_cop. (Do not activate the configuration yet.)
6. Run sp_ctrl.

7. Issue the target command to configure Post to post to the JMS queue or topic. The values you set using the target command tell SharePlex how to instantiate on the target. In the following examples, user-defined values are shown in italics. Replace these with values specific to your JMS Provider and your provider-specific JMS libraries.

   Issue the following commands, as necessary. For detailed descriptions of the properties and values, see View and change JMS settings. For more information about the target command, see the SharePlex Reference Guide.

   If posting to a JMS queue:

   sp_ctrl> target x.jmsset jms factory_class=factory_class

   sp_ctrl> target x.jms set jms provider_url=url

   sp_ctrl> target x.jms set jms lib_location=path

   For example, if using Active MQ you might set the following:

   sp_ctrl> target x.jms set jms lib_location=shareplex/lib/providers/amq

   sp_ctrl> target x.jms set jms queuename=JMS_topic_queuename

   If posting to a JMS topic:

   sp_ctrl> target x.jmsset jms factory_class=factory_class

   sp_ctrl> target x.jms set jms provider_url=url

   sp_ctrl> target x.jms set jms lib_location=path

   For example, if using Active MQ you might set the following:

   sp_ctrl> target x.jms set jms lib_location=shareplex/lib/providers/amq

   sp_ctrl> target x.jmsset jms destination=topic

   sp_ctrl> target x.jms set jms queuename=JMS_topic_queuename

Enable logs for JMS replication

We need to implement the logging mechanism with a different approach than the one we used earlier for legacy framework. In the current version, we have removed the -OB_debugToLog, -OB_debugToConsole parameters. Now, users get more control to generate and retain the log details by resetting properties inside the log4j2.properties file.

To enable the logs for JMS, follow the below steps:

1. Navigate to <Prod/Opt_Directory>/.app-modules/openbridge/configuration/ location and update the required parameters in the log4j2.properties file.
2. Restart SharePlex.
3. To enable full debug, set rootLogger.level to All. There are other possible values for rootLogger.level which are mentioned in the form of comments in the log4j2.properties file.
4. To enable console logging, set appender.console.filter.threshold.level to All. There are other possible values for appender.console.filter.threshold.levelwhich are mentioned in the form of comments in the log4j2.properties file.

Note: You can change other parameters as per standard log4j2.properties syntax. Refer to the below link for information.
https://logging.apache.org/log4j/2.x/manual/configuration.html#Properties

View and change JMS settings

To view current property settings for output to JMS, use the following command:

target x.jms show

To change a property setting, use the following command.

target x.jms [queue queuename] set jms property=value

where:

• queue queuename is the name of a Post queue. Use this option if there are multiple Post processes.
• property and value are shown in the following table.

Table 1: JMS target properties

Set recovery options

If the JMS process aborts suddenly, or if the machine that it is running on aborts, row changes may be written twice to the JMS target. The consumer must manage this by detecting and discarding duplicates.

Every record of every row-change operation in a transaction has the same transaction ID and is also marked with a sequence ID. These attributes are id and msgIdx, respectively, under the txn element in the XML output (see View the XML format).

The transaction ID is the SCN at the time the transaction was committed and the sequence ID is the index of the row change in the transaction. These two values are guaranteed to be the same if they are re-written to the JMS queue in a recovery situation.

If desired, you can configure the target to include additional metadata with every row-change record by using the following command:

target target [queue queuename] set metadata property[, property]

Table 2: Optional JMS metadata properties

Example

target x.jms set metadata time, userid, trans, size

To reset the metadata:

target x.jms [queue queuename] reset metadata

To view the metadata:

target x.jms [queue queuename] show metadata

Control memory settings

If you configured Post to process large amounts of transactions to JMS through multiple named Post queues, you may need to allocate more memory to the Post JMS bridge. The default settings are 256MB and 512MB.

To increase the JMS bridge memory:

Open the openbridge.ini file and set the following parameters in the format shown:

-Xms=512m

-Xmx=1024m

-XX:PermSize=512m

-XX:MaxPermSize=1024m

Configure debugging

You can configure Post to log whether each write to a queue or topic succeeded or failed. This is controlled with the debugging setting.

To enable debugging:

Open the openbridge.ini file and set the -OB_debugToLog parameter to true.

-OB_debugToLog=true

View the XML format

To view the XML formatting of the records that SharePlex sends to JMS, see the format category of the target command documentation in the SharePlex Reference Guide.

Set up replication from Oracle to Kafka

Pre-requisite: When replicating data to the Kafka (Apache and Confluent) target, target SharePlex should be installed only on the Linux platform.

Overview

The SharePlex Post process can connect and write to a Kafka broker. The data can be written in JSON or XML output as a sequential series of operations as they occurred on the source, which can then be consumed by a Kafka consumer.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Guidelines for posting to Kafka

• A SharePlex Post process acts as a Kafka producer. A SharePlex Post process can write to one or more topics that have one or more partitions.
• The SharePlex Post process does not create a topic itself, but you can configure the Kafka broker to auto-create topics.

Configure SharePlex on the source

You need to setup SharePlex and the database on the Oracle source system. For detailed setup steps, see Configure SharePlex on the source.

Configure SharePlex on the target

These instructions configure the SharePlex Post process to connect to Kafka. You must have a running Kafka broker.

To configure post to Kafka:

1. Create a Kafka topic.
2. Start sp_cop. (Do not activate the configuration yet.)
3. Run sp_ctrl.

4. Issue the target command to configure posting to a Kafka broker and topic. The following are example commands.

   sp_ctrl> target x.kafkaset kafka broker=host1:9092,host2:9092,host3:9092

   sp_ctrl> target x.kafka set kafka topic=shareplex

   See View and change Kafka settings for command explanations and options.

Note: Specify more than one broker so that SharePlex will attempt to connect to the other brokers in the list if any one of them is down.

Set the Kafka record format

SharePlex can output to either XML or JSON format as input to Kafka. XML is the default. To set the input format and specify format options, use one of the following target commands:

target x.kafka set format record=json

or:

target x.kafka set format record=xml

To view samples of these formats, see the format category of the target command documentation in the SharePlex Reference Guide.

Note: When replicating data from Oracle to Kafka in JSON format, SharePlex does not support the varray data type or the varray type inside the SDO_GEOMETRY data type.

View and change Kafka settings

To view current property settings for output to Kafka, use the following target command:

target x.kafka show

To change a property setting, use the following command.

target x.kafka [queue queuename] set kafka property=value

where:

• queue queuename is the name of a Post queue. Use this option if there are multiple Post processes.
• property and value are shown in the following table.

Table 3: Kafka target properties

* To avoid latency, if Post detects no more incoming messages, it sends the packet to Kafka immediately without waiting for the threshold to be satisfied.

Set recovery options

If the Kafka process aborts suddenly, or if the machine that it is running on aborts, row changes may be written twice to the Kafka topic. The consumer must manage this by detecting and discarding duplicates.

Every record of every row-change operation in a transaction has the same transaction ID and is also marked with a sequence ID. These attributes are id and msgIdx, respectively, under the txn element in the XML output (see Set up replication from Oracle to Kafka).

The transaction ID is the SCN at the time the transaction was committed, and the sequence ID is the index of the row change in the transaction. These two values are guaranteed to be the same if they are re-written to the Kafka topic in a recovery situation.

If desired, you can configure Post to include additional metadata with every row-change record by using the following command:

target x.kafka [queue queuename] set metadata property[, property]

Table 4: Optional metadata properties

Example:

target x.kafka set metadata time, userid, trans, size

To reset the metadata:

target x.kafka [queue queuename] reset metadata

To view the metadata:

target x.kafka [queue queuename] show metadata

Set up replication from Oracle to a SQL or XML file

Overview

SharePlex can post replicated Oracle data to a file formatted as SQL or XML. This data is written as a sequential series of operations as they occurred on the source, which can then be posted in sequential order to a target database or consumed by an external process or program.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Configure SharePlex on the source

On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to a SQL or XML file.

where:

• SID is the Oracle SID of the source Oracle database.

• src_owner.table is the owner and name of the source table.

• !file is a required keyword that directs Post to write to a file.
• tgt_owner.table is optional and specifies the owner and name of the target table. Use if either component is different from that of the source table.
• host is the name of the target system.

Note: For more information, see Configure SharePlex to Replicate Data in the SharePlex Administration Guide.

Source configuration example:

The following example replicates the parts table in schema PROD from Oracle instance ora112 to a file on target system sysprod.

Configure SharePlex on the target

By default, SharePlex formats data to a file in XML format, and there is no target setup required unless you want to change properties of the output file (see Set up Replication from Oracle to a SQL or XML File.) To output in SQL format, use the target command to specify the SQL output as follows.

To output data in SQL format:

1. Start sp_cop.
2. Start sp_ctrl.

3. Issue the following required target commands to output the records in SQL.

   Note: Use all lower-case characters.

   target x.file [queue queuename] set format record=sql

   target x.file [queuequeuename] set sql legacy=yes

   where: queue queuename constrains the action of the command to the SharePlex Post process that is associated with the specified queue.

   See Set up Replication from Oracle to a SQL or XML File for descriptions of these settings and other optional properties that you can set.

To view samples of the SQL and XML formats, see the target command documentation in the SharePlex Reference Guide.

View and change target settings

To view current property settings for output to a file, use the following command:

target x.file show

To change a setting, use the following target command.

target x.file [queue queuename] set [category] property=value

For more information, see the target command in the Target.

File storage and aging

Post writes to a series of files. The active working file is prepended with the label of current_ and is stored in the opx/current subdirectory of the variable-data directory.

Important: Do not open or edit the current_ file.

Post uses the max_records, max_size and max_time parameters to determine the point at which to start a new active file. When this switch occurs, Post moves the processed data to a sequenced file in the opx subdirectory of the variable-data directory. The file names include the name of the post queue, the time and date, and an incrementing ID.

SQL files:

/installed/vardir> ls -1 opx

0000000000_20140305130858_legacy.sql

0000000001_20140305131130_legacy.sql

0000000002_20140305131212_legacy.sql

0000000003_20140305133835_legacy.sql

0000000004_20140305134028_legacy.sql

XML files:

/installed/vardir> ls -1 opx

0000000000_20140305130858_prodsys.XML

0000000001_20140305131130_prodsys.XML

0000000002_20140305131212_prodsys.XML

0000000003_20140305133835_prodsys.XML

0000000004_20140305134028_prodsys.XML

To force a file switch:

The current file cannot be viewed or consumed without stopping Post. To access the data in the current file, you can use the target command with the switch option to move the data to a sequenced file, from which it can then be consumed or viewed. After issuing this command, the switch occurs after Post processes a new record.

target x.file [queue queuename] switch

Installation and Setup for Cloud-Hosted Databases for Oracle

Contents