In the previous blog posts I explained how to generate a sample config file for the AutoUpgrade tool and how to adjust it. Then I gave you an overview about the most important Advanced Options you may want or need to deal with when using the utility. And in the below blog post I would like to show you have you need to edit the Config file for AutoUpgrade – Tweaking init parameters is an important topic.
This is the forth post of a series of blog posts regarding the new AutoUpgrade tool. Please find all the other available blog posts in the overview step-by-step section below.
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
Adjusting init.ora parameters
When would you need to adjust your init.ora or spfile parameters as part of a database upgrade? Actually there are many reasons and situations I can think about. For instance, our upgrade experience tells us that running an upgrade with events and underscores in your init file may slow down things generally, or cause some trouble. That does not mean that you’ll have to remove all parameters. But it could be a wise move.
Another situation could be that you’d like to implant a standard set of special parameters. This would be good as part of the upgrade. Typical examples could be the global setting of
_cursor_obsolete_threshold=1024 for all databases.
Options to adjust init parameters
Each of these parameters is optional. You can use them as
local parameters. Each of them can point to a text parameter file specifying parameters to either delete or add, during or after the upgrade. I will give you some more detailed examples below.
Usage examples are:
Let’s do a simple example using the DB12 database of the Hands-On Lab.
This is my adjusted DB12.cfg file as I used it in the previous blog posts already:
global.autoupg_log_dir=/home/oracle/logs upg1.dbname=DB12 upg1.start_time=NOW upg1.source_home=/u01/app/oracle/product/126.96.36.199 upg1.target_home=/u01/app/oracle/product/19 upg1.sid=DB12 upg1.log_dir=/home/oracle/logs upg1.upgrade_node=localhost upg1.target_version=19 upg1.restoration=no
As explained above already, I’d like to set
_cursor_obsolete_threshold=1024 for all my databases – generally – after upgrade. Plus, in addition I’d like to remove
O7_DICTIONARY_ACCESSIBILITY and change
audit_trail='none' in the DB12 database.
I need to add to my config file:
global.add_after_upgrade_pfile=/home/oracle/pfiles/global_add_after.ora upg1.del_during_upgrade_pfile=/home/oracle/pfiles/upg1_del_during.ora upg1.add_after_upgrade_pfile=/home/oracle/pfiles/upg1_add_after.ora
The three files look like this:
# global_add_after.ora _exclude_seed_cdb_view=false _cursor_obsolete_threshold=1024
# upg1_add_after.ora audit_trail='none'
I create them all in my
As it is best practice, I first ran the
analyze, then the
deploy phase which repeats the
analyze. But this way I can be sure that the upgrade will complete successfully.
java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/scripts/DB12.cfg -mode analyze java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/scripts/DB12.cfg -mode deploy
And finally, I check my
init.ora settings (I removed all the other parameters):
*._cursor_obsolete_threshold=1024 *._exclude_seed_cdb_view=false *.audit_trail='none'
O7_DICTIONARY_ACCESSIBILITY is not set anymore.
This looks very good!