1 Understanding What’s Supported

This chapter contains support information for Oracle GoldenGate on Oracle Database.

Topics:

Summary of Supported Oracle Data Types and Objects Per Capture Mode

This topic describes how Oracle GoldenGate supports the Oracle data types according to the capture mode that you choose.

To know more information about capture modes, see Deciding Which Capture Method to Use..

Detailed support information for Oracle data types, objects, and operations starts with Details of Support for Objects and Operations in Oracle DML.

Data type Classic capture Integrated capture

Scalar columns including DATE and DATETIME columns

Captured from redo.

Captured from redo.

LONG VARCHAR

Not supported.

Captured from redo.

BASICFILE LOB columns

LOB modifications done using DML (INSERT/UPDATE/DELETE) are captured from redo.

LOB modifications done using DBMS_LOB package are captured from the source table by fetching values from the base table.

Captured from redo.

SECUREFILE LOB columns

Captured from redo, except for the following cases where SECUREFILE LOBs are fetched from the source table:

  • LOB is encrypted

  • LOB is compressed

  • LOB is deduplicated

  • LOB is stored in-line

  • LOB is modified using DBMS_LOB package

  • NOLOGGING LOBs

Captured from redo, except for the following cases where SECUREFILE LOBs are fetched from the source table:

  • LOBs is modified using DBMS_LOB.FRAGMENT_* procedures.

  • NOLOGGING LOBs.

  • Deduplicated LOBs when the source Oracle Database release/binary is less than 12.1.

Requires source database compatibility to be set to 11.2.0.0.0 or higher

Index Organized Tables (IOT)

Captured from redo with the following restrictions:

  • IOT with mapping table not supported.

  • Direct load inserts to IOT tables cannot have the SORTED clause.

  • IOT with prefix compression as specified with COMPRESS clause is not supported.

Captured from redo with the following restriction:

  • IOT with mapping table not supported.

XML columns stored as CLOB

Captured from redo.

Captured from redo.

Requires source database compatibility to be set to 11.0.0.0.0 or higher

XML columns stored as Binary

Fetched from source table.

Requires source database compatibility to be set to 11.2.0.3.0 or higher.

Fetched from source table if compatibility is less than 11.2.0.3.0.

XML columns stored as Object-Relational

Not supported.

Captured from redo.

Requires source database compatibility to be set to 11.2.0.3.0 or higher.

XML Type Table

Not supported.

Captured from redo.

User Defined Type (UDT) columns

Fetched from source table.

Captured from redo with limitations.

Specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Fetched from source table when:

  • USENATIVEOBJSUPPORT is not specified

  • If the redo compatibility is less than 12.0.0.0.0

  • If the UDT contains Nested Table, SDO_TOPO_GEOMETRY, or SDO_GEORASTER types

    Procedural supplemental logging must be enabled at the source so that TOPO and Georaster can be supported.

Invisible Columns

Not supported.

ANYDATA columns

Fetched from source table with the following data types only:

BINARY_DOUBLE

BINARY_FLOAT

CHAR

DATE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

NCHAR

NUMBER

NVARCHAR2

RAW

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIMEZONE

UDTs

VARCHAR/VARCHAR2

Requires source database compatibility to be set to 11.2.0.0.0 or higher.

Captured from redo with limitations.

Specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Fetched from source table when

  • "USENATIVEOBJSUPPORT is not specified

  • If the redo compatibility is less than 12.0.0.0.0

Spatial Types columns

Fetched from source table.

Captured from redo with limitations.

Specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo.

Use of Native Object Support requires source database compatibility to be set to 12.2.0.1.0 or higher.

Fetched from source table when:

  • USENATIVEOBJSUPPORT is not specified

  • If the redo compatibility is less than 12.0.0.0.0

  • If SDO_TOPO_GEOMETRY or SDO_GEORASTER (raster tables) are used

Procedural supplemental logging must be enabled at the source so that TOPO and Georaster can be supported.

Collections columns (VARRAYs)

Fetched from source table.

Captured from redo for VARRAY attributes

VARRAY attributes of UDT types are supported.

Specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo.

Requires source database compatibility to be set to 12.0.0.0.0 or higher.

Fetched from source table when

  • USENATIVEOBJSUPPORT is not specified

  • the redo compatibility is less than 12.0.0.0.0

  • If Top-level VARRAY columns are used, and compatibility is less than 12.2.

Collections columns (Nested Tables)

Fetched from source table with limitations.

See Details of Support for Objects and Operations in Oracle DML.

Fetched from source table with limitations.

See Details of Support for Objects and Operations in Oracle DML.

Object Table

Fetched from source table.

Captured from redo with limitations.

Specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Replication of DDL operations on an object table is not supported.

Fetched from source table with additional limitations when

  • USENATIVEOBJSUPPORT is not specified

  • If the redo compatibility is less than 12.0.0.0.0

  • If Nested Table, SDO_TOPO_GEOMETRY or SDO_GEORASTER (raster tables) are used

Transparent Data Encryption (Column Encryption & Tablespace Encryption)

Captured from redo.

Captured from redo.

No additional setup is required for local capture.

Requires source database compatibility to be set to 11.0.0.0.0 or higher.

Basic Compression

Not supported.

Captured from redo.

OLTP-Compression

Not supported.

Captured from redo.

Exadata Hybrid Columnar Compression

Not supported.

Captured from redo.

XA on non-RAC database

Captured from redo.

Captured from redo.

XA on RAC database

Not supported.

To get support, must make sure all branches of XA goes to the same instance.

Captured from redo.

Requires source database compatibility to be set to 11.2.0.0.0 or higher.

PDML on non-RAC database

Captured from redo.

Captured from redo.

PDML on RAC database

Not supported.

To get support, you must make sure child transactions spawned from a PDML transaction do not span multiple instances.

Captured from redo.

Details of Support for Oracle Data Types

The following outlines details of Oracle data type support by Oracle GoldenGate. Unless otherwise noted, the support applies to both classic and integrated capture mode.

ANYDATA Data Types

The following ANYDATA data types are supported:

Fetched from source table with the following data types only:

  • BINARY_DOUBLE

  • BINARY_FLOAT

  • CHAR

  • DATEINTERVAL DAY TO SECOND

  • INTERVAL YEAR TO MONTH

  • NCHAR

  • NUMBER

  • NVARCHAR2

  • RAW

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIMEZONE

  • UDTs

  • VARCHAR/VARCHAR2

Limitations of Support — ANYDATA
  • Your source database compatibility must be set to 11.2.0.0.0 or higher. Support for named collections and VARRAYs embedded within those data types.

Numeric Data Types

The following numeric data types are supported:

  • NUMBER up to the maximum size permitted by Oracle

  • BINARY FLOAT

  • BINARY DOUBLE

  • UROWID

Limitations of Support — Numeric

The support of the range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

Character Data Types

The following character data types are supported:

  • CHAR

  • VARCHAR2

  • LONG

  • NCHAR

  • NVARCHAR2

Limitations of Support — Character
  • If an extended VARCHAR column is part of unique index or constraint, then direct path inserts to this table may cause Replicat to abend with a warning. Verify that the extended VARCHAR caused the abend by checking all_indexes/all_ind_columns for a unique index or all_cons_columns/all_constraints for a unique constraint. Once you determine that an extended VARCHAR, you can temporarily drop the index or disable the constraint:

    For Unique Index:

    drop index t2u;

    For Unique Constraint:

    alter table v32ind modify constraint sys_c0010125 disable;

  • Extended (32K) VARCHAR2 and NVARCHAR2 columns are supported when Extract is in integrated capture mode. All modes of Replicat support 32K VARCHAR2 and NVARCHAR2 columns. The following limitations apply:

    • Oracle GoldenGate does not support 32K VARCHAR2 and NVARCHAR2 columns as part of a key or unique index, nor as a column in a KEYCOLS clause of the TABLE or MAP parameter. 32K columns cannot be used as row identifiers because they are not supplementally logged even when part of a primary key.

    • 32K columns are not supported as resolution columns in a CDR (conflict resolution and detection) configuration nor as the basis for any other work that requires a column value to be present in the transaction log.

    • Oracle GoldenGate does not limit the number of 32K columns, but each trail record has a length limit of 4MB for inline records. The number of 32K columns that reaches this limit is approximately 160 columns, but the number of columns also depends on the actual size of the extended VARCHAR2 column.

Multi-byte Character Types

Multi-byte characters are supported as part of a supported character set. If the semantics setting of an Oracle source database is BYTE and the setting of an Oracle target is CHAR, use the Replicat parameter SOURCEDEFS in your configuration, and place a definitions file that is generated by the DEFGEN utility on the target. These steps are required to support the difference in semantics, whether or not the source and target data definitions are identical. Replicat refers to the definitions file to determine the upper size limit for fixed-size character columns.

For more information about character-set support, see Administering Oracle GoldenGate for Windows and UNIX.

For information about SOURCEDEFS and the DEFGEN utility, see Administering Oracle GoldenGate.

Limitations of Support — Multi-byte
  • For Oracle GoldenGate to support multi-byte character data, the source and target databases must be logically identical in terms of schema definition for the tables and sequences being replicated. Transformation, filtering, and other manipulation are not supported. The character sets between the two databases must be one of the following:

    • Identical, for example SHIFT-JIS on the source and on the target.

    • Equivalent, which is not the same character set but containing the same set of characters, for example SHIFT-JIS and EUC-JP.

    • Target is superset of the source: For example, UNICODE is a superset of all character types, and therefore of any other character set.

  • Multi-byte data is supported whether the length semantics are in bytes or characters.

Binary Data Types

The following binary data types are supported:

  • RAW

  • LONG RAW

Date and Timestamp Data Types

The following date and time data types are supported:

  • DATE

  • TIMESTAMP (see Limitations of support)

Limitations of Support — Dates
  • Oracle GoldenGate does not support negative dates.

  • INTERVAL DAY and INTERVAL YEAR are supported only if the size of the target column is equal to, or greater than, that of the source.

  • Oracle GoldenGate supports the capture and replication of TIMESTAMP WITH TIME ZONE as a UTC offset (TIMESTAMP '2011-01-01 8:00:00 -8:00').

  • TIMESTAMP WITH TIME ZONE as TZR (Region ID) is supported for the replication of data changes, but not for initial loads, for SQLEXEC, or for operations where the column must be fetched from the database. In these cases, the region ID is converted to a time offset by the database when the column is selected. Replicat replicates the timestamp as date and time data with a time offset value.

  • Oracle GoldenGate supports timestamp data from 0001/01/03 00:00:00 to 9999/12/31 23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

  • Oracle GoldenGate supports time offset values between +12:00 and -12:00.

To support TIMESTAMP WITH TIME ZONE specified as TZR properly, and also to handle TIMESTAMP WITH LOCAL TIMEZONE properly.

Large Object Data Types

The following large object types are supported:

  • CLOB

  • NCLOB

  • BLOB

  • SECUREFILE and BASICFILE

To find out the limitations of supporting large objects in classic capture mode, see:

Limitations of support — Large Object Classic Capture Mode
  • BASICFILE option LOBs are captured from the redo log, but are fetched from the database in the following circumstances:

    • Extract determines the LOB is invalid.

    • The LOB data is not in the redo log, which occurs when the BASICFILE LOB is created with the NOLOGGING option.

    • The LOB is created with the CACHE option.

    • The LOB is only partially updated. Oracle GoldenGate does not support partial column data. Extract assumes LOB data to be incomplete if the LOB does not start with a LOB reset record, or if the LOB does not start at the first byte and does not end at the last byte, according to the new LOB length. Partial updates can be generated by the following OCI calls: OCILOBWrite(), OCILobAppend(), OCiLobCopy(), OCILobLoadFromFile(), OCILobTrim(), and by updates made through procedures in the dbms_lob package.

    • Extract detects an anomaly in the LOB data.

  • SECUREFILE option LOBs are captured from the redo logs only when the update is complete and the LOB is not transformed (the column is not compressed or encrypted or deduplicated) and stored out-of-row. SECUREFILE LOBs are fetched from the database in the following circumstances:

    • The LOB is stored in-row.

    • The LOB is transformed either with compression or encryption.

    • The LOB is created with the CACHE attribute.

    • Extract determines that a LOB instance is invalid.

    • LOB data is missing from the redo log. This can occur if the LOB is created with any of following options: DEDUPLICATE, NOLOGGING, FILESYSTEM_LIKE_LOGGNG.

    • The LOB is updated using OCILOBWrite(), OCILobAppend(), OCiLobCopy(), OCILobLoadFromFile(), OCILobTrim(), or through procedures in the dbms_lob package.

    • Any other anomalies as detected by Extract.

  • When changing a SECUREFILE LOB from one storage to another (such as from ENCRYPT to DECRYPT), Oracle updates the whole table, and Extract captures those updates from the log. Therefore, it will appear as though Oracle updated all of the data blocks that are associated with the table. This also can happen when an ALTER TABLE command sets a DEFAULT value to a column that has null values.

  • In a manage bundled agents (XAG) high availability environment that has tables containing JavaScript Object Notation (JSON) columns, Extracts can extract this data though the default Replicat mode cannot replicate the data. You must set the DBOPTIONS NOSKIPTEMPLOB parameter to avoid Replicat abending.

XML Data Types

The following XML types are supported:

  • In integrated capture mode, Oracle GoldenGate supports XMLType columns and XMLType tables stored as XML CLOB, XML Object Relational, and XML Binary.

  • In classic capture mode, Oracle GoldenGate supports XMLType columns stored as XML CLOB and XML Binary.

Limitations of Support — Integrated and Classic Capture Modes

The following are not supported:

  • Filtering and manipulation are not supported. You can map the XML representation of an object to a character column by means of a COLMAP clause in a TABLE or MAP statement.

  • Oracle recommends the AL32UTF8 character set as the database character set when working with XML data. This ensures the correct conversion by Oracle GoldenGate from source to target.

  • Hierarchy-enabled tables are managed by the Oracle XML database repository and are supported with procedural logging.

  • Assuming DDL support is enabled, Oracle GoldenGate replicates the CTAS statement and allows it to select the data from the underlying target table(s). This is controlled using the TRANSLOGOPTIONS GETCTASDML parameter and applies to all typed tables. If the parameter is used, the OIDs are preserved. For XMLType tables, the row object IDs must match between source and target, which cannot be maintained when Replicat uses logical SQL statements. XMLType tables created by an empty statement (that does not insert data in the new table) can be maintained correctly.

  • XMLType tables with primary key-based object identifiers (OID)

  • Relational tables with a single XML column

  • SQL* Loader direct-path insert for XML Binary and XML Object Relational

  • XML Schema-based XMLType are supported, but changes made to XML Schemas are not replicated and must be registered on both source and target databases with the DBMS_XMLSCHEMA package.

  • Tables that contain XMLType columns must have at least one unique key constraint that is made up of scalar columns, or the combination of all scalar columns must guarantee uniqueness. Extract or Replicat cannot use unique or primary key constraints made up of XML attributes for row identification purposes.

Limitations of Support — Integrated Capture Mode
  • XML OR and XML Binary, for native capture. XML binary/OR will be fetched if compatible with releases greater than 11.2.0.3.

  • XML CLOB, source database compatibility is with releases greater than 11.0.0.0.0.

  • The maximum length for the entire SET value of an update to an XMLType is 32K, including the new content plus other operators and XQuery bind values.

Limitations of Support — XML Binary Classic Capture Mode
  • For XML Binary, Oracle GoldenGate fetches additional row data from the source database. Because the fetched data may not part of the original transaction, it may lead to inconsistency.

  • XML Object Relational is not supported in classic capture mode.

User Defined or Abstract Types

Oracle GoldenGate supports User Defined types (UDT) or Abstract Data Types (ADT) when the source and target objects have the same structure. The schema names can be different.

General Limitations of Support — Integrated and Classic Capture Modes
  • Redo-based supports most attribute types, but falls back to fetching from source table when UDT contains:

    • Nested Table

    • SDO_TOPO_GEOMETRY

    • SDO_GEORASTER

  • Fetch-based does not support UDT that contains:

    • ANYDATA

    • TIMESTAMP WITH TIMEZONE

    • TIMESTAMP WITH LOCAL TIMEZONE

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND

    • BINARY FLOAT

    • BINARY DOUBLE

    • BFILE

  • Oracle GoldenGate does not support UDTs that contain:

    • CFILE

    • OPAQUE (with exception of XMLType

  • A table that contains a UDT must have one of the following: a primary key, column(s) with a unique constraint, or a unique index.

  • Object or relational tables where the key contains a UDT, or where a UDT is the only column, are not supported.

  • The RMTTASK parameter does not support UDT.

  • CHAR and VARCHAR attributes that contain binary or unprintable characters are not supported.

  • UDTs, including values inside object columns or rows, cannot be used within filtering criteria in TABLE or MAP statements, or as input or output for the Oracle GoldenGate column-conversion functions, SQLEXEC, or other built-in data-manipulation tools. Support is only provided for like-to-like Oracle source and targets.

  • UDT and nested tables are supported with the following limitations:

    • Nested table UDTs cannot contain CHAR, NVARCHAR2, or NCLOB attributes.

    • Nested tables are not supported if there are extended (32k) VARCHAR2 or RAW attributes in UDTs.

    • Nested tables are not supported if there are CLOB or BLOB attributes in UDTs.

    • Nested table columns/attributes that are part of any other UDT are not supported.

Limitations for Collection Types — Integrated and Classic Capture Modes
  • When data in a nested table is updated, the row that contains the nested table must be updated at the same time.

  • When VARRAYS and nested tables are fetched, the entire contents of the column are fetched each time, not just the changes.

Limitations for Object Tables — Integrated and Classic Capture Modes

Integrated Capture Only (Redo-based)

  • Redo-based captures object tables from redo when compatible with Oracle Database 12.2 and greater, but falls back to fetching from source table when an object table contains the following attributes:

    • Nested table
    • SDO_TOPO_GEOMETRY
    • SDO_GEORASTER

    These objects are only fetched if they are compatible with Oracle GoldenGate 12.2.x.

  • To fully support object tables created with CREATE TABLE as SELECT (CTAS) statement, Integrated Capture must be configured to capture DML from the CTAS statement. For more information about CTAS, see CREATE TABLE AS SELECT.

  • An Oracle object table can be mapped to a non-Oracle object table in a supported target database.

Classic and Integrated Capture (Fetch-based)

  • Fetch-based fetches all leaf-level attributes, as well as, root-level LOB, XML, UDT, ANYDATA, and collection attributes.

  • Fetch-based does not support object tables that contain the following leaf-level attributes:

    • ANYDATA
    • TIMESTAMP WITH TIMEZONE
    • TIMESTAMP WITH LOCAL TIMEZONE
    • INTERVAL YEAR TO MONTH
    • INTERVAL DAY TO SECOND
    • BINARY FLOAT
    • BINARY DOUBLE
  • Oracle GoldenGate supports object tables in uni-directional and active-active configurations. Object tables are captured from the redo log, but certain data types that are fetched from the database when in regular relational tables, such as LOBs and collection types, are also fetched when in object tables. Similarly, current limitations that apply to collection types when in regular tables also apply to these types when in object tables.

  • A primary key must be defined on the root-level object attributes of the object table, and cannot include leaf-level attributes. If no key is defined, Oracle GoldenGate will use all useable columns as a pseudo-key.

  • Oracle GoldenGate does not support the replication of DDL operations for an object table. This limitation includes the database object versioning that is associated with ALTERs of object tables.

  • Synonyms are not supported for object tables or object types that contain object tables.

Limitations for Spatial Types — Integrated and Classic Capture Modes

Oracle GoldenGate supports SDO_GEOMETRY, SDO_TOPO_GEOMETRY, and SDO_GEORASTER (raster tables).

Non-Supported Oracle Data Types

Oracle GoldenGate does not support the following data types.

  • ANYDATA fetch-based column

    If you want to capture from an Advanced Queue (AQ) object, do not use normal table replication for that AQ object. Instead use the Procedural Replication functionality, see Procedural Replication Process Overview.

    The ANYDATA support is limited to normal table objects, not AQ objects.

  • ANYDATASET

  • ANYTYPE

  • MLSLABEL

  • ORDDICOM

  • TIMEZONE_ABBR

  • URITYPE

  • UDT containing an unsupported Oracle data type

See additional exclusions in Summary of Supported Oracle Data Types and Objects Per Capture Mode.

Details of Support for Objects and Operations in Oracle DML

This section outlines the Oracle objects and operations that Oracle GoldenGatesupports for the capture and replication of DML operations.

Supported Objects and Operations in Oracle DML

Identity Columns are supported.

Multitenant Container Database

Oracle GoldenGate captures from, and delivers to, a multitenant container database, see Configuring Oracle GoldenGate in a Multitenant Container Database.

Tables, Views, and Materialized Views

Oracle GoldenGate supports the following DML operations made to regular tables, index-organized tables, clustered tables, and materialized views.

  • INSERT

  • UPDATE

  • DELETE

  • Associated transaction control operations

Tip:

You can use the DBA_GOLDENGATE_SUPPORT_MODE data dictionary view to display information about the level of Oracle GoldenGate capture process support for the tables in your database. The PLSQL value of DBA_GOLDENGATE_SUPPORT_MODE indicates that the table is supported natively, but requires procedural supplemental logging. For more information, see the DBA_GOLDENGATE_SUPPORT_MODE. If you need to display all tables that have no primary and no non-null unique indexes, you can use the DBA_GOLDENGATE_NOT_UNIQUE. For more information, see DBA_GOLDENGATE_NOT_UNIQUE.

Limitations of Support for Regular Tables

These limitations apply to integrated and classic capture modes.

  • Oracle GoldenGate supports tables that contain any number of rows.

  • A row can be up to 4 MB in length. If Oracle GoldenGate is configured to include both the before and after image of a column in its processing scope, the 4 MB maximum length applies to the total length of the full before image plus the length of the after image. For example, if there are UPDATE operations on columns that are being used as a row identifier, the before and after images are processed and cannot exceed 4 MB in total. Before and after images are also required for columns that are not row identifiers but are used as comparison columns in conflict detection and resolution (CDR). Character columns that allow for more than 4 KB of data, such as a CLOB, only have the first 4 KB of data stored in-row and contribute to the 4MB maximum row length. Binary columns that allow for more than 4kb of data, such as a BLOB the first 8 KB of data is stored in-row and contributes to the 4MB maximum row length.

  • Oracle GoldenGate supports the maximum number of columns per table that is supported by the database.

  • Oracle GoldenGate supports the maximum column size that is supported by the database.

  • Oracle GoldenGate supports tables that contain only one column, except when the column contains one of the following data types:

    • LOB

    • LONG

    • LONG VARCHAR

    • Nested table

    • User Defined Type (UDT)

    • VARRAY

    • XMLType

  • Set DBOPTIONS ALLOWUNUSEDCOLUMN before you replicate from and to tables with unused columns.

  • Oracle GoldenGate supports tables with these partitioning attributes:

    • Range partitioning

    • Hash Partitioning Interval Partitioning

    • Composite Partitioning

    • Virtual Column-Based Partitioning

    • Reference Partitioning

    • List Partitioning

  • Oracle GoldenGate supports tables with virtual columns, but does not capture change data for these columns or apply change data to them: The database does not write virtual columns to the transaction log, and the Oracle Database does not permit DML on virtual columns. For the same reason, initial load data cannot be applied to a virtual column. You can map the data from virtual columns to non-virtual target columns.

  • Oracle GoldenGate will not consider unique/index with virtual columns.

  • Oracle GoldenGate supports replication to and from Oracle Exadata. To support Exadata Hybrid Columnar Compression, Extract must operate in integrated capture mode. To support Exadata Hybrid Columnar Compression, the source database compatibility must be set to 11.2.0.0.0 or higher.

  • Oracle GoldenGate supports Transparent Data Encryption (TDE).

    • Extract supports TDE column encryption and TDE table space encryption without setup requirements in integrated capture mode. For integrated capture, the source database must be Oracle version 11.1.0 with compatibility setting of 11.0.0.0 or higher.

    • In classic capture mode, Extract supports column encryption for all versions of Oracle 11.1 and later. Tablespace encryption is supported for all versions of Oracle 11.2.0.1 and later. TDE in classic capture mode requires some setup.

  • Oracle GoldenGate supports TRUNCATE statements as part of its DDL replication support, or as standalone functionality that is independent of the DDL support.

  • Oracle GoldenGate supports the capture of direct-load INSERT, with the exception of SQL*Loader direct-path insert for XML Binary and XML Object Relational as described in Limitations of Support — Integrated and Classic Capture Modes. Supplemental logging must be enabled, and the database must be in archive log mode. The following direct-load methods are supported.

    • /*+ APPEND */ hint

    • /*+ PARALLEL */ hint (Not supported for RAC in classic capture mode)

    • SQLLDR with DIRECT=TRUE

  • Oracle GoldenGate fully supports capture from compressed objects when Extract is in integrated capture mode. The source database version must be 11.2.0.0 or higher if capturing from a downstream mining database or 11.2.0.3 if the source database is the mining database. Extract in classic capture mode does not support compressed objects.

  • Oracle GoldenGate supports XA and PDML distributed transactions in integrated capture mode. Extract in classic capture mode does not support PDML or XA on RAC.

  • Oracle GoldenGate supports DML operations on tables with FLASHBACK ARCHIVE enabled. However, Oracle GoldenGate does not support DDL that creates tables with the FLASHBACK ARCHIVE clause or DDL that creates, alters, or deletes the flashback data archive itself.

Limitations of Support for Index-Organized Tables

These limitations apply to classic capture mode.

  • IOT with key compression enabled (indicated by the COMPRESS keyword in the key_compression clause) is not supported in classic capture mode, but is supported in integrated capture mode.

Limitations of Support for Views

These limitations apply to integrated and classic capture modes.

  • Oracle GoldenGate supports capture from a view when Extract is in initial-load mode (capturing directly from the source view, not the redo log).

  • Oracle GoldenGate does not capture change data from a view, but it supports capture from the underlying tables of a view.

  • Oracle GoldenGate can replicate to a view as long as the view is inherently updateable. The structures of the source tables and a target view must be identical.

Limitations of Support for Materialized Views

Materialized views are supported by Extract in classic and integrated modes with the following limitations.

  • Materialized views created WITH ROWID are not supported.

  • The materialized view log can be created WITH ROWID.

  • The source table must have a primary key.

  • Truncates of materialized views are not supported. You can use a DELETE FROM statement.

  • DML (but not DDL) from a full refresh of a materialized view is supported. If DDL support for this feature is required, open an Oracle GoldenGate support case.

  • For Replicat the Create MV command must include the FOR UPDATE clause

  • Either materialized views can be replicated or the underlying base table(s), but not both.

Limitations of Support for Clustered Tables

Indexed clusters are supported in both integrated and classic capture modes while hash clusters are not supported in either modes. In classic capture mode the following limitations apply:

  • Encrypted and compressed clustered tables are not supported in classic capture.

  • Extract in classic capture mode captures DML changes made to index clustered tables if the cluster size remains the same. Any DDL that causes the cluster size to increase or decrease may cause Extract to capture subsequent DML on that table incorrectly.

Sequences

  • Oracle GoldenGate supports the replication of sequence values in a uni-directional and active-passive high-availability configuration.

  • Oracle GoldenGate ensures that the target sequence values will always be higher than those of the source (or equal to them, if the cache is zero).

Limitations of Support for Sequences

These limitations apply to integrated and classic capture modes.

  • Oracle GoldenGate does not support the replication of sequence values in an active-active bi-directional configuration.

  • The cache size and the increment interval of the source and target sequences must be identical. The cache can be any size, including 0 (NOCACHE).

  • The sequence can be set to cycle or not cycle, but the source and target databases must be set the same way.

  • Tables with default sequence columns are excluded from replication for Coordinated Extract.

Non-supported Objects and Operations in Oracle DML

The following are additional Oracle objects or operations that are not supported by Extract in either classic or integrated capture mode:

  • REF  are supported natively for compatibility with Oracle Database 12.2, but not primary-key based REFs (PKREFs)

  • Sequence values in an active-active bi-directional configuration

  • Database Replay

  • Tables created as EXTERNAL

The following are not supported in classic capture mode:

  • Exadata Hybrid Columnar Compression

  • Capture from tables with OLTP table compression

  • Capture from tablespaces and tables created or altered with COMPRESS

  • Capture from encrypted and compressed clustered tables

  • Invisible column

  • Distributed transactions. In Oracle versions 11.1.0.6 and higher, you can capture these transactions if you make them non-distributed by using the following command, which requires the database to be restarted.

    alter system set _CLUSTERWIDE_GLOBAL_TRANSACTIONS=FALSE;
    
  • RAC distributed XA and PDML distributed transactions

  • Version enabled-tables

Details of Support for Objects and Operations in Oracle DDL

This topic outlines the Oracle objects and operation types that Oracle GoldenGate supports for the capture and replication of DDL operations.

Trigger-based capture is required for Oracle releases that are earlier than version 11.2.0.4. If Extract will run in integrated mode against a version 11.2.0.4 or later of Oracle Database, then the DDL trigger and supporting objects are not required.

Supported Objects and Operations in Oracle DDL

When the source database is Oracle 11.2.0.4 or later and Extract operates in integrated mode, DDL capture support is integrated into the database logmining server and does not require the use of a DDL trigger. You must set the database parameter compatibility to 11.2.0.4.0. In integrated capture mode, Extract supports DDL that includes password-based column encryption, such as:

  • CREATE TABLE t1 (a number, b varchar2(32) ENCRYPT IDENTIFIED BY my_password);

  • ALTER TABLE t1 ADD COLUMN c varchar2(64) ENCRYPT IDENTIFIED BY my_password;

Note:

Password-based column encryption in DDL is not supported in classic capture mode.

The following additional statements apply to both integrated and classic capture modes with respect to DDL support.

  • All Oracle GoldenGate topology configurations are supported for Oracle DDL replication.

  • Replication of In Database Row Archival is supported from Oracle GoldenGate 18c (18.1.0.0) onward. However, update and delete on archived rows is not supported.

  • Active-active (bi-directional) replication of Oracle DDL is supported between two (and only two) databases that contain identical metadata.

  • Oracle GoldenGate supports DDL on the following objects:

    • clusters

    • directories

    • functions

    • indexes

    • packages

    • procedure

    • tables

    • tablespaces

    • roles

    • sequences

    • synonyms

    • triggers

    • types

    • views

    • materialized views

    • users

    • invisible columns

  • Oracle Edition-Based Redefinition (EBR) database replication of Oracle DDL is supported for integrated Extract for the following Oracle Database objects:

    • functions

    • library

    • packages (specification and body)

    • procedure

    • synonyms

    • types (specification and body)

    • views

    EBR does not support use of DDL triggers.

  • Oracle GoldenGate supports DDL operations of up to 4 MB in size. Oracle GoldenGate measures the size of DDL statement in bytes, not in characters. This size limitation includes packages, procedures, and functions. The actual size limit of the DDL support is approximate, because the size not only includes the statement text, but also Oracle GoldenGate maintenance overhead that depends on the length of the object name, the DDL type, and other characteristics of keeping a DDL record internally.

  • Oracle GoldenGate supports Global Temporary Tables (GTT) DDL operations to be visible to Extract so that they can be replicated. You must set the DDLOPTIONS parameter to enable this operation because it is not set by default.

  • Oracle GoldenGate supports Integrated Dictionary for use with NOUSERID and TRANLOGOPTIONS GETCTASDML. This means that Extract will be obtaining object metadata from the LogMiner dictionary instead of the DDL trigger and without querying the dictionary objects. Oracle GoldenGate uses Integrated Dictionary automatically when the source database compatibility parameter is greater than or equal to 11.2.0.4 and Integrated Extract is used.

    The Integrated Dictionary feature is not supported with classic Extract.

    When using Integrated Dictionary and trail format in the Oracle GoldenGate release 12.2.x, Integrated Capture requires the Logminer patch to be applied on the mining database if the Oracle Database release is earlier than 12.1.0.2.

  • Oracle GoldenGate supports replication of invisible columns in Integrated Capture mode. Trail format release 12.2 is required. Replicat must specify the MAPINVISIBLECOLUMNS parameter or explicitly map to invisible columns in the COLMAP clause of the MAP parameter.

    If SOURCEDEFS or TARGETDEFS is used, the metadata format of a definition file for Oracle tables must be compatible with the trail format. Metadata format 12.2 is compatible with trail format 12.2, and metadata format earlier than 12.2 is compatible with trail format earlier than 12.2. To specify the metadata format of a definition file, use the FORMAT RELEASE option of the DEFSFILE parameter when the definition file is generated in DEFGEN.

  • DDL statements to create a namespace context (CREATE CONTEXT) are captured by Extract and applied by Replicat.

  • Extract in pump mode supports the following DDL options:

    • DDL INCLUDE ALL

    • DDL EXCLUDE ALL

    • DDL EXCLUDE OBJNAME

    The SOURCECATALOG and ALLCATALOG option of DDL EXCLUDE is also supported.

    If no DDL parameter is specified, then all DDLs are written to trail. If DDL EXCLUDE OBJNAME is specified and the object owner is does not match an exclusion rule, then it is written to the trail.

Non-supported Objects and Operations in Oracle DDL

These statements apply to integrated and classic capture modes.

Excluded Objects

The following names or name prefixes are considered Oracle-reserved and must be excluded from the Oracle GoldenGate DDL configuration. Oracle GoldenGate will ignore objects that contain these names.

Excluded schemas:

  "ANONYMOUS", // HTTP access to XDB
  "APPQOSSYS", // QOS system user
  "AUDSYS", // audit super user
  "BI", // Business Intelligence
  "CTXSYS", // Text
  "DBSNMP", // SNMP agent for OEM
  "DIP", // Directory Integration Platform
  "DMSYS", // Data Mining
  "DVF", // Database Vault
  "DVSYS", // Database Vault
  "EXDSYS", // External ODCI System User
  "EXFSYS", // Expression Filter
  "GSMADMIN_INTERNAL", // Global Service Manager
  "GSMCATUSER", // Global Service Manager
  "GSMUSER", // Global Service Manager
  "LBACSYS", // Label Security
  "MDSYS", // Spatial
  "MGMT_VIEW", // OEM Database Control
  "MDDATA",
  "MTSSYS", // MS Transaction Server
  "ODM", // Data Mining
  "ODM_MTR", // Data Mining Repository
  "OJVMSYS", // Java Policy SRO Schema
  "OLAPSYS", // OLAP catalogs
  "ORACLE_OCM", // Oracle Configuration Manager User
  "ORDDATA", // Intermedia
  "ORDPLUGINS", // Intermedia
  "ORDSYS", // Intermedia
  "OUTLN", // Outlines (Plan Stability)
  "SI_INFORMTN_SCHEMA", // SQL/MM Still Image
  "SPATIAL_CSW_ADMIN", // Spatial Catalog Services for Web
  "SPATIAL_CSW_ADMIN_USR",
  "SPATIAL_WFS_ADMIN", // Spatial Web Feature Service
  "SPATIAL_WFS_ADMIN_USR",
  "SYS",
  "SYSBACKUP",
  "SYSDG",
  "SYSKM",
  "SYSMAN", // Adminstrator OEM
  "SYSTEM",
  "TSMSYS", // Transparent Session Migration
  "WKPROXY", // Ultrasearch
  "WKSYS", // Ultrasearch
  "WK_TEST",
  "WMSYS", // Workspace Manager
  "XDB", // XML DB
  "XS$NULL",
  "XTISYS", // Time Index

Special schemas:

  "AURORA$JIS$UTILITY$", // JSERV
  "AURORA$ORB$UNAUTHENTICATED", // JSERV
  "DSSYS", // Dynamic Services Secured Web Service
  "OSE$HTTP$ADMIN", // JSERV
  "PERFSTAT", // STATSPACK
  "REPADMIN",
  "TRACESVR" // Trace server for OEM

Excluded tables (the * wildcard indicates any schema or any character):

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables
Other Non-supported DDL

Oracle GoldenGate does not support the following:

  • DDL on nested tables.

  • DDL on identity columns.

  • ALTER DATABASE and ALTER SYSTEM (these are not considered to be DDL) When using Integrated Dictionary you can replicate ALTER DATABASE DEFAULT EDITION and ALTER PLUGGABLE DATABASE DEFAULT EDITION. All other ALTER [PLUGABLE] DATABASE commands are ignored.

  • DDL on a standby database.

  • Database link DDL.

  • DDL that creates tables with the FLASHBACK ARCHIVE clause and DDL that creates, alters, or deletes the flashback data archive itself. DML on tables with FLASHBACK ARCHIVE is supported.

  • Classic capture mode does not support DDL that includes password-based column encryption, such as:

    • CREATE TABLE t1 (a number, b varchar2(32) ENCRYPT IDENTIFIED BY my_password);

    • ALTER TABLE t1 ADD COLUMN c varchar2(64) ENCRYPT IDENTIFIED BY my_password;

Integrating Oracle GoldenGate into a Cluster

If you installed Oracle GoldenGate in a cluster, take the following steps to integrate Oracle GoldenGate within the cluster solution.

For more information about installing and using Oracle GoldenGate in a cluster, see the Oracle GoldenGate with Oracle Real Application Clusters Configuration white paper.

General Requirements in a Cluster

  1. Configure the Oracle Grid Infrastructure Bundled Agent (XAG) to automatically manage the GoldenGate processes on the cluster nodes. Using the XAG will make sure the required cluster file system is mounted before the GoldenGate processes are started. If an application virtual IP (VIP) is used in the cluster the bundled agent will also ensure the VIP is started on the correct node.
  2. Configure the Oracle GoldenGate Manager process with the AUTOSTART and AUTORESTART parameters so that Manager starts the replication processes automatically.
  3. Mount the shared drive on one node only. This prevents processes from being started on another node. Use the same mount point on all nodes. If you are using the Oracle Grid Infrastructure Bundled Agent, the mounting of the required file systems are automatically carried out.
  4. Ensure that all database instances in the cluster have the same COMPATIBLE parameter setting.
  5. Configure Oracle GoldenGate as directed in this documentation.

Adding Oracle GoldenGate as a Windows Cluster Resource

When installing Oracle GoldenGate in a Windows cluster, follow these instructions to establish Oracle GoldenGate as a cluster resource and configure the Manager service correctly on all nodes.

  • In the cluster administrator, add the Manager process to the group that contains the database instance to which Oracle GoldenGate will connect.

  • Make sure all nodes on which Oracle GoldenGate will run are selected as possible owners of the resource.

  • Make certain the Manager Windows service has the following dependencies (can be configured from the Services control panel):

    • The database resource

    • The disk resource that contains the Oracle GoldenGate directory

    • The disk resource that contains the database transaction log files

    • The disk resource that contains the database transaction log backup files