Vishwanath Nayak : January 2017

This article provides an overview of some of the options for upgrading existing databases to Oracle 12c Release 1. Upgrades can be very complicated, so you must always read the upgrade manual, and test thoroughly before considering an upgrade of a production environment.

This article does not include any references to pluggable databases, as in most cases the upgrade to 12c is a prerequisite of conversion to pluggable databases.

Supported Upgrade Paths

Direct upgrades to 12c are possible from existing databases with versions 10.2.0.5+, 11.1.0.7+, or release 11.2.0.2+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

When you are using the DBUA or performing a manual upgrade, you will need both versions of the database present to complete the operation. Typically, you will be installing Oracle 12c into a new ORACLE_HOME on the existing server.

Database Upgrade Assistant (DBUA)

The preferred upgrade method is to use the Database Upgrade Assistant (DBUA), a GUI tool that performs all necessary prerequisite checks and operations before upgrading the specified instances. The DBUA can be started directly from the Oracle Universal Installer (OUI) during installation, or separately after the software installation is complete.

Make sure the database is started and run the database upgrade assistant.

$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ dbua

Select the "Upgrade Oracle Database" option, then click the "Next" button.

Select the database you wish to upgrade and click the "Next" button.

It can take a few minutes before the next screen is available. Wait at the "Getting database information. Please wait..." message.

Wait while the prerequisite checks takes place.

If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. When you are happy with the prerequisites, click the "Next" button.

Amend the upgrade options if necessary, then click the "Next" button.

Select the desired management options, then click the "Next" button.

If you need to move the database files and/or the fast recovery area, check the relevant option and enter the destination. Click the "Next" button.

If this database is the only one using the 11g listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, so do now. Click the "Next" button.

Select the recovery options for use in the event of an upgrade failure, then click the "Next" button.

If you are happy with the summary information, click the "Finish" button.

Wait while the upgrade takes place. When the upgrade is complete, click the "Upgrade Results" button.

Check the upgrade results, then click the "Close" button to leave the DBUA.

The upgraded database is now available.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 17:48:01 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT name, open_mode FROM v$database;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

SQL>

Remember to alter any script or profile files that contain references to the ORACLE_HOME environment variable.

The DBUA can be run in silent mode if the correct command line parameters are passed.

Manual Upgrades

Backup the database before starting the upgrade process. In the event of a failure, you will need this backup to allow you to recreate your starting point.

To run the new Pre-Upgrade Information Tool, copy the "preupgrd.sql" and "utluppkg.sql" scripts from the 12c home to a location accessible by the database that needs upgrading. In this example I used a directory under "/tmp".

$ mkdir -p /tmp/upgrade
$ cp /u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/preupgrd.sql /tmp/upgrade
$ cp /u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/utluppkg.sql /tmp/upgrade

Make sure the ORACLE_BASE is set and run the "preupgrd.sql" script from SQL*Plus.

$ export ORACLE_SID=orcl
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES
$ cd /tmp/upgrade
$ sqlplus / as sysdba

SQL> @preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
      ************************************************************

Results of the checks are located at:
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log

Pre-Upgrade Fixup Script (run in source database environment):
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):
 /u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

      ************************************************************

         Fixup scripts must be reviewed prior to being executed.

      ************************************************************

      ************************************************************
                   ====>> USER ACTION REQUIRED  <<====
      ************************************************************

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.

           You MUST resolve the above errors prior to upgrade

      ************************************************************

SQL>

The Pre-Upgrade Information Tool produces 3 scripts.

preupgrade.log : The results of all the checks performed. You need to check this to see if it is safe to continue with the upgrade.
preupgrade_fixups.sql : A fixup script that should be run before the upgrade.
postupgrade_fixups.sql : A fixup script that should be run after the upgrade.

Assuming there are no show-stoppers in the "preupgrade.log" file, run the "preupgrade_fixups.sql" script. Any changes it can't perform are flagged as "MANUAL ACTION SUGGESTED".

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2013-07-24 15:00:32 Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...