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

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