Oracle: Supported Data Types, Objects, and Operations for DDL and DML
Find out the supported data types, objects, and operations for Oracle GoldenGate on Oracle Database.
Details of Support for Oracle Data Types and Objects
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:
NOUSERID
parameter may be used.
-
NUMBER
,BINARY FLOAT
,BINARY DOUBLE
, and (logical)UROWID
-
DATE
andTIMESTAMP
-
VECTOR
(a new data type used by AI) -
CHAR
,VARCHAR2
,LONG
,NCHAR
,NVARCHAR2
,BOOLEAN
-
RAW
,LONG RAW
,CLOB
,NCLOB
,BLOB
,SECUREFILE
,BASICFILE
, andBFILE
(LOB size limited to 4GB) -
XML
columns stored asCLOB
,Binary
and Object-Relational (OR) -
XMLType
columns andXMLType
tables stored asXML CLOB
,XML
Object Relational, andXML Binary
-
Native
JSON
datatype identified by theDTYJSON
code. -
Oracle GoldenGate 23ai supports piece-wise
JSON
updates called JSON diff. -
UDTs
(user-defined or abstract data types) onBYTE
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
, orST_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
-
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
asTZR
(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 longVARCHAR
)-
32K long columns cannot be used as:
-
Row identifiers
-
Part of a key or unique index
-
In a
KEYCOLS
clause of theTABLE
orMAP
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 extendedVARCHAR
caused the abend by checkingALL_INDEXES
orALL_IND_COLUMNS
for a unique index orALL_CONS_COLUMNS
orALL_CONSTRAINTS
for a unique constraint. Once you determine that an extendedVARCHAR
, 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 inTABLE
orMAP
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 theXML
representation of an object to a character column by means of aCOLMAP
clause in aTABLE
orMAP
statement.Oracle recommends the
AL32UTF8
character set as the database character set when working withXML
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 ifTRANLOGOPTIONS GETCTASDML
parameter is set. ForXMLType
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 theIS 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 forVARCHAR2
,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 likestr
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 |
---|---|
|
Special cases of:
|
|
Not supported |
|
Not supported |
Native |
|
|
Not supported |
|
Not supported |
Hierarchy-enabled tables |
Not supported |
|
Not supported |
|
Not supported |
|
Not supported |
Identity columns |
Not supported |
|
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
Here are the special configuration requirements for different Oracle data types for Extract.
Support for Lock-free Reservation
With Oracle database 23ai, Oracle GoldenGate supports lock-free reservation. Replication is available for the following combinations between source and target tables:
-
Source tables using lockfree reservation columns to the target tables with implicit delta conflict resolution for replication from lock-free
reservable
columns on the source to thereservable
columns on the target. -
Source tables without lock-free
reservable
columns to the target tables with lock-free reservable columns with the following restriction:-
Primary Key Updates on the source tables without lock-free
reservable
columns are NOT supported if the target table contains lock-freereservable
columns.
-
-
Source tables and target tables using lock-free
reservable
columns with implicit delta conflict resolution for replication from lock-freereservable
columns.
Note:
-
ACDR is not supported.
-
Any manual CDR that results to an
UPDATE
operation at the target is not supported. For example,UPDATE
conflict,INSERTROWEXISTS
is not supported.
Support for Data Guard PDB (DGPDB)
Oracle Database 21c introduced the Data Guard PDB (DGPDB) feature, to expand the Data Guard functionality to individual pluggable database (PDB) level. Individual PDBs from a multitenant source database (source CDB) can be configured for Data Guard protection in a target multitenant database (target CDB).
Starting with Oracle GoldenGate 23ai for Oracle Database 23ai, a PDB can be the source for DGPDB and Oracle GoldenGate per-PDB Extract, simultaneously. When the corresponding DGPDB goes through a role transition to become the new primary, Oracle GoldenGate per-PDB Extract seamlessly migrates to the new source PDB.
-
Detects the transition: Oracle GoldenGate per-PDB Extract internally identifies its change from the old PDB to the new PDB.
-
Redirects Service: The auto-restart capability of Oracle GoldenGate Extract automatically connects to the new source PDB in the target CDB.
-
Transitions to New Primary: Oracle GoldenGate finishes processing the old archive (redo) log files available from the old source PDB, establishes a sync point, and starts processing the new archive (redo) log files from the new source PDB.
JSON
-
Replication support for full JSON document from Oracle database 21c and higher.
-
Differential changes of the JSON object are replicated from Oracle database 23ai and higher.
To know about enabling replication of differential changes for JSON
objects, see ADD TRANDATA
. Also see, CREATE TABLE
logical_replication_clause
with the PARTIAL JSON
option.
Considerations
-
Compatible with when both source and target database versions are Oracle database 23ai.
-
Table must have a Primary Key.
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.
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.
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 theTRANLOGOPTIONS
parameter with theFETCHPARTIALLOB
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.
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 asOBJECT RELATIONAL
orBINARY
. To force whole XML documents to be written to the trail when only the changed portion is logged, use theTRANLOGOPTIONS
parameter with theFETCHPARTIALXML
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.
Supporting Changes to XML Schemas
Learn about supporting changes to an XML schema. Extract does not support the capture of changes made to an XML schema.
Supporting RegisterSchema
RegisterSchema
can be handled by registering the schema definition
on both source and target databases before any table is created that references the
XML schema.
Supporting DeleteSchema
Issue DeleteSchema
on the source database first.
After Replicat is caught up with the changes made to the source database, issue the
DeleteSchema
call on the target database.
Supporting CopyEvolve
The CopyEvolve procedure evolves, or changes, a schema and can modify tables by adding or removing columns.
The CopyEvolve procedure can also be used to change whether or not XML documents are
valid. Handling CopyEvolve
requires more coordination.
Use the following procedure if you are issuing
CopyEvolve
on the source database.
- Quiesce changes to dependent tables on the source database.
- Execute the
CopyEvolve
on the primary or source database. - Wait for Replicat to finish applying all of the data from those tables to the target database.
- Stop Replicat.
- Apply the
CopyEvolve
on the target database. - Restart Replicat.
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.
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.
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.
-
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.
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:
-
4K column in tables with row size less than 4 MB
-
Blockchain and Immutable Tables
See Limitations of Support for Blockchain and Immutable Tables for details.
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 aCLOB
, 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 aBLOB
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
withDIRECT=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 theFLASHBACK 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 theFOR 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
- 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 conflict and retain both copies of the row. It will mark conflicting INSERT that would have normally been discarded or overwritten as invisible (set
ORABCTAB_INVISIBLE$ = 1
). As a result, there may be multiple rows with the same key value but at most 1 row for each key will haveORABCTAB$_INVISIBLE = 0
.
-
-
DML Auto Capture
-
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 basedREFs
(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
Learn about the Oracle objects and operations that Oracle GoldenGate supports for the capture and replication of DDL operations.
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.
-
From Oracle GoldenGate 23ai onwards, SQL domains are supported.
-
Oracle GoldenGate is capable of managing tables with 4000 columns if the row size is less than 4MB.
-
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
andTRANLOGOPTIONS 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 theCOLMAP
clause of theMAP
parameter.If
SOURCEDEFS
orTARGETDEFS
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 theFORMAT RELEASE
option of theDEFSFILE
parameter when the definition file is generated inDEFGEN
. -
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
andALLCATALOG
option ofDDL 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 usesupport_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 havesupport_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 onTABLE
andTABLEEXCLUDE
parameter. -
Only table-related DDLs can be auto-captured.
-
DDLs to enable auto capture at table level:
CREATE/ALTER TABLE … ENABLE LOGICAL REPLICATION ALLKEYS;
orCREATE/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 thefinish_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 uponfinish_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 theNEW
tablespace. In this case, after thefinish_redef
operation, when the tables are swapped the partition still lives in theUSER
tablespace. Redef partition allows a partition to be moved to the interim table'sNEW
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 formSYS_SUBP sequence value
. It is recommended that the partition is renamed before excepting DML to conform to filter rules.
-
Non-supported Objects and Operations in Oracle DDL
Here's a list of non-supported 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
Other Non-supported DDL
Oracle GoldenGate does not support the following:
-
DDL on nested tables.
-
DDL on identity columns.
-
ALTER DATABASE
andALTER SYSTEM
(these are not considered to be DDL) Using dictionary, you can replicateALTER DATABASE DEFAULT EDITION
andALTER PLUGGABLE DATABASE DEFAULT EDITION
. All otherALTER [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 withFLASHBACK 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.