X

Archived Database Upgrade Blog

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

Mike Dietrich
Master Product Manager

*** 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 Time" approach 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!!! 

View Comments

Comments ( 13 )
Commenting is closed on this post.

  • Daniel Da Meda Tuesday, August 5, 2014

    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

  • guest Wednesday, September 17, 2014

    Oracle Multitenant page("http://www.oracle.com/technetwork/database/multitenant/overview/index.html") still shows upgrading database is just unplug and plug pdb. It is not talking about catupgrd.sql

    Section: Rapid Provisioning and Cloning Using SQL

  • Mike Wednesday, September 17, 2014

    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

  • guest Sunday, April 12, 2015

    Hi Mike,

    Is the same steps are applicable for windows upgrade from 12.1.0.1 to 12.1.0.2.

    Please confirm

    Thanks,
    Jagadish.

  • Mike Monday, April 13, 2015

    Hi,

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

    Mike

  • martin Thursday, May 7, 2015

    Hi Mike,

    typo

    alter sesstion set container=CDB$ROOT;

    should be

    alter session set container=CDB$ROOT;

    regards,
    Martin Eggens

  • Mike Tuesday, May 12, 2015

    Thanks Martin!

    Cheers
    Mike

  • Randall Wednesday, December 16, 2015

    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.

  • Mike Monday, December 21, 2015

    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

  • RogerB Friday, February 26, 2016

    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?

  • Mike Monday, February 29, 2016

    Yes, of course. As far as I remember DBUA will upgrade Everything-At-Once.

    Cheers
    Mike

  • Jason Friday, July 29, 2016

    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

  • Mike Dietrich Monday, August 1, 2016

    Jason,

    yes, there is one:
    Oracle Support Document 2102849.1 (Unplug/Plug on different PSU envs on 12.1.0.2) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2102849.1

    That hopefully answers your question :-)

    Cheers
    Mike

Oracle

Integrated Cloud Applications & Platform Services