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: