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: http://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/11.2.0.3

Target ORACLE_HOME=/apps/oracle/product/12.1.0.2

PRECHECKS:

1.CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)

 

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/12.1.0.2/rdbms/admin   to /apps/oracle/product/11.2.0.3/rdbms/admin

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:

 

UPGRADE:

 

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/12.1.0.2/diagnostics
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.

 

27. UPGRADE DST TIME ZONE:

 

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

 

 

7. Update the compatible parameter and restart database.

 

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

With this our upgrade activity completes.

 

IF UPGRADE FAILS

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;