SharePlex 9.0 - Installation Guide

System Requirements — Oracle

This section contains information about SharePlex support for capture from an Oracle source database and replication to supported targets.

Basic requirements

• SQL*Plus must be installed for any source or target Oracle Database.
• (Linux and Unix) Quest recommends setting the hard-limit of the system file descriptors to a minimum of 1024 or higher, as the system permits.
• A source Oracle database must have at least the minimum level of supplemental logging enabled. Some datatype or operational support may require PK/UK supplemental logging to be enabled.

Supported platforms

This section shows the SharePlex support for replication from source Oracle database-platform combinations to target database-platform combinations, both Oracle and Open Target.

NoteS:

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

Supported platforms — Oracle source to Oracle target

Important: See also Cloud support for cloud-hosted targets.

NA: Not applicable: not supported by the database type or version.

X: Not yet tested.

* SharePlex only supports 64-bit Oracle Database. 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.

*** Oracle does not officially support Debian Linux.

Supported platforms — Oracle source to Open Target

Important: See also Cloud support for cloud-hosted targets.

* If a platform is not supported by a target type, it is not supported by SharePlex for that target type. For example, only R2 of Windows 2008 is supported by EDB Postgres Advanced Server, and therefore only by SharePlex.

** SharePlex supports all implementations of the PostgreSQL open source object-relational database system.For more information, see Basic system requirements.

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 ¹
• B-FILE
• BINARY DOUBLE
• BINARY FLOAT
• BLOB ²
• CHAR
• CLOB ²
• DATE
• INTERVAL
• LONG RAW
• LONG ²
• NCHAR
• NCLOB ³
• NUMBER
• NVARCHAR2
• RAW
• ROWID
• SDO_GEOMETRY
• TIMESTAMP
• TIMESTAMP WITH LOCAL TIME ZONE
• TIMESTAMP WITH TIME ZONE
• UDT (User Defined Type)
• VARCHAR
• VARCHAR2
• VARRAY collectors ⁴
• XMLTYPE ⁵

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.

• Replication of SecureFile LOBs (compressed high or medium, or uncompressed) is supported as follows:

  • Not supported if the storage specification includes encryption, and/or deduplication.
  • Logging must be enabled.
  • Replication to non-Oracle databases is supported except if the storage specification includes any level of compression, encryption, and/or deduplication. Logging must be enabled.

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 - VARRAYS

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)

5. 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 storage is defined as SecureFile LOB with compression. SecureFile LOB without compression is supported.
• Binary XMLTypes are not supported by Compare/Repair when source and target character sets are different and require character set conversion.
• Compare/Repair does not support comparison of XMLTYPE when 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.

6. Conditions of support - general

• When replicating DML operations to an Open Target database, SharePlex queries the target database to determine the target column definition before posting the data.

• 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 shows the following:

• The Oracle data types that SharePlex replicates to each supported Open Target target.
• The default mapping of those Oracle data types to their corresponding data types in the given target, for the purpose of establishing column definitions for replicated ADD COLUMN operations.

NOTES about open Target Data type support: 

• For replicated DML, SharePlex queries the target database to determine the appropriate data type to use.
• 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.
• To view the DDL operations that SharePlex supports per database, see Supported Oracle DDL operations

Oracle to HANA supported data types

Conditions of support

1. 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.
2. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to MySQL supported data types

Conditions of support

1. 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.
2. 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.
3. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to PostgreSQL supported data types

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. See Supported data types — Oracle to Oracle for additional conditions of support for Oracle source data types.

Oracle to SAP ASE supported data types

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

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

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

Conditions of support

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

Oracle to XML supported data types

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

• File in XML format
• JMS
• Kafka

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 Oracle DML operations and objects

SharePlex supports DML replication from the following Oracle objects:

• Any table can be replicated to an Oracle or non-Oracle target if the target supports all of the column types that are being replicated. This includes:

  • index-organized tables
  • partitioned tables
  • views
  • materialized views.
• Oracle sequences can be replicated from Oracle to Oracle. Replication of sequences is not supported to a non-Oracle target. Important! 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.
• 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 Database section of the Preinstallation Checklist.

Notes:

• SharePlex only replicates DML changes that are made to Oracle source objects that are listed in the configuration file.
• SharePlex replicates changes to those objects only when they appear in Oracle redo log files.

The following table summarizes SharePlex DML support.

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 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/TRUNCATE, 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.

Supported Oracle DDL operations

SharePlex provides default and optional DDL support.

Notes:

• SharePlex supports DDL on compressed objects only for Oracle 11gR2 and later source databases.

• ALTER TABLE to ADD COLUMN and ALTER TABLE to DROP COLUMN are supported on compressed source objects (Basic, OLTP, HCC) for all Oracle and Open Target targets.

• All other DDL on compressed source objects is supported ONLY for Oracle 11g and later targets.

• Any object or operation not listed in one of the tables in this section is not supported for that feature.

Default DDL support for objects in replication

This section shows the default DDL support for objects that are included in the active replication configuration either explicitly or with a wildcard.

Oracle-to-Oracle default DDL support

The default Oracle-to-Oracle DDL configuration is controlled by the following parameter settings:

SP_OCT_REPLICATE_DDL=3 and

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_REPLICATE_ALL_DDL=0

Conditions of support

1. Enabled by default through SP_OCT_AUTOADD_ENABLE=1. Auto-Add automatically adds an object to replication if the name satisfies a wildcard in the active replication configuration.

2. Enabled by default through SP_OCT_REPLICATE_DDL=3. This is the default DDL support for objects that are listed in the active configuration.

3. The full default DDL support 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. If the database is earlier than Oracle 12.2, 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. This is not required for Oracle 12.2 or later and any setting is ignored.

Note: SharePlex does not support TRUNCATE of a system-generated sub-partition if the sub-partition is empty, and Post stops with error SP-OPO01002. To configure Post to ignore this error, set the SP_OPO_CONT_ON_ERR parameter to 1 and add error number SP-OPO01002 to the top of the oramsglist file, before you restart Post.

4. 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.

Oracle-to-Open Target default DDL support

This section shows the default DDL support for Oracle objects when replicated to an Open Target, when the objects are included in the active replication configuration either explicitly or with a wildcard.

SharePlex only supports replication of TRUNCATE TABLE and ALTER TABLE to ADD COLUMN or DROP COLUMN to an Open Target database, file, or message container. No other DDL is supported to Open Target.

The default Oracle to Open Target configuration is controlled by the following parameters:

SP_OCT_REPLICATE_DDL=3 and

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_REPLICATE_ALL_DDL=0

1. Conditions of support - TRUNCATE TABLE

TRUNCATE TABLE is not supported from Oracle compressed objects to Open Target targets.

2. Conditions of support - data type mappings

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 Oracle. When SharePlex adds the column, it defines it with the default data type. This mapping applies to the entire configuration (not per table).

Note: These mappings are only used to establish column definitions for replicated ADD COLUMN operations. For replicated DML, SharePlex queries the target database to determine the appropriate data type to use.

• To view the default datatype mapping, use the target command with the show datatype option in sp_ctrl.
• To modify a mapping, use the target command with the set datatype option. SharePlex does not validate the accuracy of a custom mapping and Quest cannot be held responsible for any inaccuracies resulting from custom mappings. For syntax, use the help command in sp_ctrl or see the target command in the SharePlex Reference Guide.

Optional DDL support for objects in replication

Valid only for Oracle-to_Oracle replication

You can enable the replication of the following DDL with parameter settings when it is issued on objects that are in the configuration file.

Optional Auto-Add support for objects in replication

Valid only for Oracle-to_Oracle replication

Optional Auto-Add support enables newly created materialized views and sequences to be added to replication automatically if the name of the object satisfies a wildcard in the active configuration file. It is configured as follows:

Materialized Views:

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_AUTOADD_MV=1 and

SP_SYS_TARGET_COMPATIBILITY=8.6.2 or higher

Sequences:

SP_OCT_AUTOADD_ENABLE=1 and

SP_OCT_AUTOADD_SEQ=1 and

SP_SYS_TARGET_COMPATIBILITY=8.6.3 or higher

Notes:

• Not supported for Open Target.
• For CREATE MATERIALIZED VIEW, 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. SharePlex does not support ALTER MATERIALIZE VIEW.
• SharePlex does not replicate materialized views to materialized views. 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.

• 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.

Expanded DDL support for objects not in replication

DDL support for objects not listed in the configuration file is valid only for Oracle-to-Oracle replication. SharePlex replicates the DDL statements, but does not replicate any data change operations made to the objects because they are not part of active replication. The expanded DDL configuration is controlled by the following parameter setting:

SP_OCT_REPLICATE_ALL_DDL=1

NoteS: 

• Any object or operation not listed is not supported.
• 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.

Non-supported items for Oracle Database

This is a list of commonly used Oracle components that SharePlex does not replicate. Since both Oracle and SharePlex continually change and improve, this list of exclusions cannot be considered complete. Unless explicitly stated that an item is supported, you should assume that SharePlex does not replicate it.

Supported SharePlex featues — Oracle to Open Target

The following table shows whether specific SharePlex features are supported for Open Target targets.

System Requirements — SQL Server

This section contains information about SharePlex support for capture from a SQL Server source database and replication to supported targets.

SharePlex support for SQL Server as a target for Oracle data is documented in System Requirements — Oracle.

Supported versions and targets

SharePlex supports Capture for SQL Server versions 2012, 2014, and 2016 on Windows 2012 R2.

Conditions of support

This section shows the limitations of capturing from a SQL Server source.

• SQL Server native replication and SharePlex replication cannot be used at the same time for the same database.
• All SQL Server tables in the SharePlex replication configuration must be defined in the database with a primary key. A SharePlex key definition is not sufficient, nor can SharePlex use all columns as a key. You can use SharePlex filtering features in the configuration file to omit tables that do not have a primary key.
• A transaction must COMMIT to the SQL Server source before it can be captured by SharePlex.
• See the SharePlex Installation Guide for additional requirements and setup information.

Supported DML operations

SharePlex supports INSERT, UPDATE, and DELETE operations between SQL Server source tables and Oracle or SQL Server target tables.

Any SQL Server table can be replicated to an Oracle or SQL Server target if the target supports all of the column types that are being replicated.

Notes:

SharePlex only replicates DML changes that are made to SQL Server source objects that are listed in the configuration file.

SharePlex requires SQL Server Replication to be installed on the source system. See the SharePlex Installation Guide for more information.

Supported DDL operations

SharePlex does not support the replication of SQL Server DDL operations.

Supported data types

SharePlex supports DML operations that contain the following SQL Server data types:

• int
• numeric
• smallint
• tinyint
• float
• real
• date
• datetime
• datetime2
• datetimeoffset
• smalldatetime
• time
• char
• text
• varchar
• nchar
• ntext
• nvarchar
• binary
• varbinary
• image

Note: When replicating to an Oracle target, SharePlex queries the target database to determine the appropriate data type to use.

Supported SharePlex features — SQL Server to supported target

The following table shows whether or not SharePlex features are supported when SQL Server is the source.

SharePlex pre-installation checklist

Contents

Review and satisfy all of the requirements in this checklist before installing SharePlex or before your SharePlex consultant arrives, if you have contracted with our Professional Services team.

Note: The requirements in this checklist apply to all source and target systems where SharePlex will be installed unless otherwise noted.

Network checklist

111.22.33.44   sysA.company.com   sysA   # source system
55.66.77.88    sysB.company.com   sysB   # target system

host    all     all     sourceip     md5