Thursday, April 14, 2022

Extract USERID or TRANLOGOPTIONS MININGUSER GGOWNER does not match the logmining server connect user SYSTEM

Problem:

 After starting an already upgraded extract from Classic Capture to Integrated Capture, the extract process ABENDED with the following error:

Extract USERID or TRANLOGOPTIONS MININGUSER GGOWNER does not match the logmining server connect user SYSTEM.


Analysis:

When connected to GGSCI to upgrade the extract process from Classic to Integrated capture, I mistakenly connected with SYSTEM user instead of connecting to goldengate schema owner "GGOWNER" the thing registered the extract group with SYSTEM user to the Logminer.

GGSCI> dblogin userid system@orcl password xxxx


Solution:

Login to the DB from inside GGSCI console to the right Goldengate schema userid, then convert back from Integrated to Classic Capture, then upgrade again from Classic to Integrated Capture:

In the following example; the extract group name is extpa

1- Login to Goldengate console with the Goldengate schema owner:

GGSCI> dblogin userid ggowner@orcl password xxxx

Successfully logged into database.

2- Downgrade back the extract group from Integrated to Classic Capture:

GGSCI> stop extpa

Sending STOP request to EXTRACT EXTPA ...
Request processed.

GGSCI> info extpa downgrade

Extract EXTPA is ready to be downgraded from integrated capture. The THREADS option with a value of 2 or greater will be required. Archive logs corresponding SCN 6571803644792 and higher need to be accessible by the downgraded extract.

GGSCI> alter extpa downgrade integrated tranlog threads 2

Extract EXTPA successfully downgraded from integrated capture.

Note: Because my DB is 2 nodes RAC, I used "threads 2" if your DB is a standalone you can omit that option.

GGSCI> unregister extract extpa database

2022-04-13 11:57:06  INFO    OGG-01750  Successfully unregistered EXTRACT EXTPA from database.
 

GGSCI> start extpa

Sending START request to MANAGER ...
EXTRACT EXTPA starting

GGSCI> info extpa

EXTRACT    EXTPA     Last Started 2022-04-13 11:57   Status RUNNING
Checkpoint Lag       00:00:17 (updated 00:00:08 ago)
Process ID           15160
Log Read Checkpoint  Oracle Redo Logs    --> Which means it's now in Classic Capture mode


3- Upgrade extract group from Classic to Integrated Capture:

GGSCI> STOP extpa

Sending STOP request to EXTRACT EXTPA ...
Request processed.


GGSCI>  REGISTER EXTRACT extpa DATABASE

2022-04-13 12:01:58  INFO    OGG-02003  Extract EXTPA successfully registered with database at SCN 6571803661069.

GGSCI>  INFO extpa UPGRADE

ERROR: Extract EXTPA is not ready to be upgraded because recovery SCN 6571803660879 has not reached SCN 6571803661069.


Note: Because of the above error we need to start again the extract group for few seconds till the LAG get fixed up to the forementioned SCN, then stop it again:

GGSCI>  start extpa

Sending START request to MANAGER ...
EXTRACT EXTPA starting

GGSCI>  info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT    
RUNNING     EXTPA       00:00:00      00:00:02 
EXTRACT     RUNNING     PMPPA       00:00:00      00:00:06    


GGSCI>  stop extpa

Sending STOP request to EXTRACT EXTPA ...
Request processed.
 

GGSCI>  INFO extpa UPGRADE
Extract EXTPA is ready to be upgraded to integrated capture.

Now it's good to go with the upgrade.

GGSCI>  ALTER extpa UPGRADE INTEGRATED TRANLOG
Extract EXTPA successfully upgraded to integrated capture.

GGSCI>  START extpa

Sending START request to MANAGER ...
EXTRACT EXTPA starting


GGSCI> INFO extpa
...
Log Read Checkpoint  Oracle Integrated Redo Logs     --> Which means it's using Integrated Capture
...

Now the extract group is correctly configured with Integrated capture method.

Thanks for reading.