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:
[oracle@HP122A tmp]$ $ORACLE_HOME/bin/dbupgrade -c PDB1You 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:
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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)