Documentation
Advanced Search


Installing and Configuring Oracle GoldenGate for Oracle Database

1 System Requirements and Preinstallation Instructions

This chapter contains the requirements for the system and database resources that support Oracle GoldenGate.

This chapter includes the following sections:

Note:

Oracle GoldenGate supports two capture modes for an Oracle source database. Some system requirements may vary depending on the capture mode that is selected. To learn about the capture modes, see Section 5.2, "Deciding Which Capture Method to Use."

1.1 Supported Platforms

To find the most recent matrix of Oracle GoldenGate builds for specific combinations of database version and operating system, log onto http://support.oracle.com and select the Certifications tab.

1.2 Operating System Requirements

This section outlines the operating system resources that are necessary to support Oracle GoldenGate.

1.2.1 Memory Requirements

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.

1.2.2 Disk Requirements

Assign an amount of free disk space based on the platform, database, and whether you are installing Oracle GoldenGate in the normal manner or with the Oracle Universal Installer (OUI). You can delete the download file after the installation is complete.

1.2.2.1 Disk Requirements for Oracle GoldenGate Installation Files

This section shows the disk requirements for a manual installation and for an installation through OUI.

Table 1-1 shows the disk space that is consumed by the files of one Oracle GoldenGate installation in a manual build. A manual build does not use OUI. The person installing Oracle GoldenGate uncompresses the files and creates the working directories.

Table 1-1 Disk Requirements for a Manual Build

Platform Oracle Version Compressed Size (MB) Installed Size (MB)

Windows

11g

92.3 zip file

188

Windows

12c

92.3 zip file

188

Linux

11g

137 zip file

319 tar file

319

Linux

12c

146 zip file

348 tar file

348

Solaris SPARC

11g

62 zip file

194 tar file

199

Solaris SPARC

12c

87 zip file

198 tar file

203


Table 1-2 shows the disk space that is consumed by the files of one Oracle GoldenGate installation when OUI is used.

Table 1-2 Disk Requirements for an OUI Build

Platform Oracle Version Compressed Size (MB) Installed Size (MB)

Windows

11g

321 zip file

325 OUI installer

504

Windows

12c

321 zip file

325 OUI installer

504

Linux

11g

325 zip file

329 OUI installer

492

Linux

12c

325 zip file

329 OUI installer

521

Solaris SPARC

11g

214 zip file

219 OUI installer

391

Solaris SPARC

12c

214 zip file

219 OUI installer

391


1.2.2.2 Other Disk Space Considerations

In addition to the disk space required for the files and binaries that are installed by Oracle GoldenGate, allow an additional 1 GB of disk space on any system that hosts the Oracle GoldenGate trail (or trails). A trail is a set of self-aging files that contain the working data at rest and during processing. 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.

Disk space is also required for the Oracle GoldenGate Bounded Recovery feature. Bounded Recovery is a component of the general Extract checkpointing facility. It caches long-running open transactions to disk at specific intervals to enable fast recovery upon a restart of Extract. At each bounded recovery interval (controlled by the BRINTERVAL option of the BR parameter) the disk required is as follows: for each transaction with cached data, the disk space required is usually 64k plus the size of the cached data rounded up to 64k. Not every long-running transaction is persisted to disk. For complete information about Bounded Recovery, see the BR parameter in Reference for Oracle GoldenGate for Windows and UNIX.

1.2.2.3 Installing in a Cluster

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.4, "Preparing to Install Oracle GoldenGate Within a Cluster" for more information.

1.2.2.4 Temporary Disk Requirements

By default, Oracle GoldenGate maintains data that it writes to disk in the dirtmp sub-directory of the Oracle GoldenGate installation directory. When total cached transaction data exceeds the CACHESIZE setting of the CACHEMGR parameter, Extract will begin writing cache data to temporary files. 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 to this directory with the CACHEDIRECTORY option of the CACHEMGR parameter.

It is typically more efficient for the operating system to swap to disk than it is for Extract to write temporary files. The default CACHESIZE setting assumes this. Thus, there should be sufficient disk space to account for this, because only after the value for CACHESIZE is exceeded will Extract write transaction cached data to temporary files in the file system name space. If multiple Extract processes are running on a system, the disk requirements can multiply.Allow at least 256 GB of disk space per Extract process for these activities. An allocation of at least 512 GB is preferable.

1.2.3 Network

The following network resources must be available to support Oracle GoldenGate.

  • For optimal performance and reliability, especially in maintaining low latency on the target, use the fastest network possible and install redundancies at all points of failure.

  • 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 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.

1.2.4 Operating System Privileges

The following are the privileges in the operating system that are required to install Oracle GoldenGate and to run the processes.

  • To install on Windows, the person who installs Oracle GoldenGate must log in as Administrator.

  • To install on UNIX, the person who installs Oracle GoldenGate must have read and write privileges on the Oracle GoldenGate installation directory.

  • The Oracle GoldenGate Extract, Replicat, and Manager processes must operate as an operating system user that has privileges to read, write, and delete files and subdirectories in the Oracle GoldenGate directory. In addition, the Manager process requires privileges to control the other Oracle GoldenGate processes.

  • (Classic capture mode) In classic capture mode, the Extract process reads the redo logs directly and must operate as an operating system user that has read access to the log files, both online and archived. On UNIX systems, that user must be a member of the group that owns the Oracle instance. If you install the Manager process as a Windows service during the installation steps in this documentation, you must install as Administrator for the correct permissions to be assigned. If you cannot install Manager as a service, assign read access to the Extract process manually, and then always run Manager and Extract as Administrator.

  • Dedicate the Extract, Replicat, and Manager operating system users to Oracle GoldenGate. Sensitive information might be available to anyone who runs an Oracle GoldenGate process, depending on how database authentication is configured.

1.2.5 Itanium Requirements

To install Oracle GoldenGate on an Intel Itanium system, the vcredist_IA64.exe runtime library package must be installed. You can download this package from the Microsoft website. This package includes Visual Studio DLLs necessary for Oracle GoldenGate to operate on the Itanium platform. If these libraries are not installed, Oracle GoldenGate generates the following error.

The application failed to initialize properly (0xc0150002). Click on Ok to terminate the application.

1.2.6 Console Character Sets

The operating system and the command console must have the same character sets. Mismatches occur on Microsoft Windows systems, where the operating system is set to one character set, but the DOS command prompt uses a different, older DOS character set. Oracle GoldenGate uses the character set of the operating system to send information to GGSCI command output; therefore a non-matching console character set causes characters not to display correctly. You can set the character set of the console before opening a GGSCI session by using the following DOS command:

chcp OS character set

If the characters do not display correctly after setting the code page, try changing the console font to Lucida Console, which has an extended character set.

1.2.7 Other Programs

The following are additional considerations in support of Oracle GoldenGate.

  • 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 http://www.microsoft.com.

  • 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. For example, on a Windows system with a RHAS 5.0 virtual machine running Oracle11g, you would install the RHAS 5.0 build for Oracle 11g, just as you would on an actual Linux machine.

1.3 Oracle Universal Installer Requirements for Oracle GoldenGate

To use Oracle Universal Installer to install Oracle GoldenGate, the following requirements must be satisfied before running the installer.

  • OUI is a graphical Java application. The host platform must be Java-enabled. The required Java Runtime Environment (JRE) is automatically installed with OUI.

  • If this is the first time you are installing an Oracle product with OUI, you will be prompted to create the Oracle central inventory, which also creates a subdirectory for Oracle GoldenGate to contain inventory information. This subdirectory typically requires 150 kilobytes of disk space.

1.4 Database Configuration

This section contains Oracle GoldenGate requirements that are specific to the Oracle database. These apply to both capture modes unless explicitly noted.

  • Database user privileges and configuration requirements are explained in Chapter 4, "Establishing Oracle GoldenGate Credentials".

  • To run Oracle GoldenGate for multiple Oracle instances (ORACLE_SID) on a Windows system, you must install an instance of Oracle GoldenGate for each one

  • On 64-bit Sun Solaris, Oracle GoldenGate requires LD_LIBRARY_PATH to include the 32-bit Oracle libraries. You will be instructed to set LD_LIBRARY_PATH in Section 2.3, "Setting Library Paths for Dynamic Builds on UNIX".

  • If the database is configured to use a bequeath connection, the sqlnet.ora file must contain the bequeath_detach=true setting.

  • The full Oracle client must be used with Oracle GoldenGate so that the Oracle GoldenGate programs have access to the Oracle XDK libraries. Do not use Oracle Instant Client, which lacks those libraries. You can download the full client from the Oracle website.

  • To install Oracle GoldenGate in an Oracle Real Application Cluster (RAC) environment, install Oracle GoldenGate on the shared drive(s) that are accessed by the RAC nodes. For more information, see Section 2.4, "Preparing to Install Oracle GoldenGate Within a Cluster."

  • (Integrated capture mode) To use integrated capture with an Oracle 11.2.0.3 source database, download and install the 11.2.0.3 database specific bundle patch for Integrated Extract. Refer to My Oracle Support article 1557031.1 for all Oracle database release bundled patches. This mode makes use of a logmining server on the source system or in a downstream Oracle database. For more information, see Chapter 5, "Choosing Capture and Apply Modes"

1.5 Summary of Supported Oracle Data Types and Objects Per Capture Mode

Table 1-3 summarizes the way that Oracle GoldenGate supports the Oracle data types according to the capture mode that you choose. For more information about capture modes, see Section 5.2, "Deciding Which Capture Method to Use."

Detailed support information for Oracle data types, objects, and operations starts with Section 1.6, "Details of Support for Oracle Data Types."

Table 1-3 Supported Data Types Per Capture Mode

Data type Classic capture Integrated capture

Scalar columns including DATE and DATETIME columns

Captured from redo.

Captured from redo.

LONG VARCHAR

Not supported.

Captured from redo.

BASICFILE LOB columns

LOB modifications done using DML (INSERT/UPDATE/DELETE) are captured from redo.

LOB modifications done using DBMS_LOB package are captured from the source table by fetching values from the base table.

Captured from redo.

SECUREFILE LOB columns

Captured from redo, except for the following cases where SECUREFILE LOBs are fetched from the source table:

  • LOB is encrypted

  • LOB is compressed

  • LOB is deduplicated

  • LOB is stored in-line

  • LOB is modified using DBMS_LOB package

  • NOLOGGING LOBs

Captured from redo, except for the following cases where SECUREFILE LOBs are fetch from the source table:

  • LOB is deduplicated (Oracle 11g)

  • LOB is modified using DBMS_LOB.FRAGMENT_* procedures

  • NOLOGGING LOBs

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Index Organized Tables (IOT)

Captured from redo with the following restrictions:

  • IOT with mapping table not supported.

  • Direct load inserts to IOT tables cannot have the SORTED clause.

  • IOT with prefix compression as specified with COMPRESS clause is not supported.

  • IOT does not have physical ROWID it is logical so capture is not supported.

Captured from redo with the following restriction:

  • IOT with mapping table not supported.

XML columns stored as CLOB

Captured from redo.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

XML columns stored as Binary

Fetched from source table.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

XML columns stored as Object-Relational

Not supported.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

XML Type Table

Not supported.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

User Defined Type (UDT) columns

Fetched from source table.

Captured from redo with limitations if redo-based capture for UDT and ANYDATA is enabled.

You can specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo . For the cases USENATIVEOBJSUPPORT does not handle, fetch is used.

Fetched from source table with limitations if redo-based capture for UDT and ANYDATA is disabled.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

ANYDATA columns

Fetched from source table with the following data types only:

BINARY_DOUBLE

BINARY_FLOAT

CHAR

DATE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

NCHAR

NUMBER

NVARCHAR2

RAW

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIMEZONE

UDTs

VARCHAR/VARCHAR2

Requires source database compatibility to be set to 11.2.0.0.0 or higher.

Captured from redo with limitations if redo-based capture for UDT and ANYDATA is enabled.

You can specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo . For the cases USENATIVEOBJSUPPORT does not handle, fetch is used.

Fetched from source table with limitations if redo-based capture for UDT and ANYDATA is disabled.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Spatial Types columns

Fetched from source table.

Captured from redo with limitations if redo-based capture for UDT and ANYDATA is enabled for:

  • SDO_GEOMETRY

  • ST_GEOMETRY

You can specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo . For the cases USENATIVEOBJSUPPORT does not handle, fetch is used.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Fetched from source table with limitations if redo-based capture for UDT and ANYDATA is disabled:

  • SDO_TOPO_GEOMETRY

  • SDO_GEORASTER (raster tables)

Collections columns (VARRAYs)

Fetched from source table.

Captured from redo for VARRAY attributes (varray is an attribute of other UDTs).

Fetched from source table.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Collections columns (Nested Tables)

Fetched from source table with limitations.

See Section 1.7, "Details of Support for Objects and Operations in Oracle DML".

Fetched from source table with limitations.

See Section 1.7, "Details of Support for Objects and Operations in Oracle DML".

Object Table

Fetched from source table.

Captured from redo if redo-based capture for UDT and ANYDATA is enabled.

Fetched from source table with limitations if redo-based capture for UDT and ANYDATA is disabled.

You can specify TRANLOGOPTIONS USENATIVEOBJSUPPORT to enable the capture from redo . For the cases USENATIVEOBJSUPPORT does not handle, fetch is used.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Transparent Data Encryption (Column Encryption & Tablespace Encryption)

Captured from redo.

Requires additional setup: See Section 11.1, "Configuring Oracle TDE Data in Classic Capture Mode".

Captured from redo.

No additional setup is required.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Basic Compression

Not supported.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

OLTP-Compression

Not supported.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

Exadata Hybrid Columnar Compression

Not supported.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

XA on non-RAC database

Captured from redo.

Captured from redo.

XA on RAC database

Not supported.

To get support, must make sure all branches of XA goes to the same instance.

Captured from redo.

Use of Native Object Support requires source database compatibility to be set to 12.0.0.0.0 or higher.

PDML on non-RAC database

Captured from redo.

Captured from redo.

PDML on RAC database

Not supported.

To get support, you must make sure child transactions spawned from a PDML transaction do not span multiple instances.

Captured from redo.

Invisible columns

Not supported.

Not supported.


1.6 Details of Support for Oracle Data Types

The following outlines details of Oracle data type support by Oracle GoldenGate. Unless otherwise noted, the support applies to both classic and integrated capture mode. For more information about these modes, see Chapter 5, "Choosing Capture and Apply Modes".

1.6.1 ANYDATA Data Types

The following ANYDATA data types are supported:

Fetched from source table with the following data types only:

  • BINARY_DOUBLE

  • BINARY_FLOAT

  • CHAR

  • DATEINTERVAL DAY TO SECOND

  • INTERVAL YEAR TO MONTH

  • NCHAR

  • NUMBER

  • NVARCHAR2

  • RAW

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIMEZONE

  • UDTs

  • VARCHAR/VARCHAR2

1.6.1.1 Limitations of Support

Your source database compatibility must be set to 11.2.0.0.0 or higher. Support for named collections and VARRAYS embedded within those data types.

1.6.2 Numeric Data Types

The following numeric data types are supported:

  • NUMBER up to the maximum size permitted by Oracle

  • BINARY FLOAT

  • BINARY DOUBLE

1.6.2.1 Limitations of Support

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.

1.6.3 Character Data Types

The following character data types are supported:

  • CHAR

  • VARCHAR2

  • LONG

  • NCHAR

  • NVARCHAR2

1.6.3.1 Limitations of Support

  • Extended (32K) VARCHAR2 and NVARCHAR2 columns are supported when Extract is in integrated capture mode. All modes of Replicat support 32K VARCHAR2 and NVARCHAR2 columns. The following limitations apply:

    • Oracle GoldenGate does not support 32K VARCHAR2 and NVARCHAR2 columns as part of a key or unique index, nor as a column in a KEYCOLS clause of the TABLE or MAP parameter. 32K columns cannot be used as row identifiers because they are not supplementally logged even when part of a primary key.

    • 32K columns are not supported as resolution columns in a CDR (conflict resolution and detection) configuration nor as the basis for any other work that requires a column value to be present in the transaction log.

    • Oracle GoldenGate does not limit the number of 32K columns, but each trail record has a length limit of 4MB for inline records. The number of 32K columns that reaches this limit is approximately 160 columns, but the number of columns also depends on the actual size of the extended VARCHAR2 column.

1.6.4 Multi-byte Character Types

The following multi-byte character types are supported:

  • NCHAR and NVARCHAR2 multi-byte character data types

  • Multi-byte data stored in CHAR and VARCHAR2 columns

1.6.4.1 Limitations of Support

  • For Oracle GoldenGate to support 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 are not supported. The character sets between the two databases must be one of the following:

    • Identical, for example SHIFT-JIS on the source and on the target.

    • Equivalent, which is not the same character set but containing the same set of characters, for example SHIFT-JIS and EUC-JP.

    • Target is superset of the source: For example, UNICODE is a superset of all character types, and therefore of any other character set.

  • Multi-byte data is supported whether the length semantics are in bytes or characters.

For additional configuration requirements, see Section 10.3, "Handling Special Data Types."

1.6.5 Binary Data Types

The following binary data types are supported:

  • RAW

  • LONG RAW

1.6.6 Date and Timestamp Data Types

The following date and time data types are supported:

  • DATE

  • TIMESTAMP (see Limitations of support)

1.6.6.1 Limitations of Support

  • Oracle GoldenGate does not support negative dates.

  • INTERVAL DAY and INTERVAL YEAR are supported only if the size of the target column is equal to, or greater than, that of the source.

  • Oracle GoldenGate supports the capture and replication of TIMESTAMP WITH TIME ZONE as a UTC offset (TIMESTAMP '2011-01-01 8:00:00 -8:00').

  • TIMESTAMP WITH TIME ZONE as TZR (Region ID) is supported for the replication of data changes, but not for initial loads, for SQLEXEC, or for operations where the column must be fetched from the database. In these cases, the region ID is converted to a time offset by the database when the column is selected. Replicat replicates the timestamp as date and time data with a time offset value.

  • 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.

  • Oracle GoldenGate supports time offset values between +12:00 and -12:00.

To support TIMESTAMP WITH TIME ZONE specified as TZR properly, and also to handle TIMESTAMP WITH LOCAL TIMEZONE properly, see the Section 10.3, "Handling Special Data Types."

1.6.7 Large Object Data Types

The following large object types are supported:

  • CLOB

  • NCLOB

  • BLOB

  • SECUREFILE and BASICFILE

1.6.7.1 General Limitations of Support — Integrated and Classic Capture Modes

Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects larger than 4K.

1.6.7.2 Limitations of support — Classic Capture Mode

  • BASICFILE option LOBs are captured from the redo log, but are fetched from the database in the following circumstances:

    • Extract determines the LOB is invalid.

    • The LOB data is not in the redo log, which occurs when the BASICFILE LOB is created with the NOLOGGING option.

    • The LOB is created with the CACHE option.

    • The LOB is only partially updated. Oracle GoldenGate does not support partial column data. Extract assumes LOB data to be incomplete if the LOB does not start with a LOB reset record, or if the LOB does not start at the first byte and does not end at the last byte, according to the new LOB length. Partial updates can be generated by the following OCI calls: OCILOBWrite(), OCILobAppend(), OCiLobCopy(), OCILobLoadFromFile(), OCILobTrim(), and by updates made through procedures in the dbms_lob package.

    • Extract detects an anomaly in the LOB data.

  • SECUREFILE option LOBs are captured from the redo logs only when the update is complete and the LOB is not transformed (the column is not compressed or encrypted or deduplicated) and stored out-of-row. SECUREFILE LOBs are fetched from the database in the following circumstances:

    • The LOB is stored in-row.

    • The LOB is transformed either with compression or encryption.

    • The LOB is created with the CACHE attribute.

    • Extract determines that a LOB instance is invalid.

    • LOB data is missing from the redo log. This can occur if the LOB is created with any of following options: DEDUPLICATE, NOLOGGING, file system_LIKE_LOGGING.

    • The LOB is updated using OCILOBWrite(), OCILobAppend(), OCiLobCopy(), OCILobLoadFromFile(), OCILobTrim(), or through procedures in the dbms_lob package.

    • Any other anomalies as detected by Extract.

  • When changing a SECUREFILE LOB from one storage to another (such as from ENCRYPT to DECRYPT), Oracle updates the whole table, and Extract captures those updates from the log. Therefore, it will appear as though Oracle updated all of the data blocks that are associated with the table. This also can happen when an ALTER TABLE command sets a DEFAULT value to a column that has null values.

For additional setup requirements, see Section 10.3, "Handling Special Data Types."

1.6.8 XML Data Types

The following XML types are supported:

  • In integrated capture mode, Oracle GoldenGate supports XMLType columns and XMLType tables stored as XML CLOB, XML Object Relational, and XML Binary.

  • In classic capture mode, Oracle GoldenGate supports XMLType columns stored as XML CLOB and XML Binary.

1.6.8.1 Limitations of Support — Integrated and Classic Capture Modes

The following are not supported:

  • Oracle GoldenGate treats XMLType data as a LOB.

  • Filtering and manipulation are not supported. You can map the XML representation of an object to a character column by means of a COLMAP clause in a TABLE or MAP statement.

  • Oracle recommends the AL32UTF8 character set as the database character set when working with XML data. This ensures the correct conversion by Oracle GoldenGate from source to target.

  • Hierarchy-enabled tables (these are managed by the Oracle XML database repository.)

  • Assuming DDL support is enabled, Oracle GoldenGate replicates the CTAS statement and allows it to select the data from the underlying target table(s). The original inserts are not replicated. For XMLType tables, the row object IDs must match between source and target, which cannot be maintained when Replicat uses logical SQL statements. XMLType tables created by an empty CTAS statement (that does not insert data in the new table) can be maintained correctly.

  • XMLType tables with primary key-based object identifiers (OID)

  • Relational tables with a single XML column

  • SQL*Loader direct-path insert for XML Binary and XML Object Relational

  • XMLSchema-based XMLType are supported, but changes made to XMLSchemas are not replicated and must be registered on both source and target databases with the DBMS_XMLSCHEMA package.

  • Tables that contain XMLType columns must have at least one unique key constraint that is made up of scalar columns, or the combination of all scalar columns must guarantee uniqueness. Extract or Replicat cannot use unique or primary key constraints made up of XML attributes for row identification purposes.

1.6.8.2 Limitations of Support — Integrated Capture Mode

  • XML OR and XML Binary, source database compatibility is with releases greater than 11.2.0.3.

  • XML CLOB, source database compatibility is with releases greater than 11.0.0.0.0.

  • The maximum length for the entire SET value of an update to an XMLType is 32K, including the new content plus other operators and XQuery bind values.

1.6.8.3 Limitations of Support — Classic Capture Mode

  • For XML Binary, Oracle GoldenGate fetches additional row data from the source database. Because the fetched data may not part of the original transaction, it may lead to inconsistency.

  • XML Object Relational is not supported in classic capture mode.

See Section 10.3, "Handling Special Data Types" for additional information about replicating XML.

1.6.9 User Defined or Abstract Types

Oracle GoldenGate supports User Defined types (UDT) or Abstract Data Types (ADT) when the source and target objects have the same structure. The schema names can be different.

1.6.9.1 General Limitations of Support — Integrated and Classic Capture Modes

  • Redo-based supports most attribute types, but falls back to fetching from source table when UDT contains:

    • Nested Table

    • SDO_TOPO_GEOMETRY

    • SDO_GEORASTER

  • Fetch-based does not support UDT that contains:

    • ANYDATA

    • TIMESTAMP WITH TIMEZONE

    • TIMESTAMP WITH LOCAL TIMEZONE

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND

    • BINARY FLOAT

    • BINARY DOUBLE

  • Oracle GoldenGate GG does not support UDTs that contain:

    • CFILE

    • BFILE

    • REF

    • OPAQUE (with exception of XMLType and ANYDATA:

  • A table that contains a UDT must have one of the following: a primary key, column(s) with a unique constraint, or a unique index.

  • Object or relational tables where the key contains a UDT, or where a UDT is the only column, are not supported.

  • The RMTTASK parameter does not support user-defined types (UDT).

  • CHAR and VARCHAR attributes that contain binary or unprintable characters are not supported.

  • UDTs, including values inside object columns or rows, cannot be used within filtering criteria in TABLE or MAP 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.

For additional setup requirements, see Section 10.3, "Handling Special Data Types."

1.6.9.2 Limitations for Collection Types — Integrated and Classic Capture Modes

  • When data in a nested table is updated, the row that contains the nested table must be updated at the same time.

  • When VARRAYS and nested tables are fetched, the entire contents of the column are fetched each time, not just the changes.

1.6.9.3 Limitations for Object Tables — Integrated and Classic Capture Modes

Integrated Capture Only (Redo-based)

  • Redo-based captures object tables from redo, but falls back to fetching from source table when an object table contains the following attributes:

    Nested Table
    SDO_TOPO_GEOMETRY
    SDO_GEORASTER
  • To fully support object tables created with CREATE TABLE as SELECT (CTAS) statement, Integrated Capture must be configured to capture DML from the CTAS statement. For more information about CTAS, see Section 13.10.4.2, "CREATE TABLE AS SELECT."

  • An Oracle object table can be mapped to a non-Oracle object table in a supported target database.

Classic and Integrated Capture (Fetch-based)

  • Fetch-based fetches all leaf-level attributes, as well as, root-level LOB, XML, UDT, ANYDATA, and collection attributes.

  • Fetch-based does not support object tables that contain the following leaf-level attributes:

    ANYDATA
    TIMESTAMP WITH TIMEZONE
    TIMESTAMP WITH LOCAL TIMEZONE
    INTERVAL YEAR TO MONTH
    INTERVAL DAY TO SECIND
    BINARY FLOAT
    BINARY DOUBLE
  • Oracle GoldenGate supports object tables in uni-directional and active-active configurations. Object tables are captured from the redo log, but certain data types that are fetched from the database when in regular relational tables, such as LOBs and collection types, are also fetched when in object tables. Similarly, current limitations that apply to collection types when in regular tables also apply to these types when in object tables.

  • A primary key must be defined on the root-level object attributes of the object table, and cannot include leaf-level attributes. If no key is defined, Oracle GoldenGate will use all useable columns as a pseudo-key.

  • Oracle GoldenGate does not support the replication of DDL operations for an object table. This limitation includes the database object versioning that is associated with ALTERs of object tables.

  • Synonyms are not supported for object tables or object types that contain object tables.

1.6.9.4 Limitations for Spatial Types — Integrated and Classic Capture Modes

Oracle GoldenGate supports SDO_GEOMETRY, SDO_TOPO_GEOMETRY, and SDO_GEORASTER (raster tables).

See additional configuration information in Section 10.3, "Handling Special Data Types."

1.6.10 Non-Supported Oracle Data Types

Oracle GoldenGate does not support the following data types.

  • ANYDATA fetch-based column support for data types with VARRAYS that do not include named collections and VARRAYS embedded within those data types

  • ANYDATASET

  • ANYTYPE

  • BFILE

  • MLSLABEL

  • ORDDICOM

  • REFs

  • TIMEZONE_ABBR

  • URITYPE

  • UDT with containing an unsupported Oracle Data Type or UDT and nested tables

  • Oracle GoldenGate does not support replication of identity column data or Valid Time Temporal column data.

See additional exclusions in Section 1.5, "Summary of Supported Oracle Data Types and Objects Per Capture Mode."

1.7 Details of Support for Objects and Operations in Oracle DML

This section outlines the Oracle objects and operations that Oracle GoldenGate supports for the capture and replication of DML operations.

1.7.1 Multitenant Container Databases

Oracle GoldenGate captures from, and delivers to, a multitenant container database. See Chapter 6, "Configuring Oracle GoldenGate in a Multitenant Container Database" for more information about how Oracle GoldenGate supports multitenant container databases.

1.7.2 Tables, Views, and Materialized Views

Oracle GoldenGate supports the following DML operations made to regular tables, index-organized tables, clustered tables, and materialized views.

  • INSERT

  • UPDATE

  • DELETE

  • Associated transaction control operations

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. For more information, see the Oracle Database Reference.

1.7.2.1 Limitations of Support for Regular Tables

These limitations apply to integrated and classic capture modes.

  • 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).

  • 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

    • Nested table

    • UDT

    • VARRAY

    • XMLType

  • Oracle GoldenGate supports tables with unused columns, but the support is disabled by default, and Extract abends on them. See Section 10.4, "Handling Other Database Properties."

  • Oracle GoldenGate supports tables with these partitioning attributes:

    • Range partitioning

    • Hash Partitioning Interval Partitioning

    • System Partitioning

    • Composite Partitioning

    • Virtual Column-Based Partitioning

    • Reference Partitioning

    • List Partitioning

    See Section 10.4, "Handling Other Database Properties."

  • 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. See Section 10.4, "Handling Other Database Properties."

  • Oracle GoldenGate ignores any virtual column that is part of a unique key or index. If a virtual column is the only unique identifier for a table, Oracle GoldenGate uses all of the remaining columns for row identification.

  • Oracle GoldenGate supports replication to and from Oracle Exadata. To support Exadata Hybrid Columnar Compression, Extract must operate in integrated capture mode. 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).

    • Extract supports TDE column encryption and TDE tablespace encryption without setup requirements in integrated capture mode. For integrated capture, the source database must be Oracle version 11.1.0 with compatibility setting of 11.0.0.0 or higher.

    • In classic capture mode, Extract supports column encryption for all versions of Oracle 11.1 and 11.2. Tablespace encryption is supported for all versions of Oracle 11.2.0.1. TDE in classic capture mode requires some setup. See Section 11.1, "Configuring Oracle TDE Data in Classic Capture Mode."

  • Oracle GoldenGate supports TRUNCATE statements as part of its DDL replication support, or as standalone functionality that is independent of the DDL support. See Section 10.4, "Handling Other Database Properties."

  • Oracle GoldenGate supports the capture of direct-load INSERTs. 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 (Not supported for RAC in classic capture mode)

    • SQLLDR with DIRECT=TRUE

  • Oracle GoldenGate fully supports capture from compressed objects when Extract is in integrated capture mode. The source database version must be 11.2.0.0 or higher if capturing from a downstream mining database or 11.2.0.3 if the source database is the mining database. Extract in classic capture mode does not support compressed objects.

  • Oracle GoldenGate supports XA and PDML distributed transactions in integrated capture mode. Extract in classic capture mode does not support PDML or XA on RAC.

  • Oracle GoldenGate supports DML operations on tables with FLASHBACK ARCHIVE enabled. However, Oracle GoldenGate does not support DDL that creates tables with the FLASHBACK ARCHIVE clause or DDL that creates, alters, or deletes the flashback data archive itself.

1.7.2.2 Limitations of Support for Index-Organized Tables

These limitations apply to classic capture mode.

  • IOT with a mapping table is not supported in classic capture mode. The DDL for an IOT with mapping table will be replicated correctly, but subsequent DML on it will fail. This limitation applies to integrated and classic capture modes.

  • IOT with key compression enabled (indicated by the COMPRESS keyword in the key_compression clause) is not supported in classic capture mode, but is supported in integrated capture mode.

1.7.2.3 Limitations of Support for Views

These limitations apply to integrated and classic capture modes.

  • 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.

  • Oracle GoldenGate can replicate to a view as long as the view is inherently updateable. The structures of the source tables and a target view must be identical.

See configuration requirements for views in Section 10.4, "Handling Other Database Properties."

1.7.2.4 Limitations of Support for Materialized Views

Materialized views are supported by Extract in classic and integrated modes 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 the FOR UPDATE clause

  • Either materialized views can be replicated or the underlying base table(s), but not both.

1.7.2.5 Limitations of Support for Clustered Tables

Indexed clusters are supported in both integrated and classic capture modes while hash clusters are not supported in either modes. In classic capture mode the following limitations apply:

  • Encrypted and compressed clustered tables are not supported in classic capture.

  • Extract in classic capture mode captures DML changes made to index clustered tables if the cluster size remains the same. Any DDL that causes the cluster size to increase or decrease may cause Extract to capture subsequent DML on that table incorrectly.

1.7.3 Sequences

  • Oracle GoldenGate supports the replication of sequence values in a uni-directional 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 0).

1.7.3.1 Limitations of Support for Sequences

These limitations apply to integrated and classic capture modes.

  • 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.

See configuration requirements in Section 10.4, "Handling Other Database Properties."

1.7.4 Non-supported Objects and Operations in Oracle DML

The following are additional Oracle objects or operations that are not supported by Extract in either classic or integrated capture mode:

  • REF

  • Sequence values in an active-active bi-directional configuration

  • Database Replay

  • Tables created as EXTERNAL

The following are not supported in classic capture mode:

  • Exadata Hybrid Columnar Compression

  • Capture from tables with OLTP table compression

  • Capture from tablespaces and tables created or altered with COMPRESS

  • Capture from encrypted and compressed clustered tables

  • Synonyms

  • Invisible columns

  • Distributed transactions. In Oracle versions 11.1.0.6 and higher, you can capture these transactions if you make them non-distributed by using the following command, which requires the database to be restarted.

    alter system set _CLUSTERWIDE_GLOBAL_TRANSACTIONS=FALSE;
    
  • RAC distributed XA and PDML distributed transactions

  • Version enabled-tables

  • Identity columns

1.8 Details of Support for Objects and Operations in Oracle DDL

This section outlines the Oracle objects and operation types that Oracle GoldenGate supports for the capture and replication of DDL operations. For more information about DDL support, see the following:

Chapter 13, "Configuring DDL Support"

Appendix D, "Installing Trigger-Based DDL Capture" (Trigger-based capture is required for Oracle releases that are earlier than version 11.2.0.4. If Extract will run in integrated mode against a version 11.2.0.4 or later Oracle database, the DDL trigger and supporting objects are not required.)

1.8.1 Supported Objects and Operations in Oracle DDL

When the source database is Oracle 11.2.0.4 or later and Extract operates in integrated mode, DDL capture support is integrated into the database logmining server and does not require the use of a DDL trigger. You must set the database parameter compatibility to 11.2.0.4.0. In integrated capture mode, 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;

Note:

Password-based column encryption in DDL is not supported in classic capture mode.

The following additional statements apply to both integrated and classic capture modes 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

  • Oracle Edition-Based Redefinition (EBR) database replication of Oracle DDL is supported for integrated Extract for the following Oracle Database objects:

    • functions

    • library

    • packages (specification and body)

    • procedure

    • synonyms

    • types (specification and body)

    • views

    EBR does not support use of DDL triggers.

  • Oracle GoldenGate supports DDL operations of up to 4 MB in size. Oracle GoldenGate measures the size of DDL statement in bytes, not in characters. This size limitation includes packages, procedures, and functions. The actual size limit of the DDL support is approximate, because the size not only includes the statement text, but also Oracle GoldenGate maintenance overhead that depends on the length of the object name, the DDL type, and other characteristics of keeping a DDL record internally.

  • 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.

1.8.2 Non-supported Objects and Operations in Oracle DDL

These statements apply to integrated and classic capture modes.

1.8.2.1 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
  "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$*",
  "*.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

1.8.2.2 Other Non-supported DDL

Oracle GoldenGate does not support the following:

  • DDL on nested tables.

  • DDL on invisible columns.

  • DDL on identity columns.

  • ALTER DATABASE and ALTER SYSTEM (these are not considered to be DDL) When using Integrated Dictionary you can replicate ALTER DATABASE DEFAULT EDITION and ALTER PLUGGABLE DATABASE DEFAULT EDITION. All other ALTER [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 with FLASHBACK ARCHIVE is supported.

  • Classic capture mode does not support 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;

1.9 Supported and Non-supported Object Names

Oracle object names are case insensitive by default, but can be made case-sensitive with the use of double quotes. Oracle GoldenGate supports Oracle case-sensitivity. For information about Oracle GoldenGate support for object names and case, see Administering Oracle GoldenGate for Windows and UNIX.

Close Window

Table of Contents

Installing and Configuring Oracle GoldenGate for Oracle Database

Expand | Collapse