Oracle GoldenGate 21c -- Additional Prepartion of an Oracle Database 11g for Data Capture

Problem

You need to setup data capture (Extract) from an Oracle Database 11g. Given that the database version is considerable old compared to the most recent GolgenGate 21c release, extra preparation is required

Solution

1) Ensure at least 11.2.0.4.200714 PSU or any more recent available PSU

Download Patch 31103343 - Oracle Database Patch Set Update 11.2.0.4.200714

OPatch utility version 11.2.0.3.23 or later is required to apply this PSU, check Oracle Database -- How to Upgrade OPatch Utility for more information 

Shutdown database

Install the PSU:

$ cd /tmp
$ mkdir p31103343
$ cd p31103343/
$ unzip -qqq <path_to_PSU_archive>
$ cd 31103343
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

In case any conflicts detected, consult:

• Database Patch Conflict Resolution (Doc ID 1321267.1)

Conflicting patches may be deinstalled with:

• $ $ORACLE_HOME/OPatch/opatch rollback -id <patch_number>

Install PSU with:

$ $ORACLE_HOME/OPatch/opatch apply

Load modified SQL files into database:

$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus / as sysdba
SQL> startup
SQL> @catbundle.sql psu apply
SQL> @utlrp.sql

For information see the README files of the PSU

2) Starting with GoldenGate 21c release, the classic architecture is deprecated, and only the microservices architecture is supported. 

With the microservices architecture, when you try start an Extract on an Oracle Database 11g version, you will receive errors similar to:

Cannot register or unregister EXTRACT because of the following SQL error: OCI Error ORA (status = 1031-ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_XSTREAM_GG_INTERNAL", line 258 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 93 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 124 ORA-06512: at line 1 )

To solve this problem, Patch 20448066 is required

Download Patch 20448066

Shutdown the database

Install Patch 20448066

$ mkdir p20448066
$ cd p20448066
$ unzip -qqq <path_to_patch_zip_file>
$ cd 20448066
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ $ORACLE_HOME/OPatch/opatch apply

For more details check:

• Insufficient Privileges While Setting Up Integrated Extract on OGG 12.3 Microservices Architecture (Doc ID 2348973.1)

3) Enable support for trail files of GG 12.2 version and higher

Extract of version 12.2 and higher will fail on an Oracle Database 11g with error:

OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later

There are two options to solve this problem

Option 1. Install Patch 17030189

Download Patch 17030189 to a directory of your choice

Shutdown database

Install patch:

$ mkdir p17030189
$ cd p17030189
$ unzip -qqq <path_patch_zip_file>
$ cd 17030189
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ $ORACLE_HOME/OPatch/opatch apply

Perform post-installation steps:

SQL> @?/sqlpatch/17030189/postinstall.sql 

Option 2. Apply workround

As user sys, run script:

SQL> $OGG_HOME/lib/sql/legacy/prvtlmpg.plb

For more information:

• OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later (Doc ID 2304095.1)
• Bug 17030189 - Logminer GG Dictionary Support: Missing Attributes (Doc ID 17030189.8)
• EXTRACT Abending With OGG-02912 (Doc ID 2091679.1)

4) Install recommended/required GoldenGate Bundle patches. Using article:

• Oracle GoldenGate -- Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)

identify recommended patches.

For the PSU 11.2.0.4.200714 used in step 1) above, the recommend GoldenGate Bundle Patch is: 

• Patch 31625980 - MERGE REQUEST ON TOP OF DATABASE PSU 11.2.0.4.200714 FOR BUGS 31489460 31503302

Download Patch 31625980 to a directory of your choice

Shutdown the database

Install the patch

$ cd /tmp
$ mkdir p31625980
$ cd p31625980
$ unzip -qqq <path_patch_zip_file>
$ cd 31625980
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ $ORACLE_HOME/OPatch/opatch apply 

Perform post-installation steps:

$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus / as sysdba
SQL> startup
SQL> @?/sqlpatch/31625980/postinstall.sql
SQL> @?/rdbms/admin/utlrp.sql