Yesterday I wrote about the installation of Oracle Database 21c on Linux. But today it is time for Upgrading to Oracle Database 21c with AutoUpgrade.
AutoUpgrade
You may have heard it already from us and other colleagues, but we can’t repeat it often enough: There is only one recommended tool to upgrade your databases – and this is AutoUpgrade.
In my 21c home, I’m checking the deployed version of AutoUpgrade at first:
[CDB3] oracle@hol:/u01/app/oracle/product/21/rdbms/admin $ java -jar autoupgrade.jar -version build.hash 57ab246 build.version 21.1.3 build.date 2021/04/21 13:32:13 build.max_target_version 21 build.supported_target_versions 12.2,18,19,21 build.type production
Now please compare with the newest version available on MOS Note: 2485457.1 – AutoUpgrade Tool.
The most recent version while I write this blog post is from July 21, 2021 – which is newer than the above version from April 21, 2021. Hence, I will exchange it. Luckily that is super simple: Download it and overwrite the existing version in the 21c Oracle Home:
$ cp /media/sf_TEMP/autoupgradeJUL21.jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar [CDB3] oracle@hol:/u01/app/oracle/product/21/rdbms/admin $ java -jar autoupgrade.jar -version build.hash 680914c build.version 21.2.210721 build.date 2021/07/21 11:14:54 build.max_target_version 21 build.supported_target_versions 12.2,18,19,21 build.type production
This looks good.
Big Change – you need a CDB
In the past, you simply upgraded a database from one release to another. But now things change. With the CDB-only architecture, you can’t upgrade anymore at first. We always recommended the following approach until Oracle 19c:
- Upgrade and migration of a non-CDB to Multitenant until Oracle 19c
- Upgrade non-CDB to Oracle 19c with AutoUpgrade
- Plugin and convert upgraded non-CDB to precreated CDB with AutoUpgrade
This is the safer way since there are proven fallback options for the first step, the upgrade: Guaranteed Restore Points (the standard in AutoUpgrade) and Downgrade with the downgrade scripts.
But this approach does not work anymore when you move to Oracle 21c since you can’t have a non-CDB anymore in 21c. Now this is the way since Oracle 21c for non-CDBs:
- Upgrade and migration of a non-CDB to Multitenant since Oracle 21c
- Plugin the non-CDB into a precreated CDB with AutoUpgrade
- Upgrade and convert the plugged in non-CDB with AutoUpgrade
Of course, it is obvious that you need a proper fallback. But I will get back to this later in another blog post.
So at first, we need a CDB.
Yes, let me repeat this. You need to create a CDB at first.
And please don’t use a seed database – use a template and create the CDB which fits your needs. Check DBA_REGISTRY in your non-CDBs beforehand and create the CDB with the components you really need.
- Create Multitenant Databases with DBCA – Things to Know
- Use your own templates to create databases in DBCA
- Should you enable _fix_controls with DBMS_OPTIM_BUNDLE
This is the path to enter the “Options” choice in DBCA:
The select “Advanced Configuration”:
And then – and this is very important – select “Custom Database”:
Otherwise you won’t see this screen:
I create my CDB as CDB3 this way:
Again, I think it is important to state that AutoUpgrade does not create this CDB. You need to create it. And once the CDB is up and running, I can kick off my upgrade to Oracle 21c.
Let’s upgrade – analyze is always first!
As in this case I won’t be able to revert to a GRP in case of failure, I’d rather create a copy of my non-CDB instead of lifting it directly into CDB3.
This is the config file I’m using for AutoUpgrade:
global.autoupg_log_dir=/home/oracle/logs
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/21
upg1.sid=DB12
upg1.target_cdb=CDB3
upg1.target_pdb_copy_option=file_name_convert=('/u02/oradata/DB12', '/u02/oradata/CDB3/db12')
upg1.log_dir=/home/oracle/logs
upg1.restoration=no
upg1.catctl_options=-t
At first I’m kicking off an analyze run.
$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode analyze AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases 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] Jobs pending [0] Please check the summary report at: /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.html /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.log
And this looks good.
Let me check the status.html report.
No ERRORs, no precheck WARNINGs – this looks good.
Let’s upgrade with -mode deploy
Since the analyze run went smoothly, the deploy shouldn’t cause trouble.
But in fact it did.
In case your database has ORDIM (Oracle Multimedia) but no SDO (Spatial Data Option), the upgrade will fail because of the LCTR (Locator), which isn’t an independent component until Oracle 19c. Please read more including the workaround in my next blog post:
I will now use the workaround and progress from there.
$ java -jar /u01/app/oracle/product/21/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode deploy AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+--------------+--------+----------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+--------------+--------+----------------+ | 100| DB12|DBUPGRADE|EXECUTING|RUNNING|21/08/24 17:47|17:54:43|10%Upgraded DB12| +----+-------+---------+---------+-------+--------------+--------+----------------+ Total jobs 1 upg> lsj +----+-------+-------------+---------+-------+--------------+--------+------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+-------------+---------+-------+--------------+--------+------------------+ | 100| DB12|NONCDBTOPDBXY|EXECUTING|RUNNING|21/08/24 17:47|18:06:55|noncdb_to_pdb - 0%| +----+-------+-------------+---------+-------+--------------+--------+------------------+ Total jobs 1 upg> lsj +----+-------+-------------+---------+-------+--------------+--------+-------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+-------------+---------+-------+--------------+--------+-------------------+ | 100| DB12|NONCDBTOPDBXY|EXECUTING|RUNNING|21/08/24 17:47|18:08:25|noncdb_to_pdb - 64%| +----+-------+-------------+---------+-------+--------------+--------+-------------------+ Total jobs 1 upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.html /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.log [CDB3] oracle@hol:~/scripts
And finally, here we go.
$ s
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 24 18:15:57 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DB12 READ WRITE NO
Summary
Let me get you a quick summary with the most important topics.
- The preferred release you upgrade to is still Oracle Database 19c
- Oracle Database 21c is an innovation release with some nice features but also a short support time lifetime
- Oracle 21c is CDB-only. If you plan to move to Oracle 21c and your database is a non-CDB, you need to create a CDB now in 21c at first
- The ONLY RECOMMENDED way to upgrade to 21c is AutoUpgrade – even Tim Hall agrees
- If you have ORDIM and no SDO in your source database, then you MUST follow this blog post to avoid a terrible pitfall
So please see this blog post as a nice exercise and step-by-step instruction. But the release you will upgrade to is most likely Oracle Database 19c as this is the Long Term Support release.
Further Links and Information
- Download the most recent AutoUpgrade Tool
- Non-CDB to PDB – The Fallback Challenge
- Create Multitenant Databases with DBCA – Things to Know
- Use your own templates to create databases in DBCA
- Should you enable _fix_controls with DBMS_OPTIM_BUNDLE
- Long Term vs Innovation Releases
- Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO
–Mike
Does “Unplug-Plug Relocate Upgrades With AutoUpgrade” works?
I followed this reference:
https://docs.oracle.com/en/database/oracle/oracle-database/21/upgrd/autoupgrade-configuration-file-examples.html#GUID-BBDA588D-2CCD-4551-BDE2-CA7821A1A54D
My scenario is upgrade/migrate an 19c database (not using CDB/PDB) to a 21c, which is located in another host.
First strange thing to me: create the database link in the destination database. This led me to
question where should I run the autoupgrade.jar. It’s not clear from the docs. Actually I created the db links on both databases as a test.
But ok, I tried from the source database/host, which seems the natural way, and the analyze run smooth.
But the messages under let me confused.
Check failed for oltppro, manual intervention needed for the below checks
[CDB_ONLY_SUPPORT, TDE_PASSWORDS_REQUIRED, TARGET_CDB_AVAILABILITY]
“1. Refer to Database Upgrade Guide documentation for options on how to
handle non-CDB upgrades to Oracle Database Release 21c or to a later
release.
Database is a non-container database.
Starting with Oracle Database Release 21c, non-container databases
(non-CDBs) are not supported for direct upgrades.”
The documentation says it will exactly do this upgrade:
“You can use the Unplug-Plug relocate upgrade as a method to create clones from other PDBs,
or from non-CDB databases”.
“3. Open the target CDB, oltp. Additionally, rerun the previous AutoUpgrade
command as there are multiple checks that depend on the target CDB being
available. Those checks have been temporarily marked as successful until
the target CDB is open.
The target CDB, oltp, is closed or unavailable.
The target CDB, oltp, must be open in order to create a PDB during a
non-CDB-to-PDB or unplug-plug operation.”
I do not understand. I am quite sure it is open.
I would be really glad to see an functional example/lab. And really happy to have some help.
Hi Dyerson,
my first reply was wrong (“this is supported for PDB to PDB only”). In fact, the feature is designed to work from non-CDB to PDB as well.
Could you share the logs with me please, ideally via an SR. Just collect the logs with:
java -jar autoupgrade.jar -zip
and send me the SR number via email: mike.dietrich —-at—- oracle.com
Thanks,
Mike
Hi Mike!
I have submitted the SR and sent the email. Meanwhile I was about to test this scenario:
Migrating Non-CDBs to New Hardware with the Same Operating System and for a New Release
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmsu/index.html
and was surprised that it does not mention the autoupgrade! I do feel comfortable using the old method, but not sure if it still safe.
Thanks Dyrson – I wait for a refreshed version of AutoUPgrade which fixes an issue we have seen in test last week.
Cheers,
Mike
Hi Mike,
There something bothering me about non-cdb to pdb conversion. I asked it in community too: https://community.oracle.com/tech/developers/discussion/4495592/migrating-19c-noncdb-to-pdb
when we create a PDB via “create pluggable database” command, dictionary objects are not actually created, they are mostly have SHARING=METADATA clause. so when we try to generate their script in a PDB via dbms_metadata it raise an error:
select dbms_metadata.get_ddl(‘VIEW’, ‘DBA_OBJECTS’, ‘SYS’) from dual;
but when I convert a non-cdb to pdb (via autoupgrade, unplug/plug or over dblink methods) this pdb has its own metadata objects and I can get result for dbms_metadata.get_ddl(‘VIEW’, ‘DBA_OBJECTS’, ‘SYS’) query in pdb.
lately I am working about context data of flashback data archive (sys.SYS_FBA_CONTEXT_AUD) and this subject come to my mind again because in cdb$root:
select * from cdb_tables where table_name =’SYS_FBA_CONTEXT_AUD’;
query returns 2 rows for cdb$root and pdb (converted from non-cdb). and again if I create a pdb with create pluggable command, this query returns one row only.
as if non-cdb metadata is not converted entirely. did this cause any problems, what do you think?
thanks.
Hi Mustafa,
you need to open an SR for this topic. I can’t solve this via the blog. Especially not since our team does not own Multitenant.
But I share your thoughts and suspicions since I have seen similar strange things before. Make sure you use the newest RU – and if that still happens open an SR.
Cheers
Mike
thanks for answering Mike, I will do my tests again and open an SR.
Hi all,
What is the best way to migrate Oracle Database 19c on AIX to Oracle Database 21c REHL ?
Hi Mexman,
at first, move to 19c, not to 21c since 21c will run out of support shortly (in 1 year).
Second, see our Virtual Classroom Seminars: https://mikedietrichde.com/videos/ about “Migration” and “Data Pump”.
Basically, you have two options, and it depends a lot on Downtime, Size of DB, Complexity etc:
1. Data Pump (usually the easiest path)
2. Full Transportable Export / Import (a combination of xTTS and Data Pump)
In the latter case, you need to convert your TS files.
If you need less downtime, then using incremental backups (see our Migration Seminars 5 and 12) is an option for xTTS / xFTEX.
If you need even less downtime, then you need to add Oracle GoldenGate on top.
Cheers,
Mike