Before I dig into the upgrade stuff this is what i did before upgrading.
1. Created DB11g instance in – 11.2.0.2 with ORACLE_HOME=/u02/app/oracle/product/11.2.0.2/db_1 and ORACLE_BASE=/u02/app/oracle.
2. Installed 11.2.0.3 Software with ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1 and ORACLE_BASE= /u01/app/oracle.
So now find out the current version of the database which is DB11g and then we will go ahead with the upgrade steps.
First of all find out the current version of the database:
*************************************************************************
[oracle@OEL64 ~]$ . oraenv
ORACLE_SID = [APEX] ? DB11g
The Oracle base has been changed from /u01/app/oracle to /u02/app/oracle
[oracle@OEL64 ~]$
[oracle@OEL64 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 22 19:47:39 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production
SQL>
******************************************************************************************************
Download the Pre-Ugrade script from support.oracle.com – utlu112i_5.sql which will describe the prerequisite before we go ahead for the upgrade. Place it on server and execute on 11.2.0.2 database.
******************************************************************************************************
[oracle@OEL64 Desktop]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 22 17:44:12 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> spool upgrade_11.2.0.3.log
SQL> @utlu112i_5.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-22-2013 17:44:29
Script Version: 11.2.0.3.0 Build: 006
.
**********************************************************************
Database:
**********************************************************************
–> name: DB11G
–> version: 11.2.0.2.0
–> compatible: 11.2.0.0.0
–> blocksize: 8192
–> platform: Linux x86 64-bit
–> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 919 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 624 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 60 MB
–> EXAMPLE tablespace is adequate for the upgrade.
…. minimum required size: 310 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
–> If Target Oracle is 32-Bit, refer here for Update Parameters:
— No update parameter changes are required.
.
–> If Target Oracle is 64-Bit, refer here for Update Parameters:
— No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
— No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> OLAP Analytic Workspace [upgrade] VALID
–> OLAP Catalog [upgrade] VALID
–> EM Repository [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Spatial [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
–> Oracle Application Express [upgrade] VALID
… APEX will only be upgraded if the version of APEX in
… the target Oracle home is higher than the current one.
–> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Your recycle bin is turned on and currently contains no objects.
…. Because it is REQUIRED that the recycle bin be empty prior to upgrading
…. and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
…. prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: –> Database contains schemas with objects dependent on DBMS_LDAP package.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),’ ‘)) FROM sys.v$parameter2
WHERE UPPER(name) =’EVENT’ AND isdefault=’FALSE’
Trace Events:
SELECT (translate(value,chr(13)||chr(10),’ ‘)) from sys.v$parameter2
WHERE UPPER(name) = ‘_TRACE_EVENTS’ AND isdefault=’FALSE’
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL>
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL64 Desktop]$
******************************************************************************************************
Now we will go ahead with the upgrade step. Set the ORACLE_HOME to 11.2.0.3 and execute dbua utility.
1. Set the ORACLE_HOME to 11.2.0.3.
2. Set the PATH to the new ORACLE_HOME (else it will still pick up the old one.)
[oracle@OEL64 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.3/db_1
[oracle@OEL64 ~]$
[oracle@OEL64 ~]$ which dbua
/u01/app/oracle/product/11.2.0.3/db_1/bin/dbua
[oracle@OEL64 ~]$
[oracle@OEL64 ~]$ dbua
Select the Database which you are planning to upgrade and Press Next.
It will fetch the information and print the same warning which we got in pre-upgrade utility.
Press Yes. If the shown warning is going to hamper your database later on, then take care of them before upgrade.
You can select the Backup Database option if you want to rollback later on if in case anything happens (i have not tried this though yet). Press next.
Specify the FRA if in case you want to change to some other place. Press Next.
So as per the above result this database have the new SPFILE Created in new ORACLE_HOME of 11.2.0.3.
Database upgrade has been completed successfully, and the database is ready to use.
The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/11.2.0.3/db_1/assistants/dbua/doc/DefaultBehaviorChanges.html
Note, the db has been restarted for you. Change the .bash_profile and /etc/oratab file if not already changed.
Check the version now:
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
SQL>
Hope this helps…
Thanks for viewing.