By Franck Pachot

.
In the previous post I described how simple it is to unplug a PDB and plug it into a newer version CDB. One goal of dictionary separation in the multitenant architecture is to keep system objects on CDB$ROOT only. Knowing that an upgrade does not touch the application metadata and data, does this make PDB upgrade fast as a simple refresh of metadata links?

CDB$ROOT upgrade

As a point of comparison I’ve run an upgrade on an empty CDB from 12.1.0.2 to 12.2.0.1 and here is the summary:

Oracle Database 12.2 Post-Upgrade Status Tool           11-19-2016 14:04:51
                             [CDB$ROOT]
 
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
 
Oracle Server                          UPGRADED      12.2.0.1.0  00:11:19
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:04:29
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:41
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:14
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:08
Oracle Label Security                  UPGRADED      12.2.0.1.0  00:00:05
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:01
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:31
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:33
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:07
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:22
Spatial                                UPGRADED      12.2.0.1.0  00:04:46
Oracle Application Express                VALID     5.0.0.00.31  00:00:02
Oracle Database Vault                  UPGRADED      12.2.0.1.0  00:00:15
Final Actions                                                    00:01:50
Post Upgrade                                                     00:00:12
 
Total Upgrade Time: 00:29:17 [CDB$ROOT]

This was running on a Oracle Public Cloud DBaaS with two OCPUs which means four threads. It’s about 30 minutes to upgrade the system dictionary and all components.
Those are the times we are used to. Since 12c some operations are parallelized to make it faster than in previous versions.

The more components you install, the longer it takes. Even if it is recommended to install all components in a CDB in case a PDB needs it, you may think about this.

PDB upgrade

When you plug a PDB, you should not have all this work to do. You can expect that the metadata links and data links just work, now pointing to the new version. At most, a quick check or refresh may be necessary to ensure that object types did not change.

At UKOUG TECH16 in 12c Multitenant: Not a Revolution, Just an Evolution I demo how those links work internally and I show that running a full CATUPGRD.SQL on each container is not required to be run for each object. However, the DBUPGRADE script runs it. Let’s see if it is optimized for pluggable databases.

In 12.2 the command is easy:

[[email protected] tmp]$ $ORACLE_HOME/bin/dbupgrade -c PDB1

You can see that it runs the catctl.pl commands that we used in 12.1

Start processing of PDB1
[/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl -c 'PDB1' -I -i pdb1 -n 2 -l /home/oracle /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catupgrd.sql]

Here is what will be run.

Number of Cpus        = 2
Database Name         = HP122A
DataBase Version      = 12.2.0.1.0
Generated PDB Inclusion:[PDB1]
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB1]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

Summary is here:

Oracle Database 12.2 Post-Upgrade Status Tool           11-19-2016 15:25:15
                             [PDB1]
 
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
 
Oracle Server                          UPGRADED      12.2.0.1.0  00:08:59
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:02:16
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:27
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:22
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:07
Oracle Label Security                  UPGRADED      12.2.0.1.0  00:00:03
Oracle XDK                             UPGRADED      12.2.0.1.0  00:00:40
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:18
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:25
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:03
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:13
Oracle Application Express                VALID     5.0.0.00.31  00:00:02
Oracle Database Vault                  UPGRADED      12.2.0.1.0  00:00:40
Final Actions                                                    00:01:49
Post Upgrade                                                     00:01:17
 
Total Upgrade Time: 00:23:55 [PDB1]
 
Database time zone version is 18. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.
 
Grand Total Upgrade Time:    [0d:0h:25m:0s]

When you compare with a CDB$ROOT upgrade the gain is very small. We saved 25% of Oracle Server time. JVM and XDK was x2 faster. But finally, that’s only 5 minutes.

It is important to understand that the upgrade time depends on the components installed. Here is the percentage of time per component:

CapturedbupgradePDB

About the core of the database, what we know as catalog/catproc, here is the detail showing which phases are run in parallel.
Note that the phase number is important because in 12.2 you can restart a failed upgrade from where it stopped.


------------------------------------------------------
Phases [0-117]         Start Time:[2016_11_19 15:00:37]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [PDB1] Files:1    Time: 36s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [PDB1] Files:5    Time: 39s
Restart  Phase #:2    [PDB1] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [PDB1] Files:19   Time: 23s
Restart  Phase #:4    [PDB1] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [PDB1] Files:6    Time: 15s
*****************   Catproc Start   ****************
Serial   Phase #:6    [PDB1] Files:1    Time: 12s
*****************   Catproc Types   ****************
Serial   Phase #:7    [PDB1] Files:2    Time: 9s
Restart  Phase #:8    [PDB1] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [PDB1] Files:70   Time: 48s
Restart  Phase #:10   [PDB1] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [PDB1] Files:1    Time: 12s
Restart  Phase #:12   [PDB1] Files:1    Time: 1s
**************   Catproc Procedures   **************
Parallel Phase #:13   [PDB1] Files:97   Time: 8s
Restart  Phase #:14   [PDB1] Files:1    Time: 1s
Parallel Phase #:15   [PDB1] Files:118  Time: 11s
Restart  Phase #:16   [PDB1] Files:1    Time: 1s
Serial   Phase #:17   [PDB1] Files:13   Time: 3s
Restart  Phase #:18   [PDB1] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [PDB1] Files:33   Time: 25s
Restart  Phase #:20   [PDB1] Files:1    Time: 0s
Serial   Phase #:21   [PDB1] Files:3    Time: 8s
Restart  Phase #:22   [PDB1] Files:1    Time: 1s
Parallel Phase #:23   [PDB1] Files:24   Time: 82s
Restart  Phase #:24   [PDB1] Files:1    Time: 1s
Parallel Phase #:25   [PDB1] Files:11   Time: 42s
Restart  Phase #:26   [PDB1] Files:1    Time: 0s
Serial   Phase #:27   [PDB1] Files:1    Time: 0s
Serial   Phase #:28   [PDB1] Files:3    Time: 5s
Serial   Phase #:29   [PDB1] Files:1    Time: 0s
Restart  Phase #:30   [PDB1] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [PDB1] Files:1    Time: 2s
Restart  Phase #:32   [PDB1] Files:1    Time: 1s
Serial   Phase #:34   [PDB1] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [PDB1] Files:283  Time: 17s
Serial   Phase #:36   [PDB1] Files:1    Time: 0s
Restart  Phase #:37   [PDB1] Files:1    Time: 0s
Serial   Phase #:38   [PDB1] Files:1    Time: 3s
Restart  Phase #:39   [PDB1] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB1] Files:3    Time: 49s
Restart  Phase #:41   [PDB1] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB1] Files:13   Time: 51s
Restart  Phase #:43   [PDB1] Files:1    Time: 0s
Parallel Phase #:44   [PDB1] Files:12   Time: 8s
Restart  Phase #:45   [PDB1] Files:1    Time: 1s
Parallel Phase #:46   [PDB1] Files:2    Time: 2s
Restart  Phase #:47   [PDB1] Files:1    Time: 1s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [PDB1] Files:1    Time: 5s
Restart  Phase #:49   [PDB1] Files:1    Time: 1s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [PDB1] Files:1    Time: 16s

In the summary when we compare with a CDB$ROOT upgrade we don’t see the Spatial part that took 4 minutes but we see it in the detail:


*****************   Upgrading SDO   ****************
Restart  Phase #:81   [PDB1] Files:1    Time: 1s
Serial   Phase #:83   [PDB1] Files:1    Time: 23s
Serial   Phase #:84   [PDB1] Files:1    Time: 4s
Restart  Phase #:85   [PDB1] Files:1    Time: 1s
Serial   Phase #:86   [PDB1] Files:1    Time: 5s
Restart  Phase #:87   [PDB1] Files:1    Time: 0s
Parallel Phase #:88   [PDB1] Files:3    Time: 110s
Restart  Phase #:89   [PDB1] Files:1    Time: 0s
Serial   Phase #:90   [PDB1] Files:1    Time: 4s
Restart  Phase #:91   [PDB1] Files:1    Time: 1s
Serial   Phase #:92   [PDB1] Files:1    Time: 4s
Restart  Phase #:93   [PDB1] Files:1    Time: 0s
Parallel Phase #:94   [PDB1] Files:4    Time: 30s
Restart  Phase #:95   [PDB1] Files:1    Time: 0s
Serial   Phase #:96   [PDB1] Files:1    Time: 3s
Restart  Phase #:97   [PDB1] Files:1    Time: 1s
Serial   Phase #:98   [PDB1] Files:1    Time: 22s
Restart  Phase #:99   [PDB1] Files:1    Time: 0s
Serial   Phase #:100  [PDB1] Files:1    Time: 3s
Restart  Phase #:101  [PDB1] Files:1    Time: 1s
Serial   Phase #:102  [PDB1] Files:1    Time: 2s
Restart  Phase #:103  [PDB1] Files:1    Time: 1s

So what?

From what we see, the multitenant architecture, with consolidation of the system directory in only one place – the CDB$ROOT – we have no gain in upgrade. In the current implementation (12.2.0.1) the same work is done on all containers, with only minimal optimization for pluggable databases where we have metadata links instead of full object metadata.
In summary:

  • Upgrading by plug-in or remote clone is faster than upgrading the whole CDB because CDB has more containers, such as PDB$SEED
  • But upgrading a single PDB, whatever the method is, is not faster than upgrading a non-CDB

I’m talking about upgrade of the container here. Transportable tablespaces/database is a different thing.

More about the Multitenant internals, dictionary separation, metadata links and data links (was called object links in 12.1) at UKOUG TECH 2016 conference next month.

CaptureUpgradePres