By:Joel Perez( Oracle ACED )
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 12cr2
11.2.0.4
We will be using hereto upgrade to, onplatform.
We already install new software for Oracle 12cR2
Below our 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.
EM console
a. To remove
/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 set the number of processes and purge the recycle bin
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
PURGE DBA_RECYCLEBIN;
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.
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
catctl.pl
If used, 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 offrom 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
postupgrade_fixups.sql
8. Run “” 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 Note1585343.1
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';
Restart the database
13.
SQL> Shut Immediate;
SQL> Startup
Cheers!!!
Upgradation Successfully Done
About the Author
Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialized in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries in 5 continents and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish & OTN Portuguese. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA(Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China and more. Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004 and as one of the biggest professional achievement in his career he was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Chief Technologist in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China www.Enmotech.com