Upgrade Enhancements: Oracle 12c
Fortunately for us, Oracle seems keen on making the lives of DBA’s simpler and Oracle 12c is a major step in that direction. This simplification cascades itself and will become clearer, when we take a look at the changes made to the steps involved in database upgrades to 12c. Not only is it now, a simpler process, but has also grown to be more efficient and less time consuming.
A Database Upgrade Project can fundamentally be said to consist of the following phases.
Database Upgrade
Oracle supports the upgrade of terminal releases 10.2.0.5, 11.1.0.7 and versions 11.2.0.2 or higher, directly to 12.1. All other releases will involve an indirect upgrade path.
12.1 gives us two choices for database upgrades, the GUI based Database Upgrade Assistant (DBUA) or the command line based manual upgrade. MOS 2085705.1 comprehensively lists the details on each upgrade step and the options available. The database upgrade steps can be divided into 3 distinct categories: Pre Upgrade Steps, Database Upgrade Steps, Post Upgrade Steps
Pre Upgrade Steps
Before running the database upgrade it is extremely crucial to run a health check on the database, capture all crucial information and run the pre checks to ensure that the database has been prepped for upgrade. Starting with Oracle 12c the pre-upgrade script “preupgrd.sql” has replaced the standard “utlu*i.sql” scripts. (e.g. utlu112i.sql for 11.2.x), which makes the name independent of the version and simply more straightforward.
For 12.1 copy the preupgrd.sql and utluppkg.sql scripts to the source Oracle Home and execute the preupgrd.sql from there. In order to run this script, the database should not be in read only mode as it may create a few registry$ tables and insert data into existing upgrade tables. Further, it will generate the following 3 files under source home that need to be reviewed.
Default Location: $ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade
preupgrade.log : A logfile with the results of the pre-upgrade checks
preupgrade_fixups.sql : A list of steps to executed in the SOURCE Oracle Home before upgrade
postupgrade_fixups.sql : A lists of steps to be executed in the NEW Home AFTER upgrade
The pre upgrade tool displays information but DOES NOT execute it .
The preupgrade_fixups.sql also displays certain steps, like gathering dictionary statistics that, though not necessary, are highly recommended to improve the upgrade performance.
Once the pre upgrade logs have been reviewed for deprecated parameters, recommended settings and all the pre checks have been satisfactorily completed, we can move to the next phase.
Database Upgrade Steps
Upgrade Parallelism is a new feature introduced using the new upgrade utility called catctl.pl. This perl based utility acts as a wrapper script, to execute the standard catupgrd.sql. It enables the components that do not have dependencies to be upgraded in parallel. Additionally it introduces the capability to run the upgrade in phases and has the ability to pick up from the phase that it failed on, when re-run.
Database upgrade depends on the number of components, their internal dependencies and the degree of parallelism (default: 4) provided to reduce the overall upgrade time. Once the database has been started from the NEW home in the upgrade mode using “startup upgrade”, we can call the catctl.pl script as follows.
$ORACLE_HOME/perl/bin/perl catctl.pl –l -n 6 catupgrd.sql
If the “-l” option is not used with catctl, the logs will be generated in the default log location i.e. DIAGNOSTIC_DEST/cfgtoollogs/tool_name/SID/upgrade<n>, where n specifies the incarnation number of the upgrade attempt and you can find one log file present for each parallel thread.
Please note that the standard post upgrade script, catuppst.sql, is run automatically as a part of the new upgrade tool unless there are errors. So check the logs and if this script has failed or not been run, it has to be executed manually. This script performs steps that do not need the database to be in upgrade mode, for instance removing the no longer required EM repository.
Starting with 12c EM DB Control has been superseded by EM Express, and hence the EM repository is not essential.
Once the execution of catctl.pl completes, the logfiles need to be reviewed for errors and the status and version of each component needs to be verified. This can be done using Post Upgrade Status Tool utlu121s.sql
Post Upgrade Steps
Once the database has been upgraded successfully, do remember to set the compatible parameter to ‘12.0.0’. It is also recommended to upgrade the database Time-zone to the latest version.
Another thing to be careful about when using DBUA is that, if a PSU/SPU/BP has been applied to the 12.1 home prior to upgrade, then datapatch has to be run separately in order to apply the sql changes corresponding to the patch. This is not the case for manual upgrades as catctl.pl takes care of applying the database changes for the corresponding patches.
The new features introduced in 12c that we discussed above can be summarized as
- SQL Script Automation for pre upgrade tool.
- Upgrade parallelism and restart from failure option during upgrades using catctl.pl
- The new default log locations with incarnation details.
- EM DB Control superseded by EM Express
Apart from this there are a few other things that have also changed.
- DBUA now has 2 panels, with the one on the right detailing the phase of execution.
- DBUA has been integrated with RMAN to provide guaranteed restore points
- OEM Cloud Control can be used for Mass Database Upgrades along with GI, RAC and Standby Database Upgrades.
- Oracle XML Db is now a mandatory component and if not found in the source database, will be installed during upgrade.
These new features have helped in reducing the database upgrade time by nearly 40%. This should help the DBA focus more on perhaps the most critical phase of an upgrade project, i.e. Testing.
It is imperative that thorough testing is carried out by simulating application load, running batch jobs and tuning them to achieve the best possible performance. Real Application Testing (RAT) is one of the licensed tools offered by oracle to replay database loads and test performance. SQL Plan Management techniques should also be leveraged to form baselines and tune the SQL Tuning Sets.
All in all, the one mantra that should never be abandoned by a DBA to achieve a successful upgrade must surely be “Test, Test and Test Again!!! “