The Oracle 19c database is replete with new capabilities and enhanced features, but customers can only take advantage of these improvements by upgrading. Historically, upgrades and migrations were painful, sometimes lengthy efforts that few database administrators (or organizations) looked forward to. Database upgrades are infrequent events for most environments, and each timestep that worked for prior versions is unlikely to apply to the current database. Source and target versions combine to dictate a potentially lengthy and confusing series of actions for checking compatibility, verifying prerequisites, disabling deprecated and unsupported features, and updating configurations.
Fortunately, among the improvements introduced alongside Oracle 18c and 19c is a tool that greatly streamlines and simplifies the process of upgrading Oracle databases: AutoUpgrade!
As AutoUpgrade is a new tool, database administrators may be reluctant to entertain it, instead deferring to more familiar methods they've used in the past. One objection to AutoUpgrade is that it's too easy. There's an expectation that upgrades must be difficult. AutoUpgrade's simple setup and interface make it easy to assume that it's not powerful or comprehensive enough for any except the most simple of environments. In fact, AutoUpgrade boasts an impressive set of checks and capabilities behind its unassuming interface.
Among these features:
When running an upgrade, AutoUpgrade performs many operations DBAs might normally run manually. This includes creating a Guaranteed Restore Point, collecting statistics on fixed objects, compiling invalid objects, emptying the Recycle Bin, removing hidden (underscore) parameters, and updating the database time zone. Of course, users still have the ability to fine-tune which steps AutoUpgrade runs, allowing it to be tailored to suit nearly any environment.
Invoke AutoUpgrade by referencing a configuration file and a mode:
We'll cover the configuration file in a moment. First, let's discuss the four modes.
The AutoUpgrade Configuration File
The flexibility and utility of AutoUpgradeis driven through its configuration file. These files can range from minimal settings for upgrading a single database with defaults to detailed instructions for controlling (and even scheduling) multiple databases across an enterprise. While a single configuration file can manage complex, conditional upgrades for dozens or even hundreds of databases, the syntax is easy to read and understand.
Configuration files are built from name-value pairs that AutoUpgradereads at run time. Each entry consists of a prefix, a parameter, and a value. For example:
Here, global and db1 are prefixes, and autoupg_log_dir and target_home are parameters separated from their values by the equal sign.
Putting this into practice, a minimal configuration for AutoUpgrade might look like this:
AutoUpgrade can generate a sample configuration template to build from using the -create_sample_file flag:
Every entry in the configuration file includes a global prefix (to denote parameters applied to all operations) or a user-defined prefix that groups settings for individual instances. Global settings are used to set values that apply to multiple upgrades, and every configuration file will include at least one global parameter, the AutoUpgrade log directory. A more realistic example of a global configuration might include the target home path and version:
Local settings are prefixed by a user-defined value and links parameters to a specific upgrade; in the example above, db1. Local settings include the host and database SID. AutoUpgrade matches local parameters to a database by reading values for host, SID, and source and/or target database home. A single configuration file can independently manage upgrades for multiple databases in a single home, multiple homes, and multiple hosts.
Some parameters may be set both globally and locally. In this case, the local setting for a database overrides the global value in the configuration. This is useful when an environment has many databases that mostly follow a convention with a few outliers. The conventional values can be set as a global parameter, and the exceptions managed through a local setting:
AutoUpgrade's detailed control of upgrades extends beyond just managing operations and directories. It can add and remove database parameters during and after the upgrade:
The files for adding parameters are formatted like a regular database pfile, with each parameter=value pair to be added on a separate line. Those for deleting parameters is a list of parameters to be deleted, one per line, without an equal sign or value.
AutoUpgrade can also remove hidden (underscore) parameters:
This is a global parameter only without a local counterpart. For environments where most hidden parameters need to be deleted, this option can be combined with add_after_upgrade_pfile to control the environment.
Some environments may have requirements to call custom scripts before or after the upgrade. AutoUpgrade can call these scripts as part of the process. This localizes control of the entire upgrade process within AutoUpgrade, and users can leverage the interface and reporting features to include these custom needs. In Linux, these are shell scripts with a .sh suffix. In Windows, they are batch scripts with either a .cmd or .bat suffix or PowerShell scripts with a .ps1 suffix:
By default, running AutoUpgrade takes the user to an interactive upg> console prompt:
This may catch some first-time users off guard, as the prompt isn't an intuitive or expected part of an automatic utility. When the AutoUpgrade job completes, it will report its status and return to the shell:
Shorter operation line analyze won't normally have much need for interaction, but upgrade and deploy will take time. The console affords users the opportunity to monitor and manage the progress of these jobs. The full set of commands is displayed by running help at the prompt:
Helpful commands include lsj, logs, and status.
Jobs are managed by the abort, resume, and restore commands. Abort may be misleading; aborting a job is often thought of as an unrecoverable or non-resumable operation, but in AutoUpgrade, it's a mechanism for stopping or pausing a job. After aborting a job, users can resume the upgrade or restore the database to its pre-upgrade state. If a job fails for any reason, users have the option of fixing the problem and resuming the job or restoring the database.
AutoUpgrade can be called with the no console option to suppress the console for silent background operation. No special environment configurations or settings are necessary to run AutoUpgrade.
Upgrades capture the interest of the enterprise, as stakeholders and database administrators have competing interests. DBAs want to focus on the technical aspects of the upgrade without interruptions from people asking for status, while the rest of the organization wants regular progress updates. AutoUpgrade includes an HTML reporting option that offers stakeholders accurate, up-to-date visibility into the upgrade progress.
A few extra steps are necessary to enable this, but they're well worth the effort. Start a simple web server on the upgrade host:
This may seem like a trivial thing, but this level of openness builds trust and confidence in the process by offering end users access to the same communication and information available to administrators.
AutoUpgrade is included in the Oracle version 19.3 database software download, and updated versions are part of each Release Update. The latest version of AutoUpgrade is always available from My Oracle Support under Note 2485457.1.
AutoUpgrade doesn't require any special licensing, and it can be used for Standard and Enterprise Edition databases. There are no plugins or agents to install. It's a roughly 4-megabyte JAR file that requires Java 8 or later, satisfied by the target Oracle home if it's not already part of the environment.
Below are some valuable, real-world recommendations from our experience using AutoUpgrade in particular and upgrading to Oracle 18c and 19c specifically:
AutoUpgrade is the preferred method for upgrading Oracle databases and advances the process for DBAs. It's a robust, powerful utility hiding behind an unassuming interface that makes the once difficult, sometimes painful, task of upgrading databases easier and more reliable.
One of the sayings we have at Viscosity is that our customers "have four aces in their pocket." Over the next 2 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!