{"id":45630,"date":"2026-08-17T08:11:00","date_gmt":"2026-08-17T06:11:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=45630"},"modified":"2026-08-13T13:34:37","modified_gmt":"2026-08-13T11:34:37","slug":"checking-long-running-transactions-in-goldengate","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/checking-long-running-transactions-in-goldengate\/","title":{"rendered":"Checking Long Running Transactions in GoldenGate"},"content":{"rendered":"\n

When doing complex operations with GoldenGate, checking for long running transactions is mandatory if you don\u2019t want to miss transactions. Let\u2019s look at two ways of retrieving such information, first with the adminclient<\/code>, and then with the REST API.<\/p>\n\n\n\n

When and why should I worry about long running transactions ?<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

This could be the case if you are moving the extract to a new GoldenGate deployment, whether it\u2019s because of a version upgrade, system change or architecture change.<\/p>\n\n\n\n

Another candidate scenario would be if you wanted to rename an extract.<\/p>\n\n\n\n

Checking for Long Running Transactions with the adminclient<\/code><\/h2>\n\n\n\n

To check for long running transactions in the source database, you can use the adminclient<\/code> and the showtrans tabular<\/code> option of the send<\/code> command.<\/p>\n\n\n\n

OGG (https:\/\/vmogg ogg_test_01) 1> send extract ext showtrans tabular\n\nSending showtrans tabular request to Extract group EXT ...\n\n\n\nXID                     Items    Extract   Redo Thread  Start Time           SCN                               Redo Seq  Redo RBA            Status\n------------------------------------------------------------------------------------------------------------------------------------------------------\n0.17.18.1700953         0        EXT       1            2026-06-06:08:04:12  629.3084780551 (2704619209735)    48911     156909584           Running<\/code><\/pre>\n\n\n\n
WARNING:<\/strong> This command will query the database for ALL<\/strong> 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\u2019s look in the database to get more information about this transaction.<\/p>\n\n\n\n
-- Query to get the schema associated with a specific transaction, based on the XID column from the OGG output above\nSELECT s.username, t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nWHERE t.xidusn = 17\nAND t.xidslot = 18\nAND t.xidsqn = 1700953;\n\nUSERNAME       XIDUSN     XIDSLOT      XIDSQN START_TIME          START_SCN\n---------  ----------  ----------  ---------- ------------------- ----------------\nDBIBLOG            17          18     1700953 06\/06\/26 08:04:12   2704619209735<\/code><\/pre>\n\n\n\n
But if I look at the extract parameter file, the DBIBLOG<\/code> schema is not even being extracted.<\/p>\n\n\n\n
OGG (https:\/\/vmogg ogg_test_01) 1> view params EXT\nEXTRACT EXT\nUSERIDALIAS source_cdb DOMAIN OracleGoldenGate\nEXTTRAIL pdb1\/aa\nSOURCECATALOG PDB1\nTABLE APP_SCHEMA.*;<\/code><\/pre>\n\n\n\n
Of course, the DBIBLOG<\/code> user might be editing data in the APP_SCHEMA<\/code> schema, but there is no way to know for sure just by looking at the output of adminclient<\/code> command above.<\/p>\n\n\n\n
When searching for long running transactions, you should retrieve the START_SCN<\/code> of the transaction. In the example given above, the START_SCN<\/code> is 2704619209735<\/code>.<\/p>\n\n\n\n
Now that we have the START_SCN<\/code>, we can check if the extract has already processed it or not by looking at the checkpoint information. From the adminclient<\/code>, run the info extract EXT showch<\/code> command:<\/p>\n\n\n\n
OGG (https:\/\/vmogg ogg_test_01) 1> info extract EXT showch\n\nExtract    EXT       Last Started 2026-06-06 07:45   Status RUNNING\nDescription          'Test extract'\nCheckpoint Lag       00:01:45 (updated 00:00:32 ago)\nProcess ID           11711\nLog Read Checkpoint  Oracle Integrated Redo Logs\n                     2026-06-06:09:01:45\n                     SCN 629.3086233843 (2704620663027)\nSettings Profile     ogg:managedProcessSettings:dbiDefault\n\n\nCurrent Checkpoint Detail:\n\nRead Checkpoint #1\n\n  Oracle Integrated Redo Log\n\n  Startup Checkpoint (starting position in the data source):\n    Timestamp: 2026-06-06:07:45:45.000000\n    SCN: 0.0 (0)\n\n  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):\n    Timestamp: 2026-06-06:08:04:13.000000\n    SCN: 629.3084780551 (2704619209735)\n\n  Current Checkpoint (position of last record read in the data source):\n    Timestamp: 2026-06-06:09:01:45.000000\n    SCN: 629.3086233843 (2704620663027)\n\n  BR Startup Recovery Checkpoint:\n    Timestamp: 2026-06-02 10:17:33.403806\n    SCN: 0.0 (0)\n\n  BR Begin Recovery Checkpoint:\n    Timestamp: 2026-06-06 08:04:13.000000\n    SCN: 629.3084780551 (2704619209735)\n\n  BR End Recovery Checkpoint:\n    Timestamp: 2026-06-06 08:08:45.000000\n    SCN: 629.3084879559 (2704619308743)\n\nWrite Checkpoint #1\n\n  GGS Log Trail\n\n  Current Checkpoint (current write position):\n    Sequence #: 41\n    RBA: 50476\n...<\/code><\/pre>\n\n\n\n
If we put side to side the START_SCN<\/code> of the long running transaction and the SCN<\/code> of the recovery checkpoint, we can see that they are exactly the same (2704619209735<\/code>). This is expected, and it means that the extract has not yet processed this transaction.<\/p>\n\n\n\n
# From SQL query on the source database\nUSERNAME       XIDUSN     XIDSLOT      XIDSQN START_TIME          START_SCN\n---------  ----------  ----------  ---------- ------------------- ----------------\nDBIBLOG            17          18     1700953 06\/06\/26 08:04:12   2704619209735\n\n# From adminclient\n  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):\n    Timestamp: 2026-06-06:08:04:13.000000\n    SCN: 629.3084780551 (2704619209735)<\/code><\/pre>\n\n\n\n
If you wanted to move the extract<\/strong> to another GoldenGate installation or rename it<\/strong>, this would be the SCN<\/code> at which you would need to start the new extract to avoid missing transactions.<\/p>\n\n\n\n
Checking for Long Running Transactions from the REST API<\/h2>\n\n\n\n
If you are trying to automate<\/strong> the process of checking for long running transactions, using the adminclient<\/code> might not be the best option. In fact, the display of long running transactions in the adminclient<\/code> is not designed to be easily parsed by scripts.<\/p>\n\n\n\n
Fortunately, you can also check for long running transactions<\/strong> using the official GoldenGate REST API<\/strong>. The endpoint that you need to call is GET \/services\/{version}\/connections\/{connection}\/activeTransactions<\/code>. It is described in the GoldenGate REST API documentation<\/a>.<\/p>\n\n\n\n
The endpoint path parameters explain why the transactions shown in the output are not specific to the endpoint. In GoldenGate, a connection<\/code> is database specific. Combine the domain name and the alias name with a dot separator to form the connection<\/code> name. In my case, the connection<\/code> name is OracleGoldenGate.source_cdb<\/code>.<\/p>\n\n\n\n
In Python, let\u2019s see two ways of getting the same information:<\/p>\n\n\n\n