Oracle Free image bumps aren't upgrades; how to patch the data dictionary anyway

TL;DR: Bump the Oracle Free container version vor an existing DB: container is healthy, apps run, but the data dictionary is quietly stuck on the old RU. Oracle doesn’t support Free RU patching, so nothing warns you. Here’s how to detect it and how I repair it in uc-local-apex-dev.

“The Oracle AI Database 26ai Free release is not supported for patching with RUs.” – docs.oracle.com So anything below is unsupported and not something you’d do on any database you treat as production. But they do work fine for my local dev DB.

Yet I was under the impression that upgrading was quite simple. You change the image tag from free:23.26.1.0 to free:23.26.2.0 (26ai is codenamed 23.26.x internally), keep your existing data volume, and start the stack. The container comes up healthy, and everything still works. Done. And that’s what I did for some time now with my uc-local-apex-dev project, that makes it super easy to run 26ai, ORDS, and APEX locally.

But a few weeks ago Philipp Salvisberg told me that some data dictionary views are missing after an upgrade.

The data dictionary is the database’s description of itself. Every time you query DBA_TABLES, ALL_OBJECTS or USER_SOURCE, you are reading it. These objects get created by scripts that ship with the Oracle software. Oracle calls those catalog scripts.

I investigated and indeed the Oracle Free container entrypoint does not upgrade anything. If it finds an existing database on the volume, it relinks a few config files and runs the new binaries on them.

Your data is completely fine. What is missing is everything the new releases added to the dictionary. When I measured a 23.26.0 → 23.26.2 jump, 18 dictionary views were missing: 9 assertion views that arrived in 23.26.1, and 9 Deep Data Security views that arrived in 23.26.2. So the DB tells you that you are on 23.26.2 but assertions and Deep Data Security actually do not work because of the missing objects.

And unfortunately nothing tells you: no error in the alert log, no warning on startup, and the container reports itself healthy. So I did an investigation with an LLM Agent (I am no DBA and would have never solved this).

There are two main symptoms worth knowing about.

A view that should exist, doesn’t. You read about a feature in the docs, query its view, and get told it isn’t there:

select * from dba_assertions;
-- ORA-00942: table or view "SYS"."DBA_ASSERTIONS" does not exist

The view was simply never created, because the script that creates it never ran.

Java inside the database stops working. Oracle ships a JVM inside the database. After an image swap it fails:

select dbms_java.longname('TEST') from dual;
-- ORA-29548: Java system class reported: could not find classes.bin version
--            that matches executable version

The new binaries expect one version of the Java class library, and your volume still contains the old one.

Oracle keeps a status column for each component in DBA_REGISTRY. In this state it says:

COMP_ID    STATUS
---------- --------
JAVAVM     VALID

VALID, while every single Java call fails.

In uc-local-apex-dev, you can run this script to check if your dictionary / Java is fine.

./scripts/repair-ru-dictionary.sh --check

I manually keep track of new views by RU and check for their existence:

NEW_VIEWS_23_26_1="
DBA_ASSERTIONS ALL_ASSERTIONS USER_ASSERTIONS
DBA_ASSERTION_DEPENDENCIES ALL_ASSERTION_DEPENDENCIES USER_ASSERTION_DEPENDENCIES
DBA_ASSERTION_LOCK_MATRIX ALL_ASSERTION_LOCK_MATRIX USER_ASSERTION_LOCK_MATRIX
DBA_PG_COMMENTS ALL_PG_COMMENTS USER_PG_COMMENTS
"

NEW_VIEWS_23_26_2="
DBA_APPLICATION_IDENTITIES
DBA_DATA_GRANTS ALL_DATA_GRANTS USER_DATA_GRANTS
DBA_DATA_ROLES DBA_DATA_ROLE_GRANTS
DBA_END_USER_SECURITY_CONTEXTS
DBA_END_USER_SECURITY_CONTEXT_ATTRIBUTES
DBA_END_USER_SECURITY_CONTEXT_DATA_ROLES
"

Additionally the script does a quick check on the installed JVM:

declare
	v varchar2(4000);
begin
  v := dbms_java.longname('TEST');
  dbms_output.put_line('  JVM OK (dbms_java.longname returned ' || v || ')');
exception when others then
  dbms_output.put_line('  JVM BROKEN -> ' || sqlerrm);
  dbms_output.put_line('  fix: javavm/install/update_javavm_db.sql (NOT initjvm.sql)');
end;
/

After the check script and once you’ve taken a backup, apply the repair:

./scripts/repair-ru-dictionary.sh --repair

It runs for a few minutes, mostly in the JVM reload. Your database stays open the whole time and no restart is needed (on my own environment ORDS kept serving APEX while it ran).

Finally, confirm the result:

./scripts/repair-ru-dictionary.sh --summary

That prints a plain list of key–value pairs, which is also handy in CI:

binary_version=23.26.2.0.0
dictionary_ru=RDBMS_23.26.0.0.0DBRU_LINUX.X64_250926.FREE2
missing_repairable=0
jvm_ok=yes
javavm_registry=VALID
non_valid_components=0
assertion_views=12

The two lines that matter are missing_repairable=0 and jvm_ok=yes. The first says nothing is missing that this Oracle installation knows how to create. The second is the real JVM call, not the status flag.

One value deliberately does not change: dictionary_ru still names the release that originally built your volume. I fonud no official way to updates it, and I would rather leave a record of how the database came to be than fake it.

Oracle ships two tools for exactly this kind of job. But both of them look at your database, decide everything is fine, and do nothing.

datapatch is the tool that applies the SQL half of a patch. It reports:

Binary registry: 23.26.2.0.0 Release_Update 260428181725: Installed
PDB CDB$ROOT:    Applied 23.26.0.0.0 Release_Update 260428181725 successfully
  No release update patches need to be installed

It sees the mismatch: binaries at 23.26.2, database at 23.26.0, and still concludes there is nothing to do. It matches on that long patch ID, which is identical across all 23.26.x releases, and never compares the version string beside it.

catupgrd.sql, the classic dictionary upgrade driver (and AutoUpgrade, which uses the same check), gives up even faster:

Container Database is already at current version.
** Must upgrade either a CDB$ROOT or a Pdb **

It compares the major version, 23.0.0.0.0 against 23.0.0.0.0.

Neither tool is broken. Oracle just does not support upgrades for the Free DB. It just leaves you without an obvious way forward.

The good news: Oracle still ships all the pieces. My script does call them in four steps:

1. Reload the catalog scripts for the missing features. For each missing view, the script finds the file in the Oracle installation that creates it, and runs it. Each feature usually needs two scripts: one that creates the underlying tables and privileges, and one that creates the views on top of them. Tables first, views second. The other way round just gives you broken views pointing at tables that don’t exist yet.

2. Recompile, and fix what the reload broke. Creating and replacing dictionary objects invalidates the code that depends on them. Worse, when a script changes the shape of a table, packages that were compiled against the old shape stop working. So the script recompiles everything, looks for internal packages that are still broken, reloads those specific ones, and recompiles again.

3. Reload the in-database JVM. This is a separate job with a dedicated script, update_javavm_db.sql. The obvious-looking initjvm.sql is the wrong tool: it installs a JVM from scratch and refuses when one is already there.

4. Refresh the status flags. Remember that lying VALID flag? After the repair it needs correcting. Oracle has a procedure that revalidates every component at once, and I deliberately do not use it: it marks everything invalid first and then revalidates, and on a real installation with APEX that left APEX flagged as broken even though all of its 4,500-odd objects were perfectly fine. So the script only touches components already marked invalid, and revalidates each one through the procedure the registry itself nominates for it.

All of this runs through catcon.pl, an Oracle utility that runs a script in every container of the database: the root and each pluggable database (PDB), so nothing gets half-repaired.

Step 1 above is the interesting part, so here is what “finds the file that creates it” actually means.

Everything lives in $ORACLE_HOME/rdbms/admin (on the Free image $ORACLE_HOME is /opt/oracle/product/26ai/dbhomeFree). On my 23.26.2 home that is 1,491 .sql files plus 874 wrapped .plb package bodies. Underneath it sits a backport_files directory, and that is where the per-feature patches are:

$ ls $ORACLE_HOME/rdbms/admin/backport_files | head
bug_30000046_preapply.sql
bug_35066798_initapply.sql
bug_35066798_preapply.sql
bug_35193288_postapply.sql
bug_35193288_prerollback.sql
...

$ ls $ORACLE_HOME/rdbms/admin/backport_files/*_apply.sql | wc -l
383

$ ls $ORACLE_HOME/rdbms/admin/cat*.sql | head
cataclsrv.sql
catactb.sql
catactx.sql
catacvw.sql
catadb.sql
catassertion.sql
...

$ ls $ORACLE_HOME/rdbms/admin/cat*.sql  | wc -l
420

383 apply scripts, each named after a bug number, so no match to the actual RU. 420 catalog upgrade scripts.

A feature added by an RU is split across (at least) two files. For assertions those are:

• backport_files/bug_37454805_apply.sql – creates the base tables and registers the privileges.
• catassertion.sql – creates the 12 assertion views on top of them (DBA_, ALL_, USER_ views)

The apply script is creating the necessary objects plus the privilege registration:

-- if assertion related tables are not exist, create them, in case
  -- earlier 23.0 upgrade to SQL assertion 23.x
  IF typ IS NULL THEN
  -- SQL Assertion assert$ table
  -- This table is used to store assertion definition
    BEGIN
      EXECUTE IMMEDIATE
      'CREATE TABLE SYS.ASSERT$
       (
        obj#           NUMBER NOT NULL,
        flags          NUMBER,
        property       NUMBER,
        spare0         LONG,
        tsname         VARCHAR2(128),
        version#       NUMBER,
        numdep         NUMBER,
        numlock        NUMBER,
        numstmt        NUMBER,
        spare1         NUMBER,
        spare2         NUMBER,
        spare3         VARCHAR2(4000),
        spare4         VARCHAR2(4000),
        spare5         TIMESTAMP,
        spare6         CLOB,
        sqltext        CLOB,
        modsqltext     CLOB,
        CONSTRAINT ASSERT$_PK PRIMARY KEY (obj#)
       ) TABLESPACE SYSAUX';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -955 THEN NULL;
        ELSE RAISE;
        END IF;
    END;

-- ... Also creates for SYS.ASSERTDEP$, SYS.ASSERTLOCK$, SYS.ASSERTSTMT$, SYS.ASSERTCOL$,  SYS.ASSERTJOURNAL$

REM new privileges for SQL Assertion
DELETE FROM SYSTEM_PRIVILEGE_MAP WHERE PRIVILEGE = -156;
INSERT INTO SYSTEM_PRIVILEGE_MAP VALUES (-156, 'CREATE ASSERTION', 0);
DELETE FROM SYSTEM_PRIVILEGE_MAP WHERE PRIVILEGE = -157;
INSERT INTO SYSTEM_PRIVILEGE_MAP VALUES (-157, 'CREATE ANY ASSERTION', 0);

-- ... Additional grants: ALTER ANY ASSERTION, DROP ANY ASSERTION, ASSERTION REFERENCES

GRANT CREATE ASSERTION TO DBA;
GRANT CREATE ANY ASSERTION TO DBA;
GRANT ALTER ANY ASSERTION TO DBA;
GRANT DROP ANY ASSERTION TO DBA;

-- ...

-- Record the fix for bug 37454805 into registry$backports
INSERT /*+IGNORE_ROW_ON_DUPKEY_INDEX(registry$backports, registry_backports_pk)*/
INTO sys.registry$backports (version_full, bugno)
VALUES ((SELECT version_full FROM sys.v$instance), 37454805);
COMMIT;

Note the DELETE before every INSERT and the IGNORE_ROW_ON_DUPKEY hint. Oracle writes these to be re-runnable, which is what makes it safe to run them again on a database that is only half missing the feature. The cat*.sql scripts are plain CREATE OR REPLACE VIEW plus create or replace public synonym, so they are idempotent too.

Finding the cat script is easy: grep all of rdbms/admin for the statement that creates the missing view. DBA_ASSERTIONS leads to catassertion.sql, DBA_PG_COMMENTS to catpgv.sql, DBA_DATA_GRANTS to rxsviews.sql.

Finding the apply script is harder as they don’t mention the DBA views.

But the *_rollback.sql twin does name them, because rolling back means dropping them:

DROP PUBLIC SYNONYM DBA_ASSERTIONS;
DROP VIEW DBA_ASSERTIONS;

So the trick is: find the rollback script that mentions the view, then run its _apply.sql sibling.

The nice part is that this leaves a trace. registry$backports is the table those apply scripts write to, so afterwards you can see exactly which ones ran:

select version_full, bugno from sys.registry$backports order by bugno;

VERSION_FULL        BUGNO
-------------- ----------
23.26.2.0.0      37279379
23.26.2.0.0      37454805
23.26.2.0.0      38641243
23.26.2.0.0      38922211

Those four are DBA_PG_COMMENTS, SQL assertions, and two Deep Data Security backports.

For new features the apply script also compiles the related packages:

$ grep -oE '@@?[^ ]*\.(plb|sql)' bug_35415315_apply.sql
@?/rdbms/admin/prvtbsibg.plb
@?/rdbms/admin/prvtbsibgs.plb

But some patches have changes to base tables, like adding additional columns, that leave behind invalidated PL/SQL packages. We can just scan for invalid packages, and look for their (wrapped) .plb files and re-compile them:

$ grep -lE 'PACKAGE BODY +XS_DATA_SECURITY_INT\b' $ORACLE_HOME/rdbms/admin/*.plb
prvtds.plb

This works from a known list of dictionary views, taken from Oracle’s own “Changes in This Release” reference. That means it can close the gaps it can name. But it cannot prove that nothing else is missing. Fixed tables, internal behavior changes and optimizer metadata are not visible this way.

The sharpest edge of that is the one I hinted at above: a missing view is the only thing that ever triggers a repair. The chain runs view → rollback script → apply script, so a feature that adds no new dictionary view has no entry point. 9 of those 27 package-introducing backports drop no view at all in their rollback script — they are pure PL/SQL. Had one of them landed in an RU I skipped, nothing in my check would have noticed, and missing_repairable=0 would have looked perfectly reassuring.

That is fixable in principle — you could probe for expected packages the same way I probe for views — but it means maintaining a second hand-curated list, and Oracle’s “Changes in This Release” page is much better at listing new views than new internal packages. For now I only claim the views.

So treat it as a pragmatic / best-effort repair for local dev. I was able to test and use Assertions and Deep Data Security that way.

The only reliable path is still the tedious one: create a fresh volume on the new image and move your data across with Data Pump. That gives you a dictionary built entirely by the new release, with nothing to reason about. uc-local-apex-dev has backup and import commands for this purpose.

As I use uc-local-apex-dev daily myself and want to try out new features I am committed to making the script work for any future versions. My process is to spin up old DBs and load some close-to-life sample data. Then I upgrade the container, see if everything is still running and whether metadata objects for the new release are missing. Then if I install their scripts, I check if the feature is working as expected.

If there is one thing to take away: changing an Oracle Free container image tag on an existing data volume is not an upgrade. It is new software opening old files. Your data comes through fine, which is exactly why the problem is so easy to miss.

I hope that Oracle treats Free users with an easy upgrade path in the future. Free is a good way to let devs try out a full Oracle database. It is great to have quarterly upgrades now, so it would make sense to let devs try them on existing DBs. What’s the goal of a trial version if it is tedious to keep up with it?

The script lives in uc-local-apex-dev, which is open source, along with the migration guides for each release. If you are setting up a local environment from scratch, I wrote about running APEX 26.1 locally and about moving to 26ai.