Oracle DBA Centre: Upgrading to Oracle Database 12c from 11g

This article provides an overview of some of the options for upgrading existing databases to Oracle 12c Release 1 and 2. Upgrades can be very complicated, so you must always read the upgrade manual (12cR1, 12cR2), 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 are possible from existing databases with the following versions.

Destination                Source
===========                ======
12c Release 1 (12.1.0.2)   10.2.0.5+, 11.1.0.7+, 11.2.0.2+ or 12.1.0.1
12c Release 2 (12.2.0.1)   11.2.0.3+ or 12.1.0.1 or 12.1.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.

Prerequisities

There are a lot of potential prerequisites, which will vary depending on the functionality you are using in your source version. Please check them in the documentation, or you may fail to upgrade your database successfully.

In addition to other prerequisites, you will always be asked to do the following actions

Gather dictionary statistics. For a non-CDB database you will do the following from an SQL prompt.

CONN / AS SYSDBA
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

For a container database you will use the following command to perform the action in all containers.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gather_dict_stats -- --x"EXEC DBMS_STATS.gather_dictionary_stats"

Purge the recycle bin using the following from an SQL prompt.

CONN / AS SYSDBA
PURGE DBA_RECYCLEBIN

Compile all invalid objects. For a non-CDB database you will do the following from an SQL prompt.

CONN / AS SYSDBA
@?/rdbms/admin/utlrp.sql

For a container database you will use the following command to perform the action in all containers.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b comp_invalid_objs -- --x"@?/rdbms/admin/utlrp.sql"

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. The screen shots below are from a 12.1 upgrade, but the 12.2 upgrade is similar.

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
#export ORACLE_HOME=/u01/app/oracle/product/12.2.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.

In 12.1 the database is shipped with a "preupgrd.sql" script, but this has been replaced by the "preupgrade.jar" file downloaded from MOS 884522.1. The "preupgrade.jar" file is shipped with later releases, but you should always download the latest version lined from MOS 884522.1 as it is constantly being improved. Don't rely on the shipped script!

The way you run the JAR file depends on what you are trying to achieve, as described in the upgrade documentation for your version. The following example does the pre-upgrade checks for the whole database, including all PDBs if you are using a container database.

$ORACLE_HOME/jdk/bin/java -jar /path/to/preupgrade.jar TERMINAL TEXT

Since we've not specified and output directory, any fixup scripts will be located in the "ORACLE_HOME/cfgtoollogs/dbunique_name/preupgrade" directory.

The remainder of this section is based on the output of the "preupgrd.sql" script, which was used when this article was originally written. The output is similar to that of the "preupgrade.jar", which is why it remains here as an example of the output, and the upgrade steps are the same.

To run the old 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...