Most of the time, when GoldenGate extract runs without a hitch, and the GoldenGate monitor displays no anomalies or slowness, we are unaware that a prolonged open transaction is holding a lock in the database, which can result in long pipeline latency.

This issue can occasionally arise because an application has not properly cleaned up the orphan transaction, among other things.

If GoldenGate encounters a problem and abends or simply is restarted, this may become a problem.

Integrated Extract must retain a checkpoint to all transactions started against its list of capture tables, even if those transactions contain no data, until the transaction either commits or is rolled back. This becomes a problem when these transactions run for days/week/months, or worse, exceed the Archive Log retention policies for database instances.

Upon restart, it must return to the transaction’s original SCN to resume mining.

Bound Recovery has various protections to prevent that, but the size can also be customized.

Within the database or GoldenGate, there are several ways to watch for that.

The GoldenGate admin server API’s ability to recognize such transactions is demonstrated simply with the Python script below.

The output for this example will be:

In this illustration, two open transactions give the user enough details to drill down to the session or DML level and perform the necessary steps to free up the sessions.

Additionally, it offers a crucial SCN and Redo Sequence necessary for the extract to resume normal operations.

Get the transaction status:

select s.inst_id,p.spid,p.program,t.start_time, t.xidusn||'.'||t.xidslot||'.'||t.xidsqn xid,
s.status,s.sid,s.serial#,s.username,s.status,s.schemaname,
decode(s.sql_id,null,s.prev_sql_id) sqlid, decode(s.sql_child_number,null,s.prev_child_number) child from
gv$transaction t, gv$session s, gv$process p
where s.saddr = t.ses_addr
and s.paddr = p.addr
order by t.start_time asc;

Get the transaction SQL:

select inst_id,sql_text,first_load_time from GV$SQL where sql_id=<from above query> and inst_id= <from above query> ;

Phyton Example:

#############################################################################################
# GoldenGate HUB Check Extract and Replicats
#
# Author: Alex Lima
#
# Execution: $python3 OpenTransaction.py
#
# Config: Set GoldenGate HUR URL, PORT and Authentication:  goldengate_hub_url and header
#
############################################################################################

import requests
import json
import os

# Clearing the Screen
os.system('clear')

# Disable Warning for InsecureRequestWarning certificate verification
requests.packages.urllib3.disable_warnings()

# Url and PORT for GOldengate HUB
goldengate_hub_url="https://<HOST>"

# Authentication
header = {"Authorization" : "Basic <encrypted authorization>"}
##---

# Extract Name for testing purposes
ext_name = "EWEST"

hub_url = goldengate_hub_url + "/services/v2/mpoints/"+ ext_name +"/currentInflightTransactions"
response = requests.get(hub_url, verify=False , headers=header).text
response_info = json.loads(response)

if (len(response_info['messages']) < 1):

    #print(len(response_info['response']['currentInflightTransactions']))
    print("Current In Flight Transactions")
    print("="*51)
    
    # Loop along dictionary keys
    i = 0
    while i < len(response_info['response']['currentInflightTransactions']):
    #    print_proc_status(response_info['response']['items'][i]['name'], hub_url, ggprocess)
    #print(response_info)
        print("XID:         " +     response_info['response']['currentInflightTransactions'][0]['xid'])
        print("Extract:     " +     response_info['response']['currentInflightTransactions'][0]['extract'])
        print("Redo Thread: " + str(response_info['response']['currentInflightTransactions'][0]['redoThread']))
        print("Start Time:  " +     response_info['response']['currentInflightTransactions'][0]['startTime'])
        print("Redo Seq:    " + str(response_info['response']['currentInflightTransactions'][0]['redoSeq']))
        print("SCN:         " +     response_info['response']['currentInflightTransactions'][0]['scn'])
        print("Redo RBA:    " + str(response_info['response']['currentInflightTransactions'][0]['redoRba']))
        print("Status:      " +     response_info['response']['currentInflightTransactions'][0]['status'])
        print()
        i += 1
else:
    print("="*51)
    print("###### OGG-25166: No Long Transaction Found #######")
    print("="*51)
    print()