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 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.
      • 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!!! 

Comments:

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

Posted by Daniel Da Meda on August 05, 2014 at 09:16 PM CEST #

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

Posted by guest on September 17, 2014 at 11:24 AM CEST #

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

Posted by Mike on September 17, 2014 at 11:52 AM CEST #

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.

Posted by guest on April 12, 2015 at 08:31 PM CEST #

Hi,

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

Mike

Posted by Mike on April 13, 2015 at 07:15 PM CEST #

Hi Mike,

typo

alter sesstion set container=CDB$ROOT;

should be

alter session set container=CDB$ROOT;

regards,
Martin Eggens

Posted by martin on May 07, 2015 at 04:31 PM CEST #

Thanks Martin!

Cheers
Mike

Posted by Mike on May 12, 2015 at 04:50 PM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« June 2015
SunMonTueWedThuFriSat
 
1
3
4
5
6
7
8
10
11
12
13
14
15
18
19
20
21
24
25
26
27
28
30    
       
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers