This chapter includes the following sections:
Make sure that you are installing your product on a supported hardware and operating system configurations. 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.
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, "Understanding and Obtaining the Oracle GoldenGate Distribution."
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 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.
For a source capture installation of Oracle GoldenGate, Manager requires Read permissions on the SQL Server database transaction log files and transaction log backups.
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 privileges.
Observe the following other program and settings information for Oracle GoldenGate for SQL Server:
To install capture on a remote Windows server, for archived log mode, 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, 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
To capture from a source SQL Server Standard Edition database, the SQL Server Replication features must be installed.
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 or for an Extract running off the database server in an Archived Log Mode, 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:
Microsoft SQL Server 2012 Feature Pack:
Microsoft SQL Server 2014 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 an instance, the following must be true:
The SQL Server server name (
@@SERVERNAME) should not be NULL.
The SQL Server instance must be either Standard or Enterprise Edition.
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. See Section 4.4, "Enabling Supplemental Logging" for more information.
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.
Source databases must be set to the Full recovery model.
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.
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.
Oracle GoldenGate does not support system databases or 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 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.
The source database must not be configured with TDE (Transparent Data Encryption).
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 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 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 DML). 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 tables 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 (
sysadmin) role. For more information, see Section 4.4, "Enabling Supplemental Logging."
The Oracle GoldenGate Extract process captures data from a source SQL Server database for initial loads and from the transaction log for change data capture, 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, SQL Server configuration. Transformation, filtering, and other types of manipulation are not supported for multi-byte character data.
If capture of 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 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.
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 is supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the
SQL_Variant data type is not supported for capture.
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.
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 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 (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 from 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:
For source databases, operations that are not supported by SQL Server Change Data Capture or Transactional Replication. 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.
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."