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

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 #

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.

Posted by Randall on December 16, 2015 at 02:51 PM CET #

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

Posted by Mike on December 21, 2015 at 01:29 PM CET #

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?

Posted by RogerB on February 26, 2016 at 02:50 AM CET #

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

Cheers
Mike

Posted by Mike on February 29, 2016 at 09:58 AM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« June 2016
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
17
18
19
21
22
23
24
25
26
27
28
29
30
  
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers