Post Oracle 12c announcement, testing the capabilities of new release become one of the top priorities of an Oracle DBA. Majority of the organization are now looking forward or thinking towards upgrading their current database to Oracle database 12c. Therefore, this blog is dedicated and focused on upgrading to Oracle database 12c. It will explore the various methods available for upgrade, and which is the best suited method to consider. Additionally, the blog is going to cover those new additions to upgrade method and explains how to downgrade an Oracle database 12 to its previous release.

This article will explain the following topics in details:

  • Why upgrade
  • Database upgrade methods, tools and approach
  • What’s new in database upgrade
  • Upgrading Oracle Database 12c manually
  • Upgrading Oracle Database 12c with DBUA
  • Downgrading Oracle Database 12c

Why upgrade?

A billion dollar question that comes into everyone’s mind is whether to upgrade or not their existing Oracle environment on every Oracle new release announcement. It is indeed a pretty difficult question to answer just with YES or NO. In my perspective, the following are the key points/conditions that encourage an organization either to hold or proceed with the upgrade:

No to upgrade:

  • Legacy application that doesn’t support the new Oracle database release.
  • Lack of proper testing environment.
  • Lack of technical personnel to take up the challenge.
  • Unable to accept a large downtime for critical business application.

Yes to upgrade:

  • To leverage exciting new additions and improvements.
  • To fix and address the technical bugs encountering with the current release.
  • Organization standard policies to maintain the updated software in the organization.
  • Oracle life-time support policy.

Database upgrade methods, tools and approaches

Upgrade is the process which transforms the existing Oracle dictionary information to reflect the new Oracle database release without actually touching the data directly. During the database upgrade process, all the database components will be upgraded to the new release. In the context, Oracle provides several methods/options to upgrade the current Oracle database to the new Oracle database release. The following are the major methods/options that are typically used/followed by the major section of the Oracle DBA to perform the task:

  • Manual upgrade procedure
  • Database Upgrade Assistance (DBUA) tool
  • Data Pumps
  • Transportable Tablespaces
  • Golden Gate.

It is a well-known fact that every method comes with its share of advantages and limitations. Though there are various methods exists to upgrade an Oracle database, what is really important for a DBA is to investigate the pros and cons of each method thoroughly and choose the right approach that typically fits into their requirements.

What’s new in database upgrade

Before commencing the database upgrade process, it is necessary to become familiar with those new additions or enhancements introduced in the context of Oracle database upgrade procedure. There are two essential elements that every DBA should realize before the database upgrade: 1) Database upgrades path, 2) Understanding and taking the right advantage of new additions and enactments in the upgrade process.

Upgrade compatible matrix

The following table depicts the compatible matrix for a direct and indirect database upgrade paths:

direct upgrade

indirect upgrade

10.2.0.5

11.1.0.7

>=11.2.0.2

<=9.2.0.8

10.1.0.5

10.2.0.2/3/4

11.1.0.6

11.2.0.1

If your current database version doesn’t match with the direct upgrade list of versions, you therefore need to choose an indirect upgrade path. In this context, you must perform an upgrade of your current database to one of the versions listed in the direct upgrade lists of version and subsequently proceed with the Oracle database 12c upgrade.

Upgrade enchantments/additions

The following new concepts were introduced in Oracle 12c with regards to database upgrade procedure:

  • Pre-upgrade tool
  • Parallel processing

Pre-upgrade tool: The new pre-upgrade information tool (preupgrd.sql ) is capable of verifying a detailed pre-upgrade checks, and generates pre/post-upgrade scripts that likely to address all the key concerns reported. The new tool is replaced with the traditional utlu__i.sql script, which offers more advantages over the old script.

The DBUA tool inherits the script internally and executes it before actually kicking off the database upgrade procedure. However, if you choose manual method over DBUA method for database upgrade, you must execute this script manually from the Oracle 12c DB home. A log file and two sql scripts will be generated upon executing the pre-upgrade information tool. The log file, preupgrade.log contains the details result about the verification checks and the pre/postupgrade.sql scripts consist of fixes of pre/post upgrade database steps.

Parallel processing: Pre Oracle 12c, there was no option to run the database upgrade process in parallel in order to take the advantage of multiple CPU on the server and cut off the total downtime required for the process. The catctl.pl utility loads the data dictionary and database components in parallel to improve the overall database upgrade process time. The DBUA inherits the tool, and triggers automatically. You must manually execute the utility whilst doing the manual database upgrade.

Manual database upgrade procedure

This goal of this section is to explain you a step-by-step procedure which will assist you upgrading a database to Oracle 12c manually. Let me demonstrate how to manually upgrade an Oracle 11.2.0.3 database – MYDB, to Oracle database 12c:

export ORACLE_SID=MYDB
sqlplus / as sysdba

SQL> @/u00/app/oracle/product/12.0.1/rdbms/admin/preupgrd.sql

--- Refer the log file and sql scripts generated under the $ORACLE_BASE/cfgtoollogs/db_unique_name location to verify the concerns raised and fixup scripts for pre/post upgrade process

SQl> SHUTDOWN DATABASE;
SQL> EXIT;

--- switch to Oracle 12c Oracle Home, startup the database in upgrade mode

cd $ORACLE_12C_HOME
SQL> STARTUP UPGRADE;
SQL> EXIT;

--- initiate the upgrade procedure in parallel using the new parallel upgrade utility

cd $ORACLE_12C_HOME/rdbms/admin
$ ./perl catctl.pl –n 0 catupgrd.sql [to run in serial mode]
$ ./perl catctl.pl –n 4 catupgrd.sql

Upon completing the catupgrd.sql script, the database will be shutdown. You are therefore need to start the database from the Oracle 12c home and run couple of post database upgrade steps.

SQL> STARTUP;
SQL> @?/rdbms/admin/utlu121s.sql;   -- to view the post-upgrade summary
SQL> @?/rdbms/admin/utlrp.sql;   -- recompile invalid objects
SQL> execute dbms_stasts.gather_fixed_objects_stats;
SQL> exit;

Database upgrade with DBUA tool

The Database Upgrade Assistant (dbua) tool provides more options and automates a few manual steps in contrast to other upgrade methods during the course database upgrade procedure. The dbua tool certainly has an edge over other approaches. Therefore, it is best recommended to use this tool for database upgrade. The following will explain a procedure to upgrade a database using the dbua tool with important screenshots:

  • Launch dbua from the Oracle database 12c home
  • Choose ‘Upgrade Oracle Database’ option, as shown in the below screen shot:

  • Select the database from the list displayed for upgrade on the ‘Select Database’ screen.
  • The procedure then advances to perform the prerequisites checks, uses the pre-upgrade information tool, as shown in the below screen:

  • On the Upgrade Options screen, input the following:
    • Specify parallelism
    • Gather statistics before upgrade
    • Audit/Diagnostic information locations
  • Optionally, configure the Enterprise Manager Express or hook-up with the existing EM Cloud Control, as shown in the below screen shot:

  • In case if you want to take automated pre-upgrade database backup and also to restart the DUBA from the point of failure, select the option on Recovery Option screen.
  • The Summary screen shot shows all the existing and Oracle 12c home details.
  • Upon clicking the Finish button, the upgrade procedure kicks off and you can see the progress bar.
  • After successful database upgrade, Upgrade Results screen will presents upgrade details.

Downgrading Oracle database 12c

It is always safeguard and important to put-in place a workaround to back-out plan whilst proceeding with the upgrade process. This is essential because there could be some complications that would arises post database upgrade either from the database side or application side. The goal of this section of explain you a procedure to downgrade an Oracle database 12c to an earlier version.

  • Login to Oracle 12c database as sysdba shutdown the database:

export ORACLE_SID=MYDB
sqlplus / as sysdba

SQL> shutdown immediate;
SQL> STARTUP DOWNGRADE;
SQL> SPOOL /tmp/downgrade.log
SQL> @?/rdbms/admin/catdwgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE;

From the earlier Oracle version, run through the following steps:

SQL> startup upgrade;
SQL> spool /tmp/reload.log
SQL> @?/rdbms/admin/catreload.sql
SQL> SPOOL OFF;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

For more information and other details, refer Oracle upgrade documentations.