GoldenGate Long Running Transaction Alert

April 25, 2023
Categories: GoldenGate

GoldenGate Long Running Transaction Alert to Email

In Oracle GoldenGate, a long-running transaction is a transaction that takes a long time to complete on the source database side and can cause performance issues for the Extract process.

A long-running transaction occurs when a transaction is not committing its work, that can be due to a variety of reasons. You may need to wait for the transaction to complete or the user of that transaction may have forgotten to commit their work. In such cases, it’s important to alert the user to either commit their work or roll it back to clear the long-running transaction lag.

In this article, I will demonstrate how to capture these GoldenGate Long Running Transaction Alert based on specific parameters. These transactions can then be sent as an email alert or stored in a database table for further analysis over an extended period of time.

ARCHITECTURE

prerequisite for script

To run the script, it is important to ensure that the below parameters are set correctly.

Tools Required : Bash Shell, Virtual Machine running Linux, SMTP Configured to send an Email from VM and Adminclient

OGG_USER=”oggadmin”
This is the username required to connect to GoldenGate MSA.
PWD=”Password_123″
This is password required to connect to GoldenGate MSA.
DEPLOYMENT_NAME=”OCIGG”
Deployment Name of MSA.
OCIGG_URL_OR_IP=”XXX.deployment.goldengate.us-ashburn-1.oci.oraclecloud.com”
This can be IP Address of MSA or FQDN of OCI GG URL. Make sure it is resolvable from Adminclient VM
ADMINCLIENT_HOME=”/home/opc/adminclient”
You need to have adminclient installed on Linux VM. You can download the Adminclient tool from here.
FILEPATH=”/DATA/LONG_RUNNING_TRANS”
Create a directory in Linux to store these scripts and output files.
EXT_NAME=”EX_SWING EX_MSK”
Name of the extracts you want to monitor. Add multiple extracts by separating them with spaces. In this example I have added 2 extracts for demonstration.
DURATION=”30 MIN”
Duration of the time you want notified with an alert. In this example I am using 30 MINUTES. If you want to change it to an hour or more please tweak this parameter accordingly.

Main Script

				
					##################################
#!/bin/bash
OGG_USER="oggadmin"
PWD="Password_123"
DEPLOYMENT_NAME="OCIGG"
OCIGG_URL_OR_IP="XXXXXX.deployment.goldengate.us-ashburn-1.oci.oraclecloud.com"
ADMINCLIENT_HOME="/home/opc/adminclient"
FILEPATH="/DATA/LONG_RUNNING_TRANS"
EXT_NAME="EX_SWING EX_MSK"
DURATION="30 MIN"
dt=`date +\%Y-\%m-\%d-\%H-\%M-\%S`
##################################

for p in $EXT_NAME
do
if [ ! -e $FILEPATH/connect.obey ]; then
    touch $FILEPATH/connect.obey
    echo "connect https://$OCIGG_URL_OR_IP deployment $DEPLOYMENT_NAME as $OGG_USER password $PWD !" > $FILEPATH/connect.obey
fi

echo send extract $p, showtrans duration $DURATION > $FILEPATH/$p.obey
cat "$FILEPATH/connect.obey" "$FILEPATH/$p.obey" > "$FILEPATH/$p"_LTXN.obey
echo obey "$FILEPATH/$p"_LTXN.obey | $ADMINCLIENT_HOME/bin/adminclient > "$FILEPATH/$p"_LTXN.out
sed -i -e 1,14d "$FILEPATH/$p"_LTXN.out

#check if the file is empty or not with the keyword XID
if grep -q "XID" "$FILEPATH/$p"_LTXN.out; then
cat "$FILEPATH/$p"_LTXN.out | mail -v -s "Long Running Transaction Found in $p "  -r "Raghav@oraclegg.com" -S replyto="Raghav@oraclegg.com"  Raghav@oraclegg.com
fi
done

Email ALERT- Output

Further troubleshooting on Oracle Database for the long running transaction sql’s

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk, b.status, a.sql_id
FROM gv$session a, gv$transaction b
WHERE a.saddr = b.ses_addr
and b.xidusn=5
order by b.used_urec;

select sql_id, child_number, plan_hash_value plan_hash, executions execs, u.username,
sql_text
from v$sql s, dba_users u
where
sql_id like nvl(‘&sql_id’,sql_id)
and u.user_id = s.parsing_user_id;

SELECT start_scn, to_char(start_timestamp, ‘DD-MON-YYYY HH:MI:SS’), undo_sql
FROM flashback_transaction_query
WHERE XID IN (
select t.xid
from v$transaction t, v$session s
where s.saddr = t.ses_addr)
and undo_sql is not null;

Summary : Although this article specifically discusses Oracle as the source database, the techniques used to generate alerts for long-running transactions in GoldenGate are not limited to Oracle databases. The script can be further customized for use with other databases as well. Additionally, apart from sending an email alert, you can also write the contents of the alert to a database table for further analysis.

For more information about Long running transactions, please do refer here.

Hope you found this article useful.
Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle..