In a previous blog, I went through the full procedure of renaming a GoldenGate extract from the adminclient. I included all necessary steps to avoid missing transactions. Doing this by hand is completely fine, but if you do it often, or want to remove the risk of a typo on an SCN, I have another solution for you. In this blog, I automate the same procedure with a Python script, rename_extract.py, using GoldenGate REST API (the full source is in this reference doc).

Please read through the previous blog if you have not already done it. I explain there the two SCNs involved (the start SCN, the oldest unprocessed transaction, and the dictionary build SCN, which must predate it) and why their ordering matters. Here, I only cover how to obtain and pass them through the REST API.

As a reminder, these were the steps:

  • Build the LogMiner data dictionary (if you don’t already build it regularly).
  • Check for long running transactions in the source database.
  • Stop the extract that you want to rename.
  • Get the SCN of the oldest unprocessed transaction.
  • Find the dictionary build to register the new extract.
  • Create the new extract, registered and started at the correct SCNs, reusing the old extract’s parameter file with only the name and trail changed.

Building the LogMiner dictionary

DBMS_CAPTURE_ADM.BUILD is a database-side operation, not a GoldenGate one, so the REST API cannot run it directly. The script runs it through the Python oracledb module when available :

import oracledb

def build_dictionary_oracledb(dsn, user, password):
    with oracledb.connect(dsn=dsn, user=user, password=password) as conn:
        with conn.cursor() as cur:
            first_scn = cur.var(int)
            cur.execute("BEGIN DBMS_CAPTURE_ADM.BUILD(first_scn => :first_scn); END;", first_scn=first_scn)
            return int(first_scn.getvalue())
>>> build_dictionary_oracledb("localhost:1521/CDB01", "c##oggadmin", "ogg")
25261458

Or through sqlplus when oracledb is not installed :

import re
import shutil
import subprocess

def build_dictionary_sqlplus(dsn, user, password):
    if not shutil.which("sqlplus"):
        raise RuntimeError("sqlplus is not on PATH")
    script = f"""
        SET SERVEROUTPUT ON
        CONNECT {user}/{password}@{dsn}
        DECLARE
            scn NUMBER;
        BEGIN
            DBMS_CAPTURE_ADM.BUILD(first_scn => scn);
            DBMS_OUTPUT.PUT_LINE('DICTIONARY_BUILD_SCN:' || scn);
        END;
        /
        EXIT
    """
    proc = subprocess.run(["sqlplus", "-s", "/nolog"], input=script, capture_output=True, text=True)
    return int(re.search(r"DICTIONARY_BUILD_SCN:(\d+)", proc.stdout).group(1))
>>> build_dictionary_sqlplus("localhost:1521/CDB01", "c##oggadmin", "ogg")
25262183

If you already build the dictionary on a regular schedule, rename_extract.py never calls either of these : the lookup further down finds an existing build old enough to reuse and moves straight on. It only builds a fresh one as a fallback, when that lookup comes back empty.

Computing the start SCN through the API

Reading the old extract’s recovery checkpoint and the database’s active transactions (exactly what the long running transactions blog describes) gives you the oldest unprocessed transaction. get_extract_checkpoint calls GET /services/{version}/extracts/{extract}/info/checkpoints :

>>> extract_checkpoints = ogg_client.get_extract_checkpoint("EXT1")
>>> extract_checkpoints["current"]["input"][0]["recovery"]
{'timestamp': '2026-09-06T14:03:16.000Z', 'thread': 1, 'sequence': 888, 'offset': 172466704, 'csn': 25167710, 'name': None}
>>> start_scn = extract_checkpoints["current"]["input"][0]["recovery"]["csn"]
>>> start_scn
25167710

get_active_transactions runs the same check the long running transactions blog covers by hand, as a REST call to GET /services/{version}/connections/{connection}/activeTransactions :

>>> ogg_client.get_active_transactions("OracleGoldenGate.source_cdb")
{'activeTransactions': [], 'currentScn': {'csn': 25167998, 'currentDate': '2026-09-06T14:03:30.000Z', 'userName': 'SYS'}, '$schema': 'ogg:activeTransactions'}

No open transactions here, so 25167710 stands as the start SCN. Had there been one, its txnStartScn would have been folded in and the smallest of the two kept, exactly as get_oldest_unprocessed_scn does in the full script rename_extract.py (see below).

Computing the dictionary SCN

Building a LogMiner dictionary is a database-side operation (DBMS_CAPTURE_ADM.BUILD). So is finding the right existing build, directly against the source database :

import oracledb

def get_dictionary_scn_oracledb(dsn, user, password, start_scn):
    with oracledb.connect(dsn=dsn, user=user, password=password) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT first_change#
                FROM v$archived_log
                WHERE dictionary_begin = 'YES'
                AND standby_dest = 'NO'
                AND name IS NOT NULL
                AND status = 'A'
                AND first_change# < :start_scn
                ORDER BY first_change# DESC
                FETCH FIRST 1 ROWS ONLY
            """, start_scn=start_scn)
            row = cur.fetchone()
            return row[0] if row else None
>>> get_dictionary_scn_oracledb("localhost:1521/CDB01", "c##oggadmin", "ogg", 25167710)
25134064

If dictionary_scn is None, no existing build predates the start SCN. It happens on a source database where the dictionary is not built on a regular schedule (the same case the adminclient blog covers by hand). rename_extract.py handles it by calling build_dictionary (above) for a fresh build. It then waits for the old extract’s checkpoint to move past that build’s SCN before continuing.

if dictionary_scn is None:
    dictionary_scn = build_dictionary(dsn, user, password, driver)
    start_scn = wait_for_dictionary_scn(ogg_client, "EXT1", "OracleGoldenGate.source_cdb", dictionary_scn)

wait_for_dictionary_scn polls get_extract_checkpoint every 30 seconds until the old extract’s start SCN passes the new build’s SCN. It gives up after 10 minutes with an error, rather than hanging forever. Both these delays are configurable. The old extract keeps running throughout, since stopping it would freeze its checkpoint.

When oracledb is not installed, the same query runs through sqlplus instead :

import shutil
import subprocess

def get_dictionary_scn_sqlplus(dsn, user, password, start_scn):
    if not shutil.which("sqlplus"):
        raise RuntimeError("sqlplus is not on PATH")
    script = f"""
        SET FEEDBACK OFF HEADING OFF PAGESIZE 0
        CONNECT {user}/{password}@{dsn}
        SELECT first_change# FROM v$archived_log
        WHERE dictionary_begin = 'YES' AND standby_dest = 'NO'
        AND name IS NOT NULL AND status = 'A'
        AND first_change# < {start_scn}
        ORDER BY first_change# DESC FETCH FIRST 1 ROWS ONLY;
        EXIT
    """
    proc = subprocess.run(["sqlplus", "-s", "/nolog"], input=script, capture_output=True, text=True)
    return int(proc.stdout.strip()) if proc.stdout.strip() else None
>>> get_dictionary_scn_sqlplus("localhost:1521/CDB01", "c##oggadmin", "ogg", 25167710)
25134064

Please note that unlike oracledb, sqlplus needs the statement’s trailing ; to execute the query.

Creating the new extract at the correct SCNs

Rather than retyping the old extract’s parameter file by hand, get_extract returns its exact config and credentials, which the new extract can reuse as is, only changing the EXTRACT name and the EXTTRAIL line :

>>> old = ogg_client.get_extract("EXT1")
>>> old["config"]
['EXTRACT EXT1', 'USERIDALIAS source_cdb DOMAIN OracleGoldenGate', 'EXTTRAIL aa', 'SOURCECATALOG PDB1', 'TABLE SALES.ORDERS;']
>>> old["credentials"]
{'alias': 'source_cdb', 'domain': 'OracleGoldenGate'}
old_config = old["config"]
credentials = old["credentials"]

new_config = []
for line in old_config:
    if line.startswith("EXTRACT "):
        new_config.append("EXTRACT EXT2")
    elif line.startswith("EXTTRAIL "):
        new_config.append("EXTTRAIL pdb1/bb")
    else:
        new_config.append(line)

The create_extract call then accepts both the begin position (start SCN) and the registration (dictionary build SCN) in a single call, on top of the reused config :

>>> ogg_client.stop_extract("EXT1")
>>> r = ogg_client.create_extract(
...     extract="EXT2",
...     begin={"at": {"csn": start_scn}},
...     registration={"containers": ["PDB1"], "csn": dictionary_scn, "replace": True},
...     source="tranlogs",
...     config=new_config,
...     credentials=credentials,
...     targets=[{"name": "bb", "path": "pdb1"}],
... )
>>> [m["title"] for m in r["messages"]]
['Integrated Extract added.', 'Extract group EXT2 successfully registered with database at SCN 25134064.', 'Parameter file EXT2.prm passed validity check.']
>>> r = ogg_client.start_extract("EXT2")
>>> [m["title"] for m in r["messages"]]
['Extract group EXT2 starting.', 'Extract group EXT2 started.']

Running OGGRestAPI’s get_extract_status method right after confirms it, with a real process ID and no lag :

>>> ogg_client.get_extract_status("EXT2")
{'$schema': 'ogg:extractStatus', 'status': 'running', 'processId': 127046, 'lastStarted': None, 'lag': 0, 'sinceLagReported': 5, 'position': '0.25477722'}

Notice how begin uses the {"at": {"csn": ...}} form to start the integrated extract at a specific SCN, and how registration carries the dictionary build SCN with its csn field. These are the REST equivalents of the add extract ... scn and register extract ... database scn commands.

The targets field is easy to miss and not optional : without it, create_extract writes the EXTTRAIL line into the parameter file but never actually creates the trail on disk, and the new extract abends on start with OGG-02454 Trail ... not found in checkpoint file. targets takes the trail’s two-character name and an optional path value. This also means the new trail must have a different name than the old extract.

Complete script

Assembling everything together into a single script, the only thing left to provide is the connection information and the new trail name. If you already have a dictionary SCN, you can pass it with --dictionary-scn, or let the script look it up by itself with --db-dsn/--db-user (--db-password is prompted if you leave it out) :

python rename_extract.py \
  --url https://vmogg \
  --user ogg \
  --deployment ogg_test_01 \
  --old-extract EXT1 \
  --new-extract EXT2 \
  --connection OracleGoldenGate.source_cdb \
  --db-dsn localhost:1521/CDB01 \
  --db-user c##oggadmin \
  --trail pdb1/bb

Here is the same EXT1 renaming, but forcing --driver sqlplus explicitly :

$ python rename_extract.py \
    --url http://localhost:7810 \
    --user ogg \
    --old-extract EXT1 \
    --new-extract EXT2 \
    --connection OracleGoldenGate.source_cdb \
    --db-dsn localhost:1521/CDB01 \
    --db-user c##oggadmin \
    --driver sqlplus \
    --trail bb
Password for ogg:
Oldest unprocessed SCN of EXT1...
  start SCN (oldest unprocessed): 25378395
Looking up the dictionary build SCN through sqlplus...
  dictionary SCN: 25342438
Stopping extract EXT1...
  start SCN after stop: 25378395
Creating extract EXT2 (register at 25342438, begin at 25378395)...
Starting extract EXT2...
Done.

The script fetches the old extract’s config and credentials, computes the start SCN from its recovery checkpoint, looks up the dictionary SCN (building a fresh one and waiting for the checkpoint to pass it if none was found), stops the old extract, then creates and starts the new one with that config, registered and started at the two SCNs.

You can find the full rename_extract.py in this reference doc. It uses oggrestapi, the GoldenGate REST API Python package you can find here or on GitHub.

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