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
_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
Hi,
Using non-cdb only, can you change the name of the DB being upgraded and also keep its original files intact?
For Example if upgrading an 11g DB orcl11 to 19c can the name be changed to orcl19 and a new set of datafiles created leaving the old 11g ones intact as well. I could only see parameters for CDB and PDB architecture not non-cdb.
Hi Hamid,
no, you can’t – an upgrade happens always in place and hence, the database keeps its name.
Cheers,
Mike