2 Upgrading Oracle GoldenGate for Oracle Database

These instructions are for upgrading to Oracle GoldenGate for an Oracle database.

Topics:

Overview of the Upgrade Procedure for Oracle Database

The upgrade instructions perform a minimal upgrade that deploys only the updated core functionality, without implementing any of the new features, such as Integrated Replicat, updated globalization support, new SQL-92 defaults, and enhanced security features. It is best practice to perform a minimal upgrade first, so that you can troubleshoot more easily in the event that any problems arise. Once you know your environment is upgraded successfully, you can implement the new functionality.

The upgrade instructions also take into consideration the steps to take if you are upgrading the source or target database at the same time that you are upgrading Oracle GoldenGate. Taken into account are the following pre-upgrade requirements:

  • Allow the Oracle GoldenGate processes to finish processing all current DML and DDL data in the Oracle GoldenGate trails.

  • Disable the DDL trigger if there is no native DDL support.

  • When upgrading your database and Oracle GoldenGate simultaneously, you must upgrade the database first.

Understanding the Scope of the Upgrade Procedure

Before performing the upgrade, review the following information about upgrading Extract and Replicat. Even though you may only be upgrading the source or target, 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.

Oracle recommends that you begin your upgrade with the target rather than the source to avoid the necessity of adjusting the trail file format.

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 triggerless 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 Databaseto upgrade one Extract at a time.

If you are using integrated capture, you will need to roll over the local and remote trails at an appropriate point in the upgrade steps. The 11.2.1 integrated Extract (version 1) captures TIMESTAMP WITH LOCAL TIME ZONE data as UTC, but the 12c (12.3.0.1) integrated Extract (version 2) captures it as the local time of the database time zone.

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.

Understanding and Obtaining the Oracle GoldenGate Distribution

For complete information about how to obtain Oracle Fusion Middleware software, see Obtaining Product Distributions in Planning an Installation of Oracle Fusion Middleware.

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

To obtain Oracle GoldenGate, follow these steps:

  1. Go to the Oracle Technology Network: http://www.oracle.com/technetwork/middleware/fusion-middleware/downloads/index.html
  2. Find the Oracle GoldenGate 18c (18.1.0) release that you want to install.
  3. Download the ZIP file appropriate for your system.

Upgrading a Configuration That Includes DDL Support

This section contains considerations and steps you should take when DDL support is active in the current Oracle GoldenGate environment. DDL support in Oracle GoldenGate 18c (18.1.0.0) offers two options:

  • The Integrated mode supports two DDL capture methods:

    • If the source database is Oracle 11.2.0.4 or later, DDL capture support is integrated into the database logmining server and does not require the use of the Oracle GoldenGate DDL trigger and supporting objects, as long as the database COMPATIBLE parameter is set to 11.2.0.4 or higher.

    • If the source database is earlier than Oracle 11.2.0.4, the Oracle GoldenGate trigger and supporting DDL objects must be used when Extract is in integrated mode.

  • Classic capture requires the use of the Oracle GoldenGate DDL trigger and supporting objects regardless of the release number of the source Oracle database.

Table 2-1 shows possible DDL upgrade paths and guidelines.

Table 2-1 Possible Upgrade Paths to Oracle GoldenGate and Requirements for DDL Support

Upgrade from: To: Classic capture using trigger method To: Integrated capture, no trigger(1)

Classic capture using trigger method (all 11.2.1 database versions)

Cannot be used for a container database.

Upgrade Oracle GoldenGate per these upgrade instructions.

Can be used for a container database.

  1. Source database must be 11.2.0.4 or higher.

  2. Source database COMPATIBLE setting must be 11.2.0.4 or higher.

  3. Upgrade Oracle GoldenGate per these upgrade instructions.

Integrated capture using trigger method (all 11.2.1 database versions)

Cannot be used for a container database.

No DDL upgrade path.

Can be used for a container database.

  1. Source database must be 11.2.0.4 or higher.

  2. Source database COMPATIBLE setting must be 11.2.0.4 or higher.

  3. Upgrade Oracle GoldenGate per these upgrade instructions.

Footnote 1 An upgrade of the database to 11.2.0.4 or 12.1 automatically takes a data dictionary snapshot in the redo stream as part of the patch set upgrade.

Upgrading a Configuration That Includes Oracle Berkeley DB

When you are upgrading Oracle GoldenGate from release 12.1.2.1 to 12.3.0.1 and have enabled monitoring and the datastore is created by the Performance Metrics server, the best practice is to purge the data store before performing the upgrade. After the upgrade, the datastore is recreated. For more information about purging a datastore, see How to Purge the Datastore. in the Using the Oracle GoldenGate Microservices Architecture.

From Oracle GoldenGate 12.3.0.1 onward, all operations related to the datastore have been removed and are taken care of by the Performance Metrics server. To know more, see Monitoring Performance.

Upgrading Oracle GoldenGate Classic Architecture for Oracle Database

These instructions contain the procedure for performing the minimal upgrade.

Before proceeding, make certain 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 is advanced to 11.2.0.4 (or higher). For example, if Extract is behind by a week when the database is upgraded to 11.2.0.4, Extract does not immediately switch to native DDL capture. It must be allowed to process the pre-11.2.0.4 redo first, then Extract upgrades to native DDL capture automatically.

  1. (Source system) The new Extract might need to start processing from the 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. 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

    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.

  2. (Source system) Stop all user activity that generates both DML and DDL on objects in your Oracle GoldenGate configuration. 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.

    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.

  3. Record the current SCN from the query result.

  4. (Source system, if currently using classic Extract) In GGSCI, issue the SEND EXTRACT command with the LOGEND option until it shows there is no more redo data to capture.

    (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

  5. (Source system) In GGSCI, stop Extract and data pumps.

    GGSCI> STOP EXTRACT group
    
  6. (Source system if not upgrading Target) If you are not upgrading Replicat on the target systems at this time, add the following parameter to the Extract parameter file to specify the version of Oracle GoldenGate that is running on the target. This parameter causes Extract to write a version of the trail that is compatible with the older version of Replicat. Before making the changes to the extract parameter file, please take a backup of the Oracle GoldenGate parameter files on source

    {EXTTRAIL | RMTTRAIL} file_name FORMAT RELEASE major.minor
    
  7. (Source system, only if currently using trigger-based DDL capture support) Follow these steps:

    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.

  8. (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
  9. (Target systems) In GGSCI, stop all Replicat processes.

    GGSCI> STOP REPLICAT group
  10. (Source and target systems) In GGSCI, stop Manager on the source and target systems.

    GGSCI> STOP MANAGER
  11. 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.

  12. (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). You do not need to backup up the dirdat folder because the trail files in this directory can be recreated.

  13. If you want to upgrade the source or target database, or both, do so at this time according to the Oracle database upgrade instructions for the new version. Ensure that you start and mount the databases after the upgrade though do not permit DML or DDL transactions on the objects in the Oracle GoldenGate configuration.

  14. (Source and target systems) If you are using an Oracle GoldenGate version prior to 11.2.1, grant write permission to the installation user on the existing Oracle GoldenGate directory.

    For example, on UNIX or Linux, run the following command:

     $ chmod -R u+w existing_Oracle_GoldenGate_directory
  15. (Source and target systems) Install Oracle GoldenGate 18c (18.1.0) using Oracle Universal Installer (OUI) into an existing Oracle GoldenGate directory on the source and target systems. Ensure that you deselect the Start Manager option during the OUI installation; otherwise, this may overwrite your existing parameter files. See Installing the Oracle GoldenGate Classic Architecture with Oracle Database in Installing Oracle GoldenGate for instructions.

    Note:

    Oracle recommends that you install with the interactive OUI option for upgrade purposes, rather than using the silent installation.

  16. (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]

    Note:

    owner.table can be omitted if the checkpoint table was created with the name listed with CHECKPOINTTABLE in the GLOBALS file.

  17. (Source system) On the source system, run the ulg.sql script as sysdba. This script converts the existing supplemental log groups to the new format required by the new release. This step is required even if you plan to use the new triggerless capture method. The script should run without error; if errors occur, contact Oracle Support.

  18. (Source and target systems) Updates to the trail version, the checkpoint storage format, and integrated capture require that you perform the following steps:

    1. In GGSCI, alter the primary Extract process and the associated data pump Extract processes to write to a new trail sequence number. The command should return Rollover performed.

      GGSCI> ALTER EXTRACT group ETROLLOVER
      GGSCI> ALTER EXTRACT primary-extract-group, ETROLLOVER  
      GGSCI> ALTER EXTRACT pump-extract-group, ETROLLOVER 
    2. In GGSCI, issue the INFO EXTRACT command with DETAIL for the primary Extract and the data pumps to verify the trail sequence number.

      GGSCI> INFO EXTRACT group, DETAIL
    3. In GGSCI, reposition the data pumps and the Replicat processes to start at the new trail sequence number.

      GGSCI> ALTER EXTRACT pump, EXTSEQNO seqno, EXTRBA RBA
      GGSCI> ALTER REPLICAT group, EXTSEQNO seqno, EXTRBA RBA
  19. (Source system) If you plan to use new trigger-based DDL support for Oracle Database, use the following sub-steps to rebuild the Oracle GoldenGate DDL trigger environment to a clean state:

    1. Run SQL*Plus and log in as a user that has sysdba privileges.

    2. Run the marker_setup script to reinstall the Oracle GoldenGate marker support system and provide the name of the Oracle GoldenGate schema.

    3. Run the ddl_setup script and provide the name of the Oracle GoldenGate DDL schema.

    4. Run the role_setup script to recreate the Oracle GoldenGate DDL role.

    5. Grant the role that you created to all Oracle GoldenGate users under which the following Oracle GoldenGate processes run: Extract, Replicat, GGSCI, and Manager. You may need to make multiple grants if the processes have different user names.

    6. Run the ddl_enable.sql script to enable the Oracle GoldenGate DDL trigger.

  20. You may now restart DDL and DML activity on the source database.

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

  22. 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 will abend with an error that indicates the log to restore. Restore the logs back to, and including that log, and then restart Extract.

Upgrading Oracle GoldenGate Microservices Architecture from 12c (12.3.0.1) to 18c (18.1.0)

Perform these steps to upgrade from the Oracle GoldenGate 12c to 18c release using the web interface.

To upgrade Oracle GoldenGate Microservices Architecture 12c (12.3.0.1) to 18c (18.1.0):
  1. Install Oracle GoldenGate Microservices Architecture 18c (18.1.0) in a new directory. For installation steps, see Installing the Microservices Architecture for Oracle GoldenGate in Installing Oracle GoldenGate .
  2. Log in to the Oracle GoldenGate Microservices Architecture 12c Service Manager:

    http://host:servicemanager_port.

    It could be http or https depending on the type of deployment.

  3. Access the Administration Server and stop all Extract and Replicat processes.
  4. Access the Distribution Server and stop all distribution server paths.
  5. Access the Service Manager and stop all deployment services. You can select the Stop option from the Action column of the Deployments table to stop the Service Manager and all the services.
  6. Click the deployment name from the Deployments table of the Service Manager home page.
  7. Edit and update the Oracle GoldenGate home directory.
  8. Go back to the Service Manager home page and use the Restart option in the Action column to restart the Service Manager.
  9. Repeat the steps from 2 to 8 for updating any other deployments to reference to the Oracle GoldenGate 18c (18.1.0) home directory.
  10. Start all the deployment services.
  11. Access the Distribution Server and start all the distribution paths.
  12. Access the Administration Server and start all Extract and Replicat processes.

Upgrading Microservices Architecture – GUI Based

Details the steps for upgrading Microservices architecture.

  1. Verify the current version of Oracle GoldenGate Home through Service Manager.
    1. Login to the Service Manager: http://host:servicemanager_port
    2. Review the deployment section for your current Oracle GoldenGate home location.
  2. Install the new Oracle GoldenGate for Microservices binaries next to the existing binaries.
  3. Update the Service Manager and the Deployments with the location of the new Oracle GoldenGate home.
    1. Click Service Manager, then the Deployment name link.
    2. Next to the deployment details, click the pencil icon. This opens the dialog box to edit the Oracle GoldenGate home.
    3. Update the Oracle GoldenGate home with the complete path to the new Oracle GoldenGate home.
    4. Click Apply.
    5. Confirm that the Oracle GoldenGate home has been updated.
    6. Use the action button to restart Service Manager or Deployment.

    Note:

    You can confirm that the Oracle GoldenGate home was updated by looking at the process from the operating system for Service Manager (ps –ef | grep –i servicemanager). The Service Manager process should be running from the correct Oracle GoldenGate home.
  4. To upgrade the associated deployments, follow the same steps for Service Manager after ensuring that all the Extract and Replicat processes in that deployment have been stopped.