ORACLE GOLDENGATE UPGRADE

(Based on Oracle GG docs 19c)

Prerequisites:

Before performing the upgrade procedure, read the Release Notes for Oracle GoldenGate to determine whether the new release affects the following in your configuration:

New default process behavior.

Parameters that changed or were deprecated.

Parameters that were added to support a desired new feature or database type.

Parameter default values that have changed.

New data type support that might require changes to TABLE or MAP statements.

Interaction with native database components that might require database change.

You can prevent startup delays that can cause lag by having all of your parameter changes made ahead of time, so that they are ready when you restart the processes. You should not make parameter changes while a process is running, but you can:

Make a copy of the parameter file.

Make edits to the copy.

After you shut down the processes during the upgrade procedure, copy the old parameter file to a new name (to save it as backup).

Copy the new parameter file to the old parameter file's name.

Upgrade Considerations if Using Quoted Object Names

Oracle GoldenGate treats strings that are within single quotes as literals. Oracle GoldenGate has supported double-quoted object names since release 11.2 but did not fully implement the rule of single quotes for literals until release 12.1. Supporting double quotes for object names and single quotes for literals brings Oracle GoldenGate into compliance with SQL-92 rules and is now enabled by default. The USEANSISQLQUOTES parameter, which forced the SQL-92 standard in previous releases, is now deprecated.

The change to default SQL-92 rules affects object names in parameter files, input to SQLEXEC clauses, OBEY files, conversion functions, user exits, and commands. You have the following options as a result of this change:

Retain non-SQL-92 quote rules: Oracle GoldenGate retains backward compatibility to enable the retention of current parameter files that do not conform to SQL-92 rules. To retain non-SQL-92 rules, add the NOUSEANSISQLQUOTES parameter to the GLOBALS file before you perform the upgrade and retain that parameter going forward. NOUSEANSISQLQUOTES affects Extract, Replicat, DEFGEN, and GGSCI.

Upgrade your parameter files to use SQL-92 rules: Oracle GoldenGate provides the convprm conversion tool which you can run to convert your parameter files to be in conformance with SQL-92 rules. Run the convprm tool before you start the upgrade process.

Overview of the convprm Tool

The following describes the convprm tool:

It is a command line program which can be run either manually or scripted.

It converts string literals from double-quoted character strings to single-quoted character strings, but leaves double-quoted object names intact. It can distinguish between an object name and a string literal even when both are represented as a sequence of characters delimited with double quotes.

It escapes quotation marks. Quotation marks must be escaped when the character that is used to delimit the string appears in the literal string itself. For example, the sentence "This character "" is a double quote" contains an escaped quote mark. The sentence 'This character '' is a single quote' contains an escaped single quote mark. When converting from double quotes to single quotes, convprm removes one of the repeated double quotes from escaped double quotes and escapes the single quotes that are embedded in the character sequence.

It issues a warning message if NOUSEANSISQLQUOTES is specified in the GLOBALS file. The message states that the converted parameter file is incompatible with NOUSEANSISQLQUOTES, but the parameter file was updated anyway.

It recursively converts the files that are included through an OBEY or INCLUDE parameter.

It creates a backup of the initial parameter file in the same directory as the original file. The backup has the name of the original file with the .bck suffix. The creation of a backup file can be disabled when you run the convprm tool.

It converts the character set. The character set for the new parameter file is taken from the CHARSET parameter in the original parameter file. Absent that parameter, the character set is taken from the CHARSET parameter in the GLOBALS file. Absent a GLOBALS parameter, the new parameter file is written in the character set of the local operating system

That tool usage

Options

[ -i  ] means recursively converts files included thru an ‘obey’ or ‘include’ parameter

[ -n ] does NOT create a backup for the file

[ -s ] does NOT display status messages

[ -q ] performs quotes conversion. This is default behavior. 

[-d | --dry-run] does not change the parameter file or create a backup file. It only prints out what would happen as the result of the conversion.

input_files is a list of the parameter files that are to be converted. Separate each file name with a white space, for example:

1.   run this command command example:

    convprm [options] extfinextacctexthr

 2.   Examine the parameter file to make certain the conversion         completed successfully. Status messages are displayed at the beginning, during, or at the end of the file conversion process.On errors, the process abends in the same way as other Oracle GoldenGate executables. All error messages that cause the converter to fail are sent to the Oracle GoldenGate error log.

Extract Upgrade Considerations:

If you are using trigger based DDL support, you must rebuild the DDL objects, even if you plan to use the new trigger less DDL support in an integrated capture. After the upgrade, when Oracle GoldenGate is running successfully again, you can remove the trigger and DDL objects. See Upgrading a Configuration That Includes DDL Support for DDL upgrade considerations.

If you are upgrading multiple Extract processes that operate in a consolidated configuration (many sources to one target), follow the steps provided in Upgrading Oracle GoldenGate Classic Architecture for Oracle Database to upgrade one Extract at a time.

The output trail file is automatically rolled over when the Extract restarts and the integrated Extract version 1 is upgraded to version 2.

Because the time zone is different, you may need to run the ALTER REPLICAT extseqno command to synchronize with newer trail files after consuming the old trail file written by Integrated Extract version 1.

After completing the upgrade, run the UPGRADE HEARTBEATTABLE command to add extra columns for tables and lag views. These extra columns are used to track the Extract restart position. See UPGRADE HEARTBEATTABLE to know more.

HEARTBEATTABLE is the table for monitoring the status of Goldengate replication. 

Replicat Upgrade Considerations:

All Replicat installations should 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, according to the upgrade instructions.

Caution:

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, so long as the upgrade is performed as directed in these instructions. To ensure data continuity, make certain to allow all Replicat processes on all systems to finish processing all of the data in their trails before stopping those processes, according to the upgrade instructions. Note that 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.

If the trail file format is pre-12.2, then SOURCEDEF is still required because no metadata exists in the trail file.

Oracle Goldengate can also be upgraded using Oracle Installer in SILENT Mode

The following command can be used for silent upgrade using response file.

runInstaller -silent -nowait -responseFile

Note:

WARNING:OUI-10030:You have specified a non-empty directory to install this product. It is recommended to specify either an empty or a non-existent directory.

You may, however, choose to ignore this message if the directory contains Operating System generated files or subdirectories like lost+found. Do you want to proceed with installation in this Oracle Home?

DDL Support

Possible Upgrade Paths to Oracle GoldenGate and Requirements for DDL Support