Config file for AutoUpgrade – Tweaking init parameters

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

1. The new AutoUpgrade Utility – Download, documentation and supported versions
2. Create and adjust the config file for AutoUpgrade
3. Config file for AutoUpgrade – Advanced options
4. Config file for AutoUpgrade – Tweaking init parameters
5. AutoUpgrade: ANALYZE, FIXUPS, UPGRADE and DEPLOY modes
6. AutoUpgrade: Where do you find all the logfiles?
7. UPG: The AutoUpgrade Command Line Interface
8. Upgrading Multitenant databases with AutoUpgrade
9. Moving to a new server with AutoUpgrade
10. How to tweak the hidden settings in AutoUpgrade
11. AutoUpgrade and Data Guard, RAC, Restart and non-CDB to PDB
12. 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 _exclude_seed_cdb_view=false and _cursor_obsolete_threshold=1024 for all databases.

Options to adjust init parameters

The autoupgrade.jar offers you a variety of options to adjust init parameters as part of a database upgrade. You can find the existing options in the documentation:

• add_during_upgrade_pfile
• add_after_upgrade_pfile
• del_during_upgrade_pfile
• del_after_upgrade_pfile

Each of these parameters is optional. You can use them as global or 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:

global.add_after_upgrade_pfile=/home/oracle/pfiles/global_add_after.ora
upg1.del_after_upgrade_pfile=/home/oracle/pfiles/upg1_del_after.ora

An example

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/12.2.0.1
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  _exclude_seed_cdb_view=false and _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_del_during.ora
O7_DICTIONARY_ACCESSIBILITY

# upg1_add_after.ora
audit_trail='none'

I create them all in my /home/oracle/pfiles directory.

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'

And O7_DICTIONARY_ACCESSIBILITY is not set anymore.

This looks very good!

–Mike