This chapter includes the following sections:
To find out which Oracle GoldenGate builds are available for a specific combination of database version and operating system, log onto My Oracle Support at
http://support.oracle.com and select the Certifications tab. For assistance, click Tips for Finding Certifications.
You will need to provide an e-mail address and password to enter this site.
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.
For more information about evaluating Oracle GoldenGate memory requirements, see the
CACHEMGR parameter in Reference for Oracle GoldenGate for Windows and UNIX.
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. For more information, see Section 2.2, "Downloading Oracle GoldenGate."
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 Section 2.3, "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. See the guidelines for sizing trails in Administering Oracle GoldenGate for Windows and UNIX.
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. See Reference for Oracle GoldenGate for Windows and UNIX for the default values of these options and detailed explanations, in case system adjustments need to be made.
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 Windows and UNIX 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 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 over the files and folders within the Oracle GoldenGate directories.
Full control over the trail files, if stored in a location other than the Oracle GoldenGate directory.
Membership in the 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 those Administrator rights.
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, and make certain to get the correct bit version for your server. This package installs runtime components of Visual C++ Libraries. For more information, and to download this package, go to
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 an instance, the following must be true:
To capture from a SQL Server database, the instance must be the Enterprise Edition of SQL Server.
Change Data Capture (CDC) must be enabled for Oracle GoldenGate and will be enabled by Oracle GoldenGate by means of the
ADD TRANDATA command. See Section 4.4, "Enabling Supplemental Logging" for more information.
The database should be configured according to the following recommendations and limitations:
A SQL Server source database must be set to use the full recovery model.
Oracle GoldenGate does not support system databases or capture from Contained databases.
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. If you need to make a backup, see Section 4.6, "Making a Full Database Backup Before You Start Oracle GoldenGate."
The log chain on the source database must not be broken between the time of the last full database backup and the time when Oracle GoldenGate is installed. (The log chain is broken if the log was backed up with the
truncate_only options, or if the recovery model was set to
bulk logged at any time after the initial full database backup was completed.) In addition, the log chain must not be broken after Oracle GoldenGate is installed. For more information, see the Microsoft SQL Server documentation on Log Chains.
SQL Server 2014 introduces In-Memory OLTP (In-Memory Optimization) that allows the use of in-memory tables (memory optimized). 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 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.
Oracle GoldenGate uses ODBC and/or OLE DB to connect to a database:
ODBC: The Extract process uses ODBC to connect to a source SQL Server database. The Replicat process uses ODBC to connect to a target SQL Server database to obtain metadata, but can optionally use it for its delivery transactions as well. ODBC must be properly configured. For more information, see Section 3.1.3, "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 data). For more information about Replicat connection options, see Section 3.1, "Configuring a Database Connection."
Use at least the SQL Server Native Client 10.0 driver. The older SQL Server driver (
SQLSRV32.DLL) does not support newer SQL Server data types.
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.
Use ODBC mode
For SQL Server 2014, only the SQL Server Native Client 11.0 driver is supported. The ODBC Driver 11 for SQL Server 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 objects in the Oracle GoldenGate configuration. A database login command (
DBLOGIN) is issued from GGSCI before issuing
ADD TRANDATA. This user must be a member of the SQL Server System Administrators (
ADD TRANDATA is performed for tables that are being added to the Oracle GoldenGate capture configuration, either during the initial deployment or when tables are added to the configuration later. For more information, see Section 4.4, "Enabling Supplemental Logging."
The Oracle GoldenGate Extract process captures data from a source SQL Server database, and the Replicat process applies it 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 in Table 1-1 on the source and target systems.
To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed in Table 1-2. 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
USERIDALIAS parameter. For more information about these parameters, see Administering Oracle GoldenGate for Windows and UNIX.
|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
Account must be at least a member of the
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 for Windows and UNIX.
Oracle GoldenGate supports most SQL Server data types except those listed under Section 1.5, "Non-Supported SQL Server Data Types."
Oracle GoldenGate does not support capture from tables that have sparse columns or column sets.
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, a unique constraint, or a substitute key specified with a
KEYCOLS clause in the
MAP statement. See Section 3.2.2, "Assigning Row Identifiers."
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 configuration. Transformation, filtering, and other types of manipulation are not supported for multi-byte character data.
If data for
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.
IDENTITY columns are supported as follows:
Fully in a uni-directional configuration when the increment values are configured properly.
Fully, or with limitations, in a bi-directional configuration, depending on how the Replicat connection is defined. Full support, including support for
IDENTITY ranges, requires OLE DB to be used with
NOT FOR REPLICATION enabled for the
IDENTITY columns and with Replicat operating as the replication user.
For more information, see Chapter 3, "Preparing the System for Oracle GoldenGate."
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 configuration. Transformation, filtering, and other types of manipulation are not supported for CLR data.
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
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.
VARBINARY (MAX) column with the
FILESTREAM attribute (introduced in SQL Server 2008) is supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the
SQL_Variant is the only non-supported data type.
The following objects and operations are supported:
Oracle GoldenGate supports the extraction and replication of DML operations on tables that contain rows of up to 512 KB in length.
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.
Oracle GoldenGate supports SQL Server tables that use data compression. This includes row compressed format in both
Oracle GoldenGate supports delivery to writable views. You can map data to a view in a Replicat
Oracle GoldenGate supports partitioned tables if the table has the same physical layout across all partitions.
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.
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.
The following objects and operations are not supported by SQL Server:
Operations that are not supported by SQL Server Change Data Capture. SQL Server tables that are in the Extract configuration are marked for SQL Server Change Data Capture when you enable supplemental logging (see "Enabling Supplemental Logging"). Refer to SQL Server Books Online for a complete list of the operations that are limited by enabling SQL Server Change Data Capture.
Extraction or replication of DDL (data definition language) operations.
Capture and delivery of 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.
TDE (Transparent Data Encryption).
Partitioned tables that have more than one physical layout across partitions.
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 Chapter 4, "Preparing the Transaction Logs for Oracle GoldenGate."
With SQL Server 2014, neither encrypted transaction log backups or compressed log backups are supported.