Chat now with support
Chat with Support

SharePlex 9.1.4 - Installation and Setup for Oracle Source

About this Guide Conventions used in this guide Basic system requirements System Requirements — Oracle Capture SharePlex pre-installation checklist Download the SharePlex installer Installation and setup for Oracle cluster Installation and setup for remote capture 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 from Oracle to a different target type Set up replication to a cloud-hosted target Generic SharePlex demonstration-all platforms Advanced SharePlex demonstrations for Oracle Solve Installation Problems Uninstall SharePlex Database Setup Utilities General SharePlex Utilities Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

About this Guide

This manual provides instructions for installing and setting up SharePlex on an Oracle source database and all supported target types.

This manual also contains some demonstration tutorials that show how certain features of SharePlex work.

Other SharePlex documentation

For the complete SharePlex documentation set, go to https://support.quest.com/shareplex.

View this document in PDF format

You can download, view and save this document in Adobe PDF format. Click Download this document in the upper right-hand corner of this page. This action downloads the entire publication.

Conventions used in this guide

Conventions used in this manual

The following typographic conventions are used in this guide.

  • Bold represents required components of a command or option that must be typed as shown.
  • Italics represent variables defined, named or entered by the user.
  • {Braces} enclose required arguments.
  • [Brackets] represent optional command components and may also be used in example command strings to emphasize required user defined variables in long strings.

    Example:

    reconcile queue {queuename} for {datasource-datadest} [on host]

  • A vertical bar, or “pipe” character, ( | ) within brackets or braces indicates that you can use only one of the enclosed components.

    Example:

    abort service {service | all}

Names of commands, programs, directories and files are expressed in Bold.

Other names are expressed in capital letters using the default font.

Examples:

The sp_ctrl program is located in the bin directory.

Open the oramsglst file.

Find the value for ORACLE_HOME.

Click Apply.

System displays, such as prompts and command output, are expressed in a monofaced (fixed-space) font.

Examples:

sp_ctrl(sysA)>

User is a viewer (level=3)

Windows menu items, dialog boxes, and options within dialog boxes are expressed in Bold.

Example:

From the File menu, select Print.

System names are expressed generically or fictitiously. When necessary, the source system (or primary system) is referred to as SysA. Target systems (or secondary systems) are referred to as SysB, SysC, SysD, and so forth.

Basic system requirements

This chapter contains the basic system requirements for SharePlex. See also:

System Requirements — Oracle Capture

System

Before installing SharePlex, ensure that your system meets the minimum hardware and software requirements.

  • SharePlex processes are all 64-bit and can exceed 4 GB.
  • Per process memory of greater than or equal to 256 MB is required. Depending on how you configure SharePlex, there can be one or more of the following processes on a system:

    • Capture
    • Read
    • Export
    • Import
    • Post
  • See the Preinstallation Checklist in the SharePlex Installation Guide for additional system and/or database requirements.

Internet protocol

SharePlex supports IPv4 and IPv6 internet protocols. The following table shows the platforms for which SharePlex was tested with IPv6.

Note: If the SP_SYS_HOST_NAME environment variable is set to an IPV6 address on the source system, SharePlex on the target system must be version 9.0 or later.

Platform

Source

Target

SharePlex Source
and Target versions

On-premises/Cloud

Linux RHEL

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

Linux RHEL

Link-Local IPV6

Link-Local IPV6

8.6.5 - 9.0

On-premises

Linux RHEL

Link-Local IPV6

Link-Local IPV6

9.0 - 8.6

On-premises

Linux Suse

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

Windows

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

Windows

Link-Local IPV6

Link-Local IPV6

9.0 - 8.6.4

On-premises

HP UA

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

HP IA

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

SUN Sparc

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

SUN Solaris

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

AIX

Link-Local IPV6

Link-Local IPV6

9.0 - 9.0

On-premises

Linux RHEL

Public IPV6

Public IPV6

9.0 - 9.0

Cloud

Windows

Public IPV6

Public IPV6

9.0 - 9.0

Cloud

Interoperability between versions

Refer to the following guidelines if the system where you are installing SharePlex is in a configuration where one or more other systems will continue to use an older version of SharePlex. An example is when installing or upgrading SharePlex on a target, but the source will continue to use SharePlex 8.5 or earlier.

Versions with full interoperability

The following SharePlex versions are fully interoperable, including the Compare/Repair feature:

8.0.x

8.5.x

8.6.x

9.0.x

9.1.x

To support replication from a higher to a lower version, set the SP_SYS_TARGET_COMPATIBILITY parameter on the source system to the lower SharePlex version. If Capture is running, restart it.

Important: Downgrading from a higher to a lower version of SharePlex is not supported.

Notes:

  • The older version of the SP_SYS_TARGET_COMPATIBILITY parameter, SP_OCT_TARGET_COMPATIBILITY, was deprecated in version 9.0. If your source SharePlex is currently replicating to a lower SharePlex version on the target and you have SP_OCT_TARGET_COMPATIBILITY set to the lower version, SharePlex will continue to use that value after you upgrade to the current version.
  • If you upgrade the target to the current release at a future time, issue a reset for SP_OCT_TARGET_COMPATIBILITY on the source system to remove it from the SharePlex environment. Going forward, SharePlex will use the setting of the new SP_SYS_TARGET_COMPATIBILITY parameter, which defaults to the current version.

    To reset SP_OCT_TARGET_COMPATIBILITY

    1. Stop Capture.

      sp_ctrl> stop capture

    2. In sp_ctrl on the source system, issue the following command:

      sp_ctrl>reset param SP_OCT_TARGET_COMPATIBILITY

    3. Restart Capture.

      sp_ctrl>start capture

System Requirements — Oracle Capture

This chapter contains the requirements for capture from an Oracle source database and replication to supported Oracle and Open Target targets.

Basic requirements

  • SQL*Plus must be installed with any source or target Oracle Database.
  • On Linux and Unix, set the hard-limit of the system file descriptors to a minimum of 1024 or higher, as the system permits.
  • At least the minimum level of supplemental logging must be enabled. Some SharePlex features may require PK/UK supplemental logging to be enabled.

Supported versions and targets

This section is divided into the following sections according to the way that a database is implemented (on-premises or cloud):

NoteS:

  • Unless specified, Oracle, RAC, and ASM are supported for the same version.
  • SharePlex supports only 64-bit Oracle Database.

On-premises Oracle source to on-premises Oracle target

This section shows the Oracle versions that SharePlex supports when the source and target databases are both Oracle and both are installed on-premises. SharePlex can replicate data between any of these database/platform combinations.

Oracle Version

(source or target)

Supported Platforms (source or target)

(See also the Notes following this table)

Oracle 11gR1*

AIX version:

  • 5.3
  • 6.1

CentOS (x64) version:

  • 5.10
  • 6.5

HP-UX 11.31 PA-RISC and Itanium

Oracle Solaris version:

  • 9
  • 10 SPARC
  • 11 SPARC and x64

Oracle Linux (x64) version:

  • 4
  • 5

RHEL (x64) version:

  • 4
  • 5

SuSE SLES (x64) version:

  • 10
  • 11

Windows Server (x64) version:

  • 2003
  • 2008
  • 2012 R1 and R2

Oracle 11gR2*

AIX version:

  • 5.3
  • 6.1
  • 7.1

CentOS (x64) version:

  • 5.10
  • 6.5

HP-UX 11.31 PA-RISC and Itanium

Oracle Solaris version:

  • 10 SPARC and x64
  • 11 SPARC and x64

Oracle Linux (x64) version:

  • 4
  • 5
  • 5 on Exadata version 2, 3, 4
  • 5.5 on Exadata version 3, 4
  • 6
  • 6.6 on Exadata version 5
  • 7

RHEL (x64) version:

  • 4
  • 5
  • 6
  • 7

SuSE SLES (x64) version:

  • 10
  • 11
  • 12**

Windows Server (x64) version:

  • 2003
  • 2008
  • 2012 R1 and R2
Oracle 12cR1

AIX version:

  • 6.1
  • 7.1
  • 7.2

CentOS (x64) version:

  • 5.10
  • 6.5

HP-UX 11.31 Itanium

Oracle Solaris version:

  • 10 SPARC and x64
  • 11 SPARC and x64

Oracle Linux (x64) version:

  • 5
  • 5 on Exadata version 2, 3, 4
  • 5.5 on Exadata version 3, 4
  • 6
  • 6.6 on Exadata version 5
  • 7

RHEL (x64) version:

  • 5
  • 6
  • 7

SuSE SLES (x64) version:

  • 11
  • 12**

Windows Server (x64) version:

  • 2008
  • 2012 R1 and R2
Oracle 12cR2

AIX version:

  • 7.1
  • 7.2

HP-UX 11.31 PA-RISC and Itanium

Oracle Solaris version:

  • 10 SPARC and x64
  • 11 SPARC and x64

Oracle Linux (x64) version:

  • 6
  • 7

RHEL (x64) version:

  • 6
  • 7

SuSE SLES (x64) version:

  • 12**

Windows Server (x64) version:

  • 2012 R1 and R2
  • 2016

Notes:

* Linux running Oracle 11g must be running on Oracle 11.1.0.6.0+ or you must apply the one-off provided for Oracle Bug 6598432.

** At the time of this writing, Oracle’s published documentation did not indicate support of SuSE SLES12. Basic testing of SharePlex has been done on SuSE SLES12. Quest will provide best effort support but cannot resolve specific Oracle issues on this platform until Oracle adds official support.

On-premises Oracle source to on-premises Open Target

This section shows the supported non-Oracle targets when the source Oracle database is installed on-premises and the target (database, file, or messaging container) is installed on-premises.

The source can be any Oracle 11g or 12c version on a platform that is supported by SharePlex as listed in On-premises Oracle source to on-premises Oracle target.

Target

ODBC Driver Version

Supported Platforms

My SQL

Version 5.6

My SQL ODBC driver 5.3.2 or later.

RHEL (x64) version:

  • 6
  • 7

SuSE SLES (x64) version:

  • 11
  • 12

Community PostgreSQL

Version 9.6.5

ODBC driver recommended by the vendor

RHEL (x64) version:

  • 6
  • 7

Windows Server (x64) version 2012

EDB Postgres Advanced Server

Version 9.4.1.3

ODBC driver recommended by the vendor

RHEL (x64) version:

  • 6
  • 7

Windows Server (x64) version 2012

Fujitsu Symfoware PostgreSQL

Version 9.5.2

ODBC driver recommended by the vendor

RHEL (x64) version:

  • 6
  • 7

Windows Server (x64) version 2012

SAP Adaptive Server Enterprise (ASE)

Version 15.7

ODBC driver recommended by the vendor

CentOS (x64) version:

  • 5.10
  • 6.5

RHEL (x64) version:

  • 4
  • 5
  • 6
  • 7

SuSE SLES (x64) version:

  • 9
  • 10
  • 11
  • 12

SAP HANA

Version 1.0 SPS 11 Rev111

Not applicable

RHEL (x64) version 7

SQL Server

Versions:

2012

2014

2016

Microsoft SQL Server ODBC Driver, same version as database*

Windows Server (x64) version:

  • 2012
  • 2012R2
  • 2016
  • 2017

 

Teradata

Version 15.00.03.05

Teradata ODBC driver version 15.10.00.01 or later

SuSE SLES (x64) version 11

Tibero

Version 6.0

Not needed

CentOS (x64) version 6.5

Windows Server (x64) version 2012

JMS

ActiveMQ Version 5.8

IBM MQ Version 7.x and 8.x

Not needed

AIX version:

  • 6.1
  • 7.1

CentOS (x64) version:

  • 5.10
  • 6.5

Oracle Solaris version:

  • 9
  • 10 (SPARC and x64)
  • 11 (SPARC and x64)

Oracle Linux (x64) version:

  • 4
  • 5
  • 5 on Exadata version 2, 3, 4
  • 5.5 on Exadata version 3, 4
  • 6
  • 6.6 on Exadata version 5
  • 7

RHEL (x64) version:

  • 4
  • 5
  • 6
  • 7

SuSE SLES (x64) version:

  • 10
  • 11
  • 12

Windows Server (x64) version:

  • 2003
  • 2008
  • 2012
  • 2012R2
Kafka

Apache Version .08 and later

Not needed

Windows Server (x64) version:

  • 2012
  • 2012R2
  • 2016
File

Not needed

AIX version:

  • 6.1
  • 7.1

CentOS (x64) version:

  • 5.10
  • 6.5

Oracle Solaris version:

  • 10 (SPARC and x64)
  • 11 (SPARC and x64)

Oracle Linux version (x64):

  • 4
  • 5
  • 5 on Exadata versions 2, 3, 4
  • 5.5 on Exadata version 3, 4
  • 6
  • 6.6 on Exadata version 5
  • 7

RHEL (x64) version:

  • 4
  • 5
  • 6
  • 7

SuSE SLES (x64) version:

  • 10
  • 11
  • 12

Windows Server (x64) version:

  • 2003
  • 2008
  • 2012
  • 2012R2

* Do not use the Microsoft SQL Server Native Client driver. The correct driver, the ODBC Driver, has versions such as 06.02.9200. The wrong driver, Native Client, has versions such as 11.00.3513

On-premises Oracle source to all cloud target types

This section shows what SharePlex supports when the source Oracle database is on-premises and the target database is cloud-based.

Source Oracle version Supported cloud targets

Oracle 11.2.0.4

and

Oracle 12.1.0.2

Oracle 11.0.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS RDS

Oracle 12.1.0.2 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

My SQL 5.5 on AWS RDS*

Aurora Postgres on AWS RDS*

Postgres 9.5.2 on AWS RDS*

Postgres 9.5.2 on AWS EC2 Linux

Postgres 9.5.2 on Azure PaaS*

SQL Server 2012 on AWS RDS*

SQL Server 2012 on Azure IaaS

SQL Server 2014 on AWS RDS*

SQL Server 2014 on Azure IaaS

SQL Server 2014 on Azure PaaS*

SQL Server 2016 on AWS RDS*

SQL Server 2016 on Azure IaaS

SQL Server 2016 on Azure PaaS*

* In a PaaS environment that hosts an Open Target (non-Oracle) database, SharePlex cannot be installed on the cloud server, nor does it have access to the underlying operating system. In a PaaS environment, you must install SharePlex on a server that is external to the cloud deployment. You then configure SharePlex to interact with the target database through a remote connection. Because the connection originates locally, use the target database ODBC driver that matches the local platform.

Cloud Oracle source to all cloud target types

This section shows the supported targets when the source Oracle database is cloud-based and the target is cloud-based.

Source Oracle version Supported cloud targets
Oracle 11.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS RDS

Oracle 12.1.0.2 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

My SQL 5.5 on AWS RDS

Aurora Postgres on AWS RDS

Postgres 9.5.2 on AWS RDS

Postgres 9.5.2 on AWS EC2 Linux

SQL Server 2012 on AWS RDS

SQL Server 2014 on AWS RDS

SQL Server 2016 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS RDS

Oracle 12.1.0.2 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

My SQL 5.5 on AWS RDS

Aurora Postgres on AWS RDS

Postgres 9.5.2 on AWS RDS

Postgres 9.5.2 on AWS EC2 Linux

SQL Server 2012 on AWS RDS

SQL Server 2014 on AWS RDS

SQL Server 2016 on AWS RDS

Oracle 11.2.0.4 on AWS RDS

Oracle 11.0.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS RDS

Oracle 12.1.0.2 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

Oracle 12.1.0.2 on AWS RDS

Oracle 11.0.2.0.4 on AWS EC2 Linux

Oracle 11.0.2.0.4 on AWS RDS

Oracle 12.1.0.2 on AWS RDS

Oracle 12.1.0.2 on AWS EC2 Linux

**** Capture from AWS RDS is supported for Oracle versions 11.2.0.4.v11 and later, and 12.1.0.2.v7 and later. Post is supported for all Oracle versions that are supported by RDS and SharePlex. Support of Oracle features on RDS is limited to the default option groups: default:oracle-ee-12-1 and default:oracle-ee-11-2.

Supported data types — Oracle to Oracle

SharePlex supports the following Oracle data types for replication to Oracle targets. See the Conditions of suppport for additional information.

  • ANYDATA 1
  • B-FILE
  • BINARY DOUBLE
  • BINARY FLOAT
  • BLOB 2
  • CHAR
  • CLOB 2
  • DATE
  • INTERVAL
  • LONG RAW2
  • LONG 2
  • NCHAR
  • NCLOB 3
  • NUMBER
  • NVARCHAR25
  • RAW5
  • ROWID
  • SDO_GEOMETRY
  • TIMESTAMP
  • TIMESTAMP WITH LOCAL TIME ZONE
  • TIMESTAMP WITH TIME ZONE
  • UDT (User Defined Type)6
  • VARCHAR
  • VARCHAR25
  • VARRAY collectors
  • XMLTYPE 4

Conditions of suppport

1. Conditions of support - ANYDATA

  • Replication of ANYDATA is supported, except when it contains a UDT or the storage is defined as SecureFile LOB with compression. SecureFile LOB without compression is supported. Only the following datatypes within ANYDATA are supported by SharePlex:

    • CHAR
    • DATE
    • NUMBER
    • RAW
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
  • A datatype within an ANYDATA type must be in-row (inside the column data). Oracle encapsulates the ANYDATA type as an in-row securefile LOB. If the encapsulated data is longer than the length allowed for an in-row LOB, that data appears as out-of-row LOB data. SharePlex assumes that the encapsulated data always appears as in-row LOB.

2. Conditions of support - LONG and LOB (BLOB, CLOB)

  • Any table that contains a LOB or LONG should have a primary key or unique key defined on it. If a table does not have a key, SharePlex builds its own key from all of the columns except LONGs or LOBs. If a LOB or LONG is the only difference between two rows that otherwise satisfy the Post WHERE clause, SharePlex cannot guarantee that the correct row will be updated.
  • SharePlex supports SecureFile LOBs as follows:

    • Logging must be enabled.
    • SharePlex supports uncompressed SecureFile LOBS and SecureFile LOBS with high or medium compression
    • SecureFile LOBS are not supported when the storage specification includes encryption and/or deduplication.
  • SharePlex does not support LONG and LONG_RAW datatypes in a configuration where transformation or conflict resolution are performed.

3. Conditions of support - NCLOB

NCLOBs are not supported by Compare/Repair if the source and target have different character sets.

4. Conditions of support - XMLTYPE

SharePlex supports the replication of XMLTYPE stored as CLOB and BINARY. SharePlex does not support XMLTYPE stored as OBJECT RELATIONAL. Additionally, the following applies:

  • XMLTYPE stored as BINARY is not supported when the storage is defined as SecureFile LOB with compression. SecureFile LOB without compression is supported.
  • Binary XMLTypes are not supported by Compare/Repair when the source and target character sets are different and require character set conversion.
  • Compare/Repair does not support comparison of XMLTYPE when the source and target have different storage clauses. Compare/Repair only supports compare/repair when source and target are both stored as BINARY or both are stored as CLOB.

5. Conditions of support - 12c Extended datatypes

With the introduction of Extended Data Types, Oracle 12c provides the option to increase the maximum sizes of the following data types:

  • VARCHAR2 up to 32767 bytes
  • NVARCHAR2 up to 32767 bytes
  • RAW up to 32767 bytes

SharePlex does not support the 12c Extended Data Type sizes. SharePlex supports the affected data types, but only up to the former 4000-byte limit for VARCHAR2 and NVARCHAR2 and up to the former 2000-byte limit for RAW — regardless of the character semantics used. Therefore, the total number of characters that are supported depends on the character set that is being used. For example, if your character set can reach 4 bytes per character (the limit is based on the potential, not actual, size of the data) then the maximum supported VARCHAR2 is 1000 characters.

6. User-defined types

SharePlex does not support abstract datatypes and VARRAYs in a configuration where the SharePlex conflict resolution feature is performed.

7. Conditions of support - general

If the precision or size of the target datatype is not large enough for the data being replicated, the data may be truncated or rounded by the database when applied.

Supported data types — Oracle to Open Target

This section contains a grid that can be read vertically and horizontally to understand how SharePlex supports replication between an Oracle source and the various Open Target targets that SharePlex supports.

  • (Column 1) Shows the Oracle data types that SharePlex can replicate to an Open Target target type.
  • (Column 2) Shows the native data types that SharePlex supports for each target type.
  • (Rows spanning Column 1-Column 2) Shows the default data type mapping (native Oracle data type to native target data type) that SharePlex uses when replicating Oracle ALTER TABLE...ADD COLUMN DDL operations. Post uses the specified native target data type in the column definition of the command on the target. The default mapping is what was tested and is supported. You can change the default mapping with the set datatype option of the target command, with the understanding that you become responsible for ensuring data compatibility. For more information, see the target command documentation in the SharePlex Reference Guide.

NOTES about open Target Data type support: 
  • Replication of SecureFile LOBS to non-Oracle databases is supported except if the storage specification includes any level of compression, encryption, and/or deduplication.
  • Replication of LOB operations generated by dbms_lob are not supported when replicating to non-Oracle targets.

  • SharePlex applies replicated Oracle data to the target according to the data type of the target column, rather than mapping to a default data type. Corresponding source and target columns must contain compatible datatypes to ensure successful DML operations.
  • If the precision or size of the target datatype is not large enough for the data being replicated, the target database may truncate or round the data when SharePlex applies it.

Oracle to HANA supported data types

Oracle HANA
BINARY_DOUBLE double
BINARY_FLOAT double
BLOB blob
CHAR char
CLOB clob
DATE date
LONG clob
LONGRAW blob
NCHAR nchar
NCLOB nclob
NUMBER decimal
NVARCHAR nvarchar
RAW varbinary
TIMESTAMP timestamp
VARCHAR/VARCHAR2 varchar

Conditions of support

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to MySQL supported data types

Oracle MySQL
BINARY_DOUBLE1 double
BINARY_FLOAT double
BLOB longblob
CHAR char
CLOB longtext
DATE datetime
LONG text
LONGRAW blob
NUMBER numeric
RAW varbinary
TIMESTAMP2 datetime
VARCHAR2 varchar

Conditions of support

  1. Because Oracle and MySQL handle this data type differently, there may be a very small difference in precision between the value of the source and the value of the target row. You may see the difference when viewing the data from SQL*Plus or other utility, but SharePlex will not report this difference as an out-of-sync condition.
  2. If the MySQL target database is earlier than version 5.6.4, the fractional seconds will be truncated. Earlier versions did not support fractional seconds.
  3. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to PostgreSQL supported data types

Oracle PostgreSQL
BINARY_DOUBLE double precision
BINARY_FLOAT 1 double precision
BLOB bytea*
CHAR char
CLOB text
DATE date
LONG text
NCHAR char
NCLOB text
NUMBER numeric
NVARCHAR varchar
RAW bytea*
TIMESTAMP timestamp
VARCHAR2 varchar

Conditions of support

  1. The Oracle BINARY_FLOAT and PostgreSQL double precision data types have different precision. Make certain the source and target lengths are compatible. Numbers of 18801973500000000000 or larger can result in out-of-sync errors.
  2. BLOB and RAW mappings to bytea are not supported for EDB Postgres Advanced Server due to limitations of the ODBC driver, but is supported for other PostgreSQL implementations.
  3. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to SAP ASE supported data types

Oracle SAP ASE
BINARY_DOUBLE float
BINARY_FLOAT float
BLOB image
CHAR char
CLOB text
DATE datetime
LONG text
LONGRAW image
NUMBER numeric
RAW varbinary
VARCHAR2 varchar

Conditions of support

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to SQL Server supported data types

Oracle SQL Server
ANYDATA sql_variant
BINARY_DOUBLE float
BINARY_FLOAT float
BLOB image
CHAR char
CLOB text
DATE datetime2
LONG text
LONGRAW image
NCHAR nchar
NCLOB ntext
NUMBER numeric
NVARCHAR nvarchar
RAW varbinary
TIMESTAMP WITH TIME ZONE datetimeoffset
TIMESTAMP datetime2
VARCHAR2 varchar

Conditions of support

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to Sybase supported data types

Oracle Sybase
BINARY_DOUBLE float
BINARY_FLOAT float
BLOB image
CHAR char
CLOB text
DATE datetime
LONG text
LONGRAW image
NUMBER numeric
RAW varbinary
VARCHAR2 varchar

Conditions of support

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to Teradata supported data types

Oracle Teradata
CHAR char
DATE timestamp
NUMBER number
VARCHAR/VARCHAR2 varchar

Conditions of support

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to Tibero supported data types

Oracle Tibero
BLOB blob
CHAR char
CLOB clob
DATE date

FLOAT

float

LONG long

LONGRAW

longraw

NCHAR nchar*
NUMBER number
NVARCHAR nvarchar*
RAW raw
TIMESTAMP timestamp
VARCHAR varchar

Conditions of support

* NCHAR and NVARCHAR mappings are not supported for Tibero on the Linux platform due to a limitation of the ODBC driver.

See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

NCLOB is not supported on Linux because of the driver limitation and not on Windows because of JIRA SPO-13226. This is scheduled to be fixed in SharePlex v.9.2.0.

Oracle to XML supported data types

This mapping applies to all targets that receive messages as XML:

  • File in XML format
  • JMS
  • Kafka
Oracle XML
BINARY_DOUBLE decimal
BINARY_FLOAT decimal
BLOB base64Binary
CHAR string
CLOB string
DATE dateTime
INTERVAL DAY TO SECOND dayTimeDuration
INTERVAL YEAR TO MONTH yearMonthDuration
LONG string
NCHAR string
NCLOB string
NUMBER decimal
NVARCHAR2 string
RAW base64Binary
ROWID string
TIMESTAMP dateTime
TIMESTAMP WITH LOCAL TIME ZONE dateTimeStamp
TIMESTAMP WITH TIME ZONE dateTimeStamp
VARCHAR2 string
VARRAY 1 Post maps the data types in the source VARRAY to the appropriate XML data types. Only certain data types in a VARRAY are supported. See the conditions of support.

Conditions of support

  1. Only the following data types in a VARRAY are supported by SharePlex when replicating to XML output:

    • BINARY_FLOAT
    • VARCHAR2
    • BINARY_DOUBLE
    • NUMBER
    • TIMESTAMP (This is converted to a DATE data type without microseconds in XML output)
    • DATE
    • UDT (only if it contains one of the data types in this list)
  2. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Supported objects and operations for DML replication

SharePlex supports DML operations on tables and sequences, with some conditions. DML can be replicated from an Oracle source to an Oracle target or an Open Target target.

Note: Replication to targets with a different character set is supported, with limitations. When posting to Open Target databases, XML files and JMS, SharePlex supports only UNICODE and US7ASCII on the target, but conversion can be performed by an Oracle client installed on the target system. For full details, see the Preinstallation Checklist in the SharePlex Installation and Setup Guide for an Oracle Source.

Tables

SharePlex supports the following DML operations on tables:

  • INSERT, UPDATE, DELETE, COMMIT, ROLLBACK
  • Direct-path loads (SQL*Loader) (INSERT AND FULL ROLLBACK). Note: Replication of partial rollbacks of DLOADs is not supported.

The target table must support the column types that are being replicated from the source Oracle table.

SharePlex supports the following table types for DML operations:

Supported table type*

Conditions of Support

Regular Oracle tables

None

Index-organized tables

SharePlex does not support:

  • Replication to/from index-organized tables that contain LOB or VARRAY columns
  • Replication from a non-index-organized table to an index-organized table
  • SharePlex horizontal partitioning of index-organized tables

Partitioned tables

None

Views

None

Materialized views

SharePlex does not support replication from a materialized view to a materialized view. SharePlex can replicate the underlying table of a materialized view to a regular target table.

SharePlex does not support the following table types:

  • Replication to/from nested tables
  • Replication to/from clustered tables

*SharePlex does not support the following for any table type:

  • Replication to/from Identity columns.

  • Replication of rows changed by 'UPDATE WITH CASE' syntax.

  • Replication of rows in which data exceeds 319 KB in size, excluding LOB and LONG columns

Sequences

  • Transactional changes to Oracle sequences can be replicated only from Oracle to Oracle.
  • To replicate transactional changes to sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.

Secured and compressed objects

SharePlex supports DML operations on secured or compressed objects as follows:

Secured/Compressed Object Can be replicated to:  
DML on tables encrypted by Oracle Obfuscation Toolkit All target types  

DML on tables with Transparent Data Encryption (TDE)1

All target types except Oracle 11gR1  

DML on Oracle Label Security (OLS)2

All target types  

DML on compressed objects: Basic, OLTP, HCC3

All target types  

Direct Load (DLOAD) on compressed objects: Basic, OLTP, HCC3

All target types  

1. Conditions of support - TDE

SharePlex supports Tablespace Encryption and Column Encryption for Oracle 11g R2 and later versions, both source and target.

The SharePlex copy/append command does not support TDE.

2. Conditions of support - OLS

By default, SharePlex cannot process rows that are protected by OLS (Oracle Label Security) because users granted DBA privileges (like SharePlex) do not have enough privileges to access those rows. However, if privilege is granted from the OLSSYS user, SharePlex can support OLS. SharePlex was tested under the following OLS configuration:

  • The SharePlex user was granted full privilege to the objects in replication.
  • Test tables with OLS security were created.
  • Data was replicated using INSERT, UPDATE, DELETE, ROLLBACK, and TRUNCATE operations, and ALTER TABLE to add and drop columns.

Note: If the OLS policy data label column is defined as hidden, the data in that column cannot be replicated by SharePlex.

3. Conditions of support - compression

Under certain conditions, Oracle does not provide enough information in the redo log for SharePlex to replicate a DML operation on a compressed object. Testing of compressed objects is advised.

Supported operations and objects for DDL replication

SharePlex provides default and optional DDL support.

Note: Support for DDL on compressed Oracle objects is limited to the following:

  • SharePlex supports DDL on compressed objects only for Oracle 11gR2 and later source Oracle databases.
  • ALTER TABLE to ADD COLUMN and ALTER TABLE to DROP COLUMN are supported on Basic, OLTP, and HCC compressed Oracle objects for replication to all Oracle and Open Target targets.

SharePlex does not support:

  • ANALYZE TABLE and ANALYZE INDEX
  • DDL to ALTER TABLE ADD OVERFLOW to an IOT in replication
  • DDL operations issued by the SharePlex database user

Oracle-to-Oracle DDL support

This section outlines both default and optional DDL support.

DDL on objects listed in the active configuration file

SharePlex supplies default and optional DDL replication for objects whose names are listed in the configuration file explicitly or by wildcard.

For details on these parameters, see the SharePlex Reference Guide.

Supported object Supported DDL Operation

Enabled by default?

Controlling parameter

Table and IOT

(when they exist in the source at the time of configuration activation)

Truncate

Alter to add, drop, modify columns

Alter to add, modify, drop, split, coalesce, move, truncate, exchange partition or subpartition1

Yes

SP_OCT_REPLICATE_DDL=3

Table and IOT

(when they are added to the source after configuration activation)

Create

Create as Select2

Drop

Yes

SP_OCT_AUTOADD_ENABLE=1

Index

Create

Alter

Drop

Yes

SP_OCT_AUTOADD_ENABLE=1

Materialized View3

Create

Drop

No

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_AUTOADD_MV=1 and

SP_SYS_TARGET_COMPATIBILITY=8.6.2 or higher

Sequences4

Create, Drop

No

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_AUTOADD_SEQ=1 and

SP_SYS_TARGET_COMPATIBILITY=8.6.3 or higher

Trigger

Create, Drop

No

SP_OCT_REPLICATE_TRIGGER=1

Synonym

Create, Drop

No

SP_OCT_REPLICATE_SYNONYM=1
Grant5 Create, Drop

No

SP_OCT_REPLICATE_GRANT=1

Conditions of support

  1. ALTER TABLE has the following conditions of support:

    • ALTER TABLE DDL that involves multiple tables, such as EXCHANGE PARTITION between tables, requires all tables involved to be in replication.
    • Default DDL support for ALTER TABLE on partitions/subpartitions applies to user-named interval partitions/subpartitions. For system-named (system generated) interval partitions/subpartitions, SharePlex supports only ALTER TABLE to DROP and TRUNCATE the system-named partitions. To enable support for DROP and TRUNCATE of system-named partitions, set the SP_OCT_TRUNC_PARTITION_BY_ID parameter to 1, and ensure that both source and target are updated to SharePlex version 8.6.4 or later. NOTE: This requirement has changed from previous SharePlex releases in that the parameter must now be set regardless of the Oracle version, including for Oracle 12.2 and later.
    • SharePlex does not support TRUNCATE of a system-generated sub-partition if the sub-partition is empty. In such cases, Post stops with error SP-OPO01002. To configure Post to ignore this error, stop Post, set the SP_OPO_CONT_ON_ERR parameter to 1, and add error number SP-OPO01002 to the top of the oramsglist file. See the SharePlex Reference Guide for more information about this parameter.
  2. For CREATE TABLE AS SELECT, SharePlex supports the following datatypes: LONG, DATE, RAW, LONG RAW, ROWID, LONG VARCHAR, CHAR, CLOB, BLOB, CFILE, BFILE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND and ANYDATA.
  3. For CREATE MATERIALIZED VIEW the following applies:

    • SharePlex supports the following datatypes for CREATE MATERIALIZED VIEW: LONG, DATE, RAW, LONG RAW, ROWID, LONG VARCHAR, CHAR, CLOB, BLOB, CFILE, BFILE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND and ANYDATA. SharePlex does not support ALTER MATERIALIZE VIEW.
    • SharePlex converts a CREATE MATERIALIZED VIEW to a CREATE TABLE, applies the CREATE TABLE to the target, and then replicates the DML that populates the view. SharePlex does not replicate materialized views to materialized views.
  4. To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.

  5. Amazon RDS restricts DBA users from issuing the GRANT command. Because SharePlex runs as the DBA user, SharePlex cannot replicate GRANT to or from an RDS database.

DDL on objects not listed in the active configuration file

SharePlex provides expanded DDL support for Oracle objects that are not listed in the configuration file. SharePlex replicates the DDL statements for these objects, but does not maintain synchronization of the objects through DML replication. Expanded DDL replication is optional and is enabled with the following parameter setting:

SP_OCT_REPLICATE_ALL_DDL=1

Note: Expanded DDL replication supports not only tables and sequences but also a wide range of other objects such as procedures, functions, users, and views, which are not part of replication. Some of these objects may have underlying objects that are in replication. In those cases, Expanded DDL replication applies to the underlying objects in replication, as well as to the object that is not in replication.

Supported object Supported Operation
Table and IOT Create table
Create table as select
Alter table add column
Alter table drop column
Drop table
Truncate
Comment on table
Comment on columns
Associate Statistics
Disassociate Statistics
Cluster Create cluster
Crop cluster
Sequence Create
Drop
Alter

Partition/

Subpartition

Add
Split
Merge
Drop
Modify
Coalesce
Exchange
Move
Truncate
Rename
Set
Index Create
Alter
Drop
View Create
Alter
Drop
Comment on view
Synonym Create
Drop
Directory1 Create
Drop
User-defined type Create type
Alter type
Drop type
Create type body
Drop type body
Stored procedure Create
Alter
Drop
Stored function Create
Alter
Drop
Package Create package
Create package body
Alter package
Alter package body
Drop package
Drop package body
User Create user
Alter user
Drop user
Grant1
Revoke
Role Create role
Alter role
Drop role
Grant1
Revoke

1. Amazon RDS restricts DBA users from issuing certain commands on an Amazon RDS database instance. Because SharePlex runs as the DBA user, SharePlex cannot replicate the following commands to or from an RDS database:

  • CREATE or DROP DIRECTORY
  • GRANT

Oracle-to-Open Target DDL support

SharePlex provides limited DDL support for replication from Oracle to Open Target. The involved objects must be listed in the active configuration file explicitly or with a wildcard. There is no optional DDL support for replication from Oracle to Open Target.

The default Oracle to Open Target configuration is represented by the following parameter setting:

SP_OCT_REPLICATE_DDL=3

Supported object Supported Operation
Table and IOT Truncate1
Alter table add column2
Alter table drop column
  1. TRUNCATE TABLE is not supported from Oracle compressed objects to Open Target targets.

  2. The default mappings of Oracle data types to their Open Target counterpart for replicated ALTER TABLE ADD COLUMN DDL is shown in Supported data types — Oracle to Open Target. When SharePlex adds the column, it defines it with the default data type. This mapping applies to the entire configuration (not per table).

Non-supported Oracle features

This is a list of commonly used Oracle features which SharePlex does not replicate and are not listed elsewhere in this document. Since both Oracle and SharePlex continually change and improve, this list of exclusions cannot be considered complete. Unless an item is stated here as being supported, assume that SharePlex does not replicate it.

Object/operation/feature Not supported
Oracle operations Operations that do not appear in the redo logs. This includes any DML or DDL not in the redo logs, and also PL/SQL packages which do not write results to the redo logs, for example, dbms_shared_pool.keep and related packages.

dbms_scheduler.create_job

This object is not supported.

Flashback

SharePlex does not support the Oracle Flashback Table feature. If the SP_REPLICATE_ALL_DDL parameter is enabled (value of 1), SharePlex may try to replicate the flashback DDL, which will return an error. To perform Flashback Table on a table that is in replication, use the following procedures to work around this issue:

  1. Remove source objects from replication
  2. Perform the flashback
  3. Add or change objects in an active configuration

Supported/non-supported SharePlex featues for Open Target

The following table shows whether specific SharePlex features are supported for replication from Oracle to Open Target targets.

SharePlex feature Supported for Open Target Not Supported for Open Target
reconcile command (target instantiation) X  
compare/compare using and repair/repair using commands   X
copy/copy using and append/append using commands   X
Hash horizontally partitioned replication   X
Column-based horizontally partitioned replication X (except Teradata)  
Vertically partitioned replication X  
Column mapping X  
Key definition X  
Build configuration with scripts   X
Named queues X  
Commit Reduction (feature of Post Enhanced Performance) X  
Dependency Checking (feature of Post Enhanced Performance)   X
Transformation   X
Conflict resolution   X
Peer-to-peer replication (bi-directional)   X
Consolidated replication (many to one) X  
Broadcast replication (one to many) X  
High availability replication (active/passive bi-directional)   X
Change tracking target (CDC)   X
Data encryption X  
Data compression X  
SSH X  
auth_hosts file X  
Monitoring scripts X  
SNMP monitoring X  
Continue posting on error (SP_OPX_CONT_ON_ERR) X  
Suspend on out of sync errors (SP_OPX_OUT_OF_SYNC_SUSPEND) X  
Reduced key (SP_OPX_REDUCED_KEY) X  
Logical Transaction Rollback on out-of-sync transactions   X
Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents