Goldengate Extract Fails to Start with OGG-00446 OGG-02829 after downgrading from Integrated to Classic Capture

 Problem:

On a RAC DB, after downgrading a goldengate extract group from Integrated to Classic capture and when Starting goldengate extract for the first time after the downgrade, it failed with the following error:

 2022-06-29T13:47:22.179+0000  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, extpa.prm:  �'Oo.
2022-06-29T13:47:22.187+0000  ERROR   OGG-02829  Oracle GoldenGate Capture for Oracle, extpa.prm:  Not able to establish initial position for SCN 1530.534313392 (6571834276272), Could not find archived log for sequence 0 thread 2 under default destinations SQL <SELECT  name   FROM v$archived_log   WHERE sequence# = :1 AND         thread# = :2 AND         resetlogs_id = :3 AND         archived = 'YES' AND         deleted = 'NO'         AND standby_dest = 'NO'         order by name DESC>, error retrieving redo file name for sequence 0, archived = 1, use_alternate = 0.
2022-06-29T13:47:22.187+0000  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, extpa.prm:  PROCESS ABENDING.

Analysis:

The error message shows that goldengate unable to access the log sequence for Thread2 which represents the second instance in my RAC setup, because it was down when starting the goldengate extract process.

Solution:

When upgrading or downgrading an extract from Integrated to Classic capture and vise versa on a RAC DB, make sure that all RAC instances in the cluster are in OPEN mode before starting the goldengate extract; in order to allow goldengate to access all threads when you start the goldengate extract group for the first time after the upgrade or the downgrade.

ORA-02097: parameter cannot be modified because specified value is invalid when modifying the parameter _fix_control

 Problem:

When modifying _fix_control parameter to enable optimizer related bug fixes on a 19.12 DB, I was getting this error:

Analysis:

I was trying to enable optimizer related bug fixes by adding the bug numbers to _fix_control, I came to know that some of these bug fixes I'm trying to enable are not exist in the DB.

The following query will show the bug fixes that are available on the DB out of the list in the failing command:

SQL> SELECT bugno,value,description FROM v$system_fix_control WHERE bugno IN ('18960760','29302565','30998035', '5705630','30786641' ,'28414968' ,'29867728' ,'29385774','27261477','31069997','31077481','30347410','28776431','29304314','29930457','30232638','29687220','28498976','29132869','28776811','28965084','25167306','22149010','28345522');

That clearly shows the available bug fixes, which I've to include only in my command, and discard the rest of bugs that are not found.

Solution:

Using only the available bug fixes in my command make it execute successfully:

SQL> alter system set "_fix_control"= '5705630:1','22149010:1','25167306:1','28345522:1','29687220:1','30232638:1','28498976:1','28965084:1','28776811:1','29132869:1','28776431:1','29304314:1','29930457:1','30347410:1','31069997:1','31077481:1','27261477:1','30998035:1','29302565:1','30786641:1','28414968:1','29867728:1','29385774:1' SCOPE=BOTH;

Verify the change:

SQL> SELECT bugno,value FROM v$system_fix_control WHERE bugno IN ('5705630','22149010','25167306','28345522','29687220','30232638','28498976','28965084','28776811','29132869','28776431','29304314','29930457','30347410','31069997','31077481','27261477','30998035','29302565','30786641','28414968','29867728','29385774');