Details of Support for Oracle Data Types and Objects

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 imply 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).

From Oracle GoldenGate 21c onward, DML on tables that are not supported will be automatically skipped when DBA_GOLDENGATE_SUPPORT_MODE.SUPPORT_MODE= NONE is set. However, DDLs for these objects are still captured based on the DDL INCLUDE/EXCLUDE settings. See Details of Support for Objects and Operations in Oracle DDL for DDL support.

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 Extract, with NOUSERID you 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. 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.

Detailed support information for Oracle data types, objects, and operations starts with the following:

Extract Redo Support:

The following data types allow capturing directly from the redo logs and do not require any fetching. If used in a downstream mining configuration, the NOUSERID parameter may be used.

  • NUMBER, BINARY FLOAT, BINARY DOUBLE, and (logical) UROWID

  • DATE and TIMESTAMP

  • VECTOR (a new data type used by AI)

  • CHAR, VARCHAR2, LONG, NCHAR, NVARCHAR2, BOOLEAN

  • RAW, LONG RAW, CLOB, NCLOB, BLOB, SECUREFILE, BASICFILE, and BFILE (LOB size limited to 4GB)

  • 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

  • Native JSON datatype identified by the DTYJSON code.

  • Oracle GoldenGate 26ai and higher support piece-wise JSON updates called JSON diff.

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

  • ANYDATA data type with source database compatibility 12.0.0.0.0 or higher

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

  • REF types with source database compatibility 12.2.0.0.0 or higher

  • DICOM with source database compatibility 12.0.0.0.0 or higher

  • SDO_TOPO_GEOMETRY, SDO_GEORASTER, or ST_GEOMETRY with source database compatibility 12.2.0.0.0 or higher and enabled procedural replication

  • Identity columns with source database compatibility 18.1.0.0.0 or higher

  • SDO_RDF_TRIPLE_S with source database compatibility 19.1.0.0.0 or higher

Data Types Fetched from the Database

Data types listed here are not readable in the redo logs and must be fetched by the Extract process during it's processing. The method for fetching these records is controlled by the use of the FETCHOPTIONS parameter.

It is recommended that the database that is generating the redo data is the same database that Oracle GoldenGate uses to fetch the data. However, if this is not possible, an Active Data Guard Standby database open for read-only can also be used as the fetch database.

SECUREFILE LOBs

  • Modified with DBMS_LOB.FRAGMENT_* procedures

  • NOLOGGING LOBs

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

Object tables contain the following attributes:

  • Nested table

  • SDO_TOPO_GEOMETRY

  • SDO_GEORASTER

Fetch does not support ANYDATA columns in a UDT.

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

      • Part of a key or unique index

      • In a KEYCOLS clause of the TABLE or MAP parameter

      • Resolution columns in a CDR (conflict detection and resolution)

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

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

  • 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, 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 TRANLOGOPTIONS GETCTASDML parameter is set. For XMLType tables, the row object IDs must match between source and target.

  • For JSON datatype, DTYJSON is stored in the binary JSON format for query and space efficiency as well as transportability between platforms. A column with JSON data as text is declared using any of the text data types (VARCHAR2, CLOB) and the IS JSON constraint. JSON datatype is supported by Oracle GoldenGate Extract, and Replicat processes along with XStream Out, XStream In processes. JSON support limits the inline text JSON to 4K to prevent Replicat from abending.

    By default Extract writes native JSON columns in text format but using binary_json_format parameter forces to write in native format. So, this paramater must not be set for VARCHAR2, NVARVAR2, CLOB, NCLOB. The parameter is not set by default. If you are only replicating from Oracle to Oracle you can set the parameter and gain a bit of performance. Also the Column manipulation functions like str are supported only for text JSON.

  • It is recommended that de-duplication is removed for LOB data types on the target database. If DEDUPLICATION is left enabled, it causes severe performance impact on the apply side.

SQLEXEC Limitations

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:

Datatypes Supported By SQLEXEC Support Limitations

NUMBER, BINARY FLOAT, BINARY DOUBLE UROWID

Special cases of:

  • XML types

  • UDTs

  • Object tables

  • Collections or nested tables

Supports Infinity/-Infinity special values.

For more information on data types by databases that support special values, refer to Special Values Supported by Database Data Types.

Supports NaN special value except the NUMBER data type.

(N)CHAR, (N) VARCHAR2 LONG, RAW, LONG RAW (N)CLOB, CLOB, BLOB, SECUREFILE, BASICFILE and BFILE

Not supported

XML columns, XMLType

Not supported

Native JSON datatype

VARCHAR2, NVARCHAR2, CLOB, NCLOB not supported with the Extract parameter binary_json_format.

UDT

Not supported

ANYDATA

Not supported

Hierarchy-enabled tables

Not supported

RET Types

Not supported

DICOM

Not supported

SDO_TOPO_GEOMETRY, SDO_GEORASTER

Not supported

Identity columns

Not supported

SDO_RDF_TRIPLE_S

Not supported

Note:

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

Note:

Any datatype not listed in the table is fully supported by SQLEXEC with the same limitations as the regular product.