ERROR OGG-01172 Discard file exceeded max bytes

Problem:

On a Goldengate 12.2 setup, one of the replicate processes get ABENDED with below error:

ERROR   OGG-01172  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Discard file (/goldengate/gghome/dirrpt/rep1.dsc) exceeded max bytes (2000000000).


Solution:

You can modify the parameter file for the same processes increasing the maximum size of the discard file which can reach up to 2048 MB like this:

DISCARDFILE ?GG_HOME/dirrpt/rep1.dsc, APPEND, MEGABYTES 2048

But again, once it reaches 2048 MB the process will ABEND, no matter what option you will use APPEND/PURGE/ROLLOVER , as this option works only at the time the replicate process starts up!

The other solution is to simply disable the reporting of the discarded transactions in the discard file if you don't care about them.

Remove or hash the entire line for the parameter DISCARDFILE and replace it with NODISCARDFILE parameter inside the replicate parameter file:

-- DISCARDFILE ?GG_HOME/dirrpt/rep1.dsc, APPEND, MEGABYTES 2048
NODISCARDFILE

Note: Both parameters DISCARDFILE  & NODISCARDFILE are mutually exclusive, which means you cannot use both of them in the same parameter file, you have to use only one; otherwise the process will get ABENDED with the below cute error when you try to start it up:

ERROR   OGG-10107 Parsing error, parameter [nodiscardfile] conflicts with parameter [discardfile]

References:

https://docs.oracle.com/goldengate/c1221/gg-winux/GWURF/discardfile-nodiscardfile.htm#GWURF462

https://docs.oracle.com/en/middleware/goldengate/non-stop/12.3.0.1/reference/discardrollover.html#GUID-9A8B91D2-F0D7-46ED-B553-D72E76D9BCE6

Goldengate ports to allow between Source & Target

 I'm getting this question frequently and hence thought to write it in a separate post:

Question: What are the Ports you need to Allow for goldengate to Work between Source & Target?

Answer: You need to allow the following range of ports between Source and Target with bidirectional option in order for goldengate to work:

First check if parameter DYNAMICPORTLIST is being used, it's specifying the list of ports which goldengate is using, then you can allow the firewall for this list plus port 7809.

 For example, if the parameter is specifying the list of ports in this fashion:

 Then you need to open the firewall between source and target for the following port:

Ports from range 7820 to 7830 plus individual ports 7833, 7835 along with port 7809 which is the default port for the Manager process.

If parameter DYNAMICPORTLIST is not configured to define the range of ports goldengate will use, then goldengate will use ports randomly from a range starting from port 7819 up to port 12818 along with port 7809 which is being used by the manager process.

References:

• https://docs.oracle.com/en/middleware/goldengate/core/19.1/admin/administering-oracle-goldengate.pdf

   

Goldengate Pump Processes Fail to Start Throwing Error "TCP/IP error 111 (Connection refused)"

 Problem:

Goldengate pump processes fail to start on the source site, ggserr.log is reporting this error:

TCP/IP error 111 (Connection refused), endpoint: 10.1.1.1:7809

 Analysis:

First make sure that goldengate ports are opened between both Source & Target sites. If parameter DYNAMICPORTLIST is not configured to define the range of ports goldengate will use, then goldengate can use ports randomly from range starting from port 7819 up to port 12818 along with port 7809 which is being used by the manager process.

I've already checked the route between Source & Target and confirmed that ports are opened:

# telnet 10.1.1.1 7809
Trying 10.1.1.1...
Connected to 10.1.1.1.


# telnet 10.1.1.1 7819
Trying 10.1.1.1...
Connected to 10.1.1.1.

 When checking GLOBALS file, it shows two paths:

# cat /goldengate/gghome/GLOBALS

GGSCHEMA GGOWNER
CHECKPOINTTABLE GGOWNER.CHECKPOINTTAB
ALLOWOUTPUTDIR /goldengate/ggdata_bkp
ALLOWOUTPUTDIR /goldengate/ggdata

While the right path "/goldengate/ggdata" which is being used by Source site is already existed, the other path "/goldengate/ggdata_bkp" is not exist:

# ls -ld /goldengate/ggdata_bkp

ls: cannot access /goldengate/ggdata_bkp: No such file or directory
 

 Solution:

Make sure the path mentioned after ALLOWOUTPUTDIR parameter in GLOBALS file is referring to an exist directory, otherwise remove or hash the line which refers to the non-exist directory, then restart the manager.

I've removed the following line, which refers to a non-exist directory:

# cat /goldengate/gghome/GLOBALS

GGSCHEMA GGOWNER
CHECKPOINTTABLE GGOWNER.CHECKPOINTTAB
ALLOWOUTPUTDIR /goldengate/ggdata

Then, restarted the manager process in the TARGET site:

GGSCI> stop manager

Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

GGSCI> start manager

 

Then, start the pump processes in the SOURCE site:

GGSCI> start p*

References:

https://docs.oracle.com/en/middleware/goldengate/core/19.1/admin/administering-oracle-goldengate.pdf

Goldengate Manager is not Deleting|Purging Old Trail Files

Problem:

In a Goldengate 19.1.0.0.1 setup, the following line was set inside inside mgr.prm to automatically delete the processes trail files older than 10 hours, but unfortunately the trail files are not getting deleted:

# cat mgr.prm | grep PURGEOLDEXTRACTS

PURGEOLDEXTRACTS /goldengate/ggdata/*  , USECHECKPOINTS, MINKEEPHOURS 24

I can see I've trail files as old as 10 days back:

[goldengate@ggserver ggdata]$ ll /goldengate/ggdata
total 228G
-rw-r----- 1 goldengate goldengate  96M Apr  3 22:55 RB000000000
-rw-r----- 1 goldengate goldengate  96M Apr  3 23:00 RB000000001
-rw-r----- 1 goldengate goldengate  96M Apr  3 23:00 RB000000002
-rw-r----- 1 goldengate goldengate  96M Apr  3 23:09 RC000000000
....
-rw-r----- 1 goldengate goldengate  96M Apr 13 08:03 RD000000365
-rw-r----- 1 goldengate goldengate  96M Apr 13 08:10 RC000001055
-rw-r----- 1 goldengate goldengate  96M Apr 13 08:13 RA000000409

Analysis:

The trail files location /goldengate/ggdata was a symbolic link for the actual location which was /u01/goldengate/ggdata

[goldengate@ggserver ggdata]$ ll /u01/goldengate/ggdata
lrwxrwxrwx 1 goldengate goldengate 11 Oct  4  2022 /u01/goldengate/ggdata -> /goldengate/ggdata
 

Solution:

Make sure the trail files location in  PURGEOLDEXTRACTS parameter line refers to the actual location, not to a symbolic link.

# cat mgr.prm | grep PURGEOLDEXTRACTS

PURGEOLDEXTRACTS /u01/goldengate/ggdata/*  , USECHECKPOINTS, MINKEEPHOURS 24

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.

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.

Goldengate Error Parameter [DBLOGREADER] is not valid for this configuration

 Problem:

Right after upgrading an extract group from Classic Capture to Integrated Capture and when starting it up it get ABENDED with the following error:

Parameter [DBLOGREADER] is not valid for this configuration

 
Analysis:

Before starting the upgraded extract group, you should remove all Classic Capture related parameters which are not compatible with Integrated Capture like:

TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS INCLUDEREGIONID

Solution:

Remove or hash all Classic Capture related parameters from the extract group parameter file before starting it with the new Integrated Capture method:

# vi extpa.prm

Login to the database as user GGOWNER failed because of error Error while trying to retrieve text for error ORA-01804

 Problem:

When starting up an extract group on Source DB, it gets ABDENDING with this error:

 GGSCI> start extra

 Error in ggserr.log:

ERROR   OGG-00303  Oracle GoldenGate Capture for Oracle, extra.prm:  Unable to connect to database using user GGOWNER@SPR. Ensure that the necessary privileges are granted to the user.
                              Login to the database as user GGOWNER failed because of error Error while trying to retrieve text for error ORA-01804.
2022-03-04T13:56:23.909+0000  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, extra.prm:  PROCESS ABENDING.
 

Solution:

Make sure the ORACLE_HOME parameter in the Extract parameter is pointing to the right location, in my case the ORACLE_HOME location was having a typo:

SETENV (ORACLE_HOME="/u01/oracle/19.5/d b")

Corrected to:

SETENV (ORACLE_HOME="/u01/oracle/19.5/db")

 

Get the SQL TEXT/SQLID of the Transaction Which Goldengate Extract is Lagging At

Problem:

Recently I had a goldengate issue where the Extract process on the source server was lagging because of a big transaction which was already completed on the database, but goldengate was still lagging for hours trying to catch up with its changes and dump them into gigabytes of trail files.

Analysis:

In a situation like this, the first troubleshooting step will come into your mind is what transaction causing this lag, and then you will know the tables which involved in this lag. You may think it's a simple task, just run one or two commands in goldengate console, and you will get all the info you need; but the shocking fact here although Goldengate is a complex product it doesn't have the capability to do so!

You may say OK then it still easy; just login to the DB and get that transaction from there; When I came to know about the Goldengate lag the transaction was already completed on the DB long back; but you are right; from an AWR report I can spot that transaction based on its physical IO, but later I came to know another way it's not straight forward, but it can do the job more quick and accurate.

Solution:

First, while the Extract group is lagging, try to get the current transaction XID using this GGSCI command:

GGSCI> send extract EXTA showtrans

Sending SHOWTRANS request to EXTRACT EXTA ...
Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 42819, SCN 1523.1174313470 (6542409505278), RBA 127366672
Redo Thread 2, Redo Log Sequence Number 46204, SCN 1523.1174314901 (6542409506709), RBA 434167824

------------------------------------------------------------
XID:                  4.16.828668        
Items:                1        
Extract:              EXTA     
Redo Thread:     1      
Start Time:         2021-09-30:13:27:46  
SCN:                  1523.1174314086 (6542409505894)   
Redo Seq:             42819
Redo RBA:             128058384           
Status:               Running             

Login to the DB and search for the transaction in current REDOLOG file using the above XID:

SQL> DEFINE XID='4.16.828668';
select  decode(s.sql_id,null,s.prev_sql_id) sqlid from gv$transaction t, gv$session s
    where s.saddr = t.ses_addr
    and t.xidusn=REGEXP_SUBSTR  ('&XID', '[^.]+', 1,1)
    and t.xidslot=REGEXP_SUBSTR ('&XID', '[^.]+', 1,2)
    and t.xidsqn=REGEXP_SUBSTR  ('&XID', '[^.]+', 1,3);

If you find it, then you are a lucky man, if you don't find it, then you have to sharpen your skills with using LogMiner feature to search for that transaction in the current and old REDOLOG files. 

But the good news here is that I put all these steps, including the LogMiner feature, in one shell script to easily do that job for you. The only effort required from your side is to get the transaction XID from Goldengate source side using the above-mentioned GGSCI command, then you can follow the screenshots below:

and here we go, the SQL Text of the transaction we're looking for:

If you know a better and faster way than this, please write it as a comment to help others.

Download the script from this link:

https://www.dropbox.com/s/j73i8lkjbu0xhc2/get_SQLText_for_goldengate_XID.sh?dl=0

GitHub version: