This chapter contains the requirements for the system and database resources that support Oracle GoldenGate.
The following data types are supported for capture and delivery, unless specifically noted in the limitations that follow:
Binary Data Types
(binary, varbinary, varbinary (max))
(Classic Extract — varbinary (max)with
Character Data Types
(char, nchar, nvarchar, nvarchar (max), varchar, varchar (max))
Date and Time Data Types
(date, datetime2, datetime, datetimeoffset, smalldatetime, time)
Numeric Data Types
(bigint, bit, decimal, float, int, money, numeric, real, smallint, smallmoney, tinyint)
(image, ntext, text)
Other Data Types
(timestamp, uniqueidentifier, hierarchyid, geography, geometry, sql_variant (Delivery only), XML)
Note:The previous versions of Oracle GoldenGate 12.3 release didn’t support column data with
From this version of Oracle GoldenGate 12.3 release, the
FILESTREAM feature is supported for CDC Extract. This feature is already available for Classic Extract.
Note:For details on
FILESTREAMsupport, see New Features - March 2018 in Release Notes for Oracle GoldenGate
Oracle GoldenGate does not support filtering, column mapping, or manipulating large objects larger than 4KB. Full Oracle GoldenGate functionality can be used for objects of up to 4KB.
Oracle GoldenGate treats XML data as a large object (LOB), as does SQL Server when the XML does not fit into a row. SQL Server extended XML enhancements (such as lax validation,
DATETIME , union functionality) are not supported.
TIMESTAMP column or a non-materialized computed column cannot be part of a key. A table containing a
TIMESTAMP column must have a key, which can be a primary key or unique constraint, or a substitute key specified with a
KEYCOLS clause in the
MAP statements. For more information see Assigning Row Identifiers.
Oracle GoldenGate supports multi byte character data types and multi byte data stored in character columns. Multi byte data is supported only in a like-to-like, SQL Server configuration. Transformation, filtering, and other types of manipulation are not supported for multi byte character data.
If data capture the
TEXT, NTEXT, IMAGE, or
VARCHAR (MAX), NVARCHAR(MAX) and
VARBINARY (MAX) columns will exceed the SQL Server default size set for the
max text repl size option, extend the size. Use
sp_configure to view the current value of
max text repl size and adjust the option as needed.
Oracle GoldenGate supports UDT and UDA data of up to 2 GB in size. All UDTs except SQL_Variant are supported.
Common Language Runtime (CLR), including SQL Server built-in CLR data types (such as, geometry, geography, and hierarchy ID), are supported. CLR data types are supported only in a like-to-like SQL Server configuration. Transformation, filtering, and other types of manipulation are not supported for CLR data.
VARBINARY (MAX)column with the
FILESTREAMattribute is supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the
FILESTREAMfile. Its supported for both Classic and CDC Extracts.
Note:Previous versions of Oracle GoldenGate 12.3 does not support this feature for CDC Extracts.
The range and precision of floating-point numbers depends on the host machine. In general, 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.
Oracle GoldenGate supports time stamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a time stamp is converted from GMT to local time, these limits also apply to the resulting time stamp. Depending on the time zone, conversion may add or subtract hours, which can cause the time stamp to exceed the lower or upper supported limit.
Limitations on Computed Columns:
(Classic Extract) Oracle GoldenGate supports tables with non-persisted computed columns, but does not capture change data for these columns, because the database does not write it to the transaction log. To replicate data for non-persisted computed columns, use the
FETCHMODCOLS option of the
TABLE parameter to fetch the column data from the table. Keep in mind that there may be discrepancies caused by differences in data values between the time that the column was changed in the database and the time that Extract fetches the data for the transaction record that is being processed.
(CDC Extract) Computed columns, either persisted or non-persisted, are not supported by Microsoft’s Change Data Capture. Therefore, no data is written to the trail for columns that contain computed columns. To replicate data for non-persisted computed columns, use the
FETCHMODCOLS option of the
TABLE parameter to fetch the column data from the table. Keep in mind that there can be discrepancies caused by differences in data values between the time that the column was changed in the data base and the time that Extract fetches the data for the transaction record that is being processed.
Replicat does not apply DML to any computed column, even if the data for that column is in the trail, because the database does not permit DML on that type of column. Data from a source persisted computed column, or from a fetched non- persisted column, can be applied to a target column that is not a computed column.
In an initial load, all of the data is selected directly from the source tables, not the transaction log. Therefore, in an initial load, data values for all columns, including non-persisted computed columns, is written to the trail or sent to the target, depending on the method that is used. As when applying change data, however, Replicat does not apply initial load data to computed columns, because the database does not permit DML on that type of column.
Oracle GoldenGate does not permit a non-persisted computed column to be used in a
KEYCOLS clause in a
If a unique key includes a non-persisted computed column and Oracle GoldenGate must use the key, the non-persisted computed column is ignored. This may affect data integrity if the remaining columns do not enforce uniqueness.
If a unique index is defined on any non-persisted computed columns, it is not used.
If a unique key or index contains a non-persisted computed column and is the only unique identifier in a table, Oracle GoldenGate must use all of the columns as an identifier to find target rows. Because a non-persisted computed column cannot be used in this identifier, Replicat may apply operations containing this identifier to the wrong target rows.
SQL_Variant data type is not supported for capture.
Tables that contain unsupported data types may cause Extract to Abend. As a workaround, you must remove
TRANDATA from those tables and remove them from the Extract’s
TABLE statement, or use the Extract’s
TABLEEXCLUDE parameter for the table.
The following objects and operations are supported:
Oracle GoldenGate supports capture of transactional DML from user tables, and delivery to user tables and writeable views.
(Classic Extract) Capture from tables of a SQL Server Standard Edition database requires a primary key on the tables. Tables for an Enterprise Edition database do not require a primary key.
Oracle GoldenGate supports the capture and delivery of DML operations on tables that contain rows of up to 512 in length.
TEXT, NTEXT, IMAGE, VARBINARY, VARCHAR (
MAX) columns are supported in their full size.
Oracle GoldenGate supports the maximum sizes that are permitted for tables that are tracked by CDC (for Enterprise Edition) and Transactional Replication (for Standard Edition).
Oracle GoldenGate supports capture from tables enabled with
ROW compression. For partitioned tables that use compression, all partitions must be enabled with compression.
Oracle GoldenGate supports capture for partitioned tables if the table has the same physical layout across all partitions.
The following objects and operations are not supported:
For source databases, operations that are not supported by SQL Server Change Data Capture or Transactional Replication, such as
TRUNCATE statements. Refer to SQL Server Books Online for a complete list of the operations that are limited by enabling SQL Server Change Data Capture (for Enterprise Edition) and Transactional Replication (for Standard Edition).
Extraction or replication of DDL (data definition language) operations. To avoid any conflict due to DDL changes, it is recommended that you perform the following steps:
Pause or Stop application data to the table or tables to be changed.
(CDC Extract) Ensure the CDC Capture job processes all remaining transactions.
Ensure Extract process all the transactions prior to making any DDL changes. An Event Marker table may help to ensure full completion.
At source, execute
DELETE TRANDATA for the specific tables on which
ALTER TABLE (DDL changes) statement has to be performed.
ALTER TABLE statement to add or drop the column in or from the table.
(CDC Extract) For cases when all tables previously enabled with
TRANDATA removed, this will also disable CDC on the database and it will be necessary to reposition the Extract with
BEGIN NOW prior to restarting it.
TRANDATAfor the same table(s) at source.
Restart your application.
Capture from views. The underlying tables can be extracted and replicated.
Operations by the
TextCopy utility and
UPDATETEXT statements. These features perform operations that either are not logged by the database or are only partially logged, so they cannot be supported by the Extract process.
Partitioned tables that have more than one physical layout across partitions.
(Classic Extract) Oracle GoldenGate does not support non-native SQL Server transaction log backups, such as those offered by third-party vendors. However, if using the
TRANLOGOPTIONS parameter with the
ACTIVESECONDARYTRUNCATIONPOINT option, Extract does not need to read from any transaction log backups, so any log backup utility may be used. For more information, see Preparing the Database for Oracle GoldenGate — Classic Capture.
(CDC Extract) Due to a limitation with SQL Server's Change Data Capture, column level collations that are different from the database collation, may cause incorrect data to be written to the CDC tables for character data and Extract will capture them as they are written to the CDC tables. It is recommended that you use
NTEXT data type for columns containing non-ASCII data or use the same collation for table columns as the database. For more information see, About Change Data Capture (SQL Server).
(CDC Extract) Due to a limitation with SQL Server's Change Data Capture,
NOOPUPDATES are not captured by the SQL Server CDC agent so there are no records for Extract to capture for no-op update operations.