Upgrade Oracle Database 19c CDB to Oracle AI Database 26ai

Things to Know

You can upgrade to Oracle AI Database 26ai if your database runs 19c or 21c.

• If you have an older like 11g or 12c database , you must first upgrade to 19c and then upgrade again to 26ai.
• If you are on 23ai, you can just patch the database; no upgrade needed, no need to re-certify your app.
• Oracle AI Database 26ai supports the multitenant architecture only. If your database is a non-CDB, you must also convert it to a pluggable database as part of the upgrade.
• Oracle AI Database 26ai is the next long-term support release. It means you can stay current with patches for many years. Premier Support ends in December 2031.

In Oracle AI Database 26ai, AutoUpgrade is the only recommended tool for upgrading your database. Oracle desupported the Database Upgrade Assistant (DBUA).

Prerequisites

Make sure you have all the OS prerequisites in place by running the 26ai preinstall package. On Oracle Linux you can do this by installing the preinstall package. It probably makes sense to update the remaining packages also.

# root user

# dnf install -y oracle-ai-database-preinstall-26ai

# yum update -y

Install 26ai Software

The following commands will perform a silent installation of the 26ai software.

# oracle user

$mkdir -p /oracle/product/26.0.0/dbhome_1

$mkdir -p /oracle/oraInventory

$export ORACLE_BASE=/oracle

$export ORACLE_HOME=$ORACLE_BASE/product/26.0.0/dbhome_1

$export ORA_INVENTORY=/oracle/oraInventory

$cd $ORACLE_HOME

$unzip -oq $ORACLE_HOME/LINUX.X64_2326100_db_home.zip

$./runInstaller -ignorePrereq -waitforcompletion -silent              \

    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp     \

    installOption=INSTALL_DB_SWONLY                                  \

    UNIX_GROUP_NAME=oinstall                                         \

    INVENTORY_LOCATION=${ORA_INVENTORY}                              \

    ORACLE_HOME=${ORACLE_HOME}                                       \

    ORACLE_BASE=${ORACLE_BASE}                                       \

    installEdition=EE                                                \

    OSDBA=dba                                                        \

    OSBACKUPDBA=dba                                                  \

    OSDGDBA=dba                                                      \

    OSKMDBA=dba                                                      \

    OSRACDBA=dba

                                                        OR

[oracle@Linux8 dbhome_1]$ ./runInstaller--------GUI Base Installation

Run the root scripts when prompted.

As a root user, execute the following script(s):

        1. /oracle/product/26.0.0/dbhome_1/root.sh

At this point you should also patch the new Oracle home if required. In my case patch not required on new 26ai home.

Download the latest "autoupgrade.jar" file from this link.

Create a Directory for AutoUpgrade

[oracle@Linux8 dbhome_1]$ java -version

openjdk version "1.8.0_492"

OpenJDK Runtime Environment (build 1.8.0_492-b09)

OpenJDK 64-Bit Server VM (build 25.492-b09, mixed mode)

[oracle@Linux8 dbhome_1]$ mkdir -p /oracle/autoupgrade/logs

[oracle@Linux8 dbhome_1]$ mkdir -p /oracle/autoupgrade/orcl

[oracle@Linux8 dbhome_1]$ mkdir -p /oracle/autoupgrade/config

[oracle@Linux8 dbhome_1]$ cd /oracle/autoupgrade

[oracle@Linux8 autoupgrade]$ ll

total 7020

-rw-r--r-- 1 oracle oinstall 7174061 Jul 27 02:33 autoupgrade.jar

drwxr-xr-x 2 oracle oinstall    4096 Jul 27 06:40 config

drwxr-xr-x 2 oracle oinstall    4096 Jul 27 06:36 logs

drwxr-xr-x 2 oracle oinstall    4096 Jul 27 06:37 orcl

Create the AutoUpgrade Configuration File

$ cat /oracle/autoupgrade/config/config.cfg

#Config file content

upg1.sid=orcl

upg1.source_home=/oracle/product/19.3.0/dbhome_1

upg1.target_home=/oracle/product/26.0.0/dbhome_1

upg1.target_version=26

upg1.log_dir=/oracle/autoupgrade/orcl

upg1.timezone_upg=yes

upg1.run_utlrp=yes

Run AutoUpgrade Analyze Mode

Set 26ai environment:

export ORACLE_HOME=/oracle/product/26.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

Run in analyze mode:

$ /oracle/product/26.0.0/dbhome_1/jdk/bin/java -jar /oracle/autoupgrade/autoupgrade.jar -config /oracle/autoupgrade/config/config.cfg -mode analyze

This step checks:

~Database compatibility

~ Invalid objects

~ Tablespace issues

~ Time zone issues

~ Parameter problems

~ Component compatibility

Screens of Analyze mode run:

$ORACLE_BASE/product/26.0.0/dbhome_1/jdk/bin/java \
  -jar $ORACLE_BASE/autoupgrade/autoupgrade.jar \
  -config $ORACLE_BASE/autoupgrade/config/config.cfg -mode analyze

AutoUpgrade 26.2.260205 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 CDB(s) plus 2 PDB(s) will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]

Please check the summary report at:
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.html
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.log

Once complete, let’s review status.html again. It should all clean and good to go with upgrade.

Run AutoUpgrade Fixups (Optional)

$ORACLE_BASE/product/26.0.0/dbhome_1/jdk/bin/java \
  -jar $ORACLE_BASE/autoupgrade/autoupgrade.jar \
  -config $ORACLE_BASE/autoupgrade/config.cfg -mode fixups

AutoUpgrade 26.2.260205 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 CDB(s) plus 2 PDB(s) will be processed
Type 'help' to list console commands
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]

Please check the summary report at:
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.html
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.log

Once complete, let’s review status.html again. It should all clean and good to go with upgrade.

Run AutoUpgrade Deploy

We are now ready to run the database upgrade with the following command. The upgrade takes some time, so you will be left at the "upg" prompt until it's complete.

$ORACLE_BASE/product/26.0.0/dbhome_1/jdk/bin/java \
  -jar $ORACLE_BASE/autoupgrade/autoupgrade.jar \
  -config $ORACLE_BASE/autoupgrade/config.cfg -mode deploy

AutoUpgrade 26.2.260205 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 CDB(s) plus 2 PDB(s) will be processed
Type 'help' to list console commands
upg>

Use the "help" command to see the command line options. We can list the current jobs and check on the job status using the following commands.

upg> lsj
+----+-------+---------+---------+-------+----------+-------+-------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------+
| 102|   orcl|DBUPGRADE|EXECUTING|RUNNING|  05:11:48| 3s ago|Running|
+----+-------+---------+---------+-------+----------+-------+-------+
Total jobs 1

upg> status -job 102
Details

        Job No           102
	Oracle SID       orcl
	Start Time       26/07/28 05:11:48
	Elapsed (min):   0
	End time:        N/A

Logfiles

        Logs Base:    /oracle/autoupgrade/orcl/orcl
	Job logs:     /oracle/autoupgrade/orcl/orcl/102
	Stage logs:   /oracle/autoupgrade/orcl/orcl/102/prechecks
	TimeZone:     /oracle/autoupgrade/orcl/orcl/temp
        Remote Dirs:

Stages
        SETUP            <1 min
        GRP              <1 min
        PREUPGRADE       <1 min
        PRECHECKS        <1 min
        PREFIXUPS        <1 min
        DRAIN            <1 min
        DBUPGRADE        ~0 min (RUNNING)
        POSTCHECKS
        POSTFIXUPS
        POSTUPGRADE
        SYSUPDATES

Stage-Progress Per Container

        +--------+---------+
        |Database|DBUPGRADE|
        +--------+---------+
        |CDB$ROOT|    0  % |
        |PDB$SEED|    0  % |
        |    PDB1|    0  % |
        +--------+---------+

upg>

Once the job completes a summary message is displayed.

Job 102 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]

--Drop GRP at your convenience once you consider it is no longer needed --
Drop GRP from orcl: drop restore point AUTOUPGRADE_9212_ORCL1930000

Please check the summary report at:
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.html
/oracle/autoupgrade/logs/cfgtoollogs/upgrade/auto/status/status.log

The "status.html" contains the top-level information about the upgrade process.

At this point I do a shutdown and startup to make sure everything is running in the correct mode.

$export ORACLE_HOME=$ORACLE_BASE/product/26.0.0/dbhome_1
$export PATH=$ORACLE_HOME/bin:$PATH
$export ORACLE_SID=orcl

SQL> sqlplus / as sysdba
SQL>shutdown immediate;
SQL>startup;

SQL>show pdbs
SQL>exit

Post-Upgrade Checks

Edit the "/etc/oratab" file and any environment files as required.

ORACLE_SID:ORACLE_HOME:AUTOSTART

orcl:$ORACLE_BASE/product/26.0.0/dbhome_1:Y

Checks DB Version

Let’s connect to the database by setting the environment variables for the Oracle 26ai Home.

[oracle@Linux8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 28 07:45:51 2026

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit

Disconnected

[oracle@Linux8 ~]$ . oraenv

ORACLE_SID = [ORCL] ? 

The Oracle base remains unchanged with value /oracle

[oracle@Linux8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.1.0.0 - Production on Tue Jul 28 07:48:24 2026

Version 23.26.1.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connected to:

Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production

Version 23.26.1.0.0

SQL> @dt

NAME OPEN_MODE DATABASE_ROLE CONTROLFIL

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

ORCL      READ WRITE     PRIMARY         CURRENT

SQL> select banner from v$version;

BANNER

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

Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production

When I’m done testing the database – including application testing – and I decided that a rollback is not needed, I’ll drop the GRP:

SQL> drop restore point AUTOUPGRADE_9212_ORCL193000;

How to Gather Schema Statistics for the APEX

You can gather statistics for the Apex (Oracle Application Express) schema. However, in an Oracle E-Business Suite (EBS) environment, you must use standard DBMS_STATS procedures instead of the EBS-specific FND_STATS wrapper for non-EBS schemas.

Recommended Steps to Gather Stats

Check Schema Statistics

This query gives you a quick overview of the total number of tables, indexes, and how many of them have missing or stale statistics.

SELECT
    owner,
    COUNT(*) as total_tables,
    SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) as missing_stats,
    MIN(last_analyzed) as oldest_stats,
    MAX(last_analyzed) as newest_stats
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
GROUP BY owner;

Gather Schema Statistics

You can gather these statistics directly using the DBMS_STATS package while connected as the SYS or SYSTEM user via SQL*Plus or SQL Developer.

Run the following PL/SQL block in your database:

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname          => 'APEX_SCHEMA',---like APEX_200200
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;
/

Gather Fixed Object Statistics (If performance is slow globally)

If APEX is running slow globally across the database, also consider ensuring your fixed objects have fresh stats:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Best Practices & Warnings for EBS

Do NOT use FND_STATS: EBS-specific wrappers (like FND_STATS.GATHER_SCHEMA_STATS) check for EBS specific metadata dictionary tables. Using them on the APEX schema may fail or corrupt standard executions. Use native DBMS_STATS instead.

Backup: If you are unsure about the impact, you can create a stats table to export the current APEX statistics first using DBMS_STATS.CREATE_STAT_TABLE and DBMS_STATS.EXPORT_SCHEMA_STATS, allowing you to roll back if necessary.

Here is the precise SQL script to backup your APEX_SCHEMA statistics and rollback them if you encounter performance issues.

Run these steps as the SYS or SYSTEM user.

Create the Backup Table

Create a dedicated table to hold the current statistics. You can place this in a system schema or a custom DBA schema (e.g., SYSTEM).

EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname => 'SYSTEM', stattab => 'APEX_STATS_BKP');

Export the Current Statistics

Export the existing statistics of the APEX schema into your newly created backup table.

BEGIN

  DBMS_STATS.EXPORT_SCHEMA_STATS (
    ownname => 'APEX_SCHEMA',---like APEX_200200
    stattab => 'APEX_STATS_BKP',
    statown => 'SYSTEM'
  );
END;
/

Gather Schema Statistics Run the following PL/SQL block in your database:

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS (

    ownname          => 'APEX_SCHEMA',---like APEX_200200

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',

    cascade          => TRUE,

    degree           => DBMS_STATS.AUTO_DEGREE

  );

END;

/

How to Roll Back (Only if needed)

If queries slow down after gathering new stats, restore the original metadata from your backup table:

BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS (
    ownname => 'APEX_SCHEMA',---like APEX_200200
    stattab => 'APEX_STATS_BKP',
    statown => 'SYSTEM'
  );
END;
/

Clean Up

Once you verify that the new statistics work well and performance is stable, drop the backup table:

EXEC DBMS_STATS.DROP_STAT_TABLE(ownname => 'SYSTEM', stattab => 'APEX_STATS_BKP');

You can check when statistics were last gathered for the APEX_SCHEMA schema by querying the data dictionary views.

Run these scripts as a user with DBA privileges (like SYS or SYSTEM).

Check Schema-Level Summary

This query gives you a quick overview of the total number of tables, indexes, and how many of them have missing or stale statistics.

SELECT
    owner,
    COUNT(*) as total_tables,
    SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) as missing_stats,
    MIN(last_analyzed) as oldest_stats,
    MAX(last_analyzed) as newest_stats
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
GROUP BY owner;

Check Specific Tables and Row Counts

This query shows the exact date and time statistics were last gathered for every table in the APEX schema, sorted from newest to oldest.

SELECT
    table_name,
    num_rows,
    blocks,
    TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_gathered,
    stale_stats
FROM dba_tab_statistics
WHERE owner = 'APEX_SCHEMA'
ORDER BY last_analyzed DESC;

What to Look For

STALE_STATS = YES: The data has changed significantly since the last gather, and Oracle considers these stats outdated.

LAST_ANALYZED IS NULL: Statistics have never been gathered for this object.

Delete statistics completely for a specific table

To remove statistics completely for a specific table in the APEX_SCHEMA schema, you use the DBMS_STATS.DELETE_TABLE_STATS procedure. This immediately clears out the table, column, and index metadata, forcing the Oracle Cost-Based Optimizer (CBO) to use its built-in dynamic sampling or internal default cards for that table.

Run this script as a user with DBA privileges (such as SYS or SYSTEM).

The Script to Delete Table Statistics

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS (
    ownname   => 'APEX_SCHEMA',
    tabname   => 'YOUR_TABLE_NAME',
    cascade_parts => TRUE,  -- Deletes partition-level statistics if applicable
    cascade_columns => TRUE, -- Deletes associated column statistics/histograms
    cascade_indexes => TRUE  -- Deletes all associated index statistics
  );
END;
/

Verify the Statistics are Gone

After running the deletion block, execute this query to confirm that the metadata has been purged. The statistics columns should now return empty (NULL).

SELECT
    table_name,
    num_rows,
    blocks,
    last_analyzed
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
  AND table_name = 'YOUR_TABLE_NAME';

A Crucial Next Step

If you delete statistics because they were causing a bad execution plan, you must lock the table immediately after deletion. If you leave it unlocked, Oracle’s automatic nightly maintenance job will automatically gather fresh statistics again, undoing your deletion.

Lock the statistics

Locking statistics prevents the automatic nightly maintenance job or accidental manual runs from overwriting your preferred, stable statistics.

Run these commands as a user with DBA privileges (such as SYS or SYSTEM).

Lock Statistics for the Entire Schema

To lock the statistics for every table and index inside the APEX_SCHEMA schema, use the following command:

EXEC DBMS_STATS.LOCK_SCHEMA_STATS(ownname => 'APEX_SCHEMA');

Verify the Lock Status

You can verify that the statistics are successfully locked by checking the STATTYPE_LOCKED column in the data dictionary. If it returns ALL, the statistics are locked.

SELECT table_name, stattype_locked FROM dba_tab_statistics
WHERE owner = 'APEX_SCHEMA';

How to Unlock Statistics (If you need to update them later)

If you patch APEX, perform a large data load, or decide it is time to gather new statistics, you must unlock the schema first:

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'APEX_SCHEMA');

Alternative: Lock a Single Specific Table

If you only want to freeze statistics for a specific problematic table instead of the whole schema, use these table-level commands:

-- Lock a single table

EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => 'APEX_SCHEMA', tabname => 'YOUR_TABLE_NAME');

-- Unlock a single table

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'APEX_SCHEMA', tabname => 'YOUR_TABLE_NAME');

Automatically gather statistics ONLY on stale tables

You can identify tables with stale statistics automatically by querying Oracle's data dictionary views or by using a PL/SQL block that utilizes the DBMS_STATS package.

The fastest way to view all stale or missing statistics for the APEX_SCHEMA schema is to query DBA_TAB_STATISTICS.

SELECT
    table_name,
    num_rows,
    TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_gathered,
    stale_stats
FROM dba_tab_statistics
WHERE owner = 'APEX_SCHEMA'
  AND (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY table_name;

STALE_STATS = YES: Means data changes have crossed the 10% threshold.

LAST_ANALYZED IS NULL: Means statistics are missing completely.

The Automated PL/SQL Script

This script flushes the latest database monitoring information to ensure accurate tracking data, then automatically gathers statistics only on the stale or unanalyzed objects within the APEX_SCHEMA schema.

BEGIN
--Step1:Force Oracle to flush the latest row modification tracking data to disk
 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
--Step2:Gather stats ONLY on stale or missing tables
  DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname          => 'APEX_SCHEMA',
    options          => 'GATHER STALE',  -- Crucial option: skips non-stale tables
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;
/

Alternative: "GATHER EMPTY"

If you are worried about performance and only want to populate statistics for tables that have never been analyzed before (completely missing stats), change the option parameter:

options  => 'GATHER EMPTY'-- Processes only tables with NULL statistics

Why use GATHER STALE?

Saves Time: It drastically cuts down script runtime by avoiding large, unchanged tables.
Reduces I/O Overhead: It prevents unnecessary disk reads and CPU usage on your EBS database container.
Maintains Performance: It updates query execution plans only where major data shifts have actually occurred.