Upgrade PDBs – One at a Time via unplug/plug/upgrade

Basically there are two techniques to upgrade an Oracle Multitenant environment:Everything at Once and One (or many) at a Time. This blog post describes the case how to Upgrade PDBs – One at a Time via unplug/plug/upgrade. I will describe all necessary steps. During some presentations, discussions etc people were left with the impression that it will be a very simple approach to unplug one or many PDBs from a CDB in lets say Oracle 12.1.0.1 and plug it into an Oracle 12.1.0.2 Container Database. Bingo, upgraded!

Well, unfortunately this is not true. In fact it is completely wrong.

If you want to upgrade via unplug/plug the following steps will have to be followed:

• In CDB1 environment – e.g. Oracle 12.1.0.1 with an PDB1
  • In SQL*Plus:
    • alter session set container=PDB1;
    • @$ORACLE_HOME_12102/rdbms/admin/preupgrd.sql
      (The output of the preupgrade.log will show you the location of the fixups)
    • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql
      (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)
    • exec dbms_stats.gather_dictionary_stats;
      (plus include all additional treatments recommended by the preupgrade.log)
    • alter session set container=CDB$ROOT; 
    • alter pluggable database PDB1 close;
    • alter pluggable database PDB1 unplug into ‘/stage/pdb1.xml’;
    • drop pluggable database PDB1 keep datafiles;
      The reason why you will need to DROP the PDB afterwards is simply to cleanup leftovers in the CDB views. It is under observation if this is a bug or not. The information does not get removed to allow quick plugin again but the leftovers may cause plenty of trouble once you’ll try to upgrade this CDB1 later on. But be aware (thanks to Martin Bach from Enkitec): Once you dropped the PDB from its original CDB you can revert to it with a previously taken backup. So it is best practice to backup your PDB in the destination CDB first, then issue the DROP command on the source as otherwise you’d sail for a while without a safety net.
    • exit
      .
• In CDB2 environment – e.g. Oracle 12.1.0.2
  • In SQL*Plus:
    • alter session set container=CDB$ROOT;
    • At this point we “could” do a Plug In Check but as the COMPATIBLE of the new CDB2 created as per recommendation with DBCA defaults to “12.1.0.2” the Plug In Check will result in “NO” – but obviously the plugin operation will work. Just for the records here’s the procedure to check plugin compatibility
      • SET
        SERVEROUTPUT ON
        DECLARE
        compatible CONSTANT VARCHAR2(3) := CASE
        DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => ‘/stage/pdb1.xml’,
        pdb_name => ‘PDB1’)
        WHEN TRUE THEN ‘YES’ ELSE ‘NO’
        END;
        BEGIN
        DBMS_OUTPUT.PUT_LINE(compatible);
        END;
        /
        .
        select message, status from pdb_plug_in_violations
        where type like ‘%ERR%’;
        .
    • create pluggable database pdb1 using ‘/stage/pdb1.xml’ file_name_convert=(‘/oradata/CDB1/pdb1’, ‘/oradata/CDB2/pdb1’);
    • alter pluggable database PDB1 open upgrade;
    • exit
  • On the command prompt:
    • cd $ORACLE_HOME/rdbms/admin 
    • $ORACLE_HOME/perl/bin/perl catctl.pl -c “PDB1” -l /home/oracle/upgrade catupgrd.sql
  • Back into SQL*Plus:
    • alter session set container=pdb1;
    • startup
    • @?/rdbms/admin/utlrp.sql
    • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups.sql
      (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)

Of course this technique will work also with more than one PDB at a given time. You’ll have to repeat the steps, and your upgrade call on the command line will look like this:

• • • $ORACLE_HOME/perl/bin/perl catctl.pl -c “PDB1, PDB2” -l /home/oracle/upgrade catupgrd.sql

Well, not really unplug+plug=upgraded 😉

Further Information

• Upgrade Testing – Online Clone a PDB to Oracle 19c
• Unplug a 12.2.0.1 PDB and plugin into 18c in the Cloud
• Upgrade Everything at Once – Multitenant Upgrade from Oracle 12.1 to 12.2
• Upgrade all PDBs – Everything at Once – Full CDB Upgrade
• Applying a PSU or BP to a Single-/Multitenant Environment
• PDB unplug/plug/patch with PSUs or BPs

-Mike