Oracle GoldenGate Microservices and Classic architecture deployments

Download and install the latest OPatch release

Download the latest OPatch release from: How To Download And Install The Latest OPatch(6880880) Version (Doc ID 274526.1).

Download the most recent release of OPatch in a directory, and keep a note of this installation directory. See Installing Patches for Oracle GoldenGate Microservices Architecture to know more about installing patches for Oracle GoldenGate Microservices architecture.

See Patching for Classic Architecture to know more about installing patches for Oracle GoldenGate Classic architecture.

The Oracle Universal Installer (OUI) that is used to install Oracle GoldenGate should be updated separately from the Oracle GoldenGate product to address any security vulnerability found in the JDK or Java components. You may follow the instructions provided in the MOS note Doc ID 2359395.1 to understand the requirements for installing or upgrading the default JDK/JRE.

This release includes critical security fixes for various CVEs. Oracle strongly recommends that you upgrade to this release of Oracle GoldenGate.

jquery-1.6.1.min.js was removed to address the following CVEs:

CVE-2011-4969

CVE-2012-6708

CVE-2015-9251

CVE-2019-11358

CVE-2020-11022

CVE-2020-11023

Upgrade to JQUERY 3.5.1 to address CVE-2020-11022

Upgrading to JQUERY 3.5.1 addresses CVE-2020-11022.

Critical Security Fix

Release 19c (19.1.0.0.4): Oracle strongly recommends upgrading to the latest OPatch utility to resolve CVE-2020-9546. Oracle GoldenGate components themselves are not impacted by CVE-2020-9546, only the OPatch utility, which is why the OPatch upgrade is recommended.

Critical Security Fixes

Release 19c (19.1.0.0.4): This release includes critical security fixes including the following CVEs. Oracle strongly recommends that you upgrade to this release of Oracle GoldenGate.

CVE-2019-11358: JQUERY upgrade to 3.4.0

CVE-2018-11058: The update for CVE-2018-11058 also addresses the following:

• CVE-2016-0701

• CVE-2016-2183

• CVE-2016-6306

• CVE-2016-8610

• CVE-2018-11054

• CVE-2018-11055

• CVE-2018-11056

• CVE-2018-11057

• CVE-2018-15769

For more information, please refer to https://www.oracle.com/technetwork/security-advisory/cpujul2019-5072835.html.

Oracle GoldenGate for Amazon RDS for SQL Server 2019 is supported

Oracle GoldenGate supports Amazon RDS for SQL Server 2019.

Oracle GoldenGate for Sybase is supported

Oracle GoldenGate supports Sybase version 16.

PostgreSQL on Azure is supported

Oracle GoldenGate has been enhanced to support Azure database for PostgreSQL instance.

PostgreSQL

Extract support for PostgreSQL version 10 and above

PostgreSQL enhanced to provide Extract support for PostgreSQL version 10 and above.

This allows for the same heterogeneous replication that is common for all Oracle GoldenGate supported platforms, and you can replicate from PostgreSQL to Oracle. See the Using Oracle GoldenGate for Heterogeneous Databases to know more about supported features and requriements..

SSL support for PostgreSQL

Enhanced Oracle GoldenGate to enable configuring one-way and two-way SSL to connect to the database using GGSCI, Extract, Replicat and other processes.

Support MONEY datatype for all locales

Enhanced support for MONEY datatype for all locales.

Amazon RDS for PostgreSQL support

Amazon RDS for PostgreSQL remote capture and delivery is supported.

Amazon Aurora PostgreSQL

Amazon Aurora PostgreSQL remote capture and delivery is supported.

Remote capture and delivery support

Remote capture and delivery is supported for all PostgreSQL versions.

Interval data type support

Support for interval data type in capture and delivery.

Heartbeat support

Heartbeat support for PostgreSQL.

WILDCARDRESOLVE DYNAMIC support

Support for wildcardresolve dynamic is available.

NLS globalization support for object names in PostgreSQL capture

Capture and delivery supports globalization of object names and column data.

Extract should capture the records after positioning by time is issued (BEGIN NOW)

Extract supports positioning by TIMESTAMP EOF, LSN, and START.

Bidirectional loop detection and support

PostgreSQL supports bidirectional replication including various bidirectional filtering parameters (GETREPLICATES, IGNOREREPLICATES , GETAPPLOPS).

Support for MANAGESECONDARYTRUNCATIONPOINT and NOMANAGESECONDARYTRUNCATIONPOINT

PostgreSQL supports managing of secondary truncation point.

Handling the unsupported data types

Handling of unsupported datatypes.

Change Data Capture and Initial Load Support

Change Data Capture and initial load is supported by PostgreSQL capture.

MariaDB

MariaDB 10.4 Support

Local or remote capture and delivery are now supported for MariaDB 10.4.

Amazon RDS support for MariaDB

Amazon RDS for MariaDB for remote capture and delivery is now supported.

SQL Server

Amazon RDS for SQL Server is supported

Remote capture and delivery is now supported for Amazon RDS for SQL Server.

Teradata

Support Delivery to Teradata 17.0

Delivery to Teradata 17.0 is supported.

SQL Server

SQL Server: Support capture and delivery for Azure SQL Database Managed Instance

Oracle GoldenGate has been enhanced to support Azure SQL Database managed instance.

SQL Server: Support capture and delivery for SQL Server 2019 Enterprise and Standard Editions

Oracle GoldenGate has been enhanced to support SQL Server 2019.

MySQL

MySQL: Support capture and delivery for Azure Database for MySQL

Oracle GoldenGate has been enhanced to support Azure Database for MySQL instance.

DB2 LUW

DB2 LUW: Support for DB2 LUW verison 11.5

Oracle GoldenGate has been enhanced to support DB2 LUW version 11.5.

SQL Server

SQL Server 2017 for Capture and Delivery is Supported

Capture and delivery is supported for SQL Server 2017.

New TRANLOGOPTIONS Parameter ALWAYSONREADONLYROUTING

The TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter allows Extract for SQL Server to route its read-only processing to an available read-intent Secondary when connected to an Always On availability group listener.

New FILEGROUP Option for ADD TRANDATA and GLOBALS File

The FILEGROUP option can be used with ADD TRANDATA per table, or within the GLOBALS file to affect all tables added for TRANDATA, and creates the CDC staging tables on the listed filegroup rather than the database's default filegroup.

TRANCOUNT Option with TRANLOGOPTIONS

The new TRANCOUNT option allows you to set a specific number of transactions to be fetched from SQL Server CDC tables at a time.

New HEARTBEATTABLE Options

This feature allows installing the Oracle GoldenGate Heartbeat implementation for a database used as a target only, and does not enable features that would be necessary for a source database implementation of the Heartbeat table.

The following options are now available for heartbeat tables from the GGSCI Command Interface:

• ADD HEARTBEATTABLE [TARGETONLY]

• ALTER HEARTBEATTABLE [TARGETONLY|NOTARGETONLY]

Support for remote Capture/Delivery with Oracle GoldenGate on Linux, for SQL Server on Windows

Oracle GoldenGate for SQL Server can now run on Linux, providing remote Capture and Delivery support for SQL Server running on Windows.

Microsoft ODBC Driver Support

The Microsoft ODBC drivers for SQL Server are now supported by Extract and Replicat.

SQL Server CDC Object Versioning

Oracle GoldenGate now provides a version tracking subsystem to track the CDC objects that are placed on the database. If the version numbers don't match, the Extract abends with an error, which requires TRANDATA be reissued for existing tables previously enabled with supplemental logging.

TimesTen

Delivery Support for Oracle TimesTen

Delivery support for Oracle TimesTen 18c for both Classic and Scaleout Architectures is available.

DB2 z/OS

Online Schema Change Support

Support for online CREATE TABLE, DROP TABLE and ADD, ALTER, DROP COLUMN commands in the Extract without stopping and restarting the Extract to get the new table metadata for the Extract. Full table version history is supported. This feature is controlled using the TRANLOGOPTIONS TRACKSCHEMACHANGES parameter.

New Features in Oracle GoldenGate Microservices

Managing Encryption Using a Key Management Service in Oracle GoldenGate Microservices Architecture

Oracle GoldenGate Microservices Architecture provides integration with Oracle Key Vault (OKV) to centralize and manage encryption keys. Also see Using a KMS with Oracle GoldenGate.

Target-Initiated Paths

Target-initiated paths for microservices enable the Receiver Server to initiate a path to the Distribution Service on the target deployment and pull trail files. This feature allows the Receiver Server to create a target initiated path for environments such as Demilitarized Zone Paths (DMZ) or Cloud to on-premise, where the Distribution Server in the source Oracle GoldenGate deployment cannot open network connections in the target environment to the Receiver Server due to network security policies.

New Admin Client Commands

The following Admin Client commands are added to Oracle GoldenGate 19c (19.1.0) to work with target-initiated distribution paths:

• ADD RECVPATH

• START RECVPATH

• STOP RECVPATH

• DELETE RECVPATH

• ALTER RECVPATH

This following commands are designed to support improved scripting (using the OBEY Admin Client command) so that the username and password does not need to be included in the script itself, and instead you can use an alias :

• ADD CREDENTIALS

• INFO CREDENTIALS

• DELETE CREDENTIALS

The command UPGRADE HEARTBEATTABLE is available to alter heartbeat table generated by the ADD HEARTBEATTABLE command using a previous Oracle GoldenGate release.

The following commands have been added to allow users with security role to enable and disable services for any update operation by non-secure users.

• DISABLE SERVICE

• ENABLE SERVICE

The following commands used to controls Profiles that allow performing automatic start and restart for Extract and Replicat processes:

• ADD PROFILE

• DELETE PROFILE

The following commands are used to manage the encryption profile when using the Oracle Key Vault:

• ADD ENCRYPTIONPROFILE

• DELETE ENCRYPTIONPROFILE

• ALTER ENCRYPTIONPROFILE

New column conversion function @BINTOBASE64

New column conversion function to convert supplied binary data into BASE64 text.

New LAG TASK feature

Oracle GoldenGate Microservices now has the lag tasks feature in the Tasks tab, which allows you to set up the thresholds for lag in each Extract, Replicat, or Distribution Path processes.

TLS 1.2 Support

TLS 1.2 is now the default for secured deployments in Microservices.

New REST Endpoint to Retrieve Active Transactions and Current SCN Details

The REST API endpoints have been enhanced to retrieve active transaction and current system change number details from the database. These endpoints are used to enhance the file-based initial load process.

The REST API endpoints are accessed from the Administration Server and are:

Manage Instantiation CSN

(/services/{version}/connections/{connection}/databases/{database}/{schema}/{table}/instantiationCsn)

Retrieve Active Transaction Details

(/services/{version}/connections/{connection}/activeTransactions)

New HEARTBEATTABLE Option

The UPGRADE HEARTBEATTABLE option is now available for heartbeat tables from the GGSCI Command Interface:

Oracle Database

Improved Initial Load in Microservices

The consistent SCN used for the initial load is automatically stored in the report file generated by an initial load Extract, so it can be used to precisely position the Extract and Replicat processes to avoid duplicate records.

Enhanced the Heartbeat Table to include details for rebuilding Extract

This feature allows Replicats to maintain a more recent source restart position and minimize the redo retention periods. This feature is enabled by default for new heartbeat tables. Existing heartbeat tables are upgraded to support this enhancement. The enhanced heartbeat tables interoperate with heartbeat tables in deployments running older version of Oracle GoldenGate.

With more recent restart positions, you can configure deleting tombstone tables for Automatic Conflict Detection Resolution (ACDR) tables (an Oracle-to-Oracle CDR feature) to be purged more aggressively. ACDR tombstone tables is only pertinent if you are rebuilding an Extract and using automatic CDR. For this feature to work, both the source and target versions of Oracle GoldenGate must be 19c or higher.

Cross Endian Support for Remote Integrated Extract

Cross endian interoperability for Integrated Extract is automatically enabled when the endianess of the server where the Integrated Extract is running is different from the endianess of the server where the Oracle instance is running.

When the Integrated Extract process is running in Oracle Database 19c with Oracle GoldenGate 19c, it can capture from redo logs with COMPATABILITY 11.2.0.4 or higher. This implies that an Oracle GoldenGate cloud environment configured on Linux can perform remote capture from any endian platform including Linux, Windows, AIX, Tru64, OpenVMS, solaris SPARC, Solaris x86, HP-UX, and so on, running Oracle Database. This also works with both multitenant and non-CDB architectures.

DB2 for i

Support for TIMESTAMP (12)

Oracle GoldenGate for DB2 for i now supports TIMESTAMP (12)

DBOPTIONS Parameter USEDATABASEENCODING

The DBOPTIONS USEDATABASEENCODING parameter is introduced to allow Extract to store all text data in the trail in its native character encoding for non-DBCS data.

TRANLOGOPTIONS BUFSIZE Parameter is available with DB2 for i

The TRANLOGOPTIONS BUFSIZE parameter is available with DB2 for i.

Improved Extract throughput

Extract efficiency has been substantially improved over previous releases. Throughput is enhanced while reducing overall processing resources for the same workload.

Difference in throughput between the default conversion of text to UNICODE and ‘COLCHARSET(PASSTHRU,*’), which does not convert text, has been substantially reduced for Oracle GoldenGate supported character sets.

Security Options

AES encryption, credential store, and Oracle Wallet are now available.

TRANLOGOPTIONS MAXAUTOCMTTRANSSIZE

This parameter sets the maximum number of records that are included in an implicitly created transaction. This affects the maximum number of records that are not part of an explicit transaction (have a 0 CCID) can be grouped together into a single implicit transaction.

CSN Requirements

System sequence number is no longer required for single journal Extracts. It is recommended that you use RCVSIZOPT(*MAXOPT3) for their journals to enable a full 64-bit journal sequence number which will eliminate the need to reset journal sequence numbers periodically.

DECFLOAT data type

DECFLOAT data type is supported.

Oracle GoldenGate can automatically map DECFLOAT to any other numeric datatype, however, it may have more precision or larger values. Ensure that target datatype can support the values that are being inserted into the source.

@COMPUTE on DECFLOAT can only be used on values that are 17 digits or fewer. You may need to use @STRNUM some cases.

Long Running Transactions (LRT) support

LRT features SHOWTRANS, SKIPTRANS, FORCETRANS are supported.

DB2 LUW

Support for TIMESTAMP (12)

Oracle GoldenGate for DB2 LUW now supports TIMESTAMP (12).

DECFLOAT data type

DECFLOAT data type is supported.

Oracle GoldenGate can automatically map DECFLOAT to any other numeric datatype, however, it may have more precision or larger values. Ensure that target datatype can support the values that are being inserted into the source.

@COMPUTE on DECFLOAT can only be used on values that are 17 digits or fewer. You may need to use @STRNUM some cases.

LRT support

LRT features SHOWTRANS, SKIPTRANS, FORCETRANS is supported.

DB2 z/OS

Online Schema Change Support

Support for online TABLE CREATE, DROP and ADD, ALTER, DROP COLUMN commands in the Extract without stopping and restarting the Extract to get the new table metadata for the Extract. Full table version history is supported.

Support for TIMESTAMP (12)

Oracle GoldenGate for DB2 z/OS supports TIMESTAMP (12).

Support for TIME ZONE WITH TIMESTAMP

Oracle GoldenGate for DB2 z/OS now supports TIME ZONE WITH TIMESTAMP.

LRT support

LRT features SHOWTRANS, SKIPTRANS, FORCETRANS is supported.

DECFLOAT data type

DECFLOAT data type is supported.

Oracle GoldenGate can automatically map DECFLOAT to any other numeric datatype, however, it may have more precision or larger values. Ensure that target datatype can support the values that are being inserted into the source.

@COMPUTE on DECFLOAT can only be used on values that are 17 digits or fewer. You may need to use @STRNUM some cases.

MySQL

Two-way SSL Support in MySQL Capture and Delivery

Oracle GoldenGate for MySQL supports an SSL connection of Extract or Replicat to the MySQL database server.

MySQL 8.0 Support

Oracle GoldenGate supports MySQL 8.0.

Oracle

Opatches bundled with JDK for Oracle

The JDK is now packaged in the Oracle GoldenGate patch starting release version 21.10 and 19.1.0.0.230418. Please note that only <GG_HOME>/jdk directory is updated with the GoldenGate patch releases. See Doc ID 2359395.1 for more information and additional references for updating JDK either through GoldenGate patch or independently.

You still have the option to install the latest JDK patch into <GG_HOME>/jdk directory independently at any time. See Doc ID 1492980.1 for more information.

PostgreSQL

Parameter TransactionErrorBehavior must be set to 2 while specifying data

For PostgreSQL Replicat to be able to handle the database error conditions appropriately the value of the parameter TransactionErrorBehavior must be set to 2. It is a required parameter for Oracle GoldenGate, which needs to be specified inside the odbc.ini file under the DSN. Following is a sample DSN specifying the parameter:

[ODBC]
IANAAppCodePage=4
InstallDir=/u01/ggate

[pgdsn]
Driver=/u01/ggate/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=mypgdb
HostName=mypghost
PortNumber=5432
LogonID=mypguser
Password=**********
TransactionErrorBehavior=2

To know more, see https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/transaction-error-behavior.html

PostgreSQL

DEFERAPPLYINTERVAL not working as expected for PostgreSQL Replicat

If the time zone is not same for the database and operating system, then Replicat waits until the local GMT time of the source system is greater than record commit timestamp + DEFERAPPLYINTERVAL delay.

Replicat is unable to identify the checkpoint table when created in mixed case after Replicat restart

The checkpointtable name must be in lower case without quote and without any special character in it.

Oracle GoldenGate Configuration Assistant doesn't run if there is a space in the Oracle GoldenGate home directory name

Oracle GoldenGate Configuration Assistant (OGGCA) on Windows does not run if the Oracle GoldenGate home directory name or OGG_HOME has space in it, for example OGG HOME.

INFO HEARTBEATTABLE

The INFO HEARTBEATTABLE command for SQL Server now reports heartbeat frequency interval, purge frequency interval, and retention time.

TRANLOGOPTION QUERYTIMEOUT default value

For SQL Server, the default QUERYTIMEOUT has been extended from 30 seconds to 300 seconds.

Adding Validation After DDL Change

Extract for SQL Server will abend if it detects a difference in table structure between the source table and the Change Data Capture table.

TLS 1.2 Connection Support

For SQL Server, TLS 1.2 connection support, if enabled, is provided automatically by the Extract and Replicat.

Prerequisites for Using Oracle GoldenGate 19c with MySQL

Oracle GoldenGate for MySQL has a dependency on OpenSSL library, so availability of OpenSSL library is a prerequisite.

Linux: Set LD_LIBRARY_PATH to include the path where OpenSSL library is installed.

Windows: For MySQL 8.0 and higher, the OpenSSL library is shipped with MySQL server and is kept in the \bin directory of the MySQL home directory. You must ensure that the location of the OpenSSL is added to the path environment variable.

For example, D:\path\to\MySQLServer_install_dir\bin directory needs to be added to the PATH environment variable.

Alternatively, you can install the latest MySQL C++ connector (MySQL 8.0 or greater), which contains OpenSSL library and make sure that the location of the OpenSSL is added to the path environment variable. For example, C:\Program Files\MySQL\Connector C++ 8.0\lib64 directory needs to be added to the PATH environment variable.

With MySQL 5.7 or less, you need to install the latest MySQL C++ connector (MySQL 8.0 or greater), which contains the OpenSSL library and make sure that location of the OpenSSL is added to the PATH environment variable. For example: C:\Program Files\MySQL\Connector C++ 8.0\lib64 directory needs to be added to the PATH environment variable.

Oracle GoldenGate for MySQL 8.0 has a dependency on OpenSSL library so the availability of this library is a prerequisite. You need to ensure that the library is installed on the system running Oracle GoldenGate and path to this library is set appropriately.

No pattern restriction for Oracle GoldenGate user name but maximum length is 512 characters

Constraints for Oracle GoldenGate user name have been removed but the maximum length is 512 characters. For Basic and Certificate type users there are no constraints on special characters for the user name in REST API.

However, in the Microservice Architecture web interface, the user name can only include specific special characters for a Basic type user. These characters are:

at sign (@), period (.) , dash (-), comma (,), underscore (_), number sign (#), dollar sign ($), plus sign (+), backslash (\), slash (/), equal sign (=), less than sign (<), or greater than sign (>).

Microservices implementation requires stronger passwords

The passwords for users in Microservices implementation require stronger passwords, with a capital letter, a number, and a special character.

Trail File Format

After Extract update, if a newer trail file format is specified then the primary Extract will automatically rollover to the next trail file and writer in the specified trail file format. There is no need to run the ALTER EXTRACT ETROLLOVER command.

Extract Default Trail File Version

The primary Extract writes trail file in the same format as existing trail file format when you upgrade to Oracle GoldenGate 19c, unless you explicitly specify the trail file format version using the FORMAT RELEASE option. This behavior change prevents subsequent Replicat to abend if replicat is not upgraded.

_INFINITYTOZERO parameter is no longer required

The _INFINITYTOZERO parameter is no longer required. When Oracle GoldenGate detects the +INF or -INF values in an Oracle numeric field, it is handled automatically as if this parameter is already set.

If the target is Oracle Replicat 19c release, +INF, and -INF are applied properly. For all other databases including older Oracle Replicat, 0 value is applied.

Oracle TIMESTAMP WITH TIME ZONE capture

TIMESTAMP with TIMEZONE with region ID data is now supported for FETCH and initial load. For details, see TRANLOGOPTIONS in Reference for Oracle GoldenGate

This feature is enabled automatically if needed and you no longer need to specify TRANLOGOPTIONS INCLUDEREGIONID. For non-Oracle target, use TRANLOGOPTIONS INCLUDEREGIONIDWITHOFFSET to convert region ID to time offset from UTC.

GETCTASDML option default setting

GETCTASDML option available with TRANLOGOPTIONS parameter is enabled by default.

NOALLOWNULLABLEKEYS default setting

The default setting for the NOALLOWNULLABLEKEYS parameter is set to true.

This feature has been created to help customers easily identify tables that may be problematic due to multiple NULL values being allowed in a key column.

Change in Binlog Format Support

(MySQL only) Capture silently ignores the binlog events that are not written in the ROW format instead of abending when it detects a binlog_format other than ROW.