Infrastructure at your Service

Alain Fuhrer

Auto Upgrade of non CDB to PDB with autoupgrade.jar in Oracle 21c

Since Oracle 21c it is possible to convert the non CDB database into a PDB directly during the upgrade using the Oracle Autoupgrade Tool.
This is an important enhancement, because with Oracle 21c the non CDB architecture is desupported, which means that with an upgrade to 21c a conversion is mandatory.
Let’s test if this new feature really works and what needs to be considered.

Work done by autoupgrade utility

  1. Check and Prepare Source non CDB Database
  2. Convert non CDB to PDB in Source Release
  3. Upgrade PDB to Target Release
  4. If the database is running in a cluster configuration, the cluster resources are also automatically adjusted.

Requirements on Source and Target DB for auto conversion during upgrade

  • The target CDB must be created in advance of performing the upgrade with AutoUpgrade.
  • The PDB created from the non-CDB must continue to use the source non-CDB name. You cannot change the name of the database.
  • The same set of Oracle Database options are configured for both the source and target.
  • The endian format of the source and target CDBs are identical.
  • The source and target CDBs have compatible character sets and national character sets.
  • The source non-CDB Oracle Database release and operating system platform must be supported for direct upgrade to the target CDB release.
  • Operating system authentication is enabled for the source and target CDBs

Fallback Scenario

  • There is no option to roll back to the non CDB Oracle Database after the upgrade has started. So please create a full backup of your non CDB before starting, that you have the possibility to restore the database in case of any problems!

Test Environment and Setup

OS Oracle Linux 8
Grid Infrastructure for Standalone Server 21.3.0
Source non CDB (19.3.0)  noncdb1
Target CDB (21.3.0) cdb01
Database Options  All options are installed in both databases

Update autoupgrade.jar
Since Oracle 19c, the autoupgrade.jar is part of the oracle binaries and located under $ORACLE_HOME/rdbms/admin. Since the autoupgrade tool is constantly being developed, it is recommended that you always download and install the latest version. The latest version can be downloaded from Oracle Support (Doc ID 2485457.1).

#Copy downloaded autoupgrade.jar to target database home 
cp /tmp/autoupgrade.jar /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/admin

Create log directory and Config File
autoupgrade tool works with a config file. In this file you define all necessary parameters for the upgrade. First i create some directories for the upgrade logfiles and afterwards the config file for the upgrade.

#create directories for the upgrade
mkdir /u01/app/oracle/autoupgrade
 mkdir /u01/app/oracle/autoupgrade/noncdb1_to_cdb1
#create config file 
cat /u01/app/oracle/autoupgrade/noncdb1_to_cdb1/config.txt
global.autoupg_log_dir=/u01/app/oracle/autoupgrade
upg1.sid=noncdb1
upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
upg1.log_dir=/u01/app/oracle/autoupgrade/noncdb_to_pdb/
upg1.target_home=/u01/app/oracle/product/21.0.0/dbhome_1
upg1.target_base=/u01/app/oracle
upg1.target_version=21.3.0
# That is the new parameter introduced with 21c to define the target cdb for the new pdb
upg1.target_cdb=cdb01

Execute Pre Upgrade Tasks
The autoupgrade has to be started always in the target ORACLE_HOME, if not, you get an error. First i start the upgrade only in analyze mode. In this mode only the pre upgrade tasks will be executed and at the end you get a summary if everything is ready and all requirements are met for the upgrade.

java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode analyze

You will automatic connected to the autoupgrade cli where you can check the progress of the upgrade activities and list some job details if you want

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 100|noncdb1|PRECHECKS|PREPARING|RUNNING|21/08/19 21:37|21:37:03|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 100|noncdb1|PRECHECKS|PREPARING|RUNNING|21/08/19 21:37|21:38:26|Remaining 83/104|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1

At the End you get a summary of the Job and a report in text and html format

upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED WITH ERROR -------------
Job 100 for noncdb1
Please check the summary report at:
/u01/app/oracle/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

Lets have a look at the summary Report, that look good so far.

==========================================
Autoupgrade Summary Report
==========================================
[Date] Thu Aug 19 22:26:13 CEST 2021
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] noncdb1
[Version Before Upgrade] 19.3.0.0.0
[Version After Upgrade] 21.3.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2021-08-19 22:24:06
[Duration] 0:02:07
[Log Directory] /u01/app/oracle/autoupgrade/noncdb_to_pdb/noncdb1/101/prechecks
[Detail] /u01/app/oracle/autoupgrade/noncdb_to_pdb/noncdb1/101/prechecks/noncdb1_preupgrade.log
Precheck passed and no manual intervention needed
------------------------------------------

If the status of the report is FAILD you can check the <ORACLE_SID>_preupgrade.log for more information. In my Environment the detail logfile is located in

/u01/app/oracle/autoupgrade/noncdb_to_pdb/noncdb1/100/prechecks/noncdb1_preupgrade.log

Start real upgrade and conversion
So from here there is no way back to the old non cdb database. So check that you have a full backup before you start the upgrade in deploy mode. Deploy mode will execute the pre checks again, then execute the upgrade and conversion and finally the post task like compile invalid objects or register the new pdb in the cluster.

java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode deploy

Like in the analyze mode you have the possibility to check the status of your upgrade in the cli. The most important cli commands in the autoupgrade are:

lsj show current job
lsj -e show jobs with errors
status -job <job_id> Details for the current job
logs Infos about the log directories

Checks and some adjustments
If everything works well, at the end you will provided with a summary report over the upgrade in html and as normal logfile. The report shows the tasks done during the upgrade, the time used and the log directory providing some detail information about the jobs.

Also the Oracle Grid Infrastructure resource was automatically changed during the autoupgrade. The Resource for the old noncdb1 database was removed from the cluster and a new resource for the pdb was added

At the end you will probably do some maintenance work. In my test lab, the database files are stored in ASM. So because the autoupgrade perform the pdb plugin operation with nocopy mode, the source files of the noncdb will be used for the pdb. In this case you have still the old db name in the file path.

SQL> select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
1 +DATA/CDB01/DATAFILE/system.261.1081002215
2 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/system.262.1081002217
3 +DATA/CDB01/DATAFILE/sysaux.263.1081002219
4 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/sysaux.264.1081002221
5 +DATA/CDB01/DATAFILE/undotbs1.265.1081002223
6 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/undotbs1.266.1081002223
7 +DATA/CDB01/DATAFILE/users.269.1081002235
12 +DATA/NONCDB1/DATAFILE/system.286.1081018771
13 +DATA/NONCDB1/DATAFILE/sysaux.276.1081018781
14 +DATA/NONCDB1/DATAFILE/undotbs1.277.1081018789
15 +DATA/NONCDB1/DATAFILE/users.278.1081018795

To fix that, you can perform an online move of the datafiles and oracle will change the file names to <CDB_NAME>/<PDB_ID>/DATAFILE/<DBF_NAME>.
Yes i know that is a feature of Enterprise Edition and perhaps your databases run only on SE or SE2. For this case you can set the parameter TARGET_PDB_COPY_OPTION in the config file for autoupgrade. That will also work for SE Customers and will copy the non cdb datafiles during the plugin operation. As Always Mike Dietrich did a great post about this option and how to use it

SQL> alter session set container=noncdb1;
Session altered.
SQL> alter database move datafile 12 to '+DATA';
Database altered.
SQL> alter database move datafile 13 to '+DATA';
Database altered.
SQL> alter database move datafile 14 to '+DATA';
Database altered.
SQL> alter database move datafile 15 to '+DATA';
Database altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
1 +DATA/CDB01/DATAFILE/system.261.1081002215
2 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/system.262.1081002217
3 +DATA/CDB01/DATAFILE/sysaux.263.1081002219
4 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/sysaux.264.1081002221
5 +DATA/CDB01/DATAFILE/undotbs1.265.1081002223
6 +DATA/CDB01/C9E99EACD6BE6700E0530A01A8C0DEDD/DATAFILE/undotbs1.266.1081002223
7 +DATA/CDB01/DATAFILE/users.269.1081002235
12 +DATA/CDB01/C9EBB833AB831A02E0530A01A8C090C5/DATAFILE/system.272.1081342185
13 +DATA/CDB01/C9EBB833AB831A02E0530A01A8C090C5/DATAFILE/sysaux.286.1081342195
14 +DATA/CDB01/C9EBB833AB831A02E0530A01A8C090C5/DATAFILE/undotbs1.276.1081342201
15 +DATA/CDB01/C9EBB833AB831A02E0530A01A8C090C5/DATAFILE/users.277.108134220

In my tests it was not possible to define the target pdb name after the upgrade. At the moment the name for the pdb will be the same as for the noncdb database. This is no big issue, and we can change the pdb name now. I will change the name from noncdb1 to pdb1.

SQL> alter pluggable database noncdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database noncdb1 open restricted;
Pluggable database altered.
SQL> alter pluggable database noncdb1 rename global_name to pdb1;
Pluggable database altered.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> alter pluggable database pdb1 save state;

Conclusion
As you can see the autoupgrade tool works better and better with every release. It is a big change, that the noncdb architecture is desupported now with 21c, but it’s nice, that you don’t have to create pdb’s or perform some plugin operation manually. Every upgrade project is different and it’s very important to understand, that the tool only helps you to automate most of the tasks of upgrading a database. The three most important things before you upgrade to a new release are test, test and test. This is still the job of the dba and the developers.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Alain Fuhrer
Alain Fuhrer

Delivery Manager & Consultant