Monitoring catctl.pl progress during upgrades

The parallel upgrade catctl.pl can resume once an upgrade has stopped for unexpected reasons. Before Oracle 12.2 you can restart it with the -p option specifying the phase it stopped. And since Oracle 12.2 you can only use the -R option to resume it. This blog post will give you a bit more insights in monitoring catctl.pl progress during upgrades.

Monitoring catctl.pl progress during upgrades

I blogged about the resume options for catctl.pl a while ago:

• Restarting a failed database upgrade with catctl.pl

But you may ask yourself: How does the tool know where to resume? And do you need to keep the logfiles in order to allow it to resume correctly?

Actually the parallel upgrade creates two tables during an upgrade:

• REGISTRY$UPG_RESUME
• REGISTRY$UPG_SUMMARY

These two tables are used to keep track of the upgrade process.

A closer look into REGISTRY$UPG_RESUME

The table REGISTRY$UPG_RESUME gets only create during a database upgrade. You can monitor the phases with this query:

column MIN format 999
column SEC format 999
column PHASENO format 99999

select phaseno,
       extract( minute from diff ) MIN,
       extract( second from diff ) SEC
  from
    (select phaseno, (endtime-starttime) diff from registry$upg_resume);

This will give you for instance the following result:

PHASENO  MIN  SEC
------- ---- ----
      0    1   52
      1    0   57
      3    0   24
      5    0   18
      6    0   14
      7    0   12
      9    0   41
     11    0   47
     13    0   10
     15    0   21
     17    0    3
     19    0   37
     21    0    9
...

You may recognize that not every phase gets an entry into the REGISTRY$UPG_RESUME table. The simple reason is that for instance phases 2 and 4 are so called RESTART phases were we clean-out worker processes and start a new workers set. Those don’t need to be recorded.

But what happens when you’d like to resume an upgrade from let’s say phase 20? Then it will be resumed from the previous phase, i.e. 19 in this case.

And REGISTRY$UPG_SUMMARY?

The table REGISTRY$UPG_SUMMARY simply holds the information where the upg_summary.log gets written to and stored in. In addition it holds the upgrade start and end times and a potential error count.

select REPORTNAME from REGISTRY$UPG_SUMMARY;

REPORTNAME
-------------------------------------------
/home/oracle/upg_summary.log

The upg_summary.log holds information mainly about how long each component has taken to upgrade – and of course the summary information, e.g.:

Oracle Database 12.2 Post-Upgrade Status Tool           04-13-2018 06:59:49

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:14:28
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:01:02
Oracle Label Security                  UPGRADED      12.2.0.1.0  00:00:10
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:03:03
Final Actions                                                    00:03:15
Post Upgrade                                                     00:01:28

Total Upgrade Time: 00:23:26

Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:0h:23m:26s]

–Mike