SharePlex 9.2.6 - Upgrade Guide for Oracle Source

System Requirements for Oracle Capture

This chapter contains the requirements when capturing from an Oracle source database and replicating to a supported target database.

Prerequisites

• At least the minimum level of supplemental logging must be enabled. Some SharePlex features may require PK/UK supplemental logging to be enabled.

Supported Operating Systems

The following operating systems are supported for capture from a supported Oracle database and replication to a supported target database. For a list of supported source and target databases for Oracle capture, see Supported Source and Target Databases.

SharePlex supports only the 64-bit version of the listed operating systems.

Note:  SharePlex supports the following operating systems only.

• AIX 7.1 and 7.2
• CentOS Linux 5.x, 6.x, and 7.x
• HP-UX 11.31 Itanium
• Oracle Linux (RHCK and UEK) 5.x, 6.x, and 7.x
• RHEL Linux 5.x, 6.x, and 7.x
• SuSE SLES 11.x, 12.x, and 15
• Oracle Solaris (SPARC and x86) 10 and 11
• Windows Server 2008, 2008 R2, 2012, 2012 R2, and 2016

Supported Source and Target Databases

When capturing from an Oracle database and replicating to an Oracle or other database, the following database types and versions are supported for source and target.

NoteS:

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

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)⁵
• UROWID
• 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 data types within ANYDATA are supported by SharePlex:

  • CHAR
  • DATE
  • NUMBER
  • RAW
  • VARCHAR
  • VARCHAR2
  • TIMESTAMP
• A data type 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 SecureFiles LOBs as follows:

  • Logging must be enabled.
  • SharePlex supports uncompressed SecureFiles LOBS and SecureFiles LOBS with high or medium compression
  • SecureFiles LOBS are not supported when the storage specification includes encryption and/or deduplication.
• SharePlex does not support LONG and LONG_RAW data types 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. User-defined types

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

6. Conditions of support - general

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

7. BINARY DOUBLE and the BINARY FLOAT

The BINARY DOUBLE and the BINARY FLOAT data types are not supported for the copy command.

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 (non-Oracle targets) that SharePlex supports.

• Column 1: Shows the Oracle data types that SharePlex can replicate to an Open Target target type (non-Oracle target).
• Column 2: Shows the native data types that SharePlex supports for each target type.

• Rows spanning Column 1 and 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.

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

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

See "Conditions of support" following this table for additional information.

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

Conditions of support

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

See "Conditions of support" following this table for additional information.

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:

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 described in the following table. See "Conditions of support" following this table for additional information.

Conditions of 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 (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. See "Conditions of support" following this table for additional information.

For details about the controlling parameter, see the SharePlex Reference Guide.

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

See "Table Notes" following this table for additional information.

Table Notes:

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

See "Table Notes" following this table for additional information.

Table Notes:

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.

Supported / non-supported SharePlex features for Open Target

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