Yann Neuhaus

In part 1, UUIDv7 did not fix the page density of a GUID clustered index in SQL Server. SQL Server sorts uniqueidentifier values starting from the last six bytes, which are random in a UUIDv7, so new rows land anywhere in the index. After 25,000 single-row inserts, the UUIDv7 and NEWID() indexes were both ~68 % full, close to the 69 % expected for random inserts into a B-tree, against ~98 % for NEWSEQUENTIALID().

Since then, automatic index compaction has become generally available in Azure SQL Database. This post checks whether it closes that gap.

This is deliberately an unfavorable case for the feature. A random clustering key on a small table under steady inserts is close to the worst workload for page density. It is also a case that is spreading in production, as more applications generate GUID or UUIDv7 keys on their side.

My colleague Amine Haloui covered the feature in two posts: part 1 explains the difference between page density and fragmentation, part 2 shows how compaction works with a demo. The short version:

Automatic index compaction runs inside the persistent version store cleaner of Accelerated Database Recovery. It visits recently modified pages, moves rows from the next page into a page that has free space, and deallocates the pages that end up empty. You enable it per database with ALTER DATABASE ... SET AUTOMATIC_INDEX_COMPACTION = ON, without a restart.

It only acts on pages modified after it was enabled, does not move rows into the space reserved by the fill factor (nor free it once inserts have used it), does not reduce fragmentation and does not update statistics.

It has been generally available since September 2026 on Azure SQL Database, Azure SQL Managed Instance with the always-up-to-date update policy, and SQL database in Fabric. It is not in SQL Server 2025 on-premises, so all the tests below run on Azure SQL Database.

In short: when inserts pause, it closes most of the gap. While they keep coming, it cannot fix the underlying problem, a key that sends inserts to random positions. At the default fill factor the index even ends up larger than without compaction, and a lower fill factor limits the effect. Measuring page splits shows why: each compaction pass is followed by a wave of splits.

I found two representative public tests. In both the clustering key is ordered (Microsoft’s test table has a NEWID() column, but not in the key) and the empty space comes from deletes or from a workload that then stops:

Sources: Microsoft announcement post, Amine Haloui part 2.

In both cases, compaction brings page density back to about 95 % with no maintenance job. This post looks at the case these tests leave out: a random key under steady inserts.

Two Azure SQL databases on the same server, General Purpose serverless, 2 vCores: GuidLab_On with compaction, GuidLab_Off as the control. Every test runs in both at the same time. The tables are those of part 1: a uniqueidentifier clustered primary key filled by NEWID(), a UUIDv7 (RFC 9562) or NEWSEQUENTIALID(), plus a char(100) payload, with rows inserted one per statement.

Each run preloads 5,000 rows per table. The occasional-insert run then measures every 10 seconds for 3 minutes. The continuous-insert runs wait 2 minutes, insert a burst every 10 seconds for 3 minutes, then measure one more minute without inserts. Two parameters vary: the fill factor (100, 90 or 80) and the burst size, 300 rows (6 % of the table) or 50 rows (1 %). Pages and fullness come from sys.dm_db_index_physical_stats in DETAILED mode, rows moved from the compaction_row_move_count column of sys.dm_db_index_operational_stats.

5,000 rows inserted, then no more writes, as when inserts come in batches with quiet periods between them.

Without compaction, the index stays at 123 to 128 pages, 67 to 70 % full. With it, it drops to 92 to 94 pages, 91 to 93 % full, in under two minutes, close to the 87 pages of the sequential key. Most of the drop happens in a single pass.

Compaction also moves some rows more than once: 6,345 and 8,991 moves for 5,000 rows.

Now 300 rows per table every 10 seconds, 6 % of the table per burst, for three minutes, then one minute without inserts. Each configuration ran at least twice; the figures below cover all runs and both random keys. The chart shows the first run, for UUIDv7.

At fill factor 100, compaction cannot keep up with this pattern. In the first run, the first burst takes the compacted index from 93 to 172 pages. At the end of the inserts it holds 326 to 334 pages, against 253 to 271 without compaction, and average fullness during the inserts is 56 %, against 68 to 70 %. Over the run, compaction moved more than 51,000 rows for a table of 10,400.

The explanation is that an index nobody compacts keeps about 31 % (100 minus 69) free space, which absorbs part of the next inserts. At fill factor 100, compaction moves rows into a page as long as they fit, so most pages are left with little room, and the next random insert splits them. Each split also writes far more log than a plain insert, about 18 times more for rows of similar size in Paul Randal’s test.

A lower fill factor helps. When pages split again soon after compaction, the documentation suggests a fill factor of 70 to 95 %, while noting that most workloads do best at 100, and the index maintenance guidance gives nonsequential GUID keys as its example. At 80, compaction leaves 20 % of each page free, at 90 only 10 %. That free space lets a page absorb a few random inserts before it splits.

At 80, average fullness during the inserts is 76 % in all three runs, against 68 to 70 % without compaction, and one minute after the inserts the index holds 223 to 230 pages, against 253 to 271. The gain is real but modest at this rate. At 90, the margin is too thin: average fullness is 66 to 69 %, and one run ended the inserts with 324 pages, more than without compaction.

Once the inserts stop, fill factor 100 catches up in one pass and ends the smallest: 202 to 207 pages.

To check the explanation above, I ran the 6 % test twice more at fill factor 100 and also recorded two counters from sys.dm_db_index_operational_stats: leaf_allocation_count, the number of leaf pages allocated, and compaction_page_deallocation_count, the number of pages compaction freed. Compaction only moves rows into pages that have room and frees the pages that end up empty, so the allocations come from the inserts. With a random key, new rows land anywhere in the index, so almost every allocation is a page split in the middle of it; with NEWSEQUENTIALID(), rows always go to the end, so every allocation is a new page there.

Without compaction, the 5,400 inserts split about 130 pages. With compaction, about 460: 3.5 times more.

The chart shows where they come from. Compaction ran about once a minute, and each time, the next burst split 78 to 133 pages. The bursts in between split 0 to 21, because the pages split by the previous wave were still half full and could take the new rows. The four bursts that followed a pass account for 90 % of the splits, and the page count follows the same steps.

From the first burst to the end of the run, compaction moved 46,000 to 47,600 rows to free about 350 pages, about 130 rows per page freed. The counters add up: in the first run of this test (UUIDv7), 94 pages at the start, plus 461 allocations, minus 224 pages freed during the inserts, gives the 331 pages measured at the end of the inserts.

The same test at 50 rows per burst, 1 % of the table, also run at least twice. The chart shows the first run, for UUIDv7.

Fill factor 100 still degrades. In the first run, the first 50 inserts turned 95 pages into 128: about two inserts in three split a page. The index ends the inserts at 176 to 194 pages, against 143 to 153 without compaction. The lower rate only slows the degradation.

At 90 and 80, compaction helps throughout. Average fullness stays at 81 to 85 %, against 66 to 70 % without compaction, and the index ends at 115 to 121 pages, about 20 % fewer. Fullness never drops below 77 % at 90 and 74 % at 80. The two fill factors end at the same size.

At fill factor 80, the inserts slowly fill the 20 % that compaction leaves free: fullness rose from 73-76 % before the first burst to about 85 % in three minutes. Compaction does not give that space back. As the documentation puts it, “if that reserved space is already filled by the previous data modifications, then compaction doesn’t currently free it up”. Only a rebuild restores the margin.

Each cell gives the range over all runs and both random keys (NEWID() and UUIDv7). The occasional-insert case ran once; its cells read NEWID() / UUIDv7.

Occasional inserts (5,000 rows, then 3 minutes without writes)

Continuous inserts, 6 % per burst (300 rows every 10 s, 10,400 rows at the end)

Continuous inserts, 1 % per burst (50 rows every 10 s, 5,900 rows at the end)

“Rows moved” counts every row compaction moved during the run: after the preload, during the inserts and in the final pass after them. “Pages at the end of the inserts” is a snapshot that depends on when the last compaction pass ran, so average fullness is the more stable measure. NEWSEQUENTIALID() gives the same figures with and without compaction: there is nothing to compact.

1. Once the inserts stop, compaction brings the random keys well above 68 %. Held: 91 to 93 % within two minutes.
2. Under continuous inserts, fullness drops after each burst and recovers between them, averaging somewhere in between. Wrong at the default fill factor: it averages below the uncompacted index, not above it.
3. NEWID() and UUIDv7 behave the same. Held for pages and fullness: the averages stay within three points. Rows moved differ by up to 50 % between the two keys, in no consistent direction; probably because the rows moved depend on how free space is spread across the index, which is random.
4. Compaction finds nothing to do on NEWSEQUENTIALID(). Held: zero rows moved in every run.

For a random clustering key, automatic index compaction is worth enabling, but it does not address the root cause: inserts that land at random positions. The fill factor decides how much it can help.

• Inserts in batches with quiet periods: the default fill factor of 100 works. Compaction catches up within a minute or two and gives the densest random-key index. Each new batch still splits many of the freshly compacted pages.
• A steady stream of inserts: at the default fill factor, compaction makes the index larger than doing nothing. A fill factor of 80 helped at both rates tested; 90 helped only at the gentler one.
• Existing indexes: compaction ignores pages not modified since you enabled it, so rebuild once first. The rebuild is also where you set the fill factor.
• Watch page fullness and log volume, not fragmentation, which can go up with compaction.
• The key choice from part 1 still stands: the sequential key stayed the smallest in every run, and compaction never fully closed the gap. These results emphasize how much the application side matters. The clustering key is usually chosen by developers, often for good reasons, so it is a decision worth taking together with whoever runs the database, early, before the table holds millions of rows.
• And on-premises nothing changes for now, since the feature is not in SQL Server 2025.

• Small tables, few runs per configuration. 5,000 to 10,400 rows. Control runs of the same setup differ by up to 18 pages.
• Intense rates. 1 % and 6 % of the table every 10 seconds. On a large table, the same inserts per second touch a far smaller share of its pages; how that changes the outcome was not tested.
• Short runs. Three minutes of inserts; how the index behaves at 80 or 90 over hours was not measured.
• An unfavorable case by design. These results say nothing about how the feature behaves on other workloads.
• One platform. Azure SQL Database, General Purpose serverless, 2 vCores, one inserting session.

If you want to reproduce these tests, feel free to contact me and I will share the full scripts.

L’article Azure SQL Database: does automatic index compaction fix the UUIDv7 density problem? est apparu en premier sur dbi Blog.

I started the second day of PGConf.EU with a session about vacuum and autovacuum, presented by Teresa Lopes.
After a short introduction to how MVCC works, she presented the different elements related to autovacuum:
What are the different phases of autovacuum? What can trigger autovacuum? Which parameters can influence autovacuum? All these points were explained in a simple and clear way during the presentation.
Teresa also presented several reasons why autovacuum might not start, including a long-running transaction, an abandoned replication slot or an abandoned prepared transaction.

The second half of the presentation focused on new autovacuum features in PostgreSQL 19, for example:

• The scoring system for autovacuum, based on criteria such as XID/MXID age and the number of inserted, updated or deleted tuples.
• The possibility to use parallel workers for index vacuuming and cleanup.
• Improved ways to observe and monitor autovacuum, especially through new views and new logging parameters.

The session ended with a look at how to recover disk space in tables after autovacuum has cleaned them up, especially with REPACK, which also has a CONCURRENTLY option that allows other transactions to use the table while it is being repacked.

Speaking about REPACK, this new feature was also presented by Rajni Baliyan during her presentation about how to achieve zero downtime with PostgreSQL 19.
REPACK CONCURRENTLY allows tables to be reorganized and disk space to be recovered without blocking normal database operations for the entire process. Unlike VACUUM FULL, which blocks access to the table during the operation, REPACK CONCURRENTLY allows applications to continue reading and writing data while a new copy of the table and its indexes is being created.
In the background, PostgreSQL uses logical decoding to capture changes made to the original table during this process and applies them to the new copy. At the end, it briefly takes an exclusive lock to replace the old table and indexes with the new ones.

An other interesting feature is the new logical sequence synchronization worker, which helps keep sequence values synchronized between the publisher and the subscriber during logical replication.
Before PostgreSQL 19, sequence values were not automatically synchronized through logical replication. This could cause problems during major version upgrades, as sequences on the new server could have outdated values and generate duplicate IDs.
With PostgreSQL 19, sequences can now be synchronized automatically when a subscription is created. The REFRESH SEQUENCES command can also be used to synchronize them again when needed.

As you may have guess, the goal of this presentation was to show how new features can help reduce service interruptions during database maintenance and remove the need for maintenance windows. The presentation focused on making database operations easier and safer while keeping services available for users.
It was an interesting topic, especially for production environments where downtime must be avoided as much as possible.

I then attended a session about pg_hardstorage, presented by Hans-Jürgen Schönig.
pg_hardstorage is a modern backup solution for PostgreSQL, designed especially for large environments and critical systems.
Unlike traditional backup tools, it uses the PostgreSQL replication protocol to retrieve a base backup and continuously stream WAL files. It does not require an agent or direct shell access on the database server. The tool runs on a separate backup host.

One interesting feature is its storage system. Each backup is treated as a full backup, but duplicate data blocks are stored only once. This reduces storage requirements without the complexity of traditional incremental backup chains.
The continuous WAL streaming also allows point-in-time recovery, making it possible to restore a database to a specific moment in time.

Another advantage is that pg_hardstorage can store backups on a filesystem or S3-compatible object storage. It also supports different PostgreSQL environments, including bare-metal servers, Patroni clusters and Kubernetes deployments.

Overall, it was an interesting presentation about an alternative approach to PostgreSQL backups, with a strong focus on simplicity, storage efficiency and reliable recovery.

I then attended a session presented by Alexander Kukushkin and Polina Bungina, two of the main contributors to Patroni.
The presentation explained how Patroni really works behind the scenes, with a special focus on automatic failover and the challenges of managing a high-availability PostgreSQL cluster.

One important topic was the risk of split-brain, when two PostgreSQL nodes believe they are the primary at the same time. The speakers explained why a simple two-node setup is not enough and why quorum and fencing mechanisms are important to prevent this situation.
They also explained how Patroni uses a Distributed Configuration Store (DCS), such as etcd, to coordinate the cluster, manage the leader election and keep track of the state of each node.
The presentation also covered some important features, such as synchronous replication, standby clusters and DCS failsafe mode. Finally, they presented some upcoming features, including site-aware failover, which will help Patroni make better decisions when a cluster is spread across several data centers.

This was a very nice session to better understand the internal mechanisms of Patroni and the challenges of building a reliable PostgreSQL high-availability architecture.

Due to meetings with my customers, I unfortunately couldn’t attend any other sessions on Wednesday. However, I just managed to catch my colleague Hervé Schweitzer presentation of our OMrun tool during the “Silver Sponsor Lightning Talks”.

Are you interested in data quality?
Take a look here to learn more about OMrun: https://www.dbi-services.com/fr/produits/omrun/

On Thursday, I attended a session presented by Jack Bonatakis about Hackorum, a tool designed to make PostgreSQL mailing lists easier to use for both experts and newcomers.
PostgreSQL development relies heavily on mailing lists, but following long email discussions can be difficult, especially when there are many messages.
Hackorum provides a modern, forum-style web interface on top of the existing mailing lists. It does not replace them. Instead, it makes discussions easier to read, follow and organize. Some of its main features are:

• Better discussion view: messages are grouped into topics, making long conversations easier to follow.
• Read tracking: you can see which messages you have already read and continue where you stopped.
• Commitfest integration: information about patches and their status is available directly alongside the related discussions.
• Tags, notes and favourites: users can organize discussions, save important topics and add personal notes.
• Team collaboration: reading status, tags and notes can be shared with other team members.

Compared to traditional mailing lists, Hackorum offers a more visual and user-friendly interface. It helps reduce the number of emails to manage and makes it easier to follow several discussions at the same time. The integration with Commitfest is also useful for PostgreSQL developers who want to follow patches and their progress.
One important point is that the mailing lists remain the main source of information. Hackorum simply provides a more convenient way to access and organize their content.

I found this an interesting initiative that could make the PostgreSQL community more accessible, especially for newcomers who may find the traditional mailing lists difficult to follow.

I finished the conference with a presentation by Jimmy Angelakos about radar and radar-analyst, two tools designed to simplify PostgreSQL diagnostics.
The idea behind radar is simple: instead of installing monitoring agents and collecting information over a long period, you can run a single command to collect a snapshot of the PostgreSQL server and its operating system.
The tool is agentless and does not require any additional dependencies. It creates a ZIP archive containing useful diagnostic information, including PostgreSQL configuration, database statistics, replication status, table and index information, as well as system resources such as CPU, memory and disk I/O.

The second tool, radar-analyst, analyzes this archive and helps identify potential problems. It uses predefined rules to check different areas, including PostgreSQL configuration, workload, replication and operating system settings. Each finding includes an explanation and recommendations to help fix the problem.

Compared to traditional monitoring solutions, this approach has several advantages. There is no need to install or maintain an agent, and a diagnostic snapshot can be collected when a problem occurs, even if no monitoring was configured beforehand.

I found this to be a practical approach to PostgreSQL troubleshooting. It can be particularly useful for support teams and DBAs who need to quickly understand the state of a database server without spending hours collecting information manually.

L’article PGConf.EU 2026 – Day 2 & 3 est apparu en premier sur dbi Blog.

I often need to retrieve report files or logs from GoldenGate deployments, and I wanted to find ways of automating this. Hence this blog, about retrieving all available logs with GoldenGate REST API.

I previously wrote about the GoldenGate log files. Some of them (extract.log, replicat.log and others) are in fact just filters of other logs (ggserr.log or restapi.log). These reconstructed logs do not have a REST API endpoint. For the others, let’s see how to retrieve them, using the GoldenGate Python client I released earlier.

With oggrestapi.py, authentication is handled once at client creation. When using auto_discovery, you won’t have to bother thinking about the port.

from oggrestapi import OGGRestAPI

ogg_client = OGGRestAPI(
    url="https://vmogg:7809",
    username="ogg",
    deployment="ogg_test_01",
    auto_discovery=True,
)

With auto_discovery, the client picks the right service port for each call. If you make direct curl/requests calls instead, you have to choose the service yourself:

• Service Manager for ServiceManager.log and the deployment service logs (adminsrvr.log, distsrvr.log, etc.)
• Administration Service for report files, diagnostic files, ggserr.log. You can also retrieve adminsrvr.log from here.

The list of log files can be retrieved through GET /services/v2/logs. Each individual log is obtained through GET /services/v2/logs/{log}, on the port of the service you connect to. default is the service log: against adminsrvr (port 7810), default is adminsrvr.log. To retrieve the content of the file, you need to send the Accept: text/plain header, or use the content toggle in oggrestapi.py.

# /services/v2/logs - List all logs
>>> ogg_client.list_logs(ogg_service="adminsrvr")
[{'name': 'default'}, {'name': 'restapi'}, {'name': 'stdout'}, {'name': 'debug'}, {'name': 'events'}]

# /services/v2/logs/default - Get adminsrvr.log metadata
>>> ogg_client.get_log(log="default", ogg_service="adminsrvr")
{'$schema': 'ogg:log', 'enabled': True, 'dataExists': True}

# /services/v2/logs/default (Accept: text/plain) - Get adminsrvr.log content
>>> ogg_client.get_log(log="default", ogg_service="adminsrvr", content=True)
"...\n2026-09-26T17:21:30.612+0000 INFO | Registering containers <nul> (Thread 7)\n2026-09-26T17:21:31.820+0000 INFO | Configuration file 'EXT.prm' changed (Thread 7)\n2026-09-26T17:21:31.881+0000 INFO | Starting extract 'EXT' (EXTRACT:EXT)\n2026-09-26T17:21:31.915+0000 INFO | Task 'EXTRACT:EXT' started with process id 39143 (EXTRACT:EXT)\n"

You can also use the get_service_log directly, when using a reverse proxy or connecting to the Service Manager:

# /services/v2/deployments/ogg_test_01/services/distsrvr/logs/default (Accept: text/plain) - Service Manager endpoint
# /services/ServiceManager/v2/deployments/ogg_test_01/services/distsrvr/logs/default (Accept: text/plain) - reverse proxy endpoint
>>> ogg_client.get_service_log(deployment="ogg_test_01", service="distsrvr", content=True)
"...\n2026-09-26T17:33:46.183+0000 INFO | remove path Command Received for path PATHX (PATHX)\n2026-09-26T17:33:46.183+0000 WARN | Path stopped. (PATHX)\n"

The debug log is only relevant when you enable debug logging on a specific service. events is the critical events log (same content as ER-events.log), reachable directly as GET /services/v2/logs/events too, wrapped as list_log_events() :

# /services/v2/logs/events (Accept: text/plain)
>>> ogg_client.list_log_events(content=True)
"...Oracle GoldenGate Capture for Oracle, EXT.prm:  Checkpoint marked as from graceful shutdown, but records found after checkpoint in trail pdb1/e1.  Expected EOF Seqno 0, RBA 0.  Found Seqno 7, RBA 8884.\n"

From the Service Manager (port 7809 in my lab), default is the ServiceManager.log:

# /services/v2/logs/default (Accept: text/plain)
>>> ogg_client.get_service_manager_log()
"...2026-09-26T15:46:20.138+0000 INFO | CSRFTokenProtection: ENABLED.\n                                    Cross-Site Request Forgery checks using CSRF-Tokens will be performed. (Thread 12)\n"

get_service_manager_log() defaults to content=True, since fetching the metadata for this one specific log is not relevant (this log is always enabled).

ggserr.log, the deployment-wide message log, is not one of the names any /logs collection returns. It is provided by a separate Administration Service endpoint at /services/v2/messages. A messages key returns the log lines.

# /services/v2/messages
>>> lines = ogg_client.list_messages()["messages"]
>>> len(lines)
53035

Once you find an OGG- message code in these lines, another endpoint (GET /services/v2/oggerr/{message}) returns its explanation:

>>> ogg_client.get_ogg_error_info(message="OGG-30375", ogg_service="adminsrvr")
{'$schema': 'ogg:messageExplanation', 'severity': 'ERROR', 'title': 'There was a problem updating the Administration Service resources.', 'cause': 'An internal error occurred when trying to update the service resources. Possible causes include a corrupted or incorrect resources repository.', 'action': 'Check the error log for other messages adjacent to this one that might help you resolve the problem. If you cannot resolve the problem, contact Oracle Support.'}

adminclient.log is the client’s connection log. It is located in the client installation at $OGG_HOME/var/log/adminclient.log, not in any deployment, and no service can read it.

The report files are accessible with list_extract_reports and get_extract_report:

# /services/v2/extracts/EXT/info/reports
>>> ogg_client.list_extract_reports(extract="EXT")
[{'name': 'EXT.dsc'}, {'name': 'EXT.rpt'}]

# /services/v2/extracts/EXT/info/reports/EXT.rpt
>>> ogg_client.get_extract_report(extract="EXT", report="EXT.rpt")
{'lines': ['', '***********************************************************************', '                 Oracle GoldenGate Capture for Oracle', ...], '$schema': 'ogg:report'}

get_current_extract_report() skips the .dsc, resolves the current .rpt for you, and returns None if the extract has no report listed :

# /services/v2/extracts/EXT/info/reports
# /services/v2/extracts/EXT/info/reports/EXT.rpt
>>> ogg_client.get_current_extract_report(extract="EXT")
{'lines': ['', '***********************************************************************', '                 Oracle GoldenGate Capture for Oracle', ...], '$schema': 'ogg:report'}

The same endpoints/methods exist for replicats.

If a diagnostics collection exists, it is a resource named {process}.diagnostics, listed under the same info path as reports. It has a status while it runs, and the start and finish times once done :

# /services/v2/extracts/EXT/info/diagnostics - while running
>>> ogg_client.list_extract_diagnostics(extract="EXT")
[{'name': 'EXT.diagnostics', 'status': 'running', 'started': '2026-09-26T17:23:21Z'}]

# /services/v2/extracts/EXT/info/diagnostics - when done
>>> ogg_client.list_extract_diagnostics(extract="EXT")
[{'name': 'EXT.diagnostics', 'status': 'stopped', 'started': '2026-09-26T17:23:21Z', 'finished': '2026-09-26T17:33:22Z'}]

When several collections exist, the previous ones are kept as EXT.diagnostics.1 (and so on), and the newest keeps the base name. An extract that never had a collection returns an empty list. It returns a JSON, but unlike reports and configuration files, it is a structured document with one entry per sample:

# /services/v2/extracts/EXT/info/diagnostics/EXT.diagnostics
>>> diag = ogg_client.get_extract_diagnostic(extract="EXT", diagnostic="EXT.diagnostics")
>>> list(diag)
['$schema', 'started', 'finished', 'collectionFrequency', 'collectionDuration', 'status', 'samples']
>>> diag["samples"][0]["processSamples"][0]["process"]
{'type': 'capture', 'state': {'status': 'waiting', 'detail': 'WAITING FOR TRANSACTION'}, 'bytesMined': 2264, 'bytesSent': 1524, 'messagesCaptured': 5, 'lag': 0}

The same pair exists for a replicat: list_replicat_diagnostics() / get_replicat_diagnostic().

GLOBALS, parameter files, and the rest of what a deployment keeps under its config directory come from /services/v2/config/files. That directory is $OGG_CONF_HOME, or $OGG_DEPLOYMENT_HOME/etc/conf. They return their content directly:

# /services/v2/config/files
>>> ogg_client.list_configuration_files(ogg_service="adminsrvr")
[{'name': 'EXT1.prm'}, {'name': 'EXT2.prm'}, {'name': 'REP1.prm'}, ...]

# /services/v2/config/files/EXT1.prm
>>> ogg_client.get_configuration_file(file="EXT1.prm", ogg_service="adminsrvr")
{'lines': ['EXTRACT EXT1', 'USERIDALIAS pdb1 DOMAIN OracleGoldenGate', 'EXTTRAIL qa/xa', 'TABLE APP_PDB1.TEST;'], '$schema': 'ogg:config'}

To summarize, for each file:

L’article Retrieving GoldenGate logs, reports, and diagnostics with the REST API est apparu en premier sur dbi Blog.

During a recent Oracle Fusion Middleware upgrade from 12.2.1.4 to 14.1.2, I encountered an interesting issue with Oracle HTTP Server (OHS) and WebGate. The upgraded components were running, but WebGate initialization failed and the application was inaccessible.

The root cause was not an incorrect OAM registration or a missing configuration file. It was a stale ObAccessClient.xml in the OHS/WebGate runtime cache.

After upgrading OHS and WebGate to 14.1.2, the application returned HTTP 500 errors. The WebGate logs reported:

Exception thrown during WebGate initialization

The Access Server has returned a fatal error with no detailed information.

Additional errors included:

OAP over REST libcurl operation failed

These messages initially suggested an OAM connectivity or agent configuration issue.

However, the OAM endpoint was reachable:

curl -sv \
  http://<oam-host>:14100/iam/access/binding/api/v10/oap \
  -o /dev/null

Response:

HTTP/1.1 405 Method Not Allowed

This confirmed HTTP connectivity to the endpoint, although it did not validate the complete OAP authentication exchange.

The next step was to verify whether WebGate was using the expected configuration.

WebGate uses ObAccessClient.xml and wallet files for its OAM agent configuration. I identified three relevant locations:

# OAM-generated configuration
$IDM_DOMAIN/output/<agent_name>/

# Active WebGate configuration
$ADF_DOMAIN/config/fmwconfig/components/OHS/instances/ohs1/webgate/config/

# WebGate runtime cache
$ADF_DOMAIN/servers/ohs1/cache/

First, I compared the SHA-256 checksums of the OAM-generated files against the active OHS configuration.

All three files were identical between the OAM-generated configuration and the active OHS configuration.

This was important: the deployed WebGate configuration and wallets were consistent with OAM.

I then compared the active ObAccessClient.xml with the cached version.

sha256sum \
  $ADF_DOMAIN/servers/ohs1/cache/ObAccessClient.xml \
  $ADF_DOMAIN/config/fmwconfig/components/OHS/instances/ohs1/webgate/config/ObAccessClient.xml

Actual output:

a1a5932a32b1c178461ab4cb373a14ff88446a6ad0cd9d2cdade90e5aecd4800  cache/ObAccessClient.xml
b110e7f0c8b153c1d17b7b79d4fc8273623fdb42dabafe0054d7c13e025d3f07  webgate/config/ObAccessClient.xml

The checksums are different. The cache directory also contained:

-rw-r----- 7618 Oct 20 16:26 ObAccessClient.xml
-rw-r-----    0 May 25  2021 ObAccessClient.xml.lck
-rw-r-----    0 May 25  2021 polltracking.lck

While different checksums do not automatically indicate corruption, the cached configuration was inconsistent with the verified active configuration. This became my primary suspect.

I stopped OHS using the domain management script:

$ADF_DOMAIN/bin/stopComponent.sh ohs1

Output:

Successfully Connected to Node Manager.
Killing server ohs1 ...
Successfully killed server ohs1

I confirmed that no OHS HTTP processes remained:

ps -ef | grep '[h]ttpd'

No processes were returned. Next, I preserved the existing cached configuration:

cd $ADF_DOMAIN/servers/ohs1/cache/
cp -p ObAccessClient.xml \
  ObAccessClient.xml.pre14c_20261023

I removed only the cached configuration and its associated lock file:

rm -f ObAccessClient.xml ObAccessClient.xml.lck

The active WebGate configuration and wallets were left untouched. I then restarted OHS:

$ADF_DOMAIN/bin/startComponent.sh ohs1

Output:

Successfully Connected to Node Manager.
Starting server ohs1 ...
Successfully started server ohs1 ...

An interesting observation occurred immediately after the restart. The initial directory listing showed that ObAccessClient.xml had not yet been recreated:

ObAccessClient.xml.pre14c_20261023
polltracking.lck

A subsequent check showed that WebGate had automatically regenerated its runtime cache:

-rw-r----- 7618 Oct 23 10:39 ObAccessClient.xml
-rw-r-----    0 Oct 23 10:39 ObAccessClient.xml.lck
-rw-r----- 7618 Oct 20 16:26 ObAccessClient.xml.pre14c_20261023

The newly generated files had fresh timestamps, while the backup preserved the original cached configuration.

Following this operation, WebGate initialized successfully and the protected application became accessible again through the expected authentication flow.

No modification to the active configuration, wallet replacement, agent re-registration or WebGate reinstallation was necessary.

This case demonstrates an important troubleshooting consideration after upgrading Oracle HTTP Server and WebGate.

Even when the active ObAccessClient.xml and wallet files match the configuration generated by OAM, an outdated WebGate runtime cache can still interfere with initialization.

For future Oracle FMW upgrades, comparing the active and cached WebGate configurations is a valuable diagnostic step when authentication fails despite apparently correct OAM integration.

Related blogs.

L’article Oracle OHS 14.1.2 Upgrade: Cache Broke Authentication est apparu en premier sur dbi Blog.

Recently, I was working on deploying Metric Extensions automatically in Enterprise Manager, but the more I looked into it, the more it turned into a different question: what does a Metric Extension look like ? In a previous blog, I presented the SYSMAN tables containing the metric extensions definitions. I also showed how to retrieve the targets where a metric extension is deployed. Now, it’s time to look at formatting metric extensions.

Once again, emcli help does not show any verb for creating metric extensions:

$ emcli help | grep -i metric_extension
    export_metric_extension           -- Export a metric extension to an archive file
    get_unused_metric_extensions      -- Get a list of unused metric extensions
    import_metric_extension           -- Import a metric extension archive file
    publish_metric_extension          -- Publish a metric extension for use by all administrators
    save_metric_extension_draft       -- Save a deployable draft of a metric extension

Unfortunately, nothing in the documentation will help us to create a metric extension outside of the web UI. So let’s reverse engineer them. The plan is the following:

• Create metric extensions in the web UI
• Export the XML files with emcli
• Later, create new metric extensions or update existing metrics more easily, directly with emcli

Let’s build the most basic metric extension first, with Host as the target type. I will choose the OS Command - Multiple Columns adapter, and just an echo command.

The adapter itself is /bin/echo with em_result=1 as its argument and em_result= as the Starts With prefix. It is the same em_result=-prefixed, delimiter-tokenized convention the OEM plugin’s OSLineToken fetchlet uses, which turned out not to be a coincidence (more on that below). One column, value, type Number:

The Test step lets you run the metric job for real against a live target before committing to anything. Since the idea is to have a valid archive that we can play with, it should definitely be tested.

You cannot export an Editable metric. The docs restrict export_metric_extension to Production, Deployable Draft, and Published versions. So the first step is promoting it, either via the UI or with emcli:

$ emcli save_metric_extension_draft -target_type=host -name='ME$minimal_uptime_check' -version=1

Don’t worry if you receive no output, this is expected. Then, you can export the metric:

$ emcli export_metric_extension -file_name=/tmp/minimal_uptime_check_v1.zip -target_type=host -name='ME$minimal_uptime_check' -version=1
Metric extension [host, ME$minimal_uptime_check, 1] exported successfully to file /tmp/minimal_uptime_check_v1.zip

There are three files in the archive:

metadata/ME#24#minimal_uptime_check.xml
collection/ME#24#minimal_uptime_check.xml
mea.xml

The first one is the manifest, mea.xml:

<MetricExtensionArchive name="MEAME$minimal_uptime_check">
<MetricExtension name="ME$minimal_uptime_check" type="host" version="1" is_repos="FALSE" display_name="Minimal Uptime Check" description="Minimal test ME - single OS command column, no credentials." version_comment=" " metadata_digest="18147044f9d9fab88c8ebbee213d2e1e" coll_digest="e1a287a6df820c5e7d7fda0bd0bfcb3f" attach_digest="d41d8cd98f00b204e9800998ecf8427e">
</MetricExtension>
</MetricExtensionArchive>

The metric definition is in metadata/ME#24#minimal_uptime_check.xml:

<TargetMetadataExt EXT_NAME="ME$minimal_uptime_check" EXT_VERSION="1" TARGET_TYPE="host"><Metric NAME="ME$minimal_uptime_check" TYPE="TABLE">
<Display>
<Label NLSID="NLS_METRIC_hostME$minimal_uptime_check">Minimal Uptime Check</Label>
<Description NLSID="NLS_DESCRIPTION_hostME$minimal_uptime_check">Minimal test ME - single OS command column, no credentials.</Description>
</Display>
<TableDescriptor>
<ColumnDescriptor NAME="value" TYPE="NUMBER">
<Display>
<Label NLSID="NLS_COLUMN_hostME$minimal_uptime_checkvalue">Value</Label>
</Display>
</ColumnDescriptor>
</TableDescriptor>
<QueryDescriptor FETCHLET_ID="OSLineToken">
<Property NAME="command" SCOPE="GLOBAL">/bin/echo</Property>
<Property NAME="scriptLoc" SCOPE="GLOBAL" OPTIONAL="TRUE">%scriptsDir%</Property>
<Property NAME="args" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=1</Property>
<Property NAME="delimiter" SCOPE="GLOBAL" OPTIONAL="TRUE">|</Property>
<Property NAME="startsWith" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=</Property>
<Property NAME="ENVEM_TARGET_NAME" SCOPE="INSTANCE" OPTIONAL="TRUE">NAME</Property>
<CredentialRef NAME="OSCreds"></CredentialRef>
</QueryDescriptor>
</Metric>
</TargetMetadataExt>

Here, we can see that:

• FETCHLET_ID="OSLineToken" is the same fetchlet a full OEM plugin’s TargetMetadata XML uses for its OSLineToken-based collectors. The wizard is just generating the same fetchlet configuration.
• command, args, delimiter and startsWith are the basic properties fields the adapter step.
• ENVEM_TARGET_NAME is set even though I did not alter the Environment Variables section. This is the default.

The last file, collection/ME#24#minimal_uptime_check.xml, is the schedule:

<TargetCollectionExt EXT_NAME="ME$minimal_uptime_check" EXT_VERSION="1" TARGET_TYPE="host"><CollectionItem NAME="ME$minimal_uptime_check" UPLOAD="YES">
	<Schedule>
		<IntervalSchedule INTERVAL="15" TIME_UNIT="Min"/>
	</Schedule>
	<MetricColl NAME="ME$minimal_uptime_check">
	</MetricColl>
</CollectionItem>
</TargetCollectionExt>

Let’s build a second metric extension from the UI with the same command, but with two columns instead of one. I will also add a custom environment variable this time.

For comparison, this is what the Environment Variables table looks like before you add anything to it: EM_TARGET_NAME is already there.

And the second column added on the Columns step:

Exporting this metric extension and comparing the metadata against the first archive shows exactly what each choice changes:

$ diff unpacked/metadata/*.xml unpacked_v2/metadata/*.xml
1c1
< <TargetMetadataExt EXT_NAME="ME$minimal_uptime_check" EXT_VERSION="1" TARGET_TYPE="host"><Metric NAME="ME$minimal_uptime_check" TYPE="TABLE">
---
> <TargetMetadataExt EXT_NAME="ME$minimal_uptime_check_v2" EXT_VERSION="1" TARGET_TYPE="host"><Metric NAME="ME$minimal_uptime_check_v2" TYPE="TABLE">
3,4c3,4
< <Label NLSID="NLS_METRIC_hostME$minimal_uptime_check">Minimal Uptime Check</Label>
< <Description NLSID="NLS_DESCRIPTION_hostME$minimal_uptime_check">Minimal test ME - single OS command column, no credentials.</Description>
---
> <Label NLSID="NLS_METRIC_hostME$minimal_uptime_check_v2">Minimal Uptime Check V2</Label>
> <Description NLSID="NLS_DESCRIPTION_hostME$minimal_uptime_check_v2">Variant with env var and 2 columns, for archive-format comparison.</Description>
9c9,14
< <Label NLSID="NLS_COLUMN_hostME$minimal_uptime_checkvalue">Value</Label>
---
> <Label NLSID="NLS_COLUMN_hostME$minimal_uptime_check_v2value">Value</Label>
> </Display>
> </ColumnDescriptor>
> <ColumnDescriptor NAME="state" TYPE="STRING">
> <Display>
> <Label NLSID="NLS_COLUMN_hostME$minimal_uptime_check_v2state">State</Label>
16c21
< <Property NAME="args" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=1</Property>
---
> <Property NAME="args" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=1|running</Property>
19a25
> <Property NAME="ENVMY_CUSTOM_VAR" SCOPE="INSTANCE" OPTIONAL="TRUE">hello_world</Property>

The second extension was saved under a new name, ME$minimal_uptime_check_v2, so every EXT_NAME, NAME and NLSID differs. Removing this noise, there are three changes that matter: the new state column, the args property, and the environment variable ENVMY_CUSTOM_VAR.

...
> <ColumnDescriptor NAME="state" TYPE="STRING">
> <Display>
> <Label NLSID="NLS_COLUMN_hostME$minimal_uptime_checkstate">State</Label>
> </Display>
> </ColumnDescriptor>

...

< <Property NAME="args" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=1</Property>
---
> <Property NAME="args" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=1|running</Property>

...

> <Property NAME="ENVMY_CUSTOM_VAR" SCOPE="INSTANCE" OPTIONAL="TRUE">hello_world</Property>

The new column is a second ColumnDescriptor, in the same order as the pipe-delimited output (em_result=1|running). The environment variable becomes ENVMY_CUSTOM_VAR=hello_world.

Both examples above run an OS command directly. Let’s create a metric extension that runs an uploaded script instead of a literal command. Exporting a metric extension with an uploaded Perl script adds a fourth path to the archive, alongside the usual mea.xml/metadata/collection:

mea.xml
metadata/ME#24#script_uptime_check.xml
collection/ME#24#script_uptime_check.xml
scripts/uptime_check.pl

The manifest lists the script by its real name, in a <scripts> element, with an updated attach_digest parameter.

<MetricExtensionArchive name="MEAME$script_uptime_check">
<MetricExtension name="ME$script_uptime_check" type="host" version="1" is_repos="FALSE" display_name="Script Uptime Check" description="ME cloned from minimal_uptime_check, adapter modified to run an uploaded script." version_comment=" " metadata_digest="e4778d5b4a756fed3d7e73aa55931d88" coll_digest="bc223105d5b2e6e31d5123eadcbd929b" attach_digest="d04278db542142ccb9a89be64c3e151e">
<scripts name="uptime_check.pl">
</scripts>
</MetricExtension>
</MetricExtensionArchive>

The QueryDescriptor has a new property called script, giving the script name. The command property is also updated, pointing at perl instead of /bin/echo.

<QueryDescriptor FETCHLET_ID="OSLineToken">
<Property NAME="command" SCOPE="GLOBAL">%perlBin%/perl</Property>
<Property NAME="script" SCOPE="GLOBAL" OPTIONAL="TRUE">%scriptsDir%/uptime_check.pl</Property>
<Property NAME="scriptLoc" SCOPE="GLOBAL" OPTIONAL="TRUE">%scriptsDir%</Property>
<Property NAME="delimiter" SCOPE="GLOBAL" OPTIONAL="TRUE">|</Property>
<Property NAME="startsWith" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=</Property>
<Property NAME="ENVEM_TARGET_NAME" SCOPE="INSTANCE" OPTIONAL="TRUE">NAME</Property>
<CredentialRef NAME="OSCreds"></CredentialRef>
</QueryDescriptor>

Let’s conclude by creating a repository-side metric extension, which runs SQL against the OMS repository. With a repository-side Metric Extension, the FETCHLET_ID is REPOSITORY_SQL, and the properties are replaced by a Source property containing the SQL query:

<QueryDescriptor FETCHLET_ID="REPOSITORY_SQL">
<Property NAME="Type" SCOPE="GLOBAL">SQL</Property>
<Property NAME="Source" SCOPE="GLOBAL">SELECT target_guid,
       target_type,
       1 AS host_count
FROM mgmt$target
WHERE target_type = 'host'</Property>
</QueryDescriptor>

In the manifest, is_repos="TRUE" is what differentiates this kind of metric extension. The <Metric> element itself picks up REPOSITORY="TRUE" too, and the columns default to the query SELECT list (TARGET_TYPE, HOST_COUNT here) rather than anything typed by hand on the Columns step.

<MetricExtensionArchive name="MEAME$sql_target_count">
<MetricExtension name="ME$sql_target_count" type="host" version="1" is_repos="TRUE" display_name="SQL Target Count" description="Test ME using the repository-side SQL adapter: counts targets by status." version_comment=" " metadata_digest="0ca6af88e017fb535ae2d59f7f1b6143" coll_digest="e7be6de5a24037de10846bc29391bf2f" attach_digest="d41d8cd98f00b204e9800998ecf8427e">
</MetricExtension>
</MetricExtensionArchive>

With all of this in mind, let’s try to feed the Enterprise Manager a custom metric extension built entirely by hand, using a small Python script as the collector instead of a literal command.

Here is a script reading the 1-minute load average from /proc/loadavg and printing it with the same em_result= prefix expected by OSLineToken.

#!/usr/bin/env python3
with open("/proc/loadavg") as f:
    load1 = f.read().split()[0]
print(f"em_result={load1}")

We have three XML files and the script, written by hand rather than exported from the wizard. metadata/ME#24#python_load_check.xml:

<TargetMetadataExt EXT_NAME="ME$python_load_check" EXT_VERSION="1" TARGET_TYPE="host"><Metric NAME="ME$python_load_check" TYPE="TABLE">
<Display>
<Label NLSID="NLS_METRIC_hostME$python_load_check">Python Load Check</Label>
<Description NLSID="NLS_DESCRIPTION_hostME$python_load_check">Runs an uploaded Python script that reads the 1-minute load average from /proc/loadavg.</Description>
</Display>
<TableDescriptor>
<ColumnDescriptor NAME="value" TYPE="NUMBER">
<Display>
<Label NLSID="NLS_COLUMN_hostME$python_load_checkvalue">Value</Label>
</Display>
</ColumnDescriptor>
</TableDescriptor>
<QueryDescriptor FETCHLET_ID="OSLineToken">
<Property NAME="command" SCOPE="GLOBAL">/usr/bin/python3</Property>
<Property NAME="script" SCOPE="GLOBAL" OPTIONAL="TRUE">%scriptsDir%/load_check.py</Property>
<Property NAME="scriptLoc" SCOPE="GLOBAL" OPTIONAL="TRUE">%scriptsDir%</Property>
<Property NAME="delimiter" SCOPE="GLOBAL" OPTIONAL="TRUE">|</Property>
<Property NAME="startsWith" SCOPE="GLOBAL" OPTIONAL="TRUE">em_result=</Property>
<Property NAME="ENVEM_TARGET_NAME" SCOPE="INSTANCE" OPTIONAL="TRUE">NAME</Property>
<CredentialRef NAME="OSCreds"></CredentialRef>
</QueryDescriptor>
</Metric>
</TargetMetadataExt>

The command property points at the interpreter (/usr/bin/python3 instead of %perlBin%/perl), while script points at the uploaded file. Everything else is untouched. Here is the collection/ME#24#python_load_check.xml file:

<TargetCollectionExt EXT_NAME="ME$python_load_check" EXT_VERSION="1" TARGET_TYPE="host"><CollectionItem NAME="ME$python_load_check" UPLOAD="YES">
	<Schedule>
		<IntervalSchedule INTERVAL="15" TIME_UNIT="Min"/>
	</Schedule>
	<MetricColl NAME="ME$python_load_check">
	</MetricColl>
</CollectionItem>
</TargetCollectionExt>

And the manifest mea.xml:

<MetricExtensionArchive name="MEAME$python_load_check">
<MetricExtension name="ME$python_load_check" type="host" version="1" is_repos="FALSE" display_name="Python Load Check" description="Runs an uploaded Python script that reads the 1-minute load average from /proc/loadavg." version_comment=" " metadata_digest="0000000000000000000000000000000" coll_digest="0000000000000000000000000000000" attach_digest="0000000000000000000000000000000">
<scripts name="load_check.py">
</scripts>
</MetricExtension>
</MetricExtensionArchive>

The digests are just zeroes here. The OMS recomputes and stores new digests when you import the metric.

Now, let’s build the archive with the same layout export_metric_extension produces, assembled by hand instead:

$ find build -type f
build/mea.xml
build/metadata/ME#24#python_load_check.xml
build/collection/ME#24#python_load_check.xml
build/scripts/load_check.py

$ cd build && zip -rq ../python_load_check.zip . && cd ..
$ unzip -l python_load_check.zip
Archive:  python_load_check.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
        0  08-27-2026 14:38   metadata/
     1212  08-27-2026 14:38   metadata/ME#24#python_load_check.xml
        0  08-27-2026 14:38   collection/
      321  08-27-2026 14:38   collection/ME#24#python_load_check.xml
        0  08-27-2026 14:38   scripts/
      116  08-27-2026 14:38   scripts/load_check.py
      529  08-27-2026 14:38   mea.xml
---------                     -------
     2178                     7 files

The last step is to import the metric extension with emcli:

$ emcli import_metric_extension -file_name=python_load_check.zip
Metric extension file python_load_check.zip imported successfully

You now know how to build metric extensions without going through the web UI.

L’article Anatomy of an Enterprise Manager metric extension est apparu en premier sur dbi Blog.

How bytecode analysis exposed an ORB initialization defect during an Oracle Fusion Middleware upgrade!

During a recent Oracle Fusion Middleware upgrade from 12.2.1.4 to 14.1.2, I encountered an unexpected startup failure affecting Oracle Reports Server.

The installation, domain upgrade and reconfiguration had completed successfully. The following components were operational:

• WebLogic AdminServer
• ReportsTools
• WLS_REPORTS
• Node Manager

However, the standalone Reports Server components, RptSvr_1 and RptSvr_2, failed to initialize.

The underlying exception was reported as: REP-50125.

Oracle documents REP-50125 as a generic internal exception. The error code alone does not identify the failing subsystem.

In this case, the root cause was neither a WebLogic configuration problem nor a database connectivity issue.

It was caused by an unexpected interaction between REPORTS_JVM_OPTIONS and the internal Oracle Reports CORBA ORB initialization code.

The Reports Server diagnostic logs exposed the following execution path:

java.lang.NullPointerException
    at java.util.concurrent.ConcurrentHashMap.putVal(
        ConcurrentHashMap.java:1011)
    at java.util.concurrent.ConcurrentHashMap.put(
        ConcurrentHashMap.java:1006)
    at java.util.Properties.put(
        Properties.java:1301)
    at oracle.reports.utility.Utility.getOrbProperties(
        Utility.java:2561)
    at oracle.reports.utility.Utility.createORB(
        Utility.java:2490)
    at oracle.reports.server.RWServer.startServer(
        RWServer.java:912)
    at oracle.reports.server.RWServer.jniMain(
        RWServer.java:289)

The important frame is:

oracle.reports.utility.Utility.getOrbProperties(
    Utility.java:2561)

The failure occurs when Oracle Reports initializes its ORB properties, before the Reports Server becomes operational.

A NullPointerException inside ConcurrentHashMap.putVal() strongly suggests that a null key or value is being inserted into a map that does not accept null entries.

The question was therefore straightforward:
Which ORB property was receiving a null value, and where did that value originate?

To investigate the issue beyond the available log messages, I inspected the compiled Oracle Reports classes using the JDK javap utility.

The objective was to identify the implementation behind:

oracle.reports.utility.Utility.getOrbProperties()

The relevant commands were:

# Locate the Oracle Reports class
find "$ORACLE_HOME" -type f -name "*.jar" \
  -exec sh -c '
    for jarfile do
      jar tf "$jarfile" 2>/dev/null |
        grep -q "^oracle/reports/utility/Utility.class$" &&
        printf "%s\n" "$jarfile"
    done
  ' sh {} +

Once the corresponding JAR is identified:

javap -c -p -l \
  -classpath /path/to/oracle-reports.jar \
  oracle.reports.utility.Utility

To focus on the relevant methods:

javap -c -p -l \
  -classpath /path/to/oracle-reports.jar \
  oracle.reports.utility.Utility |
grep -A 100 -E \
  'getOrbProperties|extractOrbWaitTimeout'

The bytecode inspection revealed that getOrbProperties() reads the environment variable: REPORTS_JVM_OPTIONS

It then calls an internal method: extractOrbWaitTimeout(…)

The extracted value is subsequently assigned to the CORBA property:

com.sun.corba.ee.transport.ORBWaitForResponseTimeout

This provided the missing link between a JVM configuration variable and the ORB initialization exception.

The investigation exposed two different execution paths.

When the environment variable is absent, Oracle Reports assigns a hardcoded fallback value: “999999999”

This behavior prevents a null timeout value from being inserted into the ORB properties.

Consider this configuration:

REPORTS_JVM_OPTIONS="-Xmx1024M"
export REPORTS_JVM_OPTIONS

The variable exists, so the fallback branch is not used.

Oracle Reports invokes extractOrbWaitTimeout() to search for: -DORBWaitForResponseTimeout=

However, that parameter is missing.

The method returns null.

The resulting behavior is equivalent to:

Properties properties = new Properties();

String timeout = null;

properties.put(
    "com.sun.corba.ee.transport.ORBWaitForResponseTimeout",
    timeout
);

This triggers the NullPointerException observed during ORB initialization.

The significant finding is that a variable containing valid JVM options can introduce an ORB initialization failure simply because one additional property is missing. It is an internal Oracle Reports configuration-parsing behavior.

These conclusions are based on the inspected Oracle Reports 14.1.2 implementation and should not be generalized to every Oracle Reports release or patch level without verification.

The correction was applied to the Oracle Reports environment configuration:

$ORACLE_HOME/reports/bin/reports.sh

The JVM options were updated as follows:

REPORTS_JVM_OPTIONS="-Xmx1024M -DORBWaitForResponseTimeout=999999999"
export REPORTS_JVM_OPTIONS

The timeout value was not chosen arbitrarily. It corresponds to the hardcoded fallback identified in Oracle’s bytecode.

The property value is expressed in milliseconds: 999999999 ms ≈ 11.57 days

This is a technical reproduction of the implementation’s fallback, not a general recommendation to configure such a long timeout for every environment.

Before restarting Reports Server, the configuration can be verified using:

grep -n "REPORTS_JVM_OPTIONS" \
  "$ORACLE_HOME/reports/bin/reports.sh"

Expected configuration:

REPORTS_JVM_OPTIONS="-Xmx1024M -DORBWaitForResponseTimeout=999999999"
export REPORTS_JVM_OPTIONS

The Reports Server was then restarted through Node Manager.

Result: RptSvr_1 started successfully after the correction.

This confirmed that the configuration change resolved the observed ORB initialization failure on that instance. The separate application-level report-generation tests and second-instance validation remain distinct checks.

After the restart, the effective environment should be checked against the actual Reports Server process.

First, identify the process:

ps -ef | grep -E 'rwserver|RWServer' | grep -v grep

Then inspect the inherited environment:

tr '\0' '\n' < /proc/<PID>/environ |
grep -E '^(REPORTS_JVM_OPTIONS|ORACLE_HOME|JAVA_HOME)='

Expected value:

REPORTS_JVM_OPTIONS=-Xmx1024M -DORBWaitForResponseTimeout=999999999

his check is important because the Node Manager startup environment may differ from an interactive shell.

A successful restart should also be followed by:

• Confirmation that the Reports Server remains running.
• Verification that the original exception no longer appears in new diagnostic entries.
• Validation of all Reports Server instances.
• Execution of representative report-generation requests.

This issue illustrates why troubleshooting Oracle Fusion Middleware upgrades sometimes requires going beyond configuration files and diagnostic logs.

The visible symptom was a generic REP-50125. The underlying failure was a NullPointerException in Utility.getOrbProperties(), caused by the interaction between REPORTS_JVM_OPTIONS and Oracle Reports’ internal ORB timeout extraction.

Oracle documentation describes the precedence of jvmOptions in rwserver.conf over the environment variable for Reports Engine startup. However, the implementation detail identified during this investigation is different. The getOrbProperties() method also consumes REPORTS_JVM_OPTIONS while constructing ORB configuration properties.

Bytecode inspection established the missing connection and identified the hardcoded fallback used by Oracle.

Adding the corresponding JVM property restored successful startup of the affected Reports Server instance.

Related blogs.

L’article Oracle Reports 14.1.2 REP-50125 – A Hidden REPORTS_JVM_OPTIONS Requirement est apparu en premier sur dbi Blog.

I often see this pattern in organizations. The ECM project was an overall success: the migration is complete, and users are happy with the new solution. But after a year or two, the magic faded. Search results are cluttered with duplicates, metadata is inconsistent, permissions have drifted, and nobody dares to delete anything. Some users have gone back to using shared drives.

The platform itself is not the cause; it simply does what it was configured to do. The real problem is that nobody was ever clearly responsible for the information. The project had a sponsor, a manager and a budget. The information had none of these.

I touched on this topic briefly in previous posts (Why ECM Projects fail or Information debt), but let’s take a closer look.

Because it is organizational and political rather than technical, it feels more challenging than a workshop on vault structure. It also seems to be something for ‘phase two’, which rarely receives the same level of attention. Business users assume IT handles it, and IT assumes the business does. Furthermore, ‘ownership’ can mean different things to different people, such as who created the document, who is accountable for it, who can edit it and who decides when it should be deleted.

I think we should start by defining the four crucial roles.

• Data owner is a business role that is accountable for a category of information. For example, the HR director is responsible for personnel files. The owner decides on the rules for classification, retention and access.
• Data steward ensures that these rules are followed on a daily basis. They monitor metadata quality, handle exceptions and propose improvements. This is the role that is most often missing.
• Custodian is usually IT. They keep the platform secure, backed up and running, but they should not decide what the information means or how long it is kept.
• Contributors are users who create and edit content. Being the author of a document does not make you the owner of its information.

In short: owners decide, stewards organize, custodians protect, users contribute.

When one of these is missing, the others compensate badly. IT ends up making business decisions, or users end up inventing their own conventions.

M-Files organizes information based on its metadata, not its storage location. The metadata model is a shared contract, and every contract needs someone to take responsibility for it.

• Who approves new classes or values in a value list? Without a clear answer, terms such as ‘Contract’, ‘Contracts’ and ‘Agreement’ end up coexisting.
• Who decides which properties are mandatory?
• Who in the business owns each workflow, since only they know when the process changes?
• Who reviews permissions as people and organizations change?
• Who validates retention and destruction rules?

AI adds urgency. Whether you use M-Files Aino or another AI capability, the quality of the answers depends on the content: accurate metadata, no duplicates and no obsolete documents being passed off as current. AI does not fix a governance gap, it exposes it.

I recommend the following six best practices. You should use them in any ECM solution.

• Ask early and write it down. For each information domain, name the owner, the steward(s), the custodian and the applicable rules. A simple table validated by management is enough.
• Name roles, not just people, with a deputy, so the organization survives a departure or reorganization.
• Give stewards time and tools. Use M-Files views that surface missing metadata, notifications and dashboards to make the work lighter.
• Define a light change process for the metadata model, such as a monthly review between steward and administrator.
• Schedule reviews: quarterly on data quality, twice a year on permissions, yearly on retention and the model itself.
• Hand over governance at go-live, not just a configured vault: the roles, the decisions and the reasons behind them.

If you want to evaluate where you are with your solution, try to answer the following questions:

• Can you name the person accountable for each type of document?
• Who can approve a new document class?
• Who decides when a document can be deleted?
• When did the business last review access to sensitive information?
• If your ECM administrator left tomorrow, would the rules still be understood?

Expert level: Who are the deputies?

If you answered ‘I don’t know’ or ‘I’m uncertain’ to at least one question, then you have identified a risk before it becomes a failed project.

Successful ECM projects are rarely lost on technology. They are lost slowly, in the months after go-live, through a lack of clear responsibility. The platform keeps working while the information quietly loses its value.

The fix is not complicated, but it does require a decision: information needs owners, just like budgets and projects do. Asking ‘Who owns the information?’ early, and writing down the answer, is one of the cheapest and most effective investments you can make in an ECM.

If you are planning a project, or wondering why a past one is not delivering what it promised, this is a good question to start with. And if you would like to discuss how to set up ownership and stewardship in your own M-Files environment, feel free to reach out.

L’article Who owns the information? Data ownership and stewardship in an ECM est apparu en premier sur dbi Blog.

With SQL Server 2025, Microsoft lets the database engine call an AI model by itself. sp_invoke_external_rest_endpoint sends an HTTPS request straight from T-SQL, to Azure OpenAI or to any other REST service, so an embedding or a completion no longer needs application code in between.

But what happens under heavy pressure, when hundreds of sessions make that call at the same moment and the model takes its time to answer? I ran this scenario on SQL Server 2025 and on Azure SQL Database, with the same procedure and the same load script. The two platforms do not react the same way at all.

The documentation of the procedure describes a cap at 10% of the worker threads. That is not what I measured. Azure SQL Database does cap the calls in my tests, but at 15% of its workers: 22 calls at 2 vCores, 45 at 4 vCores, 30 on a Standard S3. SQL Server 2025 has no cap at all, and the rest of this post shows what happens without it.

The on-premises setup has three parts, all on one virtual machine (Windows Server 2022, 2 vCPUs): a load generator, a SQL Server 2025 Standard instance, and a small HTTPS endpoint that answers after the delay given in the URL.

The call is wrapped in a procedure, so that every session runs exactly the same thing:

CREATE OR ALTER PROCEDURE dbo.CallSlowAi @delay int, @timeout smallint = 230
AS
BEGIN
    DECLARE @url nvarchar(400) = CONCAT(N'https://<YOUR_VM>:8443/?delay=', @delay);
    DECLARE @ret int, @resp nvarchar(max);
    EXEC @ret = sp_invoke_external_rest_endpoint @url = @url, @method = 'POST',
         @payload = N'{"input":"hello"}', @timeout = @timeout, @response = @resp OUTPUT;
    RETURN @ret;
END

The load generator is a PowerShell function that opens N connections without pooling, sends EXEC dbo.CallSlowAi @delay = 30 on all of them at the same moment, and records for each session the elapsed time and the error number. One process, so the client itself does not slow the VM down.

Before measuring SQL Server, I called the endpoint directly with 200 parallel requests: they all came back in 30.6 seconds. The endpoint is not the bottleneck.

A worker thread is what SQL Server uses to execute a request: one request running, at least one worker busy. The instance has a fixed pool of them, set by max worker threads; with the default setting on this 2-vCPU machine, that is 512. When they are all taken, new work waits with the THREADPOOL wait type, and that includes logging in, because a login needs a worker too (see my previous blog about this wait type).

Three words matter here, and their numbers should not be added together. A session is a connection. When it runs a request, the request is split into one or more tasks, and each task needs a worker thread to run. A simple call like ours is one task, so one worker; a parallel query would take several. The worker counts I quote also include SQL Server’s own background tasks: about 45 workers on this idle instance. A wait type, on the other hand, is counted per task, whoever owns it.

A session waiting for the endpoint reports the HTTP_EXTERNAL_CONNECTION wait type and keeps its worker thread for the whole wait: every waiting call added one worker to that baseline.

With 300 sessions, SQL Server sent everything at once. I counted 300 TCP connections from sqlservr.exe to port 8443, and all calls returned together:

ErrorNumber : 0
Sessions    : 300
MinSec      : 30.2
MaxSec      : 34.5

With 600 sessions, the instance has only 512 worker threads to give. 510 connections were open at the same time, and the calls finished in two groups:

Finished after (s)   Sessions
30                   193
40                   316
50                     1
60                    90

The first 509 calls left immediately, one per available worker. The last 91 did not wait for the endpoint: they waited for a worker. No error was raised at any point, and nothing held the calls back.

I measured the saturation itself during an earlier run of the same 600 sessions, with HTTP/2 still enabled on the endpoint (see below). From the dedicated admin connection:

active_workers  queued_tasks
556             154

-------------------------------------

wait_type                  n
HTTP_EXTERNAL_CONNECTION   513
THREADPOOL                 154

At that moment an ordinary sqlcmd connection failed:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in prelogin response.

The admin connection kept answering, and the instance recovered by itself once the calls completed. KILL also works at once on a waiting session (error 596 on the client side).

HTTP.sys, which hosts my test endpoint, offers HTTP/2 by default. In that mode SQL Server opened 6 TCP connections instead of one per call, and only 100 calls were in flight per host name. The same 300 sessions then finished in three waves:

Finished after (s)   Sessions
30                   100
60                   100
90                   100

The queued sessions show the same HTTP_EXTERNAL_CONNECTION wait and hold a worker while they queue, so the pool empties just as well: the 556 workers above were measured in this mode. The queue has a second effect. @timeout keeps running while a call waits its turn. With a 30-second endpoint and @timeout = 45, the first 100 calls succeeded and the 100 queued ones failed after 45.5 seconds with error 31629, The external rest endpoint execution exceeded the timeout value, although the endpoint was answering normally.

Turning HTTP/2 off on the endpoint removes that queue. I disabled it in HTTP.sys (EnableHttp2Tls and EnableHttp2Cleartext set to 0, then a reboot) and replayed the same load:

The waves are gone and so are the timeouts caused by the queue. It is not a fix for the instance, though. Each call still holds a worker, the 600-session run at the top of this section was made in HTTP/1.1, and the endpoint now receives every call at once. With a real AI provider you also do not choose the protocol: the server does.

I did not capture the HTTP/2 negotiation, so I cannot say which side sets the figure of 100. What the two modes show together is that SQL Server applies no cap of its own.

The page of the procedure is marked as applying to SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric. Its throttling section reads:

The number of concurrent connections to external endpoints done via sp_invoke_external_rest_endpoint are capped to 10% of worker threads, with a maximum of 150 workers.

It adds that a call made beyond the limit raises error 10928. With 512 worker threads, I expected about 51 calls to pass and the others to fail. I got neither the cap nor the error, on RTM as on CU9.

The next sentence of that section gives a hint: it speaks of a single database and of an elastic pool, two notions that only exist in Azure SQL Database. So I ran the same test there.

Azure SQL Database only calls endpoints from a closed list of Azure services, so the slow endpoint becomes an Azure Function. The database is a single Azure SQL Database in Switzerland North. I started on the vCore model, General Purpose serverless on standard-series (Gen5) hardware with 2 vCores maximum, then scaled the same database to 4 vCores, and finally to Standard S3 in the DTU model. The function runs on the Consumption plan, Node.js 22, in the same region. The procedure is the same, with the URL of the function, and the load script only changes its connection string.

Before any load, the governance view of the database already shows a dedicated limit:

SELECT primary_group_max_workers, primary_group_max_outbound_connection_workers
FROM sys.dm_user_db_resource_governance;

primary_group_max_workers  primary_group_max_outbound_connection_workers
150                        22

I stated my bet before running the load: 22 successes, 78 errors. Result for 100 sessions calling a 30-second endpoint:

ErrorNumber  Sessions  Finished after (s)
0            22        30
10928        78        0

Resource ID : 4. The outbound connection worker limit for the database is 22 and has been reached.

The 78 rejected sessions got their answer immediately, which leaves the database 128 of its 150 workers for everything else, whatever the endpoint does.

Two remarks on this test. The ratio is 15%, not the documented 10%. At 2 vCores the database has 150 workers and a limit of 22. I scaled it to 4 vCores: 300 workers, a limit of 45, and the same load gave 45 successes and 55 errors. In the DTU model, a Standard S3 shows 200 workers and a limit of 30. This is one database, in one region, read on three service objectives and load-tested on two. The ratio could differ on other tiers, hardware generations or Hyperscale, and Microsoft may change it: treat 15% as what I observed, not as a rule. On the endpoint side I needed to do a small adjustment as well: an Azure Function on the Consumption plan served only 100 concurrent requests per instance until I raised maxConcurrentRequests in host.json. A direct test of 200 parallel requests took 62.8 seconds before the change and 30.4 after.

SQL Server 2025 exposes nothing comparable. On CU9, sys.dm_user_db_resource_governance does not exist, no system column deals with outbound connections, and the only related configuration option is the on/off switch external rest endpoint enabled.

What SQL Server does have is Resource Governor, and a workload group can limit its number of concurrent requests. I created a group with the Azure value and sent the sessions of the load generator into it through their application name:

USE master;
GO
CREATE WORKLOAD GROUP AiCalls WITH (GROUP_MAX_REQUESTS = 22) USING [default];
GO
CREATE FUNCTION dbo.RgClassifier() RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN APP_NAME() = N'AiLoad' THEN N'AiCalls' ELSE N'default' END;
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RgClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Same load as on Azure, 100 sessions calling a 30-second endpoint:

ErrorNumber  Sessions
0            100

Finished after (s)   Sessions
30                   22
60                   22
90                   22
120                  22
150                  12

The limit is applied to the unit: never more than 22 calls reach the endpoint. But there is no error. Every session ends up being served, and the last ones wait two minutes for it.

During the second wave of that test, the group, the waits and the workers looked like this:

The 56 queued sessions wait on RESMGR_THROTTLED, and each of them has a worker. The instance counts 123 active workers against 45 at rest: 78 more, exactly the 22 calls in flight plus the 56 sessions in the queue.

Resource Governor protects the service being called. It does not protect the instance. With 600 sessions classified in that group, 22 calls would be in flight and the 578 others would still be queued, each holding a worker, against a pool of 512. I did not run that last case; it follows from the two measurements above.

On SQL Server 2025, every session waiting for an external endpoint holds a worker thread, and the engine sets no limit on how many can wait at once. Azure SQL Database does have such a cap, at 15% of its workers in my tests rather than the documented 10%, and a call beyond it is refused immediately. On-premises, a slow AI service plus a concurrent application is enough to exhaust the worker pool and to keep new connections out, with no blocking chain to look for and an error message, when there is one, that points at the endpoint.

The difference is also one of scope. In Azure SQL Database, the limit belongs to the database: a flood of slow calls is refused at the door of that database, and the 128 workers left keep it running. In SQL Server, the worker pool belongs to the instance. Every database on it shares the same 512 workers, so one application calling a slow AI service can lock out the users of all the others.

Resource Governor can cap the number of calls sent to the service, in Standard Edition too, but it queues the surplus instead of refusing it, and a queued session costs a worker. If you want the Azure behavior, the refusal has to come from the code that makes the call, or from the application in front of it. Make sure you can reach the dedicated admin connection before you need it: it works locally out of the box (sqlcmd -A), but from another machine it requires the remote admin connections option. It was the only way in during the saturation, and KILL on the waiting sessions frees their workers at once.

L’article Calling AI from SQL Server 2025 can lock everyone out of your on-prem instance est apparu en premier sur dbi Blog.

In a recent blog, I explained how instantiation filtering works. DBOPTIONS ENABLE_INSTANTIATION_FILTERING makes a replicat discard any record whose commit CSN is at or below a table’s instantiation CSN. It applies everything above it. This replicat parameter should be kept during the whole initial load process.

At some point the replicat has processed past the instantiation boundary for every table, and the filter has nothing left to do. Let’s answer the subsequent and very practical question: how to confirm, before editing the parameter file, that the replicat has cleared the boundary for every table it maps?

To summarize what needs to be checked: it is safe to remove DBOPTIONS ENABLE_INSTANTIATION_FILTERING once the replicat last applied CSN is greater than the instantiation CSN of every table it maps.

To answer this question, you have two sources of information:

• The per-table boundary, stored in dba_apply_instantiated_objects, with one row per source object.
• The replicat last applied CSN, stored in the checkpoint table in the log_csn column.

Read the boundaries first:

SELECT source_database, source_object_owner, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
ORDER BY instantiation_scn DESC;

SOURCE_DATABASE  SOURCE_OBJECT_OWNER  SOURCE_OBJECT_NAME  INSTANTIATION_SCN
---------------- -------------------- ------------------- -----------------
PDB1             APP_PDB1             SALES               52642820
PDB1             APP_PDB1             ORDERS              52642649

Then read the replicat last applied CSN from its checkpoint table:

SELECT group_name, log_csn
FROM oggadmin.checkpoint
WHERE group_name = 'REP1';

You can safely remove ENABLE_INSTANTIATION_FILTERING when log_csn is greater than the highest instantiation_scn among the tables it maps. A one-shot verdict, scoped to a single source database:

SELECT 'REP1' AS group_name,
c.log_csn,
MAX(o.instantiation_scn) AS max_boundary,
CASE WHEN c.log_csn > MAX(o.instantiation_scn) THEN 'SAFE TO REMOVE' ELSE 'NOT YET' END AS verdict
FROM dba_apply_instantiated_objects o
LEFT JOIN oggadmin.checkpoint c ON c.group_name = 'REP1'
WHERE o.source_database = 'PDB1'
GROUP BY c.log_csn;

Before a replicat starts for the first time, log_csn is NULL and the verdict is NOT YET:

GROUP_NAME  LOG_CSN  MAX_BOUNDARY  VERDICT
----------- -------- ------------- -------
REP1                 52642820      NOT YET

A replicat may map more than one source database, or only a subset of the instantiated objects. Then narrow the join to the objects applied.

WARNING: A replicat that has never started has no checkpoint row at all, so log_csn returns nothing. Treat that as “not safe”: there is no applied position, so it cannot have cleared anything. The verdict query above handles this case. It starts from the instantiated objects and left joins the checkpoint table. A missing row gives a NULL log_csn, and the verdict falls to NOT YET instead of the query returning no row at all.

After some time, once the replicat catches up with the initial load, the same query returns:

GROUP_NAME  LOG_CSN   MAX_BOUNDARY  VERDICT
----------- --------- ------------- --------------
REP1        52643651  52642820      SAFE TO REMOVE

You can now cleanly delete the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter and restart your replicat.

From the adminclient, INFO REPLICAT REP1, DETAIL reports the applied CSN without using sqlplus:

OGG> INFO REPLICAT REP1, DETAIL
...
Last Committed Transaction CSN value: 52643651

Compare that number against the instantiation_scn from the view exactly as above.

The CSN is reachable over the REST API, just not from a GET endpoint. You need to execute a command against the replicat using the POST /services/{version}/replicats/{replicat}/command endpoint. Using execute_command_replicat in the oggrestapi Python client:

from oggrestapi import OGGRestAPI

client = OGGRestAPI(url="http://localhost:7810", username="ogg", password="ogg")

resp = client.execute_command_replicat("REP1", data={"command": "INFO", "arguments": "DETAIL"})
output = "".join(r["reply"])
print(output)

Replicat   REP1      Last Started 2026-09-20 13:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           102660
Log Read Checkpoint  File aa000000002
                     2026-09-20 13:30:17.000000  RBA 18926
Encryption Profile   LocalWallet

Current Log BSN value: 52643543

Last Committed Transaction CSN value: 52646310

  Extract Source                          Begin             End

  aa000000002                             2020-01-01 00:00  2026-09-20 13:30:17.00
  aa000000000                             * Initialized *   2020-01-01 00:00:00.00
  aa000000000                             * Initialized *   First Record

To be truly automated, Last Committed Transaction CSN value has to be parsed out:

import re

match = re.search(r"Last Committed Transaction CSN value: (\d+)", output)
applied_csn = int(match.group(1))
print(applied_csn)

52646310

When starting a replicat during an initial load, the report file is filled with the following lines:

OGG-10155  Instantiation CSN filtering is enabled on table PDB1.APP_PDB1.ORDERS at CSN 52,642,649.
OGG-10185  Instantiation CSN filtering on table PDB1.APP_PDB1.ORDERS has started at CSN 52642649.
OGG-10186  Instantiation CSN filtering on table PDB1.APP_PDB1.ORDERS has ended, now passing transactions starting at CSN 52642649.

• If the trail the replicat reads on that start contains records below a boundary, it filters them, and these lines appear.
• If the trail begins at or after the boundary, nothing is filtered and no line appears at all.

When migrating a database online with GoldenGate, you want your replicat to catch up quickly. I tested whether keeping ENABLE_INSTANTIATION_FILTERING in the parameter file had any effect on the lag of a replicat. I found no significant statistical evidence of this.

• ENABLE_INSTANTIATION_FILTERING is safe to remove when the replicat log_csn (last applied CSN) is above the instantiation_scn of every table it maps.
• Get the applied CSN from oggadmin.checkpoint.log_csn (sqlplus), INFO REPLICAT REP1, DETAIL (adminclient), or execute_command_replicat("REP1", data={"command": "INFO", "arguments": "DETAIL"}) over REST, which returns the same information.
• The OGG-10155/10185/10186 report lines appear only when the replicat filters records on that start.

L’article Removing instantiation filtering from a GoldenGate replicat est apparu en premier sur dbi Blog.

In my blog about the new automatic schema evolution feature in GoldenGate 26ai, I was replicating the following table from Oracle to PostgreSQL:

CREATE TABLE autosch.t1 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    amount NUMBER(10,2),
    created DATE DEFAULT SYSDATE,
    note VARCHAR2(20) DEFAULT 'added'
);

The replicat created a PostgreSQL table from an Oracle one during an initial load. It worked, but one choice was not mine: the NUMBER primary key became a varchar(50). Automatic Schema Evolution takes its datatypes from a default mapping, and the TYPEMAP option is how you override it.

Automatic schema evolution is a preview feature in GoldenGate 26ai: its syntax, messages and behavior can change before it becomes generally available, so the results below apply to the 23.26.3 release I tested.

Each GoldenGate 26ai home ships the default mappings as one JSON file per source database and per target database. They are in $OGG_HOME/lib/utl/autoschema/MappingJSON, where MappingGenerator.py combines a source and a target file. For this example, the files are AutoSchemaOracleSourceMappings.json and AutoSchemaPostgreSQLTargetMappings.json.

Reading the two JSON files for an Oracle source and a PostgreSQL target, the default mappings that matter for the example table are:

A NUMBER without precision maps to varchar, and a NUMBER(p,s) maps to decimal(p,s). This is why the key of my source table did not become a number.

For each test below, I drop autosch.t1 on PostgreSQL and start a new replicat, always named REPTM, on the same file.

AUTOSCHEMAOPTIONS TYPEMAP must be written on a separate line, next to DDL AUTOSCHEMA INCLUDE ALL. The source type and the target type are separated by an equal sign. The option maps every occurrence of the source type to the given target type. Both types must be enclosed in single quotes, otherwise the replicat abends with OGG-30694:

REPLICAT REPTM
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDL AUTOSCHEMA INCLUDE ALL
AUTOSCHEMAOPTIONS TYPEMAP 'number' = 'bigint'
MAP AUTOSCH.*, TARGET AUTOSCH.*;

The replicat report logs that the override was read:

2026-09-19T15:23:47.078+0000  INFO    OGG-30632  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  AutoSchemaOptions TypeMap is used, TypeNameMap: number -> bigint.

The table created in PostgreSQL, seen with \d autosch.t1 in psql, now shows a bigint as primary key

ogg_test_pg=# \d autosch.t1
                            Table "autosch.t1"
 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | bigint                         |           | not null |
 name    | character varying(50)          |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
 note    | character varying(20)          |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

The amount column type did not change, since 'number' matches the source type NUMBER without precision. NUMBER(10,2) is another source type, and is not affected by the TYPEMAP instruction.

When mapping multiple types, each mapping needs a dedicated AUTOSCHEMAOPTIONS line:

DDL AUTOSCHEMA INCLUDE ALL
AUTOSCHEMAOPTIONS TYPEMAP 'number' = 'bigint'
AUTOSCHEMAOPTIONS TYPEMAP 'date' = 'date'
AUTOSCHEMAOPTIONS TYPEMAP 'number(10,2)' = 'real'

Every entry is logged separately:

2026-09-19T16:29:07.403+0000  INFO    OGG-30632  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  AutoSchemaOptions TypeMap is used, TypeNameMap: date -> date.
2026-09-19T16:29:07.403+0000  INFO    OGG-30632  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  AutoSchemaOptions TypeMap is used, TypeNameMap: number -> bigint.
2026-09-19T16:29:07.403+0000  INFO    OGG-30632  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  AutoSchemaOptions TypeMap is used, TypeNameMap: number\(10,2\) -> real.

And this is the resulting table:

 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 id      | bigint                |           | not null |
 name    | character varying(50) |           |          |
 amount  | real                  |           |          |
 created | date                  |           |          |
 note    | character varying(20) |           |          |

All three overrides are applied. An interesting feature is that the replicat logs an OGG-03056 warning about the target column being smaller than the source column:

2026-09-19T16:29:07.419+0000  WARNING OGG-03056  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  Source table AUTOSCH.T1 column CREATED data size exceeds the maximum target table autosch.t1 column created size. Automatic truncation is enabled for all tables/columns without further warnings.

As expected, mapping DATE to a PostgreSQL date loses the time part, and it can be seen in the target table:

 id | name  | amount |  created   | note
----+-------+--------+------------+-------
  1 | row 1 |  10.25 | 2026-09-19 | added
  2 | row 2 |   20.5 | 2026-09-19 | added

An Oracle DATE carries a time, so the default timestamp(0) mapping is the right one.

The same option can be set on a MAP statement, between parentheses. It then only applies to the tables of that MAP statement instead of the whole replicat:

MAP AUTOSCH.*, TARGET AUTOSCH.*, AUTOSCHEMAOPTIONS (TYPEMAP 'number' = 'bigint');

2026-09-19T16:29:33.344+0000  INFO    OGG-06506  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  Wildcard MAP resolved (entry AUTOSCH.*): MAP "AUTOSCH"."T1", TARGET AUTOSCH."T1", AUTOSCHEMAOPTIONS (TYPEMAP 'number' = 'bigint').
2026-09-19T16:29:33.344+0000  INFO    OGG-30632  Oracle GoldenGate Delivery for PostgreSQL, REPTM.prm:  AutoSchemaOptions TypeMap is used, TypeNameMap: number -> bigint.

The table has a bigint key again. In this case, the other number columns outside of the MAP statement would keep the default varchar(50) mapping.

To change a single column, write the column name on the left side of the equal sign, instead of specifying the source type. The two forms work, first with the full name of the column:

AUTOSCHEMAOPTIONS TYPEMAP AUTOSCH.T1.NAME = 'text'

And at the MAP level, with the column name alone:

MAP AUTOSCH.T1, TARGET AUTOSCH.T1, AUTOSCHEMAOPTIONS (TYPEMAP NAME = 'text');

Both give the same table, with only name changed into text:

 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | text                           |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
 note    | character varying(20)          |           |          |

To remap several columns on the same MAP, separate the mappings with commas inside the parentheses, and write TYPEMAP only once:

MAP AUTOSCH.T1, TARGET AUTOSCH.T1, AUTOSCHEMAOPTIONS (TYPEMAP NAME = 'text', NOTE = 'text');

 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | text                           |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
 note    | text                           |           |          |

A type and a column can be mixed in the same list. For example, AUTOSCHEMAOPTIONS (TYPEMAP 'number' = 'bigint', NAME = 'text') gives a bigint key and a text name.

• The default mapping files are located in $OGG_HOME/lib/utl/autoschema/MappingJSON. Check them before letting a replicat create tables. A NUMBER without precision becomes a varchar on PostgreSQL, for instance.
• The override syntax is AUTOSCHEMAOPTIONS TYPEMAP 'source_type' = 'target_type'. Use an equal sign, both types in single quotes, and one mapping per line.
• A source type is matched as written. An override on 'number' does not change a NUMBER(10,2) column, which needs a separate 'number(10,2)' entry.
• On a MAP, put the option between parentheses to limit it to those tables. For one column, use AUTOSCH.T1.NAME = 'text' globally or NAME = 'text' on the MAP.
• Pay attention to OGG-03056 warnings: a target type smaller than the source, like DATE to date, truncates the data without stopping the initial load.

L’article GoldenGate 26ai AutoSchema TYPEMAP: overriding the default datatype mapping est apparu en premier sur dbi Blog.

At the end of part 1 the data was across, at 42,170 rows per second into a database that could not answer a single question. Every nonclustered index disabled, the largest tables loaded as heaps with no primary key, every foreign key switched off, and 4,784 views that mostly did not compile.

This part is about putting it back: translating the views, rebuilding the keys and indexes under a Standard Edition licence that makes every build offline, and compressing 3.2 TB of heaps that turned out to be mostly empty space.

The logbook resumes at day 5.

4,658 of the 4,784 views live in a single schema, and nobody needed all of them. We went through the list with the client first and kept the ones that actually matter to the business, which brought the scope down to about 710. SSMA converted around 600 of those on its own.

SSMA fails mostly on CONNECT BY, START WITH and SYS_CONNECT_BY_PATH. For some easy cases, DECODE to CASE and NVL handles it well, for the record. For more complex use case, we needed to find an automatic solution process. So first, we discussed with the client to know which view were critical and needed to be migrated. Then, with this list, we leveraged the power of AI to help us converting the remaining views based on the following workflow:

SSMA converts what it can. The failures get extracted from dba_views into files. Then, and this is the step that changes the economics, they get classified by pattern in the Oracle source rather than one at a time by error message.

Translation itself was done in batches of one family at a time, outside production, on extracted files, with an AI assistant and a context file describing the type mapping decisions, the column types of the referenced tables and the target version. Then human review, tests against Oracle and finally, deployment.

The catalogue of families is the real deliverable. Eight of them closed the great majority of the cases:

And a word on the AI, since that is the part people ask about. No data ever left the environment: only the view definitions were processed, and every test against the live database was run by hand so we kept control of the process. We could have added MCP connectors and let a local model run the tests itself, but with the number of days allocated to the project and the small number of views left to fix, doing it manually was simpler. Around 85 percent of the views wanted by the client had already come through SSMA.

The primary keys I dropped to load into heaps had left no trace on the SQL Server side. Nothing in sys.indexes, no disabled definition, nothing.

Index builds are offline and single-threaded. The table is locked for the duration, metadata reads queue behind it, but we managed to run 4 builds in parallel. SORT_IN_TEMPDB = OFF for the clustered builds. The sort covers the entire table, and 36 GB of tempdb was never going to hold a 900 GB sort.

Meanwhile percent_complete sits at 0 for the whole build, with the serene confidence of something that has never been asked to account for itself. It works for BACKUP DATABASE. It does not work here. Real progress lives in sys.dm_exec_query_profiles, operator by operator :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT physical_operator_name, row_count, estimate_row_count,
       CAST(row_count*100.0/NULLIF(estimate_row_count,0) AS decimal(5,1)) AS pct
FROM sys.dm_exec_query_profiles WITH (NOLOCK)
WHERE session_id = <spid of the build> 
ORDER BY node_id;

Those are not three parallel counters. They are three sequential phases of one pipeline, and only one of them moves at a time. The scan reads the source table, and while it runs its percentage is the only honest progress figure available. The sort is a blocking operator: it cannot emit a single row until it has consumed all 31.9 million, so it stays at zero through the whole scan and then through the whole sort. That is the blind spot. Once the scan reaches 100 percent you get a long stretch where all three counters report nothing while the server works flat out, and if the sort spills to disk that stretch can outlast the other two phases combined. Index Insert starts moving only when the sort begins handing rows over, and it is the first number that means the end is actually in sight.

On a read-only database, PAGE compression has no recurring write cost. You keep the read benefit and pay nothing back. The argument is rarely this clean.

Compression trades CPU for I/O, and on an archive that trade is one-sided. These pages are read rarely, and when they are, nothing heavy is competing for the processor, so the decompression cost lands on a machine with nothing better to do. What you get back is density: more rows per page, which means fewer reads to find the one row somebody asked for and more of the table sitting in memory at once.

The data was unusually well suited to it, for a reason worth spelling out. These tables carry roughly 200 numeric columns, mostly null, plus five nvarchar(200) aggregation labels that repeat endlessly. Here is the part that surprises people: a fixed-length numeric column that is NULL still occupies its full width in SQL Server. “Mostly null” saves nothing before compression. It is precisely why the heap was that large, and precisely what the page dictionary destroys.

The allocated space per row tells the story:

Three tables hold 2.7 TB of the 3.2 TB total. At around 4 KB of allocated space per row against an 8 KB page, the top two fit around two rows per page and waste most of what is left.

Then the first build, on 505,819,544 rows with a five-column primary key:

ALTER TABLE <TABLE6>
ADD CONSTRAINT PK_TABLE6 PRIMARY KEY CLUSTERED
    (<COLUMNS_NAME>)
WITH (DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = OFF);

117 GB to 30.7 GB. A ratio of 3.81.

Finally, as you can see above, more than 2.5 T have been reclaimed (datafiles were full with the heaps). Compression has made huge benefits and that means less data on disks, which means fewer pages to take in the buffer (less IO).

Across every schema that is two cursors, one rebuilding the disabled indexes with compression and one re-enabling the foreign keys. It is driven by the same #Schemas table as the disable script in part 1:

SET NOCOUNT ON;
DECLARE @Execute bit = 0;   -- 0 = preview ; 1 = execute
  
DECLARE @cmd nvarchar(max);
DECLARE @n   int = 0;
  
DECLARE reb_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT N'ALTER INDEX ' + QUOTENAME(i.name)
         + N' ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
         + N' REBUILD WITH (DATA_COMPRESSION = PAGE);'
    FROM   sys.indexes  i
    JOIN   sys.tables   t ON i.object_id = t.object_id
    JOIN   sys.schemas  s ON t.schema_id = s.schema_id
    WHERE  s.name IN (SELECT schema_name FROM #Schemas)
      AND  i.is_disabled = 1          
    ORDER BY s.name, t.name, i.name;
  
OPEN reb_cur;
FETCH NEXT FROM reb_cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @n += 1;
    PRINT @cmd;
    IF @Execute = 1
    BEGIN
        BEGIN TRY EXEC sys.sp_executesql @cmd; END TRY
        BEGIN CATCH PRINT N'   -- FAILED: ' + ERROR_MESSAGE(); END CATCH
    END
    FETCH NEXT FROM reb_cur INTO @cmd;
END
CLOSE reb_cur;
DEALLOCATE reb_cur;
  
PRINT N'---';
PRINT CONVERT(nvarchar(10), @n) + N' index '
    + CASE WHEN @Execute = 1 THEN N'rebuilt.' ELSE N'to be rebuilt (preview).' END;
GO
  
  
SET NOCOUNT ON;
DECLARE @Execute bit = 1;   -- 0 = preview ; 1 = executer
  
DECLARE @cmd nvarchar(max);
DECLARE @n   int = 0;
  
DECLARE fkc_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
         + N' WITH NOCHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';'
    FROM   sys.foreign_keys fk
    JOIN   sys.tables  t ON fk.parent_object_id = t.object_id
    JOIN   sys.schemas s ON t.schema_id = s.schema_id
    WHERE  s.name IN (SELECT schema_name FROM #Schemas);
  
OPEN fkc_cur;
FETCH NEXT FROM fkc_cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @n += 1;
    PRINT @cmd;
    IF @Execute = 1
    BEGIN
        BEGIN TRY EXEC sys.sp_executesql @cmd; END TRY
        BEGIN CATCH PRINT N'   -- FAILED : ' + ERROR_MESSAGE(); END CATCH
    END
    FETCH NEXT FROM fkc_cur INTO @cmd;
END
CLOSE fkc_cur;
DEALLOCATE fkc_cur;
  
PRINT N'---';
PRINT CONVERT(nvarchar(10), @n) + N' FK '
    + CASE WHEN @Execute = 1 THEN N'activated and validated.' ELSE N'to be activated (preview).' END;
GO

All 6,072 foreign keys were already back, active and marked not trusted, because the loading script re-enabled them with NOCHECK. Revalidating them with WITH CHECK would mean full joins against the largest tables in the database.

On a read-only archive whose source integrity was enforced by Oracle for fifteen years, that buys one thing: the optimizer regains the right to eliminate redundant joins. Worth having on a schema of deeply nested views, not worth days of scanning here. They stayed not trusted.

The principle is simple: run every view, record what happened, and never lose the record. Each one gets a SELECT TOP (100) * inside a TRY/CATCH, so a view that fails is logged rather than fatal, and the outcome lands in a table with its duration in milliseconds. The cursor only picks up views that are not already recorded, which means an interrupted run resumes where it stopped instead of starting over, and the duration column is what you sort on once it is done:

SET NOCOUNT ON;
 
IF OBJECT_ID('dbo.ViewTestResults') IS NULL
    CREATE TABLE dbo.ViewTestResults (
        schema_name  sysname,
        view_name    sysname,
        statut       varchar(20),
        duree_ms     int,
        message      nvarchar(2048),
        tested_at    datetime2 DEFAULT SYSDATETIME(),
        CONSTRAINT PK_ViewTestResults PRIMARY KEY (schema_name, view_name)
    );
 
DECLARE @sch sysname, @vue sysname, @sql nvarchar(max), @t0 datetime2, @ms int, @i int = 0, @n int;
 
SELECT @n = COUNT(*)
FROM   sys.views v JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE  s.name = 'SCDAT'
  AND  NOT EXISTS (SELECT 1 FROM dbo.ViewTestResults r
                   WHERE r.schema_name = s.name AND r.view_name = v.name);
 
RAISERROR('=== %d view remaining ===', 0, 1, @n) WITH NOWAIT;
 
DECLARE c CURSOR FAST_FORWARD FOR
  SELECT s.name, v.name
  FROM   sys.views v JOIN sys.schemas s ON v.schema_id = s.schema_id
  WHERE  s.name = 'SCDAT'
    AND  NOT EXISTS (SELECT 1 FROM dbo.ViewTestResults r
                     WHERE r.schema_name = s.name AND r.view_name = v.name)
  ORDER  BY v.name;
 
OPEN c;
FETCH NEXT FROM c INTO @sch, @vue;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @i += 1;
    RAISERROR('[%d/%d] %s.%s', 0, 1, @i, @n, @sch, @vue) WITH NOWAIT;
 
    SET @t0 = SYSDATETIME();
    BEGIN TRY
        SET @sql = N'SELECT TOP (100) * FROM ' + QUOTENAME(@sch) + N'.' + QUOTENAME(@vue) + N';';
        EXEC sp_executesql @sql;
        SET @ms = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());
        INSERT dbo.ViewTestResults (schema_name, view_name, statut, duree_ms, message)
        VALUES (@sch, @vue, 'OK', @ms, NULL);
    END TRY
    BEGIN CATCH
        SET @ms = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());
        INSERT dbo.ViewTestResults (schema_name, view_name, statut, duree_ms, message)
        VALUES (@sch, @vue, 'FAILED', @ms, ERROR_MESSAGE());
        RAISERROR('    -> FAILED: %s', 0, 1, @vue) WITH NOWAIT;
    END CATCH
 
    FETCH NEXT FROM c INTO @sch, @vue;
END
CLOSE c; DEALLOCATE c;
 
RAISERROR('=== Done ===', 0, 1) WITH NOWAIT;
 
-- summary
SELECT statut, COUNT(*) AS nb, SUM(duree_ms) AS duree_totale_ms
FROM dbo.ViewTestResults GROUP BY statut;
 
-- failed
SELECT schema_name, view_name, duree_ms, message
FROM dbo.ViewTestResults WHERE statut='FAILED' ORDER BY view_name;
 
-- all
SELECT schema_name, view_name, statut, duree_ms
FROM dbo.ViewTestResults ORDER BY duree_ms DESC;

The result looked excellent: 604 views OK, zero errors, against real data. Six skipped because they are not implemented to be run without an explicit filter added. SELECT TOP 100 * without a filter is not a representative test but still confirms that definition and joins are working fine. So: test with the filter the users will actually write. A view that only misbehaves when queried in a way nobody queries it is not a broken view, but you cannot know that from a green tick.

Database set to read-only, final backup taken and user’s specific queries tested with success, the story now comes to a happy end.

An archive migration is not a cheap application migration. It is a different decision set, and read-only is the input that drives most of it. 25,000 triggers are pointless, PAGE compression free, and untrusted foreign keys perfectly acceptable. It even tells you how to index: broadly and in advance, because nobody can tell you which row an auditor will want in three years. Before anything else, ask what the target will be used for. The answer rewrites the plan before the first byte moves.

Measure, and distrust anything that presents itself as progress. The SSMA counter refreshes per batch and sits still for minutes at a time. percent_complete stays at zero for an entire index build. A green tick on SELECT TOP 100 confirms that the joins resolve and nothing beyond that. In every one of those cases the real number was sitting in a DMV.

On views, the conversion rate is the least useful number in the report. What made four thousand views a finite piece of work was not the translation but the two steps around it: agreeing the real scope with the client first, then classifying what remained by pattern in the Oracle source rather than one error message at a time. An AI assistant translates a family of twenty views very well. It validates nothing, and the test against the source stays yours.

Compression on an archive is the rare decision with nothing to weigh against it. 117 GB down to 30.7 GB on the first table, more than 2.5 TB reclaimed across the set, and not a single write to pay it back with. If your heap is mostly null numeric columns, it is bigger than you think, and it will compress better than you expect.

L’article Archiving 5 TB from Oracle into SQL Server: views, indexes and compression (part 2) est apparu en premier sur dbi Blog.

Imagine an employee asking the company’s AI assistant, ‘What is the notice period for supplier XYZ?’

The answer comes back immediately: a clear, precise quote from the contract. It sounds right, so nobody checks it. However, the quoted contract is an expired draft. The signed amendment that changed the notice period is stored somewhere else entirely.

This is a hypothetical scenario, but I believe similar situations are already occurring. It illustrates what I think is the most underestimated aspect of AI in ECM: a poor search result is obvious, but a poor AI response can be convincing.

Before AI, poor content quality was mostly just an annoyance. Searches took longer and users had to scroll through several versions of the same document, but they usually noticed when something looked wrong. Humans were still the filter.

With AI, however, this filter becomes less effective. The assistant reads the content, selects what it deems relevant and presents a coherent summary. Users may never see the other versions, outdated drafts or documents that contradict the answer.

In my post on information debt, I wrote about the shortcuts organizations take with their content and the price they pay later. AI is often the moment when that debt becomes payable. All those duplicates, forgotten drafts, and incomplete properties are now the raw material that your AI works with.

AI does not fix messy content. It simply reads it faster, summaries it with confidence and spreads the mess further.

When I look at how content is usually managed, I see five recurring problems.

Duplicates and near-duplicates. Which version is the truth? A person might know from experience. An AI assistant has no instinct for this unless the system gives it clear signals.

Outdated content. A procedure valid in 2019 sits next to the current one. Both are well written, and both look equally credible.

Missing or inconsistent metadata.. AI depends on context: the type of document, its status, its owner, the period it applies to. When the “Status” property is empty, or means different things in different departments, that context disappears. I covered part of this in The metadata trap. Metadata can fail by being too complex, and it can also fail by being unreliable.

Missing relationships. A contract, its amendments and its invoices tell one story. If they are stored as separate islands, the AI sees fragments. This is exactly the point I made in The forgotten power of relationships in ECM.

Unclear authority. Is this document a draft or approved? An internal opinion or an official policy? Without lifecycle information, an AI can’t tell the difference, and often neither can the user.

Here’s what I find reassuring: All the features that make content manageable in an ECM also make it trustworthy for AI:

• Lifecycle and status tell the AI what is current and approved.
• Metadata tells the AI what a document is and what it applies to.
• Relationships show which documents belong together.
• Permissions determine what each user’s AI is allowed to see.

The practices we have promoted for years, such as clear metadata, defined workflows and linked objects, were not just housekeeping measures. They provide the context that transforms a collection of documents into a knowledge base that an AI can utilize responsibly.

I made a similar point in ‘ECM + AI: achieving optimal synergy’: AI does not replace a well-structured ECM; it depends on it.

There is one more risk that deserves its own section because it takes many organizations by surprise.

For years, content that was shared too widely was protected by obscurity. A document might be accessible to more people than intended, but it would remain hidden because no one was looking for it. AI changes that. An assistant that can search and summarize everything a user has access to will surface content in seconds that nobody knew was reachable.

Therefore, before enabling AI features, check who can access your sensitive content. This includes broad groups, inherited rights, and shared links. It is much better to discover over-sharing during a controlled review than through an AI-generated response in front of the wrong person.

Making all content ‘AI-ready’ can seem overwhelming, but it doesn’t have to be. Here is the approach I would recommend:

Choose an area where AI could clearly add value, such as contracts, quality procedures or customer documentation. Prepare that content first.

Decide which statuses, document types and validity dates identify trusted content. If your team cannot answer the question ‘What counts as the official version?’, neither can the AI.

Archive or delete expired, duplicate and abandoned documents. This is where the lifecycle thinking from my earlier posts comes in useful.

Don’t try to perfect every property. Focus on the ones that users and AI rely on, such as type, status, owner, validity and relationships with related objects.

NOT AFTER!

Require AI answers to show their sources so that users can verify them. During the pilot, review the results and collect feedback on incorrect or unexpected answers.

When an answer is incorrect, establish the reason why. This is usually due to a content problem, such as a missing status or a duplicate. Fixing these issues improves every future answer.

This doesn’t mean that you have to have perfect content before you start. AI can help you achieve this. As I explained in M-Files AI: From Intelligent Metadata to AI Agents, it can suggest properties, classify documents, and identify similar content. This makes it a useful tool for identifying duplicates and filling in missing metadata.

A sensible approach is to work in cycles rather than gates: improve the content a little, use AI, see where it fails, then improve it again. However, it is important to have a human validate what the AI suggests, especially for properties that drive decisions.

Ask yourself the following questions about the content that AI would use:

• Can you tell whether any document is current and approved?
• Are the metadata for your key document types consistent and complete?
• Do you have an idea of how many duplicates exist of your most important content?
• Are related documents linked to each other?
• Do you know who can access your sensitive content, including via shared links?
• Can every AI answer be traced back to a source that users can verify?
• Is the quality of this content owned by someone?

If you answered “no” or “I don’t know” to several of these questions, that’s not a reason to stop. It’s a reason to start preparing!

AI readiness isn’t a separate project. Rather, it is what good ECM practice looks like when you finally ask your content to do more than just sit in a repository. It also provides a business reason to invest in quality work that was often postponed in the past.

The organizations that benefit most from AI won’t necessarily be those with the most advanced model. They will be the ones whose content can be trusted.

Have you tested AI features on your own content yet? I’d be interested to hear what surprised you.

L’article Is your content ready for AI? est apparu en premier sur dbi Blog.

In a previous blog, I noted that there might be a bug with the Maximum Input Characters setting in GoldenGate 26ai AI Service. The bug was confirmed by Oracle, so let’s see what it is exactly.

Maximum Input Characters is a setting in the AI Service. You can set it when registering a new model in the web UI.

In the Oracle documentation, the parameter is described as a cap on how much text is sent to the embedding model before it leaves GoldenGate. This ensures that an @AISERVICE mapping cannot send more than intended, and does not reach a token limit, or increase your consumption.

If this setting worked, a model with Maximum Input Characters set to 1 should truncate any input to a single character before calling the AI Service. This is not what I found when testing it.

I registered two AI Service models against the same Gemini provider and the same remote model (gemini-embedding-001). They differ only in Maximum Input Characters: one set to 1000, the other to 1.

If I query both models with the REST API, I can see the settings properly saved, in limits.maxInputCharacters:

{
    "id": "gemini_embed_max1000",
    "name": "gemini-embedding-max1000",
    "type": "remote",
    "enabled": true,
    "loaded": false,
    "description": "Model with 1000 character limit",
    "capabilities": [
        "embed"
    ],
    "providerId": "gemini",
    "remoteModelName": "gemini-embedding-001",
    "parameters": {},
    "limits": {
        "maxInputCharacters": 1000
    }
}

{
    "id": "gemini_embed_max1",
    "name": "gemini-embedding-max1",
    "type": "remote",
    "enabled": true,
    "loaded": false,
    "description": "Model with 1 character limit",
    "capabilities": [
        "embed"
    ],
    "providerId": "gemini",
    "remoteModelName": "gemini-embedding-001",
    "parameters": {},
    "limits": {
        "maxInputCharacters": 1
    }
}

I built an EXTAI/REPAI pair between PDB1.APP_PDB1.PRODUCTS on the source and PDB26.APP_PDB26.PRODUCTS on the target. Its COLMAP maps the description column through three separate @AISERVICE calls into three different VECTOR(3072, FLOAT32) columns:

MAP PDB1.APP_PDB1.PRODUCTS, TARGET APP_PDB26.PRODUCTS,
COLMAP (
    USEDEFAULTS,
    desc_vector_max1000 = @AISERVICE(embed, 'gemini_embed_max1000', description),
    desc_vector_max1000_run2 = @AISERVICE(embed, 'gemini_embed_max1000', description),
    desc_vector_max1 = @AISERVICE(embed, 'gemini_embed_max1', description));

The first two calls use the same model (gemini_embed_max1000). This is to show the baseline argument that the same input, embedded with the same model twice, produce the same vector. The third call uses the 1 character model, to test the bug.

The truncation is only relevant if identical inputs reliably produce identical output. Before comparing vectors across models, I first confirmed the embedding had no noise, by inserting the same 100 character description into two source rows and let both flow through the pipeline:

INSERT INTO app_pdb1.products (id, name, description) VALUES (101, 'AI Service Max Input Chars Test Row 1', 'GoldenGate 26ai AI Service test row for comparing embeddings across the max1000 and max1 test models');
INSERT INTO app_pdb1.products (id, name, description) VALUES (102, 'AI Service Max Input Chars Test Row 2', 'GoldenGate 26ai AI Service test row for comparing embeddings across the max1000 and max1 test models');
COMMIT;

Then, I compared the gemini_embed_max1000 vector between the two rows. I also compared the two separate @AISERVICE calls to the same model within row 101 itself. Both come back with a cosine distance of 0:

SQL> SELECT VECTOR_DISTANCE(a.desc_vector_max1000, b.desc_vector_max1000, COSINE) dist_101_vs_102_same_input
FROM app_pdb26.products a, app_pdb26.products b
WHERE a.id = 101
AND b.id = 102;

DIST_101_VS_102_SAME_INPUT
--------------------------
                         0

The conclusion is that gemini-embedding-001 is deterministic for identical input. Now, if the max1 vector is identical to the max1000 vector, we can rule out embedding noise.

With the same transaction, let’s look at the two different @AISERVICE calls, against a model capped at 1000 characters and one capped at 1:

SQL> SELECT id, VECTOR_DISTANCE(desc_vector_max1000, desc_vector_max1, COSINE) dist_max1000_vs_max1, VECTOR_DISTANCE(desc_vector_max1000, desc_vector_max1000_run2, COSINE) dist_max1000_vs_rerun
FROM app_pdb26.products
WHERE id IN (101, 102);

ID  DIST_MAX1000_VS_MAX1 DIST_MAX1000_VS_RERUN
--- -------------------- ---------------------
101                    0                     0
102                    0                     0

On both models, the distance between the resulting vectors is zero. If the Maximum Input Characters were enforced, the vectors should be different between both models. This proves that the Maximum Input Characters setting saved as 1 had no effect on what was sent to Gemini.

GoldenGate even has a documented warning for a situation where the input would exceed the limit. OGG-30677 is defined as @AISERVICE source column {0} contains {1} characters, exceeding model {2}'s maximum input limit of {3} characters. But in my case, the warning never appeared in the log files.

I added a fourth @AISERVICE call to the same replicat, mapping a CLOB column (big_description) through the 1000 character model. I then tried pushing progressively larger inputs.

With 100000 characters, the countTokens endpoint reports this input at 23679 tokens, way more than the documented inputTokenLimit of gemini-embedding-001. It applied without any error and produced a real 3072 dimension vector, distinct from every other vector in this blog.

SQL> SELECT id, VECTOR_DIMS(desc_vector_bigtest) dims
FROM app_pdb26.products
WHERE id = 103;

ID  DIMS
--- ----
103 3072

Here, we can see that even the documented token limit of your provider might not stop the AI Service to work.

On top of the per-request token limit, depending on your billing settings, you might have a usage quota on the API key. A request can be rejected by Gemini, and this error is raised in AIService.log:

{
    "error": {
        "code": 429,
        "message": "You exceeded your current quota, please check your plan and billing details. For more information on this error, head to: https://ai.google.dev/gemini-api/docs/rate-limits. To monitor your current usage, head to: https://ai.dev/rate-limit. ",
        "status": "RESOURCE_EXHAUSTED"
    }
}

And in this case, the replicat abends with the following error:

2026-09-27 08:22:21  WARNING OGG-30679  Response from EMBED endpoint using model gemini_embed_max1000 returned error: code REMOTE_INFERENCE_FAILED, message 'HTTP 429 - Too Many Requests: {
  "error": {
    "code": 429,
    "message": "You exceeded your current quota, please check your plan and billing details. For more information on this error, head to: https://ai.google.dev/gemini-api/docs/rate-limits. To monitor your current usage, head to: https://ai.dev/rate-limit. ",
    "status": "RESOURCE_EXHAUSTED"
  }
}
'.

2026-09-27 08:22:21  WARNING OGG-01431  Canceled grouped transaction on PDB26.APP_PDB26.PRODUCTS, Mapping error.
2026-09-27 08:22:27  ERROR   OGG-01668  PROCESS ABENDING.

Maximum Input Characters does not protect a replicat from an oversized @AISERVICE mapping. Because of this, the only real limit to your embeddings in GoldenGate is a usage quota. This can affect your replication, whether you rely on the setting for functional truncation or to lower your costs. Until the bug is fixed, be careful when using the AI Service!

L’article GoldenGate AI Service bug confirmed in the Maximum Input Characters setting est apparu en premier sur dbi Blog.

Cumulative Update 5 for SQL Server 2025 was released on May 20, 2026 (build 17.0.4045.5). Seven entries in the KB, mostly fixes. One of them is not a fix: it is a new server configuration option, max lock manager cache memory (%).

Reference: https://support.microsoft.com/en-us/servicing/sql/sql-server-2025/cumulative-update/kb5084896-cu5

I built a lab on CU5 and on later builds up to CU9 and I spent some time measuring. Most of the documentation is confirmed. The sentence that says what the option actually changes, is not: on my lab, under the conditions described below I could not reproduce it. This post is what I measured with the protocol so that you can run it yourself and tell me if you get something different.

A lock is not one structure in memory, it is two. The lock block describes the locked resource: this row, this page, this table. The lock owner block describes who holds it and in which mode. Three sessions reading the same row give one lock block and three lock owner blocks. sys.dm_tran_locks returns one row per owner block: the resource_* columns describe the lock block, the request_* columns the owner block and lock_owner_address is literally its address in memory.

The documentation gives 96 bytes per lock: 64 bytes for the lock block, plus 32 bytes per owner block. On my lab, a row lock costs about 195 bytes all included, the clerk’s pages divided by the number of locks held: twice the documented figure. I did not dig into where the other 100 bytes go (object store overhead, 64-bit structures, alignment). Keep 200 bytes in mind. Ten million row locks are 2 GB.

These structures are not database pages. They are stolen memory taken from a pool owned by the lock manager. This pool is visible as the OBJECTSTORE_LOCK_MANAGER memory clerk in sys.dm_os_memory_clerks (one clerk per NUMA node, named Lock Manager : Node 0…) and as the Lock Memory (KB) performance counter. The locks documentation says 2500 lock structures. On my lab, Lock Blocks Allocated reports 3050 right after a restart, on SQL Server 2022 as on 2025.

Reference : https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-locks-server-configuration-option?view=sql-server-ver17

Here is what I have :

SELECT
    cntr_value AS lock_blocks_allocated
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:Memory Manager%'
  AND counter_name = N'Lock Blocks Allocated';

Now the important part. When a transaction commits or rolls back, its locks are released. The lock structures are not returned to SQLOS. They stay in the pool, ready to be reused by the next lock request. This is a cache and the reason is simple: allocating memory for every lock acquisition would be expensive and lock acquisition is on the hot path of every query.

Four numbers. The documentation expresses the percentages against the “SQLOS committed memory”. On my lab, they behave as percentages of committed_target_kb in sys.dm_os_sys_info, the Target Server Memory (KB) counter: what SQLOS is allowed to use, not what it has used so far. Test 1 below shows why I say so.

Two remarks. Lock escalation exists precisely to keep lock memory small: 5000 row locks cost about 1 MB, one table lock costs 200 bytes. And the 60 % limit is not a cache limit: it is the maximum memory the lock manager may use, cache included.

The cache could grow up to the 60 % limit. It was given back slowly, by a background task and that task could be slow enough for the cache to look permanent.

The typical trigger is a workload that acquires a very large number of locks at once. The documentation mentions large concurrent query workloads with lock escalation disabled at the instance level (trace flag 1211). But you do not need a trace flag for that. ALTER TABLE … SET (LOCK_ESCALATION = DISABLE) on a big table, a reporting query in REPEATABLE READ or SERIALIZABLE scanning millions of rows, a purge that could not escalate because another session held an incompatible lock on the table: any of these can push lock memory to several GB.

After the transaction ends, the lock structures stay in the cache. The buffer pool, the plan cache and every other cache have to live with what is left. Page life expectancy drops, physical reads increase, and nothing in the current workload explains it, because the workload that caused it is over.

The symptom is easy to recognize once you know it. An idle instance, sys.dm_tran_locks almost empty and OBJECTSTORE_LOCK_MANAGER reporting GB. On an instance with a 128 GB target, the cache can hold up to about 77 GB.

The lock manager cache now has a ceiling and you can configure it:

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;

EXECUTE sp_configure 'max lock manager cache memory (%)', 20;
RECONFIGURE;

The server must be restarted before the setting can take effect. The Server configuration options page lists it as A, RR (advanced, restart required): https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server?view=sql-server-ver17

What the option limits is the cache, not the locks.

As stated in the documentation:

Lock manager memory can still grow up to 60 percent of the SQLOS committed memory if required by the workload. However, when locks are released, memory is freed rather than cached if the lock manager cache already reached its configured limit.

Reference : https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/max-lock-manager-cache-memory-configuration-option?view=sql-server-ver17

It says two things. A transaction may still take up to 60 % of the memory in locks: true (test 1). And when locks are released while the cache is already at its limit, the memory is freed instead of cached: this is the sentence I could not reproduce (tests 2 and 3).

;WITH lm AS
(
    SELECT SUM(pages_kb) AS lock_manager_kb
    FROM sys.dm_os_memory_clerks
    WHERE type = N'OBJECTSTORE_LOCK_MANAGER'
),
si AS
(
    SELECT committed_kb, committed_target_kb
    FROM sys.dm_os_sys_info
),
pc AS
(
    SELECT
        MAX(CASE WHEN counter_name = N'Lock Blocks'                THEN cntr_value END) AS lock_blocks_in_use,
        MAX(CASE WHEN counter_name = N'Lock Blocks Allocated'      THEN cntr_value END) AS lock_blocks_allocated,
        MAX(CASE WHEN counter_name = N'Lock Memory (KB)'           THEN cntr_value END) AS lock_memory_kb,
        MAX(CASE WHEN counter_name = N'Database Cache Memory (KB)' THEN cntr_value END) AS database_cache_kb,
        MAX(CASE WHEN counter_name = N'Free Memory (KB)'           THEN cntr_value END) AS free_memory_kb
    FROM sys.dm_os_performance_counters
    WHERE object_name LIKE N'%Memory Manager%'
)
SELECT
    SYSDATETIME()  AS measured_at,
    lm.lock_manager_kb / 1024  AS lock_manager_mb,
    si.committed_kb / 1024  AS committed_mb,
    si.committed_target_kb / 1024 AS target_mb,
    CAST(100.0 * lm.lock_manager_kb / si.committed_target_kb AS DECIMAL(5, 1)) AS lock_manager_pct,
    pc.lock_blocks_in_use,
    pc.lock_blocks_allocated,
    pc.lock_blocks_allocated - pc.lock_blocks_in_use AS lock_blocks_cached,
    pc.database_cache_kb / 1024 AS buffer_pool_mb,
    pc.free_memory_kb / 1024 AS free_mb
FROM lm
CROSS JOIN si
CROSS JOIN pc;

How to read it:

• lock_manager_mb is the whole lock manager memory: locks in use plus cached structures. On an idle instance, it is the size of the cache. This is the measure that matters.
• committed_mb is what SQLOS has taken from the operating system so far. target_mb is what it is allowed to take. On an instance that has been running for a while, the two are equal. On a freshly restarted lab, they are far apart and nothing in the percentages makes sense until they meet.
• lock_manager_pct is the clerk against the target. The 20 % and the 60 % are read on this column.
• buffer_pool_mb and free_mb show who pays for the cache, and who gets the memory back.
• lock_blocks_in_use, lock_blocks_allocated and their difference lock_blocks_cached are the performance counters on lock blocks. They are coherent on a freshly started instance (3050 allocated, 0 in use) and stop refreshing after a lock storm on every 2025 build I tested: 6 624 047 allocated blocks reported while the clerk held 1 MB. They are here so that you can see it. Do not use them as evidence, sys.dm_tran_locks is the truth for locks held, the clerk for memory.

SQL Server 2025 Developer Edition, CU5 (17.0.4045.5) first, then CU9 (17.0.5005.3), on virtual machines of a Proxmox lab and on a Nutanix environment. One SQL Server 2022 instance for comparison, in test 5. The SQLOS target is 4096 MB unless stated otherwise (2048 MB in test 4), with min server memory and max server memory pinned to the same value.

A database with one narrow table. Lock escalation is disabled on the table: we want millions of row locks, not one table lock. This is the scenario the documentation describes at table level instead of trace flag 1211:

CREATE DATABASE LockCacheDemo
GO
ALTER DATABASE LockCacheDemo SET RECOVERY SIMPLE
GO
USE LockCacheDemo
GO
CREATE TABLE dbo.T_Locks
(
    id     INT      NOT NULL,
    filler CHAR(20) NOT NULL CONSTRAINT DF_T_Locks_filler DEFAULT ('x'),
    CONSTRAINT PK_T_Locks PRIMARY KEY CLUSTERED (id)
)
GO
INSERT INTO dbo.T_Locks WITH (TABLOCK) (id)
SELECT value
FROM GENERATE_SERIES(1, 30000000)
GO
ALTER TABLE dbo.T_Locks SET (LOCK_ESCALATION = DISABLE)
GO

Thirty million rows, about 1 GB. Nothing in this block touches the lock manager: the TABLOCK on the insert takes one table lock to load fast. This block only provides lockable rows and removes the mechanism that would stop us from locking them one by one.

The workload (session A):

USE LockCacheDemo
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
DECLARE @rows INT = 1000000;

BEGIN TRANSACTION

SELECT COUNT(*) AS rows_locked
FROM (
    SELECT TOP (@rows) id
    FROM dbo.T_Locks WITH (ROWLOCK)
    ORDER BY id
) AS x

-- Leave the transaction open. Measure from session B. Then:
-- ROLLBACK TRANSACTION

Three different settings:

From a second session (session B): did we get row locks and did escalation stay out?

SELECT resource_type, request_mode, COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE request_session_id = 65   -- The session holding the locks
GROUP BY resource_type, request_mode
ORDER BY lock_count DESC;

One shared lock per row (KEY, the row of a clustered index). One intent lock per page, about 245 rows per page. One intent lock on the table and no OBJECT S: no escalation.

Second check, the cost of a lock. Measured on a restarted instance each time so that nothing was cached before the run:

DECLARE @spid    INT    = 65;  -- session A
DECLARE @idle_kb BIGINT = 0;   -- clerk before the transaction

SELECT
    l.lock_count,
    l.key_locks,
    c.lock_manager_kb / 1024                                  AS lock_manager_mb,
    (c.lock_manager_kb - @idle_kb) * 1024.0 / l.lock_count    AS bytes_per_structure,
    (c.lock_manager_kb - @idle_kb) * 1024.0 / l.key_locks     AS bytes_per_row_lock
FROM (
    SELECT COUNT_BIG(*)                                            AS lock_count,
           SUM(CASE WHEN resource_type = N'KEY' THEN 1 ELSE 0 END) AS key_locks
    FROM sys.dm_tran_locks
    WHERE request_session_id = @spid
) AS l
CROSS JOIN (
    SELECT SUM(pages_kb) AS lock_manager_kb
    FROM sys.dm_os_memory_clerks
    WHERE type = N'OBJECTSTORE_LOCK_MANAGER'
) AS c;

For example :

Four runs, the same figure: about 195 bytes per row lock, everything included. Twice the 96 bytes of the documentation (64 for the lock block, 32 per owner block).

The name of the option invites a misunderstanding: it is not a limit on locks. Option at its default of 20 and @rows = 70 000 000 far beyond what 60 % of the target can hold (Target at 4968 MB for this run). The lock manager climbs.

The probe shows 20 %, then 24 % without any escalation (the table option prevents it), then 40 %, 50 % and the statement dies:

Severity 19: the statement is terminated and the transaction is rolled back. The same message lands in the SQL Server error log and in the Windows application log.

This test also settled which memory figure the percentages refer to. On the last probe before the error, the clerk was at 2979 MB: 60 % of the 4968 MB target, and 70 % of the 4233 MB SQLOS had committed at that moment. A 60 % limit on the committed memory would have fired about 440 MB earlier, at 2540 MB. The 60 % limit, and with it the 20 % one, are percentages of the target, not of the memory committed so far. This is why the probe divides by committed_target_kb.

This is the test I wrote this post for. Option at 20. Target at 4096 MB for this run, so the cache ceiling is 819 MB. Nine million locks:

buffer_pool_mb is the Database Cache Memory (KB) counter: the data and index pages in memory. The 349 MB are the pages of the nine million rows the SELECT had to read to lock them, nothing else touched the buffer pool. sys.dm_tran_locks is empty, the session holds nothing and the clerk has not moved. 1677 MB of lock structures are sitting in the cache, twice the configured ceiling. The ROLLBACK did not free anything and neither did the minutes after it.

I ran this on CU5 and on CU9, with 5, 6, 9 and 12 million locks with targets of 2 048 and 4 096 MB. Same result every time. The last run, 12 million locks on CU9 left 2193 MB in the cache, 54 % of the target and they were still there hours later.

There is a second way to read the sentence of the documentation. Not the release of the storm but the releases that follow: once the cache is above its limit, every structure released by a later transaction should be freed instead of cached and the cache would erode with the lock activity of the workload, down to 20 %. “Large concurrent query workloads” as the documentation puts it.

A first attempt with UPDATE TOP (1000000) … SET filler = ‘y’ and a COMMIT, repeated, lowered the clerk by a few MB per pass. Not conclusive: without a ROWLOCK hint, the engine is free to take page locks for an update of this size and then only a few thousand structures travel through the cache at each pass.

So the proper version, with row locks guaranteed and verified. Cache at 40.9 % of the target after a storm, option at 20, and short transactions in a loop:

USE LockCacheDemo
GO

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

DECLARE @i INT = 0, @n INT;

WHILE @i < 100
BEGIN
    BEGIN TRANSACTION;
    SELECT @n = COUNT(*)
    FROM (SELECT TOP (200000) id
          FROM dbo.T_Locks WITH (ROWLOCK)
          WHERE id > (@i % 50) * 200000
          ORDER BY id) AS x;
    COMMIT;            -- 200 000 structures released
    SET @i += 1;
END

One hundred transactions, 200,000 row locks each, verified in sys.dm_tran_locks while the loop ran: twenty million lock releases, more than four times the 4.6 million structures sitting above the ceiling (858 MB).

The clerk did not move. Twenty million releases while the cache was more than twice its limit and the memory stayed. Whatever “when locks are released, memory is freed rather than cached” describes, it is not something I can observe with row locks, on CU5 or CU9, with the option at its default.

Two things did, during the week. None of them is the sentence of the documentation. Memory demand, once. Target at 2 048 MB, option at 20, six million locks, ROLLBACK: 1 117 MB in the cache, 54.6 %. Then a NOLOCK scan of a 17 GB table which takes one schema stability lock and nothing else but needs every page in the buffer pool:

The scan pushed committed_mb up to the target at that moment the buffer pool still wanted pages, SQLOS had nothing left to take from the operating system and it took from the lock manager: 785 MB given back, the clerk under the ceiling. Not one lock released in between. That looked like the mechanism. It was not reproducible either: on CU9 with the cache at 2 193 MB and the instance already at its 4 096 MB target, the same 17 GB scan left the cache at 2 190 MB. sys.dm_os_ring_buffers tells why: the memory broker that governs the lock manager cache (MEMORYBROKER_FOR_CACHE, which is where the object store lives, not MEMORYBROKER_FOR_STEAL) kept sending it a target of 2 809 MB, 68 % of the target with SHRINK notifications of a few MB. There is no 20 % anywhere in the broker’s arithmetic.

DBCC FREESYSTEMCACHE, instantly. The documentation only shows ‘ALL’, which releases the unused entries of every cache, plan cache included, at the price of recompiling every plan afterwards. It also says that most caches can be freed individually. The lock manager cache is one of them:

SELECT name, pages_kb / 1024 AS mb
FROM sys.dm_os_memory_clerks
WHERE name LIKE N'Lock Manager%'; 

DBCC FREESYSTEMCACHE('Lock Manager : Node 0');

Before :

After :

Probe right after, on the 1677 MB cache left by test 3: 8 MB in the clerk, and the memory back in free_mb. The cache is releasable at any time in one instruction. What is missing on these builds is not the ability to release it, it is the trigger.

If the option changed something, the comparison with a version that does not have it should show it. Same table, same workload, @rows = 70000000 for the wall and a value under the wall for the clean ROLLBACK, on SQL Server 2022 and on SQL Server 2025 CU5, CU9 with the option at 20:

Two things in this table. The background release exists on 2022, without any option, and it brings the cache down to 17.6 %, which is where the 20 % ceiling of 2025 would put it. And 2025 is not faster than 2022 at giving the memory back.

Put together, on SQL Server 2025 CU5 and CU9 with one big transaction and then normal lock activity:

• Lock memory, cache included, can grow up to 60 % of the target. Beyond that, error 1204. Confirmed and unchanged by the option.
• When the storm is released, nothing is freed. The structures go to the cache above the configured ceiling and stay there for hours.
• The lock activity that follows does not free them either: twenty million row-lock releases above the ceiling, no measurable change.
• The cache does come down, slowly, through the generic SQLOS mechanisms (background release, memory broker rebalancing when the instance is at its target), the same ones SQL Server 2022 has. On 2025, they are not faster, and the 20 % figure does not appear in the broker’s targets.
• DBCC FREESYSTEMCACHE(‘Lock Manager : Node N’) empties the cache immediately on any version I tried.

So the documented behavior, “when locks are released, memory is freed rather than cached if the lock manager cache already reached its configured limit”, is not something I could reproduce. Not on CU5, not on CU9, not with the release of the big transaction, not with twenty million releases afterwards. The option is there, it accepts 20 to 60, value_in_use says 20 and the cache sits around 40% for as long as I care to watch.

Two caveats, because this is a lab and not a support case. One access pattern: a single transaction holding millions of row locks then released. Virtual machines with 2 and 4 GB targets. Developer Edition. It is possible that the mechanism needs a condition I did not create and that “large concurrent query workloads” means something more specific than what I ran. If you reproduce the protocol above and see the cache come back under 20 % after a release, I want to hear about it. If you do not, you have the same question I have.

• Released locks are not freed, they are cached by the lock manager. A row lock costs about 195 bytes all included.
• The thresholds (24 % escalation, 60 % hard limit, 20 % cache ceiling) are read against committed_target_kb, not committed_kb. On a lab, set min and max server memory before measuring anything.
• CU5 adds max lock manager cache memory (%): 20 by default, 20 to 60 allowed, a restart to take effect (is_dynamic = 0).
• The 60 % wall and error 1204 are unchanged by the option.
• On CU5 and CU9, releasing the locks does not bring the cache back under the ceiling and neither do twenty million later releases. The documented behavior was not reproducible on my lab.
• The cache does shrink through the generic SQLOS mechanisms slowly as on SQL Server 2022 and the memory broker targets it well above 20 %.
• DBCC FREESYSTEMCACHE(‘Lock Manager : Node N’) empties it instantly (on any version I tried). It is the remedy that works.
• The lock block performance counters stop refreshing on these builds. Use the clerk and sys.dm_tran_locks.

L’article SQL Server 2025 CU5: max lock manager cache memory (%): what I could reproduce and what I could not est apparu en premier sur dbi Blog.

For as long as I have used GoldenGate, an initial load had one prerequisite: the target tables must already exist. Data Pump takes care of it between two Oracle databases. For anything else, you write the CREATE TABLE statements yourself, with the datatypes you choose, or choose another initial load method. The Automatic Schema Evolution feature launched in GoldenGate 26ai promises to “automatically detect and propagate supported schema changes as part of the replication flow”. I wanted to see it work, so I tried it from an Oracle 19c PDB to a PostgreSQL database.

And it works. The replicat creates the missing table itself, with a default datatype mapping, and loads the rows in the target.

Automatic schema evolution is a preview feature in GoldenGate 26ai: its syntax, messages and behavior can change before it becomes generally available, so the results below apply to the 23.26.3 release I tested.

The feature replicates table structure changes between databases of the same or of different types. The exception is Oracle to Oracle, where regular DDL replication is the solution. It also creates tables during an initial load. The replicat must be 26ai or higher, but the extract writing the trail can be 19c or higher. On a live trail, the replicat also adds a column that appears on the source. The documentation lists ADD COLUMN as the one supported ALTER TABLE. But an initial load never alters an existing table: it leaves it alone or, with OPTYPE RECREATE, drops and recreates it.

The replicat does not need anything from the source database. It builds the CREATE TABLE statement from the Table Definition Record (TDR) that GoldenGate already writes in the trail file.

AUTOSCHEMA is a replicat-only option of DDL: the DDL parameter of the extract does not accept it. I put DDL AUTOSCHEMA INCLUDE ALL in the extract’s parameter file. The extract abends at startup with the same parsing error as an unrecognized keyword:

2026-09-26T05:54:27Z  ERROR   OGG-10151  (EXTPGL.prm) line 3: Parsing error, parameter [DDL] has unrecognized keyword or extra value "AUTOSCHEMA".

That makes sense given what the feature does. It creates missing tables on the target, and the extract never touches the target database.

• Source: Oracle 19c, PDB1, schema AUTOSCH, table AUTOSCH.T1 with 5 rows, managed by the 26ai deployment ogg_test_01 in GoldenGate 23.26.3.0.0.
• Target: PostgreSQL 16, database pgstore, an empty schema autosch, managed by a separate deployment ogg_pg_01 from GoldenGate for PostgreSQL 23.26.3.0.1.

The source table:

CREATE TABLE autosch.t1 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    amount NUMBER(10,2),
    created DATE DEFAULT SYSDATE
);

For the initial load, on the ogg_test_01 deployment, EXTPGL is added as a SOURCEISTABLE extract.

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS pdb1 DOMAIN OracleGoldenGate
Successfully logged into database PDB1.

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 3> ADD EXTRACT EXTPGL, SOURCEISTABLE
2026-09-26T05:53:26Z  INFO    OGG-08100  Extract added.

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 4> EDIT PARAMS EXTPGL
2026-09-26T05:53:26Z  INFO    OGG-10183  Parameter file EXTPGL.prm passed validity check.

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 5> VIEW PARAMS EXTPGL
EXTRACT EXTPGL
USERIDALIAS pdb1 DOMAIN OracleGoldenGate
EXTFILE p1 MEGABYTES 100 PURGE
TABLE AUTOSCH.T1;

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 6> START EXTRACT EXTPGL
2026-09-26T05:53:26Z  INFO    OGG-00975  Extract group EXTPGL starting.
2026-09-26T05:53:26Z  INFO    OGG-15426  Extract group EXTPGL started.

The extract report confirms the 5 rows went out to the file:

Report at 2026-09-26 05:53:26 (activity since 2026-09-26 05:53:26)

Output to p1:

From table AUTOSCH.T1:
       #                   inserts:         5
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0

The target is a separate deployment, ogg_pg_01, from the Oracle GoldenGate for PostgreSQL binaries. A distribution path delivers the p1 trail from ogg_test_01 to ogg_pg_01. The replicat there, REPPGL, is added straight against the trail name, with EXTSEQNO 0 telling it where to start reading. The checkpoint table is oggadmin.checkpoint:

OGG (http://localhost:7859 ogg_pg_01) 2> DBLOGIN USERIDALIAS pgalias DOMAIN OracleGoldenGate
Successfully logged into database.

OGG (http://localhost:7859 ogg_pg_01 as pgalias@pgstore) 3> ADD REPLICAT REPPGL, EXTFILE p1, EXTSEQNO 0, CHECKPOINTTABLE oggadmin.checkpoint
2026-09-26T09:14:42Z  INFO    OGG-30509  Connected to database server using ODBC DSN: ogg_pg_01 with SSLMODE 'disable'
2026-09-26T09:14:42Z  INFO    OGG-08100  Replicat added.

REPPGL’s parameter file starts with just a MAP, no DDL clause at all:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
MAP AUTOSCH.*, TARGET AUTOSCH.*;

With this replicat, the initial load stops on the first table, as it always did:

2026-09-26 05:52:11  WARNING OGG-00869  Could not retrieve definition for table AUTOSCH.T1.
2026-09-26 05:52:11  ERROR   OGG-00199  Table AUTOSCH.T1 does not exist in target database.

The feature is called AUTOSCHEMA, but as a parameter on a line by itself, it is rejected:

2026-09-26T05:52:30Z  ERROR   OGG-10141  (REPPGL.prm) line 3 column 1: Parsing error, value "AUTOSCHEMA" syntax error.
2026-09-26T05:52:30Z  ERROR   OGG-10184  Parameter file REPPGL.prm failed validity check.

AUTOSCHEMA is an option of the DDL parameter, followed by the usual INCLUDE and EXCLUDE clauses:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDL AUTOSCHEMA INCLUDE ALL
MAP AUTOSCH.*, TARGET AUTOSCH.*;

The replicat starts and announces the feature, then finds the table missing and creates it:

2026-09-26 05:52:41  INFO    OGG-30684  The Automatic Schema Evolution feature is enabled. Please refer to the GoldenGate documentation for the supported operations, the datatype mappings, and the corresponding parameter options to enable/disable/override the functionality.
2026-09-26 05:52:41  WARNING OGG-00869  Could not retrieve definition for table AUTOSCH.T1.
2026-09-26 05:52:41  WARNING OGG-30633  Table AUTOSCH.T1 does not exist in target database, AutoSchema will attempt to create the missing table.
2026-09-26 05:52:41  INFO    OGG-02756  The definition for table AUTOSCH.T1 is obtained from the trail file.

The same warning OGG-00869 as in the baseline is still there. But it is followed by OGG-30633 and by OGG-02756, which shows that the definition comes from the trail file. In PostgreSQL:

pgstore=# \d autosch.t1
                            Table "autosch.t1"
 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | character varying(50)          |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

pgstore=# select * from autosch.t1 order by 1;
 id | name  | amount |       created
----+-------+--------+---------------------
 1  | row 1 |  10.25 | 2026-09-26 05:43:53
 2  | row 2 |  20.50 | 2026-09-26 05:43:53
 3  | row 3 |  30.75 | 2026-09-26 05:43:53
 4  | row 4 |  41.00 | 2026-09-26 05:43:53
 5  | row 5 |  51.25 | 2026-09-26 05:43:53
(5 rows)

The table was created, the primary key too, and the 5 rows were loaded. This is the default datatype mapping at work:

The primary key is the surprise: a NUMBER without precision becomes a varchar on PostgreSQL. Check the mapping before you let a replicat create tables that an application will query. It can be overridden, which is the subject of another post.

Between two loads, I added a column to the source table (ALTER TABLE autosch.t1 ADD (note VARCHAR2(20) DEFAULT 'added')). I also inserted a stray row in the PostgreSQL table. Then I reran EXTPGL and pointed the same REPPGL, still with DDL AUTOSCHEMA INCLUDE ALL, at the new file. It did not change the existing table:

2026-09-26 05:53:26  INFO    OGG-06511  Using following columns in default map by name: id, name, amount, created.
2026-09-26 05:53:26  WARNING OGG-01004  Canceled grouped transaction on table autosch.t1. Database error 3505685, (SQLState = 23000 SQLError = 3,505,685 SQLErrorHex = 00357e15 SQLErrorText = [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; duplicate key value violates unique constraint "t1_pkey"(Detail Key (id)=(1) already exists.; sautosch; tt1; nt1_pkey; File nbtinsert.c; Line 666; Routine _bt_check_unique; )).
2026-09-26 05:53:26  ERROR   OGG-01296  Error mapping from AUTOSCH.T1 to autosch.t1.

The new source column NOTE is silently left out of the column mapping: an initial load does not alter an existing table. The load then abends on duplicate key value violates unique constraint "t1_pkey", as the rows are already there. So DDL AUTOSCHEMA INCLUDE ALL creates missing tables only.

The documentation says that for an initial load trail, DDL AUTOSCHEMA “automatically creates or replaces target tables”. It does not say how to get the replacement. The replicat binary has a RECREATE token, and it is a DDL operation type, so it goes in the INCLUDE filter:

DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE

To see what RECREATE does, I prepare a PostgreSQL table that already exists. It has a column that is not on the source and a row that is not in the load:

CREATE TABLE autosch.t1 (
  id varchar(50) PRIMARY KEY,
  name varchar(50),
  amount numeric(10,2),
  created timestamp(0),
  legacy text
);
INSERT INTO autosch.t1 (id, name, legacy)
VALUES ('99', 'stray row', 'pre-existing');

The initial load is delivered the way the documentation describes it:

• A SOURCEISTABLE extract writes the 5 rows of AUTOSCH.T1 to the extract file ci.
• A distribution path sends ci to the receiver of the PostgreSQL deployment, as the trail di.
• A replicat reads the trail di with DDLOPTIONS REPORT and an explicit MAP AUTOSCH.T1, TARGET AUTOSCH.T1;.

With DDL AUTOSCHEMA INCLUDE ALL, the replicat recognizes the initial load by itself. It builds a RECREATE operation from the table definition record, then excludes it: INCLUDE ALL does not cover that operation type.

2026-10-03 13:11:47  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [ /* AutoSchema */ create table "autosch"."t1" (ID varchar(50) not null, NAME varchar(50), AMOUNT decimal(10,2), CREATED timestamp(0), primary key(ID)) (size 150)].
2026-10-03 13:11:47  INFO    OGG-00488  DDL operation excluded [not included by any filter], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "t1".

The 5 rows are loaded into the existing table, next to the stray row, and the legacy column is still there:

 id |   name    | amount |       created       |    legacy
----+-----------+--------+---------------------+--------------
 1  | row 1     |  10.25 | 2026-09-26 09:11:40 |
 2  | row 2     |  20.50 | 2026-09-26 09:11:40 |
 3  | row 3     |  30.75 | 2026-09-26 09:11:40 |
 4  | row 4     |  41.00 | 2026-09-26 09:11:40 |
 5  | row 5     |  51.25 | 2026-09-26 09:11:40 |
 99 | stray row |        |                     | pre-existing
(6 rows)

The parameter file for this second run only differs from the previous one by the OPTYPE RECREATE filter:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDLOPTIONS REPORT
DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE
MAP AUTOSCH.T1, TARGET AUTOSCH.T1;

With this file and the same table reset, the replicat warns about what it is going to do, then drops the table:

2026-10-03 13:12:34  WARNING OGG-30687  The OPTYPE 'RECREATE' is specified with DDL AUTOSCHEMA option. Please note that the corresponding pre-existing tables will be attempted to be dropped and re-created, only when the REPLICAT is applying initial load trail files. In case of CDC trails, REPLICAT would attempt to create the tables, only when the corresponding tables do not exist already.
2026-10-03 13:12:34  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "t1".
2026-10-03 13:12:34  INFO    OGG-30640  Table autosch.t1 is dropped successfully.
2026-10-03 13:12:34  INFO    OGG-00484  Executing DDL operation.
2026-10-03 13:12:34  INFO    OGG-00483  DDL operation successful.

The table is recreated from the table definition record and loaded, and the legacy column and the stray row are gone.

                            Table "autosch.t1"
 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | character varying(50)          |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

 id | name  | amount |       created
----+-------+--------+---------------------
 1  | row 1 |  10.25 | 2026-09-26 09:11:40
 2  | row 2 |  20.50 | 2026-09-26 09:11:40
 3  | row 3 |  30.75 | 2026-09-26 09:11:40
 4  | row 4 |  41.00 | 2026-09-26 09:11:40
 5  | row 5 |  51.25 | 2026-09-26 09:11:40
(5 rows)

No parameter tells the replicat that it applies an initial load: a trail written by a SOURCEISTABLE extract is enough. I got the same drop and recreate in three setups. The replicat was added on the trail with EXTTRAIL, added with EXTFILE, or reading a copy of the raw extract file. On a CDC trail, RECREATE does not drop anything, as the warning says.

The MAP of the previous test was explicit (MAP AUTOSCH.T1, TARGET AUTOSCH.T1;). The baseline of this post used MAP AUTOSCH.*, TARGET AUTOSCH.*;. I replaced it with the wildcard on the same table and the same trail, and nothing else changed:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDLOPTIONS REPORT
DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE
MAP AUTOSCH.*, TARGET AUTOSCH.*;

This time, the replicat never drops the table. There is no OGG-30640 in the report, and the CREATE TABLE that follows abends because the table is still there:

2026-10-03 13:17:02  INFO    OGG-06506  Wildcard MAP resolved (entry AUTOSCH.*): MAP "AUTOSCH"."T1", TARGET AUTOSCH."T1".
2026-10-03 13:17:02  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "T1".
2026-10-03 13:17:02  INFO    OGG-00484  Executing DDL operation.
2026-10-03 13:17:02  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [6844183], [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; relation "t1" already exists(File heap.c; Line 1167; Routine heap_create_with_catalog; )], no error handler present.
2026-10-03 13:17:02  ERROR   OGG-30538  Fatal error executing DDL statement [ /* AutoSchema */ create table "autosch"."t1" (ID varchar(50) not null, NAME varchar(50), AMOUNT decimal(10,2), CREATED timestamp(0), primary key(ID))].

The first line of the report explains it. The wildcard is resolved from the table name in the trail, so the target becomes AUTOSCH."T1": the case of the source (uppercase on Oracle), and quoted. PostgreSQL stores an unquoted name in lowercase, so my existing table is t1, not "T1". The replicat looks for "T1" to drop it, finds nothing, and the CREATE TABLE that follows hits the real t1. To check this, I created the existing table in PostgreSQL as "T1" (quoted, so uppercase). Then I ran the same wildcard replicat: this time the drop works.

2026-10-03 13:27:14  INFO    OGG-06506  Wildcard MAP resolved (entry AUTOSCH.*): MAP "AUTOSCH"."T1", TARGET AUTOSCH."T1".
2026-10-03 13:27:14  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "T1".
2026-10-03 13:27:14  INFO    OGG-30640  Table autosch.T1 is dropped successfully.
2026-10-03 13:27:14  INFO    OGG-00483  DDL operation successful.

The names are matched exactly. With an explicit MAP AUTOSCH.T1, TARGET AUTOSCH."T1"; on a lowercase t1, the replicat created a second table "T1" and left t1 alone. Without quotes, the target of an explicit MAP is lowercased for PostgreSQL, which is why the first test dropped t1. So with RECREATE on PostgreSQL, the target name in the MAP must be spelled exactly like the existing table. Write one explicit MAP per table, unquoted for a lowercase table. The RECREATE parameter file with the explicit MAP above does exactly that.

• AUTOSCHEMA is not for Oracle-to-Oracle replication. Pointing the same DDL AUTOSCHEMA INCLUDE ALL replicat at an Oracle target instead of PostgreSQL abends immediately:

  2026-09-26 05:54:11  ERROR   OGG-30624  AUTOSCHEMA is not allowed in an Oracle to Oracle replication scenario. Please use the DDL replication to control schema evolution.

  Between two Oracle databases, regular DDL replication is still the standard tool for schema evolution, and a GoldenGate initial load still has no automatic way to create the target table.

• The trail must be format 19.1 or higher with Table Definition Records.

• UNMAPPED and OTHER scope are not valid with AUTOSCHEMA. Adding INCLUDE UNMAPPED (or INCLUDE OTHER) next to INCLUDE ALL parses fine, but the replicat rejects it at startup:

  2026-09-26 07:12:06  ERROR   OGG-30689  The 'UNMAPPED' is not a valid DDL SCOPE to be used with Automatic Schema Evolution.

• Automatic schema evolution is enabled on the replicat with DDL AUTOSCHEMA INCLUDE ALL.
• During an initial load from Oracle to PostgreSQL, it creates the missing table and its primary key from the Table Definition Record of the trail (OGG-30633). Then it loads the rows.
• The datatypes come from a default mapping: check it first, as a NUMBER primary key became a varchar(50).
• An initial load leaves an existing table alone. A column added on the source is not added on the target, and the rows are loaded into it.
• DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE drops and recreates an existing table, only when the trail is an initial load trail (OGG-30640). INCLUDE ALL alone excludes that operation (OGG-00488).
• Use one explicit MAP per table with RECREATE on PostgreSQL. A wildcard MAP looks for the table under the source name in quotes and misses a lowercase table. The CREATE TABLE then abends with relation "t1" already exists.
• It is not available between two Oracle databases. Pointing it at an Oracle target from an Oracle source abends with OGG-30624. An Oracle-to-Oracle initial load still needs the target table to already exist, exactly as before.

L’article GoldenGate 26ai automatic schema evolution: creating missing tables during an initial load est apparu en premier sur dbi Blog.

When discussing agentic AI, we spend a lot of time on the model. Which one should we use? How much context does it support? Can it choose the right tool? These are useful questions. But from a my perspective, there are a few others I would ask quite early. Where does the agent store its state? Who can access the information it retrieves? What happens when two agents update the same record? Can we recover after a bad change? And can we explain which data was used to produce an answer? What governance do we need to apply ?
In an upcoming essay, Deciding in the Storm, I am exploring how organizations can make useful decisions while AI keeps changing. Here, I focus on the application underneath: where its knowledge and workflow state live, who may change them, and what happens when several users or agents depend on them at once. These are questions database teams already know how to investigate.

That is good news. We already have useful tools and operational experience, but we need to apply them properly.

On 12 June 2026, Google Cloud introduced the Open Knowledge Format, or OKF. The initial specification represents knowledge as Markdown files with YAML frontmatter and conventions for linking and organizing them. Table descriptions, metric definitions, runbooks and business rules can be exchanged in a format that both people and agents can read. You can review the files, version them in Git and move them between tools.
I like that approach. We do not need another proprietary API just to explain what a business metric means. The v0.2 specification includes provenance, verification records, lifecycle status and a stale_after timestamp. It also defines attested computations: a concept that carries both the meaning of a value and the approved method to calculate it, so a consumer can check that a figure came from the validated calculation rather than from an agent that improvised it. And it explicitly leaves storage, serving and query infrastructure to the implementation. A field can describe when knowledge becomes stale. Something still has to detect source changes, refresh that knowledge and decide how consumers handle it. Likewise, a verification record describes a check; it does not enforce access permissions.

OKF can be part of a governed platform, including one backed by a database. The format and the operational platform have different responsibilities.

The famous LLM wiki pattern described by Andrej Karpathy is a useful example, and it is the pattern Google says OKF formalizes: keep source material, then let an LLM build and maintain linked Markdown pages around it.

I personally use Obsidian for my own notes, and I understand the appeal. Files are easy to inspect, easy to edit and easy to move. When I work alone, I can decide how to organize the corpus and review what the agent changes. With suitable backups and control over what reaches the model, this can be a perfectly reasonable architecture.

As the corpus grows, I can improve how I find information. Start with filenames, links, metadata and text search. I would recommend adding an index when repeated scans become inconvenient (Karpathy’s own note puts the useful limit of a plain index file at roughly a hundred sources and a few hundred pages, before a local search engine becomes worthwhile). SQLite FTS5 provides full-text search, while ordinary tables can hold document paths, tags, source revisions and indexing status. Markdown remains the authoritative content and SQLite holds a derived index that can be rebuilt from the files, as long as the indexing process detects modifications and deletions. With SQLite we have then included a database, even though there is no database server to operate. Note that the database capabilities can start inside a local application.

SQLite is also capable of serving multiple users through an application. Its deployment guidance highlights workload and concurrency as deciding factors: it permits one writer at a time per database file. The number of colleagues alone does not determine when PostgreSQL becomes necessary.

Strictly speaking, the LLM is a model inside an application (agents are programmatic). The surrounding application supplies tools, identity, retrieval and any state it needs to retain. An application can persist information without a database: files, object storage and durable logs are all possible storage choices. The precise requirement is that state needed after a restart or a new session must be stored somewhere outside the transient computation and loaded again when required.

What changes at team and organizational level is the set of guarantees we need around that state. A team could share reviewed documentation through Git and serve a mostly read-only corpus successfully. But consider a system where several agents update shared tasks, record approvals, remember customer-specific facts and act on behalf of users with different permissions. We now need conflict handling, authoritative versions, controlled visibility and recoverable history. Personal conventions are no longer enough to establish who may change what.

The comment thread under Karpathy’s gist is a good illustration of that evolution. By September, the people building their own versions of the pattern were reporting the same needs: write gates and proposal queues so that several clients can write without overwriting each other; provenance and verification stamps so that an unsupported answer filed into the wiki is not later treated as a source; staleness checks so that someone learns when a page stopped being true; and an MCP server in front of one canonical corpus so that different tools read and write the same memory. One contributor argued that a wiki needs to record both when a fact was true and when the system came to “believe” it, and that Markdown makes this hard. Those are database requirements, and the SQL standard has described that last one, bitemporal data, since 2011.

“Memory” hides several responsibilities that do not need the same home:

Only the second kind cannot be rebuilt from something else. That is where I would normally put the database first, keeping knowledge documents where they are most useful. The database might already sit behind an existing service; it does not have to be a new PostgreSQL instance dedicated to the agent.
This is the argument I am making: when an organizational second brain starts coordinating work, it inherits the data-management requirements of a shared application. A database is often the most direct way to meet them. PostgreSQL being then my first choice for its maturity in both RDBMS and AI world as long as sovereignty advantages. This part of what we all call the vertical defensibility other and me included, talk about.

Could we skip all of this and simply give the model everything, in a larger context window?

Sometimes more context helps. If the task requires reasoning across an entire document, retrieving a few fragments may omit something essential. I would not claim that research has established a consensus against long context.

The LaRA benchmark, published at ICML 2025, found that the choice between retrieval and long-context processing depends on the model, task, context length and retrieval characteristics. An earlier EMNLP study found stronger average results for sufficiently resourced long-context processing on its benchmarks, with lower cost as an advantage of RAG. Neither establishes a universal winner. At the same time, Chroma’s context-rot report measured 18 models degrading as input length grows, often well before the window is full, so the window size is not a reliable guide to how much of it a model uses well.
What recent work does support is the need to manage context deliberately. Anthropic’s context-engineering guidance discusses selective retrieval, compaction and notes stored outside the active context. The Recursive Language Models paper, first published in December 2025, explores letting a model inspect and decompose external input programmatically. My understanding is that a larger context window is another capability to evaluate but it does not remove the need to select relevant information, track its origin or keep it current.

And even if a model could reliably process every document in the company, we would still have to decide which documents a particular user may access. More tokens do not define ownership, resolve conflicting updates or provide a recovery plan. Deeper thoughts on the matter here.

The shared state is where agents differ most from a retrieval demo, so let us follow one piece of it. An agent proposes a maintenance operation: rebuild an index on a busy table during tonight’s window. A person approves it. Another agent changes the plan. Execution times out. At each step, something must be recorded and something must be checked.

The proposal. The agent writes an operation record: the plan, a revision number starting at 1, and a state, awaiting approval. The plan can come from a runbook in Markdown; the operation itself is authoritative state, so it goes in the database.

The approval. A person approves revision 1. The approval row names the operation, the revision and the approver, and it is written by a trusted component that authenticated that person, never by the agent. An approved flag that the agent can set is not an approval.

The change. A second agent decides the window is too short and edits the plan. It read revision 1, so it updates on that condition:

UPDATE operation
   SET plan = $2,
       revision = revision + 1,
       state = 'awaiting_approval'
 WHERE id = $1
   AND revision = $3;  -- the revision this agent read

If someone changed the record in the meantime, the update touches zero rows and the agent has to read again instead of overwriting work it never saw. The edit also creates revision 2 and sends the operation back to awaiting approval: the person approved revision 1, not whatever the plan became afterwards. Depending on the operation, the same protection can come from a row lock or from serializable transactions with retry handling; PostgreSQL’s transaction isolation documentation explains why the isolation level matters.

The execution. Once revision 2 is approved, the execution service claims the operation in one statement, so two executors cannot both start it:

UPDATE operation o
   SET state = 'executing',
       attempt_id = $2
 WHERE o.id = $1
   AND o.state = 'approved'
   AND EXISTS (SELECT 1
                 FROM approval a
                WHERE a.operation_id = o.id
                  AND a.revision = o.revision)
RETURNING o.revision;

Zero rows means another executor got there first, or the approved revision is no longer the current one. The service then verifies its own authority to act and only then touches the target system. The attempt identifier is recorded before the action, so the action can be recognized later.

The timeout. The call to the target system times out. Maybe the rebuild never started, maybe it finished a second before the connection dropped. A database transaction cannot roll back an action performed outside it, and saving the workflow state does not make the external action atomic with the update that records its completion. So the state becomes requiring recovery, not failed and not completed. Reconciliation then asks the target: does the new index exist, and is it valid? Only then is the operation marked completed, or retried with the same attempt identifier so that a retry cannot apply the action twice. A blind retry is how an agent does the same thing twice.

The record. At the end, the database holds every revision, who approved which one, every attempt and its outcome. That history cannot be rebuilt from anything else, which is why it deserves a stricter recovery objective than the embeddings.

XState is one example of a library for expressing this logic: states, allowed transitions and guards that decide whether a transition may happen. It also saves and restores actor state, while the application supplies the persistent storage and the recovery design. A state machine helps express the process; its presence alone does not establish a security boundary. That said, I am quite fond of this project, and I think it is a significant piece of the puzzle. The responsibilities stay explicit:

The retrieval lab I describe below does not cover any of this. Safe autonomous writes and workflow recovery are a separate test, and the one I would want to see before an agent acts on production.

For a DBA, storing data is only the beginning. We also need to know how much we could lose and how long recovery would take: our RPO and RTO. PostgreSQL provides WAL and the mechanisms needed for replication and recovery. Point-in-time recovery still requires a usable base backup and the required WAL. High availability requires a configured deployment and a tested failover process. Installing PostgreSQL does not give us all of this automatically. A replica is also not a substitute for a backup: it can faithfully reproduce an unwanted change, including one an agent made. An embedding index may be reproducible, but rebuilding it takes time and can incur embedding costs.

A source document changes. Its embedding still represents the previous version. Retrieval continues to work, but it works against outdated information. CDC can tell a pipeline that source data changed. It does not regenerate the embedding by itself, and it does not make an external embedding service part of the source transaction. My approach would be to track the source revision and the embedding revision explicitly, generate the replacement, verify that it still corresponds to the intended source revision, then publish the new content and embedding together. Deletion needs the same attention: removing a source row does not remove copies from caches, downstream stores or conversation history. In Embedding Versioning with pgvector, I argued that batch re-embedding is technical debt and event-driven refresh a precondition. For data that changes and drives decisions, I still think so. The more precise rule is that the freshness the use case requires decides whether batch is enough. A documentation corpus refreshed every night may meet its target; operational data behind a time-sensitive decision will not. Declare the target, then measure the delay, including failed refreshes and deletion propagation.

PostgreSQL row-level security enforces row visibility in the engine, for every application and retrieval path that reads the same tables, as long as the application does not connect with a role that bypasses it. I built and measured this in AI agents on sensitive data: what PostgreSQL can enforce and what it can’t: synthetic banks, an agent answering questions over client documents, pgvector next to the data. Policies apply to the embedding tables as they do to the documents. The tenant is set inside every transaction, so it ends at commit and a pooled connection does not hand it to the next request, as long as nothing sets it at session level. But row visibility was one layer of six: column privileges, a register of what is sensitive, deterministic tokens, a gate on what leaves for the model provider, a separate vault server and the audit trail each carry part of the guarantee, and the guarantee ends where the labelling ends.
The database enforces the rule once the identity is set; it cannot tell whether the identity is the right one. The trusted service chooses the tenant from the authenticated user, and the agent must not be able to overwrite it, for example by running its own SQL on the same connection. On relevance, the measurements went against the usual fear: blunt redaction collapsed retrieval, while labelled tokens with entity-aware search matched raw text under the same search, the answers cited the expected documents as often, and zero detected sensitive values were sent to the model.

pgAudit records which statements ran and which objects they accessed; its optional row logging records a count, not the rows. It sees the database side of one step, not which question triggered it, what the model did with the rows, or whether the answer was right. For a RAG request I would also want a request identifier, the authenticated identity, the database role and whether it bypasses row-level security, the retrieved document identifiers and revisions, the policy version, the context actually sent, and the answer with its evaluation.
In the lab, I added Langfuse, self-hosted, to follow that path. Two lessons. The tracing layer is a database workload in its own right, PostgreSQL plus ClickHouse, to size, back up, secure and retain. And with evaluation scores next to the trace spans (a span is one recorded step, such as the search or the model call), a weak answer becomes a join: did the search miss the documents, or did the model ignore them?

It also stores every retrieved document once more. In the lab, a tool’s result was recorded when the tool returned, before the provider gate checked the next request, so a value the gate blocked could still reach the trace store. Tracing needs its own filtering boundary, applied before trace data leaves the application, rather than the assumption that the provider gate covers it. That variant is the next one I want to run.

Production RAG evaluation splits the retrieval step from the generation step. For retrieval, the classic measures, recall@k, precision@k and nDCG@k, still do the job (Understanding Confidence, Precision and nDCG defines them), and public benchmarks such as BEIR and MTEB still rank on nDCG@10. Their cost is a set of questions with the documents that should come back, written by someone who knows the data. Most tooling (Ragas, TruLens, DeepEval, the evaluators in Langfuse) adds measures scored by a second model acting as a judge: context precision and recall for the retrieval, faithfulness and answer relevance for the answer. Judges scale where human labels do not, but they make errors too, so I would calibrate them on a small labelled set. And each measure claims less than it seems: an answer that cites the expected documents has the right coverage, which is not proof that it is factually correct.
Two points matter for sensitive data. First, define the evaluation set per identity: the relevant documents that identity is permitted to retrieve. That avoids treating the exclusion of another customer’s data as a retrieval failure. Second, a judge model reads the question, the retrieved context and the answer, so it is one more place the data goes, like an external embedding service. Both belong in the same data-flow review as the model that answers. Measures computed from document identifiers need no judge at all.

Security testing needs its own checks. Does a request from tenant A ever return tenant B’s documents? Can a user change the retrieval identity? Do revoked permissions still allow access through a cache? Can retrieved content persuade an agent to use another tool with broader access? A high relevance score does not answer those questions. Unauthorized data can be extremely relevant to a query and still be forbidden. A mandatory access restriction is a requirement to satisfy; we can then work on preserving quality within it.

A production agent depends on more than retrieval. It needs credentials, tool permissions, monitoring, deployment procedures and recovery paths, and approval before a consequential action, as in the example above. This is shared work between application teams, database teams, infrastructure and security. I would not call every one of these problems a database problem. Model reliability, prompt injection and the safety of external actions need their own controls; I discussed where the trust boundary sits for tools in RAG, MCP, Skills, and why governance changes everything. Database experience nevertheless covers a substantial part of the foundation.
Anthropic’s AI-native SDLC playbook gives a concrete example of this broader work. It describes versioned artifacts such as intent.md, spec.md and plan.md, alongside review, approval gates and operational feedback, and it distinguishes advisory instructions in skills from controls enforced through executable mechanisms such as hooks. Markdown stays valuable inside the workflow; the process around it establishes who may approve a change, what can execute and where the authoritative record lives. It is a vendor’s implementation guidance, so I would evaluate its controls against the actual environment rather than treat every example as a production guarantee.

Readiness can be described through evidence: a restore that met the recovery objective, a failover test that included the CDC consumer, a tenant-isolation test, a measured refresh delay, a retrieval evaluation based on the intended users and data, and, for an agent that acts, a test of the timeout above. We also need to know where the data goes. Keeping PostgreSQL in Switzerland does not keep the context sent to an external model there: embedding providers, inference endpoints, judges, traces, backups and support access all belong in the assessment, and the EU AI Act and the revised Swiss data-protection law both expect us to know it. How to judge that evidence against targets an organization declared for itself, rather than against someone else’s checklist, is the subject of Deciding in the Storm.

I see a useful opportunity here for database teams. We already know how to ask about consistency, permissions, recovery and operational ownership. Agentic applications give us another reason to ask those questions early.
A personal second brain can work well with files, search and an embedded database as needed. As it becomes a shared application, separate what it holds, define the guarantees its users depend on and choose the storage and workflow mechanisms that provide them. Keep formats such as OKF where portability and reviewable knowledge help.

Larger context windows give the model more room to work. The application still needs a reliable way to retain, update and govern the information it uses.

Related articles: AI agents on sensitive data: what PostgreSQL can enforce and what it can’t; RAG, MCP, Skills, and why governance changes everything; Embedding Versioning with pgvector; Understanding Confidence, Precision and nDCG; the Swiss PGDay 2026 talk “The cost of security debt in PostgreSQL when implementing AI workflows”.

L’article AI problems are database problems est apparu en premier sur dbi Blog.

Every useful database content, document behind an internal AI agent names someone, a company, server names,…
A relationship manager might ask “what did this client complain about last quarter?”, an operations engineer might ask “what happened on this server last month?”, and the answer sits in notes, emails and incident tickets, CRMs and ERPs data, full of client names, staff names, IBANs, hostnames and IP addresses: exactly the text you would rather not send to a model.

Usually I see two answers for that. Keep AI away from that data, which in practice means there is no project. Or redact before sending: replace every name with [REDACTED] and send the rest. That one looks safe and fails quietly, because once every client is [REDACTED], the assistant can no longer tell one client from another because it is like joining two tables without a key. It answers, but without being relevant enough.

I built the layer in between, on PostgreSQL: row-level security, labels, the token columns and audit live in the database, the tokenizing and the gate in the application, the key and the mapping on a second server. Here is what it does, on a question from the lab (condensed from the run’s output):

question asked : What did Emmental Immobilien AG complain about regarding custody fees?
question sent  : What did CLIENT_43aa5d8366b5 complain about regarding custody fees?
model answer   : CLIENT_43aa5d8366b5 complained that the custody fees had increased without notice. [doc 10]
for a reviewer : Emmental Immobilien AG complained that the custody fees had increased without notice. [doc 10]

The model chose its own search, kept the token, answered correctly and cited its source. It never held the name. This is pseudonymization, not anonymization: the tokens are reversible by design (for everything downstream of the filter, only through a separate server), and the text around a token can still say a lot about someone. The goal is narrower and testable: no known sensitive value reaches the provider, and the answers stay as relevant as on raw text.

In my lab I take the example of a bank, because in Switzerland, sending client data covered by banking secrecy to an external API is a disclosure question under Article 47 of the Banking Act before it is a quality question, and FINMA expects banks to manage the data risks and third-party dependencies that come with AI. So the question is: can we build something secure enough to still use agentic capabilities? If it works in a sector as regulated as banking, most organizations can use it too. Retrieval techniques make governance possible; the other question is how far they go.
The lab has three synthetic banks, 240 clients, 18 relationship managers, 481 accounts, 30 servers and 1,521 free-text documents. It is small on purpose: big enough to measure, small enough to rebuild on a laptop and read every row. Every name in it is generated; where a generated company name turned out to match a firm in the Swiss commercial register, the generator swaps it, and any remaining resemblance is coincidental. This post walks through the data model, the vault that holds the tokens, the layers between the data and the model, a deliberate labelling gap and what catches it, the relevance measured with and without filtering, what a tracing tool ends up storing, and the query plans underneath.

The interesting part is not hiding the names. It is knowing precisely which data is sensitive: the labels are what stop the leak, the same labels are what give the relevance back, and a name that no label knows is exactly where the guarantee ends.

This demonstrates:

• Row-level security on the vectors, not only on the documents, with the tenant set per transaction.
• A label registry and a coverage query that fails toward a finding when a sensitive column is missing.
• Deterministic tokens computed with a keyed hash on a separate PostgreSQL server, which is the only way back for everything downstream of the filter.
• An egress gate that scans every request to the model, logs a hash of it, and blocks on a single finding.
• Relevance kept: with the same search and the same tools, answers on tokens are as good as answers on raw text.

Lab Environment Details

Bank server:
PostgreSQL 18.3, pgvector 0.8.6, pgAudit 18.0, postgresql_anonymizer 3.2.2.
Vault server:
PostgreSQL 18.3 with pgcrypto.
Embeddings computed locally on CPU:
dense voyage-4-nano at 1,024 dimensions, sparse SPLADE (prithivida/Splade_PP_en_v1) through fastembed.
Agent:
OpenAI gpt-6-luna through Chat Completions with function tools.
Tracing:
Langfuse 4.50 self-hosted from its official Docker Compose file (ClickHouse 25.12, PostgreSQL 17, Redis 7, MinIO), Python SDK 4.16. All in Docker on WSL2, in a 6 GB VM.

The lab is public and available with a runbook that replays every output below: lab 16 in pgvector_RAG_search_lab.
Building it takes about 40 minutes of embeddings on a CPU, and replaying every step about an hour and a half; the dry runs need no API key.

Two servers, three schemas. The bank server holds the business data, its filtered copies and the governance tables. The vault server holds only the key and the token mapping. No foreign key, foreign server or dblink crosses between them.

The bank schema: business data, its filtered copies, the mention index and the versioned embeddings. Each sensitive structured column sits next to its token column; the free text has redacted and tokenized copies (title copies omitted here).

Each document is stored three times, raw, redacted and tokenized. That is not how you would run production; it is how you measure what each choice costs, on the same rows, with the same model:

CREATE TABLE bank.documents (
    doc_id          int PRIMARY KEY,
    bank_id         text NOT NULL REFERENCES bank.banks,
    doc_type        text NOT NULL CHECK (doc_type IN ('advisor_note', 'incident', 'email')),
    title           text NOT NULL,
    body            text NOT NULL,
    created_at      timestamptz NOT NULL,
    title_redacted  text,
    body_redacted   text,
    title_tokenized text,
    body_tokenized  text,
    key_version     int
);

Embeddings are versioned: every row says which model, which text state and which vault key produced it. Why that matters, and the event-driven pipeline that keeps vectors current, is the subject of my embedding versioning post and its lab. A partial unique index allows at most one active version per state, so a new set can be built next to the old one and switched when it is ready:

CREATE UNIQUE INDEX embedding_versions_one_active ON bank.embedding_versions (state) WHERE is_active;

-- shortened: the repository version adds a hash of the embedded text
CREATE TABLE bank.embeddings (
    version_id int  NOT NULL REFERENCES bank.embedding_versions,
    doc_id     int  NOT NULL REFERENCES bank.documents,
    bank_id    text NOT NULL,
    dense      vector(1024) NOT NULL,
    sparse     sparsevec(30522) NOT NULL,
    PRIMARY KEY (version_id, doc_id)
);
CREATE INDEX embeddings_dense_hnsw ON bank.embeddings USING hnsw (dense vector_cosine_ops);
CREATE INDEX ON bank.embeddings (version_id, bank_id);

The bank_id on embeddings is deliberate. The vector table is the one people forget to protect: it gets the same tenant column, and the same row-level security policy, as the documents.

The governance schema gov is plain tables. A registry says which column is sensitive and where its token goes, and an egress log records every request that leaves, with a hash and the size of the payload and what the scanner found, never the payload itself. The gateway may read the log, insert into it and set each request’s outcome, nothing else:

CREATE TABLE gov.sensitive_columns (
    table_name   text NOT NULL,
    column_name  text NOT NULL,
    category     text NOT NULL,   -- CLIENT, PERSON, EMAIL, IBAN, HOST, IP, FREE_TEXT
    token_column text,            -- where the deterministic token is written
    PRIMARY KEY (table_name, column_name)
);

GRANT SELECT, INSERT ON gov.egress_log TO gateway;
GRANT UPDATE (outcome) ON gov.egress_log TO gateway;   -- the rest of the log is append-only

The key, and the mapping from a token back to its value, are what turn a token into a name. Anyone who only ever sees tokens (the model, the agent’s role, a tracing store) has no other way back, and a reviewer gets names only through it, so they live on a second PostgreSQL server, with its own roles and credentials, and no postgres_fdw or dblink on the bank side. The two containers also sit on separate Docker networks. On one laptop that is as far as it goes: the bank’s superuser can run programs on its server, and the vault’s port is still reachable through the Docker host. In production the vault belongs on its own host, behind a firewall that only lets the gateway in.

One honest limit of this lab’s layout: the bank tables keep each token next to its raw value (client_token beside client_name), because the pipeline needs both to filter the text. So the pipeline role and the bank’s administrators can map tokens back without the vault; what keeps the agent out is column privileges, below. One more route stays open in the lab: the agent’s role can read the raw-text embeddings kept for the measurement, and a sparse vector is indexed by words, so it partly exposes the words it was built from, names included. A stricter design moves the token columns out of the raw tables.

The vault server holds only the key and the token mapping. tokenizer can compute tokens but read neither table; reidentifier reads the mapping, never the key (the vault’s admin, a superuser in the lab, reads both).

Nobody reads the key, not even the role that creates tokens. Tokens are computed inside the vault by a SECURITY DEFINER function, which runs with its owner’s rights (in the lab the owner is the vault’s admin, a superuser, which a production setup would replace with a dedicated non-superuser owner), with a pinned search_path:

-- shortened: the repository version also raises when no key is active
CREATE FUNCTION vault.tokenize(p_category text, p_values text[])
RETURNS TABLE (value text, token text)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = vault, public, pg_temp
AS $$
#variable_conflict use_column
DECLARE
    k  bytea;
    kv int;
BEGIN
    SELECT secret, key_version INTO k, kv FROM vault.keys WHERE active;
    RETURN QUERY
    WITH src AS (
        SELECT DISTINCT v AS value FROM unnest(p_values) AS v WHERE v IS NOT NULL
    ), tok AS (
        SELECT s.value,
               upper(p_category) || '_' ||
               substr(encode(hmac(convert_to(upper(p_category) || ':' || lower(btrim(s.value)), 'UTF8'),
                                  k, 'sha256'), 'hex'), 1, 12) AS token
        FROM src s
    ), ins AS (
        INSERT INTO vault.mapping (token, key_version, category, value)
        SELECT DISTINCT ON (t.token) t.token, kv, upper(p_category), t.value
        FROM tok t ORDER BY t.token, t.value COLLATE "C"
        ON CONFLICT DO NOTHING
    )
    SELECT t.value, t.token FROM tok t;
END;
$$;

Functions are executable by PUBLIC by default, so the vault script revokes that before granting anything, and the public schema in that search_path is not writable by either role (the default since PostgreSQL 15, and revoked explicitly in the script):

REVOKE ALL ON ALL TABLES    IN SCHEMA vault FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA vault FROM PUBLIC;
GRANT USAGE ON SCHEMA vault TO tokenizer, reidentifier;
GRANT EXECUTE ON FUNCTION vault.tokenize(text, text[]), vault.active_key_version() TO tokenizer;
GRANT SELECT ON vault.mapping TO reidentifier;
GRANT EXECUTE ON FUNCTION vault.active_key_version() TO reidentifier;

From the run, by role:

tokenizer:    SELECT * FROM vault.keys;
              ERROR:  permission denied for table keys
tokenizer:    SELECT * FROM vault.tokenize('client', ARRAY['Brenta Kontor AG', 'brenta kontor ag ']);
                     value       |        token
              -------------------+---------------------
               brenta kontor ag  | CLIENT_7119873645a3
               Brenta Kontor AG  | CLIENT_7119873645a3
reidentifier: SELECT * FROM vault.keys;
              ERROR:  permission denied for table keys

Three things the token is, and one it is not. It is the category plus the first 12 hex characters of an HMAC-SHA256 of the normalized value, a keyed hash nobody can reproduce without the key; whoever holds the tokenizer credential can still submit a guess and compare tokens (and the guess lands in the mapping), which is why that credential stays with the gateway. It is 48 bits, so by the birthday bound a collision within one category becomes a 1% risk around 2.4 million values; the largest category here has 481. And this version does not detect a collision: ON CONFLICT DO NOTHING would skip it silently, where a production version should raise.

What it is not is entity resolution. The hash input is category and normalized string, with no tenant and no entity ID, so two different clients with the same name would share a token, and the same name in two banks gets the same token. The synthetic data has no such pair, which is why the results below are not affected, but a real CRM has them. A production version would tokenize a tenant-scoped canonical ID, and map aliases to it explicitly. The lab does that in one place only: a server’s hostname and its full domain name share one token.

    hostname    |             fqdn              |    host_token     |    fqdn_token
----------------+-------------------------------+-------------------+-------------------
 alb-pg-prd-01  | alb-pg-prd-01.alder.internal  | HOST_f774b8916acd | HOST_f774b8916acd

Where each layer sits: four on the bank server, the gate in the gateway process, the key and mapping on the vault server. The dotted paths go to the vault: tokens computed once and written back to the bank, and re-identification, used only for a reviewer.

One question end to end: the name never leaves the gateway and the model sees tokens only. The last two steps run only when a reviewer needs the real name; otherwise the answer stays in tokens.

The agent never writes SQL. It gets three tools, search_documents, client_profile and host_documents, each a plain SECURITY INVOKER function that runs as app_agent, so row-level security and column privileges apply inside it. In the terms of my RAG, MCP, Skills post, this is an agent with tool access, but every tool is a retrieval function under the database’s rules, so the DBA keeps the control, MCP and skills still being weaker on the governance aspects. app_agent has no privilege on any raw sensitive column (it reads tokens and attributes that do not identify anyone on their own, such as currency, domicile or balance), so a tool cannot return a client’s name even by mistake. Those attributes do reach the provider, tied to a token, which is part of why this is pseudonymization. (It can read the raw-text embeddings, which exist here only for the measurement; a production setup would not keep them.)

SELECT has_column_privilege('app_agent', 'bank.clients', 'client_name',  'SELECT') AS agent_reads_client_name,
       has_column_privilege('app_agent', 'bank.clients', 'client_token', 'SELECT') AS agent_reads_client_token;

 agent_reads_client_name | agent_reads_client_token
-------------------------+--------------------------
 f                       | t

Where the trust sits matters as much as the layers. The guarantee holds for what the agent can reach through those tools and that role. The gateway is the trusted part: it chooses the tenant, it holds the tokenizer and reidentifier credentials, and it decides what goes to the provider. It must derive the tenant from the authenticated user, never from an argument the model wrote, because row-level security enforces whatever bank the session declares without asking whether the session was entitled to declare it. A compromised gateway is outside what this design protects.

The database enforces these by itself, on every query, whatever the application does: row-level security on the documents, the mention index and the vectors; column privileges that keep raw values away from the agent’s role; the revoked defaults and the SECURITY DEFINER function that keep the key unreadable in the vault; and pgAudit’s record of what the agent executed. It also holds the label catalog and reports what is missing from it through the coverage view, though a report only helps when someone reads it.

It does not do the rest, and I won’t pretend it does. The gate that scans every outbound request is a short Python function in the gateway. The tenant is chosen by the application. The labels are only as complete as the people who maintain them, and a name that is in no table passes. The tracing store and the model provider sit outside the database. And the vault is isolated by separate servers and networks (in the lab, separate containers on one host), not by SQL.

So why build it on PostgreSQL rather than around a separate vector store? Three reasons. First, one access model: the vectors live in the same engine as the rows they came from, under the same policy and the same privileges, so there is no second copy of every rule to keep in sync. Second, every database-side layer is core PostgreSQL or an extension, not a new product: pgvector for dense and sparse vectors, postgresql_anonymizer for the labels, pgcrypto for the keyed hash, pgAudit for the audit, all under the PostgreSQL License, maintained in the open and readable by the people who have to trust them. Third, everything except the chat model runs on your own machines: the two PostgreSQL servers, the embedding models (Apache 2.0, on CPU), and even the observability, since Langfuse (MIT, apart from its enterprise edition, the one its documentation lists retention under) runs on PostgreSQL and ClickHouse (Apache 2.0), plus a queue and an object store with their own licences. At run time the only external call in the lab is the chat model, and it is the call the gate scans, and in the governed setup blocks. For a bank that has to manage the third-party dependencies FINMA points to, that is what sovereignty means here: the model can move on premises without changing the data, the labels, the vault or the gate.

A hex string like CLIENT_43aa5d8366b5 means nothing to a semantic model, so dense search barely uses it. But the labels give me something better than semantics: while filtering the text, the tokenizer records which document mentions which entity, in bank.document_mentions. When a question names an entity, the search starts with the documents that mention it, ranks those by meaning, then adds the rest:

-- excerpt of bank.retrieve()
ent AS (
    -- documents mentioning every entity the question names
    SELECT m.doc_id FROM bank.document_mentions m
    WHERE p_method = 'entity' AND cardinality(p_tokens) > 0 AND m.token = ANY(p_tokens)
    GROUP BY m.doc_id HAVING count(DISTINCT m.token) = cardinality(p_tokens)
), ent_scored AS (
    SELECT e.doc_id,
           1.0 + 1.0 / (60 + row_number() OVER (ORDER BY e.dense <=> p_dense))
               + 1.0 / (60 + row_number() OVER (ORDER BY e.sparse <#> p_sparse)) AS score
    FROM bank.embeddings e JOIN v USING (version_id)
    WHERE e.doc_id IN (SELECT doc_id FROM ent)
)

Inside the entity’s documents the order is reciprocal rank fusion of dense and sparse, the same as everywhere else. The 1.0 + puts them ahead of everything that does not mention the entity. It is a choice, not a constant: if your questions often name an entity only in passing, you would add a boost instead of ranking first. The mention index is a plain table with a B-tree on token, under the same row-level security as the rest. Raw text can use it too, which matters for a fair comparison below. Run on its own, as app_agent with bank_a set (inside the function it is a CTE, and the function’s own plan is a single opaque function scan):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.doc_id FROM bank.document_mentions m
WHERE m.token = ANY(ARRAY['CLIENT_43aa5d8366b5'])
GROUP BY m.doc_id HAVING count(DISTINCT m.token) = 1;

 GroupAggregate (actual time=0.072..0.075 rows=6.00 loops=1)
   Group Key: doc_id
   Filter: (count(DISTINCT token) = 1)
   ->  Sort (actual time=0.066..0.067 rows=6.00 loops=1)
         ->  Bitmap Heap Scan on document_mentions m (actual time=0.042..0.044 rows=6.00 loops=1)
               Recheck Cond: (token = ANY ('{CLIENT_43aa5d8366b5}'::text[]))
               Filter: (bank_id = current_setting('app.bank_id'::text, true))
               ->  Bitmap Index Scan on document_mentions_token_idx (actual time=0.025..0.025 rows=6.00 loops=1)
                     Index Cond: (token = ANY ('{CLIENT_43aa5d8366b5}'::text[]))
 Execution Time: 0.185 ms

(Shortened.) The token lookup is one index probe and six rows. Note where the row-level security predicate lands: here it is a Filter after the lookup, because the B-tree is on token alone. Keep that in mind for the vector plan below.

Every business table that carries bank_id gets the same policy, the vector table included:

ALTER TABLE bank.embeddings ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON bank.embeddings
    USING (bank_id = current_setting('app.bank_id', true));

The bank is set per transaction with set_config(..., true), the function form of SET LOCAL, so a transaction-mode pooler cannot hand one bank’s setting to the next request. As app_agent:

bank=> SELECT count(*) AS documents_without_tenant FROM bank.documents;
 documents_without_tenant
--------------------------
                        0

bank=> BEGIN;
bank=> SELECT set_config('app.bank_id', 'bank_a', true);
bank=> SELECT bank_id, count(*) AS documents FROM bank.documents GROUP BY bank_id;
 bank_id | documents
---------+-----------
 bank_a  |       508
bank=> COMMIT;

bank=> SELECT count(*) AS documents_after_commit FROM bank.documents;
 documents_after_commit
------------------------
                      0

Be precise about why both counts are zero, because they are not the same case. On PostgreSQL 18.3, current_setting('app.bank_id', true) returns NULL when the setting was never defined in the session, and an empty string once an earlier transaction has set it locally: the custom setting now exists, with an empty value. Neither matches a bank_id, so a session that forgets its bank gets nothing. If you would rather have one case, NULLIF(current_setting('app.bank_id', true), '') in the policy makes both NULL. And a transaction-local value only overrides: if something set the parameter at session level earlier, that value is back after the commit, which is why the pooling argument assumes connections that never set it at session level.

The raw client name is refused before row-level security even matters: SELECT client_name FROM bank.clients as app_agent fails with permission denied. Neither role owns the tables; if your application connects as the table owner, add FORCE ROW LEVEL SECURITY, or the owner sees every row. The pipeline role, gateway, has BYPASSRLS because it serves all three banks, and the agent never gets near it.

The registry says what is sensitive; a procedure turns it into security labels, with postgresql_anonymizer as the label provider, so the catalog (pg_seclabels) is the one place to ask whether a column is labelled. The coverage view asks it for every text column of the business tables:

-- shortened: the repository version also skips three technical tables and the
-- derived columns (tokens, filtered copies, text hashes)
CREATE VIEW gov.label_coverage AS
SELECT c.table_name, c.column_name, sl.label, sc.category,
       (sl.label IS NOT NULL) AS labelled
FROM information_schema.columns c
LEFT JOIN pg_seclabels sl
       ON sl.provider = 'anon' AND sl.objtype = 'column'
      AND sl.objoid = format('bank.%I', c.table_name)::regclass
      AND sl.objsubid = c.ordinal_position
LEFT JOIN gov.sensitive_columns sc
       ON sc.table_name = c.table_name AND sc.column_name = c.column_name
WHERE c.table_schema = 'bank' AND c.data_type = 'text'
  AND c.column_name NOT IN ('bank_id', 'doc_type', 'client_type', 'currency',
                            'role', 'environment', 'domicile', 'category', 'token');

The choice that matters is the direction of the list: it excludes what is known to be harmless, so a new text column shows up as a gap until someone labels it or excludes it on purpose. It fails toward a finding, not toward silence. It only sees text columns; a sensitive value stored as inet, date or jsonb needs its own rule. I left one sensitive column out on purpose, the clients’ phone numbers:

bank=# SELECT table_name, column_name, category, labelled FROM gov.label_coverage ORDER BY labelled, table_name;
      table_name       |  column_name  | category  | labelled
-----------------------+---------------+-----------+----------
 clients               | contact_phone |           | f
 accounts              | iban          | IBAN      | t
 clients               | client_name   | CLIENT    | t
 ...
(10 rows)

Tokenizing then replaces every labelled value in the free text, using a dictionary of every value the labelled columns hold, matched as whole words ignoring case, longest first, with the grouped spelling of each IBAN added. One caveat before the numbers: in this lab the dictionary and the scanner read hand-written lists of columns that match the registry, rather than reading the registry itself. A production version should derive both from it.

Building the governed corpus: the registry drives the labels and the coverage check, the vault computes the tokens, and one dictionary pass filters the text, fills the mention index and feeds the local embedding models.

$ python python/scan_corpus.py
raw         7171 values in 1521 documents: CLIENT 3024, PERSON 1833, EMAIL 960, HOST 630, IBAN 480, PHONE 154, IP 90
redacted     154 values in  154 documents: PHONE 154
tokenized    154 values in  154 documents: PHONE 154

The raw corpus carries 7,171 detected occurrences of sensitive values. After tokenization what is left is exactly the gap: 154 phone numbers from the column nobody labelled. Now the governed agent, asked about a client whose note carries that phone number, in a dry run that builds the real request and stops before it leaves:

$ python python/agent.py --dry-run --bank bank_a \
    "What did Emmental Immobilien AG complain about regarding custody fees?"
question sent : What did CLIENT_43aa5d8366b5 complain about regarding custody fees?
  [tool] search_documents returned:
    doc 10 advisor_note: PERSON_65a94b3e0336 met CLIENT_43aa5d8366b5. The client complained that the custody fees rose without notice. Next step agreed with CLIENT_43aa5d8366b
    doc 12 advisor_note: PERSON_65a94b3e0336 met CLIENT_43aa5d8366b5. The client complained that the custody fees rose without notice. Next step agreed with CLIENT_43aa5d8366b
    doc 11 email: From EMAIL_288e9f2f11a1. Dear client, following our call, CLIENT_43aa5d8366b5 complained that the custody fees rose without notice. Please confirm the
    doc 14 email: From EMAIL_288e9f2f11a1. Dear client, following our call, CLIENT_43aa5d8366b5 requested an ESG screening of current equity holdings. Please confirm th
    doc 13 advisor_note: PERSON_65a94b3e0336 met CLIENT_43aa5d8366b5. The client requested an ESG screening of current equity holdings. Next step agreed with CLIENT_43aa5d8366
  [egress] 3729 chars, filtering=tokenized, scanner hits=1 ['PHONE']  BLOCKED
  stopped at the egress gate: 1 sensitive value(s) in outbound payload: ['PHONE']

Every retrieved document is the right client and every name, email and account is a token, but doc 12 still carries the phone number. The gate stopped the request: that is what prevented the transmission. It caught the number because the scanner’s own list includes the phone column (I left it out of the registry on purpose, so I knew), and a Swiss phone-number pattern would have matched it anyway. Then the label fixed the filtering:

bank=# INSERT INTO gov.sensitive_columns VALUES ('clients', 'contact_phone', 'PHONE', 'phone_token');
bank=# CALL gov.apply_labels();
$ python python/tokenize_corpus.py
...
$ python python/agent.py --dry-run --bank bank_a "What did Emmental Immobilien AG complain about regarding custody fees?"
  ... (same question sent, same five documents)
  [egress] 3731 chars, filtering=tokenized, scanner hits=0
  payload cleared the gate (dry run: not sent)

Same documents, two characters longer (a 16-character phone number became an 18-character token), zero detected values. The gate stops what it recognizes; the label makes the filter remove it before it ever gets there. Both are needed, and neither knows a name that is in no table: nine documents each name a lawyer who exists in no column (Hugo Vogel in one, Selina Python in another), and those nine pass. Deterministic filtering is exactly as good as your labelling. Named-entity recognition can be added as a second net, but it puts a probabilistic step back into the path of a guarantee; I would rather know the ceiling and work on coverage.

36 labelled questions, 30 naming a client or a server and 6 generic, run against the raw, redacted and tokenized embeddings as app_agent with its bank set:

$ python python/measure.py
state      method  set       recall@5  nDCG@5  recall@10  nDCG@10
raw        hybrid  entity       0.956   0.953      1.000    0.973
raw        entity  entity       1.000   0.991      1.000    0.991
redacted   hybrid  entity       0.192   0.185      0.408    0.284
tokenized  dense   entity       0.431   0.447      0.625    0.541
tokenized  sparse  entity       0.822   0.827      0.897    0.860
tokenized  hybrid  entity       0.650   0.670      0.775    0.731
tokenized  entity  entity       1.000   0.990      1.000    0.990

(Shortened; the six generic questions score 1.000 everywhere, which says they are too easy. measure.py prints the full table.) Redaction destroys entity questions, 0.192, because the documents no longer say who. Opaque tokens alone recover part of it: the dense model barely uses a hex string (0.431), the sparse model does (0.822). Entity-aware search over the mention index brings tokenized retrieval to 1.000, the same as raw with the same search. Redaction cannot use it: a redacted question names no one.

Retrieval is not what a relationship manager reads, so the model answered all 36 questions, citing documents as [doc N], with deterministic scoring and no model judging another model. To isolate the cost of the tokens, the two setups compared here differ in the text and almost nothing else: both use the same entity-aware search, under the same row-level security and candidate limits, and the same single search tool. One reads raw text, the other tokens. (Two small differences remain: the entity is found by its token on one side and by its name on the other, and the raw side’s requests are logged but never blocked by the gate; none was blocked on either side in this run.) Both ran together on 3 October:

$ python python/evaluate_answers.py --label fair2 --modes raw_entity,tokenized_search
mode       set      cited rec cited prec context rec expl. entity blocked
raw_entity entity       0.722      0.989       1.000          1.0       0
tokenized_search entity       0.719      0.993       1.000          1.0       0
egress: {'tokenized_search': {'sent': 74, 'values_sent': 0, ...}, 'raw_entity': {'sent': 73, 'values_sent': 871, ...}}

(Shortened to the entity questions; the generic ones score 1.000 in both.) Both setups had every expected document in front of the model (context recall 1.000), and both cited the same share of them, 0.722 against 0.719. With the same search, the tokens cost nothing measurable. What changed is what left: 871 detected occurrences of sensitive values for the raw setup, none for the tokenized one.

For reference, the naive setup (raw text, plain hybrid search across all three banks, filtered to one bank afterwards) put fewer expected documents in front of the model, context recall 0.981, and sent 1,134 detected occurrences in 75 requests, in a separate run the same day. The naive setup differs from the entity-aware ones in two ways at once, the search method and searching all banks before filtering, so these runs cannot say which one accounts for the gap; what it does show is that the tokens are not the cost.

Cited recall counts expected documents cited, which is document coverage, not answer completeness: when three notes repeat the same complaint, citing one of them can be a complete answer. So the honest claim is narrower than “better answers”: on this corpus, filtering keeps the answers grounded in the right documents while nothing known leaves.

The egress log tells me what left and how big it was, and pgAudit tells me what app_agent executed. Neither tells the story of one question: what was asked, which documents came back, under which role, what the model answered, and whether it was right. So I traced every question with Langfuse, the open-source tracing and evaluation tool for LLM applications that ClickHouse acquired in January 2026, self-hosted next to the lab. Look first at what it runs on: PostgreSQL for its transactional data, ClickHouse for traces and scores, Redis as a queue, S3-compatible storage for the raw events. AI observability is a database workload.

Langfuse 4.50, self-hosted: one trace per question, marked off for the naive setup and tokenized for the governed one in the metadata. The naive rows show client names in the input and output columns; the governed rows show tokens.

Each retrieval step records the database role, whether that role bypasses row-level security, and the document IDs, so the role question becomes SQL on the ClickHouse table that holds the spans, here on the traced run of 2 October:

clickhouse> SELECT tags[1] AS mode,
                   metadata_values[indexOf(metadata_names, 'db_role')]    AS db_role,
                   metadata_values[indexOf(metadata_names, 'bypass_rls')] AS bypass_rls,
                   metadata_values[indexOf(metadata_names, 'doc_ids')]    AS doc_ids
            FROM events_full FINAL
            WHERE session_id = 'langfuse' AND name = 'search_documents' AND input LIKE '%succession%'
            ORDER BY mode, start_time LIMIT 1 BY mode;
   ┌─mode──────┬─db_role───┬─bypass_rls─┬─doc_ids────────────────┐
1. │ off       │ gateway   │ true       │ [18, 19, 206, 100, 22] │
2. │ tokenized │ app_agent │ false      │ [18, 19, 16, 17, 292]  │
   └───────────┴───────────┴────────────┴────────────────────────┘

The naive search ran as a role that bypasses row-level security and stayed inside its bank only because the application’s query filtered on it. And because the evaluation’s scores sit next to the spans, a quality question becomes a join: for the governed answers that cited fewer expected documents, what had the search returned? For one of the two worst in that run, a question about replication lag on a server, the expected documents were 948 to 951; the search returned all four at the top, and the model cited one. That is not a retrieval problem, and without the trace next to the score I would have gone tuning the index.

Then I pointed the egress scanner at the trace store itself, for the three-setup run of 3 October:

$ python python/scan_traces.py --label fair
ClickHouse, table events_full, session fair
  mode       traces  spans  spans with values  sensitive values
  off            36    150                144              2336
  raw_entity     36    144                138              1850
  tokenized      36    170                  0                 0
object storage, bucket langfuse/events/otel, session fair
  (same counts, shortened)

Tracing records what the application sends and receives; that is its job. On the naive path the trace store ends up with 2,336 detected occurrences for questions that sent 1,134 to the provider: every retrieved document is stored once more as the search result, on top of the copies inside each model call, plus the questions and answers. And all of it twice, in ClickHouse and in object storage. On the governed path both hold zero detected values, and the reason is precise: the search result is recorded when the tool returns, before the gate scans the next request, so the traces are clean only because the tools return tokens. While the phone gap was open, doc 12’s number would have reached the trace store even though the gate blocked the request.

The naive setup, as Langfuse stored it (2 October): the search step’s output holds the raw text, with the client’s name and the advisor’s name and email in clear, and an IBAN and a phone number too, masked here for publication.

The governed setup, same kind of step: every name in the stored search result is a token.

So the observability layer is one more copy of the data. In the Langfuse 4.50 I ran, the table holding the spans carries a full-text index on every span’s input and output, so questions, retrieved documents and answers are searchable word by word. On a self-hosted instance that copy is kept indefinitely unless you configure retention, a setting its documentation lists only under the Enterprise Edition. The SDK has masking hooks that run before trace data leaves the application; that is where I would plug the gate’s scanner, so the trace store can never hold more than the provider was allowed to see. I have not run that variant yet.

The dense path as app_agent with bank_a set, using a stored vector as the query. The query has no bank predicate:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT e.doc_id FROM bank.embeddings e
JOIN bank.embedding_versions v USING (version_id)
WHERE v.state = 'tokenized' AND v.is_active
ORDER BY e.dense <=> :'qd'::vector LIMIT 10;

 Limit (actual time=1.936..1.938 rows=10.00 loops=1)
   Buffers: shared hit=1762
   ->  Sort (actual time=1.935..1.936 rows=10.00 loops=1)
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Nested Loop (actual time=0.051..1.850 rows=508.00 loops=1)
               Buffers: shared hit=1759
               ->  Index Scan using embedding_versions_one_active on embedding_versions v (actual time=0.006..0.006 rows=1.00 loops=1)
                     Index Cond: (state = 'tokenized'::text)
               ->  Index Scan using embeddings_version_id_bank_id_idx on embeddings e (actual time=0.019..0.144 rows=508.00 loops=1)
                     Index Cond: ((version_id = v.version_id) AND (bank_id = current_setting('app.bank_id'::text, true)))
                     Buffers: shared hit=64
 Execution Time: 2.234 ms

(Shortened: sort key, index searches and planning lines removed.)

Reading it from the bottom: the active version comes from the partial unique index, then an index scan on (version_id, bank_id) returns the 508 rows of one bank and one version in 64 buffers, and a top-N heapsort scores all of them exactly and keeps ten. At this size the planner does not use the HNSW index at all.

Two details are worth the read. The bank_id = current_setting(...) condition is not in the query: it comes from the policy, and because the index is on (version_id, bank_id), the planner used it as an index condition next to the version. On the mention index above, with no bank_id in the B-tree, the same predicate was a filter after the lookup: where row-level security lands in a plan depends on your indexes. And of the 1,762 buffers, the index scan accounts for 64; the rest is reading the vectors back. pgvector stores vector with the EXTERNAL storage mode, and at about 4 KB each (1,024 floats plus a small header) the dense vectors live out of line: 48 MB of TOAST against 8 MB of heap, for 9,126 rows across the six embedding versions the table holds. The sparse vectors average 773 bytes and stay inline, and the same plan on the sparse path reads 66 buffers. Over five repeated runs the dense path took 1.9 to 4.5 ms, the sparse path under 1 ms, and the hybrid function 5 to 14 ms.

With much larger tables per bank the planner may switch to the HNSW index, and a plain HNSW scan stops after ef_search candidates, some of which row-level security and the version filter then remove, since one HNSW index spans every version. That is why bank.retrieve() carries SET hnsw.iterative_scan = strict_order and SET hnsw.ef_search = 100 in its definition: the index keeps scanning until enough rows pass the filter or its configured scan limits are reached, and the settings cannot leak into the caller’s session. I did not measure where that switch happens.

Underneath, there is nothing exotic. Tenant, version and entity are predicates on ordinary indexes, the vault is another PostgreSQL server with revoked defaults, the gate is a short function writing to a table, and the vectors are TOASTed values like any other. Every layer is something a DBA already knows how to operate, back up and audit.

Everything above was captured from runs of the lab on 1, 2 and 3 October 2026, command by command, and the runbook in the repository replays it, including the two comparison runs (step 19). The data is synthetic and generated with a fixed seed; vault tokens differ per vault (different hex, same behaviour). Embeddings ran on a CPU, between about 0.7 and 3.5 documents per second depending on the run, which is why the corpus is 1,521 documents and not a million.

The question set is small and generous: each entity question names one entity, and the generic ones are too easy. An outside review of an earlier run found questions whose wording matched only one of the ways their documents phrase the topic, which rewarded a correct “nothing found”; I fixed the wording and every number above uses the corrected set. Answers come from a model and vary between runs, which is why the compared setups were always run together. These numbers show the mechanism on this corpus; they are not a production benchmark.

“Zero” in this post always means zero detected occurrences: zero matches against the raw values of the bank’s sensitive columns and a few patterns, which a name in no table escapes. The scanner reads the payload with accents intact, so a name like Müller is matched too, though the lab data itself is plain ASCII. An outcome of sent in the egress log means the provider accepted the request; a request logged as failed may still have reached it before the error, so the log proves what was sent, not everything that was attempted. Key rotation is scripted but I have not run it end to end, and re-tokenizing overwrites the tokenized text in place, so older vector versions are not a full rollback. The agent ran on gpt-6-luna with reasoning turned off: in my runs, Chat Completions refused function tools for that model unless it was.

I often hear that you cannot keep sensitive data out of an AI pipeline and still get useful answers. On this lab you can, with one condition: labelling. Row-level security on the vectors decides which rows a session sees (which, as I showed in my Swiss PGDay talk, also improves precision), the labels decide what is sensitive, deterministic tokens replace it the same way every time, the gate proves what left, and the vault keeps the way back, for everything downstream of the filter, on another server. With the same search and tools, the answers on tokens were as good as the answers on raw text, and nothing known left. And the tool you add to watch all of it becomes a copy of the data unless what it records is filtered too.

This transfers to any assistant over CRM notes, a ticketing tool or a CMDB, wherever the useful text names people and systems, and it does not depend on which model you use or where it runs. Its weak points are said above: names that live in no table, a tracing store that sits outside the gate unless you put it behind, and a token that matches strings, not identities. It also makes the token mapping sensitive in its own, a secret with a required life cycle somehow.
A stale mapping increases the risk of a leak; a rotated one has to keep its history, for traceability and for questions that span a period with several mapping versions. How long to keep which version, and who may read it, is a question the business has to answer, and the real answer is probably a dynamic mapping system. This lab shows that the approach is possible, not how to run it in production.

Agentic projects put pressure on governance at a level organizations have never known, and it is understandable to feel overwhelmed, even afraid. To me this is an engineering problem, and we already hold most of the keys; the challenge is that the storm we are living through makes it hard to see clearly. Observability and open source will prevail !

L’article AI agents on sensitive data: what PostgreSQL can enforce, and what it can’t est apparu en premier sur dbi Blog.

What a great and interesting day I could spent today at the SOUG Day which took place at the SIX ConventionPoint in Zurich. Thanks to the whole SOUG team for the great organization and to all the speakers for the interesting sessions.

In his presentation, Ashish definitively confirmed that the new driver is AI. We discuss about the roadmap and latest innovations in Oracle AI database.
Trusted AI is a very important case, as we need to have trust in the AI application to access the data, and this is not easy to accept.
Deep data security uniquely enables enterprises to declare flexible and powerful end-user specific data privacy rules in the database.

Extreme performance subject was also highlighted. Next-generation intelligent Data Architecture, with Exadata evolution, is very important.
Exadata Exascale, new modern storage architecture is the future and will certainly replace ASM. With Exascale, storage management tasks are moved directly onto the storage servers. It is the next generation storage.
We also addressed Exadata system software new features. 26.2 release has just been announced recently.

The next big part is High availability and scalability. 26ai brings a lot in Maximum Availability Architecture (MAA) features. The great example is the how faster database role transitions in Oracle Data Guard now is. Comparing 19c and 26ai, for small configuration, the switchover duration was reduced from 76s to 22s, and failover duration from 80s to 15s. For large configuration, the duration drops down from 77s to 29s for switchover and 94s to 26s for failover. Ashish mentioned the great work of Ludovico teams. We can easily find this information in Ludovico blogs: https://www.ludovicocaldara.net/blog/dg-26ai-faster-role-transitions/.

Ashish also recommended to have multiple standby per regions mentioning the recent event that happened in the world.

We also spoke about Intelligent data protection with On-Premises ZDLRA (zero data loss recovery appliance) and ZRCV (zero data loss autonomous recovery service) in the cloud. It is so easy to enable automatic backup in the Cloud and choosing as backup destination autonomous recovery service, will guarantee customer RPO.

Ashish also highlighted deployment flexibility across on-premises and the cloud with On-premises databases, exadata Cloud@Customer (hybrid cloud), public cloud exadata database service, …
ODA on premises, with Oracle Base Database Cloud@Customer, has also now its public cloud solution. You can read more here.
https://blogs.oracle.com/database/announcing-oracle-base-database-cloudcustomer

Also know that exadata database-developer is available at no cost for develppers.

Chris presented some great updates in terms of performance troubleshooting that 26ai is bringing. He wrote a third edition from his book, titled “Troubleshooting Oracle Performance” that will be available in December this year.

We talked about several topics like:

Signatures:

• Signatures are used to recognize SQL statements that are equivalent from a functional point of view
• DBMS_SQLTUNE.SQLTXT_TO_SIGNATURE
• SYS.SQLLOG$ is containing the signature of SQL statements that were executed in the past
  • 19.11 bug 32238935 mix of both literals and bind variables leads to a wrong computation o fthe force-matching signature

Possibility in 23ai to push down group-by function when using union all (23+):

• Push group by clauses inside views or inline views that contain union all operators
• cost based query transformation

Subsumption of views and subqueries:

• Purpose is to replace multiple query blocks that are created due to views, inline views or subqueries accessing the same tables with one query block
• Purpose is to avoid double execution

DBMS_STATS: gather stale option specific when gathering object statistics at the database or schema level

• only objects with stale statistics are processed
• 23.7 DBA_AUTO_STAT_OBJ_GATHER_DETAILS view with column REASON_TO_GATHER will give the reason why stats were gathered

Dynamic statistics/sampling and PL/SQL functions (23.8):

• Sampling queries only call PL/SQL functions that are referenced in predicates
• DYNAMIC_STATS preference: choose, on, off

Add verified SQL Plan baseline (23.3):

• Let the database engine search for the best-known execution plan for a given SQL statement
• New function ADD_VERIFIED_SQL_PLAN_BASELINE provided by DBMS_SPM package
• Backported to 19c starting 19.22

Real-time SQL Plan Management (23):

• verify during a hard parse if the exec plan is the same as one one contained in ASTS
• AUTO_SPM_EVOLVE_TASK=AUTO

IN conditions with many expressions (23.4):

• long lists of expressions frequently lead to performace problems, in condition with 64 or more expressions re implemetned differently when a full table sacn is executed
• instead of using disjunctive predicates a key vector containing the expressions is created

Parallel executions downgrades:

• downgrades are now outlined in real-time monitoring reports
• x$qksxa_reason to find what the code means (degree of parallelism downgrade reason)

Logical Partition change tracking (23+):

• For materialized view the database can track the staleness at the table and partition level
• 23 comes with logical PCT (partition change tracking) that means the database is able to use logical range partitions now
• Logical PCT will help on faster refresh

I also would like to hightlight that Chris had really good demos.

Purpose of this presentation is for us to somehow meet the support guys, people working behind a Service Request.

Laurentiu talked about Oracle EU Sovereign Cloud.
But What is Oracle EU Sovereign Cloud?
Oracle EU Sovereign Cloud enables commercial and public sector organizations to place sensitive data and applications in the cloud—in alignment with EU data privacy and sovereignty requirements.

Laurentiu provided us some inside of oracle support. When we go to MOS and log a SR, Oracle support expect for fast handling to have following information already mentioned during SR creation: Problem summary, OCID’s, timestamp of the issue, exadata cloud versions, evidence with logs as of TFA logs, business impact.
Setting the right severity is also important:
Critical Outage -> SEV 1
Significant Impairment -> SEV 2
Technical issue -> SEV 3
General Guidance -> SEV 4

Critical outage + system unavailable + production env -> Most critical, management of oracle support will be informed.

Product classification very important to have the SR going in the right queue.

And what does Oracle Development?
Oracle Developpement is composed of product engineering behind software improvements and fixes. They will work with support on production issue, develop product capabilities and code fixes and fix delivery (patches and Release Updates).

When the progress feels slow, what can we do?

• Option 1 – Trigger/initiate Chat widget, type agent to connect with a live agent
  • Describe the business impact, is there WA, explain the concern, …
• Option 2 – Request manager Actions from Details page
  • Front line manager or level 1 duty manager or level 2 director and duty (after 45 min of no reaction)

How can we do Better together?

• Initiate zoom session (or any other conference system, just need to provide an invitation link) after several goes and back
• Proactive Milestone SR procedural document – KB127520
• Request manager action to ask for a consolidated ownership of all the SRs opened
• Useful exchange with impact, context, evidence, …

Once SR is created, there is no more AI agent or chatbot. AI is only used as tool like AI parsers to help analyzing the logs.

As you know Mike Dietrich, we spoke a lot about patching and upgrade.

Mike spoke about release strategy:

• 11.2.0.4, 12.1, 12.2, 18c -> 19c -> 26ai upgrade (with downtime) or directly with datapump
• 23ai -> 26ai release update (no downtime)
• October 2025 Oracle AI Database 26ai version is named 23.26.0.0.0
• January 2026 is named 23.26.1.0.0, see 1 for quarter and not months
• Which will make July 2026 release name as been 23.26.3.0.0
• What will be 2027? Let’s see what will come
• For 26ai release schedule, see PNEWS1360

We also spoke about:

• Converting DB to PDB
• To set alter system set max_pdbs=3; in order to prevent license violations (change spfile, it is very important)
• Going to pdb no application changes, does not impact application only connection sqlplus / as sysdba connection might be impacted
• Multitenant extra license when you have more than 3 PDBs
• Recommendation for noncdb to pdb migration is to start with 3 databases and consolidate them early enough into Multitenant
• It is important not to forget client / server interoperability, see KB141043. For security reason it is important to uplift the client. Same matrix applies to database link
  • select * from gv$session_connect_info; to know client version
• Traditional auditing is desupported in 26ai
  • It is still possible for the database to generate audit records but you can not create new policies or change existing ones.
  • KB120309 for syntax to converter script

For an upgrade:

• Look at the Upgrade guide
• Use oradiff tool (https://oradiff.oracle.com) to compare 2 releases and know fixes covered. oradiff has a rest API.
• Execute a dictionary check before upgrade KB150043
• Ensure dictionary and fixed objects statistics.
• Purge recyclebin and gather dictionary stats 24 hours before upgrade

How to upgrade and convert:

• Installation of Oracle home is simpler, gold images, use autoupgrade
• Create container database first
  • character set, always choose AL32UTF8 to not take care of PDB character set and accept any
  • components, only install the components that is only needed, for faster upgrade and patching
  • compatible parameter, 23.6.0 (default), if downgrade would be needed first manually change it
  • unplug always possible in higher version but not to a lower compatible settings
  • new command: alter database set downgrade compatibility to '23.9.0';
  • update the compatible parameter on the fly without restarting the database
  • cdb_registry to check component
    • pdb$seed might not be seeing
      • alter system set "_exclude_seed_cdb_view"=false; to make it visible

Use Autoupgrade tool for upgrade, non-cdb to pdb conversion and patching:

• 1- plugin, 2- upgrade, 3- convert
• Get it through KB123450
• Or using www.oracle.com/goto/upgrade

It is important to know that DBUA is retired. Upgrade with autoupgrade is simple. Need a config file with all parameters
We can also easily collect autoupgrade logs with option -zip.

Conversion to pdb is irreversible. Rollback options would be: backup/restore or copy data files or refreshable clone PDB.

PDB creation does not propagate to the standby.

tips for view parameter:
alter system set "_cursor_obsolete_treshold"=1024 comment="Added XXX";, comment for _ paraameter
alter system set "_column_tracking_level"=1;

By the way, here can you find Mike’s slides: https://mikedietrichde.com/slides/

Martin spoke about how security is important. Attackers comes at the oracle db using listener, privilege, sql inection, data exfiltration db links or datapump. This is why patching is so important and patch are more and more frequent.
For release and patch we have Release Update (RU), Monthly Recommended Patch (MRP), Critical Security Patch Update (CSPU) and Critical Patch Update (CPU).
To cover his explanation, Martin highlighted that there were 656 total fixes between 19.31 (MRP) and next month 19.32 (RU).
Martins also point how useful oradiff.oracle.com was.
When out of place upgrade is the prob move, the in-place is the classic way to do. Let’s push for out of the place upgrade, new home can be rollbacked and there is minimal downtime.

Oracle autoupgrade and how to create new oracle home with autoupgrade:

• Get latest autoupgrade
• Create MOS keystore
• Get release update
• Create db_home

With autoupgrade we can patch OEM as well to 24.1, lastest autoupgrade version 26.5.260807 required.

3 modes of autoupgrade: analyze, fixups and deploy (change only -mode option value)
Remaining tasks : timezone, gather dictionary statistics, compatible parameter (change when 100% no downgrade), use and drop restore point

With Data Guard we can run standby-first patching.

ansible : autoupgrade takes care of the database. Ansible handles everything else.
ansible is agentless, idempotent, declarative

OLAM: Oracle Linux Automation Manager for automation and the oracle Autonomous Database for tracking.

Martin did a great demo with OLAM and ansible.

Great presentation from my Collegue who spoke about various pluggable database cloning method and mainly with Exadata sparse.
This presentation comes from a real case customer project migrating from on premises to Exadata Cloud@Customer project, and migrating an existing cloning NetApp platform to an Oracle technlogy cloning method system using PDB cloning and Exdata Spare technology.

Maintenance:

• PDB relocation
• un-plug/plug
• RTO in case of Disaster Recovery and all with automation

Automation:

• centralized, highly-available scheduler
• log, config management
• CI pipelines
• self-service

Jerôme explained PDB cloning techniques and method: PDB clone local vs remote, Non-CDB to PDB conversion, snapshot copy (storage based vs sparse) and refreshable clones

Exadata Sparse Clone:

• Single source, multiple targets
• small storage footing
• one command line
• transform sparse clone to full clone materialization process
• 3 cons: requires a Test-Master-Read-Only source, dependency on shared storage and capacity management for Oracle ASM (%DATA, %REDO, %SPARSE)

Finally Jérôme provided perfect Demos and provided also real-world challenges, experience coming from customer projects.

Great explanation how to remote full clone in Oracle Data Guard environment (as for example STANDBY=NONE)

Exadata sparse clone:

• Create a Test Master Read-Only (TMR)
  • Refreshable clone
  • Read-Only standby database
  • Read-Only full clone (full clone subset PDB)
• Provision sparse clones
  • Locally within same CDB
  • Remotely over database link
• Drop or Materialize sparse clone
• Revert source PDB datafiles read-write
  • SQL> DBMS_DNFS.RESTORE_DATAFILE_PERMISSIONS(pdb_name=> 'PDB');
• Re-create / Refresh TMR

Jérôme successfully ran several nice demos:

• Demo remote full clone with Oracle Data Guard.
• Demo create refreshable clone from Orale Data Guard (active) standby
• Demo refresh refreshable clone from Oracle ADG standby

Axel introduced the presentation by reminding on the TCP basics connections and then presented a real case TCP issue troubleshooting: ncat, nestat, tcpdump, … tcpdump with wireshark analyses

Transparent Network Substrate (TNS): Protocol TCP, TCPS, IPC, BEQ.
Listener endpoints: sqlnet.ora, tnsnames.ora, listener.ora

What can go wrong:

• sqlnet.ora with EZCONNECT and HOSTNAME,
• listener.ora with DEFAULT_SERVICE_LISTNER: no service, take this service
• sqlnet.ora OUTBOUND_CONNECT_TIMEOUT overriden by tnsnames.ora CONNECT_TIMEOUT
• sqlnet.ora EXPIRE_TIME can be mitigated with firewall configuration
• listener configuration with dynamic and static registration
• Service managed by DBMS_SERVICE or srvctl grid infra
• listener name
• listener USE_SID_AS_SERVICE and DEFAULT_SERVICE
• TNS Connection with LISTENER and SCAN_LISTENER
• Do not minimize the firewall configuration (with RAC, needs to configuration scan listener and listener)
• TNS Connection timeouts (Data Guard address list in tnsnames.ora).
  Pay attention on the connect_timeout, retry_count and retry_delay
  transport_connect_timeout parameter
• Duplicate tns names entries in tnsnames.ora (the last always wins)
• IFILE with max 3 levels for tnsnames.ora, listener.ora and sqlnet.ora
• TWO_TASK (linux) %LOCAL% (windows) variables
• Encryption sqlnet.ora parameter

Analyze can be done with gv$session_connect_info and gv$session

During the troubleshooting funny Oracle errors have been highlighted: ORA-28041, ORA-1699 error

At dbi services we can even have our expert shine as DJs! Thanks Clemens! Good job!

L’article SOUG Day October 2026 est apparu en premier sur dbi Blog.

In today’s data-driven economy, downtime isn’t just an IT headache; it’s a direct hit to your bottom line. As modern environments grow more complex, spanning hybrid clouds, edge devices, and massive enterprise clusters, Zabbix monitoring provides the single source of truth IT teams need.

The biggest challenge for IT Managers today isn’t a lack of data; it’s fragmentation. Using one tool for Linux, another for Windows, and a third for your databases creates “blind spots.”

This is why Zabbix is our solution of choice. It bridges these disparate environments into a unified, proactive operational strategy.

Zabbix is an open-source platform built to track the speed, capacity, and uptime of network devices, servers, virtual machines, and cloud apps.

Unlike paid platforms that use strict “per-node” pricing, which quickly drives up your costs as you grow, Zabbix offers a complete solution. In fact, whether you manage 10 servers or scale across 10,000 remote devices, Zabbix gives you full visibility without any extra license fees.

The main strength of Zabbix lies in its flexibility. First, it simplifies complex systems, giving you the same clear view whether your tasks run on old physical hardware or modern containers.

Database health is vital for your business. For this reason, Zabbix provides ready-to-use templates made specifically for popular database systems:

• MSSQL: Track buffer cache hit ratios, active connections, and lock wait times.
• PostgreSQL: Monitor table bloat, transaction throughput, replication lag, and query execution speeds.
• Oracle: Deep-dive into SGA/PGA memory usage, tablespace utilization, and archivelog generation rates.

In many organizations, system administrators and Windows engineers operate in separate toolsets. However, Zabbix works as the universal translator:

• For Linux Systems: Collect low-level kernel stats, disk I/O wait times, load averages, and critical process statuses via the lightweight Zabbix Agent.
• For Windows Environments: Tap natively into WMI (Windows Management Instrumentation) and Performance Counters to track system services, Active Directory health, and Event Logs.

Moving to cloud containers can often make it hard to see what is happening in your apps. Fortunately, Zabbix gives you complete control back. As a result, you can automatically find and track container pods, server workloads, and cloud APIs right alongside your physical servers.

Why does Zabbix excel where other open-source tools require complex add-ons?

• Low-Level Discovery (LLD): Zabbix automatically discovers file systems, network interfaces, and database instances dynamically, significantly reducing repetitive configuration tasks.
• Distributed Proxies: Deploy Zabbix proxies to collect and buffer monitoring data locally in remote locations or segmented networks, while simplifying network communication with the central server.
• Automated Remediation: Beyond simple alerts, Zabbix can trigger execution scripts automatically to restart failed services or clear temporary cache files before end users are impacted.

Deploying a single Zabbix platform offers clear operational advantages:

1. Faster Root Cause Analysis: For example, if a PostgreSQL database experiences high query latency, is the bottleneck inside the SQL code or caused by underlying storage I/O? Because all metrics reside within Zabbix, engineers can cross-correlate hardware telemetry with software metrics instantly.
2. Unified Alert Management: Eliminate alert fatigue. Centralize notifications through integrations with Slack, Microsoft Teams, PagerDuty, or custom Webhooks.
3. Predictable Cost Scale: Also, eliminate per-agent software licenses. Consequently, Zabbix scales alongside your enterprise infrastructure with no hidden software costs.

As infrastructure consultants, our goal is to convert complex IT environments into transparent, manageable assets. Whether you operate a dedicated Oracle database estate, a multi-tenant Windows domain, or a modern hybrid Kubernetes cloud, Zabbix monitoring provides the proactive visibility required to resolve incidents before they degrade performance.

In short, we don’t just monitor server uptime; we safeguard the availability of your business operations.

You can find other blogs regarding Zabbix or other topics at this link: dbi blogs

L’article Zabbix – the Swiss Army knife of monitoring est apparu en premier sur dbi Blog.

SSMA (SQL Server Migration Assistant) handles the whole Oracle-to-SQL Server move: it reads the source data dictionary, converts the schema, then generates a SELECT per table to pull the rows across. That last step is where this story goes wrong.

Migrating ~5,600 Oracle tables to SQL Server with SSMA. Most load fine; ~100 tables fail Migrate Data with the same error:

ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904:
"SYS_C00004_21081414:28:22$": invalid identifier

The named column exists in no DDL anyone wrote. The [Ora] prefix says Oracle itself is rejecting the query: SSMA built an extraction SELECT naming a column Oracle refuses to resolve. Tellingly, SELECT * and COUNT(*) run fine against the same table, whatever this column is, Oracle is happy to ignore it, but not to be asked for it by name.

The name is the giveaway: SYS_C00004_21081414:28:22$ is what Oracle calls a column after ALTER TABLE … SET UNUSED COLUMN.

Oracle offers two ways to get rid of a column: a logical delete and a physical one. The physical delete (ALTER TABLE … DROP COLUMN) is the honest one, but on a large table it is very time- and resource-consuming. That’s why people reach for the logical delete instead:

ALTER TABLE table_name SET UNUSED (column_name);

That statement is metadata-only and instant. The column immediately stops being visible to users, and the physical removal is deferred to whenever there is time for it (see Oracle Documentation):

ALTER TABLE table_name DROP UNUSED COLUMNS;
-- on large tables, cap undo growth by checkpointing every N rows:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

To free the original name for reuse, Oracle renames the column to SYS_C<internal column number>_<YYMMDDHH24:MI:SS>$, sets USER_GENERATED to NO, HIDDEN_COLUMN to YES and releases its COLUMN_ID. So the timestamp is not when the column was added, it is the second someone ran SET UNUSED. Ours says 14 August 2021, 14:28:22.

That also explains the error pattern. The operation is one-way and the column is unreadable by design, so naming it gets you ORA-00904 «invalid identifier». SELECT * and COUNT(*) keep working because the column no longer has a COLUMN_ID and is simply excluded from the star. SSMA, however, lists it and builds an explicit column list Oracle then refuses.

Not to be confused with SYS_NC…$. Those are a different animal: virtual columns backing a function-based index or extended statistics.

Bottom line: returning NULL costs you nothing. This is a column its owner already decided to delete, holding data Oracle itself will no longer hand out. There is no information left to lose.

• SSMA setting Ignore hidden system columns = Yes was not making any effect on this use case
• Dropping the column on SQL Server resolves nothing because the error is on the source SELECT, unaffected.
• Dropping it on Oracle could not be done in our scenario because the source is frozen; DDL not allowed.
• Custom select, column removed or bare NULL: SSMA still expects the name in its mapping and fails with “key not present” or “does not match up” before the query ever reaches Oracle.

Before editing anything, get the full list. Discovering the affected tables one failed migration at a time is a waste of an afternoon because the data dictionary already knows.

The reason the columns are findable at all is an asymmetry between two views: an unused column is gone from ALL_TAB_COLUMNS, but still listed in ALL_TAB_COLS with HIDDEN_COLUMN = 'YES'. That second view is what you query:

SELECT owner,
table_name,
column_name,
data_type,
internal_column_id,
TO_DATE(REGEXP_SUBSTR(column_name, '\d{8}:\d{2}:\d{2}'),
'YYMMDDHH24:MI:SS') AS set_unused_at
FROM dba_tab_cols
WHERE hidden_column = 'YES'
AND user_generated = 'NO'
AND REGEXP_LIKE(column_name, '^SYS_C\d+_\d{8}:\d{2}:\d{2}\$$')
-- AND owner = '<SCHEMA_NAME>'
ORDER BY owner, table_name, internal_column_id;

The regex is deliberately strict: it matches only the SET UNUSED naming pattern, so virtual columns and other system-generated names stay out of the result.

One more view is worth a look, as a cross-check:

SELECT owner, table_name, count AS unused_columns
FROM   dba_unused_col_tabs
--WHERE owner = '<SCHEMA_NAME>'
ORDER  BY count DESC, table_name;

DBA_UNUSED_COL_TABS gives the number of unused columns per table. Sorting by that number puts the dangerous tables first: those with two or three hidden columns are the ones where you’ll forget a line in the custom select and be back at square one.

Keep the hidden column’s name as an alias, but return a literal NULL instead of reading it. SSMA’s mapping finds the name (no “key not present”); Oracle never resolves the real column (no ORA-00904).

• Tools → Project Settings → General → Migration → enable Extended data migration options.

• Data Migration Settings tab → tick Use custom select → replace each hidden-column line with:

 SELECT ...   
    TO_CHAR("<COLUMN_NAME>", 'TM', 'NLS_NUMERIC_CHARACTERS = ''.,''') as "<COLUMN_NAME>",
    NULL as "SYS_C00004_21081414:28:22$"
from <OWNER>.<TABLE_NAME> t

• Migrate Data → 100%. Drop the NULL-filled column(s) on SQL Server in post-migration cleanup.

SYS_C…$ is not an exotic Oracle feature, it’s an ordinary column someone deleted years ago, logically. Oracle keeps the name on file; SSMA finds it, insists on naming it, and Oracle refuses to hand it over. Aliasing a NULL satisfies both, then you drop the column on the target. No source DDL, no external tooling, everything inside SSMA, behind a project setting that’s hidden by default.

L’article Fixing ORA-00904 on Oracle hidden columns during SSMA data migration est apparu en premier sur dbi Blog.