Recently, I talked to a customer about upgrading Oracle Database. I was showing how you can get the most out of AutoUpgrade. The customer showed me their runbook, and together, we found several redundant tasks. AutoUpgrade already handled many of the checks and tasks.

Leave a comment and show me the tasks in your runbook. Let’s see if we can find something to remove.

What Did We Find At the Customer?

Before Upgrade

• Purging recycle bin The customer actively used the recycle bin and manually purged it before upgrading. However, AutoUpgrade has a check on the recycle bin. From the prechecks log file:

  2024-04-17 10:15:51.775 INFO Running check PURGE_RECYCLEBIN [DB12] - CheckTrigger.call#96 
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sys.recyclebin$'
  

  And purges it – if needed – during prefixups. From the prefixups log file:

  2024-04-17 10:15:44.972 INFO Running fixup [PURGE_RECYCLEBIN][DB12][PLSQL][PURGE DBA_RECYCLEBIN
  2024-04-17 10:15:44.972 INFO [EB5A67] [PURGE DBA_RECYCLEBIN
  2024-04-17 10:15:44.976 INFO [EB5A67] Executing SQL [/* [EB5A67]  */PURGE DBA_RECYCLEBIN
  2024-04-17 10:15:45.316 INFO # PURGE_RECYCLEBIN - Check.runFix#292 
  2024-04-17 10:15:45.317 INFO Finished fixup [PURGE_RECYCLEBIN][DB12][SUCCESSFUL] - FixUpTrigger.executeFixUp#231 
  

After Upgrade

• Execute catuppst.sql This script performs:

  … remaining upgrade actions that do not require that the database be open in UPGRADE mode

  AutoUpgrade handles this part for you. Here is an extract from the phase overview during upgrade:

  $ grep -B2 "catuppst"  dbupgrade/phase.log
  [phase 102] type is 1 with 1 Files
  
  @catuppst.sql
  

• Recompiling objects The customer recompiled objects using utlrp.sql. This is usually not needed. By default, AutoUpgrade performs a recompilation in the postfixups phase unless you override the behavior with run_utlrp. Here is an extract from the autocompile log file found in the postfixups directory:

  13:12:42   6      select count(*) into cnt from sys.dba_objects
  13:12:42   7         where status = 'INVALID';
  13:12:42   8      IF cnt > 0 THEN
  13:12:42   9          :utlprp_name := '/u01/app/oracle/product/23.0.0.0/dbhome_2/rdbms/admin/utlprpom.sql 2';
  13:12:42  10          :utldtchk_name := '/u01/app/oracle/product/23.0.0.0/dbhome_2/rdbms/admin/utldtchk.sql';
  13:12:42  11      END IF;
  

• Datapatch The customer executed Datapatch after a successful upgrade. The upgrade engine already does this as one of the very last phases. Here is an extract from upg_summary.log:

  Component                               Current         Full     Elapsed Time
  Name                                    Status          Version  HH:MM:SS
  
  ...
  Datapatch                                                        00:00:09
  

• Gathering database statistics The customer already knew that AutoUpgrade gathers dictionary statistics and that they needed to gather fixed objects statistics after a while. But, they were gathering database statistics to refresh the statistics on their application schemas. Although this is a good idea in some cases, I would not recommend this. AutoUpgrade does not touch user data during a database upgrade, so if you had good statistics before the upgrade, they are also good after. The only case I can think of is upgrades from Oracle Database 11.2.0.4. Oracle Database introduced new histogram types in Oracle Database 12c, which could significantly benefit some databases.

  By the way, here is a handy trick to gather fixed objects statistics some time after the upgrade.

Conclusion

While working with this customer, we found several redundant items. Getting those items out of the runbook means less overall downtime – and a less complex upgrade. AutoUpgrade makes upgrading Oracle Database much easier.

Please leave a comment if there are tasks that AutoUpgrade should do for you.

Appendix

Further Reading

• Database Preupgrade tool (via autoupgrade.jar) check list (Doc ID 2380601.1)
• AutoUpgrade Tool (Doc ID 2485457.1)
• Database Upgrade Guide