Upgrading Oracle GoldenGate for Heterogeneous Databases

These instructions are for upgrading Oracle GoldenGate Classic Architecture in the supported heterogeneous database environments.

Topics:

Parent topic: Upgrading Oracle GoldenGate Classic

Obtaining the Oracle GoldenGate Distribution

To obtain Oracle GoldenGate, follow these steps:

Go to edelivery: edelivery.oracle.com
Also see MOS note 1645495.1 and 2193391.1 for more information.

To access Oracle Technology Network, go to https://www.oracle.com/middleware/technologies/goldengate.html
Find the Oracle GoldenGate 21c release and download the ZIP file onto your system.

For more information about locating and downloading Oracle Fusion Middleware products, see the Oracle Fusion Middleware Download, Installation, and Configuration Readme Files on Oracle Technology Network.

Parent topic: Upgrading Oracle GoldenGate for Heterogeneous Databases

Overview of the Upgrade Procedure for Heterogeneous Databases

The upgrade performs a minimal feature upgrade to deploy only the core Oracle GoldenGate functionality, without implementing any of the major new features. It ensures easy troubleshooting of any upgrade related issues that may occur after the upgrade. After upgrading the Oracle GoldenGate environment successfully, you can implement the new functionality.

If you are upgrading multiple Extract processes that operate in a consolidated configuration (many sources to one target), you must upgrade one Extract at a time. All Replicat installations must be upgraded at the same time. It is critical to ensure that all trails leading to all Replicat groups on all target systems are processed until empty.

Note:

The hash calculation used by the @RANGE function to partition data among Replicat processes has been changed. This change is transparent, and no re-partitioning of rows in the parameter files is required. To ensure data continuity, ensure that you allow all Replicat processes on all systems to finish processing all the data in their trails before stopping those processes. If the Replicat processes are not upgraded all at the same time, or the trails are not cleaned out prior to the upgrade, rows may shift partitions as a result of the new hash method, which may result in collision errors.

Parent topic: Upgrading Oracle GoldenGate for Heterogeneous Databases

Upgrading Oracle GoldenGate Classic Architecture for Heterogeneous Databases

Even though you may only be upgrading Extract or Replicat, rather than both, all processes are involved in the upgrade. All processes must be stopped in the correct order for the upgrade, regardless of which component you upgrade, and the trails must be processed until empty.

(Source and target systems) Back up the current Oracle GoldenGate installation directory on the source and target systems, and any working directories that you have installed on a shared drive in a cluster (if applicable).
(Source and target systems, as applicable) Expand the Oracle GoldenGate upgrade build into a new directory on each system (not the current Oracle GoldenGate directory). Do not create the sub-directories; just complete the steps to the point where the installation files are expanded.

However, this step doesn't apply to PostgreSQL.

Oracle GoldenGate for PostgreSQL upgrade only works if you install the latest version (version to be upgraded) in the same $OGG_HOME directory as the current Oracle GoldenGate version.

For PostgreSQL, Oracle GoldenGate upgrade doesn't work if you install the latest version (version to be upgraded) in a different $OGG_HOME directory (21.3) and repoint the new $OGG_HOME to the latest version.

For PostgreSQL, use the same $OGG_HOME as current version directory for the latest Oracle GoldenGate binary. Make sure to take backup of existing $OGG_HOME before beginning the upgrade.
Stop all user activity that generates DML and DDL on objects in your Oracle GoldenGate configuration and ensure that there are no outstanding open transactions against the database.

For SQL Server CDC Extract on a Source system, manually stop the CDC Capture job for the database.
(Source system) In GGSCI on the source system, issue the SEND EXTRACT command with the LOGEND option until it shows YES, indicating that there is no more data in the transaction log to process.

For SQL Server CDC Extract on Source system, monitor that the current read position of the Extract is no longer updating, by repeatedly running SEND EXTRACT group STATUS for a few seconds, and observe that the LSN value for the current read position is no longer updating.

GGSCI> SEND EXTRACT group LOGEND
(Source system) In GGSCI, stop Extract and data pumps.

GGSCI> STOP EXTRACTgroup
(Target systems) In GGSCI on each target system, issue the SEND REPLICAT command with the STATUS option until it shows a status of "At EOF" to indicate that it finished processing all of the data in the trail. This must be done on all target systems until all Replicat processes return "At EOF."

GGSCI> SEND REPLICAT group STATUS
(Target systems) In GGSCI, stop all Replicat processes.

GGSCI> STOP REPLICATgroup
(Source and target systems) In GGSCI, stop Manager on the source and target systems and close GGSCI.

GGSCI> STOP MANAGER
If you want to upgrade the source or target database, or both, do so at this time according to the upgrade instructions provided for that database. Ensure that you start the databases after the upgrade, but do not permit transactions on the objects in the Oracle GoldenGate configuration.
For MySQL, if you upgrade from Oracle GoldenGate 19c (19.1.0) and the database is MySQL 5.7, then no change is required. However, if you upgrade from Oracle GoldenGate 19c (19.1.0) and the database is MySQL 8.0, then you need to perform the following steps:
1. Enable logging of full metadata because it's mandatory for MySQL 8.0 and higher, regardless of DDL or DML replication. Logging of full metadata can be enabled by setting the value of MySQL server variable binlog_row_metadata to FULL inside the MySQL configuration file (my.cnf for Linux and my.ini for Windows). You need to restart the database service after changing the configuration file for the settings to take effect.
2. Run the DDL uninstall scripts to disable old DDL solutions if DDL replication was enabled previously.
  
  The script name:
  
  For Windows: ddl_install.bat
  
  For Linux: ddl_install.sh
3. To uninstall, run the following script:
  
  bash$ ./ddl_install.sh uninstall mysql userid password port
(Source and target systems) Move the expanded Oracle GoldenGate files from the new directory to your existing Oracle GoldenGate directory on the source and target systems overwriting the existing files.
(DB2 for i) Run ggos400install without arguments. For an upgrade, no arguments are necessary. However, if you change the library, the old library is left on the system until you remove it.
(Source and target systems) Start GGSCI.
(Target systems, if upgrading Replicat from version 11.2.1.0.0 or earlier only) In GGSCI on each target system, issue the following commands to upgrade the Replicat checkpoint tables on those systems. This step updates the table definition.
```
GGSCI> DBLOGIN {
        [SOURCEDB data_source] |
        [, database@host:port] |USERID {/ | userid}
        [, PASSWORD password]   
        [algorithm ENCRYPTKEY {keyname | DEFAULT}] |USERIDALIAS alias [DOMAIN domain] |
        [SYSDBA | SQLID sqlid]
        [SESSIONCHARSET character_set]}
```
GGSCI> UPGRADE CHECKPOINTTABLE [owner.table]

Note:

owner.table can be omitted if the checkpoint table was created with the name listed with CHECKPOINTTABLE in the GLOBALS file.
(SQL Server Oracle GoldenGate classic Extract 12c (12.3.0.1) or prior, on Source system) Run the DELETE TRANDATA command against any tables enabled with it and delete the heartbeat tables if they exist (DELETE HEARTBEATTABLE). Then run ADD TRANDATA again for the tables and ADD HEARTBEATTABLE, if previously used.
```
GGSCI> DBLOGIN {[SOURCEDB data_source] | |USERID {/ | userid}[, PASSWORD password] |USERIDALIAS alias [DOMAIN domain]
GGSCI> DELETE TRANDATA schema.tablename
GGSCI> DELETE HEARTBEATTABLE 
GGSCI> ADD HEARTBEATTABLE 
GGSCI> ADD TRANDATA schema.tablename
```
(SQL Server CDC Extract on Source system) Run ADD TRANDATA again on any tables previously enabled with it, including any filter table and the gg_heartbeat and gg_heartbeat_seed tables if using the Oracle GoldenGate heartbeat implementation.
```
GGSCI> DBLOGIN {[SOURCEDB data_source] | |USERID {/ | userid}[, PASSWORD password] |USERIDALIAS alias [DOMAIN domain]
GGSCI> ADD TRANDATA schema.tablename
```
(Target system) If upgrading the target Oracle GoldenGate installation that is the recipient of trails from a source system running Oracle GoldenGate prior to version 11.2.1, then add the SOURCECHARSET parameter to the Replicat and specify the character set of the source database.
(Source system) By default, after upgrading, the Extract will continue to write trail files in the version of Oracle GoldenGate prior to the upgrade. To force the Extract to write in the upgraded trail version, use the FORMAT RELEASE parameter in the Extract, specifying the new version, or alternately, perform an ETROLLOVER of the Extract and manually reposition the downstream processes to start reading at the new trail sequence.
```
{EXTTRAIL | RMTTRAIL} file_name FORMAT RELEASE major.minor
```
If you made copies of the parameter files to make parameter changes, move the new parameter files into the Oracle GoldenGate directory where the old parameter files were stored, and give them the same names as the old parameter files. If using case-sensitivity support, make certain that you either added NOUSEANSISQLQUOTES to your parameter files, or that you ran the convprm utility to convert the quotes as required.

See "Upgrade Considerations if Using Character-Set Conversion" for more information.
Upgrade the heartbeat table configuration if it was previously implemented, before restarting all the processes.
```
GGSCI> DBLOGIN {[SOURCEDB data_source] | |USERID {/ | userid}[, PASSWORD password]
|USERIDALIAS alias [DOMAIN domain]
GGSCI> UPGRADE HEARTBEATTABLE
```
You also need to modify the BATCHSQL parameter to double the value of BATCHESPERQUEUE. You must do this before starting Replicat.

Note:
If you are upgrading from Oracle GoldenGate version 12.1 to any later version and using the INSERTALLRECORDS parameter, it is recommended that you should double the value of BYTESPEERQUEUE option of the BATCHSQL parameter. For example, if you are using the BYTESPEERQUEUE option with its default value, which is 20 MB, then increase the value to 40 MB. However, if you are not using the default value for the BYTESPEERQUEUE option, then double the value specified during the Oracle GoldenGate version 12.1 installation.
For example:
```
BATCHSQL BATCHESPERQUEUE 40000000
```
For SQL Server CDC Extract on a Source system, manually restart the CDC Capture job for the database.
In GGSCI, start the Oracle GoldenGate processes on the source and target systems in the following order.
```
GGSCI> START MANAGER
GGSCI> START EXTRACT group
GGSCI> START EXTRACT pump
GGSCI> START REPLICAT group
```

Parent topic: Upgrading Oracle GoldenGate for Heterogeneous Databases