Chat now with support
Chat with Support

SharePlex 9.0 - Installation Guide

About this Guide Conventions used in this guide System Requirements SharePlex pre-installation checklist Installation and setup for Oracle cluster Installation and setup for cloud-hosted databases Download SharePlex Install SharePlex on Linux and UNIX Install SharePlex on Windows Assign SharePlex users to security groups Set up an Oracle environment for replication Set up replication between SQL Server databases Set up replication between different database types Basic SharePlex demonstrations Advanced SharePlex demonstrations Solve Installation Problems Uninstall SharePlex SharePlex Utilities Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

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, datatypes 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.

Datasource:o.SID
src_owner.table !file[:tgt_owner.table] host

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.

Datasource:o.ora112

PROD.parts !file 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

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.

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

Set up replication from SQL Server to Oracle

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.

Review column names

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.

Ensure column length compatibility

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:

  • For SQL Server char and varchar columns less than or equal to 1000 bytes in length, define the Oracle columns as CHAR and VARCHAR, and specify the length (semantics) as character.
  • For SQL Server char and varchar columns greater than 1000 bytes in length, define the Oracle columns as CLOB.
  • For SQL Server nchar columns less than or equal to 1000 characters in length, define the Oracle columns as NCHAR equal in size or greater than the SQL Server ones.
  • For SQL Server nchar columns greater than 1000 characters in length, define the Oracle columns as NCLOB.
  • For SQL Server nvarchar columns less than or equal to 2000 characters in length, define the Oracle columns as NVARCHAR equal in size or greater than the SQL Server ones.
  • For SQL Server nvarchar columns greater than 2000 characters in length, define the Oracle columns as NCLOB.
  • For SQL Server binary and varbinary columns less than or equal to 2000 bytes in length, define the Oracle columns as RAW equal or greater than the SQL Server ones.
  • For SQL Server binary and varbinary columns greater than 2000 bytes in length, define the Oracle columns as BLOB.

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 on the source

Configure SharePlex and the database on the source system as follows.

Run Database Setup

Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.

Ensure that all tables have a primary key

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.

Configure replication

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:

  • r. identifies the source database as non-Oracle, in this case SQL Server.
  • database_name is the name of the SQL Server database. Important! database_name must be the actual name of the database, not a data source name (DSN).
  • src_owner.table is the owner and name of the source table.
  • tgt_owner.table is the owner and name of the target table.*
  • host is the name of the target system.
  • o. identifies the target database as Oracle.
  • SID is the ORACLE_SID of the target Oracle database.

Important!

  • If a database is case-sensitive, enclose the case-sensitive object names in quotes.
  • If the letter case of the column names on the source is different from the letter case of the target columns, for example the source is all capitals while the target is lower case, use the column mapping feature to map the column names in the configuration file. See Map source and target columns in the SharePlex Administration Guide for more information.

Source configuration example

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

Configure SharePlex on the target

  1. Make certain that the database setup meets all of the requirements in Set up Oracle database objects for replication .

  2. Run Database Setup for Oracle to establish a database account and connection information for SharePlex. See Database Setup for Oracle.
  3. See Set up an Oracle environment for replication for additional Oracle setup instructions.

Basic SharePlex demonstrations

Contents

 

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.

What you will learn

  • How to activate a configuration
  • How SharePlex replicates smoothly from source to target systems
  • How SharePlex quickly and accurately replicates large transactions
  • How SharePlex queues the data if the target system is unavailable
  • How SharePlex resumes from its stopping point when the target system is recovered
  • How SharePlex recovers after a primary instance interruption
  • How SharePlex replicates a TRUNCATE command
  • How SharePlex verifies synchronization and repairs out-of-sync rows When you complete these demonstrations, you may move on to the next chapter, which contains more advanced demonstrations of SharePlex performance and features.

Tables used in the demonstrations

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.

Description of the demo tables.
     
Column Name Data Type Null?
NAME varchar2(30)  
ADDRESS verchar2(60)  
PHONE varchar2(12)  

Part 1: Start SharePlex

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.

$ cd /productdir/bin
 
$ ./sp_cop &
 
$ . /sp_ctrl

To start SharePlex on Windows systems

  1. Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).
  2. On the Windows desktop, double-click the SpUtils shortcut to open the SharePlex Utilities dialog box.
  3. Click the SharePlex Services tab to display the SharePlex Services dialog box.
  4. In the Port list box of the SharePlex Services dialog box, select the SharePlex port number, then click Start.
  5. When the Current State text box shows that the service has started, click Close to close the dialog box.
  6. On the Windows desktop, double-click the sp_ctrl shortcut to open the sp_ctrl command prompt.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating