Manual upgrading Oracle database 11gR2 to 12cR2
If you want to know how we upgrade an 220.127.116.11 database to 18.104.22.168 using DBUA, click here.
This article explains how to upgrade 22.214.171.124 and above, and 126.96.36.199 to 188.8.131.52 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 184.108.40.206 to upgrade to 220.127.116.11, 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/18.104.22.168/db_1
1. Run the preupgrade tool
Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.
ls -ltr /oracle/app/oracle/product/22.214.171.124/dbhome-1/rdbms/admin/preupgrade.jar
$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/126.96.36.199/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
b. To remove OLAP Component
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/188.8.131.52/db_1/dbs cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwMIG /u01/app/oracle/product/184.108.40.206/db_1/dbs
6. Set new ORACLE HOME of 12c location
[oracle@oracle12 ~]$ sqlplus / as sysdba SQL*Plus: Release 220.127.116.11.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 18.104.22.168.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.
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
Upgradation Successfully Done