DEV Community: Uptime Architect

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, green across Zulip's CI matrix. 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).

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)
        .exclude(is_active=False, is_mirror_dummy=False)
        .order_by("id")
        .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)

Two subtleties in that query. The .exclude(is_active=False, is_mirror_dummy=False) drops deactivated members but keeps "mirror-dummy" users — inactive placeholder accounts that Zulip's own recipient validation still treats as valid recipients. A naive is_active=True filter gets that wrong and silently drops them; a reviewer caught exactly that, which is why the query mirrors the validation's rule instead. And .order_by("id") preserves the deterministic ordering get_display_recipient provides, since we no longer iterate it directly.

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.

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.

Oracle RMAN Recovery Runbook: Restore, Recover, Prove It

Uptime Architect — Wed, 22 Jul 2026 21:14:32 +0000

The worst day of an Oracle DBA's career is a restore they've never rehearsed. A datafile is gone, or a DELETE ran without a WHERE, or a storage array quietly corrupted a handful of blocks — and now the only thing between you and a résumé-updating outage is a backup you hope works and a procedure you think you remember.

This is the runbook for that day. It rests on one distinction most people fumble under pressure — restore is not recover — and it maps each failure to the specific commands that fix it, from a single corrupt block to a whole database rewound to the second before someone's mistake. Everything targets Oracle 19c, with notes for 23ai/26ai.

The short version. RESTORE copies datafiles back from your backups; RECOVER rolls them forward by applying redo. Most recoveries are RESTORE DATABASE then RECOVER DATABASE.
To rewind past a human error, use point-in-time recovery: SET UNTIL (time or SCN) at the top of a RUN block, then restore, recover, and OPEN RESETLOGS. For a few bad blocks, block media recovery fixes just those without touching the datafile. Lost the controlfile? NOMOUNT, SET DBID, RESTORE CONTROLFILE FROM AUTOBACKUP. And the rule under all of it: an untested backup is a hope, not a recovery plan.

Restore vs recover — the distinction that saves you

Two verbs, two completely different actions, and confusing them is how people make a bad day worse:

RESTORE copies datafiles back from your backups. It rewinds those files to whenever the backup was taken.
RECOVER takes those restored files and rolls them forward by applying archived and online redo — either all the way to the present, or to a point in time you choose.

You almost never restore alone; a restore on its own throws away every transaction since the backup. The
normal shape of a recovery is restore, then recover:

RMAN> RESTORE DATABASE;    -- copy datafiles back from the most recent backup
RMAN> RECOVER DATABASE;    -- apply redo to roll them forward to now
RMAN> ALTER DATABASE OPEN;

Hold that pair in your head — everything below is a variation on which files you restore and how far
you recover.

The failure → action map

You don't run the same command for a single corrupt block as for a lost database. Match the failure to
the smallest recovery that fixes it — that's what keeps your outage small:

What failed	What you run	Who's down
A few corrupt blocks	`RECOVER ... BLOCK` / `RECOVER CORRUPTION LIST`	those blocks only — DB stays open
One datafile / tablespace	`RESTORE` + `RECOVER DATAFILE`/`TABLESPACE` (online)	that datafile only
Many datafiles / whole DB	`RESTORE` + `RECOVER DATABASE` (mounted)	full outage
A bad `DELETE` / `DROP` (logical)	Flashback (fast) or point-in-time recovery / `RECOVER TABLE`	varies
Controlfile lost	`RESTORE CONTROLFILE FROM AUTOBACKUP`	full outage
SPFILE lost	`RESTORE SPFILE FROM AUTOBACKUP`	full outage

One datafile, without taking the database down

The best-case recovery, and the most common in real life: a single datafile or tablespace is lost while the rest of the database keeps serving. Take just that datafile offline, restore and recover it, bring it back — users on every other tablespace never notice.

RMAN> ALTER DATABASE DATAFILE 7 OFFLINE;
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> ALTER DATABASE DATAFILE 7 ONLINE;

(Swap DATAFILE 7 for TABLESPACE users to work at the tablespace level.) This is why restore speed matters more than most teams think — the typical recovery is one file, not the whole estate.

The whole database: restore then recover

If the database is down — many files lost, or you're rebuilding on fresh hardware — mount it and run the pair against everything:

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

This is complete recovery: every committed transaction, right up to the moment of failure, is back.
No RESETLOGS needed, because you recovered all the way to the present — the redo timeline is unbroken.

Rewinding past a bad DELETE: point-in-time recovery

Complete recovery brings you back to now — which is useless when now already contains the problem: a DELETE without a WHERE, a bad release, a truncated table that committed an hour ago. For that you need incomplete recovery — stop applying redo just before the mistake.

Here's the rule that catches people out: SET UNTIL goes at the top of a RUN block, before both RESTORE and RECOVER. Set it only before RECOVER and your restored files may already carry
timestamps past your target, and RMAN can't rewind them.

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2026-07-19 14:29:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

You can target a time, an exact SCN (SET UNTIL SCN 12345678 — no ambiguity about "which 14:29"), a log sequence, or a named restore point. When you know the SCN of the damage, use it — it's precise where a timestamp can be a second off in either direction.

OPEN RESETLOGS is mandatory after any incomplete recovery. It resets the online redo logs and starts a new database incarnation — a fresh redo timeline branching off the old one. Two consequences worth internalizing: take a full backup immediately afterward (your old backups now belong to a previous
incarnation), and know that RMAN can still navigate incarnations if you ever need to go back further.

Two faster paths when they apply:

Flashback Database (if you enabled it) rewinds the entire database in minutes with no restore at all — FLASHBACK DATABASE TO SCN 12345678;. It's the first thing to reach for on a logical error, and it's exactly why [The Oracle HA Decision Tree] https://uptimearchitect.com/blog/oracle-ha-decision-tree-rac-vs-data-guard/) pairs backups with Flashback as the only real answer to human error.
RECOVER TABLE (12c+) restores a single dropped or damaged table from your RMAN backups — Oracle spins up a temporary auxiliary instance behind the scenes, extracts just that table to a point in time, and imports it back. No full-database rewind, no downtime for everyone else over one table.

Just a few bad blocks: block media recovery

When storage corrupts a handful of blocks rather than a whole file, restoring the entire datafile is wildly disproportionate. Block media recovery repairs only the corrupt blocks — and the datafile stays online the whole time, so mean-time-to-recovery collapses.

RMAN records corrupt blocks in V$DATABASE_BLOCK_CORRUPTION during backups and VALIDATE. You can then fix everything on that list in one command:

RMAN> VALIDATE DATABASE;          -- populates V$DATABASE_BLOCK_CORRUPTION
RMAN> RECOVER CORRUPTION LIST;    -- repairs every block RMAN has flagged
-- or a single, known block:
RMAN> RECOVER DATAFILE 7 BLOCK 1234;

Two limits to know going in: block media recovery is an Enterprise Edition feature, and it repairs physical corruption only — it cannot fix logical block corruption (for that, restore/recover the
object or use Flashback). Catch corruption early by adding CHECK LOGICAL to your validates and backups: BACKUP VALIDATE CHECK LOGICAL DATABASE; scans for both physical and logical damage before it becomes a 3am page.

Losing the controlfile or the SPFILE

The controlfile is the map RMAN uses to find everything — lose every copy and you can't even MOUNT. If you have controlfile autobackup on (you should: CONFIGURE CONTROLFILE AUTOBACKUP ON;), recovery is mechanical — with one catch. Without a controlfile and without a recovery catalog, RMAN doesn't know
your DBID, and it needs the DBID to locate the autobackup:

RMAN> STARTUP NOMOUNT;
RMAN> SET DBID 320066378;                    -- from your records, not the database
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

A lost SPFILE follows the same shape (RESTORE SPFILE FROM AUTOBACKUP from NOMOUNT). The one thing to do today: write your DBID down somewhere outside the database. It's the piece you can't recover from the database when the database is precisely what you've lost.

The recovery decision: match the failure to the smallest recovery that fixes it. A corrupt block doesn't need a database restore; a human error doesn't need bare-metal recovery.

flowchart TD
  A{What failed?}
  A -- "A few corrupt blocks" --> B[Block media recovery<br/>RECOVER CORRUPTION LIST<br/>datafile stays online]
  A -- "One datafile / tablespace" --> C[Offline it, RESTORE + RECOVER<br/>the file, bring it online<br/>rest of DB stays up]
  A -- "Whole DB / new hardware" --> D[MOUNT, RESTORE + RECOVER<br/>DATABASE, OPEN<br/>complete recovery]
  A -- "Human error, already committed" --> E{Flashback enabled?}
  E -- Yes --> F[FLASHBACK DATABASE<br/>minutes, no restore]
  E -- No --> G[Point-in-time recovery<br/>SET UNTIL, RESTORE, RECOVER<br/>OPEN RESETLOGS]
  A -- "Controlfile / SPFILE" --> H[NOMOUNT, SET DBID<br/>RESTORE ... FROM AUTOBACKUP]

Prove it before you need it

Everything above assumes the backup you're restoring actually works — and most teams don't find out whether it does until the worst possible moment. Two RMAN commands close that gap without a full restore:

RESTORE DATABASE VALIDATE confirms the backups a restore would need exist and aren't corrupt — without writing a single datafile. Add CHECK LOGICAL to scan for logical corruption too.
RESTORE DATABASE PREVIEW reports the exact backups and archived logs RMAN would use, so you can confirm your recovery window is intact before an incident, not during one.

RMAN> RESTORE DATABASE VALIDATE CHECK LOGICAL;
RMAN> RESTORE DATABASE PREVIEW;

But validation is not a rehearsal. The only real test is an actual restore to a scratch host, on a schedule. A backup you've never restored is a hope; a restore you ran last month is a plan. If you take one thing from this post, take that.

What teams get wrong

Confusing restore and recover — restoring without recovering (and silently discarding everything since the backup), or assuming "restore" finished the job.
SET UNTIL in the wrong place — after RESTORE instead of before it, so the restored files overshoot the target time.
Forgetting OPEN RESETLOGS after incomplete recovery — or not realizing it forks a new incarnation, and failing to take a fresh backup immediately after.
No controlfile autobackup, or a DBID nobody wrote down — the recovery you can't even begin.
Full restore when a smaller tool would do — reaching for RESTORE DATABASE when Flashback (minutes) or RECOVER TABLE (one object) fixes it with a fraction of the downtime.
Never testing a restore. The backup that runs green every night and has never once been restored is the one that fails when it finally matters.

The one-paragraph version

Restore copies datafiles back; recover rolls them forward with redo — most recoveries are RESTORE DATABASE then RECOVER DATABASE, and you OPEN when you've recovered to the present. To rewind past a mistake, wrap SET UNTIL / RESTORE / RECOVER in a RUN block and OPEN RESETLOGS. Fix a few bad blocks with RECOVER CORRUPTION LIST while the datafile stays online; recover a lost controlfile from autobackup after SET DBID in NOMOUNT; and reach for Flashback or RECOVER TABLE before a full restore when the problem is logical. Then do the part nobody enjoys and everybody needs: test the restore on a schedule, because an untested backup was never a recovery plan.

FAQ

What is the difference between RESTORE and RECOVER in RMAN?

RESTORE copies datafiles back from your RMAN backups, rewinding them to the time the backup was taken. RECOVER then applies archived and online redo logs to roll those restored files forward — either to the present (complete recovery) or to a chosen point in time (incomplete recovery). A normal recovery is RESTORE DATABASE followed by RECOVER DATABASE; restoring without recovering would discard every transaction since the backup.

How do I recover an Oracle database to a point in time before a mistake?

Use database point-in-time recovery (incomplete recovery). Put a SET UNTIL clause (a time, SCN, log sequence, or restore point) at the top of a RUN block, before both RESTORE DATABASE and RECOVER DATABASE, so the target applies to both. After recovery you must open the database with ALTER DATABASE OPEN RESETLOGS, which starts a new incarnation — take a full backup immediately afterward. If Flashback Database is enabled, FLASHBACK DATABASE TO SCN is usually faster because it needs no restore.

Can I recover just a few corrupt blocks without restoring the whole datafile?

Yes — that is block media recovery, and the datafile stays online the entire time. RMAN records corrupt blocks in V$DATABASE_BLOCK_CORRUPTION during backups and VALIDATE; RECOVER CORRUPTION LIST then repairs everything on that list, or RECOVER DATAFILE n BLOCK m fixes a specific block. It is an Enterprise Edition feature and repairs physical corruption only — it cannot fix logical block corruption.

What do I do if I lose the control file?

If controlfile autobackup is enabled, start the instance in NOMOUNT, set the DBID (SET DBID), run RESTORE CONTROLFILE FROM AUTOBACKUP, then MOUNT, RECOVER DATABASE, and OPEN RESETLOGS. Without a recovery catalog, RMAN needs the DBID to find the autobackup, so record your DBID somewhere outside the database. A lost SPFILE is recovered the same way with RESTORE SPFILE FROM AUTOBACKUP.

Do I have to OPEN RESETLOGS after recovery?

Only after incomplete recovery (point-in-time recovery) or after recovering with a backup control file. Complete recovery to the present does not need RESETLOGS. When you do open with RESETLOGS, Oracle starts a new database incarnation, so you should take a fresh full backup right away because prior backups belong to the earlier incarnation.

How do I test that my RMAN backups actually work?

Use RESTORE DATABASE VALIDATE (add CHECK LOGICAL) to confirm the needed backups exist and are not corrupt without restoring, and RESTORE DATABASE PREVIEW to see exactly which backups and archived logs a recovery would use. But those checks are not a rehearsal — the only real proof is a scheduled, actual restore to a separate scratch host. A backup that has never been restored is a hope, not a recovery plan.

Recovery is the floor under every other high-availability choice — RAC and Data Guard survive hardware
and site failures, but only backups and Flashback survive a bad DELETE. For where each fits, see
The Oracle HA Decision Tree.

Practice this before you need it. The rman/ recovery lab
lets you break a throwaway Oracle Database Free with Docker and recover it for real: delete a datafile
and RESTORE/RECOVER it, corrupt a block and repair just that block with block media recovery,
rewind past a committed DELETE with point-in-time recovery, and prove a restore works with
RESTORE ... VALIDATE. It row-checks every recovery, so it only passes if the data actually comes back.
The first time you run a restore should never be in production.

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

Your First Oracle Autonomous Database on OCI Always Free

Uptime Architect — Sun, 19 Jul 2026 23:19:31 +0000

The single best way to keep your Oracle skills sharp without a license, a server, or a finance conversation is OCI Always Free. It gives you a real, fully managed Oracle database — Autonomous
Database, running the current 23ai release — that stays free indefinitely. It's the ideal sandbox: a place to test commands, try new-release features, and generate real screenshots, all on infrastructure that's yours and costs nothing.

Here's how to stand one up in a few minutes, what's genuinely worth doing with it, and the one catch that catches people out. One ground rule first: do all of this on a personal OCI accoun, never an employer's tenancy.

The short version. Sign up at oracle.com/cloud/free, create an Autonomous Database with the Always Free toggle on, and connect through the browser via Database Actions → SQL — no client install. You get a real, managed Oracle 23ai database with AI Vector Search included, free forever. The one catch: it stops after 7 days of inactivity and is reclaimed after ~90 days stopped, so log in occasionally and it's yours to keep.

What "Always Free" actually includes

OCI's Always Free tier is genuinely free forever — not a 30-day trial — and the database piece is the star:

2× Autonomous Database, each with 1 OCPU and 20 GB of storage — the focus of this post.
Arm Ampere A1 compute: up to 4 OCPUs + 24 GB RAM total — enough to also run Oracle Database Free yourself on a VM, if you want the self-managed side too.
Object storage, networking, and a handful of other services.

The catch worth knowing up front: an Always Free Autonomous Database stops automatically after 7 days of inactivity (your data is preserved), and if it stays stopped for 90 cumulative days it can be reclaimed and permanently deleted. "Activity" means an actual connection running SQL — so just log in and run something every week or two and it's yours indefinitely. Set a calendar nudge; that 90-day clock is how most people lose their sandbox.

Step 1 — Create a personal OCI account

Sign up at oracle.com/cloud/free with a personal email. Identity verification asks for a card, but Always Free resources are never charged — the card is for identity and for if you later opt into paid resources (you won't need to). Pick a home region close to you; if you also want the free Ampere VM, choose a region with A1 capacity. Your home region is permanent, so choose deliberately.

Step 2 — Provision the Autonomous Database

In the Console, go to Oracle Database → Autonomous Database → Create Autonomous Database:

Workload type: Transaction Processing (ATP) or Data Warehouse (ADW). ATP is the natural default for a general-purpose learning sandbox — it behaves like the OLTP databases you meet day to day. Pick ADW only if you're specifically playing with analytics/columnar workloads. (JSON and APEX flavors also exist; ignore them for now.)
Always Free: toggle it on. This is the switch that matters — it's easy to miss, and without it you're provisioning a paid instance.
Set a strong ADMIN password, leave everything else at defaults, and click Create. It's ready in a minute or two.

Step 3 — Connect (no client install needed)

The fastest path needs nothing on your laptop. From the database's detail page, open Database Actions → SQL — a full browser-based SQL worksheet (SQL Developer Web). Log in as ADMIN and you're querying:

-- prove you're on a current release
SELECT banner_full FROM v$version;
SELECT database_role, open_mode FROM v$database;

v$version confirms exactly which 23ai build your Always Free ADB is on the day you provision it —
worth checking, because Oracle keeps the managed service current for you.

For a desktop client or an application, download the wallet (the mTLS credentials bundle) from the database page and point your tool at the connection strings inside it. But for learning and quick tests, Database Actions in the browser is all you need.

Step 4 — Put something in it

A sandbox is more useful with data. This runs as-is under the ADMIN schema:

CREATE TABLE demo_orders (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer  VARCHAR2(60),
  amount    NUMBER(10,2),
  created   DATE DEFAULT SYSDATE
);

INSERT INTO demo_orders (customer, amount)
SELECT 'Customer ' || LEVEL, ROUND(DBMS_RANDOM.VALUE(10, 1000), 2)
FROM   dual CONNECT BY LEVEL <= 1000;
COMMIT;

SELECT COUNT(*) rows_loaded, ROUND(AVG(amount), 2) avg_amount FROM demo_orders;

That's a thousand rows to experiment against — enough to try indexes, execution plans, or a query you
want to tune.

Step 5 — Try something only the new releases can do

The reason to sandbox on a current release is to play with what's new — and on 23ai the headline is AI Vector Search: storing embeddings (the numeric fingerprints of text, images, etc.) right next to your relational data and querying them by similarity instead of exact match. It's included on Always
Free at no extra cost.

You'd normally generate embeddings from a model, but you can see the whole mechanism with tiny hand-built vectors — no model required:

CREATE TABLE docs (
  id        NUMBER PRIMARY KEY,
  content   VARCHAR2(50),
  embedding VECTOR(3, FLOAT32)     -- 3 dimensions, just to watch it work
);

INSERT INTO docs VALUES (1, 'cat',      TO_VECTOR('[0.9, 0.1, 0.0]'));
INSERT INTO docs VALUES (2, 'kitten',   TO_VECTOR('[0.8, 0.2, 0.0]'));
INSERT INTO docs VALUES (3, 'airplane', TO_VECTOR('[0.0, 0.1, 0.9]'));
COMMIT;

-- which rows are most "similar" to cat? (smallest cosine distance)
SELECT id, content,
       ROUND(VECTOR_DISTANCE(embedding, TO_VECTOR('[0.9, 0.1, 0.0]'), COSINE), 4) AS distance
FROM   docs
ORDER  BY distance
FETCH FIRST 3 ROWS ONLY;

kitten comes back as the nearest neighbor to cat and airplane as the farthest — the same operation that powers semantic search and RAG, just with three dimensions instead of a thousand. Swap in real
embeddings from a model and add an HNSW or IVF vector index, and this is production AI search. On a free database you fully control.

No OCI account? Run it locally. The AI Vector Search lab
runs this exact VECTOR / VECTOR_DISTANCE demo on Oracle Database Free with Docker — vector search ships in the free image too — so you can watch kitten land nearest cat in about two minutes, no
cloud signup: ./run.sh up && ./run.sh all.

What this sandbox is good for — and what it isn't

Verifying before you publish. Run the exact commands from a blog post, runbook, or Stack Exchange answer on a real instance instead of trusting memory. (It's how the SQL in these posts gets checked.)
Original screenshots and reports. The OCI console, Database Actions, real query output — concrete assets that make your writing credible. You can even generate AWR-style performance reports to practice reading them.
Learning the cloud-native side. Provisioning, scaling, automatic backups, and the managed-service model you'll meet on real Oracle-to-cloud migrations — Autonomous is one of the most common migration targets.

What it won't do: because Always Free ADB is a fully managed service, you don't configure theinfrastructure — so you won't stand up RAC or Data Guard on it (those are separate, self-managed exercises). But as a zero-cost, always-on, current-release Oracle database you fully control, it's very hard to beat.

FAQ

Is OCI Always Free actually free, or a trial?

It is genuinely free forever, not a time-limited trial. The Always Free tier includes two Autonomous Databases (1 OCPU and 20 GB each), Arm Ampere A1 compute (up to 4 OCPUs and 24 GB RAM), and some storage and networking — all at no charge for as long as you use them. Oracle also offers a separate 30-day free trial with credits, but the Always Free resources persist beyond it.

What version of Oracle Database does Always Free Autonomous run?

Always Free Autonomous Database runs Oracle Database 23ai, the current release, and Oracle keeps the managed service patched and current for you. Run SELECT banner_full FROM v$version in Database Actions to see the exact build on the day you provision it.

Can I use AI Vector Search on the free tier?

Yes. AI Vector Search is a core Oracle Database 23ai feature and is included at no additional charge on Autonomous Database, including the Always Free tier. You can create VECTOR columns, run VECTOR_DISTANCE similarity queries, and build HNSW or IVF vector indexes without paying anything.

Will my Always Free database get deleted?

It can, if you abandon it. An Always Free Autonomous Database stops automatically after 7 days of inactivity (its data is preserved), and if it stays stopped for 90 cumulative days it may be reclaimed and permanently deleted. Connecting and running SQL resets the inactivity clock, so logging in every week or two keeps it indefinitely.

Can I run RAC or Data Guard on Always Free Autonomous Database?

No. Autonomous Database is a fully managed service, so you do not configure the underlying infrastructure — RAC and Data Guard are not something you set up on it. Oracle handles high availability and backups for you behind the scenes. To practice building RAC or Data Guard yourself, use your own Enterprise Edition environment or lab, not Always Free ADB.

Do I need to install anything to connect?

No. From the database detail page, open Database Actions and use the browser-based SQL worksheet — nothing to install. For desktop clients or applications, download the wallet (mTLS credentials) from the database page and use the connection strings it contains.

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

Side-Project Cemetery: I built a graveyard for your abandoned repos (and a button that brings them back)

Uptime Architect — Sat, 11 Jul 2026 20:30:46 +0000

This is a submission for Weekend Challenge: Passion Edition

What I Built

Every abandoned repo was once someone's 2 AM obsession.

That's the most honest artifact of passion a developer owns — not the polished portfolio piece, but the thing you started at midnight because you couldn't not, pushed to for eleven straight days, and then... life happened.

Side-Project Cemetery turns any GitHub profile into a moonlit graveyard. Type a username and every public repo that hasn't seen a push in 180+ days rises from the ground as a headstone — birth year, death year, how long it was loved fiercely, candles for its stars, and an affectionate epitaph:

"Its last commit was hope. Its next was never."

"Written in JavaScript. Died of callback exhaustion."

"Not dead — just aggressively on hold since 2025."

But here's the part the challenge theme is really about. Passion doesn't die — it naps. So every grave has a 🔥 Rekindle button. Click it, and the app reads the repo's README and writes a pitch designed to re-seduce you with your own idea — closing with "Your first step tonight:", one small, concrete action derived from the actual repo. Then it prints you a Resurrection Certificate.

The tone rule for the whole app: loving humor, never mockery. These are graves, but we come with flowers.

Demo

🪦 Live: https://pyaroslav.github.io/side-project-cemetery/ — bring your own username. I'll wait.

This is my own graveyard — I'm not exempt. Two graves, and both epitaphs read "loved fiercely for one wild night," because both were single-evening obsessions I never came back to. The app roasted me with:

OracleDatabaseTuning rests here, one git push short of glory.

Then I hit Rekindle on it. This is the built-in offline engine — no API key involved:

OracleDatabaseTuning isn't a corpse. It's a nap that got out of hand. You started it because the idea wouldn't leave you alone at night. You built the whole first spark of it in a single sitting, which means the hard part — starting — is a thing you've already done here. [...] The distance between a dead repo and a living one is a single commit — and you've written harder code half-asleep. Tonight it doesn't need a rewrite. It needs five minutes and the version of you that started it.

Your first step tonight: Open the README, find the "Oracle Database Tuning Tool" section, and write one true sentence under it about where this actually stands today.

Point it at your favorite prolific maintainer and you get a proper necropolis — headstones sorted most-loved first, and truly dedicated abandoners get a "🚶 Walk deeper — N more souls rest here" gate after the first 24 graves. Drop your best epitaph in the comments.

Code

pyaroslav / side-project-cemetery

🪦 A moonlit graveyard for your abandoned repos — with loving epitaphs and a 🔥 Rekindle button. Built for the DEV Weekend Challenge: Passion Edition.

🪦 Side-Project Cemetery

Every abandoned repo was once someone's 2 AM obsession.

Enter any GitHub username and wander a moonlit cemetery of that person's abandoned side projects — public, original repositories that haven't been pushed to in over six months. Each one gets a headstone and a loving slightly funny epitaph.

Then comes the turn: pick any grave and hit 🔥 Rekindle. The app reads the repo, writes a passionate one-paragraph revival pitch aimed straight at its creator, and hands you one concrete first step for tonight — because passion doesn't die, it just naps.

Built for the DEV Weekend Challenge: Passion Edition.

Run it

It's a pure static site — three files, no build step, no dependencies.

cd side-project-cemetery
python3 -m http.server 8000
# open http://localhost:8000

Any static file server works. You can also deep-link straight to a cemetery:

http://localhost:8000/?u=sindresorhus

What it does

Hero — type a…

View on GitHub

How I Built It

Three static files. No build step, no framework, no server. The night scene — moon, twinkling stars, drifting fog, fireflies — is pure CSS: radial gradients, blurred shapes on slow keyframe loops, six 4px dots with per-instance custom properties. No image assets at all, and it all switches off under prefers-reduced-motion.

The graveyard is the GitHub REST API, unauthenticated. A repo becomes a headstone when it's public, not a fork (a fork is someone else's passion), and pushed_at is over 180 days old. "Loved fiercely for N days" is pushed_at − created_at — the window it actually received work. Under a day renders as "one wild night."

Epitaphs are deterministic. Reloading a graveyard shouldn't reshuffle the dead, so each repo's epitaph is seeded from a hash of its name across 20 templates, with a language→cause-of-death map (~25 languages). A de-clustering pass keeps one page of graves from repeating itself. Everything works with zero API keys.

The AI is an upgrade, never a requirement — my favorite design constraint of the weekend. Save a Gemini key in ✨ AI settings and the whole cemetery gets AI-written epitaphs in one batched call (gemini-flash-latest, responseSchema-constrained JSON, silent fallback to the local engine on any failure). It read my dead Oracle-tuning experiment and wrote:

"You dreamed of optimization for zero days, proving the fastest database code is the code never written."

Rekindle sends the README — base64-decoded client-side — and gets back {paragraph, step}. For that same repo, Gemini pitched me "an automated performance detective that rescues database administrator sanity" and assigned tonight's step: create tune.py and write one mock Oracle wait event. That's uncomfortably actionable — same grave, two engines, and you can compare them yourself in the screenshots.

Fun war story: my first test key failed with the original model list — new Google AI keys get a 404 on gemini-2.5-flash and a quota error on gemini-2.0-flash, and my retry chain only advanced on 404s. If you ship a BYO-key app, lead with the -latest rolling alias and retry on any model error, or every fresh-key user sees your fallback path and nothing else.

Save an ElevenLabs key and a 🔊 Hear the eulogy button appears: your epitaph and revival pitch, read aloud over the fog (eleven_multilingual_v2, trimmed at sentence boundaries so the voice never dies mid-thought — a bug I actually shipped and fixed). Keys live in localStorage, go only to their own services, and the app is never broken, never blank, never waiting.

The first step is derived, not canned. If the README mentions a TODO, the step targets it. Otherwise it uses the README's first heading, or a real command for the repo's language, or folds the repo's description into a task. The goal: make the next commit feel five minutes away. Because it is.

Why this one

I picked this idea because my own graveyard called me out. Both of my dead repos — an Oracle database tuning experiment and a CS50 AI course project — were one wild night each: started in a burst of 2 AM conviction, abandoned by breakfast. Building this app meant staring at that pattern for a whole weekend. It worked: cs50ai is getting its "first step tonight" — tonight.

I built a cemetery, but I really built the opposite: a machine for noticing that every grave in it is one commit away from being a garden again.

Go visit yours. Bring flowers. Leave with a first step. 🔥

Built solo within the challenge window. Stack: vanilla HTML/CSS/JS, GitHub REST API, Google Gemini (Flash) + ElevenLabs (both BYO-key, both optional), zero servers.

Migrating Oracle to the Cloud: Which Method, and When

Uptime Architect — Wed, 08 Jul 2026 19:21:51 +0000

Moving an Oracle database to the cloud is a solved problem five times over — Data Pump, transportable tablespaces, Data Guard, GoldenGate, and ZDM all do it. The hard part isn't the destination; it's picking the method, because the method — not the cloud — decides your downtime, your risk, and whether you can roll back when something goes sideways at 2am on cutover night.

There's no single best method. The right one falls out of four questions: how much downtime can you afford, are you crossing a boundary (endianness, version, platform, character set), how big is the database, and — the one people forget — what does the target even allow? Answer those and the method is nearly decided for you. This guide walks the decision for both OCI and Oracle Database@Azure.

The short version. Logical methods (Data Pump, GoldenGate) recreate objects, so they cross any boundary — version, platform, endianness, character set — but you pay in downtime (Data Pump) or licensing and complexity (GoldenGate). Physical methods (RMAN/restore, transportable tablespaces, Data Guard) copy blocks, so they're fast and faithful but locked to the same endianness (and, for Data Guard, the same version). Data Guard gives near-zero downtime and the best rollback when the architecture doesn't change; GoldenGate gives near-zero downtime when it does. ZDM is the free Oracle tool that automates whichever fits. And the target caps your options: Exadata and Database@Azure allow everything; Autonomous allows logical only.

The two axes that pick your method

Before you compare tools, answer two questions. They eliminate most of the options on their own.

1. How much downtime can you afford? Every method is either an outage (the database is down for the whole move, and the outage scales with data size) or near-zero (the target is built and synced while the source keeps serving, and only a brief cutover is downtime). Data Pump and plain RMAN restore are outages. Data Guard, GoldenGate, and incremental XTTS are near-zero.

2. Are you crossing a boundary? The big one is endianness. Logical methods recreate each block on load, so they're endian-agnostic — they don't care. Physical methods copy blocks verbatim, so they require the same endian format on both ends. Check it before you design anything:

SELECT platform_name, endian_format FROM v$transportable_platform ORDER BY endian_format, platform_name;
SELECT platform_name FROM v$transportable_platform
WHERE platform_id = (SELECT platform_id FROM v$database);

The classic cloud migration is getting off a big-endian UNIX box — Solaris SPARC, AIX, HP-UX — and onto little-endian Linux x86-64 on Exadata or in Azure. That's a cross-endian move, which rules out a straight RMAN restore or a Data Guard standby entirely. You're left with a logical method, or transportable tablespaces with an explicit RMAN CONVERT. Version changes and character-set changes
behave the same way: logical handles them, physical mostly doesn't.

Hold those two answers. Now the methods.

The five methods

Method	What it is	Downtime	Crosses endian / version?	Best when
Data Pump (`expdp`/`impdp`)	Logical export/import — recreates objects	Proportional to size (full outage)	Yes — any boundary	Small/medium DB, version or charset change, partial move, → Autonomous
Transportable / XTTS	Copy datafiles + Data Pump metadata; XTTS rolls forward with RMAN incrementals	Low (XTTS = just the final read-only increment)	Cross-endian via `RMAN CONVERT`; cross-version within rules	Very large DB, cross-endian platform exit, want a short cutover
Data Guard (standby → switchover)	Block-identical standby at the target, then switchover	Near-zero (seconds–minutes)	No — same endian and same version	Large DB, same architecture, near-zero downtime + easy rollback
GoldenGate	Logical replication — initial load, then change capture/apply, then cut over	Near-zero (sub-minute)	Yes — version, platform, endian, even heterogeneous	Large DB + minimal downtime and crossing a boundary; need fallback
ZDM	Free Oracle tool that orchestrates the methods above	= whatever method it runs	= whatever method it runs	Migrating to OCI/Exadata/Database@Azure and you want it automated

Data Pump: the universal translator

Logical export/import recreates every object on the target, which is exactly why it crosses version, platform, endianness, and character set that the physical methods can't. The cost is downtime
proportional to data volume — the unload → reload → rebuild-indexes cycle, not just raw bytes.PARALLEL and network mode (a direct INSERT … SELECT over a database link, no dump file) help, but for a large database the outage can still be unacceptable.

One myth to kill: the "source and target can't differ by more than two releases" rule applies only over a network link — dump-file transfers span a far wider version range. For moving a whole
database there's Full Transportable Export/Import (FTEX), which combines transportable datafiles for your user data with Data Pump metadata for the rest (source 11.2.0.3+ → target 12c+). And for
Autonomous Database, Data Pump is the answer: stage the dump files in object storage, run as ADMIN (never SYS), and exclude the objects ADB manages itself — Oracle's canonical recipe is
exclude=cluster,indextype,db_link.

Transportable tablespaces and XTTS: physical, but portable

Transportable tablespaces copy datafiles directly and carry only a small Data Pump metadata export — so the bulk data is never logically unloaded, which is why it's far faster than Data Pump for large databases. Same-endian is a plain file copy; cross-endian requires RMAN CONVERT to flip each block's byte order. Classic TTS needs the tablespaces READ ONLY for the entire copy window — and that read-only window is your downtime.

XTTS (cross-platform transportable with RMAN incremental backups) is the trick that shrinks it: take a level-0 backup while the source stays fully online, roll the target copy forward with level-1 incrementals (still online, no downtime), and only the final increment needs the tablespaces read only — so cutover is near-constant regardless of database size. This is the MAA-recommended way off an old big-endian UNIX box onto Linux x86-64. It's Enterprise Edition only, and mind the things that don't
transport: TIMESTAMP WITH TIME ZONE columns are skipped (Data Pump warns you) across mismatched time-zone file versions, TDE column-encrypted tables can't be in a transport set at all, and external tables, directory objects, and BFILEs aren't in the datafiles — copy and recreate those by hand.

Data Guard: relocate without transforming

When the move doesn't cross a boundary — same endianness, same version, same character set — Data Guard is the gold standard. Build a physical standby at the target, let Redo Apply catch it up while the source keeps serving traffic, then switch over. The only downtime is the switchover itself: seconds to minutes. And it has the best rollback story of any method — after cutover the old primary automatically becomes a standby of the new one, so if the cloud primary misbehaves you simply switch back, with no data loss.

The constraints are the flip side of that fidelity: same endian (a physical standby is block-for-block, so cross-endian is out), same DB version (a switchover is not an upgrade path — upgrading in flight is a separate transient-logical procedure), Enterprise Edition, and the source in ARCHIVELOG + FORCE LOGGING. This is the lift-and-shift method: same architecture in, same architecture out, tiny cutover. It runs on the same
switchover/failover machinery covered in The Oracle HA Decision
Tree and detailed in Data Guard Switchover vs
Failover.

GoldenGate: near-zero downtime across any boundary

GoldenGate is logical replication: load the target (typically with Data Pump), then capture changes from the source's redo and apply them to keep the target in sync, and when replication lag is near zero, quiesce the app and reconnect it to the target. Cutover is bounded only by drain + reconnect, so a sub-minute outage is achievable. It's the one method that gives near-zero downtime and crosses every boundary — version (11g → 26ai in a single move), platform, endianness, even heterogeneous (Oracle ↔
non-Oracle) — with transformation in flight. Bidirectional replication gives you a genuine fallback after cutover, which is nearly unique among migration methods.

The price is real: GoldenGate is separately licensed (or consumed as the managed OCI GoldenGate service), it's the most complex method to run (supplemental logging on the source, Extract/Replicat processes, lag monitoring), and it can't replicate everything — certain materialized views (such as those
created WITH ROWID), some LOB/UDT/spatial types, and tables with no usable row key fall outside it and get reloaded via Data Pump instead. Reach for it when you need minimal downtime on a
large database that's also crossing a boundary, or when you want that fallback net.

ZDM: the orchestrator, not a sixth method

You rarely wire these up by hand for a real migration. Zero Downtime Migration (ZDM) is Oracle's free, supported, CLI-driven tool that orchestrates them: it never moves data itself — it drives RMAN, Data Guard, Data Pump, and GoldenGate for you, wrapped in pre-checks, pause/resume, evaluation dry-runs, auditing, and fallback. It comes in two families that map exactly onto the physical/logical split:

Physical online — RMAN + Data Guard standby + switchover. Same platform/endian, Enterprise Edition; near-zero cutover (typically under 15 minutes). ZDM can fold a version upgrade into the same job.
Logical online — Data Pump initial load + GoldenGate change capture. Crosses boundaries. Minimal downtime.

(Each also has an offline variant that trades the near-zero cutover for not needing GoldenGate or a standby — and offline physical is the only path for Standard Edition.) If you're migrating to OCI, Exadata, or Database@Azure, ZDM is how you do it repeatably — especially across a fleet — instead of hand-scripting. It inherits every constraint of the method underneath, so it doesn't change the decision; it automates the execution.

The target decides what's even possible

Here's the gate people skip: the method you can use is capped by how much of the target database you control. Physical methods need SYSDBA, OS/file access, and a database you can mount and recover.
Logical methods need only a SQL connection and a privileged schema. Two of the three common targets give you a full database; one does not.

OCI Exadata Database Service & Base Database Service — full Enterprise Edition databases you administer, with SYSDBA and OS access. Everything works: RMAN, physical Data Guard, transportable tablespaces, Data Pump, GoldenGate, and ZDM in both modes. The most flexible target.- Oracle Database@Azure — the same Exadata Database Service, running on Oracle-managed Exadata hardware physically located inside Azure data centers, on your Azure VNet and billed through Azure. Because it's the same service, you get the same full toolbox, including standing up a physical Data Guard standby straight from on-prem into Azure and switching over — something you can't do into a managed PaaS database. That's the genuine both-clouds differentiator. (Don't confuse it with the older "Oracle Database Service for Azure," which keeps the database in OCI and bridges over the Azure interconnect — a connectivity pattern, not the same target.)
Autonomous Database (ADB) — fully managed, no SYSDBA, no file access. So physical methods are out entirely: into Autonomous it's logical only — Data Pump (dump files from object storage) or GoldenGate. Its character set defaults to Unicode (AL32UTF8) and can't be changed after provisioning, so a non-Unicode source has to converge to Unicode — run the Cloud Premigration Advisor Tool (CPAT) first.

Rule of thumb: Exadata and Database@Azure = physical + logical; Autonomous = logical only.

The decision, in one tree

Choosing an Oracle cloud migration method. The target and any boundary crossing eliminate options first; downtime tolerance picks between what's left. ZDM automates whichever physical or logical path you land on.

flowchart TD
  A{Target = Autonomous DB?} -- Yes --> B{Downtime budget?}
  B -- Outage OK --> B1[Data Pump<br/>via object storage]
  B -- Near-zero --> B2[GoldenGate<br/>logical online]
  A -- "No — you control the DB<br/>(Exadata / Base DB / Database@Azure)" --> C{Crossing endian<br/>or upgrading version?}
  C -- "Yes (cross-endian / cross-version)" --> D{Downtime budget?}
  D -- Outage OK --> D1[Data Pump or<br/>XTTS + RMAN CONVERT]
  D -- Near-zero --> D2[GoldenGate<br/>logical online]
  C -- "No (same arch)" --> E{Downtime budget?}
  E -- Near-zero --> E1[Data Guard<br/>physical online / ZDM]
  E -- Outage OK --> E2[RMAN restore or<br/>Data Pump]

What teams get wrong

Picking the destination before the method. The cloud is the easy part; the method is where downtime and risk actually live. Decide how you'll move before you argue about where.
Assuming Data Guard or RMAN can cross endianness. They can't — block-identical means same endian. A big-endian UNIX box (AIX/Solaris/HP-UX) → Linux x86-64 is a logical or XTTS-with-CONVERT job, never a straight physical restore. Check V$TRANSPORTABLE_PLATFORM first.
Treating a Data Guard switchover as an upgrade path. Same version required. Upgrading during the move is a different (transient-logical) procedure — don't plan to "just switch over to the new version."
Forgetting what the target allows. Designing a physical migration into Autonomous, which can't accept one. Confirm the target's capabilities before you design the migration, not after.
Underestimating GoldenGate. "Near-zero downtime" sounds free; it's separately licensed, the most complex method to operate, and it won't replicate every object. Budget for the license and the labor.
Sizing downtime as the whole copy. For Data Guard, XTTS, and GoldenGate the copy and sync are online; only the cutover is downtime — minutes, not the hours it takes to move the data.

Want to drill the decision? The cloud-migration method-selection lab gives you six realistic scenarios — source platform, version, size, downtime budget, and target (OCI, Database@Azure, Autonomous) — and you pick the best method and justify it, with a grade.sh self-check. No install and no cloud account: it trains the judgment, not the keystrokes. (Two scenarios hide a trap real teams fall into.)

Where this fits

Migration is one piece of running Oracle in the cloud — the rest of the cluster lives in Cloud & Migration. The near-zero-downtime methods here lean on the same switchover/failover machinery as The Oracle HA Decision Tree. And the question that decides where you land
with the full toolbox above — OCI versus Oracle Database@Azure — is its own deep dive, coming next in this pillar.

FAQ

What is the best way to migrate an Oracle database to the cloud?

There is no single best method — the right one is determined by four things: how much downtime you can afford, whether you are crossing a boundary (endianness, version, platform, or character set), the database size, and what the target allows. Data Pump is simplest but means a full outage; Data Guard gives near-zero downtime when the architecture does not change; GoldenGate gives near-zero downtime when it does; transportable tablespaces (XTTS) move very large databases with a short cutover; and ZDM automates whichever of these fits.

What is the difference between physical and logical Oracle migration?

Physical methods (RMAN restore, transportable tablespaces, Data Guard) copy data blocks verbatim, so they are fast and faithful but require the same endianness on both ends — and, for Data Guard, the same database version. Logical methods (Data Pump, GoldenGate) recreate each object on the target, so they cross version, platform, endianness, and character-set boundaries, at the cost of downtime (Data Pump) or licensing and complexity (GoldenGate).

Can I use Data Guard to migrate across different platforms or versions?

Only within strict limits. A physical standby is block-for-block identical to the primary, so it requires the same endian format and the same database version — a switchover is not an upgrade path. It is ideal for a same-architecture lift-and-shift (for example, on-prem Linux x86-64 to OCI Exadata) with near-zero downtime and easy rollback. If you are crossing endianness or changing version, use a logical method (GoldenGate or Data Pump) or transportable tablespaces with RMAN CONVERT instead.

How do I migrate to Oracle Autonomous Database?

Logical methods only. Autonomous Database is fully managed with no SYSDBA or file-system access, so physical methods (RMAN restore, Data Guard standby, transportable tablespaces) are not possible. Use Data Pump — with dump files staged in object storage, run as ADMIN rather than SYS, excluding objects ADB manages (cluster, indextype, db_link) — for an outage-tolerant move, or GoldenGate for near-zero downtime. Autonomous defaults to the Unicode AL32UTF8 character set, which cannot be changed after provisioning, so run the Cloud Premigration Advisor Tool (CPAT) first if your source is not already Unicode.

What is Oracle Zero Downtime Migration (ZDM)?

ZDM is a free, Oracle-supported tool that orchestrates a migration to OCI, Exadata, or Database@Azure. It does not move data itself — it drives the underlying methods (RMAN, Data Guard, Data Pump, GoldenGate) with pre-checks, pause/resume, and fallback. It has a physical mode (RMAN + Data Guard switchover, for same-endian/same-version moves) and a logical mode (Data Pump + GoldenGate, which crosses boundaries and is the only path into Autonomous). It inherits the constraints of whichever method it runs.

Does GoldenGate give true zero downtime?

Near-zero, not literally zero. The source stays online during the initial load and change replication; the only downtime is the brief window to drain in-flight transactions and reconnect the application to the target, which can be sub-minute. Bidirectional replication can keep the original source current after cutover to enable a fallback. Note that GoldenGate is separately licensed (or consumed as the managed OCI GoldenGate service) and is the most complex method to operate.

How do I migrate Oracle from AIX or Solaris (big-endian) to the cloud?

That is a cross-endian move, because AIX and Solaris SPARC are big-endian while Linux x86-64 (the usual cloud target) is little-endian. A straight RMAN restore or a Data Guard standby will not work across different endianness. Use cross-platform transportable tablespaces (XTTS) with RMAN CONVERT — which uses incremental backups to keep downtime to the final read-only increment — or a logical method (GoldenGate for near-zero downtime, or Data Pump if an outage is acceptable).

Is Oracle Database@Azure different from OCI for migration?

For migration purposes it behaves the same as OCI Exadata Database Service, because that is exactly what it is — Oracle-managed Exadata infrastructure running inside Azure data centers. So the full toolbox applies, including physical Data Guard standby-in and switchover from on-prem straight into Azure. That sets it apart from Autonomous Database (logical methods only) and from the older "Oracle Database Service for Azure," which keeps the database in OCI and connects over the Azure interconnect rather than running Exadata in Azure itself.

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

ORA-00060 Deadlock: Find It, Fix It, Prevent It

Uptime Architect — Wed, 08 Jul 2026 19:20:23 +0000

ORA-00060: deadlock detected while waiting for resource is one of the most misunderstood errors Oracle throws. The two myths that cause the most damage: that it rolled back your transaction (it
didn't — just one statement), and that it's a database tuning problem you fix with a parameter (it isn't — it's almost always an application bug). Oracle already broke the deadlock for you. Your job is to read the trace it left, find the two statements that collided, and stop it happening again.

Here's exactly what the error means, how to read the deadlock graph, the handful of patterns that cause deadlocks, and the fix for each.

The short version. A deadlock is a circular wait — session A holds a row B wants while B holds a row A wants. Oracle detects it automatically (within a few seconds) and breaks it by rolling
back one statement of one session — the "victim" — which gets ORA-00060. The transaction survives (everything before that statement is intact); the other session proceeds as if nothing
happened. The trace file holds a deadlock graph naming the two transactions, the rows, and the two SQL statements. Mode-6 (X) deadlocks mean fix your app's lock order; mode-4 (S) deadlocks mean look at the data structure (unindexed foreign key, ITL shortage, bitmap index).

What ORA-00060 actually does

When two sessions wait on each other's locks, neither can proceed — that's a deadlock. Oracle runs a background detector that notices the wait-for cycle (typically within ~3 seconds) and breaks it
without any timeout or configuration from you. Precisely what happens:

It rolls back one statement, not the transaction. Only the single DML that closed the cycle is undone. Everything that transaction did before it is still there, and still holds its locks.
The transaction is not terminated. The session that gets ORA-00060 is still in an open transaction, holding a half-finished change. Oracle hands it back to you to decide: retry the statement, roll back to a savepoint, or roll back the whole thing.
Only one session errors. The other session in the cycle gets its lock and continues normally — it never knows a deadlock occurred. Either session can be the victim; Oracle chooses.
The application has to handle it. In PL/SQL, declare your own exception and bind it to the error with PRAGMA EXCEPTION_INIT(deadlock_detected, -60), then retry after a short backoff. (There is no built-in DEADLOCK_DETECTED exception — ORA-00060 isn't one of PL/SQL's predefined exceptions.)

So the single most common belief — "a deadlock rolled back my transaction" — is false. It rolled back one statement and left the rest of your transaction (and its locks) in place, waiting for your code to react.

Deadlock or just blocking? (the #1 confusion)

A stuck session looks the same whether it's deadlocked or merely blocked — but they're opposite problems:

	Deadlock (`ORA-00060`)	Ordinary blocking
Shape	Circular — A waits on B and B waits on A	One-way — A waits on B only
Oracle's response	Auto-detects and breaks it in seconds	Does nothing — waits indefinitely
Ends when	Immediately — victim gets `ORA-00060`	The blocker commits or rolls back
Wait event	(transient, then the victim errors)	`enq: TX - row lock contention`
Error raised?	Yes — to the victim	No — just a hung session

The tell: a session parked on enq: TX - row lock contention is blocked, not deadlocked. It will sit there forever until the holder commits, and it will never raise ORA-00060. Mutual wait =
deadlock; one-way wait = blocking. Don't go hunting for a deadlock graph when the real problem is one long-running transaction holding a lock — that's a different fix (find and commit/kill the blocker).
The wait interface that surfaces that event is covered in Oracle Wait Events, Decoded.

Reading the deadlock graph

ORA-00060 is a critical error, so Oracle writes a trace file and notes it in the alert log:

ORA-00060: Deadlock detected. ... More info in file
   /opt/oracle/diag/rdbms/.../trace/<sid>_ora_<pid>.trc

Open that trace and find the Deadlock graph — the whole diagnosis is in this one block:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006001a-000004f2        19     137     X             24     159           X
TX-00030028-000003a1        24     159     X             19     137           X

Read it as the cycle it describes: session 137 holds a lock (mode X) that session 159 waits for, and session 159 holds one that 137 waits for. The fields that matter:

Resource name / enqueue type. TX-… is a transaction (row-level) enqueue — the usual case.TM-… is a table/DML enqueue — the fingerprint of the unindexed-foreign-key problem.
holds / waits mode. X = exclusive (mode 6); S = share (mode 4 — which also covers the ITL, unique-key, and bitmap cases below); modes 3/5 (SX/SSX) show up in TM (table-lock) cases. This mode is your best clue to the cause.
Rows waited on. Just below the graph, Oracle prints the exact rowid / object# each session was blocked on — which row, and which table.
The two SQL statements. The trace then prints each session's current SQL statement — the two DMLs that crossed. That's the bug, named.

The mode is the shortcut: mode 6 (X) = a row-lock collision → fix the application's lock order; mode 4 (S) = a structural problem → look at the data (a unique-key clash, an ITL shortage, a bitmap index, or an unindexed FK).

The causes, and the fix for each

ORA-00060 triage: read the deadlock graph, let the enqueue type and lock mode point you at the cause, and fix the cause — not a parameter.

flowchart TD
  A([ORA-00060 in the alert log]) --> B[Open the trace<br/>read the Deadlock graph]
  B --> C{Enqueue type<br/>and mode?}
  C -- "TM (table lock)" --> D[Unindexed foreign key<br/>→ index the FK column]
  C -- "TX mode 6 (X)" --> E[Inconsistent update order<br/>→ lock rows in one consistent order]
  C -- "TX mode 4 (S)" --> F{Which structural cause?}
  F --> F1[Same unique/PK value inserted<br/>→ app logic / sequence]
  F --> F2[ITL shortage on a hot block<br/>→ raise INITRANS / PCTFREE]
  F --> F3[Bitmap index on an OLTP table<br/>→ use a B-tree index]

1. Inconsistent update order — the classic TX mode-6 deadlock. Session A updates row X then Y; session B updates Y then X. Each holds an exclusive row lock the other wants. Fix: update rows in a single, deterministic order everywhere (e.g., ascending by primary key) — if every code path locks in the same order, a cycle is impossible. Batch jobs are the usual offenders; sort the working set before the DML loop, and keep transactions short.

2. Unindexed foreign keys — the most famous Oracle deadlock cause. When a child table's foreign-key column is not indexed and a session updates the parent's key, deletes a parent row, or merges into the parent, Oracle takes a full-table lock on the child table (a TM share lock) — because without the index it can't cheaply find the referencing rows. That coarse lock collides with other DML and deadlocks, blocking even unrelated rows. (Note: plain inserts into the parent don't trigger it.)
Fix: put a plain B-tree index on the child's FK column. Oracle's own rule of thumb is that foreign keys should almost always be indexed — the only exception is a parent key that's never updated or
deleted.

3. Bitmap indexes on OLTP tables — a single bitmap key entry covers many rows, so DML on one row
locks the whole entry, and two sessions updating different rows can collide. Fix: don't put bitmap
indexes on tables with concurrent DML — they're for low-cardinality, read-mostly warehouse data. Use a
B-tree for OLTP.

4. ITL / INITRANS shortage — every block has an Interested Transaction List with one slot per concurrent transaction touching it; INITRANS sets the initial count (default 1 for tables, 2 for
indexes) and Oracle grows it only if the block has free space. On a hot block with no room to grow, a transaction waits on the TX enqueue in mode 4 for a slot — and two such waits deadlock. Fix: rebuild the hot segment with a higher INITRANS and/or PCTFREE, and spread the concurrency. (MAXTRANS is deprecated — Oracle now allows up to 255 transactions per block automatically, space permitting — so the lever is INITRANS/PCTFREE.)

5. Autonomous-transaction self-deadlock — a PRAGMA AUTONOMOUS_TRANSACTION routine tries to update a row the calling transaction already locked. The autonomous transaction waits on the parent's lock, but the parent is suspended waiting for the autonomous child to return — an unbreakable cycle. Fix: never
let an autonomous transaction touch rows its caller has locked; keep it to independent resources (a separate logging table).

Prevention checklist

Index every foreign key whose parent key can be updated or whose parent rows can be deleted. This kills the #1 source.
Lock resources in one deterministic order (e.g., ascending PK) across the whole application.
Keep transactions short and commit promptly — don't hold locks across user think-time or remote calls.
No bitmap indexes on OLTP tables — B-tree instead.
Raise INITRANS/PCTFREE on hot blocks prone to ITL contention.
Audit explicit locking — SELECT … FOR UPDATE, LOCK TABLE, and ORM pessimistic locking are where most "explicit override" deadlocks live.
Make the app retry on ORA-00060 — a deterministic retry after a short backoff resolves the transient cases cleanly. Then read the trace to fix the root pattern.

Want to see one for real? The deadlock lab
induces an actual ORA-00060 on Oracle Database Free — two sessions lock the same two rows in opposite order — and prints the deadlock graph straight from the trace. Then a fixed drill runs the same workload in a consistent order so the deadlock never forms. One command each; no Diagnostics Pack.

What teams get wrong

"The deadlock rolled back my transaction." It rolled back one statement. The transaction is still open, holding its earlier work and locks — your code has to react.
Confusing a deadlock with blocking. A session on enq: TX - row lock contention is blocked, not deadlocked — it'll wait forever, and no ORA-00060 is coming. Find the blocker; don't hunt a graph.
Trying to "tune" it away. There's no parameter that fixes a deadlock. The graph names the two SQL statements; the fix is in the application (lock order) or the schema (index that FK).
Ignoring the lock mode. Mode 6 vs mode 4 tells you whether the bug is your lock order or your data structure. Read it before you guess.

Where this fits

Deadlocks are a concurrency problem, and concurrency shows up in the performance picture — when one is the headline in your report, you've found it through the wait interface and the AWR Top Events. Name the event, read the graph, follow it to the SQL: the same method as How to Read an AWR Report Without Drowning and Oracle Wait Events, Decoded, applied to the one error that diagnoses itself if you let it.

FAQ

What does ORA-00060 mean?

ORA-00060, "deadlock detected while waiting for resource," means two or more sessions were each holding a lock the other needed — a circular wait. Oracle automatically detected the deadlock and broke it by rolling back one statement of one session (the victim), which receives the ORA-00060 error. The other session proceeds normally. It is almost always caused by an application locking pattern, not by a database misconfiguration.

Does ORA-00060 roll back my whole transaction?

No. Oracle rolls back only the single statement that closed the deadlock cycle, not the entire transaction. The session that receives ORA-00060 is still in an open transaction with all of its prior work and locks intact. You decide what to do next: retry the statement, roll back to a savepoint, or roll back the whole transaction. The application should handle the error and typically retry after a short delay.

What is the difference between a deadlock and blocking in Oracle?

A deadlock is a circular wait — session A waits on B while B waits on A — and Oracle detects and breaks it automatically within seconds, raising ORA-00060 to one victim. Ordinary blocking is one-directional — A waits on B only — and Oracle does nothing about it; the waiting session sits on the wait event "enq: TX - row lock contention" indefinitely until the blocker commits or rolls back, and never raises ORA-00060. A session on enq: TX - row lock contention is blocked, not deadlocked.

How do I find which SQL caused an ORA-00060 deadlock?

Oracle writes a trace file when the deadlock occurs and records its path in the alert log. Open that trace and find the "Deadlock graph" section: it lists the two transactions (as TX or TM enqueues), the lock modes each holds and waits for, the rows each session was waiting on, and — printed just below — the current SQL statement of each session. Those two statements are the colliding DML; that is the bug to fix.

What causes Oracle deadlocks?

The common causes are: inconsistent update ordering (two sessions updating the same rows in opposite order — a TX mode-6 deadlock); unindexed foreign keys (a parent-key update, delete, or merge takes a full-table lock on the child table when the FK column is not indexed); bitmap indexes on tables with concurrent DML; an ITL/INITRANS shortage on a hot block (a TX mode-4 deadlock); and autonomous transactions that touch rows locked by their caller.

How do unindexed foreign keys cause deadlocks?

When a child table's foreign-key column is not indexed and a session updates the parent table's key, deletes a parent row, or merges into the parent, Oracle cannot cheaply identify the referencing child rows, so it locks the entire child table with a table-level (TM) share lock to protect integrity. That coarse lock blocks other DML on the child table — including unrelated rows — and collides with concurrent transactions, producing deadlocks. Indexing the foreign-key column lets Oracle avoid the full-table lock.

How do I prevent ORA-00060 deadlocks?

Index every foreign key whose parent key can be updated or whose rows can be deleted; lock rows in a single deterministic order across the whole application (for example ascending by primary key); keep transactions short and commit promptly; avoid bitmap indexes on OLTP tables; raise INITRANS/PCTFREE on hot blocks prone to ITL contention; audit explicit locking such as SELECT ... FOR UPDATE; and make the application catch ORA-00060 and retry after a short backoff.

Should my application retry after ORA-00060?

Yes. Because Oracle rolls back only one statement and leaves the transaction open, a deadlock is often transient — a deterministic retry after a brief randomized backoff resolves it cleanly. In PL/SQL, associate the error with a named exception using PRAGMA EXCEPTION_INIT(my_deadlock, -60) and handle it. Retrying is a safety net, not a cure: still read the deadlock graph and fix the underlying lock-order or schema problem so it stops recurring.

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