Understanding What’s Supported

11 Understanding What’s Supported

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

Topics:

Details of Support for Oracle Data Types and Objects
This topic describes data types, objects and operations that are supported by Oracle GoldenGate.
Details of Support for Oracle Database Editions
This topic describes the Database Editions from the Oracle Database Product Family supported with the current Oracle GoldenGate release.
Details of Support for Objects and Operations in Oracle DML
This section outlines the Oracle objects and operations that Oracle GoldenGate supports for the capture and replication of DML operations.
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.

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

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

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
CHAR, VARCHAR2, LONG, NCHAR, and 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.
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

UDTs that contain following data types:

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

Object tables contain 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;
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
`(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.

Handling Special Data Types
It addresses special configuration requirements for different Oracle data types for Extract.
Non-Supported Oracle Data Types

Parent topic: Understanding What’s Supported

Handling Special Data Types

It addresses special configuration requirements for different Oracle data types for Extract.

Topics:.

Parent topic: Details of Support for Oracle Data Types and Objects

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

Parent topic: Handling Special Data Types

Oracle Spatial Objects

To replicate tables that contain one or more columns of SDO_GEORASTER object type from an Oracle source to an Oracle target, follow these instructions to configure Oracle GoldenGate to process them correctly.

Create a TABLE statement and a MAP statement for the georaster tables and also for the related raster data tables.
If the METADATA attribute of the SDO_GEORASTER data type in any of the values exceeds 1 MB, use the DBOPTIONS parameter with the XMLBUFSIZE option to increase the size of the memory buffer that stores the embedded SYS.XMLTYPE attribute of the SDO_GEORASTER data type. If the buffer is too small, Extract abends. See XMLBUFSIZE in Reference for Oracle GoldenGate.
To ensure the integrity of the target georaster tables and the spatial data, keep the trigger enabled on both source and target. Use the REPERROR option of the MAP parameter to handle the "ORA-01403 No data found" error that occurs as a result of keeping the trigger enabled on the target. It occurs when a row in the source georaster table is deleted, and the trigger cascades the delete to the raster data table. Both deletes are replicated. The replicated parent delete triggers the cascaded (child) delete on the target. When the replicated child delete arrives, it is redundant and generates the error. To use REPERROR, do the following:
- Use a REPERROR statement in each MAP statement that contains a raster data table.
- Use Oracle error 1403 as the SQL error.
- Use any of the response options as the error handling.

A sufficient way to handle the errors on raster tables caused by active triggers on target georaster tables is to use REPERROR with DISCARD to discard the cascaded delete that triggers them. The trigger on the target georaster table performs the delete to the raster data table, so the replicated one is not needed.

MAP geo.st_rdt, TARGET geo.st_rdt, REPERROR (-1403, DISCARD) ;

If you need to keep an audit trail of the error handling, use REPERROR with EXCEPTION to invoke exceptions handling. For this, you create an exceptions table and map the source raster data table twice:

once to the actual target raster data table (with REPERROR handling the 1403 errors).
again to the exceptions table, which captures the 1403 error and other relevant information by means of a COLMAP clause.

For more information about using an exceptions table, see Administering Oracle GoldenGate for Windows and UNIX.

For more information about REPERROR options, see Reference for Oracle GoldenGate.

Parent topic: Handling Special Data Types

TIMESTAMP

To replicate timestamp data, Oracle Database normalizes TIMESTAMP WITH LOCAL TIME ZONE data to the local time zone of the database that receives it, the target database in case of Oracle GoldenGate. To preserve the original time stamp of the data that it applies, Replicat sets its session to the time zone of the source database. You can override this default and supply a different time zone by using the SOURCETIMEZONE parameter in the Replicat parameter file. To force Replicat to set its session to the target time zone, use the PRESERVETARGETTIMEZONE parameter.

To prevent Oracle GoldenGate from abending on TIMESTAMP WITH TIME ZONE as TZR, use the Extract parameter TRANLOGOPTIONS with INCLUDEREGIONIDWITHOFFSET to replicate TIMESTAMP WITH TIMEZONE as TZR from an Oracle source that is at least version 10g to an earlier Oracle target, or from an Oracle source to a non-Oracle target. This option allows replicating to Oracle versions that do not support TIMESTAMP WITH TIME ZONE as TZR and to database systems that only support time zone as a UTC offset.

You can also use the SOURCETIMEZONE parameter to specify the source time zone for data that is captured by an Extract that is earlier than version 12.1.2. Those versions do not write the source time zone to the trail.

Parent topic: Handling Special Data Types

Large Objects (LOB)

The following are some configuration guidelines for Extract LOBs.

Store large objects out of row if possible.
Extract captures LOBs from the redo log. For UPDATE operations on a LOB document, only the changed portion of the LOB is logged. To force whole LOB documents to be written to the trail when only the changed portion is logged, use the TRANLOGOPTIONS parameter with the FETCHPARTIALLOB option in the Extract parameter file. When Extract receives partial LOB content from the logmining server, it fetches the full LOB image instead of processing the partial LOB. Use this option when replicating to a non-Oracle target or in other conditions where the full LOB image is required.

Parent topic: Handling Special Data Types

XML

The following are tools for working with XML within Oracle GoldenGate constraints.

Although Extract does not support the capture of changes made to an XML schema, you may be able to evolve the schemas and then resume replication of them without the need for a resynchronization, see Supporting Changes to XML Schemas.
Extract captures XML from the redo log. For UPDATE operations on an XML document, only the changed portion of the XML is logged if it is stored as OBJECT RELATIONAL or BINARY. To force whole XML documents to be written to the trail when only the changed portion is logged, use the TRANLOGOPTIONS parameter with the FETCHPARTIALXML option in the Extract parameter file. When Extract receives partial XML content from the logmining server, it fetches the full XML document instead of processing the partial XML. Use this option when replicating to a non-Oracle target or in other conditions where the full XML image is required.

Parent topic: Handling Special Data Types

User Defined Types

If Oracle Database is compatible with releases greater than or equal to 12.0.0.0.0, then Extract captures data from redo (no fetch), see Setting Flashback Query.

If replicating source data that contains user-defined types with the NCHAR, NVARCHAR2, or NCLOB attribute to an Oracle target, use the HAVEUDTWITHNCHAR parameter in the Replicat parameter file. When this type of data is encountered in the trail, HAVEUDTWITHNCHAR causes Replicat to connect to the Oracle target in AL32UTF8, which is required when a user-defined data type contains one of those attributes. HAVEUDTWITHNCHAR is required even if NLS_LANG is set to AL32UTF8 on the target. By default Replicat ignores NLS_LANG and connects to an Oracle Database in the native character set of the database. Replicat uses the OCIString object of the Oracle Call Interface, which does not support NCHAR, NVARCHAR2, or NCLOB attributes, so Replicat must bind them as CHAR. Connecting to the target in AL32UTF8 prevents data loss in this situation. HAVEUDTWITHNCHAR must appear before the USERID or USERIDALIAS parameter in the parameter file.

Parent topic: Handling Special Data Types

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
- LOB (CLOB, NCLOB, BLOB, BFILE)
- XMLType column
- 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
- SDO_TOPO_GEOMETRY
- SDO_GEORASTER

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

Parent topic: Details of Support for Oracle Data Types and Objects

Details of Support for Oracle Database Editions

This topic describes the Database Editions from the Oracle Database Product Family supported with the current Oracle GoldenGate release.

Oracle Database Express Edition (XE) is supported for delivery only and does not support any of the integrated features such as integrated Replicat or parallel Replicat in integrated mode.

Oracle Database Standard Edition 2 (SE2) is supported, with the following limitation:

Extract, integrated Replicat, and parallel Replicat in integrated mode are limited to a single thread.

Oracle Database Enterprise Edition (EE) has full Oracle GoldenGate functionality.

Oracle Database Personal Edition (PE) is supported for delivery only, and does not support any of the integrated features such as integrated or parallel Replicat in integrated mode.

Parent topic: Understanding What’s Supported

Details of Support for Objects and Operations in Oracle DML

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

Topics:

Parent topic: Understanding What’s Supported

Multitenant Container Database

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

Application Container are not supported.

Parent topic: Details of Support for Objects and Operations in Oracle DML

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.

Parent topic: Details of Support for Objects and Operations in Oracle DML

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.

Parent topic: Tables, Views, and Materialized Views

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

Parent topic: Tables, Views, and Materialized Views

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.

Parent topic: Tables, Views, and Materialized Views

Limitations of Support for Clustered Tables

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

Parent topic: Tables, Views, and Materialized Views

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 Reference for Oracle GoldenGate.

Parent topic: Details of Support for Objects and Operations in Oracle DML

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

Parent topic: Details of Support for Objects and Operations in Oracle DML

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.

Parent topic: Sequences and Identity Columns

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

Parent topic: Details of Support for Objects and Operations in Oracle DML

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.

Parent topic: Details of Support for Objects and Operations in Oracle DML

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.

Topics:

Parent topic: Understanding What’s Supported

Supported Objects and Operations in Oracle DDL

DDL capture support is integrated into the database logmining server. You must set the database parameter compatibility to 11.2.0.4.0 or higher. 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;

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 Extract for the following Oracle Database objects:
- functions
- library
- packages (specification and body)
- procedure
- synonyms
- types (specification and body)
- views
From Oracle GoldenGate 21c onward, DDLs that are greater than 4 MB in size will be provided replication support.
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 dictionary for use with NOUSERID and TRANLOGOPTIONS GETCTASDML. This means that Extract receives object metadata from the LogMiner dictionary without querying the dictionary objects. Oracle GoldenGate uses the dictionary automatically when the source database compatibility parameter is greater than or equal to 11.2.0.4.

When using dictionary and trail format in the Oracle GoldenGate release 12.2.x, Extract 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 Extract. 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.
Starting with Oracle database 21c, the following DDL is available to support blocking of DML/DDL changes that are not replicated by Oracle GoldenGate:
```
ALTER DATABASE [ENABLE | DISABLE] goldengate blocking mode;
```
When Oracle GoldenGate blocking mode is enabled, DMLs that use support_mode NONE in tables and execute unsupported Oracle PL/SQL statements will fail with the following error:
```
ORA-26981: "operation was unsupported during Oracle GoldenGate blocking mode"
```
For Oracle database 21c, the following features cause a table to have support_mode NONE in Oracle GoldenGate:
- BFILE as an attribute of ADT column, or typed table
- Table with no scalars
- OLAP AW$ table
- Sharded queue table
- Sorted Hash Cluster Table
- Primary key constraint on ADT attribute in relational table
- Primary key/unique key constraint on long raw/varchar (over 4000 bytes)
- V$DATABASE column, Goldengate_Blocking_Mode can be queried to determine the current blocking mode status.
For DDL auto capture mode:
- It is relevant only for DDL INCLUDE MAPPED because Extract captures DDLs based on TABLE and TABLEEXCLUDE parameter.
- Only table-related DDLs can be auto-captured.
- DDLs to enable auto capture at table level:
```
CREATE/ALTER TABLE … ENABLE LOGICAL REPLICATION ALLKEYS;
```
  or
```
CREATE/ALTER TABLE … ENABLE LOGICAL REPLICATION ALLOW NOVALIDATE KEYS;
```
See How to Capture Supplemental Logging for Oracle GoldenGate in Oracle Database Utilities guide.
The following operations are supported for partition related DDLs and partition maintenance operations
- Drop partition:
  
  If a partition is recreated with the same name, then it will get a new object number. The internal caches are cleared to minimize space consumption when a drop partition DDL is processed.
- Truncate partition:
  
  Partition name and object number stays the same. Base table object version stays the same.
- Rename partition:
  
  The partition object number stays the same but gets a new name. The base table's object version gets bumped. In memory name cache will get invalidated upon seeing this DDL and repopulated upon the next DML. The cache, which stores if a given partition object number is interesting or not will also need to be reevaluated as a the new partition name may switch from filtered to not filtered or vice versa.
- Exchange partition:
  
  Exchanges data in a partition with that in a table or vice versa. The obj# of the partition being exchanged does not change. Dataobj# does change but is not used by Extract. The partition itself still belongs to the same table.
- Merge partition:
  
  Merges one or more partitions into a new partition. The DDL creates the new partition and drops the partitions from which it was merged. In memory caches should be cleared to save space and the user should ensure proper filter rules for the newly created partition.
- Split partition:
  
  The partition being split keeps its original name and object number and new partition is created for the split data. The user must ensure partition filter rules are correct for the newly created partition.
- Coalesce partition:
  
  Reduces the number of partitions in a hash partitioned table. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. The remaining partitions keep their same name and object number. The internal caches should be cleared to minimize space consumption.
- Modify partition:
  
  Modifies default and real attributes of partitions, apart from adding or dropping of values for list partitions. All modifications leave the partitions name and object number intact.
- Move partition:
  
  Partition data is moved to a new tablespace. Partition name and number remain the same.
- Redef table:
  
  dbms_redefinition can be used to partition a table through the use of an interim table. The partitions are created on the interim table and after the finish_redef operation, the tables swap names. The partitions created on the interim table keep their names and object numbers when the tables are swapped. The Extract filter cache, needs to be reevaluated upon finish_redef as the partitions now belong to the base table. The user must ensure proper filter rules.
- Redef partition:
  
  When redefining a table, the partitions follow from the original table to the interim table. For example, consider the case where the original table has partitions, which live in the USER tablespace, and the interim table is created with no partitions and the table lives in the NEW tablespace. In this case, after the finish_redef operation, when the tables are swapped the partition still lives in the USER tablespace. Redef partition allows a partition to be moved to the interim table's NEW tablespace. The partition retains its name and object number.
- System generated partition names:
  
  When partitions are created automatically for hash partitions and operations such as split partition, the partition name is in the form of SYS_P sequence value. Similarly, subpartitions are of the form SYS_SUBP sequence value. It is recommended that the partition is renamed before excepting DML to conform to filter rules.

Parent topic: Details of Support for Objects and Operations in Oracle DDL

Non-supported Objects and Operations in Oracle DDL

Here's a list of non-supported objects and operations in Oracle DDL.

Topics:

Parent topic: Details of Support for Objects and Operations in Oracle DDL

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

Parent topic: Non-supported Objects and Operations in Oracle DDL

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.

Parent topic: Non-supported Objects and Operations in Oracle DDL