By Michael Messina

Oracle upgrades have always been a challenge – all the steps to take, all the preparation work for an upgrade execution, the testing of your applications with the upgraded version. At times, this has been such a challenge the upgrades are postponed or never even happen. Oracle has given us some help with at least the upgrade process itself us the AutoUpgrade Utility.

The AutoUpgrade is used to assist with automating and simplifying parts of the pre-upgrade, upgrade, and post-upgrade for the Oracle Database from one major release version to another. While it does assist with some automation, it does not actually automate the upgrade process completely. However, it does go a long way toward automated upgrade processing.

The AutoUpgrade Utility uses the autoupgrade.jar. The process only handles the database upgrade. The DBA will still need to install the new version binaries, apply patches, etc.

The AutoUpgrade can handle the upgrade of multiple databases in an environment within a single execution. This greatly simplifies the upgrade process in environments where multiple databases exist and need to be upgraded.

Support for the AutoUpgrade Utility

The AutoUpgrade Utility does have some limitations on what versions it can be used for and some requirements for the utility. The following are the versions that the AutoUpgrade Utility can be used with.

• Support for Oracle Database 12c Release 2 (12.2) with January 2019 Patch update and Later
  • Must download the AutoUpgrade kit from My Oracle Support Document 2485457.1
• Support for Oracle Database 18c (18.5) and Later
  • Must download the AutoUpgrade kit from My Oracle Support Document 2485457.1
• Support for All Oracle Database 19c versions
  • jar exists by default no additional download needed
• Oracle Recommends always using the latest autoupgrade.jar by downloading from My Oracle Support Document 2485457.1
• Oracle home must contain Java 8, All ORACLE_HOMEs 12.1.0.2 and later contain Java 8 (Source and target ORACLE_HOMES need checked)

$ORACLE_HOME/jdk/bi/java –version

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -version

Database Upgrade Process Using AutoUpgrade

All database upgrades follow, at a minimum, three basic parts. The three parts discussed here are not intended to be the overall database upgrade project. DBAs must still consider processes for things like unit testing and load testing an application against an upgraded database. Decisions still need to be made about what database version to upgrade to, patch requirements, OS requirements, etc. This is to only outline with basic steps of the actual database upgrade.

1. Precheck – determines what is needed to be done prior to database upgrade.  This process includes database and non-database specific checks.  Such as binary install, binary patches, disk space, etc., other part of precheck is database-specific such as deprecated parameters, parameter settings, deprecated features, etc.
2. Gap resolution – is addressing all items found in the precheck and resolve prior to upgrade execution.  This step is all about what has to be done to ensure a smooth database upgrade.
3. Database upgrade – includes basic post-upgrade checks immediately after completion of upgrade.

The AutoUpgrade Utility supports several parts of the overall database upgrade and steps it supports are essential to ensuring a successful upgrade. By using the parts of the upgrade process independently, things like pre-checks can be completed without executing the entire upgrade process only to find out precheck issues have to be addressed. In this section, we will identify the parts of an upgrade and the “modes” for the AutoUpgrade Utility to support those parts of an upgrade.

The AutoUpgrade Utility can be run in a few different modes – each mode intended for a specific purpose to support a specific part of the upgrade process. There are basically three “modes” to the AutoUpgrade, which loosely correspond to understanding the scope, preparing and planning for the upgrade, and executing the upgrade.

Analyze Mode

AutoUpgrade in Analyze Mode is the first step in the overall upgrade process. The first step in any upgrade process is to check and prepare the database for a successful upgrade. This mode is an online operation and performs a read-only analysis of the database to determine if any issues/deficiencies or dependencies for a successful upgrade are required. The performance impact of running this against a live database is minimal and generates two output files that must be reviewed the <ORACLE_SID>.html  and <ORACLE_SID>_preupgrade.log which are reports of the results of the analyze mode execution.

Fixup Mode

Fixup mode identifies issues that require attention prior to being able to execute a successful upgrade of the database. The mode identifies both required fixes that the AutoUpgrade can address automatically as well as ones that require manual intervention to address. As indicated the ones that can be handled automatically will be handled, however the key to this step is the ones that require manual intervention. These will all need to be addressed by the DBA and cleared prior to execution of the upgrade via the Deploy/Upgrade mode.

Deploy/Upgrade Mode

The deploy/upgrade mode executes that database upgrade for the databases defined in the configuration file for the upgrade. The deploy/upgrade mode will execute a precheck and ensure there are no items that it can see that would keep the upgrade from being successful. Any items that can be fixed by the process automatically will be done automatically and does not affect the upgrade process. The process will execute the upgrade and show the upgrade progress during the upgrade. Once the upgrade is complete it will perform basic post-upgrade checks and steps to ensure the upgrade was successful. As part of the post-processing, the process will also execute any custom script processes that you have told the process to execute once the upgrade of the database is completed.

AutoUpgrade Stages

• SETUP Stage: The Autoupgrade job manager creates and prepares to start a job for the AutoUpgrade process.
• PREUPGRADE Stage: Run the jobs that will perform basic system configuration level sanity checks and verification, such as memory, free space, OS Compatibility etc.
• PRECHECKS Stage: Runs the jobs that will analyze source Oracle Database to be upgraded and determine if the database meets requirements to be upgraded.
• PREFIXUPS Stage: Based upon the findings of the PRECHECKS stage, perform pre-upgrade fixups prior to the start of the upgrade, e,g, gather dictionary statistics, etc.
• DRAIN Stage: Drains database sessions from database instance to prepare for shutting down listener and database. Draining is the process of allowing sessions to complete and disconnect them.
• DBUPGRADE Stage: AutoUpgrade performs and executes the actual upgrade including compiling any invalid objects that are found post-upgrade.
• POSTCHECKS Stage: Executes checks on the Upgraded Database immediately after the upgrade to ensure the core upgrade completed successfully
• POSTFIXUPS Stage: Once the POTCHECKS have come through clean=n, this stage is focused on completing and post-upgrade fix us tasks identified/needed. For e.g. upgrading the time zone.
• POSTUPGRADE Stage: This is the final stage of the upgrade wherein the source Oracle database configuration files to the target Oracle database configuration, and starts up the upgraded Oracle Database in the new Oracle home. Additionally, this stage also includes deploying and performing the Post upgrade Checks and Fixes
• With Deploy mode confirms that the upgrade has succeeded
  • Executes post-upgrade checks
  • Execute custom scripts as indicated by you as per configuration file
  • Execute automatic postupgrade fixups
  • Moves database files such as ora, tnsname.ora, and listener.ora to new ORACLE_HOME
  • Starts Upgraded database in New ORACLE_HOME

Using the AutoUpgrade Utility

Prepare

To utilize the AutoUpgrade Utility, we must first prepare/set up a configuration that will guide the AutoUpgrade execution. This section outlines that preparation/setup tasks/activities:

1. First, create sample configuration files using the AutoUpgrade Utility. Good to take a look at these and review the options in the files, the
   • $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -create_sample_file settings
   • $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -create_sample_file config
2. Make a copy of a config file for you to configure
   • -rw-r–r–. 1 oracle oinstall    2007 Jan 21 23:35 sample_autoupg.cfg
   • -rw-r–r–. 1 oracle oinstall    1626 Jan 21 23:35 sample_config.cfg
   • cp sample_config.cfg mydb.cfg
   • vi mydb.cfg
3. The Global Configuration File Parameters here is not intended to be a complete list but a guide on most commonly used and offer some indication on the most used configuration items.
   • A PFILE that contains initialization parameters to add after upgrade
     • global.add_after_upgrade_pfile=/u01/app/oracle/autoupgrade/addinitparametersafter.ora
   • A PFILE that contains initialization parameters to remove during upgrade
     • global.del_during_upgrade_pfile=/u01/app/oracle/autoupgrade/delinitparametersduring.ora
   • Tell Upgrade process to remove all _ parameters for upgrade
     • global.remove_underscore_parameters=yes
   • Drop guaranteed restore point after upgrade
     • global.drop_grp_after_upgrade=yes     (default is no)
4. Database Specific Configuration File Parameters, this is not intended to be a complete list but a guide on most commonly used and offer some indication on the most used configuration items.
   • db unique name of database to be upgraded for specified database
     • mydb.dbname=myd
   • Source database home (required)
     • mydb.source_home=/u01/app/oracle/product/18.0.0.0/dbhome_1
   • Target database home (required)
     • mydb.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
   • The Oracle SID of DB to upgrade (required)
     • mydb.sid=mydb
   • Start Time of upgrade for database (required)
     • mydb.start_time=now
   • Pluggable databases to upgrade. (Suggest to always include and set to *)
     • mydb.pdbs=*
   • Location for logging for database (required)
     • mydb.upgrade_node=oraclevm
   • Upgrade node that operation will run on (Required)
     • mydb.upgrade_node=oraclevm
   • Run utlrp as part of the upgrade (default yes suggest always yes)
     • mydb.run_utlrp=yes
   • Upgrade database timezone if needed (default yes suggest always be yes)
     • mydb.timezone_upg=yes
   • Target database version
     • mydb.target_version=19.3
5. Example Final configuration file we will use for our example here upgrade for mydb from 18.8 to 19 as part of the example.
   • global.autoupg_log_dir=/u01/app/oracle/autoupgrade/logs
   • global.remove_underscore_parameters=yes
   • # Database Specific
   • mydb.dbname=MYDB
   • mydb.source_home=/u01/app/oracle/product/18.0.0.0/dbhome_1
   • mydb.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
   • mydb.sid=mydb
   • mydb.start_time=now
   • mydb.pdbs=*
   • mydb.log_dir=/u01/app/oracle/autoupgrade/logs/mydb
   • mydb.upgrade_node=oraclevm.localdomain
   • mydb.run_utlrp=yes
   • mydb.timezone_upg=yes
   • mydb.target_version=19

Execute AutoUpgrade in Analyze Mode

Now that we have prepared/setup a configuration file to guide the execution of the AutoUpgrade, we can used that to go through the three upgrade steps outline in the prior section. This section walks through the execution of the AutoUpgrade in “analyze mode.”

Sample Command:

Sample Output:

Then, make sure we are going to have a good upgrade by reviewing logs generated files from analyze steps. A sample from what was generated in our run is also included. The files that we should include in the review are keeping in mind that the checklist.* files contain the same information in a different format so you can pick the one you would like to use for review:

• [$ORACLE_SID]_cfg
• [$ORACLE_SID]_json
• [$ORACLE_SID]_xml
• [$ORACLE_SID]_html
• log
• prechecks_[$ORACLE_SID].log

Review log files: We are looking for Warning Items where some pre-action needed/desired, probably can be handled in the FIX UP phase or for Critical Items, which most often are handled manually as Fixup may not be able to crisis.

We want to check to see if the analyze got any errors from the execution. Below is an example of what you find in the error log. In this example, the database needs to be in ARCHIVE_MODE in order to proceed.

Review the precheck checkout details in precheck logs from the analyze. We can check the analyze full results in the [$ORACLE_SID]_preupgrade.log or the [$ORACLE_SID]_preupgrade.html. In our example, the log contains a couple of items we want to highlight as there are critical to the upgrade. Keep in mind, that items such as Warning or Informative most probably do not need any attention at all. But review them, assess, and decide. Critical items must be fixed and addressed before proceeding.

Example of errors/issues that need to be addressed:

For this one, you want to make sure you set upgrade dst to yes.

This is an example where everything checks out and is ok.

Take any required actions prior to upgrade, make our corrections, then re-run analyze. As an example below, we are addressing the issue of having the database in archive log mod and in flashback mode.

Re-execute the analyze for any items that required manual attention, review, and process the results.

Execute AutoUpgrade in Fixup Mode

Now that we have executed the autoupgrade in analyze mode, we can run the autoupgrade in fixup mode to address any open items that the autoupgrade analyze mode identified that can automatically be fixed. This section will walk through the execution of the autoupgrade utility in “FIXUP mode.”

Using the upgrade.jar and the fixup mode, execute the fixups.

Check the logs from the fixups execution.

Locate the logs.

Check prefixups log for any issues.

Execute AutoUpgrade in Deploy Mode to Upgrade Database

Now that we have identified any pre-upgrade issues that need to be addressed, addressed all pre-upgrade issues that required manual intervention resolved we can proceed with the actual upgrade of the database.  This section will walk through the execution of the upgrade utility in “Deploy Mode” which executes the steps and executed the actual database upgrade and post-upgrade steps.

Upgrade

Using the upgrade.jar and our configuration, execute the upgrade.

Post Upgrade (Manual Steps)

Check our /etc/oratab we can see it is updated with New ORACLE_HOME by the autoupgrade.jar

Reset our ORACLE_HOME for the upgraded database and check our version

Appendix A: Special Notes for Now and the Future

For RAC ** Prior to July 2019 version of the AutoUpgrade later versions, cluster_database can be left TRUE.

DataGuard * Inclusion of this being worked on, so stay tuned.

• Change ORACLE_HOME for standby instance
• Relocate any orapw, iniit, tns, etc.
• Restart Standby Instance(s)
• Enable Dataguard broker to stop redo transport

About the Author

Michael R. Messina, Senior Managing Consultant

Rolta AdvizeX Cloud Managed Services

[email protected]

Mike Messina is Senior Managing Consultant for AdvizeX Managing and manages Oracle, MySQL and PostgreSQL databases.

Mike is an Oracle Ace, Oracle Certified Professional, MySQL Cloud Service 2018 Certified Implementation Specialist with over 25 years of database management experience.

Mike has presented at Oracle Open World, Collaborate, Rocky Mountain Oracle Users Group (RMOUG) as well as regional and local users group across the U.S.

Interested in Writing for SELECT?

Are you interested in getting your writing published and sharing your knowledge with other Oracle users? Quest Oracle Community is looking for writers to contribute to the SELECT Journal for our Quest IOUG Database & Technology Community audience. We are looking for writers with expertise in a variety of topics, including the following:

• Database sharding
• Database security
• Database performance
• Database development
• Database auditing
• And more!

For more information about how to get involved, please email [email protected].