Upgrading Oracle GoldenGate Classic Architecture for Oracle Database

These instructions contain the procedure for performing the minimal upgrade.

Before proceeding, make certain that you have reviewed the following preparation information:

Note:

If you are using integrated capture and plan to upgrade from trigger-based DDL capture to new native DDL capture, do not remove the DDL trigger until prompted. Extract needs to mine DDL to the point where the redo COMPATIBLE level. For example, if Extract is behind by a week when the database is upgraded, Extract does not immediately switch to native DDL capture. It must be allowed to process the previous redo first, then Extract upgrades to native DDL capture automatically.

Use the following command in GGSCI to determine the oldest archive log that you might need to restore when Extract starts. The Recovery Checkpoint field shows the oldest log needed for recovery.

GGSCI> INFO EXTRACT group, SHOWCH

It's best to perform upgrade activities outside of the peak hours. If there are large and long running transactions, consider that on the source system, the new Extract may need to start processing from normal recovery checkpoint, rather than the bounded recovery checkpoint, if the first record of the oldest open transaction at the time that you stop Extract is in a log that is not on the system.

The amount of time it takes to restart an Extract is directly related to the amount of uncomitted data at the time the Extract was stopped.

You have two options:
- You can restore the archives back to, and including, the one shown in the recovery checkpoint shown with INFO EXTRACT.
- You can clear out the long-running transactions that apply to the Extract that you are upgrading. This can be done by skipping the transactions or by forcing them to the trail as committed transactions. Skipping a transaction may cause data loss, and forcing a transaction to the trail may add unwanted data to the trail if the transaction is rolled back. To skip or force a transaction, follow these steps:
  1. View open transactions with the following command in GGSCI. Record the transaction ID of any transaction that you want to clean up.
```
GGSCI> SEND EXTRACT group, SHOWTRANS
```
  2. Clean up old transactions with the SEND EXTRACT command, using either the SKIPTRANS option to skip a transaction or the FORCETRANS option to force a transaction in its current state to the trail as a committed transaction.
```
GGSCI> SEND EXTRACT group, {SKIPTRANS | FORCETRANS} transaction_ID [THREAD n] [FORCE]
```
  3. After you are finished cleaning up the long-running transactions, force a Bounded Recovery checkpoint.
```
GGSCI> SEND EXTRACT group, BR BRCHECKPOINT IMMEDIATE
```
    Note:
    A forced checkpoint is necessary because the skipped or forced transaction is not cleaned up from the Bounded Recovery checkpoint and will be shown if SHOWTRANS is issued again. This is a known issue. For more information about SEND EXTRACT, see Reference for Oracle GoldenGate.
(Classic Extract in Oracle only) If you are using classic Extract, then after the DML and DDL have been stopped, run the following query. This query provides the minimum SCN needed to ensure all transactions are accounted for. Query the oldest SCN of any open transaction. If there are no open transactions, the current SCN of the database can be used.
```
SELECT MIN(SCN) AS INSTANTIATION_SCN
 FROM (SELECT MIN(START_SCN) AS SCN
         FROM gv$transaction
         UNION ALL
         SELECT CURRENT_SCN
         FROM gv$database);
```
Note:
You can avoid stopping DML operations when performing the Oracle GoldenGate upgrade, but you should stop DML when there is a requirement to run any SQL, such as DDL or any other upgrade SQL scripts.

The result of the query is:

YES = reached the logend
(Classic Extract in Oracle only) Record the SCN from the query result.
(Classic Extract for Source system in Oracle only) Issue the SEND EXTRACT command with the LOGEND option until it shows there is no more redo data to capture. You can skip this step, if you are using an integrated Extract.

(Source system if using integrated Extract) Wait for the Extract recovery checkpoint to progress past the current SCN from the query result. To determine whether Extract is past that SCN in its checkpoints, view the Extract report file.

GGSCI> SEND EXTRACT group LOGEND
(Source system) In GGSCI, stop Extract and data pumps.
```
GGSCI> STOP EXTRACT group
```
If you are using integrated Extract, then you can skip this step. If you are using classic Extract, then follow these steps on the source system, only if currently using trigger-based DDL capture support:
1. Run SQL*Plus and log in as a user that has sysdba privileges.
2. Disconnect all sessions that ever issued DDL to avoid an error (ORA-04021). Ensure that no DDL sessions can be started for the duration of this upgrade.
3. From the Oracle GoldenGate directory, run the ddl_disable script to disable the Oracle GoldenGate DDL trigger.
4. Run the ddl_remove script to remove the Oracle GoldenGate DDL trigger and other associated objects and provide the name of the Oracle GoldenGate DDL schema.
  
  A ddl_remove_spool.txt log file is generated that logs the script output and a ddl_remove_set.txt file that logs current user environment settings for use in debugging.
5. Run the marker_remove script to remove the Oracle GoldenGate marker support system and provide the name of the Oracle GoldenGate DDL schema.
  
  A marker_remove_spool.txt file is generated that logs the script output and a marker_remove_set.txt file for use in debugging.
(Target systems) In GGSCI, stop all Replicat processes.
```
GGSCI> STOP REPLICAT group
```
(Source and target systems) In GGSCI, stop Manager on the source and target systems.
```
GGSCI> STOP MANAGER
```
When updating target systems only, or if updating the target side before the source side, you must use STOP to stop all data pumps and any primary Extracts that write directly to those targets on any source running on this target. Any static collectors that may have been started that must be stopped, as well. To verify that there are no server processes running, use process checking shell commands, such as ps and grep.
You need to use an out-of-place upgrade, which implies that you retain the existing installation in parallel while you run the upgrade. See Installing the Oracle GoldenGate Classic Architecture with Oracle Database and Patching for Classic Architecturein Installing Oracle GoldenGate for details.
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
```
If you need to restore any log files, Extract abends with an error that indicates the log to restore. Restore the logs back to, and including that log, and then restart Extract.

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 then rename them to the same names as the old parameter files. If you are using case-sensitivity support, ensure that you either add 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 Considerations for older Oracle GoldenGate Releases

(Target systems, if upgrading Replicat from version 11.2.1.0.0 or earlier) 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 to add columns that support the Oracle GoldenGate 18c (18.1.0) release.
```
GGSCI> DBLOGIN [{SOURCEDB} data_source] | 
[, database@host:port] | {USERID {/ | user id}
[, PASSWORD password]   [algorithm ENCRYPTKEY {keyname | DEFAULT}] |USERIDALIAS alias 
[DOMAIN domain]}
GGSCI> UPGRADE CHECKPOINTTABLE [owner.table]
```
owner.table can be omitted if the checkpoint table was created with the name listed with CHECKPOINTTABLE in the GLOBALS file.
(Source system) On the source system, if ADD TRADATA is used to add supplemental logging to a table then run the ulg.sql script as sysdba. This script converts the existing table-level supplemental log groups to the new format required by the new release. The script should run without error however, if errors occur, contact Oracle Support. This is not required if using ADD SCHEMATRANDATA.

Parent topic: Upgrading Oracle GoldenGate for Oracle Database