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:

	# #############################################################################
	# Script to get the SQLID for a goldengate transaction id XID
	# Ver 1.0
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 23-09-21 # # # # #
	#
	#
	#
	# #############################################################################
	# ############
	# Description:
	# ############
	echo
	echo "=============================================================="
	echo "This script fetch the SQLText for a goldengate transaction XID"
	echo "=============================================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
	# ############################
	# Listing Available Databases:
	# ############################
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the ORACLE_SID:[Enter the number]"
	echo ---------------------
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	integ='^[1-9]+$'
	if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ]
	then
	echo
	echo "Error: Not a valid number!"
	echo
	echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
	echo "-----------------------------------------------"
	else
	export ORACLE_SID=$DB_ID
	echo
	printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
	echo
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	export PGREP=`which pgrep`
	export PWDX=`which pwdx`
	if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
	then
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	fi
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	fi
	# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`dbhome "${ORACLE_SID}"`
	export ORACLE_HOME
	fi
	# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	fi
	# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	fi
	# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	if [ -x /usr/bin/locate ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	fi
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
	# Neutralize login.sql file:
	# #########################
	# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:
	if [ -f ./login.sql ]
	then
	mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
	fi
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	#CURR_USER=`whoami`
	# if [ ${ORA_USER} != ${CURR_USER} ]; then
	# echo ""
	# echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	# echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	# echo "Script Terminated!"
	# exit
	# fi
	echo "From GoldenGate GGSCI console get the transaction XID by executing below command:"
	printf "`echo -e "\033[33;5msend extract XXX showtrans\033[0m"` `echo "-- replace XXX with the EXTRACT group name"`\n"
	echo
	echo "Then, copy the complete number in front of XID: and provide it when prompted."
	echo
	echo "Enter the complete goldengate Transaction XID: [e.g. 139.12.7079729]"
	echo "============================================="
	while read XID
	do
	case ${XID} in
	*[0-9]*.*[0-9]*.*[0-9]*) echo; export XID; echo; break;;
	*) echo;echo "Please enter a valid XID number: [e.g. 139.12.7079729]"
	echo "-------------------------------";;
	esac
	done
	echo "Searching XID [${XID}] in the REDO LOG Buffer ..."
	echo
	SQLID_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 feedback off;
	alter system checkpoint;
	prompt
	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);
	exit;
	EOF
	)
	SQLID=`echo ${SQLID_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ -z ${SQLID} ]
	then
	echo "Transaction with XID [${XID}] CANNOT be found in the REDO Buffer. This is Expected!"
	echo
	# ################################
	# LogMiner Feature:
	# ################################
	echo "Do you want to search deeper using LogMiner [License Free feature in EE]: [Highly Recommended] [Y|n]"
	echo "==========================================="
	while read ANS
	do
	case ${ANS} in
	N|n|NO|no) echo; echo "SCRIPT TERMINATED! "; echo; exit;;
	""|Y|y|YES|yes) echo; echo "Checking the availability of LogMiner Feature in this Database Edition..."; echo;
	# Checking if Data Mining feature is enabled:
	DATAMINING_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	select count(*) from v\$option where parameter='Data Mining' and value='TRUE';
	exit;
	EOF
	)
	DATAMINING_OPTION=`echo ${DATAMINING_OPTION_RAW} | awk '{print $NF}'`
	if [ ${DATAMINING_OPTION} -eq 0 ]
	then
	echo "DATA MINING Feature is DISABLED in this Database Edition!"
	echo ""
	echo "SCRIPT TERMINATED! "
	echo ""
	exit
	else
	echo "[DATA MINING Feature is Available]"
	echo
	fi
	# Checking supplemental_log_data_min is enabled:
	SUPPL_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	select count(*) from v\$database where supplemental_log_data_min='YES';
	exit;
	EOF
	)
	SUPPL_OPTION=`echo ${SUPPL_OPTION_RAW} | awk '{print $NF}'`
	if [ ${DATAMINING_OPTION} -eq 0 ]
	then
	echo "SUPPLEMENTAL LOG Feature is DISABLED, LogMiner may fail to find the required data, but let's try!"
	echo ""
	fi
	# Setting the number of Hours for LogMiner to search within:
	echo "How many HOURS back you want to mine for transactions?: [Default 1 Hour]"
	echo "------------------------------------------------------"
	while read HOURS
	do
	case ${HOURS} in
	"") export NUM_HOURS=1; echo "LogMiner will search the last 1 hours transactions";echo; break;;
	*[!0-9]*) echo "Please enter a valid NUMBER:"
	echo "---------------------------";;
	*) echo; export NUM_HOURS=${HOURS}; break;;
	esac
	done
	export SPOOL_DIR=/tmp
	export SPOOL_FILE='Build_LogMiner_Catalog.sql'
	# Building the LogMiner Catalog of Archive Logs:
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 lines 300 feedback off echo off termout off serveroutput off
	prompt Archiving the current REDOLOG file ...
	alter system archive log current;
	prompt
	prompt Adding Archive Logs to LogMiner Catalog:
	spool ${SPOOL_DIR}/${SPOOL_FILE}
	select distinct 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '''||NAME||''',OPTIONS => DBMS_LOGMNR.ADDFILE);' from gv\$archived_log where completion_time > sysdate-${NUM_HOURS}/24 and DEST_ID=1 group by name order by 1;
	spool off
	EOF
	# Executing LogMiner:
	# Removing un-needed lines from the generated spool file:
	sed -i 's/completion_time//g' ${SPOOL_DIR}/${SPOOL_FILE}
	sed -i 's/spool//g' ${SPOOL_DIR}/${SPOOL_FILE}
	# The first REDOLOG should be add with NEW parameter and the rest to keep with ADDFILE parameter:
	sed -i '1s/.ADDFILE/.NEW/' ${SPOOL_DIR}/${SPOOL_FILE}
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set feedback off
	prompt
	prompt Building the LogMiner catalog using ${SPOOL_FILE} ...
	start ${SPOOL_DIR}/${SPOOL_FILE}
	prompt
	prompt Activating the LogMiner catalog ...
	EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
	prompt
	prompt Searching for Transaction with XID [${XID}]:
	prompt
	set pages 1000 lines 156 long 2000000000 timing on feedback on
	col xid for a18
	col TIMESTAMP for a20
	col EXECUTER for a15
	col OBJ_OWNER for a15
	col sql_redo for a95
	spool Transaction_XID_${XID}.log
	select xidusn||'.'||xidslt||'.'||xidsqn xid,to_char(timestamp,'DD-MON-YY HH24:MI:SS') TIMESTAMP,username EXECUTER,sql_redo from v\$logmnr_contents
	where xidusn=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,1)
	and xidslt=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,2)
	and xidsqn=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,3)
	and sql_redo <> 'set transaction read write;';
	spool off
	set feedback off timing off
	-- Cleanup the LogMiner Session:
	execute dbms_logmnr.end_logmnr();
	EOF
	break;;
	*) echo "Please enter a valid answer ya Lemed: [YES|NO]"
	echo "------------------------------------";;
	esac
	done
	else
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 1000 lines 156 long 2000000000 feedback off;
	col SQL_ID for a15
	col SQL_FULLTEXT for a100
	prompt Voilà! Transaction found:
	prompt
	select SQL_ID, SQL_FULLTEXT from gv\$sql where SQL_ID='${SQLID}';
	prompt
	exit;
	EOF
	fi
	# De-Neutralize login.sql file:
	# ############################
	# If login.sql was renamed during the execution of the script revert it back to its original name:
	if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
	then
	mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
	fi
	# #############
	# END OF SCRIPT
	# #############
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# Do not live under a rock :-) Every month a new version of DBA_BUNDLE get released, download it by visiting:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
	# REPORT BUGs to: mahmmoudadel@hotmail.com

view raw get_SQLText_for_goldengate_XID.sh hosted with ❤ by GitHub