Upgrade PDBs – One at a Time (unplug/plug)

*** I have added an important change on May 26, 2015 ***
***      Please see below marked in YELLOW           ***
********************************************************

Basically there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will refer to the “One at a Timeapproach and describe the 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 😉

-Mike

PS: I did add a few pieces of information based on the excellent feedback given to me by Frank Kobylanski from the MAA Team – cheers, Frank!!! 

18 thoughts on “Upgrade PDBs – One at a Time (unplug/plug)

  1. Hi there,

    Thanks for the nice post! Indeed, I just found that out a few days ago and I created a post about it.

    For anyone interested in more information about the cross-version PDB upgrade. Please check out my post on upgrading a container database from 12.1.0.1 to 12.1.0.2 and performing a cross-version plugin of a PDB. It is a step-by-step post that may be helpful to some.

    Here is the link:

    http://thatoracledude.blogspot.com/2014/07/database-upgrade-to-12102-and-cross.html

    Regards

    Daniel Da Meda

  2. Well … you are right. Let me forward your comment to the Multitenant PMs and see what they reply. In case you’d like to get the answer please feel free to drop me an email.

    Thanks, good catch 🙂

    Mike

  3. Hi,

    yes, the same steps will apply to Windows as well. Only exception – slashes need to be exchanged with backslashes 😉

    Mike

  4. Hi Mike,

    typo

    alter sesstion set container=CDB$ROOT;

    should be

    alter session set container=CDB$ROOT;

    regards,
    Martin Eggens

  5. Assuming CDB1 and CDB2 are replicated to the same cluster. How does this method impact the physical standby? With a normal PDB plugin requiring manual file copies, I would assume there will be steps needed on the standby side.

  6. Very good point:
    Nothing happens automatically here – you’ll have to copy the files manually on the standby site as well. The SQL actions will be replicated but the file copy opereration done manually on the PROD host need to be done on the standby as well – just as if you’d relocate a regular non-CDB production database to somewhere else.

    Cheers
    Mike

  7. Can you just upgrade the container database with DBUA (while your pluggable databases are plugged in and up) and it will upgrade your pluggable databases as well as the container?

  8. Hi Mike,

    We are planning to upgrade from 12.1.0.2.1 to 12.1.0.2.5. The plan is to create new 12.1.0.2.5 oracle home and gradually moving PDB from 12.1.0.2.1 to 12.1.0.2.5. Should I follow the same steps? Is there any document for what we want to do?

    Thanks,
    Jason

  9. Pingback: Upgrade PDBs – Everything At Once (Full CDB Upgrade) | Upgrade your Database - NOW!

  10. Pingback: Recent News about Pluggable Databases – Oracle Multitenant | Upgrade your Database - NOW!

  11. Hi Mike

    Is it possible to Patch a Dataguard-Environment with the “One at a Time (unplug/plug)”-way. Unplug/plug Primary & Standby PDB to an other CDB on the same Cluster?

    Thanks.
    Reto

    • Reto,

      unfortunately it is not that straight forward 🙁
      I won’t have time today but I’ll try to post something useful next week. It’s a bit more stuff to explain. If you need an immediate solution please open an SR and Support may assist you. The general problem is that – once you unplug/plug you’ll have to repeat that action on your NEW standby as well. There’s a workaround to propagate your unplug/plugged PDB forward – but this will require the Jan 2017 BP.

      Cheers
      Mike

  12. Pingback: Upgrading the PDB$SEED takes always longer than any other container

Leave a Reply

Your email address will not be published. Required fields are marked *