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
Hi Mike,
I am trying to analyze with autoupgrade to precheck for non-cdb database 19.11 to 21.9 upgrade.
cat config_for_noncdbtopdb.cfg:
================================
#
# Global logging directory pertains to all jobs
#
global.autoupg_log_dir=/home/oracle/test/CDB/logs # Top level logging directory (Required)
#
# Database 1
#
#upg1.dbname=nopsppe
upg1.source_home=/u01/app/oracle/product/19.11.0.0/dbhome_1 # Source Home (Required)
upg1.target_home=/u01/app/oracle/product/21.9.0.0/dbhome_1 # Target home (Required)
upg1.sid=nopsppe1 # Oracle Sid (Required)
upg1.target_cdb=nopsptcon
upg1.start_time=now # Start time of the operation (Required)
upg1.log_dir=/home/oracle/test/CDB/logs # Local logging directory (Required)
upg1.upgrade_node=localhost # Upgrade node that operation will run on (Required)
upg1.target_version=21 # Oracle Home Target version number (Required)
upg1.run_utlrp=yes # yes(default) to run utlrp as part of upgrade, no to skip it (Optional)
upg1.restoration=no
upg1.timezone_upg=yes # yes(default) to upgrade timezone if needed, no to skip it (Optional)
[oracle@c1t-pt-isxdc-u1 CDB]$
[oracle@c1t-pt-isxdc-u1 CDB]$ /u01/app/oracle/product/21.9.0.0/dbhome_1/jdk/bin/java -jar /home/oracle/test/CDB/autoupgrade.jar -config /home/oracle/test/CDB/config_for_noncdbtopdb.cfg -mode analyze
Precheck keeps failing complaining about:
DEPLOY_JOB_VALIDATIONS
TARGET_CDB_AVAILABILITY – ChecksController.evaluateBlockerChecks
my CDB is in READ-WRITE mode,, I am not able tp proceed further how to fix this.
[oracle@c1t-pt-isxdc-u1 100]$ cat autoupgrade_20231030_user.log
2023-10-30 22:27:39.325 INFO
build.hash:165be072
build.version:23.4.230921
build.date:2023/09/21 14:43:05 -0400
build.max_target_version:21
build.supported_target_versions:12.2,18,19,21
build.type:production
build.hash_date:2023/09/21 13:05:52 -0400
build.label:(HEAD, tag: v23.4, origin/stable_devel, stable_devel)
2023-10-30 22:28:00.227 INFO Analyzing nopsppe1, 125 checks will run using 44 threads
2023-10-30 22:28:02.633 ERROR The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
nopsppe1 DEPLOY_JOB_VALIDATIONS
nopsppe1 TARGET_CDB_AVAILABILITY
[oracle@c1t-pt-isxdc-u1 100]$
[oracle@c1t-pt-isxdc-u1 100]$
[oracle@c1t-pt-isxdc-u1 100]$ cat autoupgrade_err.log
2023-10-30 22:28:01.136 WARNING Unable to confirm existence of file /u01/app/oracle/650cbe on node c1t-pt-isxdc-u2.intcx.net – ClusterConnectivity.isValidSetup
2023-10-30 22:28:02.633 ERROR The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
nopsppe1 DEPLOY_JOB_VALIDATIONS
nopsppe1 TARGET_CDB_AVAILABILITY – ChecksController.evaluateBlockerChecks
Precheck log shows the following:
=================================
REQUIRED ACTIONS
================
1. Fix the specified validations before running AutoUpgrade in DEPLOY mode.
The following job based validations will fail if AutoUpgrade is run in
DEPLOY mode.
*Target CDB availability
Unable to connect to database nopsptcon, which was defined as the
target_cdb for database nopsppe1
*All the nodes of the cluster must be configured with passwordless
connectivity
Not able to establish passwordless connectivity the following node
[c1t-pt-isxdc-u2.intcx.net]. It may not be configured properly
Before running AutoUpgrade in DEPLOY mode, all job based validations must
succeed.
2. Open the target CDB, nopsptcon. 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, nopsptcon, is closed or unavailable.
The target CDB, nopsptcon, must be open in order to create a PDB during a
non-CDB-to-PDB or unplug-plug operation.
Hi John,
is the SID of your target CDB really in lowercase characters (unusual choice for me but possible – just wanted to check). in the environment.
Then try to logon to your CDB from the OS prompt you try to start autoupgrade from by just setting the ORACLE_SID=
Does this work?
Then try to reduce your config file – I see that you have used the sample config option:
global.autoupg_log_dir=/home/oracle/test/CDB/logs
upg1.source_home=/u01/app/oracle/product/19.11.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/21.9.0.0/dbhome_1
upg1.sid=nopsppe1
upg1.target_cdb=nopsptcon
upg1.log_dir=/home/oracle/test/CDB/logs
upg1.restoration=no
These are enough, the others were just redundant options in your case.
If that doesn’t solve the issue, please open an SR and upload your logs:
java -jar autoupgrade.jar -config -zip
This will collect the logs and the alert.log, too.
Cheers,
Mike
Hi Mike,
I was able to resolve the issue.
Issue was, upg1.target_cdb was given db_unique_name instead of CDB SID. That fixed the issue.
DEPLOY_JOB_VALIDATIONS => fixed after SSH keys were fixed for node-2 connectivity.
After fixing this, deploy mode worked and was able to convert non-CDB to PDB to 21c.
One question, Just wanted to confirm, autoupgrade tool should be run from Source ORACLE_HOME environment or from target CDB ORACLE_HOME environment ?
Thanks,
John.
Thanks John!
Cheers,
Mike