Manual upgrading Oracle database 11gR2 to 12cR2

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

This article explains how to upgrade 11.2.0.2 and above, and 12.1.0.1 to 12.2.0.1 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 11.2.0.4 to upgrade to 12.2.0.1, 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

DBNAME  – MIG

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

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

PRE-UPGRADE STEPS:

1. Run the preupgrade tool

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

ls -ltr /oracle/app/oracle/product/12.2.0.1/dbhome-1/rdbms/admin/preupgrade.jar

SYNTAX

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

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.

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

6. Set new ORACLE HOME of 12c location

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

7. Run catctl.pl

If used catctl.pl, 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 catctl.pl -n 4 catupgrd.sql

For the output log, click here

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

a. To fix the manual error related to timezone

The database is using timezone datafile version 14 and the target 12.2.0.1.0 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

10. Execute 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.

13. Restart the database

Cheers!!!

Upgradation Successfully Done

Comments

Leave a Reply