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.
You can avoid this step by redirecting the location of your network files using the TNS_ADMIN environment variable.
If you have a completely default installation with no listener configuration, you don’t have a listener.ora file and can skip this step.
In an advanced configuration you might have references to the listener Oracle home. Those you must update. But don’t confuse the listener Oracle home, with the Oracle homes of the databases with static registration. Those you update, when you patch the matching database.
If you use Oracle Grid Infrastructure or Oracle Restart, they manage the listener. You automatically restart, and thus patch, the listener when you patch those components.
If you use the dbstart script to start the listener, update it with the new Oracle home.
On Windows, you must delete the matching Windows service when you stop the listener. The lsnrctl start command should create a new listener. Be sure to set it to start automatically.
The Outage
When you restart the listener, there’s a short period where there’s no listener. It takes a second or so for the new listener to start. In that period, any new connection attempt fails:
ORA-12541: TNS:no listener
This affects new connections only. Existing connections continue to work unaffected.
As soon as the listener starts, it’ll read listener.ora and go through the static listener registrations defined by the SID_LIST. Connections to those databases are ready immediately.
For optimal flexibility, however, it’s better to use dynamic listener registration, where the database instance automatically registers with the listener. When you restart the listener, it doesn’t know of all the dynamic registrations and will have to wait until the database registers (up to 60 seconds).
It’s a good idea to run through all of your databases after the listener restart and force a listener registration with the above command.
Until the dynamic registration completes, any new connection to the database will fail:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
You can hide the short outage from the client by using RETRY_COUNT and RETRY_DELAY in your connect strings. Allow the client to retry for a short period before throwing the above errors.
You can avoid the outage completely by using multiple listeners and patching them separately. Your connect strings should have an ADDRESS_LIST containing both listeners.
Do I Need to Patch the Listener and Database At the Same Time?
No, that’s up to you. If you want just one interruption, then do it all at once. Otherwise, you can do it in separate maintenance windows.
What About AutoUpgrade?
At the moment, AutoUpgrade doesn’t patch the listener. But it’s on the backlog.
When you use the patching functionality in the cloud tooling, it will patch an entire Oracle home, including all databases, using in-place patching. This is a convenient option, but it extends the maintenance window on your database.
Let’s find out how to do it using out-of-place patching!
Benefits of Out-Of-Place Patching
You can patch the database with less downtime. You just shut down the database in the old Oracle home and immediately restart it in the new Oracle home.
With in-place patching, you need more downtime. While the database is down, you need time to roll off patches and apply the new ones. Depending on the number of patches you have, this can take 5, 10, or 20 minutes.
You prepare the new Oracle home in advance.
With in-place patching, all changes to the Oracle home occur during the maintenance window when the database is down.
There are fewer tasks in your downtime window, so less risk and less stress.
You can use a fresh, brand-new Oracle home to avoid issues with rolling off patches. Plus, a brand-new Oracle home means you don’t carry any old luggage in your Oracle home.
With in-place patching, your Oracle home will grow over time. You’ll see that disk space keeps increasing.
In the unlikely event that you need to roll back or fall back, that’s a lot easier. You just restart the database in the old Oracle home.
With in-place patching, you have to reverse the entire process. So, all that was cumbersome when you patched will be cumbersome when you roll back.
AutoUpgrade
In the workshop, I showed some cool demos of using AutoUpgrade for out-of-place patching. You can’t do that on Exadata Cloud@Customer. You must use the cloud tooling when you patch an entire CDB.
Alternatively, you can patch an individual PDB using AutoUpgrade and refreshable clone PDBs. I’ll have to write a blog post about that.
How to Patch Out-Of-Place on Exadata Cloud@Customer
Create a software image at the desired Release Update with any additional one-off patches. You can skip this if you want to use the Oracle-provided images.
Create a database home using the software image you just created, or use an Oracle-provided one.
If you want to script the process, you can also use the REST APIs or dbaascli.
This also works for Exadata Database Service.
There’s no way to use out-of-place patching on Grid Infrastructure.
Happy patching!
Appendix
Other Methods
You are free to use Exadata Fleet Update for your Exadata Cloud@Customer system. It can greatly reduce your workload and automate your patching operations. Check out these resources:
When you upgrade a PDB to Oracle Database 23ai, there is a new method for performing the upgrade. It’s called Replay Upgrade.
I would call it a convenience feature. You simply plug in to a higher release CDB and open the PDB. The CDB detects the lower-release PDB and performs the upgrade. You don’t have to invoke AutoUpgrade.
Here’s how to do it.
A Few Words on Replay Upgrade
In Oracle Database 23ai, you can upgrade the data dictionary in two ways:
Parallel Upgrade – Has been around for quite a few releases. It’s what you’ve used before and can still use.
Replay Upgrade – The new thing that enables you to upgrade the data dictionary by simplying plugging in a lower-release PDB and allowing the CDB to perform the upgrade – without using AutoUpgrade.
I suggest you watch this video about the fundamental differences between the two methods.
AutoUpgrade uses Parallel Upgrade. You can force AutoUpgrade to use Replay Upgrade in your config file:
upg1.replay=yes
How To Upgrade Using Replay Upgrade
You must perform the pre-upgrade tasks while the PDB is in the lower-release CDB.
One of such tasks is to analyze the PDB for upgrade readiness:
java -jar autoupgrade.jar ... -mode analyze
If needed, run the pre-upgrade fixups:
java -jar autoupgrade.jar ... -mode fixups
Plug in a lower-release PDB into a higher-release CDB. It doesn’t matter whether you plugged in from a manifest file, using refreshable clone PDBs or any other method.
Open the PDB:
alter pluggable database PDB1 open;
When you open the PDB in normal mode, Replay Upgrade starts.
The open command doesn’t complete until the upgrade completes. The command is not hanging; it’s simply upgrading in the background.
If you open the PDB in upgrade mode, Replay Upgrade does not start.
During the open command, you can see in the alert log that the CDB upgrades the PDB:
2025-03-31T14:02:37.955470+00:00
ORANGE(6):Starting Upgrade on PDB Open
When the open command completes, the PDB will be upgraded. But it will open in restricted mode until you run Datapatch. From alert.log:
ORANGE(6) Error Violation: SQL Patch, Cause: '23.5.0.24.07 Release_Update2407102158' is installed in the CDB but no release updates are installed in the PDB, Action: Call datapatch to install in the PDB or the CDB
2025-03-31T14:11:03.803899+00:00
ORANGE(6):Opening pdb with no Resource Manager plan active
Violations: Type: 1, Count: 1
Completed: Pluggable database ORANGE opened read write
Completed: alter pluggable database orange open
Run Datapatch:
$ORACLE_HOME/OPatch/datapatch -pdbs PDB1
Restart the PDB to remove restricted mode:
alter pluggable database PDB1 close immediate;
alter pluggable database PDB1 open;
You can still perform a Replay Upgrade if you want. I’ve created instructions in the appendix that you can use. The lab takes 15 minutes to complete.
My Database Is A Non-CDB
Replay Upgrade performs an upgrade-on-open. Interestingly, it can also perform a convert-on-open. The latter will run the same commands you’ll find in noncdb_to_pdb.sql, which you normally run to convert a non-CDB to a PDB.
So, you can simply plug a 19c non-CDB into a 23ai CDB. When you open the PDB, the CDB upgrades and converts to a PDB.
My Recommendation
I recommend using AutoUpgrade. It ensures that you run all the tasks and automates them completely, giving you the safest upgrade.
Replay Upgrade does look a lot easier at first glance, but you still need to remember all the pre-upgrade and post-upgrade tasks. When there’s something you must run manually, there’s always the risk that you forget one or two of the tasks.
For me, Replay Upgrade is a convenience feature you can use in a lab or demo environment or if you think it’s easier to incorporate in your automation. But even with automation, you can still use AutoUpgrade with the -noconsole command line option.
But the choice is yours.
Happy upgrading!
Appendix
Replay Upgrade Queries and Commands
Here’s how you can tell whether Replay Upgrade (Upgrade on Open and Convert On Open) is enabled:
select property_name, property_value
from database_properties
where property_name like '%OPEN%';
You can set the property in the root container and in the PDB.
Here’s how to disable Replay Update:
alter database upgrade sync off;
Hands-On Lab
Here are the instructions for trying a Replay Upgrade in our Hands-On Lab.
The below steps perform a Replay Upgrade of the ORANGE PDB from CDB19 to CDB23.
. cdb19
sqlplus / as sysdba<<EOF
alter pluggable database ORANGE close;
alter pluggable database ORANGE unplug into '/home/oracle/orange.xml';
drop pluggable database ORANGE keep datafiles;
EOF
Plug into the 23ai CDB and open ORANGE:
. cdb23
env | grep ORA
sqlplus / as sysdba<<EOF
set timing on
create pluggable database ORANGE using '/home/oracle/orange.xml' nocopy;
alter pluggable database orange open;
EOF
The open command upgrades the PDB. The command runs for several minutes.
In the end, the command completes but prints Warning: PDB altered with errors.
Run Datapatch on the ORANGE PDB:
$ORACLE_HOME/OPatch/datapatch -pdbs ORANGE
Restart ORANGE:
sqlplus / as sysdba<<EOF
alter pluggable database orange close;
alter pluggable database orange open;
select open_mode, restricted from v\$pdbs where name='ORANGE';
EOF
The PDB now opens normally (READ WRITE) and unrestricted.
sqlplus / as sysdba<<EOF
select open_mode, restricted from v\$pdbs where name='ORANGE';
alter session set container=ORANGE;
select version_full from v\$instance;
EOF
Running Oracle Database on Microsoft Windows is slightly different from running it on other platforms. So, of course, patching Oracle Database is also slightly different.
The Oracle Database runs as a Windows service. AutoUpgrade must re-create the service when you perform out-of-place patching so the service starts oracle.exe from the new Oracle home.
To recreate the service, you must specify the credentials of the user who runs the service. Windows allows you to store the credentials in a special file; AutoUpgrade can use that when it recreates the service.
For security purposes, AutoUpgrades deletes the credential file when it is no longer needed. For automation, however, that’s impractical because you would need to recreate the credential file every time you patch or upgrade.
AutoUpgrade now allows you to keep the file and reuse it. To do so, use the config file parameter delete_credential_file.
Staying even more up-to-date with Monthly Recommended Patches (MRP) is now easier when patching using AutoUpgrade.
MRP contains fixes from 555.1 conveniently bundled together in a single patch.
> To provide customers more frequent access to recommended and well-tested collections of patches, Oracle is pleased to introduce Monthly Recommended Patches (MRPs)
Run AutoUpgrade in analyze, download or deploy mode, and AutoUpgrade gets:
* RECOMMENDED – latest available Release Update, OPatch, Data Pump Bundle Patch and OJVM Bundle Patch
* MRP – latest available MRP matching the Release Update
Optionally, you can add one-off fixes that are not part of the Release Update or MRP:
patch1.patch=RECOMMENDED,MRP,34672698
You can add more one-off fixes using a comma-separated list
The other day, I found myself praising refreshable clone PDBs to a customer (which I often do because it’s a killer feature). They liked the feature too but asked:
> We are concerned about the impact on the source database. When AutoUpgrade connects to the source database and clones the database, can we offload the work to a standby database?
Refreshable clone PDBs can eat up your resources if you don’t constrain the target CDB. So, let’s see what we can do.
Mounted Standby Database
This won’t work, because you must be able to connect to the database via a regular database link. Further, AutoUpgrade and the cloning process must be able to execute queries in the source database, which is not possible on a mounted database.
Open Standby Database / Active Data Guard
What if you stop redo apply and open the standby database? Or if you have Active Data Guard?
In this case, the database would be open in read-only mode, and those queries would work. However, the refreshable clone PDB feature was developed to work in and require a read-write database, so this won’t work either – Not even if you enable automatic redirection of DML operations (ADG_REDIRECT_DML).
Even if this case would work, we wouldn’t recommend it. Because, we recommend that you run analyze and fixups mode on the source database, which wouldn’t be possible on a read-only database.
You could run analyze and fixups on the primary database. But is that really an option? If you’re worried about affecting your primary and want to offload to the standby, would running those commands on the primary be an option?
Snapshot Standby Database
What about a snapshot standby? That’s a read-write database. Let’s give it a try.
Convert the source standby to a snapshot standby:
DGMGRL> convert database '...' to snapshot standby;
The standby must remain a snapshot standby for the entire duration of the job. If you need to switch over or fail over to the standby, you must restart the entire operation.
Ensure the PDB is open on the source standby.
alter pluggable database ... open;
Otherwise, you will run into ORA-03150 when querying the source database over the database link.
In the source standby, create the user used by the database link and grant appropriate permissions:
create user dblinkuser identified by ...;
grant create session, create pluggable database, select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;
In the target CDB, create a database link that points to the PDB in source standby:
create database link clonepdb
connect to dblinkuser identified by ...
using '';
Convert the source standby back to a physical standby:
DGMGRL> convert database '...' to physical standby;
Is It Safe?
Using a standby database for anything else than your DR strategy, is sometimes perceived as risky. But it is not, as I explain in this blog post (section What Happens If I Need to Switch Over or Fail Over?).
Oracle just released new Release Updates, so it’s time to patch your Oracle Database again. As much as I love patching, the part about finding and downloading patches is not that much fun.
Luckily, I can use AutoUpgrade to download the Release Update and other patches with just a few commands.
How to Download Release Updates
I can do the following on any computer. I just need Java 8 or 11 to run AutoUpgrade.
I want to download 19.27 Release Update and supporting patches for Linux and I specify that using the platform parameter.
I’ve already used AutoUpgrade to download patches, so my My Oracle Support credentials are already stored in the AutoUpgrade keystore. If you’ve never used AutoUpgrade to download patches, follow the instructions below (see Creating an AutoUpgrade Keystore).
I download the patches by starting AutoUpgrade in download mode:
OPatch 12.2.0.1.46 for DB 19.0.0.0.0 (Apr 2025) – p6880880_190000_Linux-x86-64.zip
Is it really that easy? Yes, it is…
Happy patching!
What About the Other Platforms
At the time of writing, Oracle has only released the Linux patches. Once the other platforms are available, you can easily download them as well. Check the Patch Availability Document for details. Add the following to your config file:
AutoUpgrade prompts for a password to protect its keystore. AutoUpgrade uses the password to encrypt the keystore, which stores my My Oracle Support credentials.
This is not the database keystore password that you use for TDE Tablespace Encryption.
Processing config file ...
Starting AutoUpgrade Patching Password Loader - Type help for available options
Creating new AutoUpgrade Patching keystore - Password required
Enter password:
Enter password again:
I specify my MOS username. AutoUpgrade then prompts me for the MOS password:
MOS> add -user <mos-username-or-email>
Enter your secret/Password:
Re-enter your secret/Password:
I save the changes, and I choose to create an auto-login keystore so I don’t have to enter the AutoUpgrade keystore password every time AutoUpgrade starts:
MOS> save
Convert the AutoUpgrade Patching keystore to auto-login [YES|NO] ? YES
Suppose you need to connect through a proxy server to reach My Oracle Support and get the patches. AutoUpgrade now supports several ways to use a proxy.
Set https_proxy in lowercase. At least on Linux, environment variables are case-sensitive and although some programs can handle any case, it’s better to use the official case.
AutoUpgrade automatically picks up and uses the proxy.
Other Proxy Types
You can set https_proxy to a value matching this specification:
This error message indicates that you forgot to set the https_proxy environment variable or set it correctly:
There were conditions found preventing AutoUpgrade Patching from successfully running
*Downloading Oracle Patch files
Patch query failed
*login-ext.identity.oraclecloud.com*
This error message indicates that your proxy doesn’t allow connections to all the required servers:
There were conditions found preventing AutoUpgrade Patching from successfully running
*Downloading Oracle Patch files
Patch query failed
*Unable to tunnel through proxy. Proxy returns "HTTP/1.1 403 Forbidden"*
Often, you need to make changes to the database or application to fit into an Autonomous Database. In part 2, we show you how to deal with the findings from the Cloud Premigration Advisor Tool (CPAT).
Part 3 is all about demos—demos on how to migrate to Autonomous Database using a number of different techniques.
Finally, how is the life of a DBA after migrating to an Autonomous Database? Is there anything left to do? We think there is, and we’ll show you the cool life of a DBA on an Autonomous Database.
Happy Migrating
We’re looking for customers to collaborate with. If you’re already working on a migration project, contact us today. You might be our next reference customer.
Databases Are Fun 