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:

–Mike