Can I restart a failed Multitenant Upgrade as well?

A while back I did blog about the new -R option of the parallel upgrade tool catctl.pl in Oracle Database 12.2.

Restarting a failed Database Upgrade with catctl.pl

And in case you will do a real Multitenant upgrade and fail – as it happened to me today due to “no space left on device” (no audit files could be written anymore) I tried the -R option as well based on Joe’s (our lead catctl.pl developer) recommendation:

$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -R -l /home/oracle/mike2 catupgrd.sql

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/mike2
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 6
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 1
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/mike2]

catcon: ALL catcon-related output will be written to [/home/oracle/mike2/catupgrd_catcon_10640.lst]
catcon: See [/home/oracle/mike2/catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle/mike2/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 2
Database Name         = CDB1
DataBase Version      = 12.2.0.1.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 6
Concurrent PDB Upgrades               = 3
PDB$SEED Open Mode = [READ ONLY] NO UPGRADE WILL BE PERFORMED
PDB1 Open Mode = [MOUNTED] NO UPGRADE WILL BE PERFORMED
PDB2 Open Mode = [MOUNTED] NO UPGRADE WILL BE PERFORMED
PDB4 Open Mode = [READ WRITE] NO UPGRADE WILL BE PERFORMED
Generated PDB Inclusion:[PDB3 PDB5]
Components in [CDB$ROOT]
    Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

** Database CDB$ROOT has already been upgraded successfully. **
    Time: 2s

Start processing of PDB3
[/u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -n 2 -R -l /home/oracle/mike2 -I -i pdb3 -c 'PDB3' catupgrd.sql]

Start processing of PDB5
[/u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -n 2 -R -l /home/oracle/mike2 -I -i pdb5 -c 'PDB5' catupgrd.sql]

Argument list for [catctl.pl]
Run in                c = PDB3
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb3
Child Process         I = 1
Log Dir               l = /home/oracle/mike2
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 1
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]



Argument list for [catctl.pl]
Run in                c = PDB5
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb5
Child Process         I = 1
Log Dir               l = /home/oracle/mike2
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 1
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/mike2]

/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/mike2]

catcon: ALL catcon-related output will be written to [/home/oracle/mike2/catupgrdpdb5_catcon_10837.lst]
catcon: See [/home/oracle/mike2/catupgrdpdb5*.log] files for output generated by scripts
catcon: See [/home/oracle/mike2/catupgrdpdb5_*.lst] files for spool files, if any
catcon: ALL catcon-related output will be written to [/home/oracle/mike2/catupgrdpdb3_catcon_10835.lst]
catcon: See [/home/oracle/mike2/catupgrdpdb3*.log] files for output generated by scripts
catcon: See [/home/oracle/mike2/catupgrdpdb3_*.lst] files for spool files, if any

Number of Cpus        = 2

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

*******Upgrade being restarted on database PDB5 from failed phase 40*******

------------------------------------------------------
Phases [40-115]         Start Time:[2017_05_09 15:25:29]
Container Lists Inclusion:[PDB5] Exclusion:[NONE]
------------------------------------------------------

*******Upgrade being restarted on database PDB3 from failed phase 109*******

------------------------------------------------------
Phases [109-115]         Start Time:[2017_05_09 15:25:29]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
   Time: 2s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB5] Files:3    Time: 2s
*******************   Migration   ******************
Serial   Phase #:109  [PDB3] Files:1    

And yes, it works!

The upgrade will be restarted for PDB3 and PDB5 exactly in the failed phased from the first run. Upgrades for CDB$ROOT, PDB$SEED, PDB1, PDB2 and PDB4 are completed already and will be skipped.

–Mike

Upgrade Everything at Once – Multitenant Upgrade from Oracle 12.1 to 12.2

I did blog about this topic a while back.

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

Theory is nice but it’s a big difference when you do it the first time with a non-lab deployment at a customer with a real Multitenant database with almost 100 PDBs.Then you learn that neither the documentation nor my previous blog post contain all the necessary steps.

My test setup

I have a simple deployment of just 5 PDBs. But it will showcase with my limited compute capabilities how the process should work.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

Run preupgrade,jar

First of all download the newest version of preupgrade.jar from MOS Note:884522.1 and copy it to your system. Execute it with all PDBs in OPEN mode. It will generate consolidated logs and sql files. The files will be written into $ORACLE_BASE/cfgtoollogs/CDB1/preupgrade.

The output does look like:

$ java -jar /home/oracle/preupgrade.jar TEXT TERMINAL


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle XML Database                    [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups_CDB_ROOT.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             550 MB  ENABLED       647 MB  None
     SYSTEM                             700 MB  ENABLED       758 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                           475 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.

   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
     sga_target*                               2260729856

     * These minimum memory/pool sizes are recommended for the upgrade process

  RECOMMENDED ACTIONS
  ===================
   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 12.1.0.2
     Oracle Database SQL Tuning Guide.

=============
AFTER UPGRADE
=============

  Run /postupgrade_fixups_CDB_ROOT.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 18 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.
     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle XML Database                    [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups_PDB_SEED.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             355 MB  ENABLED       542 MB  None
     SYSTEM                             210 MB  ENABLED       561 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.

  RECOMMENDED ACTIONS
  ===================
   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 12.1.0.2
     Oracle Database SQL Tuning Guide.

=============
AFTER UPGRADE
=============

  Run /postupgrade_fixups_PDB_SEED.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 18 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:
       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  PDB1
       Container ID:  3
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle XML Database                    [to be upgraded]  VALID

...

I cut off here as the information will repeated as my PDBs are all generated from the PDB$SEED right now. But it’s important to understand that the log and the sql files are consolidated together.

Important:
When you see the tag (AUTOFIXUP) that means “we’ll take care”. The action will be execute by either the preupgrade_fixups.sql or the postupgrade_fixups.sql. No action required from your side.

In $ORACLE_BASE/cfgtoollos/CDB1/preupgrade you’ll find also the files for each container in case you will need them.

-rw-r--r--. 1 oracle dba   3246 May  9 11:50 preupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle dba   5534 May  9 11:50 postupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle dba   4346 May  9 11:50 preupgrade_CDB_ROOT.log
-rw-r--r--. 1 oracle dba   3246 May  9 11:50 preupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle dba   5534 May  9 11:50 postupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle dba   3937 May  9 11:50 preupgrade_PDB_SEED.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB3.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB3.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB3.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB2.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB2.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB2.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB1.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB1.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB1.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB4.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB4.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB4.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB5.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB5.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB5.log
-rw-r--r--. 1 oracle dba  27913 May  9 11:50 preupgrade.log
-rw-r--r--. 1 oracle dba  12739 May  9 11:50 preupgrade_fixups.sql
-rw-r--r--. 1 oracle dba  19669 May  9 11:50 postupgrade_fixups.sql

Please don’t delete any of the files named written by preupgrade.jar as they all are necessary to progress the actions later on.

Pre Upgrade

From the above output the only real action I will have to do is to change the value of SGA_TARGET and then execute the preupgrade_fixups.sql There’s no need to execute it by each PDB as the preupgrade_fixups.sql is a consolidated file having all actions for each PDB.

SQL> alter system set sga_target=2500M scope=spfile;
System altered.

To execute preupgrade_fixups.sql now catcon.pl must be used – and (even though this is not documented in detail at the moment) you should leverage your compute power by specifying a parallel degree matching your CPU_COUNT. In my example I use -n 4 to have 4 workers execute the task.

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
   -n 4 \
   -d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade \
   -l /home/oracle/mike \
   -b preupgrade_fixups \
  preupgrade_fixups.sql 
catcon: ALL catcon-related output will be written to /home/oracle/mike/preupgrade_fixups_catcon_22960.lst
catcon: See /home/oracle/mike/preupgrade_fixups*.log files for output generated by scripts
catcon: See /home/oracle/mike/preupgrade_fixups_*.lst files for spool files, if any
catcon.pl: completed successfull

Once this step has been completed successfully, I will SHUTDOWN IMMEDIATE the database, copy the spfile over, generate a new password file in the destination Oracle home and then progress the upgrade.

Multitenant database upgrade

Once I have copied the spfile and created a password file I can startup my database in UPGRADE mode and startup all PDBs in UPGRADE mode as well.

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 13:37:28 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size		    8796240 bytes
Variable Size		  520095664 bytes
Database Buffers	 2097152000 bytes
Redo Buffers		    7979008 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MIGRATE    YES
	 3 PDB1 			  MIGRATE    YES
	 4 PDB2 			  MIGRATE    YES
	 5 PDB3 			  MIGRATE    YES
	 6 PDB4 			  MIGRATE    YES
	 7 PDB5 			  MIGRATE    YES

SQL> exit

Then I will kick-off the upgrade. You can leverage again the full CPU power of your system. In my case, as I have only two CPUs I will overload it a bit risking contention just to demonstrate some of the capabilities of catctl.pl.

The CDB$ROOT will be upgraded always first. With the -M option you can decide that it will stay for the entire upgrade, until the last PDB has been finished upgrading, in UPGRADE mode – or you let it on the default as I do meaning that the CDB$ROOT and each PDB become available as soon as they are upgraded. The -M option is suppose to generate a faster overall upgrade as it lowers potential contention in the dictionary of the CDB$ROOT. But you will see only a difference when you bring load to the database while PDBs get upgraded.

As I defined -n 6 here my CDB$ROOT will be upgraded by 6 workers. Once the PDBs get progressed, then 2 workers will take care on each PDB. And as -n 6 is set, 6 workers all together will be up meaning a maximum of 3 PDBs can be processed at one time.

Once a PDB has been upgraded the workers go on with the next accessible PDB based on the CON_ID. You can define also priority lists. Please see the documentation for further information.

$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle/mike catupgrd.sql

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/mike
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 6
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

Analyzing file /u01/app/oracle/product/12.2.0.1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/mike]

catcon: ALL catcon-related output will be written to [/home/oracle/mike/catupgrd_catcon_9755.lst]
catcon: See [/home/oracle/mike/catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle/mike/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 2
Database Name         = CDB1
DataBase Version      = 12.1.0.2.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 6
Concurrent PDB Upgrades               = 3
Generated PDB Inclusion:[PDB$SEED PDB1 PDB2 PDB3 PDB4 PDB5]
Components in [CDB$ROOT]
    Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_05_09 13:40:30]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [CDB$ROOT] Files:1    Time: 33s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [CDB$ROOT] Files:5    Time: 55s
Restart  Phase #:2    [CDB$ROOT] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [CDB$ROOT] Files:19

While the upgrade is running you won’t see a correct ordered output anymore as there’s no guarantee that each PDB will get upgrade at time X in phase Y. Especially when you have many PDBs of different sizes and contents you may see PDB1 in phase 49 whereas PDB2 is already in phase 109.

***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB1] Files:3    Time: 5s
Restart  Phase #:39   [PDB$SEED] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB$SEED] Files:3    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB2] Files:3    Time: 103s
Restart  Phase #:41   [PDB2] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB2] Files:13    Time: 104s
Restart  Phase #:41   [PDB$SEED] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB$SEED] Files:13    Time: 105s
Restart  Phase #:41   [PDB1] Files:1    Time: 1s
******************   Catproc SQL   *****************

After the upgrade is finished, the consolidated upg_summary.log will tell you all the details about duration of each upgrade for each PDB. You may recognize that the PDB$SEED will take always longer than any other upgrade. This is because the PDB$SEED gets recompiled as part of the upgrade already as it is untouchable. All other containers will need to get recompiled after the upgrade is finished.

Post Upgrade

Final steps are now the recompilation and the postupgrade_fixups.sql.

The status of my database after upgrade and before recompilation:

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 16:11:51 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED
	 4 PDB2 			  MOUNTED
	 5 PDB3 			  MOUNTED
	 6 PDB4 			  MOUNTED
	 7 PDB5 			  MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

That does look good at first sight. But it isn’t actualy.
If you check the component’s STATUS in CDB_REGISTRY you’ll see that it is UPGRADED – thus it is not finished yet.

SQL> alter session set "_exclude_seed_cdb_view"=false;

Session altered.

SQL>  select con_id, comp_id, status from cdb_registry order by 1,2;

    CON_ID COMP_ID    STATUS
---------- ---------- ----------
	 1 CATALOG    UPGRADED
	 1 CATPROC    UPGRADED
	 1 RAC	      UPGRADED
	 1 XDB	      UPGRADED
	 2 CATALOG    VALID
	 2 CATPROC    VALID
	 2 RAC	      OPTION OFF
	 2 XDB	      VALID
	 3 CATALOG    UPGRADED
	 3 CATPROC    UPGRADED
	 3 RAC	      UPGRADED
	 3 XDB	      UPGRADED
	 4 CATALOG    UPGRADED
	 4 CATPROC    UPGRADED
	 4 RAC	      UPGRADED
	 4 XDB	      UPGRADED
	 5 CATALOG    UPGRADED
	 5 CATPROC    UPGRADED
	 5 RAC	      UPGRADED
	 5 XDB	      UPGRADED
	 6 CATALOG    UPGRADED
	 6 CATPROC    UPGRADED
	 6 RAC	      UPGRADED
	 6 XDB	      UPGRADED
	 7 CATALOG    UPGRADED
	 7 CATPROC    UPGRADED
	 7 RAC	      UPGRADED
	 7 XDB	      UPGRADED

The recompilation is done with utlrp.sql but need to be executed with catcon.pl.

[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_catcon_5538.lst]
catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

Afterwards check if all the PDBs are OPEN UNRESTRICTED now.

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 20:02:13 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

As final step the postupgrade_fixups.sql needs to be executed:

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql 
catcon: ALL catcon-related output will be written to [/home/oracle/mike/postupgrade_fixups_catcon_28596.lst]
catcon: See [/home/oracle/mike/postupgrade_fixups*.log] files for output generated by scripts
catcon: See [/home/oracle/mike/postupgrade_fixups_*.lst] files for spool files, if any
catcon.pl: completed successfully
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin

And a final check:

SQL> select con_id, owner, object_type, object_name from cdb_objects where status='INVALID' order by 1;
no rows selected

All fine – database is upgraded. Final action could be to adjust the time zone but regarding this topic I may produce another blog post soon.

Please note that with Oracle Database 12.2 we offer the “dbupgrade” utility as well which simplifies the call to catctl.pl. See the documentation and Mouhamadou Diaw’s blog post.

–Mike

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

As referred to it before there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will explain the method of “Everything At Once” and describe all the steps. The benefit of this approach is simplicity and ease of maintenance. In an upgrade workshop in Melbourne earlier this year a DBA from Germany came by in one of the breaks explaining that he takes care on over 100 developer databases – and it would ease his life a lot if he could “hit” all of the databases at the same time with one patch set or PSU, or even upgrade them to a higher release. This is the use case where it will make a lot of sense to leverage from this approach. But be aware and don’t over-consolidate as the pain point is common downtime. If you plan to use this approach you need to check before if your application owners can agree on common downtime windows – otherwise you may end in trouble. Big trouble!

The technique is easy to describe:

  • CDB$ROOT will always get upgraded first – I call this CYCLE 1
    • The “-n” parameter of catctl.pl will define how many parallel workers run the upgrade – 8 is the current maximum
    • The “-M” option will decide whether the CDB$ROOT stays in UPGRADE mode throughout the entire process of upgrade or becomes available for access after being upgraded leading to the fact that PDBs become available for access as well once they got upgraded – set “-M” and the CDB$ROOT will stay in UPGRADE mode throughout the entire process
  • Afterwards we can upgrade multiple PDBs (including the PDB$SEED) in parallel at the same time – I call this CYCLE 2,3, …
    • The “-n” parameter (divided by 2) of catctl.pl will determine how many PDBs will be upgraded in parallel
    • Your limiting factor is CPU power and cores
    • The “-N” parameter can alter the number of parallel workers per PDB – default is 2.

A few simple examples will demonstrate how the parameters work hand-in-hand:

  • CDB has (always) one PDB$SEED and 25 PDBs numbered PDB1 .. PDB25
  • That means we’ll have CYCLE 1 (for the CDB$ROOT) and between one additional CYCLE 2 (for all remaining 26 PDBs in parallel) up to 27 CYCLES once you decide to have no PDBs upgraded at the same time together with another PDB
    .
  • catctl.pl -M -n 16 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB7 (-n 16 divided by 2 = 8 PDBs to upgrade in parallel)
    • CYCLE 3: PDB8-PDB15
    • CYCLE 4: PDB16-PDB23
    • CYCLE 5: PDB24 and PDB25
    • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
    • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting
  • catctl.pl -M -n 26 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB12 (-n 26 divided by 2 = 13 PDBs to upgrade in parallel)
    • CYCLE 3: PDB13-PDB25
    • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
    • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting
  • catctl.pl -n 52 -N 1 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB25 (-n 52 divided by 2 = 26 PDBs to upgrade in parallel)
    • Each PDB will be upgraded with 1 workerl as -N is 1
    • The CDB$ROOT will be available once upgraded – same applies for PDBs once the upgrade is finished

Step-by-step instructions:

Preupgrade Steps

  • Create a guaranteed restore point in order to flashback in case of failure
    CREATE RESTORE POINT UPGRADE GUARANTEE FLASHBACK DATABASE;
  • Copy preupgrd.sql and utluppkg.sql from the Oracle 12.1.0.2 home’s ?/rdbms/admin into the source Oracle 12.1.0.1 ?/rdbms/admin directoryTHIS STEP IS EXTREMLY IMPORTANT as otherwise the preupgrd.sql and the utluppkg.sql from 12.1.0.1 will be loaded into the 12.1.0.1 database – but the concept requires always to use the preupgrd.sql/utluppkg.sql from the higher version. If you fail you see it (a) hanging and (b) getting plenty of errors.
  • Execute preuprd.sql within the source database – database needs to be up and running:
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/rdbms/admin -l /home/oracle/mike -b preupgrd preupgrd.sql

    • It will create 3 files which combine all information for the root, the seed and all pdbs together into one preupgrade.log, one preupgrade_fixups.sql and one postupgrade_fixups.sql. Default location for those files is $ORACLE_HOME/cfgtoollogs/<SID>/preupgrade
    • Verify the preupgrad.log and follow all advices
  • Execute the preupgrade_fixups.sql while all PDBs are open:
    ALTER PLUGGABLE DATABASE ALL OPEN;
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b preupgrade_fixups preupgrade_fixups.sql
  • Copy the init<sid>.ora into the new $ORACLE_HOME/dbs
  • Specific stepts for RAC environments:
    • Set cluster_database=false
      ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
    • Stop all instances
      srvctl stop database -d <db_name>

Upgrade

  • Switch to the new $ORACLE_HOME including all necessary env variables
  • Connect with sqlplus:
    sqlplus / as sysdba
  • Bring the CDB$ROOT instance into upgrade mode:
    STARTUP UPGRADE
  • Bring all PDBs into upgrade mode:
    ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
  • Control it with:
    SHOW PDBS
    Status should be MIGRATE for all PDBs
  • Exit from SQL*Plus and cd to $ORACLE_HOME/rdbms/admin :
    EXIT
    cd $ORACLE_HOME/rdbms/admin
  • Perform the upgrade in parallel:
    $ORACLE_HOME/perl/bin/perl catctl.pl -d $ORACLE_HOME/rdbms/admin -n 16 -M -l /home/oracle/mike catupgrd.sql
  • The important file with timings per PDB and to for a quick check is called upg_summary.log and can be found in:
    $ORACLE_HOME/cfgtoollogs/<SID>/upgrade/upg_summary.log

Postupgrade Steps

  • Only in case -M hasn’t been used then the CDB remains open during the upgrade of the PDBs and will need to be shutdown manually post upgrade:
    SHUTDOWN IMMEDIATE
  • Followed by a startup all PDBs must be opened now for recompilation
    STARTUP
    ALTER PLUGGABLE DATABASE ALL OPEN;
  • Execute the postupgrade_fixups.sql:
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql
  • Exit from SQL*Plus and cd into $ORACLE_HOME/rdbms/admin :
    EXIT
    cd $ORACLE_HOME/rdbms/admin
  • The recompilation is done via catcon.pl using the utlrp.sql script from within ?/rdbms/admin:
    $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
    1. utl.rp does not parallelize between PDBs
    2. Default parallel degree is: cpu_cores x 2
    3. It takes roughly 45 sec per PDB PLUS another 30 seconds to
      initialize XDB – as this happens serially it takes approx a while to
      complete recompilation past upgrade
  • Drop the guaranteed restore point
    DROP RESTORE POINT UPGRADE;
  • In RAC environments only:
    • Set cluster_database=true again
      ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
    • Start all instances
      srvctl start database -d <db_name>

Seems to be a lot of work, too? But in fact most of the steps are applicable to any upgrade such as the preupgrd.sql etc. Only remarkable change is the need to start scripts with catcon.pl – and not directly within SQL*Plus. Please remember that this approach will upgrade as many PDBs as you have depending on your CPU power in parallel.

In my test environment (a very outdated Exadata V1 without Flash, 6 year old disks, 2 physical CPU sockets) I upgrade 25 PDBs each roughly 25 GB in size with user data in it and all options present in less than 3 hours including recompilation. Please repeat this exercise with 25 independent databases consolidated on the same node within the same time 😉 Try it 🙂

Please see also my updated steps +2 years later when Oracle Database 12.2.0.1 became available:

https://mikedietrichde.com/2017/05/10/upgrade-everything-at-once-multitenant-upgrade-from-oracle-12-1-to-12-2/

–Mike