Pre-requisite: Schema support must be enabled in the MySQL DSN configuration to properly reference the table name during the replication process.
SharePlex can post replicated Oracle data to a MySQL or Aurora target database through an Open Database Connectivity (ODBC) interface.
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.
Important! If replicating to MySQL or Aurora on a PaaS cloud server (no access to the operating system), see the installation instructions in Installation and setup for cloud-hosted databases.
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 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.
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:
Type the name in the correct case.
Enclose the name in quotation marks, for example "MySchema"."MyTable".
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
Perform the following steps to configure SharePlex on the target:
Make certain that the database setup meets all of the requirements in Open target checklist .
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.
|
Important! Greenplum does not allow UPDATE operations to primary keys, but Oracle does. To replicate an UPDATE to a key from Oracle to Greenplum, Post deletes the target row and then inserts a new row based on the source row, including the new key value(s). |
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 SharePlex Administration Guide for more information about column mapping with SharePlex.
You need to setup SharePlex and the database on the Oracle source system. For detailed setup steps, see Configure SharePlex on the source.
Perform the following steps to configure SharePlex on the target:
Make certain that the database setup meets all of the requirements in Open target checklist .
SharePlex can post replicated source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface.
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 SharePlex Administration Guide for more information about column mapping with SharePlex.
You need to setup SharePlex and the database on the Oracle source system. For detailed setup steps, see Configure SharePlex on the source.
Perform the following steps to configure SharePlex on the target:
Make certain that the database setup meets all of the requirements in Open target checklist .
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 SharePlex to Replicate Data 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 an 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 [queuequeuename] set format record=sql
target x.file [queuequeuename] set sql legacy=yes
where:queuequeuename 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 an 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 [queuequeuename] set [category] property=value
For more information, see the target command in the Target.
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 [queuequeuename] switch
