DATABASE UPGRADE IMPROVEMENTS

DATABASE UPGRADE IMPROVEMENTS

Whenever a new Oracle version is announced, the immediate challenge that every DBA confronts is the upgrade process. In this section, I will explain the two new improvements introduced for upgrading to 12c.

Pre-upgrade script

A new and much improved pre-upgrade information script, preupgrd.sql, replaces the legacy utlu[121]s.sql script in 12c R1. Apart from the preupgrade checks verification, the script is capable of addressing the various issues – in the form of fixup scripts – that are raised during the pre-post upgrade process.

The fixup scripts that are generated can be executed to resolve the problems at different levels, for example, pre-upgrade and post upgrade. When upgrading the database manually, the script must be executed manually before initiating the actual upgrade procedure. However, when the Database Upgrade Assistant (DBUA) tool is used to perform a database upgrade, it automatically executes the pre-upgrade scripts as part of the upgrade procedure and will prompt you to execute the fixup scripts in case of any errors that are reported.

The following example demonstrates how to execute the scripts:

SQL> @$ORACLE_12GHOME/rdbms/admin/preupgrd.sql

The above script generates a log file and a [pre/post]upgrade_fixup.sql script.  All these files are located under the $ORACLE_BASE/cfgtoollogs directory. Before you continue with the real upgrade procedure, you should run through the recommendations mentioned in the log file and execute the scripts to fix any issues.

Note: Ensure you copy the preupgrd.sql and utluppkg.sql scripts from the 12c Oracle home/rdbms/admin directory to the current Oracle database/rdbms/admin location.

Parallel-upgrade utility

The database upgrade duration is directly proportional to the number of components that are configured on the database, rather than the database size. In previous releases, there was no direct option or workaround available to run the upgrade process in parallel to quickly complete the overall upgrade procedure.

The catctl.pl (parallel-upgrade utility) that replaces the legacy catupgrd.sql script in 12c R1 comes with an option to run the upgrade procedure in parallel mode to improve the overall duration required to complete the procedure.

The following procedure explains how to initiate the parallel (with 3 processes) upgrade utility; you need to run this after you STARTUP the database in UPGRADE mode:

cd $ORACLE_12_HOME/perl/bin

$ ./perl catctl.pl –n 3 -catupgrd.sql

The above two steps need to be run explicitly when a database is upgraded manually. However, the DBUA inherits the both new changes.