8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Upgrading to Oracle Database 12c

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.

Related articles.

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.

Prerequisites

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 Operation

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

Select Database

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.

Prerequisite Checks

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.

Prerequisite Checks

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

Upgrade Options

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

Management Options

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.

Move Database Files

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.

Network Configuration

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

Recovery Options

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

Summary

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

Progress

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

Results

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.

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...

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


**********************************************************************
Check Tag:     DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary:   Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low

     Database has a maximum process count of 150 which is lower than the
     default value of 300 for this release.
     You should update your processes value prior to the upgrade
     to a value of at least 300.
     For example:
        ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
     or update your init.ora file.
**********************************************************************


PL/SQL procedure successfully completed.


**********************************************************************
Check Tag:     EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary:   Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.
**********************************************************************


PL/SQL procedure successfully completed.


**********************************************************************
Check Tag:     DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary:   Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing DBMS_LDAP dependent objects

     Database contains schemas with objects dependent on DBMS_LDAP package.
     Refer to the Upgrade Guide for instructions to configure Network ACLs.
     USER APEX_030200 has dependent objects.
**********************************************************************


PL/SQL procedure successfully completed.


**********************************************************************
Check Tag:     AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/oraolap/admin/catnoamd.sql script before or
     after the upgrade.
**********************************************************************


PL/SQL procedure successfully completed.


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************


PL/SQL procedure successfully completed.

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


PL/SQL procedure successfully completed.


           **************************************************
                ************* Fixup Summary ************

 4 fixup routines generated INFORMATIONAL messages that should be reviewed.


PL/SQL procedure successfully completed.

**************** Pre-Upgrade Fixup Script Complete *********************

PL/SQL procedure successfully completed.

SQL>

As you can see, in this case, the suggested changes were as follows.

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

SET ECHO ON;
SET SERVEROUTPUT ON;
-- emremove.sql scrip located in the 12c home.
@/u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/emremove.sql

-- Removing this before the upgrade will result in the errors shown below.
-- These errors are not show-stoppers, but if you want a cleaner run through,
-- remove this feature after the upgrade.
@?/olap/admin/catnoamd.sql

EXECUTE dbms_stats.gather_dictionary_stats;

-- Shutdown the database.
SHUTDOWN IMMEDIATE;

Copy the parameter and password files from the old home to the 12c home.

$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora /u01/app/oracle/product/12.1.0.1/db_1/dbs
$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl /u01/app/oracle/product/12.1.0.1/db_1/dbs

If the "preupgrade.log" file contains references to deprecated initialization parameters, make the suggested changes to the parameters now before continuing.

Edit the "/etc/oratab" file, setting the new ORACLE_HOME value.

orcl:/u01/app/oracle/product/12.1.0.1/db_1:Y

Make sure the environment is set to the 12c home.

$ export ORACLE_SID=orcl
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES

Start the database in upgrade mode.

Remember, if you are upgrading an existing database using the multitenant architecture, all PDBs need to be opened in upgrade mode, or they won't be upgraded.

$ sqlplus / as sysdba

SQL> STARTUP UPGRADE;
SQL> --ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE FORCE;
SQL> EXIT;

Run the new Parallel Upgrade Utility (catctl.pl).

$ # Regular upgrade command.
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

$ # Shorthand command.
$ $ORACLE_HOME/bin/dbupgrade

You can alter the level of parallelism using the "-n" parameter. For example, the following command runs the upgrade with a parallel level of 8.

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql

You can see an example of the output from this utility here.

Startup the upgraded database and run the "utlu121s.sql" script to check the summary of the upgrade results.

$ sqlplus / as sysdba

SQL> STARTUP;
SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool           07-24-2013 17:24:18
.
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
.
Oracle Server
.                                      UPGRADED      12.1.0.1.0  00:16:48
JServer JAVA Virtual Machine
.                                         VALID      12.1.0.1.0  00:04:47
Oracle Workspace Manager
.                                         VALID      12.1.0.1.0  00:01:17
OLAP Analytic Workspace
.                                         VALID      12.1.0.1.0  00:00:53
Oracle OLAP API
.   SP2-0310: unable to open file "xoqsys.sql"
.   ORA-01917: user or role "OLAPSYS" does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.                                         VALID      12.1.0.1.0  00:00:46
Oracle XDK
.                                         VALID      12.1.0.1.0  00:00:48
Oracle Text
.                                         VALID      12.1.0.1.0  00:01:07
Oracle XML Database
.                                         VALID      12.1.0.1.0  00:04:35
Oracle Database Java Packages
.                                         VALID      12.1.0.1.0  00:00:22
Oracle Multimedia
.                                         VALID      12.1.0.1.0  00:02:42
Spatial
.                                         VALID      12.1.0.1.0  00:06:21
Oracle Application Express
.                                         VALID     4.2.0.00.27  00:25:28
Final Actions
.                                                                00:02:47
Total Upgrade Time: 01:09:24

PL/SQL procedure successfully completed.

SQL>

You can see the upgrade failed for the "Oracle OLAP API" section, which is a feature we removed before the upgrade, so it is OK to continue.

If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. Since we did have errors, we need to run it manually.

SQL> @catuppst.sql

If the "postupgrade_fixups.sql" file contained any recommendations, run it now.

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

The following items are not essential, but the upgrade manual suggests it is a best practice to run them.

-- The following item is probably included in your postupgrade_fixups.sql script.
EXECUTE DBMS_STATS.gather_fixed_objects_stats;

-- Recompile invalid objects.
@utlrp.sql

-- Check for newly invalid objects.
@utluiobj.sql

-- Run again to check the final outcome of the upgrade.
@utlu121s.sql

The upgraded database is now available.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 24 18:17:03 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.

Export Import

As with previous releases, the export/import process can be used to transfer data between versions. Export the data using the expdp utility from the source version and import using the 12c version of impdp.

The basic export/import method allows you to move data directly to a pluggable database.

Transport Database

The option of an upgrade via a transport database is possible for 11.2.0.3 or later. The process is broken down into the following basic steps.

I'll discuss some of these steps in more detail below. The example is based on an upgrade on the same host, so no file conversion is necessary.

Create a new 12c database using the DBCA.

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

My source database was called "orcl", so I created a clean 12c database called "orcl12c", without the sample schemas. Make sure the character sets match.

With the 12c database in place, we must prepare the 11g database for the export. I was testing this on a clean installation of 11.2.0.3, so I had to create a directory object for the export, as well as a little dummy data.

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

-- Create a directory to export to.
-- This directory should be created in the 12c database too.
CREATE OR REPLACE DIRECTORY TEMP_DIR AS '/tmp/';

-- Create a new tablespace and test user with some dummy data.
CREATE TABLESPACE data_ts 
  DATAFILE '/u01/app/oracle/oradata/orcl/data01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;

CREATE user test IDENTIFIED BY test
  DEFAULT TABLESPACE data_ts
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON data_ts;

GRANT CREATE SESSION, CREATE TABLE TO test;

CREATE TABLE test.t1 AS
SELECT * FROM dba_objects;

-- Make the tablespaces you want to transport read-only.
ALTER TABLESPACE data_ts READ ONLY;
ALTER TABLESPACE example READ ONLY;

EXIT

Export the database, using the "FULL=Y TRANSPORTABLE=ALWAYS VERSION=12" parameters. I excluded the "USER" tablespace as it was already present in the 12c database and there was nothing I wanted to transfer in that tablespace.

$ expdp system full=Y transportable=always version=12 directory=TEMP_DIR \
   dumpfile=orcl.dmp logfile=expdporcl.log exclude=TABLESPACE:\"= \'USERS\'\"

The export contains just metadata for the data tablespaces, but includes the normal dump contents of the SYSTEM and SYSAUX tablespaces.

Copy the datafiles to the correct location for the 12c database.

$ cp /u01/app/oracle/oradata/orcl/data01.dbf /u01/app/oracle/oradata/orcl12c
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl12c

At this point it probably makes sense to switch the tablespaces back to read-write and shutdown the 11g database.

$ sqlplus / as sysdba
ALTER TABLESPACE data_ts READ WRITE;
ALTER TABLESPACE example READ WRITE;

SHUTDOWN IMMEDIATE;
EXIT;

We can now import the dump file into the 12c database. This of course assumes you have already created the directory object in the 12c database, as mentioned previously.

$ export ORACLE_SID=orcl12c
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES

$ impdp system full=Y directory=TEMP_DIR dumpfile=orcl.dmp logfile=impdporcl.log \
   transport_datafiles= \
      '/u01/app/oracle/oradata/orcl12c/data01.dbf', \
      '/u01/app/oracle/oradata/orcl12c/example01.dbf'

The 12c database now contains all the data from the original database.

$ sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 24 12:06:01 2013

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

Last Successful login time: Wed Jul 24 2013 12:05:38 +01:00

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 COUNT(*) FROM t1;

  COUNT(*)
----------
     75213

SQL>

There are a number of variations on this process, including changing platforms using the RMAN CONVERT command, or the DBMS_FILE_TRANSFER package. These are covered in documentation.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.