1 Understanding What’s Supported

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


Details of Support for Oracle Data Types and Objects

This topic describes data types, objects and operations that are supported by Oracle GoldenGate.

Within the database, you can use the Dictionary view DBA_GOLDENGATE_SUPPORT_MODE to get information about supported objects. There are different types for replication support:
  • Support by Capturing from Redo

  • Procedural Replication Support

Most data types are supported (SUPPORT_MODE=FULL), which implies that Oracle GoldenGate captures the changes out of the redo. In some unique cases, the information cannot be captured, but the information can be fetched with a connection to the database (SUPPORT_MODE=ID KEY). Tables supported with ID KEY require a connection to the source database or an ADG Standby database for fetching to support those tables. If using downstream Integrated Extract, with NOUSERID a customer must specify a FETCHUSERID or FETCHUSERIDALIAS connection.

Other changes can be replicated with Procedural Replication (SUPPORT_MODE=PLSQL) that requires additional parameter setting of Extract. See About Procedural Replication for details. In the unlikely case that there is no native support, no support by fetching and no procedural replication support, there is no Oracle GoldenGate support.

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.

There might be a few cases where replication support exists, but there are limitations of processing such as in case of using SQLEXEC. The following table lists these limitations:

Support Datatypes No Support


Special cases of:
  • XML types

  • UDTs

  • Object tables

  • Collections or nested tables


Tables with restricted uniqueness



XML columns, XMLType






Hierarchy-enabled tables


RET Types






Identity columns





SECUREFILE LOBs updated using DBMS_LOG.FRAGMENT or SECUREFILE LOBs that are set to NOLOGGING are fetched instead of read from the redo.

Supported Capture from Redo:





  • XML columns stored as CLOB, Binary and Object-Relational (OR)

  • XMLType columns and XMLType tables stored as XML CLOB, XML Object Relational, and XML Binary

  • UDTs (user-defined or abstract data types) on BYTE semantics with source database compatibility or higher

  • ANYDATA data type with source database compatibility or higher

  • Hierarchy-enabled tables are managed by the Oracle XML database repository with source database compatibility or higher and enabled procedural replication

  • REF types with source database compatibility or higher

  • DICOM with source database compatibility or higher

  • SDO _TOPO_GEOMETRY or SDO_GEORASTER with source database compatibility or higher and enabled procedural replication

  • Identity columns with source database compatibility or higher

  • SDO_RDF_TRIPLE_S with source database compatibility or higher

Supported (Fetch from database)


  • Modified with DBMS_LOB.FRAGMENT_* procedures


  • Deduplicated LOBs with a source database release less than 12gR2

UDTs that contain following data types:





Object tables contains the following attributes:
  • Nested table



Additional Considerations

  • NUMBER can be up to the maximum size permitted by Oracle. 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.

  • Non-logical UROWID columns will be identified by Extract. A warning message is generated in the report file. The column information is not part of the trail record. All other supported datatypes of the record are part of the trail record and are replicated.

  • TIMESTAMP WITH TIME ZONE as TZR (region ID) for initial loads, SQLEXEC or operations where the column can only be fetched from the database. In those cases, the region ID is converted to a time offset by the source database when the column is selected. Replicat applies the timestamp as date and time data into the target database with a time offset value.

  • VARCHAR expansion from 4K to 32K (extended or long VARCHAR)
    • 32K long columns cannot be used as row identifiers:
      • Columns as part of a key or unique index

      • Columns in a KEYCOLS clause of the TABLE or MAP parameter.

    • 32K long columns as resolution columns in a CDR (conflict resolution and detection)

    • 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 or ALL_IND_COLUMNS for a unique index or ALL_CONS_COLUMNS or ALL_CONSTRAINTS for a unique constraint. Once you determine that an extended VARCHAR, you can temporarily drop the index or disable the constraint:
      • Unique Index: DROP INDEX index_name;

      • Unique Constraint: ALTER TABLE table_name MODIFY CONSTRAINT constraint_name DISABLE;

  • Oracle GoldenGate does not support the filtering, column mapping, or manipulation of objects larger than 4K.

  • BFILE column are replicating the locator. The file on the server file system outside of the database and is not replicated.

  • 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 cannot be used.

  • The character sets between the two databases must be one of the following:

    • Identical on the source and on the target

    • Equivalent, which is not the same character set but containing the same set of characters

    • Target is a superset of the source

    Multi-byte data can be used in any semantics: bytes or characters.

  • The structure of the UDTs and Abstract Data Types (ADTs) itself must be the same on both the source and target. UDTs can have different source and target schemas. 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.

    To fully support object tables created using the CREATE TABLE as SELECT (CTAS) statement, Integrated Extract must be configured to capture DML from the CTAS statement. Oracle object table can be mapped to a non-Oracle object table in a supported target database.

  • XML column type cannot be used for filtering and manipulation. 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. With DDL replication enabled, Oracle GoldenGate replicates the CTAS statement and allows it to select the data from the underlying target tables. OIDs are preserved if TRANSLOGOPTIONS GETCTASDML parameter is set. For XMLType tables, the row object IDs must match between source and target.

Non-Supported Oracle Data Types

Oracle GoldenGate does not support the following data types.

  • Time offset values outside the range of +12:00 and -12:00..Oracle GoldenGate supports time offset values between +12:00 and -12:00.

  • Tables that only contain a single column and that column one of the following:

    • UDT


    • XMLType column. Oracle GoldenGate treats XMLType data as an LOB.

    • VARCHAR2 (MAX) where the data is greater than 32KB

  • Tables with LOB, UDT, XML, or XMLType column without one of the following:

    • Primary Key

    • Scalar columns with a unique constraint or unique index

    Table where the combination of all scalar columns do not guarantee uniqueness are unsupported.

  • Tables with the following XML characteristics:
    • Tables with a primary key constraint made up of XML attributes

    • XMLType tables with a primary key based on an object identifier (PKOID).

    • XMLType tables, where the row object identifiers (OID) do not match between source and target

    • XMLType tables created by an empty CTAS statement.

    • XML schema-based XMLType tables and columns where changes are made to the XML schema (XML schemas must be registered on source and target databases with the dbms_xml package).

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

    • SQL*Loader direct-path insert for XML-Binary and XML-OR.

  • Tables with following UDT characteristics:

    • UDTs that contain CFILE or OPAQUE (except of XMLType)

    • UDTs with CHAR and VARCHAR attributes that contain binary or unprintable characters

    • UDTs using the RMTTASK parameter

  • UDTs and nested tables with following condition:

    • Nested table UDTs with CHAR, NVARCHAR2 or NCLOB attributes.

    • Nested tables with CLOB, BLOB, extended (32k) VARCHAR2 or RAW attributes in UDTs.

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

  • When data in a nested table is updated, the row that contains the nested table must be updated at the same time. Otherwise there is no support.

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

  • Object table contains the following attributes:

    • Nested table



See additional exclusions in Details of Support for Oracle Data Types and Objects.

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


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.




  • Associated transaction control operations


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


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

  • 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 (Not supported for RAC in classic capture mode)


  • Oracle GoldenGate fully supports capture from compressed objects when Extract is in integrated capture mode. 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 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 Clustered Tables

Indexed clusters are supported by Extract while hash clusters are not supported.

Sequences and Identity Columns

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

  • Oracle GoldenGate supports the replication of sequence values and identity columns 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:

  • 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

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 If Extract will run in integrated mode against a version 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 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 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;


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

The following additional statements apply to Extract with respect to DDL support.

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

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

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




    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
  "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)
  "SPATIAL_CSW_ADMIN", // Spatial Catalog Services for Web
  "SPATIAL_WFS_ADMIN", // Spatial Web Feature Service
  "SYSMAN", // Adminstrator OEM
  "TSMSYS", // Transparent Session Migration
  "WKPROXY", // Ultrasearch
  "WKSYS", // Ultrasearch
  "WMSYS", // Workspace Manager
  "XDB", // XML DB
  "XTISYS", // Time Index

Special schemas:

  "DSSYS", // Dynamic Services Secured Web Service
  "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
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and 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) Using 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.

  • Some DDL will generate system generated object names. The names of system generated objects may not always be the same between two different databases. So, DDL operations on objects with system generated names should only be done if the name is exactly the same on the target.

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 following white papers for Classic and Microservices architectures:

  1. Oracle GoldenGate Classic Architecture with Oracle Real Application Clusters Configuration Best Practices

  2. Oracle GoldenGate Microservices Architecture with Oracle Real Application Clusters Configuration Best Practices

General Requirements in a Cluster

  1. Configure the Oracle Grid Infrastructure Bundled Agent (XAG) to automatically manage the GoldenGate processes on the cluster nodes. See Configuring Oracle GoldenGate with Oracle Grid Infrastructure Bundled Agents (XAG) to know more.
    Using the XAG makes sure that the required cluster file system is mounted before the Oracle 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