Note: These instructions apply to all implementations of PostgreSQL unless otherwise noted.
SharePlex can post replicated Oracle data to a PostgreSQL target database through an Open Database Connectivity (ODBC) interface. SharePlex supports all implementations of the PostgreSQL open-source database.
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.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
Set up SharePlex and the database on the Oracle source system as follows.
To replicate from an Oracle source to an Open Target target, you must make key information available to SharePlex.
Enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.
Set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.
See the SharePlex Reference Guide for more information about this parameter.
On the source, create a SharePlex configuration file that specifies capture and routing information.
| Datasource:o.SID | ||
|
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
* Important!
If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.
The following configuration file replicates table HR.Emp from Oracle instance ora112 to target table region1.emp in database mydb on target system sysprod. The source table is case-sensitive.
Datasource:o.ora112
HR."Emp" region1.emp sysprod@r.mydb
Make certain that the database setup meets all of the requirements in Open Target checklist .
SharePlex can connect to a database that supports Open Database Connectivity (ODBC).
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.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
Set up SharePlex and the database on the Oracle source system as follows.
To replicate from an Oracle source to an Open Target target, you must make key information available to SharePlex.
Enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.
Set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.
See the SharePlex Reference Guide for more information about this parameter.
On the source, create a SharePlex configuration file that specifies capture and routing information.
| Datasource:o.SID | ||
|
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
* Important!
If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.
The following configuration file replicates table HR.Emp from Oracle instance ora112 to target table region1.emp in database mydb on target system sysprod. The source table is case-sensitive.
Datasource:o.ora112
HR."Emp" region1.emp sysprod@r.mydb
Configure SharePlex and the database on the target system as follows.
Note: Make certain that the database setup meets all of the requirements in Open Target checklist .
Make certain to download the correct ODBC driver for your database.
On the target system, configure ODBC connection information for use by Post to connect to the target database. You have the following options for configuring this connection information:
Use the connection command to:
To set connection information
Execute the connection command with the set option, once for each keyword.
connection r.database_name set keyword=value
| Keyword | Value to enter |
|---|---|
|
user |
The database user assigned to SharePlex |
|
password |
The password for the SharePlex user |
|
dsn |
The DSN of the database. IMPORTANT! user, password, and dsn are the only required keywords if a DSN exists. |
connection r.mydb set user=myuser
connection r.mydb set password=mypassword
connection r.mydb set dsn=mydsn
Use connection with the reset option to remove SharePlex connection settings.
To remove a specific connection value
connection r.database_name reset keyword
To remove all connection values
connection r.database_name reset
connection r.mydb reset port
connection r.mydb reset
Use connection with the show option to view SharePlex connection settings.
To view connection values for a database
connection r.database_name show
To view connection settings for all local databases
connection show all
To support the accurate posting of replicated Oracle ALTER TABLE commands to the correct data types in an Open Target target, SharePlex maps Oracle data types to default data types for the target database. To view and change this mapping, use the typemap command.
Note: For DML operations, SharePlex queries the target database to determine the appropriate data type mapping for replicated data.
For more information about the typemap command, see the SharePlex Reference Guide.
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.
When replicating data to JMS, configure the source database and SharePlex on the source system as follows.
On the source system, enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
On the source system, set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.
See the SharePlex Reference Guide for more information about this parameter.
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 through a JMS queue or topic.
| Datasource:o.SID | ||
| src_owner.table | !jms[:tgt_owner.table] | host |
where:
host is the name of the target system.
Note: For more information, see Configure data replication.
Datasource:o.ora112
MY_SCHEMA.MY_TABLE !jms:"MySchema2"."MyTable2" sysprod
These instructions establish the Java provider on the target and configure the SharePlex Post process to connect to a JMS provider.
Java Runtime Environment 1.8 or later. JRE 1.8 (or later) is required and installed along with SharePlex.
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.ini configuration file to point to that JRE binary. See the "Custom JRE" steps in each of the following procedures.
These instructions configure the SharePlex Post process to post to a JMS queue or topic.
NoteS:
INFO 2016-05-10 01:43:42.000860 [x.jms+jsd-olinux-01] com.quest.shareplex.openbridge.stomp.internal.Connector - Connector x.jms+jsd-olinux-01 at tcp://localhost:61613 stopped.
ERROR 2016-05-10 01:43:42.000861 [x.jms+jsd-olinux-01] com.quest.shareplex.openbridge.stomp.internal.Connector - org/apache/activemq/jndi/ActiveMQInitialContextFactory : Unsupported major.minor version 51.0
java.lang.UnsupportedClassVersionError: org/apache/activemq/jndi/ActiveMQInitialContextFactory : Unsupported major.minor version 51.0
The following directory naming conventions are used in the examples in this procedure:
| Name | Directory |
|---|---|
| SharePlex | The SharePlex installation directory |
To configure post to the JMS target
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
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
(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 one of the following methods:
The bridge.ini file is located in the following directory:
SharePlex/bin/openbridge
If a JRE was installed during the SharePlex installation, it is located in the following directory:
SharePlex/bin/openbridge/jre
In the bridge.ini file, locate the line containing the "-vmargs" option. On a separate line before "-vmargs" add the "-vm" option and use it to specify the full path to the JRE you intend to use. For example:
-vm
C:\Java\JDK\1.8\bin\javaw.exe
-vmargs
You must specify the absolute path to your JRE, not the relative path.
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
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:
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
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:
Table 1: JMS target properties
| Property | Input Value | Default |
|---|---|---|
|
factory_class=factory_class |
Required Fully qualified class name of the factory class. Sets the JNDI environmental property java.naming.factory.initial to specify the class name of the initial context factory for the provider. |
None |
|
provider_url=url |
Required RMI URL with no object name component. This sets the JNDI environmental property java.naming.provider.url to specify the location of the registry that is being used as the initial context. Use the correct format depending your JMS Provider and type of URL. For example, if using LDAP your URL might be similar to the following: ldap://hostname.company.com/contextName Ask your JMS Provider Administrator for the JMS Provider URL. |
None |
|
lib_location=path |
Required Path to the directory where you installed the client library files. |
None |
|
destination={queue | topic} |
Messaging domain. Valid values are queue (port-to-port) or topic (publisher-subscriber model). |
queue |
|
factory_name=factory_name |
Name of a JNDI connection factory lookup. You can specify multiple names with a comma-separated list, for example: (jndi.name1, jndi.name2). |
None |
|
user=user |
Name of the user that is attaching to JMS. If authentication is not required, omit this and the password option. |
None |
|
password=password |
Password of the JMS user. |
None |
|
queuename=JMS_topic_queuename |
Name of the JMS queue or topic. |
OpenTarget |
|
persistent={yes | no} |
yes logs messages to disk storage as part of send operations. no prevents logging. |
yes |
| session_transacted ={yes | no} |
no directs Post to issue a JMS commit for every replicated message, making each one immediately visible and consumable. This is the default. yes directs Post to operate in a transactional manner. In this mode, Post issues a JMS commit (to make messages visible and consumable) at intervals based on the following:
|
no |
| properties |
Use this option if the JMS provider that you are using cannot consume messages that contain the default set of properties supplied by SharePlex. It enables you to remove or add properties. Supply the properties as a comma-delimited list.
|
None |
| client_id | Use this option if the JMS provider that you are using cannot consume messages that contain the default SharePlex client ID. Set this value to the client ID that your provider accepts. | None |
| commit_frequency |
Use this option when Post is configured to post to a JMS server in transactional style (issue a JMS commit at intervals, rather than after every message as directed by the session_transacted property). This parameter specifies the interval between JMS commits. It works in conjunction with the SP_OPX_READRELEASE_INTERVAL parameter. Valid values are 1 to any positive integer. |
1500 |
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
| Property | Description |
|---|---|
| time | The time the operation was applied on the source. |
| userid | The ID of the database user that performed the operation. |
| trans | The ID of the transaction that included the operation. |
| size | The number of operations in the transaction. |
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
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
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.
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.
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.
| Datasource:o.SID | ||
| src_owner.table | !file[:tgt_owner.table] | host |
where:
src_owner.table is the owner and name of the source table.
Note: For more information, see Configure data replication in the SharePlex Administration Guide.
The following example replicates the parts table in schema PROD from Oracle instance ora112 to a file on target system sysprod.
Datasource:o.ora112
PROD.parts !file sysprod
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
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.
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
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.
| Output Format | Name of Current File |
|---|---|
| SQL |
current_legacy.sql |
| XML |
current_prodsys.XML |
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
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
