Database upgrade from 11.2.0.2 to 11.2.0.3

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

ImagePress Next.

ImageSelect the Database which you are planning to upgrade and Press Next.

ImageIt will fetch the information and print the same warning which we got in pre-upgrade utility.

ImagePress Yes. If the shown warning is going to hamper your database later on, then take care of them before upgrade.

ImageYou 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.

ImageNo Changes, press Next.

ImageSpecify the FRA if in case you want to change to some other place. Press Next.

ImagePress Finish.

ImagePress OK.

ImageCheck out the upgrade result.

Image

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s