1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. Oracle
  5. Oracle: Supported Data Types, Objects, and Operations for DDL and DML
  6. Details of Support for Objects and Operations in Oracle DML

Details of Support for Objects and Operations in Oracle DML

Here is a list of Oracle objects and operations that Oracle GoldenGate supports for the capture and replication of DML operations.

Multitenant Container Database

Oracle GoldenGate captures from, and delivers to, a multitenant container database. See Configure a Multitenant Container Database.

Application Containers are not supported.

Tables, Views, and Materialized Views

The following DML operations are supported for regular tables, index-organized tables, clustered tables, and materialized views:

  • INSERT

  • UPDATE

  • DELETE

  • Associated transaction control operations

Starting from Oracle GoldenGate 23ai, the following features are available for tables:

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

  • 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, the source database compatibility must be set to 11.2.0.0.0 or higher.

  • Oracle GoldenGate supports Transparent Data Encryption (TDE).

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

    • SQLLDR with DIRECT=TRUE

  • Oracle GoldenGate fully supports capture from compressed objects for Extract.

  • Oracle GoldenGate supports XA and PDML distributed transactions.

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

These limitations apply to Extract.

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

Limitations of Support for Materialized Views

Materialized views are supported by Extract 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 Blockchain and Immutable Tables

Starting with Oracle Database 23ai, immutable and blockchain tables are supported. Here are the supported operations for the feature:
Support for One-Way Replication

  • Both blockchain table versions v1 (introduced in Oracle Database 19.10) and v2 (introduced in Oracle Database 23ai) as well as both immutable table versions v1 (introduced in Oracle Database 19.11) and v2 (introduced in Oracle Database 23ai) are supported for all Replicats except of coordinated Replicat.

  • Interoperability between types and versions:

    • Support for replication of blockchain and immutable tables of any version at the source to conventional tables at the target.

    • No Support for replication of conventional tables at the source to any blockchain and immutable tables of any version at the target.

  • DML, DDL, and Procedural Replication must be enabled. For Procedure Replication, the Blockchain table must be appended.

  • Delete operations of expired rows are not replicated. Expiration is managed by each database system separately.

Support for Bidirectional (Active-Active) Replication

  • Support for version v2 only.

  • Support for parallel Replicat in integrated mode and integrated Replicat only.

  • ACDR must be configured. ACDR for blockchain and immutable tables contains the following resolution methods:

    • Latest Timestamp

    • Earliest Timestamp

    Note:

    No other resolution methods (such as Column group or delta resolution) is supported. There is no Support for manual CDR.
  • A blockchain or immutable table can have at most one primary key or unique constraint and cannot have unique indexes.

    • Primary key or unique key constraint on blockchain or immutable tables for ACDR uses non-unique indexes.

    • ACDR only manages INSERT-INSERT conflicts because blockchain and immutable tables are INSERT-only tables. ACDR adds two hidden columns for timestamp (CDRTS$ROW) and visibility (ORABCTAB_INVISIBLE$).

      These hidden columns work as follows:

      ACDR will resolve a conflict and retain both copies of the row. It will mark a conflicting INSERT that would have normally been discarded or overwritten as logically invalid by setting the hidden column ORABCTAB_INVISIBLE$ to 1. As a result, there may be multiple rows with the same key value, but at most 1 row for each key will be valid and have ORABCTAB_INVISIBLE$ set to 0.

DML Auto Capture

Oracle GoldenGate supports the following DML operations with auto capture mode:

  • TABLEEXCLUSION parameter is supported.

  • TABLE parameter is supported.

  • Extract writes the table DML records delivered by the database for auto capture to trail file.

System Partitioning

System partitioning is an Oracle database feature that allows a table to be created with named partitions. A system partitioned table is not maintained by the database. Each DML must specify the partition where the row is to reside. Extract and all modes of Replicat support system partitioning. Each trail file record header pertaining to a system partitioned table includes the partition name. From Oracle GoldenGate 21c onward, a Partition Name Record (PNR) is generated for system partitioned tables, if it is included in the PARTITION parameter.

See PARTITION | PARTITIONEXCLUDE in the Parameters and Functions Reference for Oracle GoldenGate.

Sequences and Identity Columns

  • Identity columns are supported from Oracle database 18c onward and requires Extract, Parallel Replicat in Integrated mode, or Integrated Replicat.

  • Oracle GoldenGate supports the replication of sequence values and identity columns in a unidirectional 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 Extract.

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

Non-supported Objects and Operations in Oracle DML

The following are additional Oracle objects or operations that are not supported by Extract:

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

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

  • Database Replay

  • Tables created as EXTERNAL