Oracle 12c Release 2 introduced a number of new features which would help reduce outages required for database upgrades.
Multitenancy feature was introduced in Oracle 12.1 and these features introduced in Oracle 12.2 would help significantly in not only reducing the overall duration required for a database upgrade, but also in managing and having more control over a Container and Pluggable Database (CDB and PDB) upgrades.
Let us take a look at some of those features.
The Parallel Upgrade Utility catctl.pl reduces the amount of time it takes to perform an upgrade by loading the database dictionary in parallel using multiple SQL processes to upgrade the database. It takes into account the available CPU resources on the server hosting the database being upgraded.
In 12.2 we can now run the database upgrade directly using the dbupgrade shell script located in the $ORACLE_HOME/bin directory.
The dbupgrade script is like a wrapper script for catctl.pl and we can use a number of flags with dbupgrade command to influence the ‘degree’ of parallelism which will be used for the upgrade as well as controlling which PDBs are to be included or excluded in the upgrade as well as assign a prioritised order in which the PDBs will be upgraded.
We can resume a failed upgrade once the problem which caused the upgrade has been fixed (with the -R flag). This feature is available both for the GUI Database Upgrade Assistant (DBUA) utility as well as upgrades performed via the command line.
The Parallel Upgrade Utility Parameters -n and -N will determine how many PDBs will be upgraded in parallel as well as enable us to control the number of parallel SQL processes to use when upgrading databases.
We can also run the Parallel Upgrade Utility in Emulation mode (with the -E flag) and this will show us the various parameters which will be used to actually upgrade the database. We can verify the output and determine if we need to change any parameters before we actually perform the database upgrade. For example, we can run an upgrade emulation to obtain more information about how the resource allocation choices made using the -n and -N parameters are carried out.
The ability to prioritize the upgrade of the individual PDBs in a CDB when you upgrade the CDB is also now available. So we can now upgrade a certain PDB first before other PDBs and once the upgrade is over open it in Read-Write mode and available for application access – while other PDBs are still being upgraded. This can be achieved by creating a Priority List text file and using the -L flag to point to the location of this file.
We can also use the -M flag which places the CDB and all its PDBs in upgrade mode resulting in a reduced overall total upgrade time. However, you cannot bring up any individual PDB until the CDB and all its associated PDBs are upgraded.
Application data tablespaces can also be placed in Read-Only mode for the duration of the upgrade using the -T flag. The tablespaces are automatically put into Read-Write mode once the upgrade is completed. This may help in just having to do a partial restore in case we have a failure during the upgrade and wish to ‘rollback’. Think of this as something similar to creating a Guaranteed Restore Point as part of your database upgrade strategy. This feature would be particularly useful if we were upgrading large Data Warehouse databases typically running in NOARCHIVELOG mode or where we cannot use the FLASHBACK DATABASE feature in cases like using the Standard Edition of the Oracle database software.