A database can be upgraded either using DBUA or manual method. But for major production databases, it is better to do it manually, so that troubleshooting will be easy. Here we will provide steps for upgrading from 11gr2 to 12c database manually.

Before starting the upgrade make sure Oracle 12c database binary already installed on the database server.

You can refer the Installation steps here: https://dbaclass.com/article/how-to-install-oracle-12c-on-linux/

SEE ALSO : Upgrade database from 11g to 12c using OEM cloud control

SEE ALSO: Upgrade database from 11g to 12c using DBUA ( GUI method)

Current ORACLE_HOME= /apps/oracle/product/

Target ORACLE_HOME=/apps/oracle/product/




2. Check duplicate objects owned by system and sys


     If you found any other objects other than these four, then those need to be cleaned up.

3.Check the integrity of the database by running dbupgdiag-2

Check the output log for invalid objects and make a note of them.
4. Run utlrp.sql to validate invalid objects


5. Run preupgrade tool

Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1) i.e /apps/oracle/product/   to /apps/oracle/product/

Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.

SQL> @ /apps/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

If still, changes are not reflecting  Check the preupgrade_fixups.sql script and do the changes manually.

In my case, it recommended changing parameters as below.

– Alter system set PROCESSES=300 SCOPE=SPFILE
– Execute dbms_preup.purge_recyclebin_fixup;
– Alter system set JOB_QUEUE_PROCESSES= 100
– Execute dbms_stats.gather_dictionary_stats;

So let’s do the above changes before proceeding with the next step.

6. Dependencies on Network Utility Packages

6. Take ddl backup of db_link backups:

    During the upgrade to Oracle Database 12c any passwords in database links are encrypted.
To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade.

7. Check the timezone version:


For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.
8. Optimizer Statistics



9. Verify That Materialized View Refreshes Have Completed Before Upgrading


10. Ensure That No Files Need Media Recovery Before Upgrading



11. Ensure That No Files Are in Backup Mode Before Upgrading



12. Resolve Outstanding Distributed Transactions Before Upgrading



13. Purge the Database Recycle Bin Before Upgrading :



14. Synchronize the Standby Database with the Primary Database When Upgrading



15. Disable cronjob,

Take backup of crontab and comment the same.


16. Disable dbms_schduler jobs:



17. Verify system and sys default tablespace.(Both should be system tablespace)



18. Check whether database has any externally authenticated SSL users



19. Remove EM repository



20. Review and Remove any unnecessary hidden/underscore parameters



With this our precheck completes. 

Before starting the upgrade, enable flashback and create restore point, so that in case upgrade fails, we can restore it using guarantee restore point.

Enable flashback

Now create restore point:




21. stop the listener and shutdown the database.

22.Update the ORACLE_HOME,PATH pointing to 12C Home.

23. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.


24. start the database in upgrade mode.


25.Run catupgrade script from os level with paralle=6 as below.


Open another window and check the log.

cd /uv1172/apps/oracle/product/
tail -100f catupgrd0.log


26. Run the Post-Upgrade Status Tool


27.Run Catuppst.sql

              The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.


Now upgrade is completed. Now Proceed with POST CHECK.




Download the dst upgrade script from oracle




Purge the scheduler jobs



Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically)



Run upg_tzv_apply.sql ( It will do the actual dst upgrade)



Once dst upgrade is successful , validate the time_zone(It should be 18)

1. SQL>@/apps/oracle/cfgtoollogs/PROD/preupgrade/postupgrade_fixup.sql

2. Change the ORACLE_HOME to 12c in listener.ora file.

3. Uncomment the crontab

4.Enable the jobs in dba_scheduler_jobs which we disabled before the upgrade.

5. Run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.

6. Run $ORACLE_HOME/rdbms/admin/utlrp.sql script


SQL> drop restore point BEF_UPGRADE;

7. Update the compatible parameter and restart database.

NOTE- Database cannot be downgraded once compatible parameter is updated. 


NOTE – Once compatible is set to higher version, downgrade is not possible.

With this our upgrade activity completes.



If you have done the prechecks properly, then chances are very less that, upgrade will fail. But even if it fails for any other reasons like server crash during upgrade, then follow below steps to revert to back to 11g version . 

1. Shutdown immediate;

2. set ORACLE_HOME to 11g 

3. Start up mount ( with the 11g spfile)

4. select * from v$restore_point;

5. flashback database to restore point bef_upgrade. ( this restore point was created before upgrade)

6. alter database open resetlogs;