Tuesday Aug 05, 2014

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

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 and plug it into an Oracle 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 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 sesstion set container=CDB$ROOT; 
      • alter pluggable database PDB1 close;
      • alter pluggable database PDB1 unplug into '/stage/pdb1.xml';
      • exit
  • In CDB2 environment - e.g. Oracle
    • 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 "" 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
            pdb_descr_file => '/stage/pdb1.xml',
            pdb_name => 'PDB1')
            WHEN TRUE THEN 'YES' ELSE 'NO'

          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 ;-)


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

Tuesday May 13, 2014

More than one PDB in the same directory?

Can you create more than one pluggable database (PDB) within the same directory?
And how does the file naming work? Considering the fact each PDB's SYSTEM tablespace will be named system01.dbf by default the question is not trivial. 

This question got asked by a customer during one of the workshops in Switzerland last week. And the solution is straight forward. Thanks to Roy for trying it out yesterday at 170 km/h on our way back from Stuttgart :-)

Thanks :-)


Additional information:

Within ASM with OMF the file structure looks like this:

 1  select con_id, substr(file_name,1,90),tablespace_name from cdb_data_files
  2* order by 1

    CON_ID SUBSTR(FILE_NAME,1,90)                                                           TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- ---------------
         1 +DA1/CDBUPGR/DATAFILE/system.394.845632641                                       SYSTEM
         1 +DA1/CDBUPGR/DATAFILE/users.475.845632685                                        USERS
         1 +DA1/CDBUPGR/DATAFILE/undotbs4.448.845632683                                     UNDOTBS4
         1 +DA1/CDBUPGR/DATAFILE/sysaux.392.845632651                                       SYSAUX
         1 +DA1/CDBUPGR/DATAFILE/undotbs2.393.845632679                                     UNDOTBS2
         1 +DA1/CDBUPGR/DATAFILE/undotbs1.471.845632657                                     UNDOTBS1
         1 +DA1/CDBUPGR/DATAFILE/undotbs3.478.845632681                                     UNDOTBS3
         2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/sysaux.472.845632655      SYSAUX
         2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/system.398.845632647      SYSTEM
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/system.493.845643325      SYSTEM
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/sysaux.468.845643325      SYSAUX
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/soets.452.845643325       SOETS
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/system.491.845643937      SYSTEM
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/sysaux.488.845643937      SYSAUX
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/soets.484.845643937       SOETS
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/system.485.845644149      SYSTEM
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/sysaux.490.845644149      SYSAUX
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/soets.487.845644149       SOETS
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/system.486.845644363      SYSTEM
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/sysaux.483.845644363      SYSAUX
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/soets.481.845644363       SOETS
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/system.453.845644575      SYSTEM
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/sysaux.482.845644575      SYSAUX
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/soets.467.845644575       SOETS
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/system.465.845644785      SYSTEM
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/sysaux.455.845644785      SYSAUX
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/soets.479.845644785       SOETS
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/system.464.845645805      SYSTEM
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/sysaux.500.845645805      SYSAUX
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/soets.498.845645805       SOETS
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/system.499.845646023      SYSTEM
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/sysaux.504.845646023      SYSAUX
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/soets.502.845646023       SOETS
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/system.503.845646233      SYSTEM
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/sysaux.508.845646233      SYSAUX
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/soets.506.845646233       SOETS

Friday Nov 29, 2013

Starting up 252 PDBs in Oracle Multitenant

What happens when you start up 252 PDBs (Pluggable Databases) with the Oracle Multitenant Option for the first time?

Interesting question, isn't it? The expectation would be that this will happen within seconds as the SGA and therefore the shared memory segments are already allocated from within the CDB$ROOT (Container Database). But ...

The following happens:
It takes minutes ... hours .... In my tiny lab environment with just as little as 20 PDBs due to space constraints it takes over 30 minutes to startup 21 PDBs. Takashi Ikeda from Fujitsu Hokoriku Systems who did a great demo with the new Fujitsu M10 servers at OOW this year told me that it took over two hours to start up 252 PDBs for the first time.
Why is that?

Let's have a closer look into the alert.log during startup. After issueing the command:


I'd expect all PDBs to get started. With an EXCEPT PDB1, PDB2, PDB3 clause I could exclude some PDBs from this action. Now a look into the alert.log shows a very promising message:

I'm just wondering about the opening sequence of PDBs. I'd expect 1 ... 2 ... 3 ... 4 ... ... ... 21. But the "order" is 3 ... 10 ... 16 ... 15 ... 20 ... 21 etc. telling me that the Resource Manager is not active (which is a must if you take Multitenant serious).
OK, for that strange order there's an explanation:
The open action gets distrubuted to slaves so PDBs may opened in a random order.
Fuuny thing apart from that: I can access the PDB but the system seems to be really under heavy pressure. CPUs are all at 100%. What the heck is going on here in the background?

Well, XDB needs to be installed (at least that is what the message says). Strange, isn't it, as the PDB$SEED has XDB in it and all my PDBs got provisioned from it. The awkward thing here is that the XDB messages appear over 20 minutes AFTER the PDBs signaled the Opening message into the alert.log (see the time stamps above).

Now after exchanging a few emails with some very helpful people in development there's an explanation for the XDB messages as well. Actually it doesn't get really installed but the SGA needs to be initialized for XDB. And I'm guessing that this action takes a lot of resources plus may cause contention when many PDBs get opened at the same time. And there's optimization work going on right now meaning that a problem with port initialization within the PDB will get fixed in a future patch set. So this issue with the very long startups of PDBs because of XDB should disappear in most likely :-)

Finally it took another while to get the PDBs really into OPEN mode. Even though they were showing OPEN before already in V$PDBS. But as the CPUs all went to 100% as XDB got installed/initiallized at more or less the same time in all PDBs you really can't do anything.

Finally ...

... all PDBs got opened and the command ALTER PLUGGABLE DATABASE ALL OPEN returned completed.

The good news:
It takes just this long during the initial startup of a newly provisioned PDB. And you may see this issue only when you try to open many PDBs at the same time. But have a close look into your alert.log if you'll spot the message after creating a fresh PDB.

And btw, just for the records: I was using Oracle Database with Oct 2013 PSU in it.


Wednesday Jul 17, 2013

Oracle Multitenant (Pluggable Database) White Paper

The feature we did introduce for a while now as Pluggable Database got named officially Oracle Multitenant - and if you'd like to read more about this feature the newly release White Paper may give you a good overview:




Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

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

Contact me either via XING or LinkedIn


« March 2015
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers