• Arun Kumar

Oracle Database Upgrade from 11g to 12c

As an Oracle DBA, database upgrades are one of the critical tasks that you must be confident about. Time and again, Oracle releases new versions of the Oracle database and your client would ask you to upgrade their databases from the previous version to the latest version.

Reason to upgrade database

Let us first look at few reasons to upgrade your database:

  • New versions have security fixes compared to old versions

  • Latest versions will have lot of bug fixes and this will simplify your job

  • Old version support gets discontinued by Oracle

  • Hackers always attack old versions when you do not upgrade their DB

  • Latest version will have additional features and performance improvements

For the sake of previous reasons, we have made this article, to show you how to upgrade your Oracle 11g database to 12c. You need to know that its highly recommended to backup your database before starting in the upgrading process.

Database upgrade vs migrate

Let us first start with understanding the difference between database upgrade vs database migrate.

Upgrading means converting an already existing database environment which includes installed components and applications into new database environment. This means the data dictionary for the existing database will be upgraded to a new release. During the upgrading process your data will not be touched, moved, changed or even affected by any means.

Migrating means moving data from one database into another previously created one. The process of migration is done when you need to move your database environment to a new operating system or new hardware. Migrating does not contain upgrading to the latest database release, but you may handle upgrading separately after migration.

Oracle upgrade matrix

Before upgrading your database, you need to check Oracle compatibility matrix first and the minimum versions of database that can support Oracle direct upgrade.


For example, if your current installed database version is then you will need first upgrade to then to 12.2.x. You cannot directly upgrade from to 12.2.x.

Before you upgrade

There are some points that needs to be taken in consideration before upgrading your database. Here you are some of these points:

  • Review the Pre-Upgrade Information Tool provided by Oracle, which you can run on earlier Oracle database versions to check if your version is ready to be upgraded or not.

  • Put a test plan for the upgrading process. And review this plan with all the involved teams.

  • Before upgrading the database, you need to understand first what is the requirements and recommendations of the new version of database. Also, you have to review the additional features provided by the new version of database. For this point you can check Oracle documentation.

  • It is always recommended to practice the upgrading process on test servers first, before upgrading on the production.

  • Take a backup from your database before upgrading. And ensure that the backup has finished successfully and no files are hold in the backup mode.

Database upgrade methods

There are four recommended methods by Oracle to help you upgrade your database safely, lets take a look on the four methods:

  • Oracle Database Upgrade Assistance (DBUA): Database Upgrade Assistance is a graphical user interface tool provided by Oracle that can help you through the upgrade process. This tool can work for both CDB and non-CDB database systems. Its recommended by Oracle to run the Pre-Upgrade Information tool before running the DBUA. To analyze the current database and take needed actions that can minimize the upgrading downtime.

  • Manual Upgrade: It is a command line upgrade method using SQL scripts. It is commonly used by database administrators who need more control over the upgrade process.

  • Oracle Data Pump: In this method, you can use export and import tools provided by Oracle to make a full export from your current database, then perform an import into the new Oracle database release.

  • Using SQL statement CREATE TABLE AS: In this method, you will copy data from the current database into the new Oracle database release.

After you have quickly reviewed the above-mentioned methods, you can choose which one that is best for you. For this article we are going to use the Manual upgrade method.

Pre-upgrade tasks

In this section, we will start preparing our Oracle Database for upgrading by preforming some pre-upgrade tasks, but first let’s have a look on some of my environment details that will be used during this tutorial.

  • OS: OEL 6.5

  • Database SID: orcl

  • Database version:

  • Oracle 11g home: /u01/app/Oracle/product/11.2.0/dbhome_2

Check current db version

Connect to SQL, and use the next query to get your database version

SELECT * FROM v$version;

As you can see in the below screenshot, the database version is which supports direct upgrade to 12c version


Install 12cR2 software

Download Oracle 12cR2 for Linux from Oracle e-cloud website. Un-compress archived file and start the runInstaller

unzip linuxx64_12201_database.zip
cd database

Choose “Install the database software only” option


Continue with the installation process with the default options. After the installation process completes successfully, set your ORACLE_HOME to the

new 12cR2 home

export ORACLE_HOME=/u01/app/oracle/product/12R2

Run pre-upgrade tool

Run the pre-upgrade tool from your new Oracle home

/u01/app/oracle/product/11.2.0/dbhome_2/jdk/bin/java -jar /u01/app/oracle/product/12R2/rdbms/admin/preupgrade.jar FILE DIR /u01/sw

Here we have mentioned the old Oracle home that we need to upgrade. Also, we have mentioned the directory where the pre-upgrade tool files will be saved.

After running the previous command, three files will be created under the “/u01/sw” directory as following:

  • /u01/sw/preupgrade.log → Log file and recommendations by Oracle to follow

  • /u01/sw/preupgrade_fixups.sql → Script to run before upgrading to configure your system for upgrading

  • /u01/sw/postupgrade_fixups.sql→Script to run after upgrading process completes to fix things up

Connect to SQL and run the pre-upgrade fixups script generated from the previous step.

sqlplus / as sysdba

SQL> @/u01/sw/preupgrade_fixups.sql

As you can see there are some recommendations needed to be done manually as follows:

  • Fix “em_present” issue by executing the next script

SQL> @/u01/app/oracle/product/12R2/rdbms/admin/emremove.sql
  • Fix “apex_upgrade_msg” issue by upgrading the apex

  • Download apex package from https://www.Oracle.com/technetwork/developertools/apex/downloads/index.html

  • Unzip .zip

  • Change your working directory to apex.

  • Connect to sqlplus and install apex sqlplus / as sysdba @apexins SYSAUX SYSAUX TEMP /i/

  • Configure apex: @apex_epg_config.sql /DB/apex/apex

  • Fix “trgowner_no_admndbtrg” by granting administrator privilege:

  • Fix “invalid_objects_exist” by running the following recompilation script.

SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql
  • Fix “amd_exist” issue by running the next script.

  • Fix “mv_refresh” issue by running the next procedure.

num_failures integer(3) :=0;
  • Purge Recycle-bin

  • Re-run the pre-upgrade fixups script after fixing all the errors, and notice that everything is fixed.

  • Shutdown the database.

shutdown immediate
  • Stop the listener.

lsnrctl stop

Upgrade the database using manual method

  • Copy the parameter and password file from the old Oracle home to the new 12c home.

cp /u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora

cp /u01/app/oracle/product/11.2.0/dbhome_2/dbs/orapworcl
  • Set Oracle home to the new 12c location.

export ORACLE_HOME=/u01/app/oracle/product/12R2
  • Connect to SQL and open the database in upgrade mode.

sqlplus / as sysdba
startup nomount
alter database mount
alter database open upgrade
  • Run the upgrade tool.

cd /u01/app/oracle/product/12R2/rdbms/admin/

@/u01/app/oracle/product/12R2/perl/bin/perl catctl.pl catupgrd.sql

As you can see, it will take some time to finish upgrading the database. So, don’t panic if it took too long because it depends on your machine resources.

Post upgrade tasks

  • Run the post upgrade fixups script.

  • Fix time zone issue.

Run the check “upgrade_tzv_check” script. (The script is sent separately)
  • Check time zone version before upgrading:

SELECT version FROM v$timezone_file;
  • Run ““upgrade_tzv_apply”” which will upgrade the time zone.(The script is sent separately)

  • Check time zone version after upgrading.

  • Re-run the post upgrade fixups script.


As you can see the time zone issue was fixed.But for the “depend_usr_tables” issue and as recommended by Oracle we will ignore it

If Oracle database was Upgraded without using -T option,then the FAILED status can be ignored. But if the Database was Upgraded using -T option. Then you need to execute the utluptabdata.sql script (which is found in $ORACLE_HOME/rdbms/admin/utluptabdata.sql) after the upgrade is complete.

Check and validate the upgrade process.

  • Run the following script to enhance the performance.

  • Run “utlrp.sql” script to compile invalid objects.

  • Set COMPATIBALE parameter value to 12.2.0. But be careful after changing this parameter to the new value, you will not be able to downgrade your database to any earlier version.

SELECT name, value FROM v$parameter WHERE name = 'compatible';
  • Restart database and check the compatibility parameter again.

Shutdown Immediate;
SELECT name, value FROM v$parameter WHERE name = 'compatible';
  • Finally, check your database version.

SELECT * FROM v$version;

Oracle Database Downgrade From 12c to 11g

Normally rolling back your code is very easy. But when it comes to rolling back your database to an earlier version it’s a little bit tricky and difficult. In the next sections, we will discuss the following topics:

  • Why to rollback your database?

  • What are the pre-rollback steps to be done?

  • How to perform a successful rollback without losing your data?

  • What are the post-rollback steps to check that everything is back to normal?

Why DB rollback?

Here are some scenarios of why you need to rollback your database or in other words downgrade your database to an earlier version:

  • Sometimes while upgrading your database, the upgrade process terminates unexpectedly.

  • Issue with the control files while opening the database.

  • An error with “catjava” and then the java packages becomes invalid.

  • You may get stuck in the middle of the upgrade process and your system hangs.

Rollback methods

Next, we will give you a quick look on the most common methods for rolling back your database:

  • Restore from a backup:

This is the most straight forward and reliable method. You can restore your database as long as you have a full working backup for your database. Yet this method involves a downtime which may impact your business.

  • Rollback Scripts:

Many system administrators call for a rollback script corresponding to every upgrade script, this is one of the most used approaches by successful system administrators. This forces the involved teams to prepare rollback scripts and often test those scripts in advance.

Before you roll back

Next are the pre-downgrade steps you need to complete first before starting in the downgrade process.

  • Its highly recommended by Oracle that you check your “COMPATIBLE” initialization parameter before starting in the rollback or the downgrade process. By default, Oracle gives you the ability to change the “COMPATIBLE” initialization parameter across your parameter file while upgrading. But once you have changed this parameter, you can not downgrade to a lower release than you have set in the “COMPATIBLE” initialization parameter.

For example, let’s say you have upgraded to Oracle database release 12cR2(12.2) and you have changed the “COMPATIBLE” initialization parameter to (12.1) or higher, then you cannot downgrade to earlier release like But in case you have upgraded to release (12.2) and did not change the “COMPATIBLE” initialization parameter then you can downgrade to

  • To check your “COMPATIBLE” initialization parameter use the next command.

SELECT name, value FROM v$parameter WHERE name = 'compatible';
  • Output should be like that:

  • Ensure that the destination home that you need to downgrade database to contains the right versions of the time zone files that your database is using.

  • First check your database time zone.

SELECT version FROM v$timezone_file;
  • Go to your destination Oracle home and search for time zone file version 26.

ls -l
  • You should find 2 files like the next output:

  • If you did not find the files, then copy them from the source Oracle home (which is in our case is 12.2) to the destination Oracle home (

cp /u01/app/oracle/product/12R2/oracore/zoneinfo/*_26.dat
  • Check the state of each component

select substr (comp_id, 1,15) comp_id, substr (comp_name, 1,30)
comp_name, substr (version, 1,10) version, status from dba_registry;
  • Check invalid components.

select owner, count (object_name) "Invalid object count" from
dba_objects where status! = 'VALID' and owner in ('SYS', 'SYSTEM')
group by owner;

Performing DB roll-back to previous version

  • Shut down the database and start in downgrade mode

shutdown immediate
startup downgrade
  • Run the downgrade script from the source Oracle home.

  • Shut down the database.

shutdown immediate
  • As a root, modify /etc/oratab file to home.

vi /etc/oratab
  • Export Oracle new home.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2;
  • Start listener

lsnrctl START
  • Start DB in upgrade mode.

sqlplus / as sysdba
startup upgrade;
  • Run the “catrelod” script.


Post rollback tasks

  • Check the registry components.

SELECT comp_name, status, substr (version,1,10) as version from
dba_server_registry order by modified;

As you can see there is an invalid “Oracle Database Catalog Views”, so we will recompile them in the next step.

  • Recompile all existing invalid objects.

  • Finally, check the database version.

SELECT * FROM v$version;

Recent Posts

See All

Oracle Database Migration to AWS Cloud

Find different ways to migrate an Oracle database from on-premises to AWS cloud using different tools available for migration. You could choose any migration method based on your environment and needs

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i