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

Let me show you how you upgrade an entire container database, including all PDBs, to Oracle AI Database 26ai.

How to Upgrade

I’ve already prepared my database and installed a new Oracle home. The maintenance window has started, and users have left the database.

This is my AutoUpgrade config file:
```
global.global_log_dir=/home/oracle/autoupgrade/logs/CDB19
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/26
upg1.sid=CDB19
```
- sid contains the name of SID or my database.
- I specify the source and target Oracle homes. I’ve already installed the target Oracle home.
- Check the appendix for additional parameters.
I start AutoUpgrade in deploy mode:
```
java -jar autoupgrade.jar -config CDB19.cfg -mode deploy
```
- AutoUpgrade starts by creating a guaranteed restore point to protect my database.
- Then, it analyzes the database for upgrade readiness and executes the pre-upgrade fixups. Next is the actual upgrade, followed by post-upgrade checks and fixups.
While the job progresses, I monitor it:
```
upg> lsj -a 30
```
- The -a 30 option automatically refreshes the information every 30 seconds.
- I can also use status -job 100 -a 30 to get detailed information about a specific job.

In the end, AutoUpgrade completes the upgrade:

Job 100 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 CDB19: drop restore point AUTOUPGRADE_9212_CDB191927000

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

This includes the post-upgrade checks and fixups, incl. updating /etc/oratab and Grid Infrastructure configuration.

I review the Autoupgrade Summary Report. The path is printed to the console:

vi /home/oracle/autoupgrade/logs/CDB19/cfgtoollogs/upgrade/auto/status/status.log

I take care of the post-upgrade tasks.
I update any profiles or scripts that use the database.
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_CDB191927000;
```

That’s It!

With AutoUpgrade, you can easily upgrade your entire CDB.

Check the other blog posts related to upgrade to Oracle AI Database 26ai.

Happy upgrading!

Appendix

What If My Database Is A RAC Database?

There are no changes to the procedure if you have an Oracle RAC database. AutoUpgrade detects this and sets CLUSTER_DATABASE=FALSE at the appropriate time. It also updates the Grid Infrastructure configuration.

For warp-speed upgrades, take a look at distributed upgrade.

What If I Use Oracle Restart?

No changes. AutoUpgrade detects this and automatically updates the Grid Infrastructure configuration.

What If My Database Is Encrypted

You must use an auto-login keystore. AutoUpgrade checks this during the pre-upgrade analysis.

Multitenant Upgrade

A few words about an upgrade of an entire CDB. AutoUpgrade first upgrades the root container. When that completes, it will upgrade the seed container and your PDBs. The latter part happens in parallel.

So, upgrading a CDB is always slower than just upgrading a single PDB. If you’re tight on time, consider upgrading just the PDB. This is called an unplug-plug upgrade.

Other Config File Parameters

The config file shown above is a basic one. Let me address some of the additional parameters you can use.

drop_grp_after_upgrade: AutoUpgrade doesn’t drop the GRP after the upgrade. This allows you to roll back even after a successful upgrade if your application testing reveals a problem. However, it means that you must manually remember to drop the GRP. Otherwise, you’ll eventually run out of space in FRA, and that will halt your database. Set this parameter to yes, and AutoUpgrade drops the GRP if the upgrade completes without problems.
timezone_upg: AutoUpgrade upgrades the database time zone file after the actual upgrade. This requires an additional restart of the database and might take significant time if you have lots of TIMESTAMP WITH TIME ZONE data. If so, you can postpone the time zone file upgrade or perform it in a more time-efficient manner.
raise_compatible: If you want to raise the initialization parameter COMPATIBLE immediately after the upgrade, you can use this parameter. Don’t use if you have standby databases, because there’s a specific procedure for raising COMPATIBLE in a Data Guard configuration. Don’t use it if you want to keep the option of downgrading.
before_action / after_action: Extend AutoUpgrade with your own functionality by using scripts before or after the job.
em_target_name: Allow AutoUpgrade to create blackouts before restarting the database and to update the target configuration after the upgrade. Requires a local installation of emcli. See this blog post for details.
rman_catalog_connect_string: Relevant for databases that store backup metadata in a recovery catalog. Upgrades the recovery catalog schema following an upgrade. See this blog post for details.

How to Prepare Your Oracle Database for Release 26ai

Here is a list of things to check and carry out while preparing for Oracle AI Database 26ai.

These tasks are not mandatory, but I recommend them based on my experience. It increases your chances of upgrading successfully.

Weeks Before

Versioning

Oracle AI Database 26ai introduces a new versioning schema. Although 26ai replaces 23ai, in the database, the version remains the same:

After upgrade to 26ai, the version remains 23.0.0.0.0:

SQL> select version from v$instance;

VERSION
-------
23.0.0.0.0

If you check version_full, you’ll see that the second digit reflects the new version:

SQL> select version_full from v$instance;

VERSION
-------
23.26.0.0.0

Platform Certification

Ensure that the new release supports your operating system. When available, you can check it in the installation guides. However, you can find the most up-to-date information in the Product Certification Matrix tab in My Oracle Support.

Clients

Check which clients are connecting to the database and ensure their client driver versions are compatible with Oracle AI Database 26ai. The client/server interoperability matrix gives you an overview of which clients work with which servers.

Client	Database 26ai	Database 19c
26ai	Yes	Yes
21c	Yes	Yes
19c	Yes	Yes
18c	No	Yes
12.2.0.1	No	Yes
12.1.0.2	No	Yes
11.2.0.4	No	Yes

19c supports much older clients compared to 26ai. If you’re using such old clients, start upgrading them right away. This also applies to database links.

Optionally, monitor the database over time to generate a list of clients connecting.

AutoUpgrade

Get the latest version of AutoUpgrade from My Oracle Support. I recommend that you always use the latest version. AutoUpgrade is fully backward compatible, so any newer version can upgrade older releases of Oracle AI Database.

Upgrade Readiness

Run AutoUpgrade in analyze mode to determine your database’s upgrade readiness. Check the summary report for findings with no fixups. Such findings must be dealt with manually.

You can run the analysis even before you install the new Oracle home. Simply specify target_version=26 in your AutoUpgrade config file, and AutoUpgrade knows which checks to run.

Dictionary Check

Check the dictionary in your most important databases. You can run a dictionary check together with an AutoUpgrade analysis. In your config file:

upg1.run_dictionary_health=full

Or, you can run it separately using DBMS_DICTIONARY_CHECK.

Behavior Changes

Review the upgrade guide for information on deprecated and desupported features, plus any behavior changes.

Also, use ORAdiff to check for changes between your current release and 26ai. Use the Report menu item to generate a report that you can share with your company.

It’s also a good idea to look at the new reserved keywords. Especially, those marked as reserved. Don’t use those keywords in object/column names, queries, or the like.

Grid Infrastructure

If Grid Infrastructure manages your database either in RAC or Oracle Restart configuration, you should upgrade it as well. If you can tolerate an additional maintenance window, I’d recommend upgrading Grid Infrastructure in advance.
Grid Infrastructure 26ai can manage databases from 19c and onwards. Any older database must be moved away from the system.
I recommend keeping the GI and database patch levels in sync. If you want to upgrade the database to 26.1, then upgrade GI to the same Release Update. If that’s not possible, at least keep it within two Release Updates.

Days Before

Statistics

Gather dictionary and fixed objects statistics:

begin
   dbms_stats.gather_fixed_objects_stats;
   dbms_stats.gather_schema_stats('SYS');
   dbms_stats.gather_schema_stats('SYSTEM')
end;

I prefer gathering schema stats rather than dictionary stats. I’ve seen a few edge cases solved by schema stats, and generally, current stats on SYS and SYSTEM are sufficient.
Gather statistics no earlier than seven days before the upgrade. If AutoUpgrade determines your statistics are older than that, it will gather them as part of the upgrade. This extends downtime unnecessarily.
If you’re curious, you can find details about the importance of fixed objects statistics in a blog post by Maria Colgan.

Install Oracle Home

You can use AutoUpgrade to:

In release 26ai, the Oracle homes that you download are already patched with the latest Release Update and OPatch.

On the Day of Upgrade

Backup

If time allows, run a level 0 backup. If not, you have to settle with a level 1.

Don’t run the backups inside your maintenance windows; that’s a waste of downtime. Start the backup in advance, so it finishes just before the maintenance window begins.

AutoUpgrade automatically creates a guaranteed restore point before starting the upgrade.

Scheduler

Normally, it is not necessary to disable the scheduler. But in some situations, it can be beneficial.

That’s It

You are now ready to begin your journey to Oracle AI Database 26ai and its many exciting new features.

Check the other blog posts related to upgrade to Oracle AI Database 26ai.

Happy upgrading!

The Best Approach For Backup Before Upgrade

While doomscrolling on LinkedIn the other day, I came across an interesting post:

What is the best approach to take 65TB database backup before upgrade from 12c to 19c?

This post started a discussion with many good answers and ideas. Let me add my thoughts.

The Ideas

Cold Backup

Stop DB and take a cold backup.

I don’t recommend this.
A cold backup means downtime.
An online backup works just as fine.

Don’t Make Any Preparations

You should be able to restore at any time within the timeframe specified by your business/SLA.

You should always be prepared!
If our SLA allows us to use four hours, why not make it faster?
You could start an additional level 0 or level 1 backup before the maintenance window. That would reduce the number of archive logs, thereby reducing the time to restore.

Partial Offline Backup

Set app tablespace to read-only, then perform a partial offline backup (or a regular backup). In case of a restore, you need to restore less data.

This is a special backup strategy that you normally don’t use. Be sure to test and practice it.
A good solution for Standard Edition 2 databases, since you don’t have access to Flashback Database or parallel RMAN backups/restores.
A viable approach, but not my favorite, since it’s a technique that you rarely use.

Data Guard

Just keep your standby not upgraded.

This conflicts with the MAA guidelines that suggest keeping the standbys online and applying redo.
But I like this approach. You need a little more time after the upgrade to go live, because the standby must catch up. But that shouldn’t take long, as an upgrade doesn’t generate that much redo.
If you have multiple standbys, this technique becomes even more attractive. Have some standbys follow the primary (to go live faster) and a few lagging behind (in case you need to rollback).

Flashback Database

Or keep compatible at 12 and create a guaranteed restore point before the upgrade so you can flashback.

Yes – my favorite.
Flashback Database should be your first rollback option. It’s so fast and so convenient.

Another reply:

I just enable Flashback Database, create guaranteed restore point, and start the upgrade. After validation of application, drop the guarantee restore point.

The answer suggests that Flashback Database was the only rollback option.
As much as I love Flashback Database, don’t rely solely on it.
It should be your first option, but not the only one.
By the way, you don’t have to enable Flashback Database to create a guaranteed restore point. Without Flashback Database turned on, you can still revert back to a restore point.

Downgrade

Oracle Database also has downgrade method.

Downgrade is a cool option. But it’s a fallback option – something you can use days or weeks later following a successful upgrade.
If the upgrade crashes midway, you can’t use a downgrade to go back.

Data Pump

Export import is a good option.

Export/import is not a rollback plan. Similar to downgrade, if the upgrade crashes midway, you won’t be able to use Data Pump.
Days or weeks later, you can use Data Pump. But it might be a viable option for larger databases.

GoldenGate

Another approach can be use of GoldenGate

This is an awesome way to upgrade.
Especially if you go the extra mile and use active/active replication. Then, you can gradually move sessions to the new database and test things slowly.
But are you willing to pay the price? Not only license fees, but also the added complexity.

What Does Oracle Say?

If you check the Upgrade Guide you will find one recommendation:

Perform a level 0 backup if time allows. Otherwise, at least a level 1 backup.

By doing a level 1 backup close to the maintenance window, then you can limit the amount of archive logs that you would need to apply on top of your level 0/1 backups. This can dramatically reduce the time it takes to restore.

In addition, AutoUpgrade automatically creates a guaranteed restore point prior to the upgrade.

My Final Thoughts

This discussion on LinkedIn is a really good example that there’s no such thing as one-size-fits-all in tech. It always depends…

I recommend finding a solution that:

You’re comfortable with.
Fits your business requirements and SLA.
You know work, and you gain that knowledge by testing it. You never know that it works until you test it.
Doesn’t stand alone. You need multiple rollback options.

What are your thoughts? Which rollback options do you have? Leave a comment below.

Happy upgrading!

RAC Rolling Patching in Autoupgrade – Looking for Early Testers

Support for RAC rolling patching in AutoUpgrade has been on our wishlist for a while. Finally, our developers are about to write the last lines of code.

We plan to release the feature next year, but we’d like to get some early feedback on the functionality.

If you’d like to give it a try, here’s your chance.

What Do You Get

Early access to the functionality.
The latest version of AutoUpgrade, plus the code for RAC rolling patching.
Introduction to the feature and a short description of how it works.
Possibility to provide feedback and influence our decisions.
A big THANK YOU for helping us.

What Do We Expect

You have time and resources to test the functionality in your own environment.
You will report the errors as service requests using My Oracle Support.
You can share the AutoUpgrade log files.

What We Hope

If you like the feature, you will help us spread the message.
We’d also love it if you could provide a quote, a video, or even a reference story.

How

Send me an e-mail at daniel.overby.hansen (a) oracle.com.
Add a few words about your setup and such.
I can’t guarantee that everyone will be able to help us.

I’m really excited about this feature, and I know many of you are waiting for it.

Upgrade to Oracle AI Database 26ai

Get ready for the future and enjoy the many new cool features in Oracle AI Database 26ai. It is just an upgrade away.

This blog post gives you a quick overview of the upgrade to Oracle AI Database 26ai. Plus, it is the starting point of a whole series of blog posts with all the details.

Things to Know

You can upgrade to Oracle AI Database 26ai if your database runs 19c or 21c.
- If you have an older database, you must first upgrade to 19c and then upgrade again to 26ai.
- If you’re 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.
Although the Multitenant Option requires a separate license, you can still run databases on the multitenant architecture without it. Oracle allows a certain number of pluggable databases in a container database without the Multitenant Option. Check the license guide for details, and be sure to set max_pdbs=3 if you don’t have the license.
Oracle AI Database 26ai is the next long-term support release. It means you can stay current with patches for many years. At the time of writing, Premier Support ends in December 2031, but check Release Schedule of Current Database Releases (Doc ID 742060.1) for up-to-date information.
In Oracle AI Database 26ai, AutoUpgrade is the only recommended tool for upgrading your database. Oracle desupported the Database Upgrade Assistant (DBUA).
You can also use Data Pump or Transportable Tablespaces to migrate your data directly into a 26ai PDB. Even if the source database runs on a lower release and in a non-CDB. In fact, you can export from Oracle v5 and import directly into a 26ai PDB.

Important Things about Multitenant Migration

The multitenant conversion is irreversible. Not even Flashback Database can help if you want to roll back. You must consider this when planning for a potential rollback.
For smaller databases, you can rely on RMAN backups. However, for larger databases, a restore may take too long.
For rollback, you can use a copy of the data files:
- The CREATE PLUGGABLE DATABASE statement has a COPY clause, which copies the data files and uses the copies for the new PDB.
- Refreshable clone PDB can minimize the time needed to copy the data files by doing it in advance and rolling forward with redo.
- Use image copies of your data files and roll forward with RMAN.
- Use a standby database for rollback.
- Storage snapshots
Depending on your Data Guard configuration, the plug-in operation needs special attention on your standby databases. If you have standby databases, be very thorough and test the procedure properly.
In the worst case, you can break your standby databases without knowing it. Be sure to check your standby databases at the end of the migration. I recommend performing a switchover to be sure.
The multitenant conversion requires additional downtime. Normally, I’d say around 10-20 minutes of additional downtime. But if you have Data Guard and must fix your standby databases within the maintenance window, then you need even more time.

And Then …

Over the coming months, I will publish several blog posts with step-by-step instructions and other info. Stay tuned!

By the way, this blog post was originally posted a while ago for Oracle Database 23ai, but we all know what happened there. So, let’s start over with Oracle AI Database 26ai.

Happy upgrading!

How To Upgrade 100 PDBs And Move Them To Another Server

The other day, I helped a customer with an interesting case:

We have a 19c CDB with 100 PDBs running on old hardware. We need to upgrade and move the PDBs to a new server with Oracle Database 23ai. We would like to move the PDBs in batches.

Here’s how I would do that using AutoUpgrade and refreshable clone PDBs.

How To

In each PDB, you must create a user that you can use for the database link:

create user dblinkuser identified by dblinkuser;
grant create session to dblinkuser;
grant select_catalog_role to dblinkuser;
grant create pluggable database to dblinkuser;
grant read on sys.enc$ to dblinkuser;

Repeat this process for every source PDB.

In the target CDB, create a database link in the root container for every PDB:
```
create database link clonepdb1
connect to dblinkuser
identified by dblinkuser
using 'sourcehost/pdb1';
```
- Repeat this process for each PDB and assign a unique name to the database link.

Create an AutoUpgrade config that upgrades a batch of the PDBs. In this case, PDBs 1-4:

global.global_log_dir=/home/oracle/logs
global.keystore=/home/oracle/keystore

upg1.sid=CDB19
upg1.target_cdb=CDB23
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.pdbs=PDB1,PDB2,PDB3,PDB4
upg1.source_dblink.PDB1=CLONEPDB1 1800
upg1.source_dblink.PDB2=CLONEPDB2 1800
upg1.source_dblink.PDB3=CLONEPDB3 1800
upg1.source_dblink.PDB4=CLONEPDB4 1800
upg1.target_pdb_copy_option.PDB1=FILE_NAME_CONVERT=NONE
upg1.target_pdb_copy_option.PDB2=FILE_NAME_CONVERT=NONE
upg1.target_pdb_copy_option.PDB3=FILE_NAME_CONVERT=NONE
upg1.target_pdb_copy_option.PDB4=FILE_NAME_CONVERT=NONE
upg1.target_pdb_name.PDB1=PDBNEW1
upg1.target_pdb_name.PDB2=PDBNEW2
upg1.target_pdb_name.PDB3=PDBNEW3
upg1.target_pdb_name.PDB4=PDBNEW4
upg1.parallel_pdb_creation_clause.PDB1=2
upg1.parallel_pdb_creation_clause.PDB2=2
upg1.parallel_pdb_creation_clause.PDB3=2
upg1.parallel_pdb_creation_clause.PDB4=2
upg1.start_time=01/10/2026 02:30:00

In source_dblink you specify the name of the database link that you previously created. You must assign the right database link to the right PDB. The following number (1800) is the refresh rate in seconds. You can adjust that accordingly.
In this example, you’re using Oracle Managed Files, and FILE_NAME_CONVERT=NONE allows the target CDB to automatically generate new file names.
I recommend renaming the PDB to avoid any confusion. You can do that with target_pdb_name.
Also, limit the number of parallel processes that the target CDB can use to copy the PDB over the network. The file copy happens for all PDBs at the same time, so in this case, the target CDB should have at least 8 CPUs. Also, take the resources of the source CDB into consideration.

Run AutoUpgrade in analyze mode on the source database host. This checks the PDBs for upgrade readiness:
```
java -jar autoupgrade.jar -config upgrade.cfg -mode analyze
```
Run AutoUpgrade in deploy mode on the target database host:
```
java -jar autoupgrade.jar -config upgrade.cfg -mode deploy
```
- The target CDB now starts to copy the PDBs over.
- After that, it periodically refreshes the PDBs with redo from the source database.
- It doesn’t proceed with the actual upgrade.
- Instead, it waits until you reach the time specified by the start_time parameter.
Downtime starts now
Run AutoUpgrade in fixups mode on the source database host. This runs recommended and required pre-upgrade fixups in the PDBs:
```
java -jar autoupgrade.jar -config upgrade.cfg -mode fixups
```
Instruct AutoUpgrade to move on with the upgrade even though the start_time hasn’t been reached yet:
```
upg> proceed -job <job-number>
```
AutoUpgrade now performs a final refresh to bring over the latest changes. Then, it upgrades the PDBs concurrently. It will upgrade CPU_COUNT/2 at the same time. You can tweak that using catctl_options.
Be sure to stop the source PDBs that are running in the 19c CDB.

That’s it! You’ve now moved and upgraded the first batch of PDBs. You can repeat the process with the next batch.

In One Batch

Technically, you can put all PDBs into the same configuration file and move/upgrade them all at the same time.

However, you might as well use Data Guard to build a standby database and then perform an upgrade of the entire CDB.

However, if possible, I would recommend moving/upgrading the PDBs in batches.

Final Words

AutoUpgrade and refreshable clones are a perfect match.

What would you recommend? Leave a comment and let me know what you would suggest.

How To Downgrade a PDB from Oracle Database 23ai

When talking about upgrades, Oracle Database has a great fallback mechanism; a downgrade. Even after going live on Oracle Database 23ai, you can get back to Oracle Database 19c or 21c – with no data loss.

How to Downgrade From Oracle Database 23ai

My PDB, PDB1, has already been upgraded to Oracle Database 23ai. Now, I want to downgrade to Oracle Database 19c. In the downgrade process, I will unplug from CDB23 and into CDB19.

I open the PDB in downgrade mode:
```
alter pluggable database PDB1 close immediate;
alter pluggable database PDB1 open downgrade;
```
- Downgrade mode is a special mode – similar to upgrade mode. It enables exclusive access to the database and disables a lot of features.
I set the environment to CDB23 and start the downgrade process:
```
cd $ORACLE_HOME/bin
./dbdowngrade -c 'PDB1'
```
- The -c command line parameter starts a downgrade of a specific PDB; not the entire CDB.

After the downgrade, I close and unplug from CDB23:

alter pluggable database PDB1 close;
alter pluggable database PDB1 unplug into '/home/oracle/scripts/pdb1.xml';

Now, I connect to CDB19 running on Oracle Database 19c. I plug in and open the PDB in upgrade mode:

create pluggable database PDB1 using '/home/oracle/scripts/pdb1.xml';
alter pluggable database PDB1 open upgrade;

I switch to PDB1 and complete the downgrade by running the catrelod.sql script:
```
alter session set container=PDB1;
@$ORACLE_HOME/rdbms/admin/catrelod.sql
```
Then, I recompile all invalid objects:
```
@$ORACLE_HOME/rdbms/admin/utlrp.sql
```

A restart of the PDB. I open in normal mode:

alter pluggable database PDB1 close;
alter pluggable database PDB1 open;

I gather new dictionary statistics:
```
exec dbms_stats.gather_dictionary_stats;
```
- After a while, when the database is warmed up, I also gather fixed objects statistics.

I verify that all components are VALID or OPTION OFF:

select comp_id, version, status from dba_registry;

I run Datapatch to ensure all SQL patches are properly applied:
```
$ORACLE_HOME/OPatch/datapatch
```

That’s it!

Worth Knowing About Downgrades

The downgrade is a two-step process.
- The first part happens while the database is running in the new Oracle Home. Startup in a special downgrade mode and execute catdwgrd.sql to start the downgrade.
- Next, restart the database in the old Oracle Home and start in upgrade mode. catrelod.sql will re-install any missing objects in the database and finish the downgrade.
Oracle recommends that you install the latest Release Update in both Oracle homes; the one that you downgrade from, and the one to which you downgrade.
You can only downgrade if the compatible hasn’t been changed after the upgrade.
If the timezone file was upgraded, the same timezone file must be present in the old Oracle Home.
Before you start the downgrade, there’s no need to roll off any patches with Datapatch. The downgrade mechanism takes care of that.
The data dictionary in a downgraded database is not identical to the pre-upgraded database. The data dictionary will be different, but compatible. Here are some examples:
- Generally, dropping objects is avoided.
- New tables are most likely not dropped but truncated.
- New indexes are most likely kept.
Although you can downgrade a database from Oracle Database 23ai to 19c, you can’t undo the multitenant migration. To get back to a non-CDB you must use other means like Data Pump, transportable tablespaces, or GoldenGate.

Want to Try?

Hopefully, you never need to downgrade a PDB. But I bet you can resist the urge to try it. Right? RIGHT?

In our hands-on lab, Hitchhiker’s Guide for Upgrading to Oracle Database 23ai, there is a downgrade exercise. Give it a try. The lab is free to use and doesn’t require any installation – it runs completely inside a browser.

Happy downgrading!

Appendix

AutoUpgrade New Features: Custom Oracle Home Name

When you create a new Oracle home using AutoUpgrade, you can now give it a custom name.

What do I mean by a custom name? Let’s examine the Oracle Inventory:

cat /u01/app/oraInventory/ContentsXML/inventory.xml

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2025, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.7.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/product/19" TYPE="O" IDX="1"/>
<HOME NAME="OraDB21Home1" LOC="/u01/app/oracle/product/21" TYPE="O" IDX="2"/>
<HOME NAME="OraDB23Home1" LOC="/u01/app/oracle/product/23" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

The Oracle home name is listed in the NAME attribute on the HOME element.

When you install a new Oracle home, the installer automatically generate a name for you, like OraDb19Home1.

In AutoUpgrade, you can decide to use a custom name instead of an auto-generated one.

How To Specify A Custom Oracle Home Name

I use the latest version of AutoUpgrade

wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

I create the following config file:

global.global_log_dir=/home/oracle/autoupgrade-patching/log
global.keystore=/home/oracle/autoupgrade-patching/keystore
patch1.source_home=/u01/app/oracle/product/19
patch1.target_home=/u01/app/oracle/product/19_28
patch1.home_settings.home_name=DBHOME1928
patch1.sid=FTEX
patch1.folder=/home/oracle/patch-repo
patch1.patch=RU,OCW,DPBP,OJVM,OPATCH

Notice the home_settings.home_name parameter. This is where I specify the custom name for the new Oracle home.

I patch the database using AutoUpgrade:

java -jar autoupgrade.jar -config FTEX.cfg -mode deploy

I check the inventory:

grep -i "19_28" /u01/app/oraInventory/ContentsXML/inventory.xml
<HOME NAME="DBHOME1928" LOC="/u01/app/oracle/product/19_28" TYPE="O" IDX="4"/>

AutoUpgrade created the new Oracle home with the name DBHOME1928.

What Do I Use It For

For a regular Oracle home, it’s used seldom. I’ve seen some customers using certain Oracle home names as part of their corporate standard, but most people don’t care.

However, in a read-only Oracle home, the name is important. It now becomes part of Oracle Base Home which is the location for most of the writable files from the Oracle home.

Do you have a specific use case for the Oracle home name or do you have a corporate standard mandating a certain name? Leave a comment and let me know.

Happy patching!

When A Refreshable Clone Takes Over The Service

Following my advice, a customer migrated a database to multitenant using refreshable clone PDB. The CDB was on the same host as the non-CDB.

When we prepare the migration and create the refreshable clone, the users can no longer connect to the source database.

The users were getting ORA-01109: database not open when connecting to the source database.

But this was before the final refresh and before the migration was supposed to happen.

Why did the refreshable clone PDB interfere with operations in the source database?

The Details

The customer has a non-CDB called SALES, which they wanted to migrate into CDB1. They wanted the PDB to keep the original name, SALES.

Any database registers a default service at the listener. The name of the default service is the same as the name of the database.

In this case, the non-CDB registered the sales service:

$ lsnrctl status

Service "sales" has 1 instance(s).
  Instance "SALES", status READY, has 1 handler(s) for this service...

The customer used AutoUpgrade for the migration. When preparing for the migration, they started in deploy mode, and AutoUpgrade created the refreshable clone.

Bear in mind that these steps are preparations only. The switch to the PDB should happen at a later time.

Look what happens to the listener:

$ lsnrctl status

Service "sales" has 2 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
  Instance "SALES", status READY, has 1 handler(s) for this service...

The CDB also registers a sales service. It does so because of the refreshable clone PDB with the same name.

The users were connecting to the default service, sales. The listener handed off the connections to the CDB, not the non-CDB.

Since the PDB was still a refreshable clone PDB, it was not open, and users received ORA-01109: database not open.

Besides that, the refreshing process didn’t work either. The refresh of the PDB happens over a database link to the source database. Guess what happened?

2025-05-09T07:25:58.854934+00:00
Errors in file /u01/app/oracle/diag/rdbms/cdb19/CDB19/trace/CDB19_ora_61633.trc:
ORA-17627: ORA-01109: database not open
ORA-17629: Cannot connect to the remote database server
ORA-17627 signalled during: ALTER PLUGGABLE DATABASE FTEX REFRESH...

Yes, it couldn’t connect to the source database either. It ended up trying to connect to itself.

Optimal Solution

The real problem is the connections to the default service, sales. The service with the same name as the database.

This service is not meant for general use. You should create your own service and have your application connect through that.

Why is using default services a bad idea?

You can’t customize the default service.
The default service is for administrative use only.
You easily end up with collisions like this one. This can also happen with two PDBs in different CDBs on the same host.
If you rename the database, you also rename the default service and have to update all connection strings.

Why are custom services a good idea?

Custom services allow you to set many attributes. While this might not be important for a single-instance database, it is essential for Data Guard and RAC.
When you clone a database, a custom service doesn’t follow with it. You have to create the services in the clone when and if it is appropriate.

You can create custom services using DBMS_SERVICE or srvctl. You can find more about that in a previous blog post.

Recommendation

Keep using refreshable clone PDB for migrations. It’s a great way to migrate, patch, or upgrade databases.
Always create your own custom service. Don’t use the default service.

Connor McDonald Is Wrong!

In a recent blog post, Connor McDonald was calling out LinkedIn clickbait.

Someone made incorrect claims about Oracle Database on LinkedIn to draw attention. According to sources (himself), Connor tried to resist the urge to jump in but failed. We all know that Resistance is futile.

Connor wrote a good blog post showing that the attention-seeking author of the LinkedIn post is wrong!

Upgrades Are Risky and Complex

One of the false claims was that:

Upgrades are risky and complex. Want to upgrade? Prepare for a long, nerve-wracking process where something will break.

Connor replied:

Upgrades are now editing a 10 line configuration file and running autoupgrade. One line command and you’re good to go.

Here’s the config file Connor used:

global.autoupg_log_dir=/default/current/location
upg1.dbname=employee
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.1.0/dbhome_1
upg1.sid=emp
upg1.log_dir=/scratch/auto
upg1.upgrade_node=node1
upg1.target_version=19.1

But you can make it even simpler.

autoupg_log_dir defaults to $ORACLE_BASE/cfgtoollogs/autoupgrade.
dbname is not used anymore.
start_time=now is the default.
log_dir is not needed when you use global.autoupg_log_dir.
upgrade_node is rarely needed. Only when you have one config file that is used on multiple hosts.
target_version is only needed when the target Oracle home doesn’t exist yet.

You could do the same with this simplified config file:

upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.1.0/dbhome_1
upg1.sid=emp

You don’t like config files? OK – use environment variables instead. Here’s how to upgrade completely without a config file:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export TARGET_ORACLE_HOME=/u01/app/oracle/product/19.1.0/dbhome_1
export ORACLE_SID=emp
java -jar autoupgrade.jar -config_values -mode deploy

Upgrades Are Simple, Fast, and Safe

I might be a little biased, but I think upgrades are:

Simple

Since we introduced AutoUpgrade in 2019, upgrades have become even easier.
Keep it simple or customize it to your exact needs – you decide.
Use the preupgrade analysis and run hundreds of tests on your database. If it passes the test, you’re good to go.
Still worried? Use the dictionary check to ease your mind.

Fast

The parallel upgrade introduced in Oracle Database 12.1 uses more threads to upgrade your database.
You can perform unplug-plug upgrades to minimize downtime.
Or do rolling upgrades to minimize it even further.
Or use Oracle GoldenGate to upgrade with no downtime.

Safe

If something happens, you have Flashback Database to revert the changes in just minutes.
You can use our performance stability prescription to find execution plan changes. Nail them down with SQL Plan Management.
The tools from our Real Application Testing pack are part of our cloud offerings. Spin up a database in OCI and use the tools there – even for your on-prem systems.
There’s a huge community that is there to help you.

Was Connor Wrong?

No, Connor was not wrong. Connor was right!

He just needed a tiny correction. Connor made it look like AutoUpgrade is easy, when it is in fact very easy.

Sorry for the clickbait!

Happy upgrading!

How to Upgrade

That’s It!

Appendix

What If My Database Is A RAC Database?

What If I Use Oracle Restart?

What If My Database Is Encrypted

Multitenant Upgrade

Other Config File Parameters

Weeks Before

Versioning

Platform Certification

Clients

AutoUpgrade

Upgrade Readiness

Dictionary Check

Behavior Changes

Grid Infrastructure

Days Before

Statistics

Install Oracle Home

On the Day of Upgrade

Backup

Scheduler

That’s It

The Ideas

Cold Backup

Don’t Make Any Preparations

Partial Offline Backup

Data Guard

Flashback Database

Downgrade

Data Pump

GoldenGate

What Does Oracle Say?

My Final Thoughts

What Do You Get

What Do We Expect

What We Hope

How

Things to Know

Important Things about Multitenant Migration

And Then …

Other Blog Posts

Non-CDB

CDB

PDB

OCI

How To

In One Batch

Final Words

Further Reading

How to Downgrade From Oracle Database 23ai

Worth Knowing About Downgrades

Want to Try?

Appendix

Further Reading

How To Specify A Custom Oracle Home Name

What Do I Use It For

The Details

Optimal Solution

Other Solutions

Recommendation

Upgrades Are Risky and Complex

Upgrades Are Simple, Fast, and Safe

Simple

Fast

Safe

Was Connor Wrong?