Manual upgrading Oracle database 11gR2 to 12cR2

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<

Example

$ /u01/app/oracle/product/11.2.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin/preupgrade.jar 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

/u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin/emremove.sql

b. To remove OLAP Component

/u01/app/oracle/product/11.2.0/db_1/olap/admin/catnoamd.sql

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

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
PURGE DBA_RECYCLEBIN;

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.

SQL>

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/12.2.0.1/db_1/dbs

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

6. Set new ORACLE HOME of 12c location

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

[oracle@oracle12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 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 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.

$ 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 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

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

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

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>  ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;
SQL> select count(1) from dba_objects where status='INVALID';

13. Restart the database

SQL> Shut Immediate;
SQL> Startup

Cheers!!!

Upgradation Successfully Done