Automating Complex Oracle GoldenGate Procedures with AI, MCP, and REST APIs

Can we automate an Oracle GoldenGate procedure that involves querying databases, coordinating multiple processes, validating a load, and resuming replication from a consistent point?

That is what I explored in a lab using Codex, MCP servers for Oracle Database and GoldenGate, and the Microservices REST APIs. The use case was parallel initial load and replication re-instantiation, guided by a procedure documented in Markdown.

The approach captures operational knowledge in a runbook that an AI tool can consult to plan actions, execute authorized steps, verify results, and record evidence.

Markdown at the center of the workflow

The .md file defines how the procedure should run: what to check, the order of operations, the conditions for proceeding, and when to stop. It also records environment-specific decisions and situations that require human review.

In the lab, the runbook covered:

• Objective and scope: which environments and tables are included and which existing components must be preserved.
• Prerequisites: connectivity, privileges, database structures, capture coverage, trail retention, and conditions for consistent reads.
• Operational sequence: CDC preparation, SCN selection, extraction, target preparation, apply, validation, and resumption.
• Criteria for proceeding: the evidence required to consider each step complete.
• Failure handling: when to keep processes stopped, preserve files, and review the plan before continuing.
• Execution records: parameters, counts, checkpoints, incidents, and final status.

Each execution therefore starts from an explicit, reviewable set of instructions. The agent must inspect the current environment and determine the values for that run: table list, SCN, extraction ranges, and available names for temporary processes. Historical values serve as references and should not be reused automatically.

Markdown also provides a way to incorporate lessons from the lab. An API limitation, an additional check, or an approved target change can become part of the runbook for subsequent executions.

The role of each component

MCP – the Model Context Protocol – provides an interface through which an AI tool can discover and invoke operations exposed by external systems. The GoldenGate MCP server uses the product’s REST APIs, while SQLcl MCP enables database interaction. These integrations are described in Oracle’s article about GoldenGate MCP and the SQLcl MCP documentation.

The tool used in this work was Codex. The approach can also be evaluated with compatible clients such as Claude or GitHub Copilot, depending on their available integrations, permissions, and execution capabilities. The SQLcl documentation describes integration paths for Claude Desktop and Copilot in VS Code. These other clients were not tested in this lab. Configuration reference.

Parallel initial load as a practical use case

The tests used an Oracle-to-Oracle flow with Autonomous Database as the target. The most recent runs used GoldenGate 23.26.3.0.6, build 260911.1206, from the 26ai release family, as recorded in the updated lab runbook. The workflow coordinated by Codex covered:

1. Inspect the environment: validate connections, inspect processes, and identify tables, sizes, and effective mappings.
2. Prepare replication: stop the CDC Replicat, preserve parameters and checkpoints, and keep the Extract capturing changes.
3. Establish the load reference point: verify capture coverage and open transactions, select an SCN, and record source counts at that point.
4. Extract data in parallel: create four SOURCEISTABLE Extracts with non-overlapping ranges and consistent reads at the same SCN.
5. Prepare the target: confirm that all extraction files are complete before truncating the tables within scope.
6. Apply the load in parallel: run four Parallel Replicats concurrently, each with four appliers and BATCHSQL.
7. Validate the load: check counts by table and range, errors, discards, checkpoints, and actual apply completion.
8. Resume CDC: start the Replicat with AFTERCSN to apply transactions after the snapshot, and monitor the processing of accumulated changes.
9. Complete the procedure: remove only that run’s temporary processes and files, and produce the final report.

In this configuration, extraction and apply run in sequential phases: the four Extracts work concurrently, but all must finish before apply begins. The same SCN links the snapshot to CDC resumption, whose coverage depends on capture and the retained trails. The semantics of AFTERCSN are documented in the START REPLICAT reference.

Parallelism across Extracts and the use of SQLPREDICATE are covered in Nick Wagner’s blog post. Alex Lima also presents a parallel initial load procedure automated through REST APIs. In this lab, the agent coordinated these operational capabilities using the Markdown runbook.

Adapting the approach to other platforms

The approach can be adapted to Db2, SQL Server, MySQL, and other platforms supported by GoldenGate, subject to supported versions and source–target combinations. The runbook must address each database’s read consistency and CDC positioning mechanisms: SCN for Oracle and, for example, snapshot isolation and LSN in precise instantiation for SQL Server.

Available parallelism features and MCP connectors or REST APIs for the required operations must also be checked. These adaptations should follow the requirements for each platform and be validated in the corresponding environment. The scenario tested in this work was Oracle → Oracle.

MCP and REST working together

One practical finding was the need to verify exactly what each MCP tool can execute. In the version used, creating the initial load Extract and resuming replication with AFTERCSN required direct REST calls because the available MCP tools did not expose all the necessary options.

These details were incorporated into the Markdown runbook. It specified the appropriate interface, required parameters, and expected evidence for confirming the result. This helps prevent a specific operation from being replaced with a generic command that does not preserve its semantics.

Guiding a new execution

Once connections and tools are configured and the runbook is available, a request can follow this pattern:

Read the Markdown re-instantiation procedure and validate the current environment. Identify the tables and processes involved, check the prerequisites, and present a concrete plan for the parallel initial load. Preserve CDC capture and components outside the scope. Wait for my authorization before making operational changes. After execution, present the validation evidence and final replication status.

The request establishes the session’s objective; Markdown supplies the operational details. Authorization applies to the plan prepared for the current environment, and subsequent steps depend on the completion criteria recorded in the runbook.

What the lab demonstrated

In the most recent documented execution, the workflow loaded approximately 5.1 million records, validated counts, resumed CDC, and removed temporary components. The configuration also evolved to include partitioning of the larger target table following diagnosis and authorization. Checks for that structure were incorporated into the runbook for reuse.

The GoldenGate MCP server presented by Oracle is an open-source project intended for prototyping, development, and testing. It is not an Oracle-supported or certified product feature, as stated in its introductory article.

The experience demonstrated that a procedure with dependencies across database operations, capture, apply, and validation can be coordinated using a Markdown runbook, MCP tools, REST APIs, and an AI agent. The operational knowledge recorded in the runbook guides execution and allows corrections and lessons to be reviewed and reused in subsequent runs.

A Run in Practice The screenshots below illustrate the workflow in practice: environment checks, a proposed execution plan, user authorization, and the final validation and cleanup report.