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

Besides the DBA_GOLDENGATE_SUPPORT_MODE at the source database you should check the DBA_GOLDENGATE_NOT_UNIQUE dictionary view at the target side. If there are tables without any uniqueness and unbounded data_types (BAD_COLUMN='Y'), the table records cannot be uniquely identified and cannot be used for logical replication.

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
`NUMBER, BINARY FLOAT, BINARY DOUBLE UROWID`	Special cases of: XML types UDTs Object tables Collections or nested tables
`DATE` and `TIMESTAMP`	Tables with restricted uniqueness
`(N)CHAR`, `(N) VARCHAR2 LONG`, `RAW`, `LONG RAW (N)CLOB`, `CLOB`, `BLOB`, `SECUREFILE`, `BASICFILE` and `BFILE`	X
`XML` columns, `XMLType`	X
`UDT`s	X
`ANYDATA`	X
Hierarchy-enabled tables	X
`RET` Types	X
`DICOM`	X
`SDO_TOPO_GEOMETRY`, `SDO_GEORASTER`	X
Identity columns	X
`SDO_RDF_TRIPLE_S`	X

Note:

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:

NUMBER, BINARY FLOAT, BINARY DOUBLE, and (logical) UROWID
DATE and TIMESTAMP
CHAR, VARCHAR2, LONG, NCHAR, and NVARCHAR2
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
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 or SDO_GEORASTER 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

Supported (Fetch from database)

SECUREFILE LOBs

Modified with DBMS_LOB.FRAGMENT_* procedures
NOLOGGING LOBs
Deduplicated LOBs with a source database release less than 12gR2

UDTs that contain following data types:

TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, TIMESTAMP WITH TIMEZONE with region ID
INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
BINARY FLOAT, BINARY DOUBLE
BFILE

Object tables contains the following attributes:

Nested table
SDO_TOP_GEOMETRY
SDO_GEORASTER

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.