Manual upgrading Oracle database 11gR2 to 12cR2

If you want to know how we upgrade an database to using DBUA, click here.

This article explains how to upgrade and above, and to manually.

Manually upgrading to 12c

There are many DBAs who don’t want to follow the recommended way of upgrade using DBUA (Database upgrade Assistant). Following is the method of manually upgrading 11gr2 database to 12cr2. I will be using here to upgrade to, on Linux (x64_86) platform.

I already install new software for Oracle 12cR2

If you want to know how we install database to 12cr2 click here.

Below is my environment detail


CURRENT ORACLE_HOME – /u01/app/oracle/product/11.2.0/db_1

NEW ORACLE_HOME – /u01/app/oracle/product/


1. Run the preupgrade tool

Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.

ls -ltr /oracle/app/oracle/product/


$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY<


$ /u01/app/oracle/product/11.2.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/ FILE DIR /u01/sw

2. Stop EM and Listener.

$ emctl stop dbconsole

$lsnrctl stop LISTENER

3. Run the preupgrade_fixup.sql

To fix the pre-upgrade action, we need to run manually.

a. To remove EM console


b. To remove OLAP Component


c. we have the number of processes and purge the recycle bin


d. Again re-run preupgrade_fixup.sql, we can ignore apex warning information.

4. Shutdown the database and take full database backup. Alternatively you can turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


5. Copy the parameter and password file from the 11g home to the 12c home.

cp /u01/app/oracle/product/11.2.0/db_1/dbs/spfileMIG.ora /u01/app/oracle/product/

cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwMIG /u01/app/oracle/product/

6. Set new ORACLE HOME of 12c location

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

[oracle@oracle12 ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Sat Nov 21 05:59:59 2015

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  788529152 bytes

Fixed Size                  2929352 bytes

Variable Size             327159096 bytes

Database Buffers          452984832 bytes

Redo Buffers                5455872 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open upgrade;

Database altered.

SQL> exit

7. Run

If used, the output of this upgrade process would be similar to the following. For this example, log files would be in /tmp folder as specified in upgrade command. For conventional running of catupgrd.sql from SQLPLUS, please see the spool file for output or any errors

-n is used for parallelism (new feature on oracle 12c)

$ORACLE_HOME/perl/bin/perl -n 4 catupgrd.sql

For the output log, click here

8.  Run “postupgrade_fixups.sql” which was created earlier.

$ sqlplus / as sysdba

SQL> startup

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

a. To fix the manual error related to timezone

The database is using timezone datafile version 14 and the target database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data.  For further information, refer to My Oracle Support Note 1585343.1

Or we can do manually from here

9. Execute utlu122s.sql

SQL> @?/rdbms/admin/utlu122s.sql

10. Execute catuppst.sql

SQL> @?/rdbms/admin/catuppst.sql

11. Lastly, execute utlrp.sql script to compile if there are any invalid objects


12. Set COMPATIBALE parameter value to 12.2.0 and also start the listener with new home.

SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
SQL> select count(1) from dba_objects where status='INVALID';

13. Restart the database

SQL> Shut Immediate;
SQL> Startup


Upgradation Successfully Done


  1. Kalpana Raajendhran

    Hi Skanth,

    I followed your post exactly and it worked out gracefully for me . very clear and comprehensive .
    Thanks 🙂

  2. KP

    This method may only work on and above. Not including I tested on and failed.
    ./ -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle
    ERROR – Database version is not supported, please upgrade to a valid version first.

  3. Rinu

    for upgrading the oracle db to oracle db, do we also need to upgrade the asm/grid which is in version?
    Please reply at the earliest..

  4. Grzegorz Goryszewski

    great job, can You tell how to conduct the upgrade in a way You will end up with pdb. I’m assuming this is no-cdb version now , right ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.