In the previous blog post I explained how to create a config file for the AutoUpgrde utility. And now I’d like to show how you can use some important advanced options to have the AutoUpgrade utility do or skip specific tasks. Read about how you can tweak the config file for AutoUpgrade 19c – Advanced options.
I will describe only the most important advanced parameters which I think can be very useful in your environments. Of course, there are many more as you can see in the Database Upgrade Guide.
AutoUpgrade – Step-by-step
- The new AutoUpgrade Utility – Download, documentation and supported versions
- Create and adjust the config file for AutoUpgrade
- Config file for AutoUpgrade – Advanced options
- Config file for AutoUpgrade – Tweaking init parameters
- AutoUpgrade: ANALYZE, FIXUPS, UPGRADE and DEPLOY modes
- AutoUpgrade: Where do you find all the logfiles?
- UPG: The AutoUpgrade Command Line Interface
- Upgrading Multitenant databases with AutoUpgrade
- Moving to a new server with AutoUpgrade
- How to tweak the hidden settings in AutoUpgrade
- AutoUpgrade and Data Guard, RAC, Restart and non-CDB to PDB
- AutoUpgrade and Wallets
The full list of (almost) all parameters
You will find the list of almost all parameters, global and local, in the Database Upgrade Guide:
Why do I write “almost”? We have no control on when our documentation colleagues are able to publish a refreshed version of the docs. Hence, it could be the case that our downloadable tool has a new parameter which is actually not yet in the documentation. While I’m writing this,
drop_grp_after_upgrade is such an example. It will be in the official documentation as soon as it gets refreshed the next time.
Guaranteed Restore Points – Yes or No?
In order to have a valid fallback in place, the AutoUgrade uses a guaranteed restore point you can fallback to. This is meant for situations where something fails – and the tool allows you to flashback. After a successful upgrade, we do not delete the guaranteed restore point by default (see below
But there are situations where you’d like to override this setting – or where you need to. For instance, if your database is in
NOARCHIVELOG mode, you can’t create a restore point. And the AutoUograde utility can’t either. Or you have a different restore plan, and hence, you don’t want a restore point. Or if you plan to upgrade a Standard Edition database. In SE and SE2 there are no guaranteed restore points available. But for SE databases, there’s no need to use the below parameter – we detect it automatically and
autoupgrade.jar does not attempt to set a GRP in an SE database.
Whatever the reason is, this is how you disable the creation of a guaranteed restore point with AutoUpgrade.
Be aware when you use this. Having a correct and working fallback strategy is key.
Drop the Guaranteed Restore Point automatically
By default, we keep the GRP even after a successful upgrade. This is important for instance when you’d like to test certain things but you plan to flashback the night after to rehearse another test run.
Since the June 2019 version of the AutoUpgrade tool (20190620) you can override this default behavior by setting:
Then the GRP will be dropped by autoupgrade.jar after a successful upgrade.
Changing the Start Time
The AutoUpgrade parameter
start_time is an optional parameter. If you don’t set it, the upgrade will start immediately once you invoke the tool. In the sample config we use
start_time=NOWwhich is equivalent to not explicitly setting it.
But you can adjust the
start_time as well. The format is
For example, you can set it to:
But you can’t set it to (failures marked in red):
The default will be:
If you alter it, remember to use the slash “
/” as a date separator and specify the day, then the month. For the time, use the colon “
:” and the 24 hour format.
Adding scripts before and after
When we started the beta test, one of the first requirements from customers was to execute a script before and after the upgrade. Usually the database upgrade is only a small portion of a change process, an application server farm needs to be taken down, applications need to be stopped – and everything of course needs to be restarted afterwards.
Hence, we built in these two global parameters, which can be used as local parameters as well on a per-DB basis:
global.before_action=/home/oracle/scripts/before.sh global.after_action=/home/oracle/scripts/after.sh prefix.before_action=/home/oracle/scripts/before_DB12.sh prefix.after_action=/home/oracle/scripts/after_DB12.sh
You can call the following script types with it:
- Unix shell (
- Microsoft Windows batch (
- Microsoft Windows PowerShell (
- Oracle SQL file (
.sql), with a local operation only designated by the prefix.
In addition, with a special
Y flag after the script, you advice the autoupgrade.jar to stop in case the script fails.
Remove Underscores and Events
I’ve just dealt with a strange customer case where it turned out that the issue is caused by an event still set from an issues happening years ago. Once the customer removed the event, all went fine. For such purposes you can set:
This is a global-only parameter. If you’d like to tweak parameters on a per-db-level, you should use the parameter options in the following blog post. By default, we’ll keep underscores and events in the
There are situations when you won’t like to have the recompilation after upgrade happen automatically. This could be the case when you have too many CPU cores presented to the database. Or too much additional load at the same time. See also: utlrp.sql – How to decrease resource consumption with utlprp.sql. For such situations, you can use:
This is a local parameter you need to set on a per-database level.
Postpone Time Zone Adjustment
Some may also want to postpone or even neglect the time zone adjustment. The
autoupgrade.jar does all the necessary time zone adjustments for the highest available time zone file in the destination home. But there are cases where you may not want this. Be it that you’d prefer a specif time zone version everywhere, or that you would like to handle this by yourself – or, in some rare cases, that it simply takes too much downtime. Anyway, this is how you won’t get a time zone adjustment after the upgrade has been completed:
This is a local parameter. It needs to be set on a per-database level.
This is already a long list of parameters you can set when you use the autoupgrade.jar. There are much more options. I summarized only the most important non-standard parameters. You can find the full list in the AutoUpgrade Utility Configuration Files – Oracle 19c Upgrade Guide.