This note describes the steps used to upgrade an Oracle 19c database to Oracle 21c as well as convert the 19c Non-CDB database to a Pluggable Database (PDB) hosted in an Oracle 21c Container Database.
Source Database: ORCL (19c Non-CDB )
Target Database: DB21C (21c CDB)
###########################################################################
Create Auto Upgrade configuration file
###########################################################################
[oracle@db21c ~]$ vi config.txt
ORCL.source_home=/opt/oracle/product/19c/dbhome_1
ORCL.target_home=/u01/app/oracle/product/21.0.0.0/dbhome_1
ORCL.sid=ORCL
ORCL.log_dir=/u01/app/oracle/upgrade-jobs
ORCL.restoration=yes
ORCL.target_cdb=db21c
###########################################################################
Create directory for Auto Upgrade log files
###########################################################################
[oracle@db21c ~]$ mkdir -p /u01/app/oracle/upgrade-jobs
###########################################################################
Set Oracle 21c environment and run autoupgrade in ANALYZE mode
###########################################################################
[oracle@db21c ~]$ . oraenv
ORACLE_SID = [ORCL] ? db21c
The Oracle base has been changed from /opt/oracle to /u01/app/oracle
[oracle@db21c ~] $ cd $ORACLE_HOME/rdbms/admin
[oracle@db21c admin]$ pwd
/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin
[oracle@db21c admin]$ java -jar autoupgrade.jar -config /home/oracle/config.txt -mode analyze -console
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 100| ORCL|PRECHECKS|PREPARING|RUNNING|20/12/15 08:06|08:06:10|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------+
| 100| ORCL|PRECHECKS|PREPARING|RUNNING|20/12/15 08:06|08:07:48|Remaining 3/88|
+----+-------+---------+---------+-------+--------------+--------+--------------+
Total jobs 1
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for ORCL
###########################################################################
Review Pre-Upgrade log files
###########################################################################
[oracle@db21c 100]$ pwd
/u01/app/oracle/upgrade-jobs/ORCL/100
[oracle@db21c 100]$ ls -l
total 116
-rwx------ 1 oracle oinstall 103401 Dec 15 08:08 autoupgrade_20201215.log
-rwx------ 1 oracle oinstall 552 Dec 15 08:08 autoupgrade_20201215_user.log
-rwx------ 1 oracle oinstall 279 Dec 15 08:08 autoupgrade_err.log
drwx------ 2 oracle oinstall 4096 Dec 15 08:11 prechecks
[oracle@db21c 100]$ cd prechecks/
[oracle@db21c prechecks]$ ls -l
total 264
-rwx------ 1 oracle oinstall 4223 Dec 15 08:07 orcl_checklist.cfg
-rwx------ 1 oracle oinstall 16246 Dec 15 08:07 orcl_checklist.json
-rwx------ 1 oracle oinstall 15458 Dec 15 08:07 orcl_checklist.xml
-rwx------ 1 oracle oinstall 35410 Dec 15 08:07 orcl_preupgrade.html
-rwx------ 1 oracle oinstall 15588 Dec 15 08:07 orcl_preupgrade.log
-rwx------ 1 oracle oinstall 148783 Dec 15 08:07 prechecks_orcl.log
-rwx------ 1 oracle oinstall 22751 Dec 15 08:08 upgrade.xml
###########################################################################
Run autoupgrade in DEPLOY mode
###########################################################################
[oracle@db21c admin]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /home/oracle/config.txt -mode deploy -console
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg>
###########################################################################
Guaranteed Restore Point is being created
###########################################################################
upg> lsj
+----+-------+-----+---------+-------+--------------+--------+-------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS| START_TIME| UPDATED|MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+-------+
| 101| ORCL| GRP|EXECUTING|RUNNING|20/12/15 08:24|08:28:46| |
+----+-------+-----+---------+-------+--------------+--------+-------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 101| ORCL|PRECHECKS|PREPARING|RUNNING|20/12/15 08:24|08:28:50|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+---------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+---------------+
| 101| ORCL|PRECHECKS|PREPARING|RUNNING|20/12/15 08:24|08:30:35|Remaining 43/88|
+----+-------+---------+---------+-------+--------------+--------+---------------+
#######################################################################################
Gathering dictionary statistics and check for INVALID objects in the PREFIXUPS phase
#######################################################################################
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 101| ORCL|PREFIXUPS|EXECUTING|RUNNING|20/12/15 08:24|08:31:05|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
2020-12-15 08:31:35.863 INFO Running 3 fixups in parallel, 2 at the time level 0 - FixUpsRunner.executeBatch
2020-12-15 08:31:35.865 INFO Starting fixup execution for [DICTIONARY_STATS] [ORCL] - FixUpTrigger.call
2020-12-15 08:31:35.868 INFO Starting fixup execution for [INVALID_OBJECTS_EXIST] [ORCL] - FixUpTrigger.call
2020-12-15 08:31:35.885 INFO Gossip Thread has begun to monitor remaining checks/fixups for db [ORCL] - Gossip.run
2020-12-15 08:34:34.332 INFO FixUp [DICTIONARY_STATS][ORCL] elapsed 177 seconds - FixUpTrigger.executeFixUp
2020-12-15 08:34:34.339 INFO Starting fixup execution for [PRE_FIXED_OBJECTS] [ORCL] - FixUpTrigger.call
upg> lsj
+----+-------+-----+---------+-------+--------------+--------+------------------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+------------------+
| 101| ORCL|DRAIN|EXECUTING|RUNNING|20/12/15 08:24|08:52:46|Executing describe|
+----+-------+-----+---------+-------+--------------+--------+------------------+
###########################################################################
Database Upgrade phase now starts
###########################################################################
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+---------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+---------------+
| 101| ORCL|DBUPGRADE|EXECUTING|RUNNING|20/12/15 08:24|08:54:57|0%Upgraded ORCL|
+----+-------+---------+---------+-------+--------------+--------+---------------+
upg> status
---------------- Config -------------------
User configuration file [/home/oracle/config.txt]
General logs location [/u01/app/oracle/upgrade-jobs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
DB upg fatal errors ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time [1440] minutes
DB restore abort time [120] minutes
DB GRP abort time [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total CDB being processed [1]
Jobs finished successfully [0]
Jobs finished/aborted [0]
Jobs in progress [1]
Jobs stage summary
Job ID: 101
DB name: ORCL
SETUP <1 min
GRP <1 min
PREUPGRADE <1 min
PRECHECKS 2 min
PREFIXUPS 2 min
DRAIN 2 min
DBUPGRADE 1 min (IN PROGRESS)
------------ Resources ----------------
Threads in use [29]
JVM used memory [55] MB
CPU in use [13%]
Processes in use [20]
[oracle@db21c dbupgrade]$ pwd
/u01/app/oracle/upgrade-jobs/ORCL/101/dbupgrade
[oracle@db21c dbupgrade]$ ls -lrt
total 92
-rwx------ 1 oracle oinstall 34367 Dec 15 08:54 phase.log
-rwx------ 1 oracle oinstall 516 Dec 15 08:54 catupgrd20201215084736orcl_catcon_99089.lst
-rwx------ 1 oracle oinstall 328 Dec 15 08:55 catupgrd20201215084736orcl_catcon_kill_sess_99089_ALL.sql
-rwx------ 1 oracle oinstall 63 Dec 15 08:55 catupgrd20201215084736orcl_catcon_99814.done
-rwx------ 1 oracle oinstall 1500 Dec 15 08:55 catupgrd20201215084736orcl1.log
-rwx------ 1 oracle oinstall 2775 Dec 15 08:55 autoupgrade20201215084736orcl.log
-rwx------ 1 oracle oinstall 35544 Dec 15 08:56 catupgrd20201215084736orcl0.log
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 101| ORCL|DBUPGRADE|EXECUTING|RUNNING|20/12/15 08:24|09:04:15|22%Upgraded ORCL|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 101| ORCL|DBUPGRADE|EXECUTING|RUNNING|20/12/15 08:24|09:19:34|52%Upgraded ORCL|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1
[oracle@db21c dbupgrade]$ tail -f catupgrd20201215084736orcl0.log
DBUA_TIMESTAMP RAC FINISHED 2020-12-15 09:18:33 Container=ORCL Id=4
DBUA_TIMESTAMP RAC UPGRADED 2020-12-15 09:18:33
COMP_TIMESTAMP JAVAVM 2020-12-15 09:19:24
DBUA_TIMESTAMP JAVAVM FINISHED 2020-12-15 09:19:24 Container=ORCL Id=4
DBUA_TIMESTAMP JAVAVM UPGRADED 2020-12-15 09:19:24
DBUA_TIMESTAMP XML STARTED 2020-12-15 09:19:24 Container=ORCL Id=4
COMP_TIMESTAMP XML 2020-12-15 09:20:58
DBUA_TIMESTAMP XML FINISHED 2020-12-15 09:20:58 Container=ORCL Id=4
DBUA_TIMESTAMP XML UPGRADED 2020-12-15 09:20:58
DBUA_TIMESTAMP CATJAVA STARTED 2020-12-15 09:20:58 Container=ORCL Id=4
COMP_TIMESTAMP CATJAVA 2020-12-15 09:21:16
DBUA_TIMESTAMP CATJAVA FINISHED 2020-12-15 09:21:16 Container=ORCL Id=4
DBUA_TIMESTAMP CATJAVA UPGRADED 2020-12-15 09:21:16
DBUA_TIMESTAMP XDB STARTED 2020-12-15 09:21:17 Container=ORCL Id=4
COMP_TIMESTAMP XDB 2020-12-15 09:23:13
DBUA_TIMESTAMP XDB FINISHED 2020-12-15 09:23:13 Container=ORCL Id=4
DBUA_TIMESTAMP XDB UPGRADED 2020-12-15 09:23:13
DBUA_TIMESTAMP ORDIM STARTED 2020-12-15 09:23:14 Container=ORCL Id=4
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 101| ORCL|DBUPGRADE|EXECUTING|RUNNING|20/12/15 08:24|09:34:50|88%Upgraded ORCL|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1
###########################################################################
After upgrade is 100% complete, review Upgrade Summary log
###########################################################################
[oracle@db21c dbupgrade]$ cat upg_summary.log
Oracle Database Release 21 Post-Upgrade Status Tool 12-15-2020 09:35:4
Container Database: DB21C
[CON_ID: 4 => ORCL]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 21.1.0.0.0 00:17:41
JServer JAVA Virtual Machine UPGRADED 21.1.0.0.0 00:05:54
Oracle XDK UPGRADED 21.1.0.0.0 00:01:34
Oracle Database Java Packages UPGRADED 21.1.0.0.0 00:00:18
OLAP Analytic Workspace UPGRADED 21.1.0.0.0 00:00:24
Oracle Label Security UPGRADED 21.1.0.0.0 00:00:08
Oracle Database Vault UPGRADED 21.1.0.0.0 00:01:25
Oracle Text UPGRADED 21.1.0.0.0 00:01:21
Oracle Workspace Manager UPGRADED 21.1.0.0.0 00:01:42
Oracle Real Application Clusters UPGRADED 21.1.0.0.0 00:00:00
Oracle XML Database UPGRADED 21.1.0.0.0 00:01:55
Oracle Multimedia UPGRADED 21.1.0.0.0 00:01:26
Spatial UPGRADED 21.1.0.0.0 00:09:57
Oracle OLAP API UPGRADED 21.1.0.0.0 00:00:18
Final Actions 00:00:05
Post Upgrade 00:00:26
Total Upgrade Time: 00:39:43 [CON_ID: 4 => ORCL]
Database time zone version is 32. It is older than current release time
zone version 35. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time: [0d:0h:41m:57s]
###########################################################################
After database upgrade run the script to convert noncdb to PDB
###########################################################################
upg> lsj
+----+-------+-------------+---------+-------+--------------+--------+------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-------------+---------+-------+--------------+--------+------------------+
| 101| ORCL|NONCDBTOPDBXY|EXECUTING|RUNNING|20/12/15 08:24|09:36:49|noncdb_to_pdb - 0%|
+----+-------+-------------+---------+-------+--------------+--------+------------------+
Total jobs 1
upg> lsj
+----+-------+-------------+---------+-------+--------------+--------+-------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-------------+---------+-------+--------------+--------+-------------------+
| 101| ORCL|NONCDBTOPDBXY|EXECUTING|RUNNING|20/12/15 08:24|09:37:19|noncdb_to_pdb - 35%|
+----+-------+-------------+---------+-------+--------------+--------+-------------------+
2020-12-15 09:36:51.235 INFO Executing SQL [@/u01/app/oracle/upgrade-jobs/ORCL/temp/noncdbtopdb_orcl_ORCL.sql
upg> status
---------------- Config -------------------
User configuration file [/home/oracle/config.txt]
General logs location [/u01/app/oracle/upgrade-jobs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
DB upg fatal errors ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time [1440] minutes
DB restore abort time [120] minutes
DB GRP abort time [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total CDB being processed [1]
Jobs finished successfully [0]
Jobs finished/aborted [0]
Jobs in progress [1]
Jobs stage summary
Job ID: 101
DB name: ORCL
SETUP <1 min
GRP <1 min
PREUPGRADE <1 min
PRECHECKS 2 min
PREFIXUPS 2 min
DRAIN 2 min
DBUPGRADE 42 min
NONCDBTOPDBXY 6 min (IN PROGRESS)
------------ Resources ----------------
Threads in use [29]
JVM used memory [39] MB
CPU in use [13%]
Processes in use [16]
upg> lsj
+----+-------+-------------+---------+-------+--------------+--------+------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-------------+---------+-------+--------------+--------+------------------+
| 101| ORCL|NONCDBTOPDBXY|EXECUTING|RUNNING|20/12/15 08:24|09:59:59|Validating PDB {0}|
+----+-------+-------------+---------+-------+--------------+--------+------------------+
Total jobs 1
###########################################################################
ORCL is now a Pluggable Database
###########################################################################
SQL> alter pluggable database "ORCL" open read write force ;
Pluggable database altered.
SQL> spool off
SQL>
SQL> Disconnected from Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
###########################################################################
Post-Upgrade phase now starts
###########################################################################
upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------------+
| 101| ORCL|POSTCHECKS|PREPARING|RUNNING|20/12/15 08:24|10:01:26|Remaining 2/12|
+----+-------+----------+---------+-------+--------------+--------+--------------+
Total jobs 1
upg> lsj
+----+-------+----------+---------+-------+--------------+--------+-------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+-------------+
| 101| ORCL|POSTFIXUPS|EXECUTING|RUNNING|20/12/15 08:24|10:01:52|Remaining 3/5|
+----+-------+----------+---------+-------+--------------+--------+-------------+
Total jobs 1
###########################################################################
Gather statistics and Time Zone DST upgrade
###########################################################################
2020-12-15 10:01:51.763 INFO Executing SQL [DECLARE
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS('SYS', 'I_OBJ#');
SYS.DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/
] in [db21c, container:ORCL] - ExecuteSql$SQLClient.run
2020-12-15 10:04:25.954 INFO Executing SQL [DECLARE
BEGIN
SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
] in [db21c, container:ORCL] - ExecuteSql$SQLClient.run
upg> lsj
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
| 101| ORCL|POSTFIXUPS|EXECUTING|RUNNING|20/12/15 08:24|10:08:41|Loading database information|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
2020-12-15 10:07:43.667 INFO Running fixup [OLD_TIME_ZONES_EXIST][ORCL][JAVA][Java based fixup] - FixUpTrigger.executeFixUp
2020-12-15 10:07:43.670 INFO Starting - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.671 INFO All the scripts were found, using the scripts from /u01/app/oracle/product/21.0.0.0/dbhome_1 - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.671 INFO Adding script to list [/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin/sqlsessstart.sql] - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.671 INFO Adding script to list [/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin/utltz_upg_check.sql] - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.672 INFO Adding script to list [/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin/utltz_upg_apply.sql] - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.672 INFO Adding script to list [/u01/app/oracle/product/21.0.0.0/dbhome_1/rdbms/admin/sqlsessend.sql] - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.672 INFO End - old_time_zones_exist.populateScriptsToExecute
2020-12-15 10:07:43.672 INFO TimeZone upgrade log files will be located under /u01/app/oracle/upgrade-jobs/ORCL/temp - old_time_zones_exist.fixUpCode
###########################################################################
Upgrade to 21c and conversion to a PDB is now completed
###########################################################################
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 101 for ORCL
[oracle@db21c admin]$ sqlplus sys as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 17 08:35:26 2020
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB21C READ WRITE NO
4 ORCL READ WRITE NO
