Upgrade PDBs – Everything At Once (Full CDB Upgrade)

As referred to it before there are two techniques to upgrade an Oracle Multitenant environment:

• Everything at Once
• One (or many) at a Time — via unplug/plug

In this post I will explain the method of “Everything At Once” and describe all the steps. The benefit of this approach is simplicity and ease of maintenance. In an upgrade workshop in Melbourne earlier this year a DBA from Germany came by in one of the breaks explaining that he takes care on over 100 developer databases – and it would ease his life a lot if he could “hit” all of the databases at the same time with one patch set or PSU, or even upgrade them to a higher release. This is the use case where it will make a lot of sense to leverage from this approach. But be aware and don’t over-consolidate as the pain point is common downtime. If you plan to use this approach you need to check before if your application owners can agree on common downtime windows – otherwise you may end in trouble. Big trouble!

The technique is easy to describe:

• CDB$ROOT will always get upgraded first – I call this CYCLE 1
  • The “-n” parameter of catctl.pl will define how many parallel workers run the upgrade – 8 is the current maximum
  • The “-M” option will decide whether the CDB$ROOT stays in UPGRADE mode throughout the entire process of upgrade or becomes available for access after being upgraded leading to the fact that PDBs become available for access as well once they got upgraded – set “-M” and the CDB$ROOT will stay in UPGRADE mode throughout the entire process
• Afterwards we can upgrade multiple PDBs (including the PDB$SEED) in parallel at the same time – I call this CYCLE 2,3, …
  • The “-n” parameter (divided by 2) of catctl.pl will determine how many PDBs will be upgraded in parallel
  • Your limiting factor is CPU power and cores
  • The “-N” parameter can alter the number of parallel workers per PDB – default is 2.

A few simple examples will demonstrate how the parameters work hand-in-hand:

• CDB has (always) one PDB$SEED and 25 PDBs numbered PDB1 .. PDB25
• That means we’ll have CYCLE 1 (for the CDB$ROOT) and between one additional CYCLE 2 (for all remaining 26 PDBs in parallel) up to 27 CYCLES once you decide to have no PDBs upgraded at the same time together with another PDB
  .
• catctl.pl -M -n 16 would lead to:
  • CYCLE 1: CDB$ROOT
  • CYCLE 2: PDB$SEED, PDB1-PDB7 (-n 16 divided by 2 = 8 PDBs to upgrade in parallel)
  • CYCLE 3: PDB8-PDB15
  • CYCLE 4: PDB16-PDB23
  • CYCLE 5: PDB24 and PDB25
  • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
  • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting
• catctl.pl -M -n 26 would lead to:
  • CYCLE 1: CDB$ROOT
  • CYCLE 2: PDB$SEED, PDB1-PDB12 (-n 26 divided by 2 = 13 PDBs to upgrade in parallel)
  • CYCLE 3: PDB13-PDB25
  • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
  • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting
• catctl.pl -n 52 -N 1 would lead to:
  • CYCLE 1: CDB$ROOT
  • CYCLE 2: PDB$SEED, PDB1-PDB25 (-n 52 divided by 2 = 26 PDBs to upgrade in parallel)
  • Each PDB will be upgraded with 1 workerl as -N is 1
  • The CDB$ROOT will be available once upgraded – same applies for PDBs once the upgrade is finished

Step-by-step instructions:

Preupgrade Steps

• Create a guaranteed restore point in order to flashback in case of failure
  CREATE RESTORE POINT UPGRADE GUARANTEE FLASHBACK DATABASE;
• Copy preupgrd.sql and utluppkg.sql from the Oracle 12.1.0.2 home’s ?/rdbms/admin into the source Oracle 12.1.0.1 ?/rdbms/admin directory. THIS STEP IS EXTREMLY IMPORTANT as otherwise the preupgrd.sql and the utluppkg.sql from 12.1.0.1 will be loaded into the 12.1.0.1 database – but the concept requires always to use the preupgrd.sql/utluppkg.sql from the higher version. If you fail you see it (a) hanging and (b) getting plenty of errors.
• Execute preuprd.sql within the source database – database needs to be up and running:
  $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/rdbms/admin -l /home/oracle/mike -b preupgrd preupgrd.sql
  • It will create 3 files which combine all information for the root, the seed and all pdbs together into one preupgrade.log, one preupgrade_fixups.sql and one postupgrade_fixups.sql. Default location for those files is $ORACLE_HOME/cfgtoollogs/<SID>/preupgrade
  • Verify the preupgrad.log and follow all advices
• Execute the preupgrade_fixups.sql while all PDBs are open:
  ALTER PLUGGABLE DATABASE ALL OPEN;
  $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b preupgrade_fixups preupgrade_fixups.sql
• Copy the init<sid>.ora into the new $ORACLE_HOME/dbs
• Specific stepts for RAC environments:
  • Set cluster_database=false
    ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
  • Stop all instances
    srvctl stop database -d <db_name>

Upgrade

• Switch to the new $ORACLE_HOME including all necessary env variables
• Connect with sqlplus:
  sqlplus / as sysdba
• Bring the CDB$ROOT instance into upgrade mode:
  STARTUP UPGRADE
• Bring all PDBs into upgrade mode:
  ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
• Control it with:
  SHOW PDBS
  Status should be MIGRATE for all PDBs
• Exit from SQL*Plus and cd to $ORACLE_HOME/rdbms/admin :
  EXIT
  cd $ORACLE_HOME/rdbms/admin
• Perform the upgrade in parallel:
  $ORACLE_HOME/perl/bin/perl catctl.pl -d $ORACLE_HOME/rdbms/admin -n 16 -M -l /home/oracle/mike catupgrd.sql
• The important file with timings per PDB and to for a quick check is called upg_summary.log and can be found in:
  $ORACLE_HOME/cfgtoollogs/<SID>/upgrade/upg_summary.log

Postupgrade Steps

• Only in case -M hasn’t been used then the CDB remains open during the upgrade of the PDBs and will need to be shutdown manually post upgrade:
  SHUTDOWN IMMEDIATE
• Followed by a startup all PDBs must be opened now for recompilation
  STARTUP
  ALTER PLUGGABLE DATABASE ALL OPEN;
• Execute the postupgrade_fixups.sql:
  $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql
• Exit from SQL*Plus and cd into $ORACLE_HOME/rdbms/admin :
  EXIT
  cd $ORACLE_HOME/rdbms/admin
• The recompilation is done via catcon.pl using the utlrp.sql script from within ?/rdbms/admin:

  $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

  1. utl.rp does not parallelize between PDBs
  2. Default parallel degree is: cpu_cores x 2
     
  3. It takes roughly 45 sec per PDB PLUS another 30 seconds to
     initialize XDB – as this happens serially it takes approx a while to
     complete recompilation past upgrade
• Drop the guaranteed restore point
  DROP RESTORE POINT UPGRADE;
• In RAC environments only:
  • Set cluster_database=true again
    ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
  • Start all instances
    srvctl start database -d <db_name>

Seems to be a lot of work, too? But in fact most of the steps are applicable to any upgrade such as the preupgrd.sql etc. Only remarkable change is the need to start scripts with catcon.pl – and not directly within SQL*Plus. Please remember that this approach will upgrade as many PDBs as you have depending on your CPU power in parallel.

In my test environment (a very outdated Exadata V1 without Flash, 6 year old disks, 2 physical CPU sockets) I upgrade 25 PDBs each roughly 25 GB in size with user data in it and all options present in less than 3 hours including recompilation. Please repeat this exercise with 25 independent databases consolidated on the same node within the same time 😉 Try it 🙂

Please see also my updated steps +2 years later when Oracle Database 12.2.0.1 became available:

https://mikedietrichde.com/2017/05/10/upgrade-everything-at-once-multitenant-upgrade-from-oracle-12-1-to-12-2/

–Mike