Chat now with support
Chat with Support

SharePlex 9.0 - Preinstallation Checklist

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.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating