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, datatypes 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 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
SharePlex can capture from a SQL Server source database and replicate the data to an Oracle target.
These instructions provide an overview of the steps required to support replication between these database types.
For the versions, datatypes 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.
SQL Server defines CHAR and VARCHAR data in bytes, whereas Oracle can define it in bytes or characters depending on the semantics definition of the database or the specific table. Additionally, SQL Server allows larger maximum column sizes than Oracle. To allow for these differences in column length, adjustments must be made to the Oracle target table definitions as follows to ensure that the target columns can fit all of the data:
The following chart represents these relationships:
| SQL Server Source column definition | Length (bytes) | Required Oracle column definition |
|---|---|---|
|
char length |
<=1000 >1000 |
CHAR(length char) CLOB |
|
varchar length |
<=1000 >1000 |
VARCHAR(length char) CLOB |
|
nchar length |
<=1000 >1000 |
NCHAR(length)
NCLOB |
|
nvarchar length |
<=2000 >2000 |
NVARCHAR(length) NCLOB |
| binary |
<=2000 >2000 |
RAW(length) BLOB |
Configure SharePlex and the database on the source system as follows.
Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.
To replicate from a source SQL Server database to a target Oracle database, all SQL Server source tables must have a primary key. This is a requirement of the native SQL Server replication, which is used in part by SharePlex for data capture. See the SQL Server source checklist for more information about these and other pre-configuration requirements for a SQL Server source.
All Oracle target tables must have corresponding keys.
To configure replication from SQL Server to Oracle, use the following syntax in the configuration file on the source system.
Note: See Configure SharePlex to replicate data in the SharePlex Administration Guide for additional information about creating a configuration file.
| Datasource:r.database_name | ||
|
src_owner.table |
tgt_owner.table |
host@o.SID |
where:
Important!
The following configuration file replicates table HR.EMP from the source to target table Region1.Emp on target system. The target table is case-sensitive.
|
Datasource:r.mss1 | ||
|
HR.EMP |
"Region1"."Emp" |
sysprod@o.Ora1 |
Make certain that the database setup meets all of the requirements in Set up Oracle database objects for replication .
This chapter demonstrates SharePlex replication using the sp_ctrl command-line interface. This demonstration can be run on Unix, Linux, or Windows systems.
Warning! These demonstrations are intended to introduce you to the SharePlex software. All of the demonstration components were created specifically for demonstration purposes, not deployment in a production environment. Do not use these demonstrations as the basis for establishing replication. To properly implement replication in your environment, use the SharePlex Administrator’s Guide.
Tip: The commands used in these demonstrations are described in more detail in the SharePlex Reference Manual.
These demonstrations assume that SharePlex is installed on one source system and one target system.
The tables used in these demonstrations are source table demo_src and target table demo_dest, both of which are installed in the SharePlex schema when SharePlex is first installed. The demo tables are installed empty. In order to insure that each demo is started from a fresh state, please truncate the tables prior to beginning the demonstration.
You will replicate demo_src from a source instance on the source system (described as sysA) to demo table demo_dest in a target instance on another system, the target system (described as sysB).
For this documentation, the owner of the demo tables is assumed to be “splex,” which is the default name for the SharePlex Oracle user. If you assigned SharePlex a different user name, use that one.
You need to know the ORACLE_SID of your source and target instances. On Unix and Linux systems, the SID can be found by viewing the oratab file in the /etc directory (/var/opt/ oracle directory on Solaris platforms). You will see a display similar to this:
qa12:/qa/oracle/ora12/app/oracle/product/12.0
In the example, qa12 is the ORACLE_SID.
On Windows systems, the ORACLE_SID is in the Oracle entry in the Windows Registry.
| Column Name | Data Type | Null? |
| NAME | varchar2(30) | |
| ADDRESS | verchar2(60) | |
| PHONE | varchar2(12) |
The following are instructions for starting SharePlex and the sp_ctrl command-line interface on Unix, Linux, and Windows systems. Start SharePlex on the source and target systems.
To start SharePlex on Unix and Linux systems
Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).
From the bin sub-directory of the SharePlex product directory (the one containing the binaries, represented by the productdir variable in the following syntax), run sp_cop and sp_ctrl.
To start SharePlex on Windows systems
© 2023 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy
