Tales From A Lazy Fat DBA

Classic to Microservices – what actually changes when you migrate GoldenGate

Posted by FatDBA on July 26, 2026

I’ve been putting off writing this one for a while, mostly because every time I sit down to do it I remember another edge case that bit me on some engagement. But we’ve now done enough of these migrations at client sites that the pattern is stable enough to write down.

Quick framing before we start. Classic Architecture …the one with GGSCI, a Manager process, and the whole thing driven by parameter files you edit with EDIT PARAMS … is gone in the latest release. GoldenGate 23ai is delivered for the Microservices Architecture only; 23ai no longer supports Classic Architecture at all. So this isn’t really a “should we migrate” conversation anymore. It’s a “when and how” conversation. And the timing pressure is real — Premier Support for GoldenGate 19c and 21c is ending in April 2026, so a lot of shops are up against it.

One correction to a thing people often get muddled: Classic Extract and Classic Architecture are two different deprecations. Classic Extract (as opposed to Integrated Extract) was deprecated in 18c and desupported in 21c. Classic Architecture … the whole GGSCI/Manager control plane … is what’s removed in 23ai. If you’re still running a Classic Extract, you have to move to Integrated Extract before you migrate architecture; the migration utility will simply fail if a classic extract is configured

he thing people get wrong is assuming this is a rewrite. It isn’t. Your Extract and Replicat processes do the same work, read the same redo, apply the same DML. What changes is the control plane around them.

In Classic you had a Manager process listening on a port, GGSCI as your shell, and everything living under one $OGG_HOME that mixed binaries with runtime data. Microservices splits this apart:

ServiceManager : supervises the deployment
Administration Service : replaces Manager and GGSCI’s admin functions
Distribution Service : replaces the pump Extract for sending trails downstream
Receiver Service .. receives distribution paths on the target side
Performance Metrics Service : metrics collection

And critically, binaries now live in OGG_HOME while runtime config, trails, and reports live in a separate deployment directory. That separation is genuinely nice once you get used to it … deployments can just point at a new OGG_HOME, which makes upgrades much less scary. You also get adminclient instead of GGSCI (the GGSCI-equivalent tool is still available as the Admin Client), a REST API for everything, and a web UI. The parameter file syntax itself is almost entirely unchanged, which is the good news.

One replicat note while we’re here, because it caught a colleague out: in 23ai, Integrated Replicat cannot read an EXTFILE … only non-integrated (classic-mode) Replicat can, and for OLTP workloads Oracle recommends Parallel Replicat in non-integrated mode. Keep that in mind if your config reads files rather than trails.

There are genuinely two ways to do this, and you should pick deliberately.

Path A :: the Migration Utility. Oracle ships a dedicated tool. It’s available on My Oracle Support as Patch 37274898 (KB100447), it’s interactive, and it migrates Extracts and Replicats from a Classic deployment to a Microservices deployment, converts pumps to a distribution path, and also migrates trail files, checkpoints, and associated artifacts. That “converts pumps to a distribution path automatically” bit is the big selling point — the pump conversion is the fiddliest part to do by hand. Prerequisites are mentiopned below.

GoldenGate 19c or higher with Bundle Patch 200714 or later, and Java 1.8 JRE or later (the one shipped with GoldenGate works).
A new Microservices deployment already created with all services running, local to the Classic deployment (or the Classic home filesystem available on the same host).
The Classic deployment must be stopped during migration, including any server collectors; abended processes must be restarted and shut down gracefully first.
Run the utility from a shell with the environment variables set for the Microservices deployment.

The invocation is a Java jar with a dry-run mode. Set OGG_HOME and LD_LIBRARY_PATH to the 23ai home first, then .. Note: Always run -dryrun first and read every line of what it says it will migrate.

export OGG_HOME=/u01/app/oracle/product/ogg23ai
export LD_LIBRARY_PATH=$OGG_HOME/lib
java -jar ogg-migration-package-23.4.0.0.0.jar \
     -classicHome /u01/app/oracle/product/ogg19 -dryrun

Path B : rebuild by hand. The reason I still reach for this on some engagements: a rebuild forces you to look at every parameter file. I’ve never done one of these without finding a parameter sitting there since 12c doing nothing, or a table in a wildcard that should’ve been excluded three years ago. The utility faithfully preserves your accumulated cruft. If you have twelve well-documented Extracts, use the utility. If you have three and a suspicion nobody’s audited them lately, rebuild.

The rest of this post covers the manual path, because even if you use the utility you should understand what it’s doing under the hood. Before you touch anything, verify the source is clean. On the Classic side:

GGSCI> INFO ALL
GGSCI> SEND EXTRACT ext1, STATUS
GGSCI> LAG EXTRACT ext1
GGSCI> LAG REPLICAT rep1

You want Extract at EOF on redo, Replicat with zero lag and no pending transactions. Anything else and you’re migrating a mess. Then record checkpoint positions. People rush this and regret it:

GGSCI> INFO EXTRACT ext1, SHOWCH
GGSCI> INFO REPLICAT rep1, SHOWCH

Write these down somewhere outside the server … the Extract’s Recovery Checkpoint SCN especially. I once watched a team lose a checkpoint during a rushed cutover and end up repositioning from an SCN that only existed in a terminal scrollback someone had already closed.

Your checkpoint table and GoldenGate DB user carry over fine … the checkpoint table format is compatible, and DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE grants don’t need redoing.

Installing Microservices alongside : Install to a new OGG_HOME. Do not install over Classic … you want both available during transition so rollback is real, not theoretical. And per Oracle’s own prereq, the new MA deployment needs to be on the same host (or the Classic home reachable there).

cd /stage/ogg23ai
./runInstaller -silent -responseFile /stage/ogg_install.rsp

Then create the deployment with the config assistant, oggca.sh, which has a silent mode I prefer for repeatability … along with some of the key oggca response entries:

export OGG_HOME=/u01/app/ogg/23ai
$OGG_HOME/bin/oggca.sh -silent -responseFile /stage/oggca.rsp

-- some of the key entries 
CONFIGURATION_OPTION=ADD
DEPLOYMENT_NAME=ogg_prod
ADMINISTRATOR_USER=oggadmin
HOST_SERVICEMANAGER=dbhost01.example.com
PORT_SERVICEMANAGER=9000
SECURITY_ENABLED=false
OGG_SOFTWARE_HOME=/u01/app/ogg/23ai
OGG_DEPLOYMENT_HOME=/u01/app/ogg/deployments/ogg_prod
OGG_SCHEMA=ggadmin
PORT_ADMINSRVR=9001
PORT_DISTSRVR=9002
PORT_RCVRSRVR=9003
PORT_PMSRVR=9004

I’ve set SECURITY_ENABLED=false for readability. In production, don’t … turn on TLS and point it at a proper wallet. The extra hour isn’t worth having your REST API sitting in the clear.

Credentials: Classic used a credential store under $OGG_HOME/dircrd; Microservices has its own per deployment. Just recreate them … cleaner than copying wallet files … One important note is that the CONNECT … you’re connecting to the Administration Service over HTTP, not opening a local shell.

$OGG_HOME/bin/adminclient
OGG> CONNECT http://dbhost01:9001 AS oggadmin PASSWORD ******
OGG> ALTER CREDENTIALSTORE ADD USER ggadmin@ORCLPDB1 PASSWORD ****** ALIAS ggsrc DOMAIN OracleGoldenGate
OGG> INFO CREDENTIALSTORE

Migrating Parameter Files: Mostly copy-paste. Two differences. The DOMAIN clause .. MA credential stores are domain-scoped, and OracleGoldenGate is the default domain. And the trail: you give just the two-character prefix, resolved relative to the deployment’s dirdat. Register and add it, positioning at the checkpoint SCN you recorded (not BEGIN NOW):

-- Classic 
EXTRACT ext1
USERIDALIAS ggsrc
EXTTRAIL ./dirdat/ea
TABLE SALES.ORDERS;

-- Microservices
EXTRACT ext1
USERIDALIAS ggsrc DOMAIN OracleGoldenGate
EXTTRAIL ea
TABLE SALES.ORDERS;

-- Register and add it, positioning at the checkpoint SCN you recorded (do not BEGIN NOW)
OGG> DBLOGIN USERIDALIAS ggsrc DOMAIN OracleGoldenGate
OGG> REGISTER EXTRACT ext1 DATABASE CONTAINER (ORCLPDB1)
OGG> ADD EXTRACT ext1, INTEGRATED TRANLOG, SCN 45892017334
OGG> ADD EXTTRAIL ea, EXTRACT ext1, MEGABYTES 500

One accuracy note specific to 23ai targets: Per-PDB Extract is the only supported Extract for Oracle Database 23ai, except in a DownStream Capture configuration, where you use a root-level Extract and must set the Streams Pool. That doesn’t change a 19c-source migration, but if your database is also moving to 23ai, plan the Extract mode accordingly. For Replicat, this is a good moment to move to Parallel Replicat:

OGG> DBLOGIN USERIDALIAS ggtgt DOMAIN OracleGoldenGate
OGG> ADD REPLICAT rep1, PARALLEL, EXTTRAIL ra, CHECKPOINTTABLE ggadmin.ggs_checkpoint

The PUMP replacement : Biggest conceptual change. The Classic data pump … a secondary Extract with RMTHOST/RMTTRAIL pushing to the target Manager … becomes a Distribution Path in the Distribution Service, with no parameter file. On authentication, be aware there’s a real choice here: Microservices offers USERIDALIAS target authentication (an Operator-role user on the target whose credentials are stored on the source; the path’s target auth method is set to Password and it uses the WSS secure web socket protocol) or certificate-based authentication using trusted CA certificates. Via adminclient against the Distribution Service:

OGG> CONNECT http://dbhost01:9002 AS oggadmin PASSWORD ******
OGG> ADD DISTPATH dp1 SOURCE trail://localhost/services/v2/sources?trail=ea -
     TARGET wss://tgthost01:9003/services/v2/targets?trail=ra
OGG> START DISTPATH dp1
OGG> INFO DISTPATH dp1

I’ve used wss:// (secure WebSocket) for the target because that’s what you want across any network you don’t fully trust … and it’s the protocol tied to the USERIDALIAS/password method above. The Receiver Service on the target picks the path up automatically; there’s nothing to configure there beyond having it running and the port reachable. That’s a real simplification over Classic’s Manager-plus-Collector dance.

Lets discuss a real time production cutover sequence, specially what comes fiorst and second and later on as it matters the most.

– Stop Classic Extract, let pump and Replicat drain fully:

GGSCI> STOP EXTRACT ext1
GGSCI> SEND EXTRACT pump1, LOGEND
GGSCI> INFO REPLICAT rep1

Note: Wait for Replicat to show At EOF, no more records to process. Don't rush this. Its important.

– Stop pump and Replicat, record final positions, stop Manager:

GGSCI> STOP EXTRACT pump1
GGSCI> STOP REPLICAT rep1
GGSCI> INFO REPLICAT rep1, SHOWCH
GGSCI> STOP MANAGER

– Start the MA Extract at the SCN where Classic stopped:

OGG> ALTER EXTRACT ext1, SCN 45892017334
OGG> START EXTRACT ext1

– Start the path, then the Replicat:

OGG> START DISTPATH dp1
OGG> START REPLICAT rep1

– Verify with real traffic before declaring victory 🙂

OGG> INFO ALL
OGG> STATS EXTRACT ext1, TOTAL
OGG> STATS REPLICAT rep1, TOTAL
OGG> LAG REPLICAT rep1

Then do an actual data comparison … Veridata if licensed, row-count-plus-checksum on your highest-churn tables if not. I’ve seen an Extract sit happily in RUNNING while silently missing a table because a wildcard didn’t match what someone assumed.

Some of the items that might surpirse you 🙂

Ports. Four to five per deployment plus ServiceManager. If firewall changes take two weeks in your shop, start that request before the migration. Reverse proxy. Oracle recommends fronting the services with NGINX so everything comes through one port … there’s a generator script under the reverse proxy tooling. Worth doing, but I get the migration working direct-to-ports first, then add the proxy. Trail compatibility. Classic-written trails can be read by MA Replicat, so if you need to, you can point an MA Replicat at old Classic trails to drain them. Useful escape hatch. Desupported parameters (Thanks for highlighting it @Alex Lima). Audit your files. 23ai removed Integrated Replicat, dropped Blowfish encryption, removed trace-table functionality, and replaced FILTERTABLE with EXCLUDEFILTERTABLE. If any of those are lurking in a parameter file, they’ll bite you.

adminclient is not GGSCI. ADD/START/STOP/INFO/STATS behave as expected; SEND MANAGER is gone; DBLOGIN is per-service and you’ll re-issue it more often than you’d like.

The migration itself is a few hours for a typical two-node config. The real time cost is elsewhere … updating monitoring, rewriting operational scripts against the REST API instead of parsing GGSCI output, retraining whoever runs the environment at 3am. Start that early. The Extract and Replicat will be fine. It’s the ecosystem around them that takes the time.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: goldengate, migration, oracle, renaps, replication | Leave a Comment »

Reading MySQL execution plans without guessing

Posted by FatDBA on July 12, 2026

Every DBA hits this situation eventually. A query that ran in 40 milliseconds last month now takes eleven minutes, nobody deployed anything, and the developer swears the table “isn’t even that big.” The instinct is to add an index and hope. That works often enough to be genuinely dangerous, because it teaches you nothing about why it worked, and the next time the same trick will quietly fail.

The alternative is to read what the optimizer is actually telling you. MySQL is not shy about it. It will hand you its plan, its estimates, and since 8.0.18 .. its real measured execution, if you know how to ask.

Everything below was run on MySQL 8.0.46 against a schema I built specifically to misbehave: 200,000 customers and 1,000,000 orders, with a status column that is dangerously skewed.

CREATE TABLE customers (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(120) NOT NULL,
  country      CHAR(2)      NOT NULL,
  signup_date  DATE         NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),
  KEY ix_country (country)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  INT UNSIGNED    NOT NULL,
  status       VARCHAR(12)     NOT NULL,
  amount       DECIMAL(10,2)   NOT NULL,
  created_at   DATETIME        NOT NULL,
  PRIMARY KEY (id),
  KEY ix_customer (customer_id),
  KEY ix_status (status)
) ENGINE=InnoDB;



-- The skew is the whole point:

status      rows_actual   pct
shipped        980123    98.012
pending         12043     1.204
cancelled        4876     0.488
refunded         2958     0.296

Now comes the main topic of discussion, How the optimizer picks the plan ?

MySQL’s optimizer is cost-based, and the machinery is simpler than people assume. There’s no plan cache learning from history, no adaptive feedback loop. Every compilation is a fresh search over possible strategies, and the cheapest estimated plan wins. Before it costs anything, MySQL rewrites the query. Constants get folded (price > 10 * 5 becomes price > 50). Constants get propagated — if you write WHERE a.id = 42 AND a.id = b.a_id, MySQL infers b.a_id = 42 and pushes that predicate straight into b. Simple views get merged into the outer query. And — this one bites people constantly .. an outer join gets silently converted to an inner join if the WHERE clause rejects NULLs on the inner table. Write LEFT JOIN orders o ... WHERE o.status = 'shipped' and you have written an inner join, whether you meant to or not, because a NULL o.status can never equal 'shipped'.

Then comes range analysis, and this is the part that matters most for the rest of this post. For each usable index, MySQL doesn’t just consult stored statistics. It performs an actual B tree search .. records_in_range() .. physically walking into the index to count how many rows fall inside your predicate’s range. This is real work, touching real pages, at plan time.

Join ordering is a greedy search, not exhaustive. Two conditions govern it: optimizer_search_depth (default 62) and optimizer_prune_level (default 1). For three or four tables MySQL will find something near optimal. For fifteen tables it will find a plan, and that plan may be mediocre. Counterintuitively, if you’re joining that many tables and getting bad plans, lowering optimizer_search_depth to 4 or 8 often produces better plans faster, because you stop the optimizer from burning its budget on a search space it can’t cover anyway.

Costs are computed from tunable constants in mysql.server_cost and mysql.engine_cost …. row_evaluate_cost, io_block_read_cost, and friends. You can change them. You almost definately shouldn’t.

Next lets talk about plan generation and tools we have –> Three tools, three different jobs, and people conflate them constantly.

EXPLAIN gives you the plan as a flat table. It does not run the query. It’s fast, it’s compact, and it’s what you reach for 90% of the time. EXPLAIN FORMAT=JSON gives you the same plan with everything EXPLAIN had to leave out .. the actual cost numbers, which key parts were used, which columns get read, and the exact condition attached to each table. Still doesn’t run the query. When EXPLAIN says something confusing, this is where you go to find out why. EXPLAIN ANALYZE (8.0.18+) actually executes the query and reports what really happened alongside what was predicted. This is the one that ends arguments. It’s also the one you should think twice about before running against production on a DELETE— though for SELECTs it’s just a query that costs you its own runtime.

Same query, all three.

mysql> EXPLAIN SELECT c.email, o.amount FROM customers c
    ->   JOIN orders o ON o.customer_id = c.id
    ->  WHERE o.status = 'refunded' AND c.country = 'JP'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: ref
possible_keys: ix_customer,ix_status
          key: ix_status
      key_len: 50
          ref: const
         rows: 2958
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY,ix_country
          key: PRIMARY
      key_len: 4
          ref: shop.o.customer_id
         rows: 1
     filtered: 5.00
        Extra: Using where



-- JSON format adds the details what were hidden
{
  "query_block": {
    "cost_info": { "query_cost": "3665.19" },
    "nested_loop": [
      { "table": {
          "table_name": "o",
          "access_type": "ref",
          "key": "ix_status",
          "used_key_parts": ["status"],
          "key_length": "50",
          "rows_examined_per_scan": 2958,
          "rows_produced_per_join": 2958,
          "filtered": "100.00",
          "cost_info": { "read_cost": "829.74", "eval_cost": "295.80",
                         "prefix_cost": "1125.54" }
      }},
      { "table": {
          "table_name": "c",
          "access_type": "eq_ref",
          "key": "PRIMARY",
          "key_length": "4",
          "ref": ["shop.o.customer_id"],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 147,
          "filtered": "5.00",
          "cost_info": { "prefix_cost": "3665.19" },
          "attached_condition": "(`shop`.`c`.`country` = 'JP')"
      }}
    ]
  }
}

prefix_cost is cumulative … 1125.54 after the first table, 3665.19 after both. rows_produced_per_join: 147 is the estimate flowing out of the join. attached_condition shows you exactly what’s being evaluated where, which is worth the verbosity all by itself. And now comes the truth time 🙂

mysql> EXPLAIN ANALYZE SELECT c.email, o.amount FROM customers c
    ->   JOIN orders o ON o.customer_id = c.id
    ->  WHERE o.status = 'refunded' AND c.country = 'JP'\G

-> Nested loop inner join  (cost=4286 rows=148) (actual time=1.18..37.4 rows=56 loops=1)
    -> Index lookup on o using ix_status (status='refunded')
         (cost=1035 rows=2958) (actual time=0.826..11.5 rows=2958 loops=1)
    -> Filter: (c.country = 'JP')
         (cost=0.999 rows=0.05) (actual time=0.00866..0.00866 rows=0.0189 loops=2958)
        -> Single-row index lookup on c using PRIMARY (id=o.customer_id)
             (cost=0.999 rows=1) (actual time=0.00842..0.00844 rows=1 loops=2958)

Read that inner block carefully, because it’s the single most misread thing in MySQL. rows=0.0189 loops=2958 does not mean MySQL found 0.0189 rows. It means the inner side was executed 2,958 times, and averaged 0.0189 rows per execution. Multiply: 0.0189 × 2958 ≈ 56, which is exactly the final row count. Same for timing … actual time=0.00866 is per loop, not total. The total time for that operation is roughly 0.00866 × 2958 ≈ 25ms. Estimated 148 rows, got 56. That’s a 2.6× overestimate, and I’ll come back to where it came from.

Next, lets check all of the columns and values used in the plan and what exact that means..

id : which SELECT this row belongs to. Same number across rows means same query block, and the order those rows appear in is the join order. Different numbers mean subqueries or unions, and a bigger id executes first.

select_type : SIMPLE for a plain query, PRIMARY for the outer part of one with subqueries, SUBQUERY, DERIVED, UNION, MATERIALIZED. If you see DEPENDENT SUBQUERY, tense up: it means the subquery reruns for every outer row.

table : the table, or a synthetic name like <derived2> for a derived table.

type : The access method. Most important column in the output; full section below.

possible_keys : indexes the optimizer considered. NULL here is diagnostic: it means no index could even theoretically serve this predicate, so before you tune anything, go look at whether the right index exists at all.

key : what it actually chose. When possible_keys lists three indexes and key picks one, the other two lost on cost. That’s a decision you can second-guess.

key_len : bytes of the index actually used. This is the column that catches partial index usage, and people ignore it because the calculation/maths looks like magic, but It isn’t:

Each column’s base size (INT = 4, BIGINT = 8)
+1 byte if the column is NULLable
For variable-length strings, +2 bytes for the length prefix
Character columns are counted in bytes, not characters — under utf8mb4 that’s 4 bytes per character

Check it against our real output. status is VARCHAR(12) NOT NULL under utf8mb4: 12 × 4 = 48 octets, no null byte, +2 for the length prefix = 50. And EXPLAIN reports key_len: 50. Exactly. ix_country on CHAR(2) gives 2 × 4 = 8, and yes, key_len: 8.

I built a throwaway table to prove the NULL byte is real …

INT NULL      -> key_len: 5     (4 + 1 null byte)
INT NOT NULL  -> key_len: 4
VARCHAR(10) NULL -> key_len: 43 (10*4 + 1 + 2)

Why care? Because on a composite index (a, b, c), key_len tells you how many of those columns are actually doing work. If key_len only accounts for a, then b and c are dead weight for this query and you’ve been fooling yourself about your index.

ref : what’s being compared against the indexed column. const for a literal, or a column name like shop.o.customer_id when the value comes from an earlier table in the join. This is how you read the data flow of a nested loop.

rows : estimated rows examined for this step. Estimated, and in a join it’s per iteration, not total.

filtered : percentage of those rows expected to survive the WHERE conditions that weren’t handled by the index. This is the column everyone skips and shouldn’t, because rows × filtered / 100 is what actually flows to the next table. In the join above, table c shows rows: 1, filtered: 5.00 … MySQL believes 5% of customers are in Japan. It isn’t 5%. There are eight countries in my data and JP is the rarest at about 2%. That 5.00 is a guess, and it’s precisely why the join estimate came out at 148 instead of 56.

Extra — everything else, and where the real diagnoses live.

Access types, which is best and the worst ??

const : at most one row, matched on a primary key or unique index against a literal. MySQL reads it once during optimization and substitutes it as a constant. You cannot do better.

EXPLAIN SELECT email FROM customers WHERE id = 42;
type: const   key: PRIMARY   key_len: 4   ref: const   rows: 1

eq_ref : one row from this table per row from the previous table, via PK or unique index. This is the ideal for the driven side of a join, and it’s what our join got:

type: eq_ref   key: PRIMARY   key_len: 4   ref: shop.o.customer_id   rows: 1

ref : A non-unique index lookup, may return many rows. Perfectly healthy. Our status='refunded' lookup is ref with rows: 2958.

range : index scan over a bounded range. BETWEEN, >, IN (...), LIKE 'foo%'.

EXPLAIN SELECT id FROM orders WHERE id BETWEEN 100 AND 500;
type: range   key: PRIMARY   key_len: 8   rows: 401   Extra: Using where; Using index

401 estimated, 401 actual. B-tree dives are good at this.

index : full scan of the entire index. Not a lookup. It’s ALL wearing a nicer coat, and it’s only cheap because the index is narrower than the table:

EXPLAIN SELECT COUNT(country) FROM customers;
type: index   key: ix_country   rows: 199488   Extra: Using index

ALL : Is a full table scan. Every row, from disk.

EXPLAIN SELECT * FROM orders WHERE amount > 500;
type: ALL   possible_keys: NULL   key: NULL   rows: 997152   filtered: 33.33

Note: possible_keys: NULL : there’s no index on amount, so this was never even a contest. And note rows: 997152 when the table holds exactly 1,000,000 rows. InnoDB’s row count is itself an estimate, sampled, not counted. Don’t trust it to the digit. A full scan is not automatically a bug, by the way. If you’re reading 98% of the table, a scan genuinely is cheaper than a million index lookups each followed by a random row fetch. The bug is a full scan on a selective predicate.

Now lets read ‘EXTRA’:

Using index : a covering index. Everything the query needs lives in the index; the table itself is never touched. This is the single biggest win available in the Extra column, and it’s easy to engineer. Watch this ..

-- before
EXPLAIN SELECT customer_id, status FROM orders WHERE status='refunded';
type: ref   key: ix_status   rows: 2958   Extra: NULL

ALTER TABLE orders ADD INDEX ix_status_cust (status, customer_id);

-- after
type: ref   key: ix_status_cust   rows: 2958   Extra: Using index

Same rows, same access type, but Extra flipped to Using index and we eliminated 2,958 random primary-key lookups.

Using where : MySQL is filtering rows after fetching them from the storage engine. Not fatal on its own. But Using where sitting next to type: ALL and a low filtered is the classic missing-index signature.

Using index condition : Index Condition Pushdown. Different from Using index, and the names are unhelpfully similar. ICP means the condition is evaluated at the index level so non-matching entries never trigger a row fetch. It’s a real optimization, not a warning.

Using temporary : an internal temp table was built, usually for GROUP BY or DISTINCT. In 8.0 it’s TempTable in memory up to temptable_max_ram (default 1GB), then spills to disk. Small ones are fine. Big ones are why your query fell off a cliff.

Using filesort : the results had to be sorted, because no index provided the required order. “Filesort” does not mean it hit disk; it may well sort entirely in memory within sort_buffer_size. Bad name, causes endless confusion. Still, it’s work you might be able to design away with the right index.

Both at once, which is the combination worth hunting for:

EXPLAIN SELECT c.country, SUM(o.amount) t
  FROM customers c JOIN orders o ON o.customer_id = c.id
 WHERE o.status = 'pending'
 GROUP BY c.country ORDER BY t DESC;

table: o   type: ref   key: ix_status   rows: 22536
Extra: Using temporary; Using filesort

The GROUP BY builds the temp table, and the ORDER BY SUM(o.amount) forces the filesort on top of it .. you cannot index your way out of ordering by an aggregate that doesn’t exist until the group is computed. Sometimes the answer is “this is inherent, accept it.” Knowing which it is, is the job.

Scenario one: the cardinality trap (and where the usual advice is wrong)

Here’s where I have to correct something I’ve written myself in the past.

The standard story goes: MySQL assumes uniform distribution, so on a skewed column it wildly misestimates, and histograms fix it. Let’s test that. Two queries, same column, wildly different selectivity:

-- refunded: 2,958 rows, 0.296% of table
type: ref   key: ix_status   rows: 2958   filtered: 100.00

-- shipped: 980,123 rows, 98% of table
type: ref   key: ix_status   rows: 498576   filtered: 100.00

For refunded, rows: 2958 :: the exact true count. Not close. Exact. So much for uniform-distribution assumptions. This is records_in_range() diving into the B-tree and counting for real. But look at shipped: estimated 498,576, and EXPLAIN ANALYZE reports the truth:

-> Index lookup on orders using ix_status (status='shipped')
   (cost=52812 rows=498576) (actual time=2.35..2136 rows=980123 loops=1)

980,123 actual against 498,576 estimated. A 1.97× underestimate … on the unselective value, not the selective one. The dive samples a bounded number of pages; over a huge range it extrapolates, and extrapolation drifts. So, surely a histogram fixes it?

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;
-- re-run, WITH histogram in place
type: ref   key: ix_status   rows: 498576   filtered: 100.00

Nothing changed. Identical estimate. This is the finding I did not expect, and it’s the most useful thing in this post: when a usable index exists, the index dive wins and the histogram is ignored for the rows estimate.m Histograms feed filtered, not rows. To see them work, take the index away:

-- ix_status dropped
type: ALL   possible_keys: NULL   rows: 997152   filtered: 0.30   Extra: Using where

-> Filter: (orders.status = 'refunded')  (cost=100912 rows=2949)
   (actual time=0.83..251 rows=2958 loops=1)
    -> Table scan on orders  (cost=100912 rows=997152)
       (actual time=0.738..200 rows=1e+6 loops=1)

filtered: 0.30 against a true 0.296%. Estimated 2,949 rows, actual 2,958. The histogram is superb — but only where the optimizer had nothing better.

The practical rule, then, is the opposite of the folklore. Build histograms on columns you are filtering on but have not indexed … typically because they’re low-cardinality and an index would be pointless, but they still need a decent filtered estimate to drive join ordering. Indexing the column and histogramming it is mostly wasted effort. One more trap while we’re in here. Look at the stored histogram:

"buckets": [
  ["base64:type254:Y2FuY2VsbGVk", 0.004594302009347028],
  ["base64:type254:cGVuZGluZw==",  0.01689858210334539],
  ["base64:type254:cmVmdW5kZWQ=",  0.019855833971430835],
  ["base64:type254:c2hpcHBlZA==",  1.0]
],
"histogram-type": "singleton",
"sampling-rate": 0.07373942163752957

Those are cumulative frequencies, not per-value. refunded is not 1.98% of the table … it’s 0.0199 − 0.0169 = 0.0030, i.e. 0.30%. The per-value frequency is the difference between consecutive buckets. I have watched people read that 1.0 on shipped and conclude the histogram is broken. And while we’re looking at bad statistics, check what InnoDB thinks ix_status contains:

INDEX_NAME    COLUMN_NAME    CARDINALITY
ix_status     status         3
ix_customer   customer_id    197646
PRIMARY       id             997152

Cardinality 3. There are four distinct statuses. The default 20 page sample (innodb_stats_persistent_sample_pages) missed one entirely. If you have a low-cardinality column with a rare value that matters, raise that setting for the table and re-analyze … otherwise the optimizer is reasoning about a value it doesn’t know exists.

Scenario two: join order, and why it’s the whole game

Nested-loop joins have a brutal asymmetry: the driving table’s row count becomes the loop count for everything after it. Get it backwards and you multiply your own pain. MySQL got our join right. It drove from orders (2,958 refunded rows) and probed customers by primary key:

-> Nested loop inner join  (cost=4286 rows=148) (actual time=1.18..37.4 rows=56 loops=1)
    -> Index lookup on o using ix_status (status='refunded')
         (cost=1035 rows=2958) (actual ... rows=2958 loops=1)
    -> Filter: (c.country = 'JP')
         (cost=0.999 rows=0.05) (actual ... rows=0.0189 loops=2958)

2,958 loops. 37.4ms. …. Now let’s force the mistake with STRAIGHT_JOIN, which pins join order to the order you wrote:

-> Nested loop inner join  (cost=7692 rows=202) (actual time=1.2..93.1 rows=56 loops=1)
    -> Index lookup on c using ix_country (country='JP')
         (cost=920 rows=4039) (actual ... rows=4039 loops=1)
    -> Filter: (o.status = 'refunded')
         (cost=1.2 rows=0.05) (actual ... rows=0.0139 loops=4039)
        -> Index lookup on o using ix_customer (customer_id=c.id)
             (cost=1.2 rows=4.79) (actual ... rows=5.03 loops=4039)

4,039 loops. 93.1ms. Identical 56 rows out, two and a half times the time. And the inner side is worse than the loop count suggests: each of those 4,039 loops now fetches ~5 orders and throws nearly all of them away (rows=5.03 in, rows=0.0139 surviving the filter). We’re reading roughly 20,000 order rows to keep 56.

The optimizer picked correctly here. It doesn’t always … and when it doesn’t, the reason is almost always upstream: a filtered percentage built on a guess. Recall that filtered: 5.00 on customers. MySQL had no histogram on country, so it fell back to a canned guess, and that guess is what produced the 148-row estimate against 56 actual. On this query the error was harmless. On a five-table join, an error like that at step one compounds through every subsequent step, and that is how you get a plan that’s wrong by four orders of magnitude. While we’re here: filtered: 33.33 on the amount > 500 scan earlier is the same phenomenon in its purest form. That number is not measured. It’s MySQL’s hardcoded 1/3 guess for a range condition on a column it knows nothing about. Any time you see 33.33, 11.11, or 5.00 in filtered, you are looking at a guess, not a statistic.

Selecting and validating indexes

Start with the predicate, not the query text. Equality columns go first in a composite index, then range columns, then columns needed only for ORDER BY or covering. The reason is mechanical: a B tree can only use one range column before the ordering breaks down for everything to its right.

Check key_len to confirm the index is being used as far as you think it is. If your index is (status, customer_id, created_at) and key_len comes back as 50, only status is doing work.

Aim for Using index in Extra where you can. Adding one column to an existing index to make it covering is often a bigger win than adding a whole new index … and it costs you far less on write throughput.

Then validate with EXPLAIN ANALYZE, and compare estimated against actual. A ratio inside about 10× is usually fine. Beyond that, ask why: stale stats (run ANALYZE TABLE), a sample too small to see your rare values (raise innodb_stats_persistent_sample_pages), or a filtered built on a guess (histogram the column … as long as it isn’t already indexed).

Finally, resist the urge to just add the index. Every index is a write tax on every INSERT, UPDATE, and DELETE against that table, forever. Before you create one, check sys.schema_unused_indexes and sys.schema_redundant_indexes … I have never once run those on a mature production system and not found something to drop.

So, in short, summary, EXPLAIN tells you the plan. FORMAT=JSON tells you the costs behind it. EXPLAIN ANALYZE tells you the truth, and the truth is the only one of the three you can act on with confidence. Read rows and filtered together, always, because rows × filtered / 100 is what actually flows downstream. Treat loops as a multiplier, and remember that rows= and actual time= on an inner node are per loop. And when filtered shows you 33.33 or 5.00, recognise it for what it is: MySQL shrugging.

Then go and check whether the optimizer’s guess matched reality. Usually it does. The eleven-second query is where it didn’t.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: SQL, performance, troubleshooting, mysql | Leave a Comment »

When MySQL OPTIMIZE TABLE made the file bigger instead of smaller and how old_alter_table solved the issue …

Posted by FatDBA on July 2, 2026

Every DBA has had that moment where a command looks simple on paper, but the database decides to teach you a lesson in production-style humility. For me, this happened during a MySQL InnoDB space reclaim activity. The task looked straightforward. We had a few large InnoDB tables where a lot of data had been deleted or moved over time. The logical data inside the table was much smaller than the physical .ibd file sitting on disk. So the goal was simple: rebuild the table, compact the data, and return unused space back to the filesystem.

In MySQL terms, this usually means running —–> OPTIMIZE TABLE XXXX;

The environment was MySQL Community Server 8.0.43. The tables were InnoDB tables, using innodb_file_per_table, which means each table had its own .ibd file on disk. That part is important because if the table is stored in its own file per table tablespace, rebuilding the table can physically shrink the .ibd file and return unused space back to the operating system … At least, that was the expectation. Before starting, we checked the table size from MySQL and from the operating system. For example, one test table, let’s call it appdata.customer_activity_history, had a physical .ibd file that was much larger than the active data inside the table.

The checks were simple and familiar:

SELECT NAME, SPACE_TYPE, ROW_FORMAT, PAGE_SIZE, ROUND(FILE_SIZE/1024/1024/1024,2) AS FILE_SIZE_GB, ROUND(ALLOCATED_SIZE/1024/1024/1024,2) AS ALLOCATED_SIZE_GB, 
STATE FROM information_schema.INNODB_TABLESPACES WHERE NAME = 'appdata/customer_activity_history';

-- And from the OS side:
du -sh /var/lib/mysql/appdata/customer_activity_history.ibd

The table had a clear gap. The .ibd file was large, and based on the data pattern, we expected a rebuild to reclaim a good amount of space. So we ran the usual command —> OPTIMIZE TABLE appdata.customer_activity_history

The command completed, and MySQL returned the familiar InnoDB message … “Table does not support optimize, doing recreate + analyze instead” .. BTW, this message is normal for InnoDB. It simply means MySQL is not doing a classic MyISAM style optimize. For InnoDB, MySQL translates the operation into a table rebuild and analyze style operation.

mysql> OPTIMIZE TABLE appdata.customer_activity_history;

+----------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                            | Op       | Msg_type | Msg_text                                                          |
+----------------------------------+----------+----------+-------------------------------------------------------------------+
| ppdata.customer_activity_history | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ppdata.customer_activity_history | optimize | status   | OK                                                                |
+----------------------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (3 hours 8 min 33.18 sec)

But then came the surprise. When we checked the .ibd file again, the space was not reclaimed as expected. In some cases, the file looked almost the same. In other cases, it actually increased. That is the kind of thing that makes you stop for a minute.

You run a command to reclaim space, wait for a long table rebuild to finish, and then the file gets bigger. Not exactly the kind of result you want to explain with confidence. The first reaction was to question the obvious things. Was the table really using InnoDB? Was innodb_file_per_table enabled? Was the file we checked really the correct .ibd file? Were there long-running transactions? Was there enough free disk space? Was this table partitioned? Were stats outdated? Was the table still holding more active data than expected?

All of those checks were important. Nothing looked wrong from a basic validation point of view. The table was InnoDB. The table had its own .ibd file. The table was accessible. There was enough disk space. The command completed successfully. But the physical reclaim was still not happening the way we expected.

At this point, the issue looked less like a data problem and more like a rebuild method problem. The fix came from changing how MySQL performed the rebuild. Instead of relying on the default optimized ALTER behavior, we forced MySQL to use the older copy style ALTER method by setting this session parameter before running the optimize command …. old_alter_table

Then we ran the optimize again in the same session:

SET old_alter_table=1;
OPTIMIZE TABLE appdata.customer_activity_history;

This small change made the difference … with old_alter_table=1, MySQL does not use the newer optimized ALTER processing method. It falls back to the older temporary table copy method. In simple words, MySQL creates a new copy of the table, copies the active data into it, rebuilds the indexes, and then renames the rebuilt table back into place. That copy style rebuild gave us the clean physical rebuild we were looking for.

After running the command with the session parameter enabled, we checked the .ibd file again and this time, the file size reduced as expected. That was the “okay, now we are getting somewhere” moment.

But one successful table is not enough. A DBA never trusts one clean result when dealing with large tables and filesystem reclaim. So we repeated the same process on a few more large InnoDB tables and for each table, we followed the same careful pattern. We checked the file size before starting. We checked available disk space. We made sure there were no obvious long-running transactions or blockers. We ran SET old_alter_table=1; in the same MySQL session. Then we ran OPTIMIZE TABLE. After it completed, we checked the .ibd file again from the OS level and validated that the table was still accessible.

The important part was not just that the command completed. The important part was that the physical file size actually reduced after the operation. That confirmed the workaround was repeatable. One detail that should not be missed is that old_alter_table is a session-level setting when set this way. It is not something you run once in one window and then assume it applies everywhere forever. If the MySQL session disconnects, or if you open a new MySQL session, you need to run it again before the next OPTIMIZE TABLE.

Another important point is disk space. This type of rebuild can temporarily require extra space because MySQL may need space for the rebuilt copy while the operation is running. So even though the final result may be a smaller .ibd file, you still need enough free filesystem space before starting. This is especially important when working with hundreds of GBs or TB-sized tables. This is also not something I would run blindly across many tables in parallel. Large InnoDB rebuilds can be heavy. They can consume I/O, generate temporary space usage, affect replication lag if replication is involved, and introduce metadata locking during parts of the operation. The more controlled approach is to run one table at a time, validate the result, then move to the next one.

The other lesson here is about expectations. OPTIMIZE TABLE sounds like a simple maintenance command, but with InnoDB it is really a table rebuild operation. And with large InnoDB tables, the rebuild method matters. The command can complete successfully from MySQL’s point of view, but that does not always mean the physical reclaim result will match what you expected. In our case, the standard optimize path did not reclaim space correctly and even increased the .ibd file size in some cases. After we added the session command SET old_alter_table=1; immediately before OPTIMIZE TABLE, MySQL used the copy-style rebuild method, and the .ibd file started shrinking as expected. The final version of the approach was simple ..

-- In the session run below param 
SET old_alter_table=1;

-- Now all set to run the optimize command and to avoid the issue
OPTIMIZE TABLE appdata.customer_activity_history;

-- Then validate from the OS:
du -sh /var/lib/mysql/appdata/customer_activity_history.ibd

-- And validate from MySQL:
SELECT
    table_schema,
    table_name,
    ROUND((data_length + index_length)/1024/1024/1024, 2) AS logical_gb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'appdata'
  AND table_name = 'customer_activity_history';

So if you ever run OPTIMIZE TABLE on a large MySQL InnoDB table and the .ibd file does not shrink, or worse, it grows, do not panic immediately. First, verify the basics. Confirm the storage engine, confirm innodb_file_per_table, check the physical file, check the logical size, and make sure you have enough disk space. It is a small setting, but in this case, it made a big difference.

And yes, sometimes the most useful DBA lessons start with a command doing the exact opposite of what you expected.Then, if the default rebuild path is not giving the expected reclaim, test the session-level copy-style rebuild method carefully

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: defrag, innodb, mysql, optimize, performance | Leave a Comment »

Using pathMappingTemplate and fileNameMappingTemplate in Oracle GoldenGate 23ai DAA

Posted by FatDBA on July 1, 2026

One interesting issue we hit during one of the GoldenGate DAA to Microsoft Fabric Lakehouse testing was not really a replication failure. GoldenGate was running fine, the Replicat was not abended, the trail was being read, and stats were showing that records were getting processed. So from a GoldenGate side, everything looked healthy. But when we checked the Fabric Lakehouse, the files were not showing up where we expected them.

That made the issue a little tricky at first. Usually, when something is wrong with GoldenGate, we expect to see an abend, an authentication error, a trail read issue, or something obvious in the report file. In this case, GoldenGate was doing its job. The problem was that the output path being used by the OneLake handler was not the folder structure the team wanted. The target was Microsoft Fabric Lakehouse, and the GoldenGate property file had the usual OneLake entries: workspace, lakehouse, tenant ID, OneLake endpoint, app registration client ID/secret, classpath, and datatype mapping options. Something like this:

gg.target=fabric_lakehouse

gg.eventhandler.onelake.workspace=<workspace_name>
gg.eventhandler.onelake.lakehouse=<lakehouse_name>
gg.eventhandler.onelake.tenantId=<tenant_id>
gg.eventhandler.onelake.endpoint=https://onelake.dfs.fabric.microsoft.com

gg.eventhandler.onelake.clientId=<client_id>
gg.eventhandler.onelake.clientSecret=<client_secret>

gg.handler.onelake.format.enableDecimalLogicalType=true
gg.handler.onelake.format.mapOracleNumbersAsStrings=false
gg.handler.onelake.format.mapLargeNumbersAsStrings=false
gg.handler.onelake.format.enableTimestampLogicalType=true
gg.format.timestamp=yyyy-MM-dd HH:mm:ss.SSS

With this basic configuration, files were getting created, but GoldenGate was using its own default folder pattern. That default structure was not what the downstream team wanted to consume. The expectation was simple. We wanted the files to go under a clean Lakehouse folder, something like:

Files/<business-folder>/<schema>/<table>

But the actual folder structure was coming out differently. At one point, we saw folder names with .schema in them, like:

Files/<business-folder>/<schema>.schema/<table>

At first, it was easy to think this might be a Fabric artifact or something automatically added by GoldenGate. But after checking the property file carefully, we realized the .schema suffix was coming from our own path mapping template. The property had been set like this:

gg.eventhandler.onelake.pathMappingTemplate=${catalogname}.lakehouse/Files/<business-folder>/${schemaname}.schema/${tablename}

So GoldenGate was doing exactly what we told it to do. Because the template had ${schemaname}.schema, it created folders with .schema in the name. The fix was simple once we identified it. We removed .schema from the template:

gg.eventhandler.onelake.pathMappingTemplate=${catalogname}.lakehouse/Files/<business-folder>/${schemaname}/${tablename}

After this change, the Lakehouse folder structure became much cleaner … Files/<business-folder>/<schema>/<table> and that solved the issue.

The second feedback was around parquet file names. The files were being generated with the GoldenGate process name as a prefix. That can be useful in some cases, but here the team wanted a cleaner name based only on schema, table, and timestamp. So instead of file names like this —> <replicat_name>_<schema>.<table>_<timestamp>.parquet and the expected format was —> <schema>.<table>_<timestamp>.parquet

To control that, I’ve added this property:

gg.eventhandler.onelake.fileNameMappingTemplate=${schemaname}.${tablename}_${currentTimestamp[yyyy-MM-dd_HH-mm-ss.SSS]}.parquet

With that, the file names became much easier to understand from the Fabric side. The final important part of the property file looked like this:

gg.eventhandler.onelake.pathMappingTemplate=${catalogname}.lakehouse/Files/<business-folder>/${schemaname}/${tablename}
gg.eventhandler.onelake.fileNameMappingTemplate=${schemaname}.${tablename}_${currentTimestamp[yyyy-MM-dd_HH-mm-ss.SSS]}.parquet

Once those changes were made, we restarted the target process. For a normal CDC flow, a stop/start would be enough for new files to follow the new format. But because this was a test and we wanted to validate the full output from the beginning, we cleaned the old test folder in Lakehouse and replayed the trail from the start. That is an important point. If you are testing folder or file naming changes and you do not clean the previous output, it can become confusing very quickly. Old files and new files can sit together, and then it becomes hard to tell which setting produced which folder or file.

The way we diagnosed this was also a good reminder. We did not start by changing random properties. First, we confirmed GoldenGate was actually processing data. Then we checked the target Lakehouse. Since files were being created, we knew authentication and basic OneLake writing were working. That narrowed the issue down to path and file naming.

In the end, this was not a GoldenGate failure. It was a configuration alignment issue between GoldenGate’s OneLake handler and the folder structure expected by the Fabric team. The main lesson is: when GoldenGate DAA is writing to Fabric Lakehouse and files are landing in the wrong place, check these two properties first

gg.eventhandler.onelake.pathMappingTemplate and gg.eventhandler.onelake.fileNameMappingTemplate

Small strings inside those templates make a big difference. If the template contains .schema, the folder will contain .schema. If the file name template includes the process name, the file will include the process name. GoldenGate is very literal here.

Once we controlled both the path and file name templates, the output started landing exactly where we wanted it in the Lakehouse, with a much cleaner structure for the downstream team.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: azure, fabric, goldengate, lakehouse, mirroreddatabase, oracle, oracle-database, replication | Leave a Comment »

Oracle AI database 26ai .. unified hybrid Vector Search is the real deal

Posted by FatDBA on April 11, 2026

If you’ve been following Oracle’s release cadence, you already know that Oracle Database 23ai laid the groundwork for a lot of the AI native stuff … the VECTOR data type, AI Vector Search, Select AI, all of it. But 26ai? Oracle didn’t just iterate. They rebranded, re-architected in places, and packed enough into this release to justify calling it a generational step forward.

Now, there are a lot of features in 26ai. The release covers AI Vector Search, AI for Database Management, AI for Data Development, AI for Application Development, and AI for Analytics …it’s a broad canvas. But I want to focus today on the one feature that I think is most immediately useful to real practitioners doing real work: Unified Hybrid Vector Search.

Oracle AI Vector Search allows you to use simple, intuitive SQL to perform similarity search on vectors and freely combine vectors with relational, text, JSON, and other data types within the same query. You can take complete control of the search accuracy your application requires by specifying the target accuracy as a simple percentage.

Under the hood, there are two index types doing the heavy lifting:

HNSW (Hierarchical Navigable Small World) … the in memory graph based index, fastest option Oracle offers for similarity search.

IVF (Inverted File Index) … better for massive datasets where you can’t hold everything in memory.

And one very important thing 26ai adds that 23ai didn’t fully have: HNSW Index now supports DML on tables that have an HNSW index built on the vector column. Vector search queries using the HNSW Index will see transactionally consistent results based on their read snapshot. Transactional consistency is guaranteed even on Oracle RAC where the HNSW Index is duplicated on all instances in the cluster.

This was a real blocker before. You couldn’t write to a table while it had an HNSW index. Now you can .. and your search results are ACID consistent. That’s production grade behavior, not a poc.

I’m going to walk through a realistic DBA use case. We’re building a searchable error log knowledge base. The goal is to search it semantically … but filter by error category and severity at the sql level simultaneously.

Lets setup the table …

-- Drop and recreate for clean demo
DROP TABLE dba_error_kb PURGE;

CREATE TABLE dba_error_kb (
    id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    error_code   VARCHAR2(20),
    error_desc   VARCHAR2(4000),
    category     VARCHAR2(50),    -- e.g. CONNECTIVITY, MEMORY, IO, LOCKING
    severity     NUMBER(1),       -- 1=Low, 2=Medium, 3=High, 4=Critical
    embedding    VECTOR           -- will store the semantic vector
);

Table created.


-- load some date intio tis
INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-12541', 
        'TNS no listener. The listener process is not running on the remote host.', 
        'CONNECTIVITY', 3);

1 row created.


INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-04031', 
        'Unable to allocate memory in the shared pool. Consider increasing SGA_TARGET or flushing the shared pool.',
        'MEMORY', 4);

1 row created.


INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-00060', 
        'Deadlock detected while waiting for resource. A deadlock cycle was found between sessions.',
        'LOCKING', 3);

1 row created.


INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-12154',
        'TNS could not resolve the connect identifier. Check tnsnames.ora or LDAP configuration.',
        'CONNECTIVITY', 2);
1 row created.


INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-01555',
        'Snapshot too old. Rollback segment too small or long-running query encountering wrap-around.',
        'IO', 3);

1 row created.


INSERT INTO dba_error_kb (error_code, error_desc, category, severity)
VALUES ('ORA-00257',
        'Archiver error. Connect internal only until freed. Archive log destination full.',
        'IO', 4);

1 row created.



COMMIT;

Lets generate some embeddings and or this, you need an ONNX embedding model loaded into the database. Oracle ships a compatible model called all_MiniLM_L12_v2 … or you can use their DBMS_VECTOR API to call an external provider (OCI Generative AI, OpenAI, Cohere, etc.). Here’s the in-database ONNX path:

-- Assumes model is already loaded via DBMS_VECTOR.LOAD_ONNX_MODEL
-- Update column with generated embeddings

UPDATE dba_error_kb
SET embedding = vector_embedding(
    all_minilm_l12_v2 
    USING error_desc AS data
);

6 rows updated.


COMMIT;

Commit complete.



SELECT id, error_code, 
       VECTOR_DIMS(embedding)   AS dims,
       VECTOR_NORM(embedding)   AS l2_norm
FROM   dba_error_kb
ORDER BY id;

ID  ERROR_CODE   DIMS  L2_NORM
--  -----------  ----  -------
1   ORA-12541    384   1.0
2   ORA-04031    384   1.0
3   ORA-00060    384   1.0
4   ORA-12154    384   1.0
5   ORA-01555    384   1.0
6   ORA-00257    384   1.0

6 rows selected.

384 dimensions, unit-normalized (L2 norm = 1). Good. That’s the MiniLM signature.

BNow lets build the HNSW Vector Index

CREATE VECTOR INDEX dba_errors_hnsw_idx
ON dba_error_kb (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Index Created.


SQL> COLUMN index_name         FORMAT A22
SQL> COLUMN index_organization FORMAT A28
SQL> COLUMN distance_type      FORMAT A14
SELECT index_name, 
       index_organization,
       num_vectors,
       distance_type,
       index_dimensions,
       default_accuracy
FROM   v$vector_index
WHERE  index_name = 'DBA_ERRORS_HNSW_IDX';


INDEX_NAME            INDEX_ORGANIZATION      NUM_VECTORS  DISTANCE_TYPE  INDEX_DIMENSIONS  DEFAULT_ACCURACY
--------------------  ----------------------  -----------  -------------  ----------------  ----------------
DBA_ERRORS_HNSW_IDX  INMEMORY NEIGHBOR GRAPH  6           COSINE         384               95

Let’s search for errors related to “database cannot connect to remote host”:

SELECT e.error_code,
       e.category,
       e.severity,
       e.error_desc,
       VECTOR_DISTANCE(
           e.embedding,
           vector_embedding(all_minilm_l12_v2 USING 'database cannot connect to remote host' AS data),
           COSINE
       ) AS similarity_distance
FROM   dba_error_kb e
ORDER BY similarity_distance
FETCH FIRST 3 ROWS ONLY;


ERROR_CODE  CATEGORY      SEVERITY  DISTANCE  ERROR_DESC
----------  ------------  --------  --------  -----------------------------------------------
ORA-12541   CONNECTIVITY  3         0.1821    TNS no listener...
ORA-12154   CONNECTIVITY  2         0.2104    TNS could not resolve the connect identifier...
ORA-00060   LOCKING       3         0.4612    Deadlock detected while waiting for resource...

3 rows selected.

Semantically reasonable. But notice ORA-00060 sneaking in at rank 3? A deadlock error has nothing to do with connectivity … but the phrase “waiting for resource” has some embedding overlap with network-waiting concepts. This is exactly the false positive problem I mentioned earlier.

Hybrid Search —-> Semantic + Relational Filter Together … Now let’s be precise. We want connectivity errors only, and only severity 2 or higher:

SQL> SELECT e.error_code,
  2         e.category,
  3         e.severity,
  4         e.error_desc,
  5         VECTOR_DISTANCE(
  6             e.embedding,
  7             vector_embedding(all_minilm_l12_v2 USING 'database cannot connect to remote host' AS data),
  8             COSINE
  9         ) AS similarity_distance
 10  FROM   dba_error_kb e
 11  WHERE  e.category  = 'CONNECTIVITY'
 12  AND    e.severity >= 2
 13  ORDER BY similarity_distance
 14  FETCH FIRST 3 ROWS ONLY;

ERROR_CODE CATEGORY      SEVERITY SIMILARITY_DISTANCE ERROR_DESC
---------- ------------ -------- ------------------- ---------------------------------------------
ORA-12541  CONNECTIVITY         3              0.1821 TNS no listener. The listener process is
                                                  not running on the remote host.

ORA-12154  CONNECTIVITY         2              0.2104 TNS could not resolve the connect
                                                  identifier. Check tnsnames.ora or LDAP
                                                  configuration.

2 rows selected.

Clean. Two results. Both connectivity. Both relevant. No locking errors, no memory errors … even though they might be semantically adjacent in the embedding space.

The application does not need to know it is running vector search. It is just SQL. That line keeps hitting different the more you think about it. Your application devs write a WHERE clause and an ORDER BY. No vector SDK, no external search tier, no Pinecone license.

Full Hybrid Index (Text + Vector in One Index) … For document-heavy workloads .. PDFs, knowledge bases, internal wikis .. 26ai also gives you a combined hybrid vector index using DBMS_HYBRID_VECTOR. This is for when you want BM25 style keyword scoring AND semantic scoring merged into one relevance score:

SQL> BEGIN
  2      ctx_ddl.create_preference('KB_DATASTORE', 'DIRECT_DATASTORE');
  3  END;
  4  /

PL/SQL procedure successfully completed.



SQL> CREATE SEARCH INDEX dba_kb_hybrid_idx
  2  ON dba_error_kb (error_desc)
  3  FOR HYBRID VECTOR SEARCH
  4  PARAMETERS ('
  5      EMBEDDING MODEL all_minilm_l12_v2
  6      VECTOR INDEX TYPE HNSW
  7      DISTANCE COSINE
  8      ACCURACY 95
  9  ');

Index created.


SQL> SELECT *
  2  FROM   TABLE(
  3      DBMS_HYBRID_VECTOR.SEARCH(
  4          p_index_name    => 'DBA_KB_HYBRID_IDX',
  5          p_search_text   => 'listener not reachable on remote server',
  6          p_top_k         => 3,
  7          p_vector_weight => 0.7
  8      )
  9  );

ERROR_CODE CATEGORY      SEVERITY SCORE    ERROR_DESC
---------- ------------ -------- -------- -----------------------------------------------
ORA-12541  CONNECTIVITY         3   0.9321 TNS no listener. The listener process is not
                                           running on the remote host.

ORA-12154  CONNECTIVITY         2   0.7814 TNS could not resolve the connect identifier.
                                           Check tnsnames.ora or LDAP configuration.

ORA-00257  IO                   4   0.2148 Archiver error. Connect internal only until
                                           freed. Archive log destination full.

3 rows selected.

The p_vector_weight parameter is the tuning knob. Set it to 1.0 and you’re doing pure semantic search. Set it to 0.0 and you’re doing pure keyword search. Anything in between blends both scores. That’s a powerful control surface to expose to end users or tune per use case.

AI Vector Search now supports custom, user-defined distance metrics. Proprietary or domain specific distance metrics can be used in addition to the standard built n euclidean, cosine, and dot Product. User defined vector distance functions allow users to create their own custom metrics using JavaScript functions. Niche but powerful for specialized domains …. think genomics, financial signals, or custom similarity scoring for enterprise knowledge graphs.

Oracle has launched Select AI Agent … an in-database framework for building, deploying, and managing autonomous agentic AI workflows. This one’s early stage but worth watching. Agentic AI where the orchestration logic lives inside the database, with full access to your relational data and governance controls, is a compelling architecture alternative to building langchain pipelines outside.

Data Annotations help explain the purpose, characteristics, and semantics of data to AI. This additional information helps AI generate better applications and provide more accurate responses to natural language questions. Think of it as metadata enrichment that makes your schema more legible to LLMs … useful for Select AI natural language query scenarios.

My Take ,…. There’s genuinely no reason to sit on 23.8 or 23.9 at this point.

For the DBAs still on 19c thinking about the migration path ….Oracle supports direct upgrades to 26ai without requiring an intermediate migration to 23ai. This compresses migration effort and improves timelines for modernization projects.

The Unified Hybrid Vector Search is the feature I’d put in front of any architect today. It’s not hype. The ability to do semantically intelligent search without leaving SQL, without a separate vector database tier, without a data pipeline into Pinecone or Weaviate …that is genuinely useful. The HNSW transactional consistency fix alone makes this ready for production workloads that 23.5 couldn’t handle cleanly.

So, go for it .. looad a model. Build one vector table. Write one hybrid query. You’ll see what I mean pretty quickly 🙂

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

Why oracle’s optimizer has been getting smarter for 15 years and what 26ai version actually adds

Posted by FatDBA on March 31, 2026

Every bad execution plan you’ve ever debugged traces back to the same root cause. The optimizer made a wrong guess about how many rows an operation would return ..and built an entire plan on top of that wrong number.

That number is called cardinality. It’s the estimated row count for each operation in your plan. Get it right and the optimizer picks the right join order, the right join method, the right access path. Get it wrong and you get a nested loops join against a table that returns 500,000 rows when the optimizer thought it was 12. You’ve seen this plan. It hurt.

Oracle has been progressively solving this problem for 15+ years. The story isn’t a single breakthrough ..it’s a series of increasingly smarter mechanisms, each one handling a class of estimation problem that the previous one couldn’t.

Here’s the full honest picture, ending with what 26ai actually adds.

Oracle 10g .. Dynamic Sampling The optimizer noticed when stats were missing or insufficient and sampled the data at parse time to get a rough estimate. Controlled by OPTIMIZER_DYNAMIC_SAMPLING. Blunt instrument, but better than pure guesswork.

Oracle 11g .. Cardinality Feedback The optimizer started comparing its estimates to reality after execution. If it estimated 50 rows and got 50,000, it stored the real number in the SGA and flagged the statement for re-optimization on the next execution. The estimate corrected itself over time. The problem: stored in SGA only — lost on restart, lost when the cursor aged out.

Oracle 12c …The Big Jump Three things landed together:

Statistics Feedback (renamed from Cardinality Feedback): same learning mechanism, better persistence
Adaptive Plans: the optimizer could now switch join methods mid-execution .. starting with Nested Loops based on its estimate, and switching to Hash Join live if actual rows exceeded the threshold. The final plan was then fixed for subsequent executions
SQL Plan Directives: when a misestimate was detected, the optimizer created a persistent directive (stored in SYSAUX, survives restarts) that told future parses: “when you see this predicate pattern, gather dynamic statistics first”. Directives are cross-statement … query’s lesson protects another with the same predicate pattern

Oracle 19c/23ai ..Automation at Scale Automatic SQL Tuning Sets (ASTS), Automatic SPM, and Real-Time SPM turned the individual learning mechanisms into a system-level feedback loop. The database wasn’t just learning from single statements .. it was maintaining plan stability across the entire workload automatically.

Oracle 26ai (23.8 RU) .. The Specific New Additions Two documented, named improvements to cardinality estimation:

Dynamic Statistics for PL/SQL Functions : a new parameter plsql_function_dynamic_stats giving fine-grained control over whether PL/SQL functions called inside SQL can participate in dynamic statistics sampling at parse time. Previously the optimizer treated PL/SQL functions as black boxes with unknowable return cardinality. Now it can sample them.
PL/SQL to SQL Transpiler : when enabled, the optimizer inlines eligible PL/SQL functions directly into SQL at parse time, eliminating the black box entirely. The optimizer can now see and estimate the underlying SQL expression rather than guessing at what a function returns.

Plus the general continued improvement of ML-informed cost models inside the optimizer engine .. real, but not a named switchable feature.

Now let’s see all of this in the plan output where it actually matters and I will do a quick demo — The single most important diagnostic habit in Oracle performance work. The GATHER_PLAN_STATISTICS hint tells the optimizer to track actual row counts during execution, then ALLSTATS LAST in DBMS_XPLAN surfaces them alongside the estimates.

-- Prereqs (run as SYS)
GRANT ADVISOR TO sh;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO sh;

CONN sh/sh

-- Set output format for readable plans
SET LINESIZE 200
SET PAGESIZE 10000
SET LONG 100000

-- Run the query with stats collection enabled
SELECT /*+ GATHER_PLAN_STATISTICS */
  c.cust_state_province,
  COUNT(*)           AS num_orders,
  SUM(s.amount_sold) AS revenue
FROM   sales     s
JOIN   customers c ON s.cust_id = c.cust_id
WHERE  c.cust_state_province = 'CA'
AND    c.cust_income_level   = 'G: 130,000 - 149,999'
GROUP  BY c.cust_state_province;



SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id          => NULL,
  cursor_child_no => 0,
  format          => 'ALLSTATS LAST +COST'
));
```

**Output — before extended statistics exist:**
```
Plan hash value: 3421987654

-------------------------------------------------------------------------------------------
| Id | Operation            | Name      | Starts | E-Rows | A-Rows | Cost  | Buffers |
-------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |           |      1 |        |      1 |  1891 |    1533 |
|  1 |  HASH GROUP BY       |           |      1 |      1 |      1 |  1891 |    1533 |
|* 2 |   HASH JOIN          |           |      1 |     17 |    127 |  1890 |    1533 |  <- E:17, A:127
|* 3 |    TABLE ACCESS FULL | CUSTOMERS |      1 |     17 |    127 |   406 |    1213 |  <- E:17, A:127
|   4|    PARTITION RANGE   |           |      1 |    918K|    918K|  1459 |     320 |
|   5|     TABLE ACCESS FULL| SALES     |     28 |    918K|    918K|  1459 |     320 |
-------------------------------------------------------------------------------------------

Predicate Information:
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   3 - filter("C"."CUST_STATE_PROVINCE"='CA'
          AND "C"."CUST_INCOME_LEVEL"='G: 130,000 - 149,999')

E-Rows: 17. A-Rows: 127. That’s a 7.5x underestimate.

The optimizer assumed cust_state_province = 'CA' and cust_income_level = 'G: 130,000 - 149,999' were independent. They’re not — they’re correlated. California has a disproportionate number of high-income customers in this dataset. The optimizer applied the selectivity of each predicate independently, multiplied them, and got the wrong answer.

This is the classic multi-column predicate correlation problem. The fix is extended statistics.

Lets try to fix it via extended statistics: Extended statistics (column groups) teach the optimizer about correlated columns. One DBMS_STATS call, no schema changes.

-- Create a column group for the two correlated columns
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
  ownname  => 'SH',
  tabname  => 'CUSTOMERS',
  extension => '(CUST_STATE_PROVINCE, CUST_INCOME_LEVEL)'
) AS col_group_name
FROM DUAL;

-- COL_GROUP_NAME
-- SYS_STUFBF#JKQM8F3GTPA7XDE9  (system-generated name)

-- Now gather stats to populate the column group
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname    => 'SH',
  tabname    => 'CUSTOMERS',
  method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);


---- Lets re runn the same Sql.
SELECT /*+ GATHER_PLAN_STATISTICS */
  c.cust_state_province,
  COUNT(*)           AS num_orders,
  SUM(s.amount_sold) AS revenue
FROM   sales     s
JOIN   customers c ON s.cust_id = c.cust_id
WHERE  c.cust_state_province = 'CA'
AND    c.cust_income_level   = 'G: 130,000 - 149,999'
GROUP  BY c.cust_state_province;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id          => NULL,
  cursor_child_no => 0,
  format          => 'ALLSTATS LAST +COST'
));


After extended statistics:
Plan hash value: 3421987654

-------------------------------------------------------------------------------------------
| Id | Operation            | Name      | Starts | E-Rows | A-Rows | Cost  | Buffers |
-------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |           |      1 |        |      1 |  1891 |    1533 |
|  1 |  HASH GROUP BY       |           |      1 |      1 |      1 |  1891 |    1533 |
|* 2 |   HASH JOIN          |           |      1 |    124 |    127 |  1890 |    1533 |  <- E:124, A:127
|* 3 |    TABLE ACCESS FULL | CUSTOMERS |      1 |    124 |    127 |   406 |    1213 |  <- Near perfect
|   4|    PARTITION RANGE   |           |      1 |    918K|    918K|  1459 |     320 |
|   5|     TABLE ACCESS FULL| SALES     |     28 |    918K|    918K|  1459 |     320 |
-------------------------------------------------------------------------------------------

E-Rows went from 17 to 124. Actual is 127. That’s less than 3% off.

Same plan hash .. same shape. But now the cost model is working from accurate numbers. In a more complex query, this difference in estimated rows would change join order, join method, and index decisions.

nEXT, Lets see SQL Plan directives and see watching optimizer learn.

When the optimizer detects a cardinality misestimate during execution, it creates a SQL Plan Directive — a persistent instruction stored in SYSAUX telling future parses to gather dynamic statistics for this predicate pattern. You can watch this happen.

First, drop the extended stats so the misestimate recurs:

-- Reset: drop the column group
EXEC DBMS_STATS.DELETE_EXTENDED_STATS(
  ownname   => 'SH',
  tabname   => 'CUSTOMERS',
  extension => '(CUST_STATE_PROVINCE, CUST_INCOME_LEVEL)'
);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'CUSTOMERS');

-- lets fliush the SP and re-run.

-- As SYS (flush shared pool in test environment only)
ALTER SYSTEM FLUSH SHARED_POOL;

CONN sh/sh

-- Run with stats collection
SELECT /*+ GATHER_PLAN_STATISTICS */
  c.cust_state_province,
  COUNT(*), SUM(s.amount_sold)
FROM   sales s
JOIN   customers c ON s.cust_id = c.cust_id
WHERE  c.cust_state_province = 'CA'
AND    c.cust_income_level   = 'G: 130,000 - 149,999'
GROUP  BY c.cust_state_province;


-- Lets see if the directive was crwated. 

-- Check for new SQL Plan Directives on CUSTOMERS
SELECT d.directive_id,
       d.type,
       d.state,
       d.auto_drop,
       d.created,
       o.object_name,
       o.subobject_name  AS column_name
FROM   dba_sql_plan_directives     d
JOIN   dba_sql_plan_dir_objects    o
       ON d.directive_id = o.directive_id
WHERE  o.object_name = 'CUSTOMERS'
ORDER  BY d.created DESC;


Output — directive created after the misestimate:

DIRECTIVE_ID  TYPE             STATE   AUTO_DROP CREATED              OBJECT  COLUMN_NAME
------------  ---------------  ------  --------- -------------------  ------  -------------------
8273641920    DYNAMIC_SAMPLING USABLE  YES        2026-03-29 14:33:12 CUSTOMERS CUST_STATE_PROVINCE
8273641920    DYNAMIC_SAMPLING USABLE  YES        2026-03-29 14:33:12 CUSTOMERS CUST_INCOME_LEVEL

The optimizer created a directive covering both columns … it noticed the multi-column predicate correlation caused a misestimate and now knows to sample dynamically next time it sees this pattern. Run the query a second time:

SELECT /*+ GATHER_PLAN_STATISTICS */
  c.cust_state_province,
  COUNT(*), SUM(s.amount_sold)
FROM   sales s
JOIN   customers c ON s.cust_id = c.cust_id
WHERE  c.cust_state_province = 'CA'
AND    c.cust_income_level   = 'G: 130,000 - 149,999'
GROUP  BY c.cust_state_province;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  format => 'ALLSTATS LAST +NOTE'
));


At the bottom of the plan output:
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

The optimizer is now dynamically sampling at parse time because the directive told it to. The cardinality estimate will be much closer to reality on this execution.

Now new in 26ai ..The SQL aanalysis report .. This is the part that’s genuinely new in 26ai. Previously you had to know to look at E-Rows vs A-Rows yourself. The SQL Analysis Report .. surfaced directly in DBMS_XPLAN.DISPLAY_CURSOR output …flags these problems inline without you having to hunt for them.

-- The standard DISPLAY_CURSOR call — no extra parameters needed
-- SQL Analysis Report appears automatically in 26ai when issues exist

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id          => NULL,
  cursor_child_no => 0,
  format          => 'ALLSTATS LAST +COST'
));


In Oracle 26ai, after the standard execution plan output, you now see:

SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
--------------------------------------------------------------------------------
3 - SEL$1 / "C"@"SEL$1"
  - The following columns have predicates which prevent their use as keys
    in an index range scan. Consider rewriting the predicates or creating
    column group statistics.
    "CUST_STATE_PROVINCE", "CUST_INCOME_LEVEL"

The optimizer is telling you directly: these two columns in combination are causing an estimation problem, and column group statistics would fix it. You no longer have to derive this by comparing E-Rows and A-Rows yourself. It’s surfaced automatically in the plan output.

That’s a real DBA quality-of-life improvement. The diagnosis that used to take 10 minutes of plan reading is now one line in your standard plan output.

Okay next is Dynamic Stats for PL/SQL Functions …. This is the specific new documented feature in 26ai (RU 23.8). Consider a query that filters through a PL/SQL function:

-- A function the optimizer previously couldn't estimate
CREATE OR REPLACE FUNCTION sh.get_high_value_threshold
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN 1000;
END;
/

-- Query using the function in a predicate
SELECT /*+ GATHER_PLAN_STATISTICS */
  COUNT(*),
  SUM(amount_sold)
FROM   sh.sales
WHERE  amount_sold > sh.get_high_value_threshold();


Before 26ai (or with `plsql_function_dynamic_stats = 'OFF'`):

The optimizer treats `get_high_value_threshold()` as a black box. It has no idea what value the function returns, 
so it can't estimate selectivity. It either guesses based on defaults or uses a very conservative estimate.

| Id | Operation            | Name  | E-Rows | A-Rows |
|  0 | SELECT STATEMENT     |       |        |      1 |
|  1 |  SORT AGGREGATE      |       |      1 |      1 |
|* 2 |   PARTITION RANGE ALL|       |   9188 |  12116 |  <- Rough guess
|*  3|    TABLE ACCESS FULL | SALES |   9188 |  12116 |

In 26ai with plsql_function_dynamic_stats = 'ON':

-- Enable dynamic stats for PL/SQL functions (session level)
ALTER SESSION SET plsql_function_dynamic_stats = 'ON';

-- Rerun
SELECT /*+ GATHER_PLAN_STATISTICS */
  COUNT(*),
  SUM(amount_sold)
FROM   sh.sales
WHERE  amount_sold > sh.get_high_value_threshold();

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  format => 'ALLSTATS LAST +NOTE'
));


| Id | Operation            | Name  | E-Rows | A-Rows |
|  0 | SELECT STATEMENT     |       |        |      1 |
|  1 |  SORT AGGREGATE      |       |      1 |      1 |
|* 2 |   PARTITION RANGE ALL|       |  12203 |  12116 |  <- Near accurate
|*  3|    TABLE ACCESS FULL | SALES |  12203 |  12116 |

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - PL/SQL function sampled for dynamic statistics

The optimizer called the function during dynamic statistics gathering at parse time, got the actual return value (1000), and used it to estimate selectivity accurately. E-Rows 12,203 vs A-Rows 12,116 — less than 1% off.

You can also control this at the object level, which is the right approach in production .. turn it on only for specific functions you trust:

-- Prefer object-level control in production
-- Allow dynamic stats for a specific function
EXEC DBMS_STATS.SET_FUNCTION_PREFS(
  ownname   => 'SH',
  funcname  => 'GET_HIGH_VALUE_THRESHOLD',
  pref_name => 'PLSQL_FUNCTION_DYNAMIC_STATS',
  pref_value => 'ON'
);

-- Check current settings
SELECT function_name, preference_name, preference_value
FROM   all_stat_extensions
WHERE  object_type = 'FUNCTION'
AND    owner = 'SH';

So, in short, Oracle’s optimizer hasn’t made one big leap … it’s made fifteen years of deliberate, incremental improvements, each one handling a class of cardinality problem the previous release couldn’t.

What 26ai specifically adds isn’t magic. It’s two concrete, named, documented improvements … dynamic statistics for PL/SQL functions, and the SQL Analysis Report surfacing optimizer advice inline .. plus the PL/SQL transpiler removing the problem class entirely for eligible functions. These are real. They’re testable. They’re in the docs.

The underlying ML enhanced cost modelling is also real, but it’s an evolutionary improvement without a named switch … Oracle’s engineering continues to get better at estimating costs, particularly for complex workloads, vector queries, and correlated predicates. That’s not hype. It’s just not a single feature you can point to in the docs either.

Know your E-Rows vs A-Rows. Know your SQL Plan Directives. Know your extended statistics. And in 26ai, let the SQL Analysis Report do the first pass for you.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: 26ai, ai, databases, fatdba, performance, plsql, renaps, troubleshooting | Leave a Comment »

Oracle 26ai Assertions – The Database constraint that took years to ship

Posted by FatDBA on March 29, 2026

If you’ve been working with Oracle for a while, you’ve probably hit a wall with constraints at some point. Primary keys, foreign keys, check constraints .. they’re great, but they all have one thing in common: they only work within a single row, or between two specific tables in a fixed relationship.

What happens when your business rule spans multiple rows? Or crosses two unrelated tables? You write a trigger. And then you spend the next hour making sure the trigger fires correctly on INSERT, UPDATE, and DELETE. And then six months later someone disables it during a data load and forgets to re-enable it. And then your data is a mess.

The idea is simple: define a boolean condition at the schema level, and the database guarantees that condition is always true, no matter what DML runs. Think of it as a check constraint, but with no limits … it can look across rows, across tables, anything.

The problem? For 30 + years, no major database engine actually implemented it. It existed in textbooks, in SQL courses, in Oracle certification material with a footnote saying “not supported” .. but never in production.

Until now ….. Oracle AI Database 26ai (Release Update 23.26.1, January 2026) is the first major database engine to ship SQL Assertions. That’s not marketing. That’s genuinely a first in this industry.

An assertion is a schema-level integrity constraint that contains a boolean SQL expression. The database evaluates that expression after every relevant DML operation and rolls back the statement if it returns false. The simplest way to think about it: a CHECK constraint which is more powerfull. A CHECK constraint can only see the row being modified. An assertion can see anything … the whole table, multiple tables, aggregates, subqueries.

CREATE ASSERTION assertion_name
CHECK ( <your boolean SQL condition here> )
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY IMMEDIATE | INITIALLY DEFERRED ];
```

The moment any DML would violate the condition, Oracle raises:
```
ORA-08601: SQL assertion (SCHEMA.ASSERTION_NAME) violated.

And the statement rolls back. Clean, declarative, engine-enforced.

Lets discuss a real scenario that show where traditional constraints fail and assertions step in.

You have a FLIGHTS table with a capacity column and a SEATS table with one row per assigned seat per flight. Business rule: the number of rows in SEATS for a given flight can never exceed the flight’s capacity.

CREATE ASSERTION no_overbooking
CHECK (
  NOT EXISTS (
    SELECT f.flight_id
    FROM   flights f
    WHERE  (SELECT COUNT(*) FROM seats s WHERE s.flight_id = f.flight_id)
           > f.capacity
  )
);

Try to insert a seat assignment that pushes a flight past capacity …

INSERT INTO seats (seat_id, flight_id, passenger_id)
VALUES (9999, 101, 4455);

-- ORA-08601: SQL assertion (APP.NO_OVERBOOKING) violated.

The check happens inside the database engine, not in your booking API. That means it works even if someone runs a direct INSERT from SQL Developer, a data migration script, or any other path that bypasses your application layer.

Assertions work exactly like other Oracle constraints in terms of state management. You have four states:

State	Meaning
`ENABLE VALIDATE`	Existing data checked. All future DML checked. Default when you create.
`ENABLE NOVALIDATE`	Existing data NOT checked. Future DML is checked.
`DISABLE VALIDATE`	Existing data was validated. DML not currently checked.
`DISABLE NOVALIDATE`	Fully off. No checking at all.

You can also make assertions deferrable, which is critical when you need to temporarily violate a rule mid-transaction and fix it before commit:

-- Deferrable assertion: checked at COMMIT, not after each statement
CREATE ASSERTION president_salary_range
CHECK (
  EXISTS (
    SELECT 1 FROM emp
    WHERE  job = 'PRESIDENT'
    AND    sal BETWEEN 4000 AND 10000
  )
)
DEFERRABLE INITIALLY DEFERRED;

This means within a transaction you can update the president’s salary to something temporarily outside the range …as long as it’s corrected before COMMIT. The assertion only fires at commit time. For non-deferrable assertions (the default), the check happens immediately after each DML statement.

But there are some limitations as well … Oracle’s implementation is solid but it’s version 1. These are real constraints to be aware of:

No non-deterministic functions : You cannot use SYSDATE, SYSTIMESTAMP, SYS_CONTEXT, USER, USERENV, CURRENT_SCHEMA, or any session-dependent context. Assertions must evaluate identically regardless of who runs the DML or when.
No PL/SQL : You can’t call PL/SQL functions inside an assertion … not even deterministic ones. Pure SQL only.
Read Committed only : Assertions are not supported under SERIALIZABLE isolation level.
No synonyms for cross schema references: If your assertion references tables in another schema, you must use the explicit SCHEMA.TABLE_NAME notation. Synonyms are not resolved.
No GROUP BY or analytic functions: At least in the current release. Aggregate conditions need to be expressed using subqueries.
No EXCEPTIONS INTO clause: Unlike constraint violations on existing data, there’s no way to route assertion violations to an exceptions table ..yet.

So, what’s the enginnering behind it, what happens internally ?>

When you create an assertion, Oracle creates internal change tracking auxiliary tables prefixed ORA$SA$TE_ … alongside your regular tables. These are internal global temporary tables with restricted read access (ORA-08709 if you try to query them directly). Their job is to track which rows changed in a DML operation so Oracle doesn’t have to re-evaluate the entire assertion from scratch on every statement.

You can see the locking strategy Oracle uses via the dictionary view, here are some of the related views.


-- Privilege to create assertions in your own schema
GRANT CREATE ASSERTION TO your_user;

-- Or just use the built-in developer role
GRANT DB_DEVELOPER_ROLE TO your_user;

-- Dictionary views
SELECT assertion_name, status, validated, deferrable, deferred
FROM   user_assertions;

SELECT * FROM user_assertion_dependencies;
SELECT * FROM user_assertion_lock_matrix;

For example, if your assertion involves EMPNO as a join column, Oracle knows it only needs to revalidate the assertion for the specific EMPNO values touched by the DML not the entire table. This is what makes assertions practical at scale. It’s essentially fine-grained, row-level re-validation rather than a full table scan on every INSERT or UPDATE.

If you manage data integrity rules through triggers today .. especially cross-table or multi row rules ..this is worth a serious look. The syntax is clean, the behavior is predictable, and Oracle’s internal change-tracking implementation means it’s not just a theoretical feature that kills performance on every write.

It’s real, it’s on-prem available, and it’s the most quietly significant data integrity improvement Oracle has shipped in a very long time. Oracle AI Database 26ai, Release Update 23.26.1 (January 2026) and later. On-premises Linux x86-64 supported.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

Real Time Data Replication from Oracle to DataStax Cassandra using Oracle GoldenGate 23ai DAA

Posted by FatDBA on March 16, 2026

Modern enterprise architectures rarely live inside a single database engine anymore. It is very common to see operational data stored in relational systems like Oracle while analytics or distributed applications rely on NoSQL platforms such as Cassandra.

In several real-world projects I have worked on, Cassandra clusters were used to power high-volume applications while Oracle remained the authoritative system of record. In these cases, Oracle GoldenGate D23ai DAA becomes a powerful bridge that streams transactional changes from Oracle redo logs directly into Cassandra in near real time.

In this article we will walk through a complete end-to-end implementation of Oracle → DataStax Cassandra replication using Oracle GoldenGate.

The goal is to demonstrate:

• Initial data load
• Continuous change capture
• Replication of DML operations
• Validation on the Cassandra side

Source System : Oracle Database Server
Hostname: ora-prod01.lab.internal
IP: 10.10.10.11

Operating System: Oracle Linux 8
Database Version: Oracle 19c (19.21 RU)
GoldenGate Version: 23ai Classic Deployment

Target System: DataStax Enterprise Cassandra Node
Hostname: cass-node01.lab.internal
IP: 10.10.10.21

Operating System: Rocky Linux 8
Cassandra Distribution: DataStax Enterprise 6.8.50

GoldenGate Big Data / Java Adapter is installed on the Cassandra side.

Step 1 – Prepare Oracle Source Database

Oracle GoldenGate captures data changes directly from the Oracle redo logs, allowing near real-time replication without querying tables. To support this, the database must have ARCHIVELOG mode, force logging, and supplemental logging enabled so that all row-level changes are fully recorded and available for capture.

sqlplus / as sysdba

archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled

-- enable archivelog mode
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

-- enable force logging
alter database force logging;

-- enable supp logging 
alter database add supplemental log data;

-- enabled GG parameter in source database
alter system set enable_goldengate_replication=true scope=both;

-- streams pool size 
alter system set streams_pool_size=2G scope=both;

Step 2 – Create GoldenGate User in Oracle

A dedicated database user is required for GoldenGate to capture changes from the Oracle redo logs and access the necessary metadata. This user is granted the required privileges to read transaction logs, access dictionary information, and manage replication objects.

CREATE TABLESPACE ogg_ts 
DATAFILE '/u02/oradata/ogg_ts01.dbf' 
SIZE 500M AUTOEXTEND ON;

CREATE USER ggadmin IDENTIFIED BY "Welcome#123"
DEFAULT TABLESPACE ogg_ts
TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE TO ggadmin;
GRANT SELECT ANY TABLE TO ggadmin;
GRANT SELECT ANY TRANSACTION TO ggadmin;
GRANT SELECT ANY DICTIONARY TO ggadmin;
GRANT FLASHBACK ANY TABLE TO ggadmin;
GRANT ALTER SYSTEM TO ggadmin;
GRANT UNLIMITED TABLESPACE TO ggadmin;


-- Granted OGG admin privs 
exec dbms_goldengate_auth.grant_admin_privilege(
   grantee => 'GGADMIN',
   privilege_type => 'CAPTURE',
   grant_select_privileges => TRUE,
   do_grants => TRUE);

Step 3 – Create Source Table in Oracle

To validate the replication pipeline, create a sample table and insert a few test records in the Oracle source database. These records will later be used to verify that GoldenGate successfully captures and replicates the data to the DataStax Cassandra target.

CREATE TABLE SALES_TRANSACTIONS
(
   TXN_ID NUMBER PRIMARY KEY,
   CUSTOMER_NAME VARCHAR2(100),
   PRODUCT_CODE VARCHAR2(50),
   TXN_AMOUNT NUMBER,
   TXN_TIMESTAMP DATE
);

INSERT INTO SALES_TRANSACTIONS VALUES
(101,'John Carter','PRD-A',1200,SYSDATE);

INSERT INTO SALES_TRANSACTIONS VALUES
(102,'Alice Brown','PRD-B',850,SYSDATE);

INSERT INTO SALES_TRANSACTIONS VALUES
(103,'Michael Lee','PRD-C',430,SYSDATE);

COMMIT;


select * from SALES_TRANSACTIONS;

TXN_ID CUSTOMER_NAME PRODUCT_CODE TXN_AMOUNT TXN_TIMESTAMP
-----------------------------------------------------------
101    John Carter   PRD-A        1200       05-MAR-26
102    Alice Brown   PRD-B        850        05-MAR-26
103    Michael Lee   PRD-C        430        05-MAR-26

Step 4 – Install DataStax Cassandra

On the target node install DataStax Enterprise. Example repository configuration:

sudo vi /etc/yum.repos.d/datastax.repo

[datastax]
name=DataStax Repo
baseurl=https://rpm.datastax.com/enterprise
enabled=1
gpgcheck=0

-- Install DSE 
sudo yum install dse-full-6.8.50 -y

-- Start DSE 
sudo systemctl start dse

-- Veroify cluster/nodes status 
nodetool status
Datacenter: DC1
===============
Status=Up/Down
|/ State=Normal

--  Address       Load       Tokens Owns  Host ID                               Rack
UN  10.10.10.21   120 KB     256    100%  8c34a0c1-b8c1-4b35-a6a7-9c2d8bfae111   rack1

Step 5 – Create Cassandra Keyspace

In DataStax Cassandra, a keyspace is the top-level namespace that defines how data is replicated across the cluster. Before creating tables for replication, we first create a keyspace and configure its replication strategy and replication factor.

cqlsh 10.10.10.21 <port> -u xxxx -p xxx

CREATE KEYSPACE retail_ks
WITH replication = {
'class': 'NetworkTopologyStrategy',
'DC1': 1
};

-- verify 
DESCRIBE KEYSPACES;


-- Created target tables in cassandra
USE retail_ks;

CREATE TABLE sales_transactions
(
   txn_id int,
   customer_name text,
   product_code text,
   txn_amount decimal,
   txn_timestamp timestamp,
   PRIMARY KEY(txn_id)
);

-- verofy table structure 
DESCRIBE TABLE retail_ks.sales_transactions;

Step 6 – Install GoldenGate on Oracle

Oracle GoldenGate software must be installed on the source server to capture database changes. In production environments, the silent installation method is commonly used because it allows automated and repeatable deployments without manual interaction.

-- Unpack GoldenGate binaries.
unzip 231000_fbo_ggs_Linux_x64_Oracle.zip

-- Run silent installation. Respoonse file
INSTALL_OPTION=ora23ai
SOFTWARE_LOCATION=/u01/app/ogg
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/19c


-- Install
./runInstaller -silent -responseFile ogg.rsp

-- LaUnch OGG
cd /u01/app/ogg
./ggsci

GGSCI> INFO ALL
Program     Status
MANAGER     RUNNING

Step 7 – Enable Table Level Replication

GGSCI> DBLOGIN USERID ggadmin PASSWORD Welcome#123

GGSCI> ADD TRANDATA SALES_TRANSACTIONS
OGG-15132 Logging supplemental redo data enabled

Step 8 – Configure Extract

Create parameter file.

GGSCI> EDIT PARAMS EXT_SALES

EXTRACT EXT_SALES
USERID ggadmin PASSWORD Welcome#123
EXTTRAIL ./dirdat/st
TRANLOGOPTIONS INTEGRATEDPARAMS (PARALLELISM 4)
TABLE SALES_TRANSACTIONS;


-- Create extract
ADD EXTRACT EXT_SALES INTEGRATED TRANLOG BEGIN NOW
ADD EXTTRAIL ./dirdat/st EXTRACT EXT_SALES

-- start & verifgy its status 
START EXTRACT EXT_SALES
INFO EXTRACT EXT_SALES

EXTRACT    EXT_SALES     Last Started 2026-03-15 21:48   Status RUNNING
Checkpoint Lag           00:00:00 (updated 00:00:04 ago)
Process ID               32418
Log Read Checkpoint      Oracle Redo Logs
                        SCN  845732918 (0x0000.3267a3b6)
                        2026-03-15 21:48:12

Step 9 – Configure Data Pump

The GoldenGate Data Pump reads captured changes from the local trail files and forwards them to the target GoldenGate deployment. This process improves reliability and allows data to be securely transported to the target environment.

EDIT PARAMS PUMP_SALES

EXTRACT PUMP_SALES
RMTHOST 10.10.10.21, MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE SALES_TRANSACTIONS;

-- add pump
ADD EXTRACT PUMP_SALES EXTTRAILSOURCE ./dirdat/st
ADD RMTTRAIL ./dirdat/rt EXTRACT PUMP_SALES

-- Start Pump
START EXTRACT PUMP_SALES
INFO PUMP_SALES

EXTRACT    PUMP_SALES    Last Started 2026-03-15 21:48   Status RUNNING
Checkpoint Lag           00:00:00 (updated 00:00:02 ago)
Process ID               32455
Log Read Checkpoint      File ./dirdat/es000000000
                        RBA 18425

Step 10 – Configure Cassandra Handler

On the GoldenGate Big Data / DAA node, configure the Cassandra handler properties so that the Replicat process knows how to connect to the DataStax Cassandra cluster and apply incoming changes. This configuration defines connection details, consistency level, datacenter information, and the Java classpath required for the Cassandra driver.

This configuration allows the GoldenGate Replicat process to establish a connection with the Cassandra cluster and apply transactional changes coming from the Oracle source database.

[oggadmin@cass-hub01 ~]$ vi /u01/app/ogg_23ai/dirprm/cassandra.props
gg.handlerlist=cassandra

gg.handler.cassandra.type=cassandra
gg.handler.cassandra.mode=op
gg.handler.cassandra.contactPoints=10.20.30.41
gg.handler.cassandra.port=9042
gg.handler.cassandra.keyspace=retail_ops_ks
gg.handler.cassandra.consistencyLevel=LOCAL_QUORUM
gg.handler.cassandra.cassandraMode=async
gg.handler.cassandra.compressedUpdates=true
gg.handler.cassandra.datacenter=DC_MAIN
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
gg.classpath=/opt/datastax/cassandra/resources/cassandra/lib/*:/u01/app/ogg_23ai/DependencyDownloader/dependencies/cassandra_4.1/*
javawriter.stats.full=true
javawriter.stats.display=TRUE

-- If authentication is enabled in DataStax Cassandra, the property file can also include credentials depending on the adapter version and security model being used.
-- exampl;e
gg.handler.cassandra.username=oggsvc
gg.handler.cassandra.password=Str0ngPassw0rd

Step 11 – Configure Replicat

At this stage, the source-side Extract and Data Pump are already running and writing trail records to the remote trail on the target GoldenGate deployment. The next step is to configure the Replicat process on the Cassandra side so that trail records can be applied into the target DataStax Cassandra keyspace and table.

In a GoldenGate Big Data or DAA style deployment, the first thing you should always do is connect to the deployment administration interface and verify that the deployment is up before creating the Replicat.

-- Loginto deployment
[oggadmin@cass-hub01 ~]$ cd /u01/app/ogg_23ai/bin
[oggadmin@cass-hub01 bin]$ ./adminclient

Oracle GoldenGate Admin Client for Oracle
Version 23.10.0.25.10 OGGCORE_23.10.0.0.0_PLATFORMS_251018.0830

Copyright (C) 1995, 2025, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized), Generic on Oct 18 2025 14:00:54
Operating system character set identified as UTF-8.


-- connect to the deployment
OGG (not connected) 1> CONNECT http://10.10.40.25:9001 deployment CassandraDAA as oggadmin password "xxxxxx";
Successfully connected to CassandraDAA.

Before creating the Replicat, verify that the remote trail files are arriving from the Data Pump. This is a simple but important validation step. If the trail is not being written, the Replicat will start but will have nothing to process.

[oggadmin@cass-hub01 ~]$ cd /u01/app/ogg_23ai/var/lib/data
[oggadmin@cass-hub01 data]$ ls -ltr rt*

-rw-r----- 1 oggadmin oinstall  1048576 Mar 15 20:11 rt000000000
-rw-r----- 1 oggadmin oinstall   524288 Mar 15 20:16 rt000000001

This confirms that the remote trail prefix rt is present and the target deployment is receiving records from the source-side Pump process.

Next lets create the replicate.

OGG (http://10.10.40.25:9001 CassandraDAA as oggadmin@CassandraDAA) 3> EDIT PARAMS REP_CASS

REPLICAT REP_CASS
TARGETDB LIBFILE libggjava.so SET property=dirprm/cassandra.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 1000
DISCARDFILE ./var/lib/data/REP_CASS.dsc, APPEND, MEGABYTES 500
DISCARDROLLOVER AT 02:00
MAP APP_OWNER.SALES_ORDERS_SRC,
TARGET retail_ops_ks.sales_orders_rt;

-- add the repl process
OGG (http://10.10.40.25:9001 CassandraDAA as oggadmin@CassandraDAA) 4> ADD REPLICAT REP_CASS, EXTTRAIL ./var/lib/data/rt
REPLICAT added.

-- staret the process
OGG (http://10.10.40.25:9001 CassandraDAA as oggadmin@CassandraDAA) 5> START REPLICAT REP_CASS
Sending START request to Manager ...
REPLICAT REP_CASS starting.

REPLICAT   REP_CASS      Last Started 2026-03-15 21:49   Status RUNNING
Checkpoint Lag           00:00:00 (updated 00:00:01 ago)
Process ID               32504
Log Read Checkpoint      File ./dirdat/rt000000000
                        RBA 18874

Params explanations …

REPLICAT REP_CASS defines the Replicat group name.

TARGETDB LIBFILE libggjava.so SET property=dirprm/cassandra.props tells GoldenGate to use the Java-based Big Data adapter library and load all Cassandra handler settings from the referenced properties file.

REPORTCOUNT EVERY 1 MINUTES, RATE prints transaction statistics regularly into the report file, which is very useful during initial testing and performance validation.

GROUPTRANSOPS 1000 batches operations to improve throughput. This value can be tuned depending on transaction profile, row size, and Cassandra write latency.
DISCARDFILE captures records that fail processing, while DISCARDROLLOVER keeps the discard file manageable.
The MAP statement defines the source Oracle table and the target DataStax Cassandra table.

In this example:
Oracle source table: APP_OWNER.SALES_ORDERS_SRC
Cassandra target table: retail_ops_ks.sales_orders_rt

Step 12 – Testing End-to-End Replication (Oracle to DataStax Cassandra)

In this section we will validate that the replication pipeline is working correctly by performing a few test transactions on the Oracle source database and confirming that the changes are applied on the DataStax Cassandra target table.

I am not going to discuss the initial load process here since it is relatively straightforward and typically performed using standard GoldenGate initial load methods or bulk data movement tools. For the purpose of this test, we assume that the base dataset has already been loaded into Cassandra and that GoldenGate is responsible only for capturing and replicating incremental changes.

-- Source DB

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

SELECT * FROM SALES_ORDERS_SRC;
ORDER_ID   CUSTOMER_NAME   ORDER_STATUS   ORDER_AMOUNT
--------   -------------   ------------   ------------
1001       NORTH RETAIL    CREATED        450
1002       ALPHA STORES    SHIPPED        720
1003       OMEGA MART      CREATED        610

On Target/Cassandra cluster

cqlsh 10.20.30.41 9042 -u cassandra -p cassandra
Connected to RetailCluster at 10.20.30.41:9042
[cqlsh 6.1.0 | DSE 6.8.x | CQL spec 3.4.6]

USE retail_ops_ks;

SELECT * FROM sales_orders_rt;
order_id | customer_name | order_status | order_amount
---------+---------------+--------------+--------------
1001     | NORTH RETAIL  | CREATED      | 450
1002     | ALPHA STORES  | SHIPPED      | 720
1003     | OMEGA MART    | CREATED      | 610

-- This confirms that the initial dataset already exists on both systems.

Lets do some changes on source and see how it reaches on target.

INSERT INTO SALES_ORDERS_SRC
VALUES (1004,'PACIFIC TRADING','CREATED',980);
COMMIT;

1 row created.

Commit complete.



-- Veroify OGG 
INFO EXTRACT EXT_SALES
EXTRACT EXT_SALES Last Started 2026-03-16 20:12 Status RUNNING
Checkpoint Lag 00:00:00
Log Read Checkpoint
SCN 845736122

-- scn moVED. This confirms that the Extract process captured the transaction from the Oracle redo logs.

Now lets check it on target

SELECT * FROM sales_orders_rt
WHERE order_id = 1004;

order_id | customer_name    | order_status | order_amount
---------+------------------+--------------+--------------
1004     | PACIFIC TRADING  | CREATED      | 980

The inserted record has successfully replicated.

By performing these test operations, we validated that:

• GoldenGate Extract captured Oracle redo changes
• Data Pump delivered trail files to the target deployment
• Replicat applied changes into DataStax Cassandra

The Oracle —> GoldenGate —-> Cassandra replication pipeline is now functioning correctly.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: 23ai, bigdata, cassandra, daa, goldengate, migration, ogg, renaps, replication | Leave a Comment »

Parquet, hadoop, and a quietly dying process : lessons from a migration test using GoldenGate 23ai DAA

Posted by FatDBA on February 8, 2026

I was doing some hands-on testing with Oracle GoldenGate 23ai DAA, trying to move data from an old but reliable Oracle 11g database into Microsoft Azure Fabric. The idea was simple enough. Capture changes from Oracle 11g, push them through GoldenGate 23ai, and land them in Fabric OneLake so they could be used by a Lakehouse or a Mirrored Database. On paper, it sounded clean. In real life… well, it took a bit of digging.

The source side was boring in a good way. Oracle 11g behaved exactly as expected. Extracts were running, trails were getting generated, no drama there. The real work was on the target side. I configured a Replicat using the File Writer with Parquet output, since Parquet is the natural fit for Microsoft Fabric. Fabric loves Parquet. Lakehouse loves Parquet. Mirrored databases too. So far, so good.

I started the Replicat and GoldenGate politely told me it had started. That tiny moment of relief you get when a command doesn’t fail right away. But then I checked the status… and it was STOPPED. No lag, no progress, nothing. That’s usually when you know something went wrong very early, before any real work even started.

So I opened the report file. And there it was. A Java error staring right back at me:

OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 18> START REPLICAT FATD11D
2025-12-12T21:25:18Z  INFO    OGG-00975  Replicat group FATD11D starting.
2025-12-12T21:25:18Z  INFO    OGG-15445  Replicat group FATD11D started.


OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 20> info replicat FATD11D

Replicat   FATD11D    Initialized  2025-12-12 16:24   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:55 ago)
Log Read Checkpoint  File dirdat/i1000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet





OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 21> view report FATD11D

***********************************************************************
     Oracle GoldenGate for Distributed Applications and Analytics
                   Version 23.10.0.25.10 (Build 001)

                      Oracle GoldenGate Delivery
 Version 23.10.1.25.10 OGGCORE_23.10.0.0.0OGGRU_LINUX.X64_251018.0830
    Linux, x64, 64bit (optimized), Generic on Oct 18 2025 14:00:54

Copyright (C) 1995, 2025, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2025-12-12 16:25:18
***********************************************************************

2025-12-12 16:25:19  INFO    OGG-15052  Using Java class path: /testgg/app/ogg/ogg23ai/ogg23aidaa_MA//ggjava/ggjava.jar:/testgg/app/ogg/ogg23ai/ogg23aidaa_DEPLOYMENT/etc/conf/ogg:/u01/app/ogg/ogg
23ai/ogg23aidaa_MA/.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/parquet/hadoop/metadata/CompressionCodecName
        at oracle.goldengate.eventhandler.parquet.ParquetEventHandlerProperties.<init>(ParquetEventHandlerProperties.java:43)
        at oracle.goldengate.eventhandler.parquet.ParquetEventHandler.<init>(ParquetEventHandler.java:53)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
        at java.base/java.lang.Class.newInstance(Class.java:587)
        at oracle.goldengate.datasource.eventhandler.EventHandlerFramework.instantiateEventHandler(EventHandlerFramework.java:219)
        at oracle.goldengate.datasource.eventhandler.EventHandlerFramework.initEventHandler(EventHandlerFramework.java:163)
        at oracle.goldengate.datasource.eventhandler.EventHandlerFramework.init(EventHandlerFramework.java:58)
        at oracle.goldengate.handler.filewriter.FileWriterHandlerEO.init(FileWriterHandlerEO.java:627)
        at oracle.goldengate.datasource.AbstractDataSource.addDataSourceListener(AbstractDataSource.java:602)
        at oracle.goldengate.datasource.factory.DataSourceFactory.getDataSource(DataSourceFactory.java:164)
        at oracle.goldengate.datasource.UserExitDataSourceLauncher.<init>(UserExitDataSourceLauncher.java:45)
        at oracle.goldengate.datasource.UserExitMain.main(UserExitMain.java:109)
Caused by: java.lang.ClassNotFoundException: org.apache.parquet.hadoop.metadata.CompressionCodecName
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:581)
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
        at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:526)
        ... 15 more
2025-12-12 16:25:22  WARNING OGG-00869  java.lang.ClassNotFoundException: org.apache.parquet.hadoop.metadata.CompressionCodecName.

Source Context :
  SourceFile              : [/ade/aime_phxdbifa87/oggcore/OpenSys/src/gglib/ggdal/Adapter/Java/JavaAdapter.cpp]
  SourceMethod            : [HandleJavaException]
  SourceLine              : [350]
  ThreadBacktrace         : [19] elements
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(_MSG_String(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libggjava.so()]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libggjava.so(ggs::gglib::ggdal::CJavaAdapter::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::ggdal::CDALAdapter::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::GetWriter())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::GetGenericDBType())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::er::ReplicatContext::ReplicatContext(ggs::gglib::ggapp::ReplicationContextParams const&, bool, ggs::gglib::
ggmetadata::MetadataContext*, ggs::er::ReplicatContext::LogBSNManager*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::er::ReplicatContext::createReplicatContext(ggs::gglib::ggapp::ReplicationContextParams const&, ggs::gglib::
ggdatasource::DataSourceParams const&, ggs::gglib::ggmetadata::MetadataContext*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat()]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(main)]
                          : [/lib64/libc.so.6()]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(_start)]

2025-12-12 16:25:22  ERROR   OGG-15051  Java or JNI exception:
java.lang.NoClassDefFoundError: org/apache/parquet/hadoop/metadata/CompressionCodecName.

2025-12-12 16:25:22  ERROR   OGG-01668  PROCESS ABENDING.

At that point it clicked. GoldenGate itself was fine. Oracle 11g was fine. Fabric wasn’t even in the picture yet. The problem was simpler. The Parquet libraries were missing.

All of the pre-reqs are there in the DependencyDownloader directory. Inside you will find all scripts for everything… Parquet, Hadoop, OneLake, Kafka, and more. Before touching anything, I checked Java. Java 17 was already installed. I ran the Parquet dependency script. Maven kicked in, downloaded a bunch of JARs, and finished successfully. I restarted the Replicat, feeling pretty confident. And… it failed again. Different error this time, though, which honestly felt like progress.

[oggadmin@D-ADON-01-CC-VM bin]$
[oggadmin@D-ADON-01-CC-VM bin]$ find /u01/app/ogg/ogg23ai -name "*.properties" | egrep -i "sample|example|handler|parquet|filewriter" | head -n 20
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/oci.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/kafka.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/hbase.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/parquet.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/kafkaconnect.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/azureservicebus.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/mongo.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/filewriter.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/bigquery.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/nosql.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/hdfs.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/synapse.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/redshift.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/pubsub.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/s3.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/redis.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/elasticsearch.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/jdbc.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/adw.properties
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/AdapterExamples/templates/jms.properties
[oggadmin@D-ADON-01-CC-VM bin]$




[oggadmin@D-ADON-01-CC-VM bin]$
[oggadmin@D-ADON-01-CC-VM bin]$ ls -ltrh /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/ggjava
total 60K
-rwxrwxr-x. 1 oggadmin ogg  34K Jun  5  2024 NOTICES.txt
-rwxrwxr-x. 1 oggadmin ogg   95 Oct 21 10:50 ggjava-version.txt
-rwxrwxr-x. 1 oggadmin ogg 9.5K Oct 21 10:50 ggjava.jar
drwxr-xr-x. 5 oggadmin ogg 4.0K Jan 29 16:51 resources
drwxr-xr-x. 6 oggadmin ogg 4.0K Jan 29 16:51 maven-3.9.6



[oggadmin@D-ADON-01-CC-VM bin]$ find /u01/app/ogg/ogg23ai -iname "onelake.sh" -o -iname "*parquet*.sh" -o -iname "*dependency*.sh"
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/onelake.sh
/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/parquet.sh
[oggadmin@D-ADON-01-CC-VM bin]$ /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/onelake.sh


[oggadmin@D-ADON-01-CC-VM bin]$ cd /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$ ls
aws.sh                    cassandra_dse.sh          gcs.sh                    hbase_hortonworks.sh         kafka.sh             orc.sh             snowflake.sh
azure_blob_storage.sh     cassandra.sh              googlepubsub.sh           hbase.sh                     kinesis.sh           parquet.sh         snowflakestreaming.sh
bigquery.sh               config_proxy.sh           hadoop_azure_cloudera.sh  internal_scripts             mongodb_capture.sh   project            synapse.sh
bigquerystreaming.sh      databricks.sh             hadoop_cloudera.sh        kafka_cloudera.sh            mongodb.sh           redis.sh           velocity.sh
cassandra_capture_3x.sh   docs                      hadoop_hortonworks.sh     kafka_confluent_protobuf.sh  onelake.sh           redshift.sh        xmls
cassandra_capture_4x.sh   download_dependencies.sh  hadoop.sh                 kafka_confluent.sh           oracle_nosql_sdk.sh  s3.sh
cassandra_capture_dse.sh  elasticsearch_java.sh     hbase_cloudera.sh         kafka_hortonworks.sh         oracle_oci.sh        snowflake-fips.sh
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$






[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$ java -version
openjdk version "17.0.18" 2026-01-20 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.18.0.8-1.0.1) (build 17.0.18+8-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.18.0.8-1.0.1) (build 17.0.18+8-LTS, mixed mode, sharing)
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$






[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$ ./onelake.sh
openjdk version "17.0.18" 2026-01-20 LTS
Java is installed.
Apache Maven 3.9.6 (bc0240f3c744dd6b6ec2920b3cd08dcc295161ae)
Maven is accessible.
Root Configuration Script
INFO: This is the Maven binary [../../ggjava/maven-3.9.6/bin/mvn].
INFO: This is the location of the settings.xml file [./docs/settings_np.xml].
INFO: This is the location of the toolchains.xml file [./docs/toolchains.xml].
INFO: The dependencies will be written to the following directory[../dependencies/onelake].
INFO: The Maven coordinates are the following:
INFO: Dependency 1
INFO: Group ID [com.azure].
INFO: Artifact ID [azure-storage-file-datalake].
INFO: Version [12.20.0]
INFO: Dependency 2
INFO: Group ID [com.azure].
INFO: Artifact ID [azure-identity].
INFO: Version [1.13.1]
[INFO] Scanning for projects...
[INFO]
[INFO] ---------------< oracle.goldengate:dependencyDownloader >---------------
[INFO] Building dependencyDownloader 1.0
[INFO]   from pom_central_v2.xml
[INFO] --------------------------------[ pom ]---------------------------------
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-clean-plugin/3.2.0/maven-clean-plugin-3.2.0.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-clean-plugin/3.2.0/maven-clean-plugin-3.2.0.pom (5.3 kB at 24 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/35/maven-plugins-35.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/35/maven-plugins-35.pom (9.9 kB at 431 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/35/maven-parent-35.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/35/maven-parent-35.pom (45 kB at 1.7 MB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/apache/25/apache-25.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/apache/25/apache-25.pom (21 kB at 1.0 MB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-clean-plugin/3.2.0/maven-clean-plugin-3.2.0.jar
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-clean-plugin/3.2.0/maven-clean-plugin-3.2.0.jar (36 kB at 1.4 MB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/2.9/maven-dependency-plugin-2.9.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/2.9/maven-dependency-plugin-2.9.pom (13 kB at 602 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/maven/plugins
.........
...............
...................
[INFO] Copying netty-tcnative-boringssl-static-2.0.65.Final-windows-x86_64.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/onelake/netty-tcnative-boringssl-static-2.0.65.Final-windows-x86_64.jar
[INFO] Copying reactive-streams-1.0.4.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/onelake/reactive-streams-1.0.4.jar
[INFO] Copying oauth2-oidc-sdk-11.9.1.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/onelake/oauth2-oidc-sdk-11.9.1.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  8.334 s
[INFO] Finished at: 2025-12-12T16:45:52-05:00
[INFO] ------------------------------------------------------------------------
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$













[oggadmin@D-ADON-01-CC-VM templates]$ cd /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader
[oggadmin@D-ADON-01-CC-VM templates]$   ./parquet.sh 1.13.1
openjdk version "17.0.18" 2026-01-20 LTS
Java is installed.
Apache Maven 3.9.6 (bc0240f3c744dd6b6ec2920b3cd08dcc295161ae)
Maven is accessible.
Root Configuration Script
INFO: This is the Maven binary [../../ggjava/maven-3.9.6/bin/mvn].
INFO: This is the location of the settings.xml file [./docs/settings_np.xml].
INFO: This is the location of the toolchains.xml file [./docs/toolchains.xml].
INFO: The dependencies will be written to the following directory[../dependencies/parquet_1.13.1].
.....
...........
.................
.....
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-hadoop/1.13.1/parquet-hadoop-1.13.1.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-hadoop/1.13.1/parquet-hadoop-1.13.1.pom (15 kB at 69 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet/1.13.1/parquet-1.13.1.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet/1.13.1/parquet-1.13.1.pom (25 kB at 790 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-column/1.13.1/parquet-column-1.13.1.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-column/1.13.1/parquet-column-1.13.1.pom (6.0 kB at 238 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-common/1.13.1/parquet-common-1.13.1.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-common/1.13.1/parquet-common-1.13.1.pom (3.4 kB at 143 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/parquet/parquet-format-structures/1.13.1/parquet-format-structures-1.13.1.pom
......
..............
...............
[INFO] Copying jackson-annotations-2.12.7.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/parquet_1.13.1/jackson-annotations-2.12.7.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  2.119 s
[INFO] Finished at: 2025-12-12T16:52:03-05:00
[INFO] ------------------------------------------------------------------------

Once again the replicate on target side failed to start and this time with a different error.

OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 8>  info REPLICAT FATD11D

Replicat   FATD11D    Initialized  2025-12-12 16:24   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:34:28 ago)
Log Read Checkpoint  File dirdat/i1000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet


OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 9> view report FATD11D

***********************************************************************
     Oracle GoldenGate for Distributed Applications and Analytics
                   Version 23.10.0.25.10 (Build 001)

                      Oracle GoldenGate Delivery
 Version 23.10.1.25.10 OGGCORE_23.10.0.0.0OGGRU_LINUX.X64_251018.0830
    Linux, x64, 64bit (optimized), Generic on Oct 18 2025 14:00:54

Copyright (C) 1995, 2025, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2025-12-12 16:58:47
***********************************************************************

2025-12-12 16:58:47  INFO    OGG-15052  Using Java class path: /testgg/app/ogg/ogg23ai/ogg23aidaa_MA//ggjava/ggjava.jar:/testgg/app/ogg/ogg23ai/ogg23aidaa_DEPLOYMENT/etc/conf/ogg:/u01/app/ogg/ogg
23ai/ogg23aidaa_MA/:/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/onelake/*:/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/parquet_1.13.
1/*.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/conf/Configuration
        at oracle.goldengate.eventhandler.parquet.GGParquetWriter.init(GGParquetWriter.java:72)
        at oracle.goldengate.eventhandler.parquet.ParquetEventHandler.init(ParquetEventHandler.java:219)
        at oracle.goldengate.datasource.eventhandler.EventHandlerFramework.initEventHandler(EventHandlerFramework.java:168)
        at oracle.goldengate.datasource.eventhandler.EventHandlerFramework.init(EventHandlerFramework.java:58)
        at oracle.goldengate.handler.filewriter.FileWriterHandlerEO.init(FileWriterHandlerEO.java:627)
        at oracle.goldengate.datasource.AbstractDataSource.addDataSourceListener(AbstractDataSource.java:602)
        at oracle.goldengate.datasource.factory.DataSourceFactory.getDataSource(DataSourceFactory.java:164)
        at oracle.goldengate.datasource.UserExitDataSourceLauncher.<init>(UserExitDataSourceLauncher.java:45)
        at oracle.goldengate.datasource.UserExitMain.main(UserExitMain.java:109)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.conf.Configuration
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:581)
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
        at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:526)
        ... 9 more

2025-12-12 16:58:48  WARNING OGG-00869  java.lang.ClassNotFoundException: org.apache.hadoop.conf.Configuration.

Source Context :
  SourceFile              : [/ade/aime_phxdbifa87/oggcore/OpenSys/src/gglib/ggdal/Adapter/Java/JavaAdapter.cpp]
  SourceMethod            : [HandleJavaException]
  SourceLine              : [350]
  ThreadBacktrace         : [19] elements
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libgglog.so(_MSG_String(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libggjava.so()]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/../lib/libggjava.so(ggs::gglib::ggdal::CJavaAdapter::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::ggdal::CDALAdapter::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::Open())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::GetWriter())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(GenericImpl::GetGenericDBType())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::er::ReplicatContext::ReplicatContext(ggs::gglib::ggapp::ReplicationContextParams const&, bool, ggs::gglib::
ggmetadata::MetadataContext*, ggs::er::ReplicatContext::LogBSNManager*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::er::ReplicatContext::createReplicatContext(ggs::gglib::ggapp::ReplicationContextParams const&, ggs::gglib::
ggdatasource::DataSourceParams const&, ggs::gglib::ggmetadata::MetadataContext*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat()]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(main)]
                          : [/lib64/libc.so.6()]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/testgg/app/ogg/ogg23ai/ogg23aidaa_MA/bin/replicat(_start)]

2025-12-12 16:58:48  ERROR   OGG-15051  Java or JNI exception:
java.lang.NoClassDefFoundError: org/apache/hadoop/conf/Configuration.

2025-12-12 16:58:48  ERROR   OGG-01668  PROCESS ABENDING.

That one made me pause for a second. The target wasn’t HDFS. I wasn’t running Hadoop. This was Microsoft Fabric. But here’s the catch. Parquet depends on Hadoop, even when you’re not using Hadoop directly. Some core Parquet classes expect Hadoop configuration classes to exist. No Hadoop libs, no Parquet writer.

So back to the DependencyDownloader I went, this time running the Hadoop script. More downloads, more JARs, more waiting.

[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$ cd /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader

[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$ ./hadoop.sh 3.4.2
openjdk version "17.0.18" 2026-01-20 LTS
Java is installed.
Apache Maven 3.9.6 (bc0240f3c744dd6b6ec2920b3cd08dcc295161ae)
Maven is accessible.
Root Configuration Script
INFO: This is the Maven binary [../../ggjava/maven-3.9.6/bin/mvn].
INFO: This is the location of the settings.xml file [./docs/settings_np.xml].
INFO: This is the location of the toolchains.xml file [./docs/toolchains.xml].
INFO: The dependencies will be written to the following directory[../dependencies/hadoop_3.4.2].
[INFO] ---------------< oracle.goldengate:dependencyDownloader >---------------
[INFO] Building dependencyDownloader 1.0
[INFO]   from pom_central_v2.xml
[INFO] --------------------------------[ pom ]---------------------------------
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/hadoop/hadoop-client/3.4.2/hadoop-client-3.4.2.pom
Downloaded from central: https://repo.maven.apache.org/maven2/org/apache/hadoop/hadoop-client/3.4.2/hadoop-client-3.4.2.pom (11 kB at 58 kB/s)
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/hadoop/hadoop-project-dist/3.4.2/hadoop-project-dist-3.4.2.pom
Downloaded from central: https://repo.maven.apach
..........
................
.....................
[INFO] Copying netty-codec-stomp-4.1.118.Final.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/hadoop_3.4.2/netty-codec-stomp-4.1.118.Final.jar
[INFO] Copying dnsjava-3.6.1.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/hadoop_3.4.2/dnsjava-3.6.1.jar
[INFO] Copying netty-transport-native-unix-common-4.1.118.Final.jar to /testgg/app/ogg/ogg23ai/ogg23aidaa_MA/opt/DependencyDownloader/dependencies/hadoop_3.4.2/netty-transport-native-unix-common-4.1.118.Final.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  7.627 s
[INFO] Finished at: 2025-12-12T18:02:30-05:00
[INFO] ------------------------------------------------------------------------
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$
[oggadmin@D-ADON-01-CC-VM DependencyDownloader]$

Once that finished, I restarted the Replicat again. No big expectations this time. This time it stayed up.

OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 2> START REPLICAT FATD11D
2025-12-12T23:07:54Z  INFO    OGG-00975  Replicat group FATD11D starting.
2025-12-12T23:07:54Z  INFO    OGG-15445  Replicat group FATD11D started.

OGG (http://192.168.10.10:9001 OGG23AIDAA as BigData@) 3> info FATD11D
No Extract groups exist.

Replicat   FATD11D    Last Started 2025-12-12 18:07   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           47420
Log Read Checkpoint  File dirdat/i10000000001
                     First Record  RBA 167873
Encryption Profile   LocalWallet

The big takeaway from this whole exercise is pretty simple. When you’re doing Oracle database to Microsoft Azure Fabric using GoldenGate 23ai DAA, the tricky part is not Oracle, and not Fabric. It’s the middle layer. Parquet is the bridge, and Parquet brings Hadoop with it, whether you like it or not. If those dependencies aren’t staged correctly, the OGG processes will start, smile at you, and then quietly fall over 😀

Once everything was in place, though, the setup worked exactly the way it should. A clean path from a legacy Oracle 11g database into a modern Microsoft Fabric Lakehouse. No magic. Just the right pieces, in the right order… and a bit of patience

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: fatdba, goldengate, repli, replication, troubleshooting | Leave a Comment »

Lessons from Integrating Oracle 11g with GoldenGate – ORA-06512 ORA-06502

Posted by FatDBA on February 7, 2026

This one took time. More than I’d like to admit.

I was trying to run GoldenGate 21c Integrated Extract from a remote extraction server, sourcing redo from an Oracle 11g (11.2.0.4.201020 (October 2020 PSU)) database. Everything worked fine, I was able to create the extract, login to the database, but got a weird issue when tried to register the extract with 11g source database from remote extraction.

GGSCI (fatdbatestlab1) 7> dblogin useridalias ogg11g
Successfully logged into database.

GGSCI (fatdbatestlab1) GGREPAZUR@boom1) 8> REGISTER EXTRACT ext11g DATABASE

xxxx:xxx:xxxx   ERROR   OGG-08221  Cannot register or unregister Extract group EXT11G because of the following SQL error: OCI Error ORA 
(status = 6502-ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 145
ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 186
ORA-06512: at line 1).

So Integrated Extract was the plan from day one. Even with that clarity, things didn’t go smoothly. Integrated Extract should work with 11g, specially with remote-extraction option due to security concerns and performance issues. The docs say so (if you are on compatible 11.2.0.4), compatibility matrices agree.

Now, just to be clear upfront .. I already knew that if I tried classic (non-integrated) extract remotely, I would hit “OGG-02022 Logmining server does not exist on this Oracle database.” .. That part wasn’t a surprise. Classic extract + remote server + 11g… yeah, that’s expected.

Yet I kept running into weird behavior that just didn’t add up. That’s when the doubt started creeping in … “Am I missing something?” “Is Integrated Extract actually usable with 11g in real life… not just on paper?”

Before touching the source server, I paused and went deeper into Oracle notes and bugs. That’s when I landed on the real issue. This wasn’t a GoldenGate 21c problem. And it wasn’t a remote extraction limitation either. It was an Oracle 11g RDBMS bug.

The Actual Root Cause was the database Bug 28367006.. Once I applied Patch 21683400 & the datapatch things finally started behaving like a sane system again.

GGSCI (fatdbatestlab1) GGREPAZUR@boom1) 8> REGISTER EXTRACT ext11g DATABASE
xxxx:xxx:xxx INFO    OGG-02003  Extract group EXT11G successfully registered with database at SCN 189381938103811.

It’s worth calling out that while Oracle 11g is technically supported for Integrated Extract, it is still a very old database release, and expectations need to be set accordingly. Running the latest available PSU is not optional in this kind of setup, and being aware of known bugs, defects, and architectural limitations is part of the job when working with legacy versions. In practice, if you stumble into a new or undocumented issue on 11g, Oracle Support is unlikely to engage development for a fresh bug fix, which means the only real options are workarounds, existing patches, or architectural adjustments. That reality alone makes proactive patching and careful design choices even more critical when pairing modern GoldenGate versions with older database platforms.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: datamigration, goldengate, integration | Leave a Comment »

« Previous Entries

Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes

Classic to Microservices – what actually changes when you migrate GoldenGate

Reading MySQL execution plans without guessing

Access types, which is best and the worst ??

Scenario one: the cardinality trap (and where the usual advice is wrong)

Scenario two: join order, and why it’s the whole game

Selecting and validating indexes

When MySQL OPTIMIZE TABLE made the file bigger instead of smaller and how old_alter_table solved the issue …

Using pathMappingTemplate and fileNameMappingTemplate in Oracle GoldenGate 23ai DAA

Oracle AI database 26ai .. unified hybrid Vector Search is the real deal

Hybrid Search —-> Semantic + Relational Filter Together … Now let’s be precise. We want connectivity errors only, and only severity 2 or higher:

Why oracle’s optimizer has been getting smarter for 15 years and what 26ai version actually adds

Oracle 26ai Assertions – The Database constraint that took years to ship

Real Time Data Replication from Oracle to DataStax Cassandra using Oracle GoldenGate 23ai DAA

Parquet, hadoop, and a quietly dying process : lessons from a migration test using GoldenGate 23ai DAA

Lessons from Integrating Oracle 11g with GoldenGate – ORA-06512 ORA-06502

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes

Share this:

Access types, which is best and the worst ??

Scenario one: the cardinality trap (and where the usual advice is wrong)

Scenario two: join order, and why it’s the whole game

Selecting and validating indexes

Share this:

Share this:

Share this:

Hybrid Search —-> Semantic + Relational Filter Together … Now let’s be precise. We want connectivity errors only, and only severity 2 or higher:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: