DEV Community: Uptime Architect

Oracle Flashback: Undo at the Database Level

Uptime Architect — Sun, 30 Aug 2026 20:21:09 +0000

Someone runs UPDATE accounts SET balance = 0 and forgets the WHERE. Commits it. A thousand balances are gone. The instinct is to reach for the backup — find last night's copy, restore it somewhere, extract the rows, lose everything that happened since. Hours, if it works at all.

There's a faster answer, and it's built into the database: undo the mistake in place. Oracle Flashback is a family of features that reverse human error at point-in-time — a query, a table, a dropped object, or the entire database — in seconds to minutes, without restoring anything. And here's the part that catches teams off guard: your standby doesn't help. A Data Guard replica faithfully replicated that UPDATE to the standby within seconds. Replication is not a backup; it copies your mistakes as diligently as your data. Flashback is what actually saves you.

The confusion is that "Flashback" is not one thing — it's half a dozen, built on different plumbing, with different reach. Knowing which one to grab, and where each one stops, is the whole skill.

The family, and what each is built on

Every Flashback feature reverses time, but they read from different places, and that determines their limits:

Flashback Query / Version Query — read a table's past directly, from UNDO. Nothing is changed; you just SELECT as of a past time. Bounded by undo_retention.
Flashback Table (to SCN/time) — rewind a table's rows to a past point, also from UNDO. Actually changes the data back. Bounded by undo_retention, and needs row movement.
Flashback Drop — recover a dropped table from the recycle bin, where dropped objects sit until space is needed.
Flashback Database — rewind the entire database to a past point, from flashback logs (or a guaranteed restore point). The heavy hammer; needs ARCHIVELOG.
Flashback Data Archive — long-term history for chosen tables, so you can query years back, long after UNDO is gone. (Different problem — compliance history, not oops-recovery.)

The rule of thumb: the smaller the blast radius, the cheaper and faster the fix. Reach for the narrowest tool that covers what you broke.

What did you break, and which Flashback undoes it. Start narrow: a query to look, a table-level flashback for one object, the whole-database rewind only when the damage is broad. The heavier the tool, the more it needs set up in advance.

Look before you leap: Flashback Query

Before you change anything, you can see the past. Flashback Query reads a table as it was, straight from UNDO — no restore, no downtime, just a SELECT:

-- how many rows matched five minutes ago?
SELECT COUNT(*) FROM accounts AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- or as of a precise SCN you captured before the change
SELECT * FROM accounts AS OF SCN 12345678 WHERE id = 42;

Its sibling, Flashback Version Query, shows you the history of a row — every version between two points, with who-did-what pseudo-columns — which is how you find when the damage happened before you undo it:

SELECT versions_startscn, versions_operation, balance
FROM   accounts VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP
WHERE  id = 42;

versions_operation reads I/U/D — insert, update, delete. This is often all you need: read the good values as of the right SCN and put them back yourself. But for a whole table, there's a cleaner way.

Undo a bad DML: Flashback Table to SCN

Someone zeroed every balance and committed. You don't need to reconstruct anything — rewind the table itself to the moment before the mistake:

-- capture the SCN BEFORE the change (or find it with a version query afterward)
SELECT dbms_flashback.get_system_change_number FROM dual;   -- e.g. 12345678

-- ... the bad UPDATE happens, gets committed ...

-- rewind just this table to that point
FLASHBACK TABLE accounts TO SCN 12345678;

Two requirements bite people here. First, the table must have been created with ENABLE ROW MOVEMENT — Flashback Table physically re-inserts the recovered rows, changing their rowids, and Oracle refuses unless row movement is on. It's the single most common reason this fails. Second, you can't flash back across a structural change (a DDL) to the table, and Oracle won't let you flash back to within a few seconds of the table's creation (ORA-01466). Within those limits it's instant, keeps working within undo_retention, and leaves triggers and indexes intact.

Recover a dropped table: Flashback Drop

DROP TABLE doesn't (by default) destroy the table — it renames it and moves it to the recycle bin, a per-user holding area where it waits until the tablespace actually needs the space. So a drop is reversible until then:

-- oops
DROP TABLE accounts;

-- bring it back, rows and all, from the recycle bin
FLASHBACK TABLE accounts TO BEFORE DROP;

-- what's in the bin?
SELECT object_name, original_name, droptime FROM recyclebin;

Two things to know. DROP TABLE accounts PURGE bypasses the recycle bin — the object is gone immediately, no flashback. And the recycle bin is not infinite insurance: objects are purged automatically under space pressure, so "it was in the recycle bin last week" is not a recovery plan. It's a safety net for the recent accidental drop, which is exactly when you need it.

Rewind everything: Flashback Database

Sometimes the damage is broad — a bad deployment, a truncate spree, a dropped schema. Table-level tools won't cut it. Flashback Database rewinds the entire database to a past SCN, timestamp, or named restore point, using flashback logs it has been keeping since you turned the feature on. It's the closest thing to a database-wide undo button — and the one that must be set up before the disaster:

-- prerequisites (once): ARCHIVELOG + a flashback recovery area
-- then, before a risky change, drop a marker you can rewind to:
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- ... the disaster: someone drops the whole app schema ...

-- rewind the WHOLE database to the restore point
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
ALTER DATABASE OPEN RESETLOGS;

A guaranteed restore point is the seatbelt you fasten before anything risky — an upgrade, a big data load, a schema migration. It pins the flashback logs so you can always get back to that exact point, no matter how much changes. In a multitenant database you can even rewind a single pluggable database with FLASHBACK PLUGGABLE DATABASE, leaving the others untouched. The catch is the setup: Flashback Database is only available if the database was already in ARCHIVELOG mode with flashback logging (or a guaranteed restore point) in place. Turn it on when things are calm; you can't retrofit it mid-crisis.

Don't take my word for it — run it. The Flashback lab builds a 1,000-row ACCOUNTS table and stages three disasters, each reversed and asserted: a committed UPDATE with no WHERE zeroes all 1,000 balances → FLASHBACK TABLE ... TO SCN brings them back (0 still zero); the table is dropped → FLASHBACK TABLE ... TO BEFORE DROP restores it with all 1,000 rows; and the entire LABUSER schema is dropped → the lab enables ARCHIVELOG, creates a guaranteed restore point, and FLASHBACK DATABASE TO RESTORE POINT rewinds the whole database, bringing the schema back with its 1,000 rows. If any recovery doesn't restore the data, the run fails. All three are proven on every CI push.

What teams get wrong

Believing a standby is a backup. Data Guard replicates your DELETE to the standby in seconds. A replica protects against hardware loss, not human error — for that you need Flashback (or a delayed standby apply). Don't confuse the two.
Creating tables without ENABLE ROW MOVEMENT. Then the day you need FLASHBACK TABLE ... TO SCN, it's refused. Turn row movement on for tables you'd ever want to rewind.
Assuming Flashback Query reaches back forever. It's bounded by undo_retention and the size of the undo tablespace — under pressure, old undo is overwritten and you get ORA-01555. For guaranteed reach, set RETENTION GUARANTEE on undo, or use a Flashback Data Archive.
DROP ... PURGE out of habit, or a full recycle bin. PURGE skips the recycle bin entirely, and the bin is auto-purged under space pressure. Neither is a bug — just know that "flashback drop" only works while the object is still in the bin.
Never enabling Flashback Database — or never testing it. It's unavailable unless ARCHIVELOG + flashback logging were on before the disaster. Turn it on, size the FRA, and actually run a flashback-to-restore-point drill, so it's not the first time when it counts.
A guaranteed restore point quietly filling the FRA. Guaranteed restore points pin flashback logs forever; leave one lying around and the recovery area fills, which can hang the database. Drop the restore point once the risky change is safely done.
Reaching for Flashback Database when a table flashback would do. Rewinding the whole database to fix one table punishes everyone. Match the tool to the blast radius — narrowest first.

Frequently asked questions

What is Oracle Flashback?

Oracle Flashback is a family of features that let you reverse the effects of human error at a point in time without restoring a backup. It includes Flashback Query and Version Query (read a table as it was in the past, from undo data), Flashback Table (rewind a table’s rows to a past SCN or time), Flashback Drop (recover a dropped table from the recycle bin), Flashback Database (rewind the entire database to a past point using flashback logs or a restore point), and Flashback Data Archive (retain long-term history for chosen tables). Each reads from different underlying data — undo, the recycle bin, or flashback logs — which determines how far back it can go and what it requires.

What is the difference between Flashback and restoring from a backup?

Restoring from a backup (for example with RMAN) copies datafiles from a saved backup and rolls forward, which is slower, usually needs a separate location or downtime, and typically loses everything since the backup unless you also apply archived logs. Flashback undoes changes in place using data the database already keeps — undo, the recycle bin, or flashback logs — so recovery is fast and surgical, from seconds to minutes, with no restore. Flashback is the right tool for recent human error (a bad UPDATE, a dropped table, a bad deployment); RMAN restore is the fallback for media loss, corruption, or when the change is older than your flashback and undo retention allows.

Why does FLASHBACK TABLE ... TO SCN require row movement?

Flashback Table recovers rows by physically deleting the current rows and re-inserting the past versions, which gives them new rowids. Oracle only permits an operation that changes rowids if the table has row movement enabled, so FLASHBACK TABLE ... TO SCN (or TO TIMESTAMP) fails on a table created without it. You enable it with CREATE TABLE ... ENABLE ROW MOVEMENT or ALTER TABLE name ENABLE ROW MOVEMENT. This is the most common reason a table-level flashback is refused. Note that Flashback Drop (TO BEFORE DROP) and Flashback Query do not need row movement — only the table-to-SCN rewind does.

How far back can Flashback Query go?

Flashback Query and Flashback Table read from undo, so they can go back only as far as undo for the changed blocks still exists — governed by the UNDO_RETENTION parameter and the size of the undo tablespace. Under write pressure Oracle may overwrite unexpired undo to avoid failing DML, so a query too far back can return ORA-01555 (snapshot too old). To make the reach reliable, set RETENTION GUARANTEE on the undo tablespace (which prevents overwriting unexpired undo) and size it accordingly, or use a Flashback Data Archive for tables that need long-term, guaranteed history measured in months or years.

What is FLASHBACK TABLE ... TO BEFORE DROP and the recycle bin?

When you DROP a table, Oracle does not immediately destroy it by default — it renames the object and its dependents and moves them to the recycle bin, a per-user logical holding area, where they remain until the tablespace needs the space. FLASHBACK TABLE name TO BEFORE DROP restores the table and its data from the recycle bin, optionally renaming it with RENAME TO. Two caveats: DROP TABLE name PURGE bypasses the recycle bin and destroys the object immediately (no flashback), and objects in the recycle bin are purged automatically under space pressure, so it is a safety net for a recent accidental drop, not a long-term backup.

What does Flashback Database require?

Flashback Database rewinds the entire database to a past SCN, timestamp, or restore point using flashback logs, and it must be set up in advance. The database must be in ARCHIVELOG mode with a fast recovery area (FRA) configured, and either flashback logging turned on (ALTER DATABASE FLASHBACK ON) or a guaranteed restore point created, so the flashback logs exist to rewind through. The operation itself is run with the database mounted (not open): SHUTDOWN, STARTUP MOUNT, FLASHBACK DATABASE TO ..., then ALTER DATABASE OPEN RESETLOGS. Because these prerequisites cannot be added after the fact, enable Flashback Database before you need it and test a flashback-to-restore-point drill.

Can I flash back a single pluggable database (PDB)?

Yes. In a multitenant database you can rewind one PDB independently with FLASHBACK PLUGGABLE DATABASE, leaving the other PDBs and the rest of the CDB untouched, provided the CDB is in ARCHIVELOG mode and local undo is in use (the default in recent releases). You can flash a PDB back to a PDB-level restore point, an SCN, or a timestamp, and you can create guaranteed restore points scoped to a single PDB. This is far less disruptive than flashing back the whole container database, so for damage confined to one PDB it is the right level to operate at — the same "match the tool to the blast radius" principle as choosing between table and database flashback.

Does a Data Guard standby protect against an accidental DELETE?

No, not by itself. A standby applies the same redo the primary generated, so a committed accidental DELETE or a bad UPDATE is replicated to the standby within seconds, just like legitimate changes — replication cannot tell a mistake from intended work. Standbys protect against media and site failure, not logical or human error. The defenses for human error are Flashback (Query, Table, Drop, or Database) on the primary, and, at the standby level, a deliberately delayed apply (DELAY on the apply, or a snapshot standby) that gives you a window to intervene before the mistake reaches the standby. Treat replication and point-in-time undo as complementary, not interchangeable.

Flashback is the recovery discipline’s scalpel, and it belongs next to the heavier tools, not instead of them: RMAN restore and recovery is the fallback for media loss and corruption, Data Guard protects against site and hardware failure, and the HA decision tree places each where it belongs. Human error is the failure they don’t cover — and it’s the most common one. Turn Flashback Database on while things are calm, enable row movement on the tables you’d ever want to rewind, and know which tool matches which mistake. Then prove all three recoveries end to end with the Flashback lab, so the first time you undo a disaster isn’t in production with everyone watching.

Originally published at uptimearchitect.com.

Oracle Transparent Data Encryption: Prove the Datafile Is Unreadable

Uptime Architect — Wed, 26 Aug 2026 12:23:29 +0000

"The data is encrypted at rest." Everyone says it. Almost nobody checks it. It goes in the compliance questionnaire, the auditor ticks the box, and the actual bytes on disk stay a mystery until the day a backup tape, a decommissioned disk, or a cloned VM image walks out the door and someone finds out the hard way whether it was true.

It's a checkable claim. Take a database, write a recognizable string into a table, and read the datafile off disk with grep. If the string is sitting there in plaintext, the data is not encrypted at rest, whatever the questionnaire says. If it's gone — replaced by ciphertext — it is. Transparent Data Encryption is how you get the second answer, and the whole point of this post is that you don't have to take it on faith. You can watch it happen.

But first, the part most TDE write-ups skip, and the part that decides whether it's protecting you or just making you feel protected: what it actually defends against.

What TDE protects — and what it doesn't

TDE encrypts data at rest: in the datafiles, in RMAN backups, in redo and undo and temp on disk, in Data Pump exports (with the right flag). It does this transparently — the application sees plaintext, the SQL doesn't change, and a user with the right privileges reads the data exactly as before. That transparency is the feature and the boundary:

It protects the files. A stolen datafile, a lost backup, a snapshot copied out of the storage array, a disk sent back to the vendor without being wiped — all unreadable without the key. This is the threat TDE is built for, and it's a real one: the data breach that starts with "someone got a copy of the storage" is common precisely because it bypasses every database control.
It does not protect against a logged-in user. An attacker who has valid credentials, or a SQL injection hole that runs queries as the app, sees plaintext — because the database decrypts for anyone authorized to read, which is the entire design. TDE is not access control. Stopping that is what privileges, hardening, and auditing are for.
It does not encrypt data in the buffer cache or on the wire. Blocks in the SGA are plaintext; TDE is about disk. Encrypting the connection is a separate setting (native network encryption or TLS).

Get that boundary right and TDE is one of the highest-value, lowest-friction controls you can turn on. Get it wrong — treat it as a magic "now we're secure" switch — and you've encrypted the disk while leaving the front door open.

The keystore is the whole game

TDE is a two-level key hierarchy, and understanding it is understanding TDE. The actual data is encrypted with tablespace (or column) encryption keys. Those keys live in the datafiles themselves — but they're encrypted, wrapped by a single master encryption key. The master key is the one thing that does not live with the data. It lives in a separate keystore (historically called the Oracle wallet).

The two-level hierarchy, and why the keystore is separate. The data keys travel with the datafiles; the master key that unlocks them does not. Steal the .dbf and you have ciphertext plus a locked box. You need the keystore too.

That separation is the entire security model, and it has a sharp consequence: if the keystore is stolen along with the datafiles, TDE has protected nothing. Which is exactly the mistake an auto-login keystore invites. A software keystore comes in three flavors:

Password keystore — the database can't open it without a human (or a script) supplying the password. Safest, most operationally annoying: someone has to open the wallet after every restart.
Auto-login keystore — the database opens it automatically at startup. Convenient, and how most production databases run. But the auto-login file, if it sits next to the datafiles and gets copied with them, hands the thief the key. Keep it off the data volume.
Local auto-login keystore — auto-login, but tied to the host it was created on. Copy it to another machine and it won't open. This is usually the right default: the database opens its own wallet, but a stolen copy is useless elsewhere.

For anything beyond a single box, the master key belongs in a real key manager — Oracle Key Vault or an HSM — not a file on the same server at all. The file keystore is where you start; centralized key management is where a fleet ends up.

Modern Oracle (19c and up) configures all of this through two settings — WALLET_ROOT (a static parameter, so setting it needs one restart) and TDE_CONFIGURATION:

-- one-time: tell the database where keystores live, then turn on the FILE keystore
ALTER SYSTEM SET WALLET_ROOT = '/etc/oracle/wallets/prod' SCOPE = SPFILE;  -- then restart
ALTER SYSTEM SET TDE_CONFIGURATION = 'KEYSTORE_CONFIGURATION=FILE' SCOPE = BOTH;

-- create the keystore, open it, and set the master key
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "<strong-pw>";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<strong-pw>" CONTAINER = ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<strong-pw>" WITH BACKUP;

That WITH BACKUP is not optional decoration. Lose the master key and the data is gone — not locked, gone, as unrecoverable as if you'd deleted it. Back the keystore up, separately from the database backups, and guard it like the crown jewel it is. More teams have lost data to a lost wallet than to a stolen one.

Tablespace encryption, not column encryption (usually)

TDE has two modes, and the choice is easier than it looks:

Tablespace encryption encrypts an entire tablespace — every table, index, and LOB in it — with AES. It's fully transparent, imposes a small and roughly fixed CPU cost, and has no functional surprises: every query, every index, every join works exactly as before. This is the default answer.
Column encryption encrypts specific columns. It sounds surgical and appealing — "just encrypt the card number" — but it comes with real limitations: an encrypted column can't be a foreign key, can't be indexed with a normal range scan, and breaks some optimizations. Reach for it only when you genuinely need one or two columns encrypted and can't encrypt the tablespace.

Creating an encrypted tablespace is one clause:

CREATE TABLESPACE app_secure
  DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/app_secure.dbf' SIZE 100M
  ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

And you don't have to rebuild the world to encrypt what you already have. Since 12.2, you can convert an existing tablespace online, with the application still running:

ALTER TABLESPACE users ENCRYPTION ONLINE ENCRYPT;   -- no outage; encrypts in the background

New data written to an encrypted tablespace is encrypted; the online convert handles the existing blocks. There's no "half-encrypted" state a query can trip over — the transparency holds throughout.

Now prove it

Here's the part that separates "we enabled TDE" from "we checked." Put the same recognizable rows into an encrypted tablespace and an ordinary one, flush them to disk, and read the raw datafiles — not through the database, which would helpfully decrypt for you, but straight off the filesystem:

# a distinctive canary string is written into BOTH tablespaces' tables, then flushed to disk.
# read the bytes on disk directly (grep -a treats the binary datafile as text):
grep -a -c 'CANARY_TDE' /opt/oracle/oradata/FREE/FREEPDB1/tde_plain.dbf   # -> 133   (plaintext, right there)
grep -a -c 'CANARY_TDE' /opt/oracle/oradata/FREE/FREEPDB1/tde_enc.dbf     # ->   0   (ciphertext, gone)

Same rows, same fake card numbers, same everything — the only difference is one tablespace was created ENCRYPTION USING 'AES256'. In the ordinary datafile the canary is sitting there in the clear, 133 times over. In the encrypted one it's simply not there; the bytes are AES ciphertext. That's encryption at rest, demonstrated rather than asserted — and it's exactly what a thief with a copy of your storage would find.

The keystore is what stands between those two outcomes. Close it and the database itself goes blind:

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<strong-pw>";
SELECT SUM(amount) FROM app.secrets_enc;
-- ORA-28365: wallet is not open

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<strong-pw>";
-- readable again, no data lost

With the wallet closed, the encrypted table throws ORA-28365 on any read — the master key is out of memory, so the tablespace key can't be unwrapped, so the blocks can't be decrypted. The ordinary table keeps reading fine. That's the whole model in one gesture: the key, not the file, is the thing you're protecting.

Don't take my word for it — run it. The TDE lab stands up an Oracle Database Free container, configures a software keystore, and builds one encrypted tablespace and one ordinary one holding identical canary rows. It then reads both datafiles off disk and asserts the canary appears in the plaintext file (133 hits) and is absent from the encrypted one (0 hits) — encryption at rest, proven on the actual bytes. Then it closes the keystore and asserts the encrypted read fails with ORA-28365 while the plaintext read still works, and that reopening the wallet restores access. If the canary shows up in the encrypted datafile, or closing the wallet doesn't block the read, the run fails. The whole thing is proven on every CI push.

What teams get wrong

Auto-login wallet next to the datafiles. The single most common way to make TDE pointless: the auto-login keystore gets backed up or copied alongside the data it's supposed to protect. Use a local auto-login keystore (host-tied), and keep it off the data volume.
Not backing up the keystore — or losing it. Lose the master key and the data is unrecoverable. The keystore needs its own backup, stored separately from the database backups (putting both in the same place recreates the theft problem). WITH BACKUP on every key operation, and a real archive of the wallet.
Thinking TDE stops a logged-in attacker. It doesn't. Encryption at rest is orthogonal to access control. A stolen password or a SQL-injection hole reads plaintext all day. TDE is one layer; auditing and least-privilege are the others.
Forgetting the copies. Encrypting the tablespace but not the backups, or exporting with Data Pump without ENCRYPTION, leaves plaintext copies lying around. The data is only as encrypted as its least protected copy.
Column encryption where a tablespace would do. Reaching for column encryption and then fighting its limits — no range-scan index, no foreign keys — when tablespace encryption would have been transparent and simpler. Encrypt the tablespace unless you have a specific reason not to.
Never rotating the master key. The master key can and should be rekeyed periodically (and immediately if you suspect exposure) with ADMINISTER KEY MANAGEMENT SET KEY. A key that never changes is a key with an ever-growing blast radius.

Frequently asked questions

What is Transparent Data Encryption (TDE) in Oracle?

Transparent Data Encryption is an Oracle feature that encrypts data at rest — in datafiles, backups, redo, undo, and temp on disk — without requiring any change to the application. It is called transparent because authorized users and the SQL they run see plaintext exactly as before; the encryption and decryption happen automatically at the storage layer. TDE uses a two-level key hierarchy: tablespace or column encryption keys encrypt the data and are stored (encrypted) in the datafiles, while a single master encryption key that unwraps them is kept separately in a keystore. TDE protects against theft of the physical files or backups, not against a user who is already authenticated to the database.

What does TDE protect against, and what does it not?

TDE protects against threats where someone obtains the files rather than a database session: a stolen or lost backup, a decommissioned disk, a copied storage snapshot, or a cloned VM image. In all of those the data is unreadable without the master key. TDE does not protect against an attacker who has valid credentials or exploits SQL injection, because the database decrypts transparently for anyone authorized to read — that is what access control, hardening, and auditing are for. It also does not encrypt data in the buffer cache (which is plaintext in memory) or on the network connection, which needs native network encryption or TLS configured separately.

What is the Oracle keystore (wallet) and why is it kept separate?

The keystore, historically called the Oracle wallet, is where the TDE master encryption key is stored. The tablespace and column keys that actually encrypt data live inside the datafiles, but they are themselves encrypted by the master key, and the master key lives only in the keystore. This separation is the entire security model: a stolen datafile contains ciphertext and a locked, wrapped key, but not the master key needed to open it. If the keystore is stolen together with the datafiles, TDE protects nothing, which is why an auto-login keystore should never sit on the same volume as the data and why fleets move the master key into Oracle Key Vault or an HSM.

What is the difference between an auto-login and a local auto-login keystore?

A password keystore must be opened by supplying its password, so the database cannot read encrypted data after a restart until a human or script opens the wallet. An auto-login keystore lets the database open the wallet automatically at startup, which is convenient but means anyone who copies that auto-login file can open it anywhere. A local auto-login keystore is also opened automatically, but it is tied to the host on which it was created — copied to a different machine it will not open. For most production databases a local auto-login keystore, kept off the data volume, is the right balance of convenience and safety.

Should I use tablespace encryption or column encryption?

Tablespace encryption is the default choice for almost all cases. It encrypts an entire tablespace — every table, index, and LOB — transparently, with a small and predictable CPU cost and no functional limitations: all queries, indexes, and joins behave exactly as before. Column encryption encrypts specific columns and sounds more surgical, but it carries real restrictions: an encrypted column cannot be a foreign key or be used in a normal range-scan index, and it interferes with some optimizations. Use column encryption only when you need to encrypt one or two specific columns and cannot encrypt the whole tablespace; otherwise encrypt the tablespace.

Can I encrypt an existing tablespace without downtime?

Yes. Since Oracle 12.2 you can convert an existing tablespace to encrypted online, while the application keeps running, with ALTER TABLESPACE ENCRYPTION ONLINE ENCRYPT. Oracle encrypts the existing blocks in the background and encrypts new data as it is written, and there is no half-encrypted state that queries can trip over. This means you do not have to create a new encrypted tablespace and migrate objects into it; you can encrypt the data where it already lives. You do need enough auxiliary space for the online conversion, and the master key and keystore must be set up first.

What happens if I lose the TDE keystore or master key?

If you lose the keystore and have no backup of it, the encrypted data is permanently unrecoverable — not merely locked, but effectively destroyed, because the tablespace keys inside the datafiles can never be unwrapped again. This is the most important operational risk of TDE and it causes more data loss than theft does. You must back up the keystore whenever the master key changes (the WITH BACKUP clause creates a backup at each key operation), store that keystore backup separately from the database backups so a single compromise cannot capture both, and test that you can actually open a restored keystore. Treat the keystore as the most critical artifact in the environment.

Does Oracle TDE require an extra license?

On-premises Enterprise Edition, TDE is part of the Advanced Security Option, which is a separately licensed pack, so using it on-prem EE requires that license. In Oracle Cloud Infrastructure, including Autonomous Database and the Base Database and Exadata cloud services, TDE is included and is typically enabled by default, so cloud databases are encrypted at rest out of the box. Because licensing terms change and depend on your edition and platform, confirm your specific entitlement before enabling TDE in production on-premises rather than assuming it is included.

Encryption at rest is the fourth leg of the same security-and-ops discipline as the rest: patching closes known vulnerabilities, the hardening checklist closes the configuration gaps, unified auditing tells you when either is being tested, and TDE makes sure that when the files themselves are the target, what walks out the door is unreadable. Do it deliberately: the right keystore type, kept off the data volume and backed up separately, tablespace encryption over column encryption, and the master key rotated on a schedule. Then prove it the way that ends the argument — with the TDE lab, where you can watch the canary vanish from the datafile and watch the database go blind the moment the wallet closes.

Originally published at uptimearchitect.com.

Oracle SQL Plan Management: Stop a Good Plan From Going Bad

Uptime Architect — Wed, 19 Aug 2026 19:25:20 +0000

A query that ran in fifty milliseconds for a year is suddenly taking thirty seconds. Nobody changed the code. Nobody changed the query. And that's exactly the problem: the optimizer is allowed to change its mind. New statistics, a bind variable it peeked at differently, an upgrade, a dropped index — any of them can hand the same statement a new plan, and the new plan can be worse. Most of the time you never notice. The time you notice, it's a pager at 2 a.m.

The two posts before this one were about making the optimizer's estimate right — reading the plan to find where the estimate went wrong, and fixing the statistics behind it. This one is about the plan you've already got right and want to keep. Because "get the estimate right" is a moving target: statistics get re-gathered, data shifts, and a plan that was correct on Tuesday is not guaranteed on Wednesday. SQL Plan Management is the seatbelt. It lets the optimizer keep improving plans everywhere else while refusing to let a known-good plan silently regress on the statements you can't afford to have go wrong.

What a SQL plan baseline actually is

A SQL plan baseline is a stored set of accepted execution plans for one SQL statement. It lives in the SQL Management Base in SYSAUX, and it's keyed by the statement's signature — a hash of the normalized SQL text (case- and whitespace-insensitive, but literal-sensitive, which is one more reason to use bind variables). When a statement with a baseline parses, the optimizer does something subtly different from its usual job:

It builds its best-cost plan the way it always does, from the current statistics.
Then it checks the baseline. If that best-cost plan is already accepted, it just uses it — nothing to protect against.
If the best-cost plan is not accepted, the optimizer sets it aside as a non-accepted plan (kept for later, in case it's genuinely better) and instead runs the best accepted plan that still reproduces against today's schema.
Only if no accepted plan can be reproduced — say the index it needs was dropped — does it fall back to the best-cost plan.

The effect is a ratchet. The optimizer is free to find new plans, but it can't use one you haven't blessed. A plan you captured while things were good stays in force even when the cost model, fed newer statistics, starts preferring something else.

What the optimizer does when an enabled baseline exists. It still computes its best-cost plan — it just isn't allowed to run it unless you've accepted it. A newly found plan is parked as non-accepted for later verification, not used on the spot.

The three flags that decide everything

Every plan in a baseline carries three independent states, and mixing them up is the single most common source of "SPM isn't working":

ENABLED — the plan is eligible for consideration at all. Disable it and the optimizer ignores it.
ACCEPTED — the plan is allowed to run. This is the one that matters. A captured plan can be enabled but not accepted (it's a candidate, waiting to prove itself); the optimizer will not use it until it's accepted.
FIXED — the plan is preferred. When a baseline has any fixed plans, the optimizer chooses only among those and stops adding new candidates. A fixed baseline is how you say "this, and nothing else, until I say otherwise."

The mental model: ENABLED is "in the pool," ACCEPTED is "cleared to fly," FIXED is "and it's the captain." Most of SPM is moving plans between those states deliberately instead of letting it happen to you.

Capturing the plan you want to keep

There are three ways plans get into a baseline. The one you'll reach for most is loading a plan you already have in the cursor cache — you found the good plan, now you pin it:

-- pin the plan currently in the cursor cache for one SQL_ID
DECLARE
  n NUMBER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'a1b2c3d4e5f6g');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

Plans loaded this way arrive enabled and accepted — ready to enforce immediately. The other two sources:

Automatic capture. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE and Oracle records a baseline for every repeatable statement it sees. The first plan for a statement is auto-accepted (it becomes the reference); any different plan found later is captured as non-accepted, waiting for you to verify it. Powerful, but it's a blanket — turn it on around a risky change (an upgrade, a big data load), capture what you need, then turn it back off rather than leaving it on forever.
From a SQL tuning set / AWR. DBMS_SPM.LOAD_PLANS_FROM_SQLSET pulls plans out of a tuning set, which is how you seed baselines from AWR history — including from the old release before an upgrade, so the plans that worked last quarter are on file before the new optimizer ever runs.

That last one is the killer use case: capture baselines from the current release, upgrade, and the optimizer inherits a floor. New plans still get found and parked as candidates, but nothing regresses on day one because every statement already has its old, proven plan accepted.

Confirm it's actually in force

Two checks. First, does the baseline exist and what state is it in:

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%shop.orders%';

Then the check that matters — that the plan the optimizer actually built came from the baseline. The plan's Note section says so outright:

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
-- ...
-- Note
-- -----
--    - SQL plan baseline SYS_SQL_PLAN_1a2b3c4d used for this statement

If that line is there, the baseline held. If it isn't — and you expected it to — the usual reasons are OPTIMIZER_USE_SQL_PLAN_BASELINES turned off, the plan isn't accepted (only enabled), or the accepted plan can no longer be reproduced because the schema changed underneath it.

Evolving: how a baseline gets better without getting worse

A baseline that never changes eventually holds a plan that's genuinely stale — a new index really would be faster now. That's what evolution is for. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE takes the non-accepted candidates piling up in the baseline, runs them, and accepts one only if it actually performs better than the current accepted plan by a real margin. This is the crucial difference from just letting the optimizer loose: a plan is promoted on measured performance, not on estimated cost.

-- verify the parked candidates and accept only the ones that prove faster
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_1a2b3c4d5e6f7g8h') FROM dual;

From 19c on, the automatic SPM evolve advisor task does exactly this in the maintenance window: it evaluates the candidates that accumulated during the day and auto-accepts the ones that verify faster. So the healthy steady state is: baselines protect you from regressions, and evolution quietly promotes real improvements after they've proven themselves. You get stability and progress, instead of choosing.

Don't take my word for it — run it. The SQL Plan Management lab builds a million-row ORDERS table with a rare, indexed status and a histogram, so WHERE status = 'OPEN' gets the cheap index plan. It captures that plan as an accepted baseline, then drops the histogram to simulate the everyday stats drift that breaks plans — now the optimizer estimates a third of the table and costs a full scan as cheaper. The lab runs the same query twice and asserts both outcomes: with baselines off, the plan regresses to a full table scan; with baselines on, the accepted index plan is held (it's still reproducible because the index exists) and DBMS_XPLAN reports SQL plan baseline ... used for this statement. If the regression doesn't happen, or the baseline doesn't stop it, the run fails. The whole before/after is proven on every CI push.

What teams get wrong

Confusing enabled with accepted. A captured candidate plan is enabled but not accepted, and the optimizer won't run it. People load a plan, see it in DBA_SQL_PLAN_BASELINES, and assume it's in force — then wonder why nothing changed. Check the ACCEPTED column, not just that a row exists.
Fixing a baseline and forgetting. A FIXED baseline stops accepting new candidates entirely. That's correct for a statement you never want touched — and a slow poison for one whose data grew tenfold since, because the plan can no longer improve even when it should. Fixed plans need a review date.
Leaving automatic capture on forever. Blanket capture around an upgrade is smart; leaving it on fills the SQL Management Base with baselines for one-off and ad-hoc statements and quietly grows SYSAUX. Turn it on with intent, capture, turn it off.
Baselining statements full of literals. Signatures are literal-sensitive. A statement that inlines WHERE id = 48213 has a different baseline from WHERE id = 48214. Without bind variables you're baselining a million near-identical statements, none of which recurs. Bind first, baseline second.
Treating a baseline as a fix for bad statistics. SPM freezes a plan; it doesn't make the estimate correct. If you pin a plan to paper over stale stats, you've hidden the problem, not solved it — and every other statement on that table still gets the bad estimate. Fix the number, then baseline the good plan it produces.
Capturing on the new release, after the regression. The highest-value moment to capture is before an upgrade, from the release that still works. Seed baselines from AWR on the old version and the new optimizer inherits a floor instead of a surprise.

Frequently asked questions

What is SQL Plan Management in Oracle?

SQL Plan Management (SPM) is an Oracle feature that prevents execution plans from regressing by controlling which plans the optimizer is allowed to use for a statement. It stores accepted plans as SQL plan baselines in the SQL Management Base. When a statement that has a baseline is parsed, the optimizer still computes its best-cost plan, but it will only execute a plan that has been accepted into the baseline; a newly found plan is recorded as a non-accepted candidate for later verification rather than used immediately. This lets the optimizer keep finding better plans while guaranteeing that a known-good plan cannot be silently replaced by a worse one.

What is the difference between enabled, accepted, and fixed plans in a baseline?

Enabled means the plan is eligible for the optimizer to consider at all. Accepted means the plan is allowed to actually run — this is the state that determines whether a plan is used, and a plan can be enabled but not accepted, in which case it is only a candidate. Fixed means the plan is preferred: when a baseline contains any fixed plans, the optimizer chooses only among the fixed plans and stops adding new candidates to the baseline. A plan loaded from the cursor cache arrives enabled and accepted; a plan captured automatically as an alternative arrives enabled but not accepted until it is evolved or manually accepted.

How do I capture a SQL plan baseline?

There are three main ways. First, load a plan already in the cursor cache with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE for a specific SQL_ID — this loads the plan enabled and accepted, ready to enforce. Second, enable automatic capture by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE, which records a baseline for every repeatable statement (the first plan is auto-accepted and later alternatives are captured as non-accepted). Third, load plans from a SQL tuning set with DBMS_SPM.LOAD_PLANS_FROM_SQLSET, which is how you seed baselines from AWR history, including from an older release before an upgrade.

How do I know if a SQL plan baseline is being used?

Run the statement and pull its plan with DBMS_XPLAN.DISPLAY_CURSOR using a format such as ALLSTATS LAST or TYPICAL. If a baseline built the plan, the Note section at the bottom reads "SQL plan baseline used for this statement." You can also query V$SQL.SQL_PLAN_BASELINE for the cursor, which holds the baseline plan name when one was applied. If you expected a baseline and neither appears, check that OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, that the plan is accepted and not merely enabled, and that the accepted plan is still reproducible against the current schema.

What does it mean to evolve a SQL plan baseline?

Evolving a baseline means testing the non-accepted candidate plans that have accumulated for a statement and accepting one only if it actually performs better than the current accepted plan. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE runs the candidates and promotes them based on measured performance, not estimated cost, which is what makes evolution safe. From Oracle 19c on, the automatic SPM evolve advisor task performs this verification in the maintenance window and auto-accepts candidates that prove faster, so baselines protect against regressions while still allowing genuinely better plans to be adopted after they have been verified.

Does a SQL plan baseline fix bad statistics?

No. A baseline freezes which plan a statement uses; it does not correct the optimizer cardinality estimate. If you pin a plan to work around stale or missing statistics, you have hidden that one symptom while every other statement against the same table still gets the wrong estimate. The correct order is to fix the statistics first — a histogram for a skewed column, extended statistics for correlated columns, a fresh gather where the data moved — and then capture a baseline of the good plan that results, so it is protected going forward. SPM is a stability tool, not a statistics tool.

Should I use SQL plan baselines before an Oracle upgrade?

Yes — an upgrade is one of the strongest use cases. A new optimizer version can change plans for statements that were previously fine, and some of those changes are regressions. If you capture baselines from the current release before upgrading — for example by loading plans from AWR history into a SQL tuning set and then into baselines — the upgraded optimizer inherits an accepted plan for each of those statements and cannot silently regress them on day one. New and potentially better plans are still found and parked as candidates, so you can evolve them deliberately after the upgrade rather than discovering regressions in production.

What is the difference between a SQL plan baseline and a SQL profile?

A SQL plan baseline stores one or more complete, accepted execution plans and constrains the optimizer to use an accepted plan, which makes it a plan-stability mechanism. A SQL profile does not store a plan; it stores corrective information — essentially adjustment factors for the optimizer estimates — that helps the optimizer build a better plan, but it does not lock a specific plan in place. In practice you use a SQL profile to help the optimizer estimate correctly and a baseline to guarantee a specific proven plan is used. They can coexist: a profile improves the estimate while a baseline fixes the resulting plan.

SQL Plan Management is the last stop in the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows where the estimate went wrong, and statistics are usually why. Once you've done that work and the plan is right, a baseline is how you keep it right — the optimizer stays free to improve everything else, and the plan you fought for doesn't quietly unravel the next time the numbers move. Prove it end to end with the SQL Plan Management lab: watch a plan regress to a full scan when it's unprotected, and watch the baseline hold the line when it isn't.

Originally published at uptimearchitect.com.

Oracle Optimizer Statistics, Demystified

Uptime Architect — Mon, 17 Aug 2026 01:59:32 +0000

The optimizer doesn't guess. It calculates — and every number in that calculation comes from statistics you either gathered on purpose or left to rot.

That's the part the "the optimizer is dumb" stories always skip. Reading an execution plan, you find the line where the estimate parted ways with reality — the step that expected five rows and got five million, and dragged the whole plan down with it. That's not the optimizer being stupid. That's the optimizer doing correct arithmetic on a wrong number. It thought five rows because the statistics said five rows. Fix the plan and you're treating a symptom. Fix the statistics and the bad plan never gets built.

So here's the reframe: tuning statistics isn't a nightly ritual of gathering everything and hoping. Most of that is cargo cult — full re-gathers that burn a maintenance window to re-confirm numbers that hadn't moved. The actual skill is narrow. Gather the right things — a histogram where a column is skewed, an extended statistic where columns are correlated — keep them fresh where the data actually moves, and let the automatic job handle the rest instead of fighting it. Everything below is which numbers matter and how to keep them honest.

What the optimizer is actually reading

When the optimizer estimates how many rows a step returns — its cardinality — it's doing one sum: rows × selectivity. The row count and the selectivity both come from stored statistics, gathered by DBMS_STATS and kept in the data dictionary:

Table statistics — number of rows, number of blocks, average row length. This is the rows in the sum.
Column statistics — for each column: number of distinct values (NDV), low and high value, number of nulls, and density. This is where basic selectivity comes from: for col = :x, the optimizer assumes 1/NDV of the rows match.
Index statistics — height, leaf blocks, clustering factor (how well index order matches table order). This decides whether an index access is actually cheaper than a scan.
Histograms — the shape of a column's data when it isn't evenly distributed. Without one, the optimizer assumes every value is equally common. (More below.)

Get those right and the optimizer makes good choices. Feed it a stale row count, a missing histogram, or two columns it thinks are independent, and it makes a bad one — confidently, because the math checks out.

Histograms: when "1/NDV" lies

The 1/NDV assumption falls apart the moment a column is skewed — a few values far more common than the rest. If status is 99% CLOSED and 0.1% OPEN, but the optimizer only knows there are two distinct values, it estimates half the table for either one. A histogram records the real distribution so the optimizer stops assuming and starts knowing. You need one when a column is skewed and shows up in WHERE clauses — and Oracle's default METHOD_OPT of FOR ALL COLUMNS SIZE AUTO creates them only for exactly those columns, using its record of which columns get queried.

This is the single most common estimate bug, and it's the one the execution-plans lab reproduces end to end — a missing histogram turning a 500-row query into a full-table scan. So I won't relitigate it here. The second most common bug gets far less airtime and is just as destructive:

Correlated columns: the estimate that quietly collapses

Give the optimizer two predicates on the same table and, by default, it assumes the columns are independent — it multiplies their selectivities together. WHERE make = 'TOYOTA' AND model = 'CAMRY' becomes selectivity(make) × selectivity(model). That's fine when the columns really are unrelated. It's a disaster when they aren't — because every Camry is a Toyota. The model predicate already implies the make; multiplying by selectivity(make) a second time divides the estimate by the number of makes for no reason at all.

The result is a severe under-estimate — the mirror image of the skew problem. The optimizer thinks a few dozen rows match when tens of thousands do, so it reaches for an index and a nested loop that would be perfect for a few dozen rows and catastrophic for tens of thousands. You can't fix this with a histogram; neither column is individually skewed. You fix it by telling the optimizer the two columns travel together — an extended statistic on the column group:

-- create the column group AND gather it in one step
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SALES',
    tabname    => 'CARS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (make, model)');
END;
/

-- confirm the extension exists
SELECT extension_name, extension
FROM   user_stat_extensions
WHERE  table_name = 'CARS';

Now the optimizer stores a real NDV for the combination of make and model, and the estimate snaps to the truth. Extended statistics also cover expressions — if you query WHERE UPPER(last_name) = 'SMITH', a statistic on (UPPER(last_name)) gives the optimizer a real selectivity for the expression instead of a blind guess. (There's an automation for this too: the preference AUTO_STAT_EXTENSIONS, off by default, lets Oracle create column groups on its own from the predicates it sees. Useful, but I like knowing exactly which extensions exist rather than discovering them.)

The estimate that rots: stale, and out of range

The other way good statistics go bad is simply time. Stats are a snapshot; the data keeps moving. Two symptoms matter most:

Stale stats on a changing table. Oracle flags a table's stats stale once roughly 10% of its rows have changed since the last gather. Past that line, the optimizer is reasoning about a table that no longer exists.
The out-of-range predicate. This one's sneakier. Gather stats today, and the optimizer's high_value for order_date is today. Query tomorrow for order_date > SYSDATE - 1 and you're asking about values past the edge of what the stats know — so the optimizer estimates almost nothing matched and under-reads. On any ever-growing table (orders, events, logs), the newest and most-queried data is exactly the data the last gather never saw.

Both are why you don't just gather once and forget. But they're also why you shouldn't gather everything nightly — most tables don't cross the staleness line most nights. Which is what the automatic job is for.

Let the automatic job do the boring part

Modern Oracle gathers statistics for you, and it's good at it. The automatic optimizer statistics task runs in the maintenance window and gathers stats for any object whose stats have gone stale — not every object, just the ones that moved. Two newer pieces make it sharper, both from 19c on:

High-frequency automatic statistics — a lightweight task that checks for stale objects every 15 minutes rather than waiting for the nightly window, so fast-moving tables don't spend all day on yesterday's numbers.
Real-time statistics — the optimizer maintains basic stats as conventional DML runs, so a table loaded this morning isn't invisible until tonight's gather. (Availability varies by edition and platform — know whether yours has it.)

Your job is mostly to not get in the way. The DBMS_STATS defaults are the product of two decades of the optimizer team's scar tissue — AUTO_SAMPLE_SIZE (a fast, accurate scan rather than a guessed sample percent) and SIZE AUTO (histograms only where they're earned). Overriding them with a hand-picked estimate_percent or a blanket SIZE 254 is how people make stats worse while feeling productive. Leave the defaults, and reach for manual gathering only in the specific cases the automation can't see:

-- a good manual gather looks like the defaults, on purpose
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SALES',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE);
END;
/

Two overrides that are worth knowing. Lock the stats on a volatile or staging table so the automatic job doesn't gather it mid-load and catch it half-empty — you set representative stats once and freeze them:

EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'STAGING_LOAD');

And when you must re-gather a critical table but can't risk a plan regression, use pending statistics — gather them, test them in your own session, and publish only if they behave:

EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');   -- lands as PENDING, unused by other sessions
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;  -- test the new stats, just for you
-- happy? publish. unhappy? delete them, no one else ever saw them.
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES', 'ORDERS');

Is it actually your statistics?

Before you gather anything, confirm the stats are the problem. Two checks:

-- how old, how big, and does Oracle think they're stale?
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM   user_tab_statistics
WHERE  table_name = 'ORDERS';

Then the same check that reading any plan comes down to: run the statement with /*+ GATHER_PLAN_STATISTICS */, pull the plan with DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'), and compare E-Rows to A-Rows. If they track, your stats are fine and the work is genuinely large — a re-gather won't help. If they diverge by an order of magnitude, you've found the estimate to fix, and this post is the menu of why it's wrong. Reading that plan is the subject of its own post (Oracle Execution Plans, Decoded); statistics are where the fix usually lands.

Same loop as reading a plan, one level deeper: find the estimate that's wrong, then match the fix to the reason it's wrong. A re-gather is one branch, not the whole tree.

Don't take my word for it — run it. The optimizer-stats lab builds a million-row table where model determines make — perfectly correlated — and joins it to a second table. It runs WHERE make = ... AND model = ..., captures the real ALLSTATS LAST plan, and asserts the under-estimate — the optimizer multiplies the selectivities, expects ~200 rows, and picks a nested loop join that's right for a couple hundred rows and a disaster for the ~10,000 that actually match. Then it creates an extended statistic on the (make, model) column group, re-gathers, re-runs, and asserts the plan flipped to a hash join with E-Rows ≈ A-Rows. (It turns adaptive plans off first, so you see what the estimate alone decides — the runtime safety net is a different lesson.) If the under-estimate doesn't reproduce, or the column group doesn't correct it, the run fails. The whole before/after is proven on every CI push.

What teams get wrong

Gathering everything, every night. A full re-gather of tables that didn't change is a maintenance window spent re-confirming yesterday. Let the automatic task gather what's stale; intervene only where it can't see.
Overriding the defaults to feel productive. A hand-picked estimate_percent or a blanket SIZE 254 usually makes stats slower to gather and worse. AUTO_SAMPLE_SIZE and SIZE AUTO are the right answer far more often than not.
Never creating extended statistics. The correlated-columns under-estimate is invisible until you know to look for it — and no amount of re-gathering the individual columns fixes it. A column group does.
Deleting histograms because "they cause plan instability." The instability is usually a bind-peeking or sampling issue, not the histogram itself. Removing a histogram a skewed column needs just trades a visible problem for a quieter one.
Gathering stats, then not testing. On a critical table, a re-gather is a plan change. Use pending statistics to try it in one session before it becomes everyone's plan.
Leaving volatile and staging tables to the automatic job. Caught mid-load, they get stats that describe a table that only exists for ten minutes a day. Lock representative stats instead.

Frequently asked questions

What are optimizer statistics in Oracle?

Optimizer statistics are stored descriptions of your data that the cost-based optimizer uses to estimate how many rows each step of a query will process and how expensive each access path is. They include table statistics (row count, block count, average row length), column statistics (number of distinct values, high and low values, nulls, density), index statistics (height, leaf blocks, clustering factor), and histograms (the distribution of values in a skewed column). The optimizer combines these into a cardinality estimate; if the statistics are wrong or stale, the estimate is wrong and the plan can be poor even though the optimizer reasoned correctly.

How often should I gather optimizer statistics in Oracle?

For most tables, let the automatic optimizer statistics task do it. It runs in the maintenance window and gathers statistics only for objects whose statistics have gone stale (roughly 10% of rows changed), rather than re-gathering everything. From 19c, high-frequency automatic statistics can check for stale objects every 15 minutes, and real-time statistics maintain basic stats during DML. Gather manually only in specific cases the automation cannot handle well: right after a bulk load, on a volatile or staging table (where you usually lock stats instead), or when you need to create extended statistics. Blanket nightly re-gathers of unchanged tables waste resources without improving plans.

What are extended statistics and when do I need them?

Extended statistics are statistics on a group of columns or on an expression, rather than a single column. You need column group statistics when two or more columns are correlated and are used together in WHERE clauses — for example make and model, or country and city. By default the optimizer assumes columns are independent and multiplies their selectivities, which badly underestimates cardinality when the columns are related. A column group gives the optimizer a real number of distinct values for the combination. Expression statistics do the same for a function such as UPPER(last_name). Create them with DBMS_STATS, for example method_opt of FOR COLUMNS (make, model).

Why does Oracle underestimate rows when I filter on two related columns?

Because by default the optimizer treats predicates on different columns as independent and multiplies their selectivities. If you filter on make = TOYOTA and model = CAMRY, it multiplies the selectivity of make by the selectivity of model — but every Camry is already a Toyota, so the make predicate adds no additional filtering. Multiplying by it anyway divides the estimate by the number of makes for no reason, producing a large underestimate. The optimizer then picks a plan suited to far fewer rows than actually match, such as an index range scan and nested loop where a full scan and hash join were correct. Extended statistics on the column group fix the estimate.

What is the difference between a histogram and extended statistics?

A histogram describes the distribution of values within a single column, so the optimizer knows that some values are far more common than others rather than assuming an even spread. You need one when a single column is skewed and appears in predicates. Extended statistics describe a relationship the optimizer cannot see from single-column stats: either a correlation between multiple columns (a column group) or the selectivity of an expression. They solve different problems — a histogram fixes a per-value skew underestimate or overestimate on one column, while a column group fixes the underestimate caused by treating correlated columns as independent. Some queries need both.

Should I change the DBMS_STATS default parameters?

Usually not. The defaults — AUTO_SAMPLE_SIZE for the sample size and FOR ALL COLUMNS SIZE AUTO for method_opt — are the result of extensive tuning by the optimizer development team. AUTO_SAMPLE_SIZE gives near-full-scan accuracy at a fraction of the cost, and SIZE AUTO creates histograms only for columns that are skewed and actually used in predicates. Overriding them with a fixed estimate_percent or a blanket histogram size such as SIZE 254 typically makes gathering slower and the resulting statistics less accurate. Change preferences deliberately and per-table when you have a specific reason, not as a global habit.

What are pending statistics and how do I use them?

Pending statistics let you gather new statistics without immediately exposing them to the optimizer, so you can test them before they affect everyone. Set the table preference PUBLISH to FALSE, gather statistics (they are stored as pending, and other sessions keep using the old published stats), then in your own session set optimizer_use_pending_statistics to TRUE and run your critical queries to check the plans. If the new statistics produce good plans, publish them with DBMS_STATS.PUBLISH_PENDING_STATS; if not, delete them and no other session was ever affected. This is the safe way to re-gather statistics on an important table where a plan regression would be costly.

Why should I lock statistics on some tables?

You lock statistics on tables whose contents change so dramatically and so often that any automatically gathered snapshot is misleading — typically staging, load, or global temporary tables that are empty for most of the day and full for a few minutes. If the automatic statistics job happens to run while such a table is empty or half-loaded, the optimizer gets statistics describing a state the table is almost never in, and plans that reference it go wrong. Instead you gather representative statistics once, when the table holds a typical working set, and lock them with DBMS_STATS.LOCK_TABLE_STATS so the automatic job leaves them alone.

Statistics are the layer underneath every plan the optimizer builds, which is why they sit at the root of the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows you where the estimate went wrong, and statistics are usually why. Don't gather more — gather what's earned: a histogram for skew, a column group for correlation, fresh numbers where the data actually moves, and the automatic job for everything else. Prove the correlated-columns fix end to end with the optimizer-stats lab, and the next time a plan goes wrong, you'll know whether to read the plan or fix the number behind it. And once the number is right and the plan is good, SQL Plan Management is how you keep it that way — pinning the plan so the next re-gather can't quietly undo the fix.

Originally published at uptimearchitect.com.

Oracle Execution Plans, Decoded: The One Number That Matters

Uptime Architect — Wed, 12 Aug 2026 16:16:38 +0000

EXPLAIN PLAN is a liar. Not on purpose — it just tells you what the optimizer hopes will happen, never what did. It's a forecast, printed with the confidence of a receipt.

And a plan is nothing but forecasts stacked on forecasts. The optimizer guesses how many rows each step will produce, and every choice after that — which table to lead with, whether to use an index, nested loop or hash join — rests on the guess before it. Get the first estimate wrong and the whole plan tips over: it thought a step would return five rows, it returned five million, and it picked a strategy that's a catastrophe at that scale. That's what a slow query almost always is. Not a mysterious optimizer mood — one estimate that missed, and a plan that trusted it.

So here's the part that changes how you tune: reading the estimates is nearly useless, because the estimate is exactly the thing that's wrong. The skill is reading the estimate against reality — putting the number the optimizer guessed next to the number it actually got, and finding the line where they diverge. Do that and the slow query stops being a mystery. Everything below is how.

Stop reading the guess. Get the real plan.

EXPLAIN PLAN, AUTOTRACE, the plan tab in your IDE — they all show you the estimate without ever running the statement. Worse, the plan they show you isn't guaranteed to be the plan that runs: bind variables get peeked, the real cursor may have been built for a different value, and EXPLAIN PLAN doesn't peek at all. You can spend an afternoon tuning a plan the database never actually uses.

Run the statement and ask the database what it actually did. Two pieces: the GATHER_PLAN_STATISTICS hint (or STATISTICS_LEVEL = ALL for the session), which tells Oracle to count real rows as it goes, and DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST format, which prints those counts next to the estimates for the statement you just ran:

SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(amount)
FROM   shop.orders
WHERE  status = 'OPEN';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

That gives you a plan with columns the estimate-only tools can't:

E-Rows — rows the optimizer estimated a step would return.
A-Rows — rows it actually returned.
Starts — how many times the step ran (crucial for nested loops).
Buffers — logical I/O, the truest measure of work done (more on this below).

This is the difference between a weather forecast and looking out the window. Everything that follows is reading that window.

The one number: E-Rows vs A-Rows

If you learn to read exactly one thing in a plan, read this. Go down the plan and compare E-Rows to A-Rows on every line. Where they track each other, the optimizer understood the data. Where they diverge by an order of magnitude or more, you've found the lie — and it's almost always the root of the slowness, because every operation above that line was planned for the wrong number of rows.

------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.02 |    8616 |
|  1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.02 |    8616 |
|* 2 |   TABLE ACCESS FULL| ORDERS |      1 |    500K|    500 |00:00:00.02 |    8616 |
------------------------------------------------------------------------------------

Line 2 is the whole story. The optimizer estimated 500K rows for status = 'OPEN' and got 500 — a thousandfold overshoot. Believing half the table matched, it did the sensible thing for that belief and full-scanned. But only 500 rows matched, so it read over eight thousand buffers to find a few hundred rows an index would have fetched in ten. The full scan isn't the bug. The 500K estimate is the bug; the full scan is just what a rational optimizer does when you feed it a bad number.

For nested loops, apply the same idea with Starts: the inner step's real output is Starts × A-Rows. A step showing Starts = 50000 is an inner probe that ran fifty thousand times because the optimizer thought the outer row source would return a handful. Same disease — a low estimate on the driver — different symptom.

Read it inside-out

Plans read like nested parentheses, not top to bottom. The most-indented line runs first; a parent consumes what its children produce. To find where a plan goes wrong, start at the leaves (the table and index accesses), walk outward, and watch two columns: the A-Rows that balloon and the Buffers that accumulate. The line where the row count first explodes past its estimate is your driving row source — the step that set the plan's fate. You don't need to understand all forty lines of a hairy plan. You need the one where reality parted ways with the forecast.

When a full scan is right — and when it's a symptom

A full table scan is not a failure. Reading 40% of a table, it's faster than an index — an index would mean 400,000 scattered single-block reads where a scan does clean multi-block ones. The optimizer switches between index access and full scan on selectivity: how much of the table your predicate keeps. Below roughly a few percent, the index wins; above it, the scan does.

Which means a full scan is only a symptom when the optimizer reached for it because it misjudged selectivity — the exact case above, where a missing histogram made a 0.05%-selective predicate look 50%-selective. So don't reflexively "add an index" or paste in an INDEX hint. A hint that forces the index papers over the wrong estimate and leaves a landmine for the next value. Fix the number, and the optimizer picks the index on its own — for every value, not just the one you tested.

While you're on that line, read its predicate section (DISPLAY_CURSOR prints it under the plan). It splits into access predicates — what an index used to find rows — and filter predicates — what got applied after, throwing rows away. A fat filter where you expected an access predicate means the index isn't doing the work you think it is: rows are being read and then discarded, not skipped.

Buffers, not seconds

The instinct is to chase A-Time. Resist it. Elapsed time lies — it drops when data's cached, spikes when the box is busy, and changes every run. Buffers — logical I/O, the count of buffer accesses — is the work the query actually asked for, and it's stable across warm cache, cold cache, and a loaded server. When you tune, watch Buffers fall. A rewrite that "feels faster" but moves the same Buffers just cached the blocks; a rewrite that cuts Buffers 100× is genuinely less work, and it'll still be less work at 2am under load. Time is the thing users feel; Buffers is the thing you can trust while you're fixing it.

Why the estimate was wrong

Once E-Rows and A-Rows point you at the guilty line, there's one question left: why did the optimizer believe that? Almost always one of these:

Stale or missing statistics. The optimizer is reasoning about a table that no longer exists — last week's row counts, yesterday's high value. Re-gather with DBMS_STATS and the estimate often just fixes itself.
Column skew with no histogram. The classic. The optimizer assumes values are spread evenly across a column, so for status = 'OPEN' it divides row count by the number of distinct values. If OPEN is 0.05% of the table but there are two distinct values, it estimates 50%. A histogram on the column tells it the truth about the distribution. (This is the worked example below.)
Correlated columns. Two predicates the optimizer treats as independent when they aren't — WHERE make = 'Toyota' AND model = 'Camry'. It multiplies the two selectivities and lands far too low, because every Camry is a Toyota. Extended statistics on the column group teach it the correlation.
A function on the column. WHERE UPPER(name) = 'ACME' or an implicit type conversion (a number column compared to a string) makes the column unsargable — the index can't be used and the estimate falls apart. Rewrite to leave the column bare, or add a function-based index.
Bind peeking meets skew. With bind variables, the optimizer peeks at the first value and builds a plan for it — great until the next value has wildly different selectivity and inherits a plan built for someone else. Adaptive cursor sharing exists to catch this; skew plus binds is where it earns its keep.
A predicate past the edge of the stats. Query for a date newer than the newest value the stats know about and the optimizer estimates almost nothing matched — so it under-reads. Common on ever-growing tables between stats gathers.

Modern Oracle fights back on your behalf: adaptive plans (12c and up) let the optimizer switch join methods mid-flight when the real row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai spots a plan regression as it happens and reinstates a known-good plan automatically. Both are real, and both are worth having on. But they're a safety net under the trapeze, not a reason to skip the routine: they catch some falls, they don't make your statistics tell the truth. The durable fix is still to correct the number the optimizer started from.

Watch it happen

Take the plan from earlier and give the optimizer what it was missing. Before, with no histogram on status, it split the table evenly between two values and estimated 500K rows for OPEN — so it full-scanned. Gather a histogram:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SHOP',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS SIZE 254 STATUS');  -- build the histogram it was missing
END;
/

Run the same query again, pull the same ALLSTATS LAST plan, and the guilty line has changed its mind:

--------------------------------------------------------------------------------------------------
| Id | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |               |      1 |        |      1 |00:00:00.01 |      10 |
|  1 |  SORT AGGREGATE                      |               |      1 |      1 |      1 |00:00:00.01 |      10 |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS        |      1 |    500 |    500 |00:00:00.01 |      10 |
|* 3 |    INDEX RANGE SCAN                  | ORD_STATUS_IX |      1 |    500 |    500 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

Now E-Rows is 500 and A-Rows is 500. The estimate matches reality, so the optimizer reaches for the index on its own — no hint, no forcing — and Buffers collapse from 8,616 to 10. (The elapsed times barely move, because a million cached rows scan fast — which is exactly why you read Buffers, not the clock. On a table too big to sit in memory, that Buffers gap is the difference between milliseconds and minutes.) You didn't tune the query. You corrected the one number it was wrong about, and the right plan fell out.

One loop, not a hundred knobs: get the real plan, find the line where estimate and reality part ways, fix the reason the estimate was wrong — not the symptom the optimizer picked because of it.

Don't take my word for it — run it. The execution-plans lab stands up an Oracle Database Free container, builds a million-row table where OPEN is deliberately rare, and gathers stats without a histogram. It runs the query, captures the real ALLSTATS LAST plan, and asserts the misestimate is there — a full scan with E-Rows a thousandfold over A-Rows. Then it gathers the histogram, re-runs, and asserts the plan flipped to an index range scan with E-Rows ≈ A-Rows. If the misestimate doesn't reproduce, or the fix doesn't correct it, the run fails. The whole before/after is proven on every CI push, not asserted in prose.

What teams get wrong

Tuning the estimate-only plan. EXPLAIN PLAN shows a guess and sometimes not even the guess that runs. Tune the plan the database actually executed — ALLSTATS LAST — or you're tuning fiction.
Reading E-Rows and stopping. The estimate is the suspect, not the evidence. Its value is only in the gap between it and A-Rows.
Forcing an index with a hint. A hint fixes today's value and hides the real defect — a broken estimate — until the next value walks into the same trap. Fix the stats; let the optimizer choose.
Chasing seconds. Elapsed time moves with cache and load. Chase Buffers; that's the work that doesn't lie between runs.
Blaming the optimizer. It's not moody. Given honest numbers it makes good choices; given a skewed column with no histogram it makes a bad one for a good reason. Feed it the truth.
Gathering stats blindly and hoping. A plain re-gather fixes stale numbers but not skew, not correlation, not a function on a column. Read why the estimate was wrong, then pick the matching fix.

Frequently asked questions

What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?

EXPLAIN PLAN produces the optimizer's estimated plan without executing the statement, and because it does not peek at bind variables it can even show a different plan than the one that actually runs. DBMS_XPLAN.DISPLAY_CURSOR shows the plan for a cursor that really executed, and with the ALLSTATS LAST format it prints actual row counts (A-Rows), start counts, and logical I/O (Buffers) next to the optimizer's estimates (E-Rows). For tuning you want DISPLAY_CURSOR with real execution statistics, because the estimates alone are exactly what tends to be wrong.

How do I get actual row counts (A-Rows) in an Oracle execution plan?

Either add the /*+ GATHER_PLAN_STATISTICS */ hint to the query, or set STATISTICS_LEVEL = ALL for the session, so Oracle counts real rows as the statement runs. Then display the plan with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')). The output adds A-Rows (actual rows per step), Starts (how many times each step ran), and Buffers (logical I/O) alongside the estimated E-Rows, so you can compare what the optimizer predicted against what actually happened.

What does it mean when E-Rows and A-Rows are very different?

It means the optimizer's cardinality estimate for that step was wrong, and that is usually the root cause of a bad plan. The optimizer chooses join methods, join order, and access paths based on how many rows it expects each step to produce; if it expects five rows and gets five million (or the reverse), every decision above that line was made for the wrong scale. Find the plan line where E-Rows and A-Rows diverge by an order of magnitude or more, and you have found the estimate to fix.

Why is Oracle choosing a full table scan instead of my index?

Usually because it estimates your predicate matches a large fraction of the table, which would make a full scan genuinely faster than many single-block index reads. If the estimate is correct, the full scan is the right choice. If the estimate is wrong — often because a skewed column has no histogram, so the optimizer assumes even distribution — fix the estimate rather than forcing the index with a hint. A histogram, fresh statistics, or extended statistics will let the optimizer pick the index on its own for every value, not just the one you tested.

What are E-Rows, A-Rows, Starts, and Buffers in DISPLAY_CURSOR output?

E-Rows is the estimated number of rows the optimizer expected a step to return. A-Rows is the actual number it returned at run time. Starts is how many times that step executed — important for nested loops, where the inner step runs once per outer row, so its true output is Starts multiplied by A-Rows. Buffers is logical I/O, the number of buffer accesses the step performed, which is the most reliable measure of work because it does not vary with caching or server load the way elapsed time does.

Should I use a hint to fix a bad execution plan?

Usually not as the first move. A hint that forces an index or a join method fixes the specific case in front of you but masks the underlying cause — typically a wrong cardinality estimate — and can produce a worse plan for a different bind value or as the data grows. Diagnose why the estimate is wrong (stale statistics, missing histogram, correlated columns, a function on a column) and correct that. Then the optimizer makes the right choice for all values. Hints are a last resort or a temporary stabilizer, not the fix.

What is a histogram and when do I need one?

A histogram is a statistic that describes how values are distributed within a column, rather than assuming they are spread evenly. You need one when a column is skewed — a few values appear far more or far less often than the rest — and it is used in WHERE-clause predicates. Without a histogram the optimizer divides the row count by the number of distinct values, which badly misestimates selectivity for rare or dominant values. Gather one with DBMS_STATS.GATHER_TABLE_STATS using METHOD_OPT such as FOR COLUMNS SIZE 254 column_name.

Do adaptive plans and Real-Time SPM mean I can stop tuning statistics?

No. Adaptive plans (from 12c) can switch join methods at run time when actual row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai can detect a plan regression as it happens and automatically reinstate a known-good plan. Both reduce the blast radius of a bad estimate, but neither makes your statistics accurate — they react to problems rather than prevent them. Correct statistics still produce better first plans across the whole workload; the adaptive features are a safety net beneath that, not a replacement for it.

Reading a plan is the third skill in the same performance discipline as the other two: an AWR report tells you which SQL is expensive across the whole database, wait events tell you what a session is stuck waiting on, and the execution plan tells you why a single statement is doing too much work. Start at the top, narrow to the statement, then open the plan and find the one line where the estimate and reality part ways. Prove the whole loop end to end with the execution-plans lab — a misestimate you can watch reproduce, and a histogram you can watch fix it.

Originally published at uptimearchitect.com.

Oracle Unified Auditing Without the Noise

Uptime Architect — Sun, 09 Aug 2026 21:15:15 +0000

Most Oracle audit trails are useless, and they fail in one of two ways. Either nobody ever enabled anything, so when a breach investigation asks "who altered that account," the answer is a shrug. Or someone once turned on everything — every statement, every user, every object — and the trail became a firehose that filled SYSAUX, slowed the database, and got quietly ignored until it was disabled again. Neither of those is auditing. One is blindness; the other is noise you can't read.

The skill isn't auditing more. It's auditing the few things that actually matter — privilege abuse, credential attacks, changes to your security configuration — in a trail you can trust and actually read. Oracle's Unified Auditing makes that easy, and there's a deadline attached: as of Oracle Database 23ai, traditional auditing is desupported. If your process still leans on AUDIT ... writing to AUD$, it has an expiry date. Here's how to do auditing right, and what changes underneath you as you move to 23ai.

The short version. Unified Auditing (the model since 12c) writes every audit record to a single protected, read-only internal table owned by AUDSYS, and you turn it on through policies, not statement by statement. Keep the baseline Oracle pre-enables — ORA_SECURECONFIG (privileged and structural changes) and ORA_LOGON_FAILURES — then add a few narrow policies of your own, not a firehose. 19c ships in mixed mode (traditional AUD$ and unified both active); move to pure unified auditing by relinking the binary (uniaud_on) and restarting. In 23ai, traditional auditing is gone — unified is all there is. Read the trail in UNIFIED_AUDIT_TRAIL; keep it from growing forever with DBMS_AUDIT_MGMT.

What Unified Auditing actually changed

Before 12c, auditing was a scatter of destinations — AUD$ in the database, FGA_LOG$ for fine-grained policies, OS files, XML files, the SYS operations audit — each configured differently and each with its own gaps. Unified Auditing collapses all of it into one trail and one way to configure it. Three properties make it worth adopting on purpose:

A single, protected trail. Every record lands in a read-only internal table in the AUDSYS schema. You cannot INSERT, UPDATE, or DELETE it with SQL — not even as SYS. An attacker who compromises a privileged account can't quietly scrub their tracks the way they could when audit rows lived in a normal table. You read it through the UNIFIED_AUDIT_TRAIL view.
Policy-based, not statement-by-statement. You define audit policies — named bundles of actions, privileges, or roles to watch — and enable them, optionally scoped to specific users. It's declarative and far easier to review than a pile of individual AUDIT statements.
Better performance, by default. Unified records are written in queued (asynchronous) mode: they buffer in the SGA and flush to disk in batches, rather than forcing a write on every audited action. (That buffering has one practical consequence for the impatient — see "reading the trail" below.)

The baseline is already on — don't turn it off

Here's the part that surprises people: a modern Oracle database is already auditing the right security-critical actions, because Oracle pre-enables a sensible baseline. Two policies do most of the work:

ORA_SECURECONFIG — audits the actions that change your security posture: CREATE/ALTER/DROP USER, GRANT, REVOKE, ALTER SYSTEM, CREATE/DROP of directories and database links, and more. This is enabled by default from 12.2 onward.
ORA_LOGON_FAILURES — audits failed logons, which is exactly the fingerprint of a credential attack or a misconfigured application hammering the listener.

Check what's actually enabled right now — the first query every audit review should start with:

SELECT policy_name, enabled_option, entity_name
FROM   audit_unified_enabled_policies
ORDER  BY policy_name;

If ORA_SECURECONFIG isn't in that list, someone disabled it, and that is your finding — a database that isn't recording who changes its own security is a database you can't investigate. The baseline is the floor, not the ceiling, but it is a floor most estates already have for free. Don't rip it out in the name of "reducing overhead"; the overhead is negligible and the coverage is exactly the high-value set.

Audit the few things that are specific to you

On top of the baseline, add narrow policies for the things that matter in your database — a sensitive table, a powerful privilege, a specific service account. This is where discipline pays: every policy you add is a policy someone has to read, so add few and make them sharp.

-- Watch reads of one sensitive table (and who did them)
CREATE AUDIT POLICY aud_salary_access
  ACTIONS SELECT, UPDATE, DELETE ON hr.employee_salary;
AUDIT POLICY aud_salary_access;

-- Watch any use of a skeleton-key system privilege
CREATE AUDIT POLICY aud_any_table
  PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
AUDIT POLICY aud_any_table;

Policies can be scoped and conditioned so they capture signal, not volume: AUDIT POLICY aud_x BY app_user limits it to one account, ... WHENEVER NOT SUCCESSFUL records only the failed attempts (often the interesting ones), and an audit_condition on a policy lets you fire only when a column or context matches. The instinct to "audit all DML on everything" is the firehose again — resist it. A handful of targeted policies plus the baseline is a trail a human will actually read during an incident.

Mixed mode, pure mode, and the 23ai cliff

Whether unified auditing is the only audit system running depends on your release, and this trips people up during upgrades.

12c–19c ship in mixed mode. Both the old traditional auditing (AUDIT → AUD$) and unified auditing are active. Mixed mode exists so you can adopt unified auditing gradually without breaking legacy scripts. It also means your audit data is split across two systems — the thing unified auditing was supposed to end.
Pure unified auditing turns traditional auditing off entirely. Moving to it isn't a SET — you relink the Oracle binary with unified auditing on and restart the instance:

-- database down, then from $ORACLE_HOME/rdbms/lib:
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
-- restart; it's reversible with uniaud_off if you must go back

23ai removes the choice. As of Oracle Database 23ai, traditional auditing is desupported. A freshly created 23ai database has no traditional audit at all — unified auditing is the audit system. On an upgrade, any traditional audit settings you were still using are carried forward for compatibility, but the direction is one-way: new work goes to unified, and the old model is on its way out. The practical takeaway — if your compliance runbook still says "AUDIT this, check AUD$," it needs rewriting before you reach 23ai, not after.

Getting Unified Auditing right is three decisions, not a hundred: keep the baseline, add a few sharp policies, and know which audit mode your release actually runs.

Prove your policies actually capture what you think. The unified-auditing lab stands up an Oracle Database Free container, enables the baseline plus a custom policy on a sensitive table, then triggers the real events — a failed login, a GRANT, a read of the protected table — flushes the audit buffer, and queries UNIFIED_AUDIT_TRAIL to assert every one was recorded. If a policy silently isn't capturing, the run fails. It's the difference between "we have auditing" and "we checked."

Reading and keeping the trail

A trail you never read is theater. Reading unified auditing is one view — UNIFIED_AUDIT_TRAIL — filtered by what you care about:

-- failed logons in the last day (credential-attack fingerprint)
SELECT event_timestamp, dbusername, userhost, return_code
FROM   unified_audit_trail
WHERE  action_name = 'LOGON' AND return_code <> 0
AND    event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER  BY event_timestamp;

Two operational details keep this working. First, because records are written in queued mode, the newest ones may still be in the SGA buffer, not yet in the view — force them out with DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL before you trust "nothing's there" (the lab does exactly this before it checks). Second, the trail grows forever unless you manage it: set a retention window and a scheduled purge with DBMS_AUDIT_MGMT so the audit table doesn't quietly consume your tablespace.

-- keep 180 days, then schedule automatic cleanup
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '180' DAY);
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type       => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name => 'unified_purge',
    use_last_arch_timestamp => TRUE);
END;
/

What teams get wrong

Auditing everything. "Audit all actions on all objects" is the firehose that gets muted and then disabled. Enable the baseline, add a few sharp policies, and review them. Coverage you never read is worse than none — it's cost with no benefit.
Turning off the baseline "for performance." ORA_SECURECONFIG is cheap and its coverage is exactly the security-critical actions. Disabling it is how a database loses the ability to explain who changed its own configuration.
Assuming traditional auditing still works forever. It's desupported in 23ai. A runbook built on AUDIT statements and AUD$ queries breaks on the upgrade you haven't scheduled yet.
Living in mixed mode by accident. On 19c, "we use unified auditing" is often only half true — the old system is still running alongside it, splitting your trail. Decide to go pure, and relink.
Forgetting to purge. The unified trail grows without bound. No retention policy means an audit table that eventually pressures SYSAUX/its tablespace — set a DBMS_AUDIT_MGMT purge job on day one.
Not flushing before you conclude "nothing happened." Queued-mode records lag. Flush the buffer before you trust an empty result during an investigation.

The one-paragraph version

Auditing isn't about volume, it's about signal. Use Unified Auditing — one protected, read-only trail in AUDSYS, configured by policies. Keep the baseline Oracle already enabled (ORA_SECURECONFIG + ORA_LOGON_FAILURES) and add a few narrow policies for your sensitive tables and powerful privileges — never a firehose. Know your mode: 19c runs mixed (traditional and unified together), you move to pure by relinking uniaud_on and restarting, and 23ai desupports traditional auditing outright, so rewrite any AUD$-based runbook before you get there. Read it through UNIFIED_AUDIT_TRAIL (flush the buffer first), and keep it bounded with a DBMS_AUDIT_MGMT purge job. A handful of policies you actually read beats a trail of everything you don't.

Frequently asked questions

What is the difference between traditional and Unified Auditing in Oracle?

Traditional auditing (the AUDIT command writing to SYS.AUD$, plus separate destinations for fine-grained auditing, OS files, and SYS operations) is configured statement by statement and scattered across multiple trails. Unified Auditing, the model since Oracle 12c, consolidates everything into a single protected, read-only internal table in the AUDSYS schema and is configured through named policies you enable or disable as a unit. Unified Auditing is easier to manage, harder to tamper with, and higher performance because records are written asynchronously. As of Oracle Database 23ai, traditional auditing is desupported.

Is Unified Auditing enabled by default in Oracle?

Partly. Unified Auditing as a system is available in every 12c and later database, and Oracle pre-enables a baseline of predefined policies — most importantly ORA_SECURECONFIG (security-relevant actions such as CREATE/ALTER USER, GRANT, and ALTER SYSTEM) from 12.2 onward, and ORA_LOGON_FAILURES for failed logons. However, in 12c through 19c the database runs in mixed mode, meaning traditional auditing is still active alongside unified auditing. Running pure unified auditing (unified only) requires relinking the Oracle binary and restarting.

How do I enable pure Unified Auditing?

Pure Unified Auditing is enabled by relinking the Oracle executable with unified auditing turned on, not by a parameter. Shut the database down, then from $ORACLE_HOME/rdbms/lib run: make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME, and restart the instance. After that, traditional auditing is disabled and only unified auditing records are produced. The change is reversible by relinking with uniaud_off. In Oracle Database 23ai this is moot because traditional auditing is desupported.

Where are Unified Audit records stored and can they be modified?

Unified audit records are stored in a read-only internal table owned by the AUDSYS schema, and you read them through the UNIFIED_AUDIT_TRAIL view. They cannot be changed with ordinary SQL — INSERT, UPDATE, and DELETE against the trail are not permitted, even for SYS. Records are managed only through the DBMS_AUDIT_MGMT package, which is used to archive and purge old records. This protection is a core reason to prefer unified auditing: a compromised privileged account cannot silently erase its own audit trail.

Why do my new audit records not appear in UNIFIED_AUDIT_TRAIL immediately?

Because unified audit records are written in queued (asynchronous) mode by default: they buffer in the SGA and are flushed to the audit table in batches for performance, so the most recent actions can lag before they appear in the view. To force them out immediately — for example during an investigation before concluding that nothing was recorded — run DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL, then re-query. This buffering is normal and is the tradeoff that keeps auditing cheap.

What audit policies should I actually enable?

Keep the baseline Oracle already enables — ORA_SECURECONFIG for security-configuration changes and ORA_LOGON_FAILURES for failed logons — and add a small number of narrow policies for what matters in your database: access to a specific sensitive table, use of powerful ANY privileges, or activity by a particular service account. Scope and condition them (BY user, WHENEVER NOT SUCCESSFUL, or an audit_condition) so they capture signal rather than volume. Auditing everything produces a trail nobody reads; a handful of sharp policies plus the baseline is what actually helps in an incident.

How do I stop the unified audit trail from growing forever?

Use the DBMS_AUDIT_MGMT package to set a retention window and a scheduled purge for the unified audit trail. Set a last-archive timestamp with DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and create a recurring cleanup with DBMS_AUDIT_MGMT.CREATE_PURGE_JOB for audit_trail_type AUDIT_TRAIL_UNIFIED. Without a purge policy the audit table grows without bound and eventually pressures its tablespace. Decide a retention period that satisfies your compliance requirement and automate the cleanup from the start.

Is traditional auditing really gone in Oracle 23ai?

Traditional auditing is desupported in Oracle Database 23ai. A newly created 23ai database has no traditional auditing; unified auditing is the audit system. If you upgrade a database that still had traditional audit settings, those settings are carried forward for compatibility, but new auditing work uses unified auditing and the traditional model should not be relied on going forward. Any process, script, or compliance runbook built on the AUDIT command and the AUD$ table should be migrated to unified auditing before adopting 23ai.

Auditing is the third leg of the security-and-ops discipline, alongside patching and hardening — patching closes known vulnerabilities, hardening closes the configuration gaps, and auditing is how you know when either one is being tested. Do it the same way you do the other two: not exhaustively, but deliberately — the few high-value policies, enabled, purged, and actually read. Prove yours capture what you think with the unified-auditing lab, and put the review on the same cadence as your quarterly patch.

Originally published at uptimearchitect.com.

The Oracle Hardening Checklist That Actually Matters

Uptime Architect — Sat, 01 Aug 2026 22:15:45 +0000

Open any Oracle security benchmark and you'll find two hundred–plus line items, each with a rationale, a check, and a remediation. It is thorough, it is auditable, and it is where most hardening projects go to die — three weeks of setting obscure parameters that shave a rounding error off your risk while the two gaps that actually get databases breached sit untouched because they weren't near the top of the alphabet.

Hardening is not a completeness exercise. A short list of controls stops the overwhelming majority of real incidents: accounts that still have their default password, privileges handed out for convenience, a listener open to the world, unencrypted data at rest, and no audit trail to notice any of it. Here's that list — in priority order, each with why it matters, how it actually gets exploited, and the one query or command that tells you where you stand.

The short version. Do these six, roughly in order. (1) Find and close accounts with default passwords — DBA_USERS_WITH_DEFPWD names them for you. (2) Take back what you granted for convenience: EXECUTE on the network packages (UTL_HTTP, UTL_TCP, UTL_SMTP) from PUBLIC, any %ANY% system privilege, and DBA on application accounts. (3) Lock the listener — valid-node checking, admin restrictions, and never on the public internet. (4) Turn on TDE so a stolen datafile or backup is useless. (5) Give the DEFAULT profile a failed-login lockout and a password verify function. (6) Enable Unified Auditing for the few things worth watching (ORA_SECURECONFIG + ORA_LOGON_FAILURES), not everything. Everything past this is refinement.

1. Kill the default passwords

This is number one because it is the single most reliably exploited weakness in an Oracle estate, and it is free to fix. Every attacker's first move against an Oracle listener is to try known account/password pairs — SYSTEM/manager, DBSNMP/dbsnmp, SCOTT/tiger, an app account whose password equals its username. It requires no exploit, no CVE, nothing but a dictionary.

You do not have to guess which of your accounts are exposed, because Oracle ships a view that checks the password hashes against its own list of known defaults:

SELECT username, account_status FROM dba_users_with_defpwd ORDER BY username;

Every row is an account an attacker can walk into. For each one: if you don't need it, lock and expire it; if you do, give it a real password. Sample schemas (HR, OE, SCOTT) shouldn't be in a production database at all — they're not installed by default anymore, so if they're present, someone added them.

ALTER USER scott  ACCOUNT LOCK PASSWORD EXPIRE;   -- don't need it → close it
ALTER USER dbsnmp IDENTIFIED BY "<a real secret>"; -- need it → stop using the default

While you're here, look at what's simply open and unused: SELECT username FROM dba_users WHERE account_status = 'OPEN' and lock every account no human or application actually logs in as. An account that can't authenticate can't be the way in.

2. Least privilege: PUBLIC, the ANY privileges, and DBA-for-convenience

Almost every over-privilege problem in Oracle traces back to a grant someone made to stop being asked about it. Three of them do most of the damage.

PUBLIC means every user, including the one an attacker just compromised. Historically Oracle granted EXECUTE on the network and file packages — UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_INADDR, UTL_FILE — to PUBLIC. Those packages let PL/SQL open outbound network connections and read files; in the hands of a low-privilege account that's been popped, they're an exfiltration and lateral-movement kit. Find what PUBLIC can execute that it shouldn't:

SELECT table_name, privilege
FROM   dba_tab_privs
WHERE  grantee = 'PUBLIC'
AND    table_name IN ('UTL_HTTP','UTL_TCP','UTL_SMTP','UTL_INADDR','UTL_FILE','DBMS_LOB');

Revoke from PUBLIC and grant back to the specific schemas that genuinely need it — usually far fewer than you expect.

The ANY privileges are a skeleton key. SELECT ANY TABLE, EXECUTE ANY PROCEDURE, ALTER ANY … — each one crosses every schema boundary in the database. They get granted to application accounts and reporting users because it was easier than enumerating objects. List the ones held by accounts you created (Oracle's own maintained accounts legitimately hold some):

SELECT p.grantee, p.privilege
FROM   dba_sys_privs p JOIN dba_users u ON u.username = p.grantee
WHERE  u.oracle_maintained = 'N'
AND    p.privilege LIKE '%ANY%';

DBA (or PDB_DBA) on an application account is the same mistake wearing a role. An app should own its objects and hold exactly the privileges its code needs — never the role that can read, alter, and drop everything. Check with DBA_ROLE_PRIVS filtered the same way, and replace the role with a tailored one.

3. Lock the front door: the listener and the network

The database can be perfectly hardened and still be trivially reachable if the listener in front of it isn't. Three controls matter more than the rest:

Valid-node checking. Restrict which hosts may even connect to the listener with TCP.VALIDNODE_CHECKING, TCP.INVITED_NODES, and TCP.EXCLUDED_NODES in sqlnet.ora. It's an allow-list at the network layer, before authentication.
Admin restrictions. Set ADMIN_RESTRICTIONS_<listener> = ON so nobody can reconfigure the listener remotely at runtime, and make sure it requires OS authentication for admin — a listener you can set remotely is a listener an attacker can set remotely.
Never on the public internet. A database listener on 0.0.0.0:1521 reachable from outside your network is the finding behind a large share of Oracle compromises. It belongs on a private subnet, behind a security group that allows only the application tier.

If you can encrypt the client-to-database traffic, do — either native network encryption in sqlnet.ora or TCPS/TLS. An unencrypted connection is credentials and data in cleartext on the wire.

4. Encrypt at rest: TDE

Everything above protects the running database. Transparent Data Encryption protects it when it isn't running — when a datafile, a backup piece, or a decommissioned disk leaves the building. Without TDE, a stolen datafile or RMAN backup is just a file an attacker strings; with it, it's noise without the keystore.

TDE encrypts at the tablespace (or column) level and is transparent to the application — no SQL changes. You set up a keystore once, then encrypt:

-- one-time: configure and open a keystore (WALLET_ROOT), then:
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "<pwd>" WITH BACKUP;
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

New tablespaces should be encrypted from creation. And TDE is what makes an RMAN backup safe to store off-site — pair it with the recovery discipline in the RMAN Recovery Runbook so the backup you can restore is also the backup you don't have to worry about losing.

5. Password profiles: make brute force expensive

Default-password hygiene (item 1) closes the known passwords. A failed-login lockout closes the guessable ones by making an online brute-force attack die after a handful of tries. This is one ALTER PROFILE on the DEFAULT profile — which nearly every account inherits — and it's often left wide open:

SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'DEFAULT'
AND    resource_name IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME','PASSWORD_VERIFY_FUNCTION');

If FAILED_LOGIN_ATTEMPTS reads UNLIMITED, an attacker can guess forever. Set a finite lockout, a password lifetime, and attach a verify function so weak passwords can't be set in the first place:

ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LIFE_TIME 180
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

(Balance the lockout against your reality: too aggressive a threshold plus a shared service account is a self-inflicted denial of service. Ten is a sane default; service accounts should use long, rotated secrets rather than relying on lockout at all.)

6. Audit the few things worth auditing

The reason most Oracle databases have no useful audit trail is that "audit everything" produces a firehose nobody reads, so eventually someone turns it off. Unified Auditing — the model since 12c, which writes to a protected internal table instead of OS files — makes selective, low-noise auditing easy through policies. You don't need dozens; you need the two that catch privilege abuse and credential attacks, which Oracle pre-defines:

-- what's actually enabled right now:
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies ORDER BY policy_name;

-- the sensible baseline:
AUDIT POLICY ora_secureconfig;    -- privileged actions, structural changes (on by default — keep it)
AUDIT POLICY ora_logon_failures;  -- failed logins: the brute force from item 5, recorded

ORA_SECURECONFIG is enabled by default in a modern database — the failure mode is someone having turned it off. From that baseline, add narrow policies for the things specific to you (access to a sensitive table, use of a powerful role) rather than broad ones. If you're still on the legacy AUDIT/AUD$ model, plan the move to pure Unified Auditing — it's faster and its trail can't be tampered with from outside the database.

Hardening in priority order: each step closes a class of real attack. Do them top to bottom — a later control on an unhardened account is polish on a door that's already open.

Run the checklist against a live database. The hardening-audit lab stands up an Oracle Database Free container in a deliberately weak state, runs ./run.sh audit to score it against these controls — default passwords, PUBLIC/ANY grants, DBA-on-app-accounts, the failed-login profile, and the auditing baseline — then ./run.sh harden fixes them and re-audits to prove every check flips from FAIL to PASS. The CI matrix runs the whole cycle on every push, so the numbers aren't a claim, they're a test.

What teams get wrong

Chasing the whole benchmark. Two hundred controls sorted by document order means the critical five compete for attention with the trivial. Prioritize by exploitability, not by checklist completeness.
Auditing everything, reading nothing. A firehose gets muted. Enable the two baseline policies plus a few targeted ones, and actually review them.
Leaving default and unused accounts open. DBA_USERS_WITH_DEFPWD and account_status = 'OPEN' take one query each. There is no excuse for an open account with a known password.
Granting DBA (or %ANY%) for convenience. The app that "just needs it to work" becomes the account that can read and drop every schema once it's compromised.
Treating hardening as one-time. Grants accumulate, accounts get created, someone disables a policy to debug and forgets. Configuration drifts; re-audit on a cadence, the same way you patch on a cadence.
Encrypting nothing because "it's internal." Internal networks get breached and backups get lost. TDE is the control that makes those events non-events.

The one-paragraph version

Skip the two-hundred-item benchmark and do the six that matter, in order. Close accounts with default passwords (DBA_USERS_WITH_DEFPWD finds them). Take back convenience grants — EXECUTE on the network packages from PUBLIC, every %ANY% privilege, and DBA on application accounts. Lock the listener with valid-node checking and admin restrictions, and keep it off the public internet. Turn on TDE so stolen datafiles and backups are useless. Give the DEFAULT profile a failed-login lockout and a verify function. Enable Unified Auditing for ORA_SECURECONFIG and ORA_LOGON_FAILURES, then add narrow policies — not a firehose. Then re-audit on a schedule, because hardening drifts. That list stops the incidents that actually happen; everything else is refinement.

Frequently asked questions

What is the most important first step in Oracle database hardening?

Closing accounts that still have a default or known password. It is the most reliably exploited Oracle weakness and requires no exploit — an attacker simply tries known username/password pairs against the listener. Oracle provides the DBA_USERS_WITH_DEFPWD view, which lists every account whose password matches a known default; lock and expire the ones you do not need and set real passwords on the ones you do. It costs one query and a few ALTER USER statements and removes the easiest way in.

Should I revoke EXECUTE on packages from PUBLIC?

For the network and file packages — UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_INADDR, UTL_FILE — yes, in most environments. Granted to PUBLIC, they let any authenticated account (including a compromised low-privilege one) open outbound network connections or read files, which is an exfiltration and lateral-movement capability. Revoke EXECUTE from PUBLIC and grant it back only to the specific schemas that genuinely require it. Test first, because application code occasionally relies on these grants.

What is the difference between traditional and Unified Auditing in Oracle?

Traditional auditing (the legacy AUDIT command writing to SYS.AUD$ or to OS files) is configured statement by statement and can be verbose and hard to protect. Unified Auditing, the model since Oracle 12c, consolidates all audit data into a single protected, read-only internal table and is configured through policies you enable or disable as a unit. Unified Auditing is faster, harder to tamper with, and easier to keep low-noise. Oracle recommends moving to pure Unified Auditing; enable the ORA_SECURECONFIG and ORA_LOGON_FAILURES policies as a baseline.

Does hardening an Oracle database require downtime?

Most of it does not. Locking accounts, revoking privileges, altering the DEFAULT profile, and enabling Unified Auditing policies are all online operations. Encrypting existing tablespaces with TDE can be done online in current releases, though the initial keystore configuration may involve a restart depending on how WALLET_ROOT is set. Listener and sqlnet.ora changes take effect on a listener reload, not a database restart. Plan TDE and network-encryption rollouts, but the high-value account and privilege work is all no-outage.

Is Transparent Data Encryption (TDE) necessary if my database is on an internal network?

It protects a different threat than network controls do. Listener and firewall rules protect the running database from unauthorized connections; TDE protects the data when it is at rest and leaves the running system — a stolen or misplaced datafile, an RMAN backup piece copied off-site, a decommissioned disk. Internal networks are breached and backups do get lost, so for any database holding sensitive or regulated data, TDE is the control that turns those events into non-events. It is transparent to the application.

How often should I re-audit an Oracle database against a hardening checklist?

On a defined cadence, the same way you patch — quarterly is a reasonable baseline, plus after any significant change. Hardening drifts: privileges get granted for a one-off task and never revoked, accounts get created, someone disables an audit policy to debug an issue and forgets to re-enable it. A one-time hardening project degrades steadily unless you re-check it. Automating the checks (a script or a lab-style audit that reports PASS/FAIL) makes the re-audit cheap enough to actually do.

What are the ANY privileges and why are they a hardening risk?

The system privileges containing ANY — SELECT ANY TABLE, EXECUTE ANY PROCEDURE, ALTER ANY TABLE, and others — grant an operation across every schema in the database, ignoring object ownership boundaries. They are commonly granted to application or reporting accounts as a shortcut instead of enumerating the specific objects needed. If such an account is compromised, the ANY privilege lets the attacker read, alter, or drop objects anywhere. Find them with DBA_SYS_PRIVS filtered to accounts where ORACLE_MAINTAINED = N, and replace them with grants on the specific objects the account actually uses.

Hardening and patching are the two halves of the security-and-ops discipline: patching closes the vulnerabilities Oracle tells you about, hardening closes the ones your own configuration opened. Neither is a one-time project — both are a cadence. Start with the six controls above, prove them with the hardening-audit lab, and put the re-audit on the same calendar as your quarterly Release Update.

Originally published at uptimearchitect.com.

Okroshka, Ice-Cold: I painted a Khokhloma bowl of cold soup in pure CSS (0 lines of JS — even the interaction)

Uptime Architect — Sat, 01 Aug 2026 16:40:27 +0000

This is a submission for Frontend Challenge - Comfort Food Edition, CSS Art.

Inspiration

Okroshka is the soup you eat when it's too hot to eat soup — a cold kvass (or
kefir) base over a confetti of diced cucumber, radish, potato and egg, buried
under more fresh dill than seems reasonable, with one proud dollop of smetana
in the middle.

Mine comes with a memory. When I was six, my parents took me haymaking in
the water meadows. At noon we would hide in the dense shade of a fresh
haystack, and my father would pull a jar wrapped in a thick towel out of a
canvas bag: ice-cold okroshka my mother had made back at dawn. The cucumbers
crunched, the sharp homemade kvass stung the tongue, and every sip drove the
exhaustion away. (The full memory lives in my sister entry, linked below.)

The scene I painted is the okroshka of my imagination's summer table:
a Khokhloma bowl — that black-lacquered Russian wooden ware painted with
gold leaves and red berries — on a red lacquer saucer, over a green gingham
tablecloth, with rye bread, a painted wooden spoon, and a little bowl of
smetana standing by.

Demo

Click (or Tab + Space) the bowl to stir the smetana into the soup. The
cream sweeps a full turn and a half through the dill, the herb blanket gets
dragged along with it, and it all settles back slowly when you let go.

There is no JavaScript anywhere on this page — not for the art, and not
for the interaction. The stir is a visually-hidden (but fully keyboard-real)
checkbox, a :has() selector, and one registered @property transition.

Journey

Some of the tricks I'm proudest of, in the order they saved me:

The curved rim bands ("ellipse shingles"). A Khokhloma bowl has a red lip
and a gold band that curve with the rim. Horizontal gradient stripes can't
bend — so each band is the same ellipse as the bowl opening, dropped a couple
of cqi lower. Only its curved front crescent peeks out from behind the
ellipse above it. Three stacked ellipses, a perfectly curved rim.

The dill blanket. The photo-real signature of okroshka is a surface
buried in chopped dill. Mine is five overlapping speckle patterns with
deliberately co-prime-ish background-size tiles (2.3×1.7, 1.7×1.3,
2.9×2.1…) so the grids never visually align, a tiny blur() to melt them
into herb texture, and a radial ring mask that opens the center for the
smetana dollop.

Stirring a flat ellipse. Rotating the dill layer in screen space swings
it out of the soup plane like a slab (I know because I shipped it for five
minutes). The fix is the old animator's trick — squash → rotate → unsquash:
transform: scaleY(0.25) rotate(38deg) scaleY(4) stretches the ellipse into
a circle, rotates it in that space, and squashes it back. The silhouette
never moves; the texture swirls in the plane of the soup.

The smetana swirl. A conic-gradient comma masked into a ring, whose start
angle is a registered custom property:

@property --swirl { syntax: '<angle>'; inherits: true; initial-value: 0deg; }
.stage:has(.stir:checked) .soup { --swirl: 540deg; }
.soup { transition: --swirl 1.6s cubic-bezier(0.3, 0.7, 0.2, 1); }

Registration is the whole trick — an unregistered custom property would snap
instead of sweeping. Unchecking transitions back over 2.4s, because cream
settles slower than it stirs.

Dill fronds with mask-composite. A frond is a repeating-conic fan of
needle rays intersected with a 96° wedge — without the wedge the fan is a
360° starburst (also shipped that for five minutes).

Accessibility, because art is still a page. The scene is one
role="img" figure with alt text written like a photo caption. The checkbox
and its label live outside the figure (role="img" flattens its subtree
for assistive tech), the label announces "Stir the sour cream into the soup",
and focus draws a gold ring around the bowl's lip. Every animation and
transition lives inside @media (prefers-reduced-motion: no-preference) —
reduced-motion visitors get a finished still life, and the stir still works
as an instant state change.

Everything is sized in cqi container units, so the same fragment drops
into any page at any width — which is exactly what my sister entry does:
Dear Okroshka — a love letter to a cold soup, a full landing page for the same dish.

Code is MIT-licensed.

Dear Okroshka: a love letter to a cold soup and a landing page that switches sides with zero JavaScript

Uptime Architect — Sat, 01 Aug 2026 16:36:13 +0000

This is a submission for Frontend Challenge - Comfort Food Edition, Perfect Landing

What I Built

The prompt allowed "a love letter to a regional dish," so I took it
literally: Dear Okroshka is a single-page letter written to the cold
Eastern European summer soup — with a salutation, enclosed evidence (it's on
an actual Russian postage stamp), an anatomy of the bowl, a recipe you can
check off while cooking, and a sign-off.

Okroshka is my dish because of one memory: the shade of a fresh haystack at
noon during haymaking, and a jar wrapped in a thick towel — ice-cold
okroshka my mother had made at dawn, sharp kvass stinging the tongue, the
air full of dill. That memory, nearly in full, is the letter's centerpiece
on the page.

The centerpiece is the argument every okroshka household has: kvass or
kefir? The page doesn't just describe the feud — it takes sides. A native
radio group re-themes the entire page (colors, content strings, even the
recipe's first ingredient) between an amber rye world and a cool milky one.

The whole switch is CSS:

body:has(#base-kefir:checked) {
  --bg: #F5F8F2; --ink: #25322B; --accent-strong: #2C5A3A; /* …every token */
}
body:has(#base-kefir:checked) .only-kvass { display: none; }

JavaScript is optional on this page. The ~60 lines it ships add two
enhancements: scroll-reveal (gated on both an html.js class and
prefers-reduced-motion: no-preference, so no-JS and reduced-motion visitors
see everything with zero flash) and a polite role="status" announcement
when the base changes. Turn JS off and the page still re-themes, the
ingredient cards still open, the recipe still checks off.

Demo

Journey

Why the switch is a radio group, not a toggle. My first sketch was an
aria-pressed button. But neither kvass nor kefir is "off" — a switch is
the wrong mental model, and a screen reader would announce it misleadingly.
A native radio group in a fieldset gives exclusivity, arrow-key behavior and
"selected, 1 of 2" announcements for free — and it's what makes the zero-JS
theming possible at all. The accessible choice and the clever choice turned
out to be the same choice.

Accessibility was a judging criterion, so I treated it as a feature:

Landmarks + labeled sections, one h1, no heading skips; skip link.
Both themes' text tokens are contrast-verified: every body-text pair ≥ 4.5:1 (kvass ink 11.66:1, muted 5.97:1; kefir ink 12.48:1, muted 5.87:1).
The ingredient explorer is native <details> — keyboard and screen-reader operable with zero ARIA bolted on. The recipe steps are real labeled checkboxes; the done-state is strikethrough + the checkbox itself, never color alone.
Reduced motion: no reveal offsets, no smooth scroll, no theme transition.
Forced colors (Windows High Contrast): the selected base keeps a double-border so it survives color removal.
Lighthouse accessibility: 100.

Real photography, licensed properly. The food photos are from Wikimedia
Commons (CC BY / CC BY-SA / public domain), credited inline under each
figure and in the footer — including a matched pair of kvass and kefir cups
by the same photographer for the debate, a 2016 Russian okroshka postage
stamp, and a July 1977 photo of a street kvass barrel with its queue.
Choosing photos that share warmth turned out
to be half the design system.

No webfonts, no frameworks, no build. The type system is two stacks:
a Palatino-class serif for the letter's voice, system sans for the UI layer.
The contrast between them is the typography. One fluid clamp() scale, one
token sheet, five labeled CSS layers.

The sister entry. The gingham ribbon under the hero is a hand-off: the
same table setting exists as a pure-CSS painting — Okroshka, Ice-Cold — my entry for the CSS Art prompt.
Same dish, two languages.

Code is MIT-licensed.

The transaction that committed in the past: how I diagnosed one bug six times in an evening

Uptime Architect — Sat, 01 Aug 2026 13:26:17 +0000

This is a submission for DEV's Summer Bug Smash: Smash Stories powered by Sentry.

There is a special kind of bug that doesn't crash anything. No exception, no error log, no red dashboard. The system hums along, green across the board — and quietly throws away your data.

This is the story of one of those, in a change-data-capture engine I build in Rust — a system that reads Oracle's redo logs byte-by-byte, off the database, and replicates committed transactions to warehouses and streams. Its one sacred promise: if Oracle committed it, we deliver it.

One June evening, a test proved the promise was a lie. Over the next three hours and thirteen minutes I diagnosed the bug six times. Only the first was right — and I dismissed it, then chased four wrong theories, caught myself in an embarrassing hex-arithmetic error halfway through, and circled all the way back to where I'd started. The trail of wrong turns is preserved, in real time, in my commit history, and I'm going to walk you through all of it, timestamps and all, because the wrong turns are the story.

Up front, so you know what you're reading: the engine is closed-source, so there's no repo to hand you. But every commit message I quote below is real — lifted verbatim from that evening's git history, wrong arithmetic and all. Internal tool and ticket names are the only things I've changed.

The setup

Two weeks earlier I had shipped the engine's real-time mode: instead of waiting for Oracle to archive a redo log and reading it cold, the tailer polls the live online redo log every few hundred milliseconds — decoding a file while Oracle is still writing it. Median commit-to-decoded latency: 238 ms. I was very proud of it.

Polling a live file means re-decoding an ever-growing window, which means seeing the same transactions again on every poll. So the tailer deduplicated across polls the obvious way: remember the highest commit SCN you've emitted (Oracle's System Change Number — a monotonically increasing clock stamped on every change), and on the next poll, skip everything at or below that floor.

Simple. Obvious. Wrong in a way that took me six theories — and a full circle back to the first — to prove.

20:03 — Theory #1: it's the dedup floor

A long-transaction test went red: open a transaction, leave it open while a dozen short transactions commit, then commit it. The engine captured the twelve short ones and silently dropped the long one. The archived-log path captured it fine — this was real-time-mode only.

My first commit that evening documented a suspicion that will sound very smart in about three hours:

"the windowed re-decode dedups by a scalar commit-SCN floor … the txn's insert is released under that prior (low) commit SCN, and once the floor passes it the row is pruned forever. … **Proper fix is a design change: dedup by transaction identity … not a scalar SCN floor."

I wrote down the correct fix, in the first twenty minutes, in the commit message. Then I spent the rest of the evening talking myself out of it — because my repro was dirty in a way I didn't yet understand, and clean-looking evidence was about to point somewhere much scarier.

20:17 — Theory #2: no, it's the core decoder

I tightened the repro with unique per-run markers, re-ran, and the new evidence said the dropped row's commit_scn equaled its insert SCN — not its real commit SCN. Worse: the batch path decoding the very same log showed the same wrong value. This wasn't a real-time dedup quirk. This was the core decoder stamping the wrong commit SCN on any transaction held open while others commit.

I escalated it in the log: core decode bug, affects every path, do not rush the fix. The dedup-floor theory from 14 minutes ago? Officially a red herring, "misled by cross-run contamination." (Hold that phrase.)

20:27 — Theory #3: a single wrong byte

If the decoder stamps the wrong SCN, find where. I dumped the raw redo with ALTER SYSTEM DUMP LOGFILE and went byte-level. (A held-open transaction is an IMU — in-memory undo — transaction: Oracle buffers its redo privately and flushes begin + insert + commit as one unit at commit time.) The dump was unambiguous: the row's true SCN was 0x150b4a1 everywhere it mattered, but the decoder emitted 0x150b2a1 — a value that appears nowhere in the log. One byte off. My commit concluded the parser was mis-framing the record, and helpfully wrote out the decimal conversions of both hex values.

One of those conversions was wrong. Nobody noticed. Especially not me.

20:31 — Theory #4: the byte is fabricated downstream

Four minutes later, an offline probe over the preserved log killed Theory #3: the parser framed the record perfectly. Every change-vector SCN in that record was correct. The mystery value 0x150b2a1 matched no header anywhere in the file — so it wasn't mis-read, it was fabricated somewhere downstream in event assembly. I listed three candidate functions and narrowed the hunt.

I was now hunting the origin of a value that did not exist. For a very good reason.

20:42 — Theory #5: the pivot

"That was a HEX-CONVERSION ERROR on my part: 0x150b4a1 = 22066337 (the CORRECT commit SCN), not 0x150b2a1. The held-open IMU transaction actually decodes CORRECTLY."

There it is, verbatim, the most embarrassing commit message I have ever written — and the most valuable. Fifteen minutes earlier, doing arithmetic in my head at 20:27, I had converted 0x150b4a1 to the wrong decimal and then pinned the correct decimal onto the wrong hex string. The decoder had been right all along. The "single wrong byte" was in my head. The batch path was fine; the caveats I'd splashed across three docs got withdrawn.

Which meant the bug was real-time-only after all… and I still hadn't found it. Two reasoned fixes had already failed — implemented, 140 tests green, repro still red, reverted. My commit ends with a change of method that decided the whole hunt:

"the next step is an OFFLINE poll-simulation over the preserved corpus to pin the exact drop and iterate the fix fast (not rebuild-and-pray)."

21:52 — Theory #6: the ghost with four timestamps

I spent the next seventy minutes building instead of guessing: I split the tailer's dedup core into a pure function an offline test could drive, and built a serializer that dumps a live mid-write window — the exact bytes the tailer saw mid-poll — to a file that replays deterministically offline.

The offline simulation immediately proved the sealed decode and the dedup path were both correct on any static snapshot. And the live trace showed something spectacular: the same id=1000 row appearing poll after poll with a different, increasing commit SCN each time — 22087937 → 22089678 → 22090175 → 22090911. One row, four commit timestamps. A transaction whose commit kept moving.

New theory, stated with great confidence: when a held-open transaction's own commit record is still beyond the write head, the assembler mis-attributes its commit SCN to the latest visible commit. The row is right, the metadata is wrong, the floor prunes it. Q.E.D.

It's a beautiful theory. It survived exactly eighty-four minutes.

23:16 — What the NOTE column said

The test table had a NOTE column I'd been ignoring — each test run writes a unique random marker into it. I finally dumped it for those four "re-attributed" rows:

r1854822674_long, r878629041_long, r348217387_long, X3647_long.

Four different markers. Four different runs.

Oracle's online redo logs are circular — a "new" log is a recycled one, still holding valid-looking blocks from previous incarnations. Every run of my test did DROP TABLE … PURGE, CREATE, insert id=1000. So four runs' worth of stale id=1000 rows were sitting in the log, landed in one decode window, and read as "one row whose commit SCN keeps changing." There was no re-attribution. There was no moving commit. The centerpiece evidence of Theory #6 was four different transactions wearing the same primary key.

And with the ghosts exorcised, the live trace of the current run finally showed the actual smoking gun, small and undramatic:

held-open txn:  commit_scn = 22103071   ← correct, its own, real
dedup floor:                 22103185   ← already past it

A transaction held open while others commit gets its commit record written after theirs — so it becomes readable last with an SCN from the past. It commits earlier in SCN time, later in file time. By the time its commit surfaced in the window, the scalar floor — advanced by the younger transactions that beat it into the file — had already rolled past it, and the dedup pruned it as "already seen."

Theory #1. From 20:03. The one whose correct fix I wrote into my first commit message and then abandoned because a dirty repro made better theories look true. The bug was never in the decode, never in the framing, never a wrong byte, never a mis-attribution. It was the floor, all along, exactly as first suspected — only now proven, with a trace that distinguished this run's transaction from its four dead ancestors.

The fix

Dedup by transaction identity, not by a scalar SCN watermark. The tailer now tracks emitted: HashMap<TxId, commit_scn>, self-bounded each poll to transactions still decodable in the window (anything that aged out of the circular log can never reappear, so it needn't be remembered). A transaction is emitted iff its commit TxId hasn't been — so an out-of-SCN-order held-open commit can never be pruned by younger neighbors:

CaptureMessage::Commit { tx_id, scn } => {
    let already = emitted_txns.contains_key(tx_id);   // identity, not an SCN floor
    if !already {
        emitted_txns.insert(tx_id.clone(), scn.0);
        group.push(m);
        out.append(&mut group);
    }
    group.clear();
}

The regression test encodes the whole evening in fourteen lines: poll 1 emits a transaction committed at SCN 200; poll 2 surfaces a held-open commit at SCN 150 — below the highest already emitted — and asserts it must still come out:

let out2 = select_new_messages(poll2, &mut txns, &mut ddl);
assert_eq!(
    commit_scns(&out2),
    vec![150],
    "held-open commit (150) emitted though it is below the already-emitted 200"
);

The long-transaction gate flipped from KNOWN-FAIL to a mandatory passing gate: 2/2, three consecutive runs, on a live containerized Oracle 23ai. 145 unit tests green, p95 poll latency 248 ms — the fix costs nothing measurable.

And the offline window-replay tool, built at 21:52 to chase a bug that turned out not to exist? Its inability to reproduce the drop on any static snapshot was the strongest clue that the decode was innocent. Sometimes the most useful thing a tool can tell you is "not here."

What I keep from that evening

The log remembers your old experiments. Circular buffers, recycled logs, reused primary keys — any repro that can't distinguish this run from previous runs will eventually hand you fiction. One unique marker per run turned three hours of ghost-hunting into one SELECT.
Give your first theory the same rigor as your last. I wrote the correct diagnosis and the correct fix in the first commit — then abandoned them because contaminated evidence made a scarier theory look stronger. Theories aren't refuted by newer theories; they're refuted by clean evidence.
When rebuild-and-pray fails twice, stop and build a replay harness. The serializer + offline simulator took one focused hour and ended a hunt that guessing would have extended indefinitely.
Write the wrong turns down. Every one of these quotes is a real commit message from that evening, wrong arithmetic and all. That paper trail is why I can tell you this story — and why the post-mortem took ten minutes instead of a week.

The transaction that committed in the past ships on time now. Its commit SCN is lower than its neighbors'; its identity is its own; and there's a HashMap that will never forget it — for exactly one window, and not a poll longer.

What's the longest you've chased a bug that turned out to be your test harness lying to you? I want to hear the war story in the comments. 👇

This is one of my two Summer Bug Smash entries. The other is a Clear the Lineup fix — a year-old Zulip bug that six contributors tried before me — where the hard part wasn't the code, it was finishing what everyone else started.

Written up from that evening's git history (2026-06-18, 20:03–23:16). The engine is a from-scratch Rust CDC system that decodes Oracle redo logs off-database; this real-time path was already live in production, which is exactly what made a silent, deniable transaction drop worth three hours of hunting.

Zulip logged 'Successfully processed.' Your reply was already gone.

Uptime Architect — Sat, 01 Aug 2026 13:22:01 +0000

This is a submission for DEV's Summer Bug Smash: Clear the Lineup powered by Sentry.

You reply to an email notification from your team chat — a quick "sounds good" to a thread you missed. It never arrives. No bounce, no error, no red anything. And the server's own logs report, cheerfully, "Successfully processed email."

That reply had already been thrown away. And it kept happening for a full year — in a tool thousands of organizations trust — while six people tried and failed to fix it.

TL;DR — Replying by email to a group-DM notification silently dropped your message whenever a group member had been deactivated (while logging success). The fix is ~15 lines; the PR is #39766, now merged into Zulip's main. Sentry's Seer independently reconstructed my diagnosis from one captured event, and Gemini stress-tested my test coverage.

Project Overview

The feature at the center of this is Zulip's email gateway — Zulip being the open-source team chat thousands of organizations run themselves. Miss a message, get a notification email, and just reply to it: your reply is parsed and dropped straight back into the conversation. Brilliant — until the conversation is a group DM and one of its members has since been deactivated. Then your reply vanishes, and the log lies about it.

Bug Fix or Performance Improvement

Issue #35273, opened by a Zulip core maintainer in July 2025 — one year old this week. When someone replies by email to a group-DM notification and any member of that group has been deactivated, message validation throws:

django.core.exceptions.ValidationError:
    ["'deactivated-recipient@example.com' is no longer using Zulip."]
...
zerver.lib.exceptions.JsonableError:
    'deactivated-recipient@example.com' is no longer using Zulip.

The exception is caught deep in the internal send path and logged — so nothing crashes, nothing retries, and the sender is never told. The reply is simply gone. And here's the lie from the intro — my favorite detail of the whole bug. Right after eating your message, the mirror logs

INFO Successfully processed email from user 8 to ...

A success log for a message that was never delivered. That's the worst class of bug there is: silent data loss that every party — sender, server, and logs — believes succeeded. Nobody files a report for a message they don't know they lost, which is exactly why it hid in a shipping product for a year.

The web app can't hit this — it refuses to compose to a group containing a deactivated user at all. Only the email path, replying to a notification that predates the deactivation, can walk into it.

The bug, by the numbers: open 1 year · 6 contributors claimed it and drifted off · 4 PRs opened, 0 merged · the fix is ~15 lines of logic + 3 new tests · 75/75 email-mirror tests green · CI green on 5 Python versions (3.10–3.14) · now merged to main.

Behind those numbers is a pattern. Every one of the six claimants was auto-unassigned for inactivity; none of the four PRs cleared review. The most promising got a "Looks great!" from a maintainer with a short punch-list — use .values_list("email", flat=True), add a test where the conversation remains a group DM, extract the duplicated test setup into a helper, and also handle the 1:1 DM case — and then stalled in commit-style churn. So before writing a line, I read all four attempts and that review, and treated the punch-list as my spec.

The reading paid off with the key insight: the "1:1 case" no longer exists. Since the earlier attempts, Zulip finished migrating all direct messages — including 1:1s — to direct-message groups and dropped personal recipient rows entirely. The separate 1:1 fix the reviewer asked for in January is now structurally impossible and structurally unnecessary: one fix in the group branch covers everything.

Code

[ai] email_mirror: Ignore deactivated users in group DM email replies. #39766

pyaroslav posted on Jul 19, 2026

Fixes: #35273

Replying by email to a message notification email for a group direct message raised an unhandled JsonableError when any member of the group had since been deactivated, and the reply was silently lost (while the mirror still logged "Successfully processed email").

This filters the group's members to active users when constructing the recipient list, so the reply is delivered to the remaining active participants — consistent with the web app, which does not offer composing to such a group at all. If nobody but the sender remains active, the reply is dropped with an INFO log rather than an exception.

Two notes for review:

Since personal Recipient rows no longer exist (1:1 direct messages are direct message groups), the same branch covers the 1:1 case raised in the review of #37449; a separate commit for it is no longer meaningful.
The filtering query adds one database query to the reply path, so the query-count assertions in the two existing tests are updated from 22 to 23.

A possible follow-up (not included, to keep this scoped like the previously reviewed approach): notifying the sender via the notification bot when the reply is dropped, as send_mm_reply_to_stream does for channel replies.

Prior work on this issue: #36882, #37449 (whose review feedback is addressed here: values_list-based filtering, a test where the conversation remains a group DM, and the repeated test setup extracted into helpers), #38117, #38933.

How changes were tested:

[x] ./tools/test-backend zerver.tests.test_email_mirror.TestMissedMessageEmailMessages — 12/12 pass (re-run after rebasing on current main); the three new tests fail without the lib change (silent message loss, no drop log).
[x] ./tools/lint zerver/lib/email_mirror.py zerver/tests/test_email_mirror.py
[x] Coverage: zerver/lib/email_mirror.py shows no uncovered lines under this test class.
[x] Reproduced end-to-end in the dev environment (group DM → deactivate a member → email reply): before the fix the reply is lost; after, it is delivered to the remaining active members.

Screenshots and screen captures:

N/A (backend-only change).

Self-review checklist

[x] Self-reviewed the changes for clarity and maintainability (variable names, code reuse, readability, etc.).
[x] Followed the AI use policy.

Communicate decisions, questions, and potential concerns.

[x] Explains differences from previous plans (e.g., issue description).
[x] Highlights technical choices and bugs encountered.
[x] Calls out remaining decisions and concerns.
[x] Automated tests verify logic where appropriate.

Individual commits are ready for review (see commit discipline).

[x] Each commit is a coherent idea.
[x] Commit message(s) explain reasoning and motivation for changes.

Completed manual review and testing of the following:

[ ] Visual appearance of the changes.
[ ] Responsiveness and internationalization.
[ ] Strings and tooltips.
[x] End-to-end functionality of the changes.
[ ] Accessibility.
[ ] Theming.

View on GitHub

The whole change is about two dozen lines in zerver/lib/email_mirror.py (roughly half of them comments): drop the group's deactivated members before constructing the recipient list, deliver to whoever remains, and if nobody but the sender is left, log and drop instead of throwing. Here's the logic, comments elided:

elif recipient.type == Recipient.DIRECT_MESSAGE_GROUP:
    display_recipient = get_display_recipient(recipient)
    user_ids = [user_dict["id"] for user_dict in display_recipient]
    emails = list(
        UserProfile.objects.filter(id__in=user_ids)
        .filter(Q(is_active=True) | Q(is_mirror_dummy=True))
        .values_list("email", flat=True)
    )
    if len(emails) <= 1:
        logger.info(
            "Dropping message notification email reply from user %s to a group direct message with no active recipients",
            user_profile.id,
        )
        return
    recipient_str = ", ".join(emails)
    internal_send_group_direct_message(user_profile.realm, user_profile, body, emails=emails)

One subtlety in that query. The Q(is_active=True) | Q(is_mirror_dummy=True) keeps both active members and "mirror-dummy" users — inactive placeholder accounts that Zulip's own recipient validation still treats as valid recipients — while dropping genuinely deactivated members. A flat is_active=True filter would silently drop the mirror-dummy users; a reviewer caught exactly that on an earlier revision, which is why the filter mirrors the validation's rule instead. (My first cut also carried an .order_by("id") to pin the ordering; a reviewer pointed out it was unnecessary once the result only feeds a recipient set, so it's gone — the review made the code smaller, not just correct.)

Plus three new backend tests (delivered-and-stays-a-group-DM, keeps-a-mirror-dummy-member, only-sender-remains-so-drop) and the test-helper refactor the reviewer asked for — extracted into its own preparatory commit, per Zulip's commit discipline. The filtering adds exactly one indexed query, so the two existing tests' query-count assertions moved from 22 to 23 — stated in the PR rather than hidden.

My Improvements

The discipline was: make the deactivation tests fail on main for the right reasons before making them pass (the third test guards the opposite direction — that a valid inactive "mirror-dummy" member is kept, not over-filtered). On unfixed code they fail exactly as production does — recipients' latest message is still the original group DM (the reply was eaten), and the drop-path test finds the lying "Successfully processed" log where the graceful drop should be:

AssertionError: Actual and expected outputs do not match; showing diff.
- original group direct message
+ Reply with deactivated group member body

With the fix: the whole test_email_mirror module is green (75/75, my three new tests included), ./tools/lint (including mypy) clean, and coverage shows zero uncovered lines in email_mirror.py under this suite — including the drop path.

Design choice worth surfacing: when members are filtered out, the reply is delivered to the reduced group (the active members' conversation), matching what the previously-reviewed attempt did and what the maintainers endorsed in-thread. And when nobody but the sender remains, we log-and-drop rather than crash; a follow-up could notify the sender via Zulip's notification bot, the way stream-reply failures already do — I flagged that in the PR rather than scope-creeping into it.

Best Use of Sentry

Zulip ships Sentry integration natively (zproject/sentry.py) — enabling it in the dev environment took exactly one environment variable, which made a live before/after demo of this bug almost embarrassingly easy.

The whole loop, start to finish; the detail on each step is below.

Before: I ran the real scenario end-to-end — group DM, deactivate a member, reply by email through process_message() — with the unfixed code and a Sentry DSN. The swallowed JsonableError arrives in Sentry as a first-class error event with the full traceback from the issue:

The trace pins it exactly: except ValidationError as e: … raise JsonableError(e.messages[0]) in check_message — the reply is turned into an exception, caught upstream, logged, and dropped.

Seer: I connected my Zulip fork and pointed Sentry's Seer at the captured event. Its root-cause analysis independently landed on the same mechanism I'd fixed:

"The recipient list for the conversation still includes the deactivated user11, causing a JsonableError and silently dropping the email reply."

That's exactly the bug — and Seer reconstructed the reproduction steps (create group DM → deactivate a member → reply by email) matching the ones I'd used, from a single captured event. A nice independent check on the diagnosis before shipping the fix.

After: identical scenario, fixed code — the reply is delivered to the remaining active members, the success log now lists only the active recipients, no new event is captured, and I marked the issue resolved (you can watch the whole before→after in the recording above).

What sold me on the workflow: this bug's signature — error event + success log + missing message — is invisible in any one log file, but obvious the moment the exception becomes a first-class object in Sentry with grouping and history.

Best Use of Google AI

Green tests are a trap: they prove the cases you wrote pass, not the cases you forgot. So I handed Gemini 2.5 Flash (via the Google AI Studio API) the exact patch and my three test descriptions and asked the one question I can't ask myself objectively — what am I not testing?

It went straight for the boundary that worried me most, the len(emails) <= 1 drop path, and reasoned it out instead of hand-waving:

Sender is the only active member in a group DM — Correctly dropped and logged. len(emails) <= 1 correctly handles this, as the sender's email will be in emails.

That's the load-bearing assumption of the whole drop branch — the sender is always in the list, so "nobody left" means length 1, not 0. Then it flagged the email vs delivery_email question under restricted email-visibility — the exact trap I'd already hit building the repro, where the send path resolves recipients by .email, not .delivery_email — and correctly scoped it to the downstream function rather than crying wolf on my diff. It also named two recipient shapes my tests don't exercise (bot members, cross-realm members) and reasoned both already-correct, which is why I kept the PR scoped to the deactivation case instead of expanding it.

No bug fell out — and on a year-old issue that had already beaten six people, that was the win. Gemini bought me the thing you skip when the suite is green: a fast, adversarial second read that turns "my tests pass" into "I know exactly which cases I'm not covering, and why they're safe." The full prompt and response are saved alongside the code.

The takeaway

Six people had the right instinct on this issue; what was missing wasn't cleverness, it was finishing the job — reading the one review that already said exactly what to do, and acting on all of it. If you're hunting for a first open-source contribution, the highest-leverage move is often to pick up the issue everyone started and nobody closed. The trail of dead PRs isn't a warning sign — it's a spec. I followed it, and PR #39766 is now merged into main — the year-old issue closed with it.

Have you ever shipped (or inherited) a "Successfully processed" log line sitting right on top of a dropped message? Tell me the worst one in the comments. 👇

This is one of my two Summer Bug Smash entries. The other is a Smash Stories deep-dive — the data-loss bug I diagnosed six times in one evening — different chaos, same lesson about trusting your evidence over your theories.

OCI vs Oracle Database@Azure: Where Should Your Oracle Database Live?

Uptime Architect — Sun, 26 Jul 2026 20:36:40 +0000

You've decided the database is going to the cloud, and you've picked a migration method. Now comes the question that determines everything downstream — networking, contracts, operations, and what your 3am looks like: where does the database actually live? For most Oracle estates in 2026 that's a choice between OCI native and Oracle Database@Azure — and a third option people keep forgetting, the OCI–Azure Interconnect.

The wrong way to decide is by database features, because that's the trick of it: the database is the same in all three. Same Exadata, same 19c/26ai, same Data Guard, same RMAN. What changes is everything around the database — and that's where the decision really lives.

The short version. Database@Azure puts Oracle-operated Exadata hardware physically inside Azure datacenters: you provision from the Azure portal, latency to your Azure apps is same-building, and the spend burns down your Azure commitment (MACC). OCI native gives you the full Oracle cloud — every service, typically the lowest total cost — and is the obvious home when your apps aren't married to Azure. The Interconnect is the middle path: database in OCI, apps in Azure, under 2ms between them with no egress charges over the link. Decide by three things: where your apps run, who holds your commercial commitments, and how much latency the app-to-database path can afford. And the same template now works for AWS and Google Cloud — Database@AWS and Database@Google Cloud are the identical play on different hosts.

Three options, not two

1. OCI native. The database runs in Oracle's cloud — Exadata Database Service, Autonomous Database, Base Database, or plain compute — alongside the rest of OCI's catalog. One console, one bill, Oracle's infrastructure pricing.

2. Oracle Database@Azure. Oracle ships Exadata racks into Microsoft's datacenters and operates them there. You buy it in the Azure Marketplace, provision it from the Azure portal, and your apps in the same Azure region talk to it at in-datacenter latency. It is genuinely OCI — a slice of Oracle's cloud embedded in Azure — operated by Oracle's own OCI team, with support handled jointly by Oracle and Microsoft.

3. The OCI–Azure Interconnect. The database stays in OCI; your Azure workloads reach it over a private, pre-built link between paired OCI and Azure regions — a dozen region pairs, sub-2ms round-trip, and no ingress/egress charges over the link. (You may also find references to Oracle Database Service for Azure (ODSA), the 2022 portal-sync service built on this link — for new projects it's been superseded in practice by Database@Azure.)

Choosing where an Oracle database lives. Follow app gravity first, then latency, then commercials — the database itself is the same everywhere.

What Database@Azure actually is

The name undersells it. This isn't a connector or a managed VM — it's OCI hardware in Azure buildings:

The services: Exadata Database Service on Dedicated Infrastructure and on Exascale Infrastructure, Autonomous Database (Serverless and Dedicated), Base Database Service, plus GoldenGate and the Zero Data Loss Autonomous Recovery Service. Oracle Database 19c and the current 26ai are supported — 26ai being 23ai renamed (a single Release Update makes the jump, no upgrade or recertification), which is also why you'll increasingly see the branding Oracle AI Database@Azure.
The footprint: available in 33 Azure regions and climbing — it more than doubled in the last year.
The commercial model: purchased through the Azure Marketplace; consumption counts 1:1 toward your Microsoft Azure Consumption Commitment (MACC). Your existing Oracle licenses apply — BYOL or license-included, same as OCI.
The operating model: Oracle's OCI operations team runs the hardware, patching, and infrastructure; Microsoft runs the datacenter; you get Azure-portal provisioning with Entra ID identity and Azure-native monitoring hooks, while database internals still surface through an OCI console linked to your Azure tenancy.

That last point is the honest fine print: day-2 operations span two control planes. Provisioning, networking, and billing feel like Azure; the deep database machinery is still OCI underneath. Teams that expect a purely Azure experience find the seam; teams that know OCI find it familiar.

The decision factors

Factor	OCI native	Database@Azure	Interconnect (DB in OCI)
App-to-DB latency	in-OCI	same datacenter	< 2ms between paired regions
Buys down Azure MACC	No	Yes — 1:1	No (OCI spend)
Oracle license BYOL	Yes	Yes	Yes
Service breadth	Full OCI catalog	Core DB services + GoldenGate + ZRCV	Full OCI catalog
Regions	OCI's global footprint	33 Azure regions	12 paired regions
Consoles	OCI only	Azure portal + OCI (split)	Azure + OCI (two estates)
Typical total cost	Lowest	Parity on DB, Azure prices around it	Low DB cost + free link egress

App gravity beats everything. If the applications, integration layer, identity, and the team's tooling live in Azure — and the CIO's strategy says they stay there — the database follows the apps. Fighting app gravity with a cheaper database quote is how you end up owning a latency problem.

Commercials decide more of this than architects like to admit. A large enterprise with a MACC to burn and an Azure-first mandate has an easy business case for Database@Azure: the same Oracle spend now counts against the Microsoft commitment. Conversely, if you're not carrying Azure commitments, OCI native is usually the cheaper estate — Oracle's compute, storage, and egress pricing undercut the equivalent hyperscaler line items, and that's before you price the second cloud's networking.

Latency is the tiebreaker, not the headline. Sub-2ms over the Interconnect is fine for most applications — batch, reporting, services with sane call patterns. Where it isn't fine is chatty OLTP: an app that makes hundreds of sequential database round-trips per user action multiplies every one of those milliseconds. Measure your app's round-trip count before you let anyone tell you 2ms is nothing — wait events don't lie, and SQL*Net message from client on the database side of a chatty app is exactly where this shows up. (You can measure this multiplication yourself — see the lab below.)

When each one wins

OCI native wins when you're not anchored to Azure: greenfield builds, estates consolidating on Oracle's stack, Exadata Cloud@Customer hybrids, cost-driven migrations, and anywhere you want the whole OCI catalog next to the database. It's also the simplest operationally — one console, one support path, no seam.

Database@Azure wins when the application estate is committed to Azure and the app-to-database path matters: same-datacenter latency, MACC burn-down, Azure-portal provisioning, Entra ID, and Exadata/Autonomous without leaving the building. It's the answer that lets an Azure-first enterprise run real Oracle — not a compromise port — and every migration method works against it, because it's Exadata under the hood.

The Interconnect wins when you want OCI's pricing and catalog and Azure apps, and the app can tolerate single-digit milliseconds. Database in OCI, apps in Azure, private link, no egress fees over it. This is also the natural DR pattern: primary in one cloud, Data Guard standby across the link in the other — a cross-cloud failover story that doesn't require moving the estate.

And if your apps live on AWS or Google Cloud: the identical decision now exists there. Database@AWS and Database@Google Cloud are both generally available — Oracle-operated Exadata inside the other hyperscalers' datacenters — and AWS's own AWS Interconnect added OCI connectivity in preview in 2026. Swap the vendor names and this whole article still applies.

What teams get wrong

Deciding on database features. The database is identical in all three options. The decision is about apps, contracts, latency, and operations — never about the engine.
Ignoring the split control plane. Database@Azure provisioning is Azure; deep database operations are OCI. Budget for your team learning both, or the first incident will do the teaching.
Treating 2ms as free. For chatty OLTP, interconnect latency compounds per round-trip. Profile the app's call pattern first; move the database into the same building only if the numbers say so.
Leaving MACC on the table. If the company has an Azure commitment, Database@Azure spend counts toward it 1:1 — finance may like the "more expensive" option better than your spreadsheet does.
Forgetting the Interconnect exists. Not every Azure app needs the database in Azure. The middle path keeps OCI economics with Azure proximity — and nobody promotes it, because neither vendor's sales team owns it.
Assuming Azure ends the DR conversation. Wherever the database lands, it still needs a protection architecture — Data Guard across regions (or clouds), backups, and a tested restore.

The one-paragraph version

Follow the apps. If they're committed to Azure and the app-to-database path is chatty, put the database in the same building — Database@Azure, which is real Oracle-operated Exadata inside Azure, bought through the Marketplace and burning down your MACC. If they're committed to Azure but the path tolerates a few milliseconds, consider the Interconnect — database in OCI at OCI prices, sub-2ms away, no egress over the link. If the apps aren't anchored to Azure, OCI native is the default: full catalog, one console, lowest total cost. The engine is identical everywhere — so decide on app gravity, commercial commitments, and latency, in that order. The same template now answers the AWS and Google Cloud versions of this question, too.

Measure it before you decide it. The latency/ lab runs this post's chatty-vs-batched experiment on your own machine: Oracle Database Free in Docker, with tc netem injecting the 0ms / 2ms / 10ms of the three homes above. Same database, same rows — the chatty workload went from 0.7s to 3.6s to 11.8s as the latency rose, while the batched one barely moved (0.1s → 0.3s). The round-trip counts come from v$mystat, and the run fails unless the elapsed times actually match the injected delay — so it only passes if the math is real. Chatty apps don't have a cloud problem; they have a round-trip problem that latency exposes.

Want to try the OCI side for free? Oracle Autonomous Database on OCI Always Free gives you a real, fully managed 26ai database — no license, no server, no cost — and it's the same Autonomous service that runs inside Database@Azure. Standing one up is the fastest way to feel how the OCI half of this decision operates before you commit an estate to it.

Frequently asked questions

What is Oracle Database@Azure?

Oracle Database@Azure is Oracle-operated database infrastructure — Exadata Database Service (Dedicated and Exascale), Autonomous Database (Serverless and Dedicated), Base Database Service, GoldenGate, and the Zero Data Loss Autonomous Recovery Service — running on Oracle hardware physically located inside Microsoft Azure datacenters. You purchase it through the Azure Marketplace, provision it from the Azure portal, and your Azure applications reach it at in-datacenter latency. It is available in 33 Azure regions.

Who operates Database@Azure — Microsoft or Oracle?

Oracle. The hardware is owned, patched, and operated by Oracle Cloud Infrastructure personnel; Microsoft provides the datacenter, the Azure-side networking, and the portal integration. Support is handled jointly, so you can raise issues through either vendor. Day to day you provision from the Azure portal, while deeper database operations surface through an OCI console linked to your Azure tenancy.

Does Database@Azure spend count toward my Azure commitment (MACC)?

Yes. Oracle Database@Azure is purchased through the Azure Marketplace and consumption counts 1:1 toward a Microsoft Azure Consumption Commitment. For organizations carrying a large MACC, that often makes the business case by itself — the Oracle database spend reduces the Microsoft commitment.

Can I bring my own Oracle licenses to Database@Azure or OCI?

Yes, both. Database@Azure and OCI database services support bring-your-own-license (BYOL) as well as license-included pricing, so existing Oracle Database licenses and support agreements carry over to either destination.

What is the difference between Database@Azure and the OCI–Azure Interconnect?

Database@Azure puts the database physically inside the Azure datacenter — same-building latency, Azure Marketplace billing, MACC burn-down. The Interconnect keeps the database in OCI and links it privately to a paired Azure region at under 2 milliseconds round-trip with no ingress/egress charges over the link. The Interconnect preserves OCI pricing and the full OCI catalog; Database@Azure wins when the app-to-database path is latency-critical or the commercials favor Azure. The older Oracle Database Service for Azure (ODSA) portal service has been superseded in practice by Database@Azure for new projects.

Is Database@Azure more expensive than running in OCI?

The database services are priced comparably, but the estate around them usually is not: OCI compute, storage, and egress typically cost less than the Azure equivalents, so OCI native tends to have the lowest total cost. Database@Azure changes the calculation when a MACC is in play, because the spend burns down an existing Microsoft commitment — the accounting can favor it even when the sticker price does not.

How do I migrate an existing Oracle database into Database@Azure?

The same way you would migrate to Exadata in OCI — because it is Exadata. All the standard methods apply: Data Pump for simple moves with an outage, Data Guard for near-zero-downtime same-endian migrations with easy rollback, transportable tablespaces with RMAN CONVERT for very large or cross-endian moves, GoldenGate when you are crossing versions or need a fallback, and ZDM to automate whichever fits. Autonomous targets accept logical methods only.

Do Database@AWS and Database@Google Cloud work the same way?

Yes — both are generally available and follow the same model: Oracle-operated Exadata and Autonomous services physically inside AWS and Google Cloud datacenters, purchased through the respective marketplaces and counting toward those clouds' commitments. AWS's own AWS Interconnect service added OCI connectivity in preview in 2026. The decision framework — app gravity, commercial commitments, then latency — is identical; only the vendor names change.

Wherever the database lands, the architecture questions that follow are the same ones this blog keeps coming back to: which migration method gets you there with the downtime you can afford, which protection architecture keeps it alive once it's there, and whether you can prove a restore on the worst day. The cloud changes the building; it doesn't change the job.

Originally published at uptimearchitect.com. I write here in a personal capacity — questions or feedback are welcome via the contact page.