When doing complex operations with GoldenGate, checking for long running transactions is mandatory if you don’t want to miss transactions. Let’s look at two ways of retrieving such information, first with the adminclient, and then with the REST API.

When and why should I worry about long running transactions ?

In a standard extract life-cycle, you should not be worrying about long running transactions. In fact, the only time you should think about these is when you plan an extract migration. By this, I mean moving an ongoing extract to a new GoldenGate environment.

This could be the case if you are moving the extract to a new GoldenGate deployment, whether it’s because of a version upgrade, system change or architecture change.

Another candidate scenario would be if you wanted to rename an extract.

Checking for Long Running Transactions with the adminclient

To check for long running transactions in the source database, you can use the adminclient and the showtrans tabular option of the send command.

OGG (https://vmogg ogg_test_01) 1> send extract ext showtrans tabular

Sending showtrans tabular request to Extract group EXT ...



XID                     Items    Extract   Redo Thread  Start Time           SCN                               Redo Seq  Redo RBA            Status
------------------------------------------------------------------------------------------------------------------------------------------------------
0.17.18.1700953         0        EXT       1            2026-06-06:08:04:12  629.3084780551 (2704619209735)    48911     156909584           Running

WARNING: This command will query the database for ALL active transactions ! There is absolutely no filter in place to only show transactions that are relevant for the extract you are targeting. To confirm this, let’s look in the database to get more information about this transaction.

-- Query to get the schema associated with a specific transaction, based on the XID column from the OGG output above
SELECT s.username, t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE t.xidusn = 17
AND t.xidslot = 18
AND t.xidsqn = 1700953;

USERNAME       XIDUSN     XIDSLOT      XIDSQN START_TIME          START_SCN
---------  ----------  ----------  ---------- ------------------- ----------------
DBIBLOG            17          18     1700953 06/06/26 08:04:12   2704619209735

But if I look at the extract parameter file, the DBIBLOG schema is not even being extracted.

OGG (https://vmogg ogg_test_01) 1> view params EXT
EXTRACT EXT
USERIDALIAS source_cdb DOMAIN OracleGoldenGate
EXTTRAIL pdb1/aa
SOURCECATALOG PDB1
TABLE APP_SCHEMA.*;

Of course, the DBIBLOG user might be editing data in the APP_SCHEMA schema, but there is no way to know for sure just by looking at the output of adminclient command above.

When searching for long running transactions, you should retrieve the START_SCN of the transaction. In the example given above, the START_SCN is 2704619209735.

Now that we have the START_SCN, we can check if the extract has already processed it or not by looking at the checkpoint information. From the adminclient, run the info extract EXT showch command:

OGG (https://vmogg ogg_test_01) 1> info extract EXT showch

Extract    EXT       Last Started 2026-06-06 07:45   Status RUNNING
Description          'Test extract'
Checkpoint Lag       00:01:45 (updated 00:00:32 ago)
Process ID           11711
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2026-06-06:09:01:45
                     SCN 629.3086233843 (2704620663027)
Settings Profile     ogg:managedProcessSettings:dbiDefault


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):
    Timestamp: 2026-06-06:07:45:45.000000
    SCN: 0.0 (0)

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2026-06-06:08:04:13.000000
    SCN: 629.3084780551 (2704619209735)

  Current Checkpoint (position of last record read in the data source):
    Timestamp: 2026-06-06:09:01:45.000000
    SCN: 629.3086233843 (2704620663027)

  BR Startup Recovery Checkpoint:
    Timestamp: 2026-06-02 10:17:33.403806
    SCN: 0.0 (0)

  BR Begin Recovery Checkpoint:
    Timestamp: 2026-06-06 08:04:13.000000
    SCN: 629.3084780551 (2704619209735)

  BR End Recovery Checkpoint:
    Timestamp: 2026-06-06 08:08:45.000000
    SCN: 629.3084879559 (2704619308743)

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 41
    RBA: 50476
...

If we put side to side the START_SCN of the long running transaction and the SCN of the recovery checkpoint, we can see that they are exactly the same (2704619209735). This is expected, and it means that the extract has not yet processed this transaction.

# From SQL query on the source database
USERNAME       XIDUSN     XIDSLOT      XIDSQN START_TIME          START_SCN
---------  ----------  ----------  ---------- ------------------- ----------------
DBIBLOG            17          18     1700953 06/06/26 08:04:12   2704619209735

# From adminclient
  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2026-06-06:08:04:13.000000
    SCN: 629.3084780551 (2704619209735)

If you wanted to move the extract to another GoldenGate installation or rename it, this would be the SCN at which you would need to start the new extract to avoid missing transactions.

Checking for Long Running Transactions from the REST API

If you are trying to automate the process of checking for long running transactions, using the adminclient might not be the best option. In fact, the display of long running transactions in the adminclient is not designed to be easily parsed by scripts.

Fortunately, you can also check for long running transactions using the official GoldenGate REST API. The endpoint that you need to call is GET /services/{version}/connections/{connection}/activeTransactions. It is described in the GoldenGate REST API documentation.

The endpoint path parameters explain why the transactions shown in the output are not specific to the endpoint. In GoldenGate, a connection is database specific. Combine the domain name and the alias name with a dot separator to form the connection name. In my case, the connection name is OracleGoldenGate.source_cdb.

In Python, let’s see two ways of getting the same information:

  • Using the production-ready Python client I presented in another blog.
  • Using the requests library to call the REST API directly.

Using the Python client, you can just call the get_active_transactions method as follows:

from oggrestapi import OGGRestAPI

ogg_client = OGGRestAPI(
    url="https://vmogg:7809",
    username="ogg",
)

active_transactions = ogg_client.get_active_transactions('OracleGoldenGate.source_cdb')

>>> active_transactions
{'activeTransactions': [{'txnStartScn': 2704619209735, 'txnStatus': 'ACTIVE', 'txnStartDate': '2026-06-06T08:04:12.000Z', 'sid': 834, 'serialNum': 16450, 'instanceId': 1, 'userName': 'DBIBLOG', 'osUser': 'oracle', 'sessionStatus': 'INACTIVE', 'logonTime': '2026-06-06T08:04:11.456Z'}], 'currentScn': {'csn': 2704620465717, 'currentDate': '2026-06-06T08:27:45.717Z', 'userName': 'SYS'}, '$schema': 'ogg:activeTransactions'}

Otherwise, with the requests library, you can call the activeTransactions endpoint as follows:

import requests

connection_name = "OracleGoldenGate.source_cdb"
# Basic configuration
# Direct connection (no reverse proxy)
# url = f"https://vmogg:7809/services/v2/connections/{connection_name}/activeTransactions"
# NGINX reverse proxy
url = f"https://vmogg/services/ogg_test_01/adminsrvr/v2/connections/{connection_name}/activeTransactions"

auth = ("ogg", "ogg_password")
response = requests.get(
    url,
    auth=auth
)

Here is an example of the output that you should get when looking at the response.json() value:

>>> active_transactions = response.json()['response']
>>> active_transactions
{'activeTransactions': [{'txnStartScn': 2704619209735, 'txnStatus': 'ACTIVE', 'txnStartDate': '2026-06-06T08:04:12.000Z', 'sid': 834, 'serialNum': 16450, 'instanceId': 1, 'userName': 'DBIBLOG', 'osUser': 'oracle', 'sessionStatus': 'INACTIVE', 'logonTime': '2026-06-06T08:04:11.456Z'}], 'currentScn': {'csn': 2704620465717, 'currentDate': '2026-06-06T08:27:45.717Z', 'userName': 'SYS'}, '$schema': 'ogg:activeTransactions'}

Or using the json.dumps() method to get a more readable output:

>>> import json
>>> print(json.dumps(active_transactions, indent=4))
{
    "activeTransactions": [
        {
            "txnStartScn": 2704619209735,
            "txnStatus": "ACTIVE",
            "txnStartDate": "2026-06-06T08:04:12.000Z",
            "sid": 834,
            "serialNum": 16450,
            "instanceId": 1,
            "userName": "DBIBLOG",
            "osUser": "oracle",
            "sessionStatus": "INACTIVE",
            "logonTime": "2026-06-06T08:04:11.456Z"
        }
    ],
    "currentScn": {
        "csn": 2704620465717,
        "currentDate": "2026-06-06T08:27:45.717Z",
        "userName": "SYS"
    },
    "$schema": "ogg:activeTransactions"
}

Using the REST API, the information is more complete and easier to parse. As mentioned before, retrieving the SCN at which the transaction started is sometimes necessary. In that case, you can get it from the following command:

>>> start_scn = active_transactions['activeTransactions'][0]['txnStartScn']
>>> start_scn
2704619209735

If you have multiple long running transactions, you should retrieve the minimum value for the txnStartScn to be sure to get the SCN of the oldest long running transaction.

>>> start_scns = [txn['txnStartScn'] for txn in active_transactions['activeTransactions']]
>>> min(start_scns)
2704619209735

Now that we’ve retrieved the START_SCN of the long running transaction, we should check the checkpoint information.

Using the Python client, you can call the get_extract_checkpoint method as follows:

>>> extract_checkpoints = ogg_client.get_extract_checkpoint('EXT')
>>> extract_checkpoints
{'$schema': 'ogg:extractCheckpoints', 'current': {'input': [{'starting': {'timestamp': '2026-06-06T07:45:45.000Z', 'thread': 1, 'sequence': 0, 'offset': 0, 'csn': None, 'name': None}, 'recovery': {'timestamp': '2026-06-06T08:04:13.000Z', 'thread': 1, 'sequence': 48911, 'offset': 156909584, 'csn': 2704619209735, 'name': None}, 'current': {'timestamp': '2026-06-06T09:01:45.000Z', 'thread': 1, 'sequence': 0, 'offset': 0, 'csn': 2704620663027, 'name': None}, 'boundedRecoveryPrevious': {'timestamp': '2026-06-02 10:17:33.404Z', 'thread': 0, 'sequence': 0, 'offset': 0, 'csn': None, 'name': None}, 'boundedRecoveryBegin': {'timestamp': '2026-06-06T08:04:13.000Z', 'thread': 0, 'sequence': 48911, 'offset': 156909584, 'csn': 2704619209735, 'name': None}, 'boundedRecoveryEnd': {'timestamp': '2026-06-06T08:08:45.000Z', 'thread': 1, 'sequence': 48912, 'offset': 156918384, 'csn': 2704619308743, 'name': None}}]}

Or, using the requests library:

response = requests.get(
    "https://vmogg/services/ogg_test_01/adminsrvr/v2/extracts/EXT/checkpoint",
    auth=auth
)

extract_checkpoints = response.json()['response']

Here is a more readable output from the checkpoint information:

>>> print(json.dumps(extract_checkpoints, indent=4))
{
    "$schema": "ogg:extractCheckpoints",
    "current": {
        "input": [
            {
                "starting": {
                    "timestamp": "2026-06-06T07:45:45.000Z",
                    "thread": 1,
                    "sequence": 0,
                    "offset": 0,
                    "csn": null,
                    "name": null
                },
                "recovery": {
                    "timestamp": "2026-06-06T08:04:13.000Z",
                    "thread": 1,
                    "sequence": 48911,
                    "offset": 156909584,
                    "csn": 2704619209735,
                    "name": null
                },
                "current": {
                    "timestamp": "2026-06-06T09:01:45.000Z",
                    "thread": 1,
                    "sequence": 0,
                    "offset": 0,
                    "csn": 2704620663027,
                    "name": null
                },
                "boundedRecoveryPrevious": {
                    "timestamp": "2026-06-02T10:17:33.404Z",
                    "thread": 0,
                    "sequence": 0,
                    "offset": 0,
                    "csn": null,
                    "name": null
                },
                "boundedRecoveryBegin": {
                    "timestamp": "2026-06-06T08:04:13.000Z",
                    "thread": 0,
                    "sequence": 48911,
                    "offset": 156909584,
                    "csn": 2704619209735,
                    "name": null
                },
                "boundedRecoveryEnd": {
                    "timestamp": "2026-06-06T08:08:45.000Z",
                    "thread": 1,
                    "sequence": 48912,
                    "offset": 156918384,
                    "csn": 2704619308743,
                    "name": null
                }
            }
        ]
    }
}

And to finish with, from the json, you can retrieve the SCN of the recovery checkpoint:

>>> recovery_checkpoint_scn = extract_checkpoints['current']['input'][0]['recovery']['csn']
>>> recovery_checkpoint_scn
2704619209735

Whether it’s to rename or move an extract, you now know why you should check long running transactions in GoldenGate, and how to do it from the adminclient and the REST API.

Post Views: 1
Thumbnail [60x60]
by
Julien Delattre