{"id":9372,"date":"2016-11-20T15:39:11","date_gmt":"2016-11-20T14:39:11","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12cr2-how-long-to-upgrade-a-pdb\/"},"modified":"2016-11-20T15:39:11","modified_gmt":"2016-11-20T14:39:11","slug":"12cr2-how-long-to-upgrade-a-pdb","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12cr2-how-long-to-upgrade-a-pdb\/","title":{"rendered":"12cR2: How long to upgrade a PDB?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn the previous post<\/a> 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?
\n<\/p>\n

CDB$ROOT upgrade<\/h3>\n

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:<\/p>\n

Oracle Database 12.2 Post-Upgrade Status Tool           11-19-2016 14:04:51\n                             [CDB$ROOT]\n \nComponent                               Current         Version  Elapsed Time\nName                                    Status          Number   HH:MM:SS\n \nOracle Server                          UPGRADED      12.2.0.1.0  00:11:19\nJServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:04:29\nOracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00\nOracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:41\nOLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:14\nOracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:08\nOracle Label Security                  UPGRADED      12.2.0.1.0  00:00:05\nOracle XDK                             UPGRADED      12.2.0.1.0  00:01:01\nOracle Text                            UPGRADED      12.2.0.1.0  00:00:31\nOracle XML Database                    UPGRADED      12.2.0.1.0  00:01:33\nOracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:07\nOracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:22\nSpatial                                UPGRADED      12.2.0.1.0  00:04:46\nOracle Application Express                VALID     5.0.0.00.31  00:00:02\nOracle Database Vault                  UPGRADED      12.2.0.1.0  00:00:15\nFinal Actions                                                    00:01:50\nPost Upgrade                                                     00:00:12\n \nTotal Upgrade Time: 00:29:17 [CDB$ROOT]\n<\/code><\/pre>\n
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.
\nThose are the times we are used to. Since 12c<\/em> some operations are parallelized to make it faster than in previous versions.<\/p>\n
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.<\/p>\n
PDB upgrade<\/h3>\n
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.<\/p>\n
At UKOUG TECH16 in 12c Multitenant: Not a Revolution, Just an Evolution<\/a> 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.<\/p>\n
In 12.2 the command is easy:<\/p>\n
[oracle@HP122A tmp]$ $ORACLE_HOME\/bin\/dbupgrade -c PDB1<\/code><\/pre>\n
You can see that it runs the catctl.pl commands that we used in 12.1<\/p>\n
Start processing of PDB1\n[\/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]<\/code><\/pre>\n
Here is what will be run.<\/p>\n
Number of Cpus        = 2\nDatabase Name         = HP122A\nDataBase Version      = 12.2.0.1.0\nGenerated PDB Inclusion:[PDB1]\nCDB$ROOT  Open Mode = [OPEN]\nComponents in [PDB1]\n    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]\nNot Installed [EM MGW ODM RAC WK]<\/code><\/pre>\n
Summary is here:<\/p>\n
Oracle Database 12.2 Post-Upgrade Status Tool           11-19-2016 15:25:15\n                             [PDB1]\n \nComponent                               Current         Version  Elapsed Time\nName                                    Status          Number   HH:MM:SS\n \nOracle Server                          UPGRADED      12.2.0.1.0  00:08:59\nJServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:02:16\nOracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00\nOracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:27\nOLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:22\nOracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:07\nOracle Label Security                  UPGRADED      12.2.0.1.0  00:00:03\nOracle XDK                             UPGRADED      12.2.0.1.0  00:00:40\nOracle Text                            UPGRADED      12.2.0.1.0  00:00:18\nOracle XML Database                    UPGRADED      12.2.0.1.0  00:01:25\nOracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:03\nOracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:13\nOracle Application Express                VALID     5.0.0.00.31  00:00:02\nOracle Database Vault                  UPGRADED      12.2.0.1.0  00:00:40\nFinal Actions                                                    00:01:49\nPost Upgrade                                                     00:01:17\n \nTotal Upgrade Time: 00:23:55 [PDB1]\n \nDatabase time zone version is 18. It is older than current release time\nzone version 26. Time zone upgrade is needed using the DBMS_DST package.\n \nGrand Total Upgrade Time:    [0d:0h:25m:0s]\n<\/code><\/pre>\n
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.<\/p>\n
It is important to understand that the upgrade time depends on the components installed. Here is the percentage of time per component:<\/p>\n
\"CapturedbupgradePDB\"<\/a><\/p>\n
About the core of the database, what we know as catalog\/catproc, here is the detail showing which phases are run in parallel.
\nNote that the phase number is important because in 12.2 you can restart a failed upgrade from where it stopped.<\/p>\n
\n------------------------------------------------------\nPhases [0-117]         Start Time:[2016_11_19 15:00:37]\nContainer Lists Inclusion:[PDB1] Exclusion:[NONE]\n------------------------------------------------------\n***********   Executing Change Scripts   ***********\nSerial   Phase #:0    [PDB1] Files:1    Time: 36s\n***************   Catalog Core SQL   ***************\nSerial   Phase #:1    [PDB1] Files:5    Time: 39s\nRestart  Phase #:2    [PDB1] Files:1    Time: 1s\n***********   Catalog Tables and Views   ***********\nParallel Phase #:3    [PDB1] Files:19   Time: 23s\nRestart  Phase #:4    [PDB1] Files:1    Time: 0s\n*************   Catalog Final Scripts   ************\nSerial   Phase #:5    [PDB1] Files:6    Time: 15s\n*****************   Catproc Start   ****************\nSerial   Phase #:6    [PDB1] Files:1    Time: 12s\n*****************   Catproc Types   ****************\nSerial   Phase #:7    [PDB1] Files:2    Time: 9s\nRestart  Phase #:8    [PDB1] Files:1    Time: 0s\n****************   Catproc Tables   ****************\nParallel Phase #:9    [PDB1] Files:70   Time: 48s\nRestart  Phase #:10   [PDB1] Files:1    Time: 1s\n*************   Catproc Package Specs   ************\nSerial   Phase #:11   [PDB1] Files:1    Time: 12s\nRestart  Phase #:12   [PDB1] Files:1    Time: 1s\n**************   Catproc Procedures   **************\nParallel Phase #:13   [PDB1] Files:97   Time: 8s\nRestart  Phase #:14   [PDB1] Files:1    Time: 1s\nParallel Phase #:15   [PDB1] Files:118  Time: 11s\nRestart  Phase #:16   [PDB1] Files:1    Time: 1s\nSerial   Phase #:17   [PDB1] Files:13   Time: 3s\nRestart  Phase #:18   [PDB1] Files:1    Time: 1s\n*****************   Catproc Views   ****************\nParallel Phase #:19   [PDB1] Files:33   Time: 25s\nRestart  Phase #:20   [PDB1] Files:1    Time: 0s\nSerial   Phase #:21   [PDB1] Files:3    Time: 8s\nRestart  Phase #:22   [PDB1] Files:1    Time: 1s\nParallel Phase #:23   [PDB1] Files:24   Time: 82s\nRestart  Phase #:24   [PDB1] Files:1    Time: 1s\nParallel Phase #:25   [PDB1] Files:11   Time: 42s\nRestart  Phase #:26   [PDB1] Files:1    Time: 0s\nSerial   Phase #:27   [PDB1] Files:1    Time: 0s\nSerial   Phase #:28   [PDB1] Files:3    Time: 5s\nSerial   Phase #:29   [PDB1] Files:1    Time: 0s\nRestart  Phase #:30   [PDB1] Files:1    Time: 0s\n***************   Catproc CDB Views   **************\nSerial   Phase #:31   [PDB1] Files:1    Time: 2s\nRestart  Phase #:32   [PDB1] Files:1    Time: 1s\nSerial   Phase #:34   [PDB1] Files:1    Time: 0s\n*****************   Catproc PLBs   *****************\nSerial   Phase #:35   [PDB1] Files:283  Time: 17s\nSerial   Phase #:36   [PDB1] Files:1    Time: 0s\nRestart  Phase #:37   [PDB1] Files:1    Time: 0s\nSerial   Phase #:38   [PDB1] Files:1    Time: 3s\nRestart  Phase #:39   [PDB1] Files:1    Time: 1s\n***************   Catproc DataPump   ***************\nSerial   Phase #:40   [PDB1] Files:3    Time: 49s\nRestart  Phase #:41   [PDB1] Files:1    Time: 1s\n******************   Catproc SQL   *****************\nParallel Phase #:42   [PDB1] Files:13   Time: 51s\nRestart  Phase #:43   [PDB1] Files:1    Time: 0s\nParallel Phase #:44   [PDB1] Files:12   Time: 8s\nRestart  Phase #:45   [PDB1] Files:1    Time: 1s\nParallel Phase #:46   [PDB1] Files:2    Time: 2s\nRestart  Phase #:47   [PDB1] Files:1    Time: 1s\n*************   Final Catproc scripts   ************\nSerial   Phase #:48   [PDB1] Files:1    Time: 5s\nRestart  Phase #:49   [PDB1] Files:1    Time: 1s\n**************   Final RDBMS scripts   *************\nSerial   Phase #:50   [PDB1] Files:1    Time: 16s\n<\/code><\/pre>\n
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:<\/p>\n
\n*****************   Upgrading SDO   ****************\nRestart  Phase #:81   [PDB1] Files:1    Time: 1s\nSerial   Phase #:83   [PDB1] Files:1    Time: 23s\nSerial   Phase #:84   [PDB1] Files:1    Time: 4s\nRestart  Phase #:85   [PDB1] Files:1    Time: 1s\nSerial   Phase #:86   [PDB1] Files:1    Time: 5s\nRestart  Phase #:87   [PDB1] Files:1    Time: 0s\nParallel Phase #:88   [PDB1] Files:3    Time: 110s\nRestart  Phase #:89   [PDB1] Files:1    Time: 0s\nSerial   Phase #:90   [PDB1] Files:1    Time: 4s\nRestart  Phase #:91   [PDB1] Files:1    Time: 1s\nSerial   Phase #:92   [PDB1] Files:1    Time: 4s\nRestart  Phase #:93   [PDB1] Files:1    Time: 0s\nParallel Phase #:94   [PDB1] Files:4    Time: 30s\nRestart  Phase #:95   [PDB1] Files:1    Time: 0s\nSerial   Phase #:96   [PDB1] Files:1    Time: 3s\nRestart  Phase #:97   [PDB1] Files:1    Time: 1s\nSerial   Phase #:98   [PDB1] Files:1    Time: 22s\nRestart  Phase #:99   [PDB1] Files:1    Time: 0s\nSerial   Phase #:100  [PDB1] Files:1    Time: 3s\nRestart  Phase #:101  [PDB1] Files:1    Time: 1s\nSerial   Phase #:102  [PDB1] Files:1    Time: 2s\nRestart  Phase #:103  [PDB1] Files:1    Time: 1s\n<\/code><\/pre>\n
So what?<\/h3>\n
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.
\nIn summary:<\/p>\n