Unplug / Plug / Upgrade with AutoUpgrade

When we launched AutoUpgrade, it could upgrade non-CDBs and CDBs with all PDBs. Soon after, it learned to upgrade a non-CDB and plug it into an existing CDB as a new PDB. And then, as next step, we added Unplug / Plug / Upgrade with AutoUpgrade. In this case you have a PDB (or hopefully more than one), and you’d like to upgrade one or many PDBs by moving them to a new CDB with a higher database version.

Photo by Kelly Sikkema on Unsplash

Test Setup

As test setup I use our Hands-On Lab.

I will let AutoUpgrade unplug PDB3 from CDB1 in Oracle 12.2.0.1, and plug it into CDB2 with Oracle 19.11.0 and upgrade it. And trust me, it’s as simple as writing this sentence. You will find the “old” way in the Hands-On Lab instructions right now. But now it’s time to do it with AutoUpgrade.

SQL> select CDB, CON_ID, NAME from V$DATABASE;

CDB	CON_ID NAME
--- ---------- ---------
YES	     0 CDB1

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB3 			  READ WRITE NO

Always download the newest AutoUpgrade Utility at first and move it into your 19c ?/rdbms/admin directory.

Unplug / Plug / Upgrade

This technique gives you a lot of flexibility with upgrades in a Multitenant environment. We differ between two techniques:

• Everything at Once
• Unplug / Plug / Upgrade

You can complete the latter approach with one or many PDBs at the same time.

Unplug / Plug / Upgrade of a PDB between two different CDB releases

Especially when you have different downtime requirements between your PDBs, or in case one application is ready for upgrade while the other hosted in another PDB isn’t, this is the right technique for you.

Please be aware that the default AutoUpgrade will chose is the NOCOPY option. Your files will remain in place and won’t be moved.

AutoUpgrade Config File

Every AutoUpgrade exercise usually starts with a config file. AU works now without a config file but instead using environment variables only. But here I’d like to share the config file I will use:

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/19
upg1.sid=CDB1
upg1.pdbs=PDB3
upg1.target_cdb=CDB2
upg1.log_dir=/home/oracle/logs

So I advice AutoUpgrade to tackle PDB3 (upg1.pdbs=PDB3) in CDB1 (upg1.sid=CDB1) and move it to CDB2 (upg1.target_cdb=CDB2).

Analyze Phase

It is always best practice to run an “analyze” at first to check whether there will be any issues you should fix at first.

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -mode analyze -config CDB1PDB3.cfg 
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|   CDB1|PRECHECKS|PREPARING|RUNNING|21/06/07 14:07|14:07:09|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
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

Let’s have a quick look at the status report, and especially the important parts of it:

In my particular case, AutoUpgrade finds nothing to worry about and displays one important WARNING:

Hence, in case I have no proper and proven backup and recovery strategy, I’d rather should use the COPY option. My config file will need one additional entry, upg1.target_pdb_copy_option as this will advice AutoUpgrade to COPY my PDB.

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/19
upg1.sid=CDB1
upg1.pdbs=PDB3
upg1.target_cdb=CDB2
upg1.target_pdb_copy_option=file_name_convert=('CDB1', 'CDB2')
upg1.log_dir=/home/oracle/logs

It will then move the PDB3 from its current location /u02/oradata/CDB1/pdb3 to /u02/oradata/CDB2/pdb3. As you can see, the “covert” algorithm is working in a very simple way. It just replaces the differing parts of the path, here CDB1 with CDB2, and leaves the rest as-is.

Deploy Phase

Now let AutoUpgrade do the Unplug / Plug / Upgrade.

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -mode deploy -config CDB1PDB3.cfg 
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg>

I will monitor it from the command prompt every 10 minutes or so.

upg> lsj -r
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|                     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 101|   CDB1|PREFIXUPS|EXECUTING|RUNNING|21/06/07 14:17|14:18:34|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1

upg> lsj -r
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|         MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 101|   CDB1|DBUPGRADE|EXECUTING|RUNNING|21/06/07 14:17|14:22:00|18%Upgraded PDB3|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1

upg> lsj -r
+----+-------+---------+---------+-------+--------------+--------+----------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|         MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------+
| 101|   CDB1|DBUPGRADE|EXECUTING|RUNNING|21/06/07 14:17|14:28:01|39%Upgraded PDB3|
+----+-------+---------+---------+-------+--------------+--------+----------------+
Total jobs 1

upg> lsj -r
+----+-------+----------+---------+-------+--------------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+-------------+
| 101|   CDB1|POSTFIXUPS|EXECUTING|RUNNING|21/06/07 14:17|14:35:53|Remaining 1/4|
+----+-------+----------+---------+-------+--------------+--------+-------------+
Total jobs 1

upg> Job 101 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

Actually, if you weren’t aware already, you will get a pretty decent summary at the end of the upgrade, at first for AutoUpgrade and the different stages:

So you can see that the actual upgrade of the PDB took 13 minutes 49 seconds in my case.

Final check:

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 15:26:22 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB3 			  READ WRITE NO

PDB3 has been successfully plugged in and upgrade to Oracle 19.11.0.

Plugin the “old” PDB3 back into Source?

And in my 12.2.0.1 CDB1, it has been removed:

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 7 15:28:12 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO

That is expected. But the files remain still in the previous place.

$ cd PDB3
[CDB1] oracle@hol:/u02/oradata/CDB1/PDB3
$ ls
sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

If needed, I could plugin the PDB3 again into CDB1. In case you’d like to plugin PDB3 now again into the source CDB1, you will find the XML manifest file in the AutoUpgrade log directory:

~/logs/CDB1/101/drain/PDB3.xml

It exists only in the log tree created by the “deploy” command – and there in the drain subdirectory.

With this file, I can logon to my CDB1 and plugin again:

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 7 15:38:47 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database PDB3 using '/home/oracle/logs/CDB1/101/drain/PDB3.xml' NOCOPY TEMPFILE REUSE;
Pluggable database created.

SQL> alter pluggable database PDB3 open;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB3 			  READ WRITE NO

Not bad 🙂

Summary

As you see, using AutoUpgrade to move one or many PDBs to a higher release CDB version is simple and straight forward. Just keep in mind that you will need an additional AU parameter in order to use the more safer COPY option instead of the default, NOCOPY. Still, NOCOPY may be the best solution in case you have a very large database to move.

This approach gives you a lot of flexibility. And AutoUpgrade does all the checks for you, too.

Further Information and Links

• Upgrade and Plugin with AutoUpgrade
• Hands-On Lab 19c – Unplug / Plug / Upgrade
• Upgrade PDBs – One at a time (2014)
• Download the newest AutoUpgrade Utility
• Upgrade PDBs with RELOCATE – does it work?
• More information on TARGET_PDB_COPY_OPTION

–Mike