I’ve already downloaded the patches to download_folder.
I want to create a new Oracle home in the target_home location.
AutoUpgrade copies the settings from the source_home.
After installing the new Oracle home, AutoUpgrade creates a gold image in download_folder. Instead of specifying the name of the gold image, I can also set create_gold_image=yes and let AutoUpgrade generate a name.
I set patch=recommended to get the recommended patches: latest Release Update, OPatch, MRP, OJVM, and Data Pump bundle patches.
I start AutoUpgrade to install the new Oracle home:
AutoUpgrade stores your MOS credentials and other secrets in its own keystore.
It’s convenient to create an auto-login keystore. Otherwise, AutoUpgrade prompts for the keystore password every time it needs a secret. But the auto-login keystore only works on the computer where it was created.
Unless you create a shared keystore. Let’s see how.
Shared Keystore
If you have an existing AutoUpgrade auto-login keystore, remove it:
Let me show you how to upgrade an encrypted non-CDB to Oracle AI Database 26ai. Since this release only supports the multitenant architecture, you must also convert it to a PDB.
To preserve the source database for rollback and to minimize downtime, I’ll use AutoUpgrade and refreshable clone PDBs.
How to Upgrade and Convert
AutoUpgrade and refreshable clone PDBs give you the option of moving the database to a different host. If you want to stay on the same host, just imagine source and target hosts are the same.
Start by familiarizing yourself with the restrictions on refreshable clone PDB for non-CDBs.
create user dblinkuser identified by ... ;
grant create session,
create pluggable database,
select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;
I need the user so I can connect from the CDB via a database link.
I’ll drop it after the upgrade.
In my target CDB, as SYS, I create a database link connecting to my source non-CDB:
create database link clonepdb
connect to dblinkuser identified by ...
using 'source-db-alias';
2. Analyze
I must run the pre-upgrade analysis on the source system.
I create an AutoUpgrade config file for the analysis. I call it upgrade26-analyze.cfg:
I set source_home to /tmp because it doesn’t exist on the target system. I set source_base to the Oracle base of my target system.
sid is the database that I want to upgrade and convert.
target_cdb is the SID of the database where the non-CDB ends up.
source_dblink.<sid> is the name of the database link and the refresh rate in seconds.
target_pdb_name.<sid> allows me to rename the database. I strongly recommend that you rename the database if your CDB is on the same system. Otherwise, you’ll have a service name collision to deal with.
start_time is when AutoUpgrade performs the final refresh and starts the upgrade/conversion. I set it far out in the future, so I can better control the final refresh using the proceed command. Leave a comment if you know what happens at the specified time. :-)
parallel_pdb_creation_clause.<sid> limits the number of parallel processes used by the CDB to make the initial copy. Set it at a reasonable level that doesn’t overload the source database.
target_pdb_copy_option.<sid> allows me to specify the location of the data files. I use OMF and let the database decide where to put the files.
drop_dblink instructs AutoUpgrade to drop the database link when it’s no longer needed.
I start the AutoUpgrade password console to load the target CDB keystore password:
AutoUpgrade informs me that it can’t run certain preupgrade fixups because the target Oracle home doesn’t exist on the source system. This is expected and ignorable.
Next, I switch to the target system. From the AutoUpgrade console, I instruct AutoUpgrade to proceed with the upgrade:
upg> proceed -job 100
AutoUpgrade performs a final refresh.
Then, it disconnects the PDB from the source.
Then, starts the upgrade and conversion.
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 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
Please check the summary report at:
/home/oracle/autoupgrade/upgrade26/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/autoupgrade/upgrade26/cfgtoollogs/upgrade/auto/status/status.log
This includes the post-upgrade checks and fixups.
I review the Autoupgrade Summary Report. The path is printed to the console:
vi /home/oracle/autoupgrade/upgrade26/cfgtoollogs/upgrade/auto/status/status.log
If you’ve configured Data Guard on your target CDB, you must restore the PDB on all standbys. Check the appendix for details.
That’s It!
With AutoUpgrade, you can easily upgrade your encrypted non-CDB and convert it to a PDB. For maximum protection, AutoUpgrade lets me preserve the source non-CDB in case I need to roll back.
When your maintenance window starts, you must kick users off.
In a previous blog post, I gave an idea on how you can do that. Whatever you do, don’t restart the source database. It’ll break the refreshable clone PDB.
Does It Work Cross-Platform
Refreshable clone PDB does not work for cross-endian migrations (like AIX to Linux), but cross-platform should work fine (like Windows to Linux).
What If My Database Is A RAC Database?
Ensure that all instances can resolve the database link connection identifier and that the database link works from all instances. The CREATE PLUGGABLE DATABASE statement scales out on all instances for the initial cloning.
In your config file, when specifying the sid parameter you must use the SID on the node where AutoUpgrade runs. If my database is called DB19 and it runs on host1 which is instance 1 in my cluster, I’d specify:
upg1.sid=DB191
Recreate services in the target CDB using srvctl. If you have many services, consider export/import of the services.
Other Config File Parameters
The config file shown above is a basic one. Let me address some of the additional parameters you can use.
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.
before_action / after_action: Extend AutoUpgrade with your own functionality by using scripts before or after the job.
Compatible
During plug-in, the PDB automatically inherits the compatible setting of the target CDB. You don’t have to raise the compatible setting manually.
Typically, the target CDB has a higher compatible and the PDB raises it on plug-in. This means you don’t have the option of downgrading.
If you want to preserve the option of downgrading, be sure to set the compatible parameter in the target CDB to the same value as the source CDB.
A customer had to move hundreds of PDBs using AutoUpgrade and refreshable clone PDBs. As a cool customer, they wanted to automate the entire process.
The PDBs are encrypted, so AutoUpgrade needs the source and target TDE keystore passwords. AutoUpgrade stores these passwords in its own keystore until they are needed.
You can manually load the passwords into the AutoUpgrade keystore using the load password console (-load_password).
But loading passwords manually doesn’t fit very well with automation.
The Solution
For security reasons, there is no native way in AutoUpgrade to load these passwords besides typing them manually.
Loading passwords via response files or command line parameters exposes the sensitive information, e.g., in your command history.
But if you accept the risk, you can load passwords using the expect command. You’ll need to place the passwords in clear-text in a file for a short period. When the loading completes, you can remove the file and the passwords are now safely stored in the AutoUpgrade keystore.
Loading Passwords Using Expect
Here’s my AutoUpgrade config file called sales.cfg:
Since the file will contain my passwords, I place it in /dev/shm which is a tmpfs or RAM-based file system. I don’t want the file on persistent storage.
Replace MyS3cr3tPassw0rd# with the password you want for the AutoUpgrade keystore.
Replace ash@weyland-yutani.com with your MOS username.
Replace MyS3cr3tMOSPassw0rd# with your MOS password.
That’s It
With expect you can automate the loading of passwords into the AutoUpgrade keystore.
I consider it safer to load passwords manually, but to fully automate the process you must cut a corner.
I suggest using a unique keystore for each of your AutoUpgrade invocations. This makes it easier to automate. AutoUpgrade can also create a shared keystore that you can create once and then distribute to other servers.
But it kept failing during the initial copy of the PDB:
upg> Copying remote database 'PDB2' as 'NEWPDB2' for job 101
-------------------------------------------------
Errors in database [CDB19]
Stage [CLONEPDB]
Operation [STOPPED]
Status [ERROR]
Info [
Error: UPG-4016
[Unexpected exception error]
Cause: There was an error during the database clone operation
For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/CDB19/101/autoupgrade_20400101_user.log]
-------------------------------------------------
Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/CDB19/101/autoupgrade_20400101_user.log]
-------------------------------------------------
Luckily, there’s extensive logging in AutoUpgrade, so I went into the directory holding the logs from the CLONEPDB stage and found the following:
create pluggable database "NEWPDB2" FROM PDB2@CLONEPDB file_name_convert=none tempfile reuse keystore identified by "*" REFRESH MODE MANUAL
*
ERROR at line 1:
ORA-17628: Oracle error 46659 returned by remote Oracle server
ORA-46659: master encryption keys for the given PDB not found
Help: https://docs.oracle.com/error-help/db/ora-17628/
Let’s dig into the error.
A Possible Solution
A search on MOS revealed a note with a possible solution:
Manually export/import the encryption keys, or
Use the undocumentedINCLUDING SHARED KEYS clause on the CREATE PLUGGABLE DATABASE statement.
I didn’t like these solutions because:
The target CDB should be able to import the keys automatically over the database link.
It worked fine on other encrypted databases without the workaround.
So what was the problem?
The Root Cause
In the source CDB, I could see from V$ENCRYPTION_KEYS that the source PDB, PDB2, didn’t have any encryption keys.
There should be an encryption key activated by PDB2. But the query returned no rows.
SELECT * FROM v$encryption_keys WHERE activating_pdbname='PDB2';
So which encryption key did PDB2 use?
It turns out that PDB2 was recently created by cloning another local PDB, PDB1.
After cloning an encrypted PDB, the new PDB keeps using the same encryption keys as the source PDB. This is called a shared key.
So, PDB2 was using the same encryption key as PDB1.
For security reasons, if you try to clone a PDB using a shared key, the database errors out.
The Solution
I advise you to rekey your database after cloning. This ensures that the clone gets its own encryption keys and, thus, strengthens security.
After connecting to PDB2, I performed a rekey using the set key command:
alter session set container=PDB2;
administer key management set key
force keystore identified by "<keystore_pwd>"
with backup;
Then, I could clone PDB2 using refreshable clone PDB and upgrade it without problems.
After cloning an encrypted database, you should perform a rekey, so the clone has its own encryption keys and does not share encryption keys with another database.
The database can operate with a shared key, but it might cause problems later.
Use out-of-place patching. This allows you to install the new Oracle home in advance. It reduces downtime, is less risky and makes rollbacks easier.
Use a brand-new home each time.
If you insist on in-place patching or clone Oracle homes be sure to clean up.
Create and use gold images. Once you’ve created your own gold image, you can deploy it to other hosts faster than installing and patching a new Oracle home.
Standardize on as few gold images as possible. Ideally, you have only one gold image for a specific Release Update.
Move files out of the Oracle home. You can find many configuration files and such inside the Oracle home. You must copy them to the new Oracle home when you use out-of-place patching – unless you use AutoUpgrade that does it for you.
Many of these files can be placed outside the Oracle home.
Use Data Guard standby-first patching. It minimizes the interruption to a Data Guard switchover and provides the possibility of testing the patch application.
You can run Datapatch while users are connected. Knowing this you can minimize the outage on single instance databases by allowing users to connect as soon as you’ve restarted the database in the new Oracle home.
Limit the space used by Datapatch in the SYSTEM tablespace.
If you wonder what Datapatch spends time on, check the Datapatch logs in $ORACLE_BASE/cfgtoollogs/sqlpatch.
Ensure Datapatch patches the most important PDBs first.
Datapatch patches many PDBs at the same time. This depends on the database CPU_COUNT.
By default, Datapatch takes the PDBs in order by CON_ID.
But you can change the order using ALTER PLUGGABLE DATABASE <pdb_name> PRIORITY 1. The lower the priority, the sooner the PDB is processed.
Patch multiple databases at the same time.
Datapatch works on one database only. But you can start multiple instances of Datapatch to patch multiple databases simultaneously provided you have the CPU resources.
After a short delay, the latest Release Updates are out for Linux.
Are you wondering about the remaining platforms? Or do you want to refresh your knowledge of database patching? We recently aired two webinars that you’ll find interesting.
Webinar
Database Patching for DBAs – Patch smarter, not harder
If you’re still downloading patches from My Oracle Support, you must watch these webinars. Save yourself a lot of time and grab all the patches you need in one command.
If you have RAC databases and the applications are a little slow to drain or you just want more control, check out the DBA-controlled draining we recently introduced.
My Highlights
Here are a few of the topics that I find especially useful:
I use the platform parameter to instruct AutoUpgrade to find OPatch for Linux.
I want OPatch for an 19c database and I specify that with target_version.
I just want OPatch, so I set patch=OPATCH.
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:
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
Databases Are Fun 