Make sure that you are installing your product on a supported hardware and operating system configuration. For more information, see the certification document for your release on the Oracle Fusion Middleware Supported System Configurations page.
Oracle has tested and verified the performance of your product on all certified systems and environments; whenever new certifications occur, they are added to the proper certification document right away. New certifications can occur at any time, and for this reason the certification documents are kept outside of the documentation libraries and are available on Oracle Technology Network.
This section describes the operating system requirements of Oracle GoldenGate. These requirements fall into one of the following categories:
The amount of memory that is required for Oracle GoldenGate depends on the amount of data being processed, the number of Oracle GoldenGate processes running, the amount of RAM available to Oracle GoldenGate, and the amount of disk space that is available to Oracle GoldenGate for storing pages of RAM temporarily on disk when the operating system needs to free up RAM (typically when a low watermark is reached). This temporary storage of RAM to disk is commonly known as swapping or paging (herein referred to as swapping). Depending on the platform, the term swap space can be a swap partition, a swap file, a page file (Windows) or a shared memory segment (IBM i platforms).
Modern servers have sufficient RAM combined with sufficient swap space and memory management systems to run Oracle GoldenGate. However, increasing the amount of RAM available to Oracle GoldenGate may significantly improve its performance, as well as that of the system in general.
Typical Oracle GoldenGate installations provide RAM in multiples of gigabytes to prevent excessive swapping of RAM pages to disk. The more contention there is for RAM the more swap space that is used.
Excessive swapping to disk causes performance issues for the Extract process in particular, because it must store data from each open transaction until a commit record is received. If Oracle GoldenGate runs on the same system as the database, the amount of RAM that is available becomes critical to the performance of both.
RAM and swap usage are controlled by the operating system, not the Oracle GoldenGate processes. The Oracle GoldenGate cache manager takes advantage of the memory management functions of the operating system to ensure that the Oracle GoldenGate processes work in a sustained and efficient manner. In most cases, users need not change the default Oracle GoldenGate memory management configuration.
Assign free disk space according to the following instructions:
To determine the size of the Oracle GoldenGate download file, view the Size column before downloading your selected build from Oracle Software Delivery Cloud. The value shown is the size of the files in compressed form. The size of the expanded Oracle GoldenGate installation directory will be significantly larger on disk..
To install Oracle GoldenGate into a cluster environment, install the Oracle GoldenGate binaries and files as the Oracle user on a shared file system that is available to all cluster nodes. See Preparing to Install Oracle GoldenGate Within a Cluster for more information.
An additional 1 GB of disk space on any system that hosts Oracle GoldenGate trails, which are files that contain the working data. You may need more or less than this amount, because the space that is consumed by the trails depends on the volume of data that will be processed. .
(CDC Capture) When enabling a database for CDC Capture, CDC staging tables are created for each table enabled with supplemental logging and stored within the database. Data from DML operations are inserted into the CDC staging tables and are retained in the database until either the SQL Server CDC Cleanup job, or the Oracle GoldenGate Cleanup job, removes that data based on the retention period set, so plan accordingly to expect more disk usage from the database files.
By default, Oracle GoldenGate maintains data that it swaps to disk in the
dirtmp sub-directory of the Oracle GoldenGate installation directory. The cache manager assumes that all of the free space on the file system is available. This directory can fill up quickly if there is a large transaction volume with large transaction sizes. To prevent I/O contention and possible disk-related Extract failures, dedicate a disk to this directory. You can assign a name and size to this directory with the
CACHEDIRECTORY option of the
CACHEMGR parameter. The
CACHESIZE option of
CACHEMGR sets a soft limit for the amount of virtual memory (cache size) that is available for caching transaction data.
Configure networking according to the following instructions:
Configure the system to use TCP/IP services, including DNS. Oracle GoldenGate supports IPv4 and IPv6 and can operate in a system that supports one or both of these protocols.
Configure the network with the host names or IP addresses of all systems that will be hosting Oracle GoldenGate processes and to which Oracle GoldenGate will be connecting. Host names are easier to use.
Oracle GoldenGate requires some unreserved and unrestricted TCP/IP ports, the number of which depends on the number and types of processes in your configuration. See the Administering Oracle GoldenGate for details on how to configure the Manager process to handle the required ports.
Keep a record of the ports that you assigned to Oracle GoldenGate. You will specify them with parameters when configuring the Manager process.
Configure your firewalls to accept connections through the Oracle GoldenGate ports.
Assign operating system privileges according to the following instructions.
The Manager process can run as a Windows service, or it can run interactively as the current user. The Manager process requires:
Full control permissions over the files and folders within the Oracle GoldenGate directories.
Full control permissions over the trail files, if stored in a location other than the Oracle GoldenGate directory.
(Classic Extract) For a source capture installation of Oracle GoldenGate, Manager requires Read permissions on the SQL Server database transaction log files and transaction log backups.
(Classic Extract) If running a source capture in Archived Log Mode from a middle tier Windows server, Manager requires Read permissions to the network share where the transaction log backups are written, and Read permissions on the transaction log backups.
Membership in the server's local Administrators Group (on all nodes in a cluster).
The programs that capture and replicate data (Extract and Replicat) run under the Manager account and inherit the Manager's operating system level privileges.
Observe the following other program and settings information for Oracle GoldenGate for SQL Server:
To install capture on a remote Windows server, for a Classic Extract in archived log mode or for a remote CDC Extract, set the remote server's time and time zone to that of the database server.
Before installing Oracle GoldenGate on a Windows system, install and configure the Microsoft Visual C ++ 2010 SP1 Redistributable Package. Make certain it is the SP1 version of this package. Make certain it is the SP1 version of this package. This package installs runtime components of Visual C++ Libraries that are required for Oracle GoldenGate processes. to download this package, go to https://www.microsoft.com/en-us/download/details.aspx?id=13523.
(Classic Extract) To capture from a source SQL Server 2008/2008R2/2012/2014 Standard Edition database, the SQL Server Replication features must be installed and a Distributor must be configured.
SQL Server Client Tools Connectivity features must be installed on the server where Oracle GoldenGate is to be installed. This feature is normally installed by default when installing an instance of SQL Server, but for a Windows server that is to be used for a remote Replicat, a Classic Extract running off the database server in an Archived Log Mode, or a remote CDC Extract, the required client connectivity drivers can be obtained through the SQL Server installation media or from the following links:
Microsoft SQL Server 2008 SP4 Feature Pack:
Microsoft SQL Server 2008 R2 SP3 Feature Pack:
For SQL Server 2012, 2014, and 2016, install the Microsoft SQL Server 2012 Native Client, which is part of the Microsoft SQL Server 2012 Feature Pack:
Oracle GoldenGate fully supports virtual machine environments created with any virtualization software on any platform. When installing Oracle GoldenGate into a virtual machine environment, select a build that matches the database and the operating system of the virtual machine, not the host system.
To operate with Microsoft SQL Server databases, Oracle GoldenGate requires the following setup in the database instance.
To configure Oracle GoldenGate for SQL Server instance, the following must be true:
Classic Extract supports SQL Server Enterprise and Standard editions of versions 2008, 2008R2, 2012, and 2014.
CDC Extract supports SQL Server Enterprise editions of versions 2008, 2008R2, 2012, 2014, 2016, and Standard Edition of SQL Server 2016 with Service Pack 1 (or above).
(CDC Extract) – For SQL Server 2012, 2014, and 2016, Microsoft has identified and fixed an issue where some
UPDATE operations may be written incorrectly to a CDC staging table as an
INSERT followed by a
DELETE, rather than a
DELETE/INSERT pair. This may cause downstream replication issues, such as a primary key violation, therefore it is recommended to apply the Microsoft fix for this issue: https://support.microsoft.com/en-us/help/3030352
Oracle GoldenGate Delivery supports SQL Server Enterprise and Standard editions of versions 2008, 2008R2, 2012, 2014, and 2016.
The SQL Server server name (
@@SERVERNAME) should not be NULL.
(CDC Extract) For the source SQL Server instance, the SQL Server Agent must be running in order for the CDC Capture Job to load change data to the CDC tables, in order for Oracle GoldenGate to capture transactional data.
(CDC Extract) For SQL Server 2016, prior to enabling supplemental logging, ensure that you have patched the SQL Server instance based on the following bug fix from Microsoft: https://support.microsoft.com/en-us/help/3166120/fix-could-not-find-stored-procedure-sys.sp-cdc-parse-captured-column-list-error-in-sql-server-2016 If the instance is not correctly patched with the Microsoft fix, issuing
ADD TRANDATA against a table for the database may incorrectly report that supplemental logging succeeded, when in fact it may not have; therefore no records will be captured for that table.
(Classic Extract) To capture from a source SQL Server Standard Edition database, the SQL Server Replication features must be installed, a Distributor configured, and a distribution database created.
If capture of data for
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” and adjust as needed.
The database should be configured according to the following requirements:
Only user databases are supported for capture and delivery.
The database must be set to the compatibility level of the SQL Server instance version.
(CDC Extract) The source database can be configured with Transparent Data Encryption (TDE).
(CDC Extract) Source database can be set to any recovery model that supports Microsoft SQL Server’s Change Data Capture feature.
(Classic Extract) Source databases must be set to the Full recovery model.
(Classic Extract) After the source database is set to full recovery, a full database backup must be taken. This backup could be one that was already done, prior to the installation of Oracle GoldenGate, for a database that was previously using the full or bulk-logged recovery model.
(Classic Extract) If a source database was restored from a backup of a database from another instance, the database must have a new backup taken on the new instance, once restored.
If the source database was created by restoring a backup from a different instance to the current instance, the database owner SID must be synced with a SID that exists on the new instance. Alternatively
sp_changedbowner can be used to set the restored database to a current login for that instance.
(Classic Extract) The log chain on the source database must not be broken at any time while Oracle GoldenGate is installed and running. The log chain is broken if the log was backed up with the no_log or truncate_only options, or if the recovery model was set to Simple or Bulk_logged at any time after the initial full database backup was completed. For more information, see the Microsoft SQL Server documentation on Log Chains.
AlwaysOn - Extract supports capturing from the Primary database, or a read-only, Synchronous mode Secondary databases. Asynchronous mode Secondary databases are not supported for capture.
(CDC Extract) The source database must not be configured with SQL Server Transactional Replication. The CDC Extract cannot work in conjunction with a database enabled with SQL Server Transactional Replication.
Oracle GoldenGate does not support capture or delivery of system databases.
Oracle GoldenGate does not support capture from Contained databases.
Source database names should not exceed 121 characters due to a limitation in the SQL Server stored procedures that are used to enable supplemental logging.
If configuring the Oracle GoldenGate heartbeat functionality, the SQL Server database name should not exceed 107 characters.
Capture from SQL Server 2014 databases enabled with In-Memory OLTP (In-Memory Optimization) is not supported. When you add a Memory Optimized Data file group to your database, Oracle GoldenGate is not allowed to enable supplemental logging for any table in the database, and conversely, if supplemental logging has been enabled for any table in the database prior to the creation of a Memory Optimized Data file group, SQL Server will not allow a Memory Optimized Data file group to be created.
SQL Server 2014/2016 provides the option of Delayed Transaction Durability for transactions, however, transactions against a table configured with supplemental logging (
TRANDATA) by Oracle GoldenGate will always be Fully Durable.
(Classic Extract) The source database must not be configured with Transparent Data Encryption (TDE).
Capture from Asynchronous mode Secondary databases of an Always On Availability Group are not supported.
Tables to be included for capture and delivery must meet the following requirements and must only include data types listed in Supported SQL Server Data Types:
Oracle GoldenGate supports capture of transactional DML from user tables, and delivery to user tables and writeable views.
DDL operations are not supported.
Source tables for an Enterprise Edition database do not require a primary key.
(Classic Extract) Source tables for a Standard Edition database do require a primary key.
Oracle GoldenGate supports the maximum table names and column lengths that are permitted for tables that are tracked by SQL Server Change Data Capture for Enterprise Edition, and that are permitted for tables that are Articles of a Publication for SQL Server Transactional Replication.
The sum of all column lengths for a table to be captured from cannot exceed the length that is allowed by SQL Server for enabling Change Data Capture for that table. If the sum of all column lengths exceeds that allowed by the SQL Server procedure
ADD TRANDATA cannot be enabled for that table. The maximum allowable record length decreases as more columns are present, so there is an inverse relationship between maximum record length and the number of columns in the table.
Oracle GoldenGate uses ODBC and OLE DB to connect to a database:
ODBC: The Extract process uses ODBC to connect to a source SQL Server database to obtain metadata and perform other process queries. The Replicat process uses ODBC to connect to a target SQL Server database to obtain metadata, but can optionally use it for its delivery of transactions as well. ODBC must be properly configured. For more information, see Configuring an ODBC Connection.
OLE DB: By default, the Replicat process uses OLE DB to connect to a target SQL Server database to perform DML operations (thus there are always least two Replicat connections: ODBC for metadata and OLE DB for DML). For more information about Replicat connection options, see Configuring a Database Connection.
Limitations of Support
For SQL Server 2008/2008R2, use the SQL Server Native Client 10.0 driver. The older SQL Server driver (
SQLSRV32.DLL) does not support newer SQL Server data types.
For SQL Server 2012/2014/2016, use the SQL Server Native Client 11.0 driver.
Using the SQL Server Native Client 11 OLE DB driver to connect to a SQL Server 2012 or a SQL Server 2014 instance in OLEDB mode may lead to a memory leak issue (Microsoft article 2881661). Microsoft has provided a fix in SQL Server 2012 SP1 CU7 (Microsoft article 2894115) and SQL Server 2014 CU1 (Microsoft article 2931693). To avoid a possible memory leak, you may choose one of the following options:
For SQL Server 2012, upgrade the SQL Native Client 11.0 driver to the SP1 CU7 level.
For SQL Server 2014, a possible memory leak still may exist after installing SQL Server 2014 CU1 on a new Windows system. This does not occur when you upgrade from SQL Server 2012 SP1 CU7 to SQL Server 2014 CU1.
For SQL Server 2014/2016, only the SQL Server Native Client 11.0 driver is supported. The ODBC Driver 11/13 for SQL Server is not supported.
An AlwaysOn Availability Group Listener connection is not supported.
The following database users and privileges are required for Oracle GoldenGate to capture from, and apply to, a Microsoft SQL Server database.
A database user is required to issue the
ADD TRANDATA command to enable supplemental logging on the source database in the Oracle GoldenGate configuration. A database login command
(DBLOGIN) is issued from GGSCI before issuing
The database user that enables
TRANDATA must be a user that has
(CDC Extract) Extract can run with dbowner permissions, however when issuing
ADD HEARTBEATTABLE or
DELETE HEARTBEATTABLE or
INFO HEARTBEATTABLE, the user will need sysadmin rights.
The Oracle GoldenGate Extract processes capture data from a source SQL Server database for initial loads, from the transaction log and transaction log backups for a Classic Extract, and from the CDC tables for a CDC Extract. The Replicat process applies the data to a target SQL Server database. These processes can use either Windows Authentication or SQL Server Authentication to connect to a database.
To use Windows authentication, the Extract and Replicat processes inherit the login credentials of the Manager process, as identified by the Log On account specified in the Properties of the Manager service. This account must have the privileges listed here.
|Oracle GoldenGate Process||Manager privileges if using Local System account||Manager privileges if using local or domain account|
Account must be a member of the SQL Server fixed server role
CDC Extract (source system)
Account must be at least a member of the
Account must be at least a member of the
If using SQL Server authentication, you will need to specify the user and password with the
USERID parameter (including the PASSWORD option) in the Extract or Replicat parameter file or, alternatively, use the Oracle GoldenGate credential store and specify a user alias with the
To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed here.
|Classic Extract connecting via SQL Server Authentication||CDC Extract connecting via SQL Server Authentication||Replicat connecting via SQL Server Authentication|
Member of the SQL Server fixed server role
At least a member of the
At least a member of the
For more information about these parameters, see Administering Oracle GoldenGate for Windows and UNIX.
If using SQL Server authentication rather than Windows authentication, you will at times during installation and setup of Oracle GoldenGate need to log into the database by using the
DBLOGIN command in the GGSCI command interface. An example is when you add supplemental logging with the
ADD TRANDATA command.
Encrypting the login password is a recommended security measure. However, using a secure password in the standard
DBLOGIN command requires encrypting it through the previous use of the
ENCRYPT PASSWORD command. To avoid having to encrypt the password each time that you issue
DBLOGIN, and also to protect the user ID from exposure, you can create an Oracle GoldenGate credential store before performing any setup and configuration.
The credential store enables you to simply supply an alias for the login credential whenever you log in with
DBLOGIN. It also makes the work of specifying login credentials for the Extract and Replicat processes easier and more secure when configuring the parameter files. You can create basic entries in the credential store now and then use the management commands to expand it later as needed. For more information, see Administering Oracle GoldenGate.
The following data types are supported for capture and delivery, unless specifically noted otherwise:
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)
Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects larger than 4K. Full Oracle GoldenGate functionality can be used for objects that are 4K or smaller.
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. See Assigning Row Identifiers for further information.
Oracle GoldenGate supports multi-byte character data types and multi-byte data stored in character columns. Multi-byte data is only supported in a like-to-like, SQL Server configuration. Transformation, filtering, and other types of manipulation are not supported for multi-byte character data.
If capture of data for
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 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 only supported 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
FILESTREAM attribute is supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the
The support of 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.
Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp 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, you can 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 when the column was changed in the database and when 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 will be written to the trail for columns that contain computed columns. To replicate data for non-persisted computed columns, you can 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 when the column was changed in the data base and when 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, gets written to the trail or sent to the target, depending on the method that is being 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 that key, the non-persisted computed column will be ignored. This might affect data integrity if the remaining columns do not enforce uniqueness.
If a unique index is defined on any non-persisted computed columns, it will not be used.
If a unique key or index contains a non-persisted computed column and is the only unique identifier on 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, it is possible that Replicat could apply operations containing this identifier to the wrong target rows.
SQL_Variant data type is not supported for capture.
Sparse columns and Column Sets are not supported for capture, even when their base column data type is supported.
(CDC Extract) The
FILESTREAM feature is not supported.
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), and
NVARCHAR (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
ROWcompression. 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.
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.