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 to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database

Cool, I like that ūüôā I will do this right away and use the approach to Upgrade first, then Plugin afterwards.

Upgrading an Oracle 12.1.0.2 database to Oracle 12.2.0.1

First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the newest drop with future PSUs and BPs.

$ java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/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:  DB12
Container Name:  DB12
Container ID:  0
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
Oracle Workspace Manager               [to be upgraded]  VALID
Oracle XML Database                    [to be upgraded]  VALID

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

Run /preupgrade_fixups.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  DISABLED      500 MB  None
SYSTEM                             700 MB  ENABLED       765 MB  None
TEMP                                20 MB  ENABLED       150 MB  None
UNDOTBS1                           295 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.

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

Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/DB12/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/DB12/preupgrade/postupgrade_fixups.sql

Next step is to follow the guidelines displayed by the output, i.e. changing parameters, gathering stats, running the preupgrade_fixups.sql etc., then shutting down the database and copying the SPFILE and creating a new password file in the 12.2 Oracle Home’s directory.

[oracle@localhost ~]$ . db121
[DB12] oracle@localhost:~
$ s
SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 14:09:25 2017Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL> @/u01/app/oracle/cfgtoollogs/DB12/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 3
Generated on:            2017-03-07 13:46:51

For Source Database:     DB12
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  12.2.0.1.0

Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
dictionary_stats          Passed  None

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/spfileDB12.ora $OH12/dbs
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/orapwDB12 $OH12/dbs
[DB12] oracle@localhost:~

-- it's Best Practice to create a new password file instead of copying the old one!

Now the database is ready to get upgraded to Oracle Database 12.2.0.1.

Database Upgrade to Oracle Database 12.2.0.1

Database must be in STARTUP UPGRADE mode – and the upgrade gets executed from the command line in my case. Alternative would be the DBUA of course. You could also use the shortcut “dbupgrade” instead of typing all the characters below.

[DB12] oracle@localhost:~
$ . db122
[DB12] oracle@localhost:~
$ s
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:16:20 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[DB12] oracle@localhost:~

$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/
[DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle 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
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_170107]

/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]

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

Number of Cpus        = 2
Database Name         = DB12
DataBase Version      = 12.1.0.2.0
Parallel SQL Process Count            = 6
Components in [DB12]
Installed [CATALOG CATPROC OWM XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_03_07 14:17:11]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [DB12] Files:1    Time: 29s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [DB12] Files:5    Time: 43s
Restart  Phase #:2    [DB12] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [DB12] Files:19   Time: 22s
Restart  Phase #:4    [DB12] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [DB12] Files:6    Time: 16s
*****************   Catproc Start   ****************
Serial   Phase #:6    [DB12] Files:1    Time: 12s
*****************   Catproc Types   ****************
Serial   Phase #:7    [DB12] Files:2    Time: 11s
Restart  Phase #:8    [DB12] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [DB12] Files:69   Time: 38s
Restart  Phase #:10   [DB12] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [DB12] Files:1    Time: 37s
Restart  Phase #:12   [DB12] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [DB12] Files:97   Time: 12s
Restart  Phase #:14   [DB12] Files:1    Time: 1s
Parallel Phase #:15   [DB12] Files:118  Time: 21s
Restart  Phase #:16   [DB12] Files:1    Time: 1s
Serial   Phase #:17   [DB12] Files:13   Time: 2s
Restart  Phase #:18   [DB12] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [DB12] Files:33   Time: 31s
Restart  Phase #:20   [DB12] Files:1    Time: 0s
Serial   Phase #:21   [DB12] Files:3    Time: 7s
Restart  Phase #:22   [DB12] Files:1    Time: 0s
Parallel Phase #:23   [DB12] Files:24   Time: 117s
Restart  Phase #:24   [DB12] Files:1    Time: 0s
Parallel Phase #:25   [DB12] Files:11   Time: 67s
Restart  Phase #:26   [DB12] Files:1    Time: 0s
Serial   Phase #:27   [DB12] Files:1    Time: 0s
Serial   Phase #:28   [DB12] Files:3    Time: 3s
Serial   Phase #:29   [DB12] Files:1    Time: 0s
Restart  Phase #:30   [DB12] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [DB12] Files:1    Time: 1s
Restart  Phase #:32   [DB12] Files:1    Time: 0s
Serial   Phase #:34   [DB12] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [DB12] Files:283  Time: 18s
Serial   Phase #:36   [DB12] Files:1    Time: 0s
Restart  Phase #:37   [DB12] Files:1    Time: 0s
Serial   Phase #:38   [DB12] Files:1    Time: 5s
Restart  Phase #:39   [DB12] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [DB12] Files:3    Time: 48s
Restart Phase #:41   [DB12] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [DB12] Files:13   Time: 69s
Restart  Phase #:43   [DB12] Files:1    Time: 0s
Parallel Phase #:44   [DB12] Files:12   Time: 12s
Restart  Phase #:45   [DB12] Files:1    Time: 1s
Parallel Phase #:46   [DB12] Files:2    Time: 0s
Restart  Phase #:47   [DB12] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [DB12] Files:1    Time: 4s
Restart  Phase #:49   [DB12] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [DB12] Files:1    Time: 20s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [DB12] Files:1    Time: 0s
Restart  Phase #:52   [DB12] Files:1    Time: 0s
****************   Upgrading Java   ****************
Serial   Phase #:53   [DB12] Files:1    Time: 1s
Restart  Phase #:54   [DB12] Files:1    Time: 0s
*****************   Upgrading XDK   ****************
Serial   Phase #:55   [DB12] Files:1    Time: 0s
Restart  Phase #:56   [DB12] Files:1    Time: 1s
*********   Upgrading APS,OLS,DV,CONTEXT   *********
Serial   Phase #:57   [DB12] Files:1    Time: 0s
*****************   Upgrading XDB   ****************
Restart  Phase #:58   [DB12] Files:1    Time: 0s
Serial   Phase #:60   [DB12] Files:3    Time: 8s
Serial   Phase #:61   [DB12] Files:3    Time: 5s
Parallel Phase #:62   [DB12] Files:9    Time: 3s
Parallel Phase #:63   [DB12] Files:24   Time: 4s
Serial   Phase #:64   [DB12] Files:4    Time: 6s
Serial   Phase #:65   [DB12] Files:1    Time: 0s
Serial   Phase #:66   [DB12] Files:30   Time: 3s
Serial   Phase #:67   [DB12] Files:1    Time: 0s
Parallel Phase #:68   [DB12] Files:6    Time: 2s
Serial   Phase #:69   [DB12] Files:2    Time: 14s
Serial   Phase #:70   [DB12] Files:3    Time: 31s
Restart  Phase #:71   [DB12] Files:1    Time: 0s
*********   Upgrading CATJAVA,OWM,MGW,RAC   ********
Serial   Phase #:72   [DB12] Files:1    Time: 32s
****************   Upgrading ORDIM   ***************
Restart  Phase #:73   [DB12] Files:1    Time: 0s
Serial   Phase #:75   [DB12] Files:1    Time: 1s
Parallel Phase #:76   [DB12] Files:2    Time: 0s
Serial   Phase #:77   [DB12] Files:1    Time: 0s
Restart  Phase #:78   [DB12] Files:1    Time: 0s
Parallel Phase #:79   [DB12] Files:2    Time: 1s
Serial   Phase #:80   [DB12] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:81   [DB12] Files:1    Time: 0s
Serial   Phase #:83   [DB12] Files:1    Time: 1s
Serial   Phase #:84   [DB12] Files:1    Time: 0s
Restart  Phase #:85   [DB12] Files:1    Time: 0s
Serial   Phase #:86   [DB12] Files:1    Time: 0s
Restart  Phase #:87   [DB12] Files:1    Time: 1s
Parallel Phase #:88   [DB12] Files:3    Time: 0s
Restart  Phase #:89   [DB12] Files:1    Time: 0s
Serial   Phase #:90   [DB12] Files:1    Time: 1s
Restart  Phase #:91   [DB12] Files:1    Time: 0s
Serial   Phase #:92   [DB12] Files:1    Time: 0s
Restart  Phase #:93   [DB12] Files:1    Time: 1s
Parallel Phase #:94   [DB12] Files:4    Time: 0s
Restart  Phase #:95   [DB12] Files:1    Time: 0s
Serial   Phase #:96   [DB12] Files:1    Time: 1s
Restart  Phase #:97   [DB12] Files:1    Time: 0s
Serial   Phase #:98   [DB12] Files:2    Time: 0s
Restart  Phase #:99   [DB12] Files:1    Time: 0s
Serial   Phase #:100  [DB12] Files:1    Time: 1s
Restart  Phase #:101  [DB12] Files:1    Time: 0s
***********   Upgrading Misc. ODM, OLAP   **********
Serial   Phase #:102  [DB12] Files:1    Time: 0s
****************   Upgrading APEX   ****************
Restart  Phase #:103  [DB12] Files:1    Time: 1s
Serial   Phase #:104  [DB12] Files:1    Time: 0s
Restart  Phase #:105  [DB12] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:106  [DB12] Files:1    Time: 0s
*************   Final Upgrade scripts   ************
Serial   Phase #:107  [DB12] Files:1    Time: 118s
**********   End PDB Application Upgrade   *********
Serial   Phase #:108  [DB12] Files:1    Time: 0s
*******************   Migration   ******************
Serial   Phase #:109  [DB12] Files:1    Time: 32s
Serial   Phase #:110  [DB12] Files:1    Time: 0s
Serial   Phase #:111  [DB12] Files:1    Time: 40s
*****************   Post Upgrade   *****************
Serial   Phase #:112  [DB12] Files:1    Time: 73s
****************   Summary report   ****************
Serial   Phase #:113  [DB12] Files:1    Time: 0s
Serial   Phase #:114  [DB12] Files:1    Time: 0s
Serial   Phase #:115  [DB12] Files:1     Time: 22s

------------------------------------------------------
Phases [0-115]         End Time:[2017_03_07 14:34:45]
------------------------------------------------------

Grand Total Time: 1055s

LOG FILES: (/home/oracle/catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:17m:35s]
[DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin

Database DB12 is now upgraded to Oracle Database 12.2.0.1 but needs some post-upgrade treatments.

Post-Upgrade Treatments

Those include recompilation, postupgrade_fixups.sql and time zone adjustment.

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:36:06 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size            8628640 bytes
Variable Size          369100384 bytes
Database Buffers      662700032 bytes
Redo Buffers            8146944 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN          2017-03-07 14:36:40

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END          2017-03-07 14:41:52

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL> @/u01/app/oracle/cfgtoollogs/DB12/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 3
Generated on:            2017-03-07 13:46:52

For Source Database:     DB12
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  12.2.0.1.0

Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None

PL/SQL procedure successfully completed.

Session altered.

SQL> @/home/oracle/DST/upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.2.0.1 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv26 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @/home/oracle/DST/upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be upda
ted to DSTv26 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv26 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL>

Database is now fully upgraded and ready to go. You may create fixed objects statistics a while after the database is up and running. But I can proceed now with the plugin operation.

Plugin the upgraded database as a PDB into a CDB

This step is simple and straight forward, though on part, the sanity script noncdb_to_pdb.sql, which will be only run once in the life span of a database and is irreversible (but restartable in Oracle Database 12.2.0.1 – it wasn’t before), may take a bit to complete depending on the number of objects and some other constraints.

First I’m creating the XML manifest file which basically described the physical layout of my database. The database has to be on read only mode – and it has to be at least an Oracle 12.1.0.1 database as otherwise the package DBMS_PDB wouldn’t exist.

$ . db122
[DB12] oracle@localhost:~
$ sSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 22:58:38 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
SQL> exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Once the file has been created and the database is shutdown I’ll connect now into my already existing container database and progress from there. I will plugin now the DB12 database, give it a new name (not required but I’d recommend some obvious name identifying it as a PDB), doing a compatibility check (for errors please see PDB_PLUG_IN_VIOLATIONS) and then kick off the sanity script noncdb_to_pdb.sql.

[DB12] oracle@localhost:~
$ . cdb2
[CDB2] oracle@localhost:~
$ s
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 23:07:19 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set serverout on
SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/tmp/pdb1.xml',
pdb_name => 'PDB1')
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is UPGR compatible? ' || compatible);
END;
/
Is UPGR compatible? YES
PL/SQL procedure successfully completed.

SQL> create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
Pluggable database created.

SQL> alter session set container=PDB1;
Session altered.
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> startup
Pluggable database opened.
SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1                           READ WRITE NO
SQL>

When I run the CREATE PLUGGABLE DATABASE command I use the NOCOPY option intentionally. But be aware that in this case where the databases stays in place and does not get moved you have to have a valid backup of the database as in case of failure you may be lost in outer space with no way back or forth.

In addition, after this operation is completed, the sanity script has been run successfully and the new PDB is OPEN you must take a fresh backup as all your archives and backups from “before” are useless from now on. Don’t forget this – it’s key!

Yep – all done!

Database DB12 got upgraded to Oracle Database 12.2.0.1 – and then plugged into CDB2 as a new PDB1.

–Mike

Restarting a failed Database Upgrade with DBUA 12.2

In my previous blog post I did showcase how to restart a failed database upgrade on the command line before Oracle Database 12c, with Oracle Database 12.1.0.x and with the new Oracle Database 12.2.0.x:

Restarting a failed Database Upgrade with catctl.pl

Here I’d like to show the new capability of the Database Upgrade Assistant (DBUA) to restart an upgrade, a functionality the DBUA was missing until now. Please note that you can always go back to the command line, no matter in which version you have used the DBUA.

I won’t describe an upgrade with the DBUA in this blog post as this is showcased in the documentation already:

Starting the database upgrade with DBUA

I will upgrade the UPGR database well known from our Hands-On Lab.

DBUA Database Selection Oracle 12.2

And please don’t put in your credentials in the fields below – if you are logged in as the oracle user then this will lead to failure and drive you crazy …

Another thing which puzzles me:
I still have to execute the olspreupgrade.sql script from the newer (in my case the 12.2) home by myself. I’d wish
the DBUA would do this for me as well as I’ll have to open an xterm, set my environment and type in a very long path name to point to the new 12.2 home in order to execute this script in my source environment.

DBUA Database Selection Oracle 12.2

Ok, let’s kick off the upgrade:

Oracle 12.2 DBUA Database Upgrade

The progress bar is very imprecise in relation to the duration – you can ignore it more or less.

And – very sad – the Alert and Activity Monitor buttons disappeared – but they may reappear in a later release of the DBUA.
.

The Error Scenario

It’s always fun to kill pmon finding out how cool this database is ūüėČ It survives the deadly attack ūüôā Of course it does … it’s the Oracle Database ūüėČ

kill -9 pmon

Bang!
.

The DBUA recognizes the failure

Even though the DBUA recognized the failure quite quickly it still tries to complete the upgrade – which of course results in a ton of errors. It just means that you’ll have to wait until the DBUA has “progressed” the upgrade till the end – and DON’T CLOSE THE DBUA as otherwise you’ll lose the ability to RETRY.

DBUA - upgrade failure

You’ll see the error count going up until the DBUA has reached the “end” of the (failed) upgrade.

DBUA Oracle 12.2 - upgrade failure scenario 2

And then it displays the RETRY button:

DBUA Oracle 12.2 - upgrade failure scenario

Rerun the Database Upgrade

Once you hit RETRY the DBUA will try to solve the situation – and in my case it will start up my source database in STARTUP UPGRADE mode again – and then process the upgrade using the -R option of catctl.pl (described in the previous blog post)

DBUA Oracle 12.2 - upgrade failure scenario

You’ll find also a new set of logfiles in $ORACLE_BASE/cfgtoollogs/dbua/ subdirectories indication with a number (here: 1) and an “R” that this is the first restart attempt’s logs:

Logfiles from DBUA rerun 12.2

–Mike

Restarting a failed Database Upgrade with catctl.pl

What if …

What if a database upgrade fails in between? First of all you should have enabled one of the common protections for issues happening during an upgrade:

  • Guaranteed Restore Point to Flashback Database
  • Restore an RMAN Online Backup
  • Restore a partial Offline Backup

See our slide’s Fallback section for further details.

But actually there are ways to restart the upgrade without the need to fallback in case of a non-destructive incident such as running out of archivelog space.

Restart the Upgrade before Oracle Database 12c

Before Oracle Database 12c the most simple way to restart an upgrade was to restart catupgrd.sql from SQL*Plus making sure the database is back in upgrade mode. Upgrade (and downgrade) scripts are designed to run again … and again … if necessary. Even if you kicked off the upgrade with the Database Upgrade Assistant (DBUA) who by itself is not able to rerun the upgrade you can invoke the command line upgrade and rerun it without the need for restore.

SQL> startup upgrade

SQL> spool /home/oracle/catupgrd.log

SQL> @?/rdbms/admin/catupgrd.sql

This will restart the upgrade from the beginning and rerun it again. But always from the beginning.

Restart the Upgrade in Oracle Database 12.1.0.x

With this release we introduced the parallel upgrade utility, catctl.pl.  The database needs to be in startup upgrade mode again but the upgrade will be driven from the command line with the Perl tool running the upgrade with a maximum of 8 parallel workers. And if the upgrade fails non-destructive for whatever reason you invoke catctl.pl from the phase where it has stopped with the -p option specifying the phase number.

In the following example the upgrade has been stopped in phase 100 (in my example by a CTRL-C):

*********** Upgrading Misc. ODM, OLAP **********
Serial Phase  #:95 [UPGR] Files:1 Time: 0s
**************** Upgrading APEX ****************
Restart Phase #:96 [UPGR] Files:1 Time: 0s
Serial Phase  #:97 [UPGR] Files:1 Time: 1s
Restart Phase #:98 [UPGR] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase  #:99 [UPGR] Files:1 Time: 0s
************* Final Upgrade scripts ************
Serial Phase #:100 [UPGR] Files:1   ^[c

Now I simply can restart it from this phase again without the need to rerun all the previous steps:

Make sure the database is in STARTUP UPGRADE mode. Then invoke from ?/rdbms/admin:

$ORACLE_HOME/perl/bin/perl catctl.pl -p 100 -l /home/oracle/ catupgrd.sql
************* Final Upgrade scripts ************
Serial Phase #:100 [UPGR] Files:1 Time: 142s
********** End PDB Application Upgrade *********
Serial Phase #:101 [UPGR] Files:1 Time: 1s
******************* Migration ******************
Serial Phase #:102 [UPGR] Files:1 Time: 60s
Serial Phase #:103 [UPGR] Files:1 Time: 0s
Serial Phase #:104 [UPGR] Files:1 Time: 71s
***************** Post Upgrade *****************
Serial Phase #:105 [UPGR] Files:1 Time: 30s
**************** Summary report ****************
Serial Phase #:106 [UPGR] Files:1 Time: 1s
Serial Phase #:107 [UPGR] Files:1 Time: 0s
Serial Phase #:108 [UPGR] Files:1 Time: 38s

This works also when the upgrade has been started with the DBUA and failed. The DBUA is unable to rerun the upgrade but you can always invoke the command line upgrade by looking at the logfiles DBUA produced finding the phase where it has been stopped.

Restart the Upgrade in Oracle Database 12.2.0.x

In Oracle Database 12.2.0.1 we have again improved the rerun ability of the upgrade – now you don’t have to know the phase where it stopped – but we introduce the -R option for catctl.pl to rerun the upgrade automatically after the last completed phase.

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

See the documentation for further details:

Rerunning Upgrade for Oracle Databases – Oracle Database Upgrade Guide 12.2

And the DBUA has been improved as well. It can now rerun an failed upgrade but only from within the same DBUA session (so please don’t close it). And of course you can always fall back to the command line and complete it from there.

See a separate blog post about:

Restarting a failed Database Upgrade with DBUA 12.2

–Mike

How to SET TIMING ON for parallel upgrades to 12c?

Annotation:
This hack is not necessary anymore since Oracle Database 12.1.0.2 as TIMING ON is the standard as well as ECHO ON. Please don’t edit catupses.sql by yourself.

Have you asked yourself how to get timings in an Oracle Database 12c upgrade for all statements?

When you run the parallel upgrade via catctl.pl, the parallel upgrade Perl driving script in Oracle Database 12c, you may also want to get timings written in your logfile during execution. As catctl.pl does not offer an option yet the best way to achieve this is to edit the catupses.sql script in $ORACLE/rdbms/admin as this script will get called all time over and over again throughout all steps of theupgrade run.

Just add these lines marked in RED to catupses.sql and start your upgrade:

Rem =============================================
Rem Call Common session settings
Rem =============================================
@@catpses.sql

Rem =============================================
Rem  Set Timing On during the Upgrade
Rem =============================================
SET TIMING ON;

Rem =============================================
Rem Turn off PL/SQL event used by APPS
Rem =============================================
ALTER SESSION SET EVENTS=’10933 trace name context off’
;

-Mike

PS: This may become the default in a future patch set ūüėČ