Upgrade Oracle 11.2.0.3 RAC to 11.2.0.4 - ( Out-of-Place Manual Upgrade )

The different upgrade methods you can use to upgrade your database to the new Oracle Database release or higher are:

1) Database Upgrade Assistant (DBUA)
2) Manual Upgrade
3) Export/Import
4) Data Copying
5) Golden Gate

1) DBUA (Database Upgrade Assistant) 

The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database  release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations. This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is done automatically by the upgrade assistant.
For more information, refer to the following link:

10.2=> http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm#i1011482

11.1=> http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#i1011482

11.2 => http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf

Refer to the following oracle support notes for DBUA upgrade:
================================================
NOTE 556477.1 Complete Checklist for Upgrades to 11gR1 using DBUA
NOTE 870814.1 Complete checklist to upgrade the database to 11g R2 using DBUA
NOTE 1516557.1 Complete Checklist for Upgrading to Oracle Database 12c Release 1 (12.1) using DBUA
Upgrade Advisor: Database from 10.2 to 11.2 (Doc ID 251.1)
How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

2) Manual upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release. A manual upgrade gives you finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easier to diagnose the error. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
When manually upgrading a database, perform the following pre-upgrade steps:

Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database release, and must be run in the environment of the database being upgraded.
The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database release.
Prepare the new Oracle Home.
Perform a backup of the database.
Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

Refer to the following oracle support notes for manual upgrade:
================================================
NOTE 1503653.1 Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)
NOTE 837570.1 Complete Checklist for Manual Upgrades to 11gR2
NOTE 429825.1 Complete Checklist for Manual Upgrades to 11gR1
NOTE 263809.1 Complete checklist for manual upgrades to 10gR1 (10.1.0.x).
NOTE 316889.1 Complete checklist for manual upgrades to 10gR2.
NOTE 466181.1 10g Upgrade Companion
NOTE 601807.1 Oracle 11gR1 Upgrade Companion

Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1) Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)

Note: DBUA can be used only if the source & target Oracle homes are on the same Server.
But manual upgrade will work even if the source and target Home are on different servers provided that the Hardware architecture and operating system on source and target Home are the same.

Example :
- You cannot manually upgrade a database from an AIX Operating System to Solaris Operating System.
- You cannot manually upgrade a database from a Solaris X86-64 Machine to Solaris SPARC 64-bit.

3) Export/Import

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database.

Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.

For more information, refer to the following link:
http://docs.oracle.com/cd/B19306_01/server.102/b14238/expimp.htm#i262247

NOTE:  Export/Import works for all versions.  But for 10g and above, DataPump or Transportable Table Spaces methods are better.

NOTE 351598.1 Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)

4) Data Copying

You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.
Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables

Oracle Database Upgrade Guide
10g Release 2 (10.2)
Part Number B14238-02
http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm

5) Golden Gate

Check on the OTN site with the following url for how to upgrade with zero downtime using Golden Gate:

http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

In this article we will do , out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset:

The 11.2.0.2 and later patchsets are a full release. The 11.2 Patchset Installer does not update existing 11.2 installations.The install process performs a new installation whether you are doing out-of-place upgrade or in-place upgrade.

Starting with 11.2.0.2, you have two ways to apply a patchset:

1. Out-of-place upgrade (Recommended)
2. In-place upgrade

In the below example we are going to upgrade 11.2.0.3 DB to 11.2.0.4 using out-of-place manual upgrade method : 

Current  GRID Version : 11.2.0.3  ---> Upgrade to 11.2.0.4
Current GRID_HOME : /data01/app/11.2.0/grid_11203
Upgrade to GRID_HOME : /data01/app/11.2.0/grid_11204

Current RDBMS Version : 11.2.0.3 ---> Upgrade to 11.2.0.4
Current ORACLE_HOME : /data01/app/oracle/product/11.2.0.3/db_1
Upgrade to ORACLE_HOME : /data01/app/oracle/product/11.2.0.4/db_1

Step- 1; Upgrade the Grid Infra

 http://ora10gadmin.blogspot.in/2015/01/upgrading-grid-infrastructure-from.html

Step 2: Upgrade RDBMS

1. Install 11.2.0.4 binaries in new  ORACLE_HOME /data01/app/oracle/product/11.2.0.4/db_1

Download the binaries from oracle support website and unzip in a directory as below:

unzip p13390677_112040_Linux-x86-64_1of7 -d /home/oracle/software
unzip p13390677_112040_Linux-x86-64_2of7 -d /home/oracle/software

STEP 2
======
Install the latest 11.2 RDBMS Software into a new ORACLE_HOME.

Please note that with the 11.2 installation forward, all base RDBMS components are installed.  The only options are if a component is linked on or off (active and able to be used).  Custom installations are not possible.

You can run "opatch lsinventory -detail" against previous and new ORACLE_HOME to compare installed products.

Also please note:

rootupgrade.sh will fail if you are using /opt/oracle as the ORACLE_BASE.   For further information please see: 

Note: 1281913.1 Root Script Fails if ORACLE_BASE is set to /opt/oracle

Start the runInstaller :

[oracle@node1 database]$ ls -lrth
total 60K
drwxrwxr-x  2 oracle oinstall 4.0K Aug 27  2013 sshsetup
-rwxrwxr-x  1 oracle oinstall 3.2K Aug 27  2013 runInstaller
drwxrwxr-x  2 oracle oinstall 4.0K Aug 27  2013 rpm
drwxrwxr-x  2 oracle oinstall 4.0K Aug 27  2013 response
-rwxrwxr-x  1 oracle oinstall  30K Aug 27  2013 readme.html
drwxrwxr-x 14 oracle oinstall 4.0K Aug 27  2013 stage
-rwxrwxr-x  1 oracle oinstall  500 Aug 27  2013 welcome.html
drwxrwxr-x  4 oracle oinstall 4.0K Aug 27  2013 install

[oracle@node1 database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 4702 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 1285 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-01-16_10-11-15AM. Please wait ...
[oracle@node1 database]$ You can find the log of this install session at:
 /data01/app/oraInventory/logs/installActions2015-01-16_10-11-15AM.log

When you click next it will promt you to run root.sh of node1 & node2 . So execute the root.sh using root on both nodes :

 [root@node1 ~]# sh /data01/app/oracle/product/11.2.0.4/db_1/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /data01/app/oracle/product/11.2.0.4/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Check the current version of the 11.2.0.1 Oracle RDBMS time zone definitions -
If this reports version 14 then there is no action to take for the Oracle time zone definitions, you can upgrade from 10.1.0.5 / 10.2.0.x or 11.1.0.x to 11.2.0.2 without any action on TSLTZ or TSTZ data.

If it is not 14 follow the doc : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset (Doc ID 1201253.1) 

SQL>SELECT version FROM v$timezone_file;

VERSION
----------
14

Now run the Pre-upgrade Utility tool and follow the recommendations:

[oracle@node1 ~]$ cp /data01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/utlu112i.sql /home/oracle/upgrade/
[oracle@node1 ~]$ echo $ORACLE_HOME
/data01/app/oracle/product/11.2.0.3/db_1
[oracle@node1 ~]$
[oracle@node1 ~]$ /data01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 12:19:13 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> spool pre_upgrade_utlu112i_log.log
SQL> @/home/oracle/upgrade/utlu112i.sql 
Oracle Database 11.2 Pre-Upgrade Information Tool 01-16-2015 12:21:01
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:   RACDB
--> version:   11.2.0.3.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: 932 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 724 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
.
**********************************************************************
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
--> Real Application Clusters [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: --> The "cluster_database" parameter is currently "TRUE"
.... and must be set to "FALSE" prior to running a manual upgrade.
WARNING: --> Your recycle bin contains 2 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database.  The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
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.

**********************************************************************


Backup database for a Manual Upgrade:

run {
Backup database format 'someformatdirectory%U' TAG BEFORE_UPGRADE ;
Backup current controlfile format '/location/control_backup_name' ;
}

Prepare the New Oracle Home for the Upgrade:

1. Copy configuration files from 11.2.0.3 home to 11.2.0.4 home $ORACLE_HOME/dbs ( pfile , spfile) 

2. Take backup of spfile also by creating a pfile at some  non-default location 

3. copy the password file from old home to new home 

4. If you are doing a cluster database upgrade perform above steps on all nodes 

5. In the pfile make necessary changes like : 

                    --- Remove obsolete parameters 

--- Make sure compatibility parameter is 11.2 

-- Adjust other parameters as suggested by pre-upgrade utility

-- Make sure all paths are fully specified in the pfile 

-- If you are upgrading a cluster database , make sure you make cluster_database=FALSE and after upgrade 

you must set this back to TRUE

Manually  Upgrade the database :

1. Shutdown the database 

2. change the PATH and bash profiles to point to new $ORACLE_HOME

3. If there us a different user and group for GRID and Oracle RDBMS then run the setasmgidwrap script which is located in $GRID_HOME/bin 

against the new $ORACLE_HOME/bin/oracle directory with the -o option as shown below:

Run on all nodes :

[root@node1 ~]# ls -lrth /data01/app/oracle/product/11.2.0.3/db_1/bin/oracle*

-rwxr-x--- 1 oracle oinstall    0 Sep 17  2011 /data01/app/oracle/product/11.2.0.3/db_1/bin/oracleO

-rwsr-s--x 1 oracle asmdba   222M Dec 21 11:57 /data01/app/oracle/product/11.2.0.3/db_1/bin/oracle

[root@node1 ~]# ls -lrth /data01/app/oracle/product/11.2.0.4/db_1/bin/oracle*

-rwxr-x--- 1 oracle oinstall    0 Aug 24  2013 /data01/app/oracle/product/11.2.0.4/db_1/bin/oracleO

-rwsr-s--x 1 oracle oinstall 229M Jan 16 11:10 /data01/app/oracle/product/11.2.0.4/db_1/bin/oracle

[grid@node1 ~]$ $GI_HOME/bin/setasmgidwrap o=/data01/app/oracle/product/11.2.0.4/db_1/bin/oracle

4. From the new $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/bin/sqlplus / as sysdba

SQL>spool startup_upgrade.log

SQL> Startup upgrade pfile='/location of pfile for new home with cluster_databse=false' ;

sql>spool off;

5. Once the DB is open in upgrade mode:

SQL> SPool catupgrade.log

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql ;

SQL> spool off ;

The catupgrd.sql script determines which upgrade scripts must be run , runs them and then shut down the database.

if this script fails for some reason , then you can run the script again after you correct the issue.

6. Now start the database . 

7.Run SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql  "The Post Upgrade status tool" which provides the summary of the upgrade at the end of spool log.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql ;

.

Oracle Database 11.2 Post-Upgrade Status Tool 01-23-2015 22:05:02

.

Component Current      Version Elapsed Time

Name Status     Number HH:MM:SS

.

Oracle Server

.  VALID      11.2.0.4.0  02:30:04

JServer JAVA Virtual Machine

.  VALID      11.2.0.4.0  01:41:39

Oracle Real Application Clusters

.  VALID      11.2.0.4.0  00:00:10

Oracle Workspace Manager

.  VALID      11.2.0.4.0  00:01:57

OLAP Analytic Workspace

.  VALID      11.2.0.4.0  00:04:28

OLAP Catalog

.  VALID      11.2.0.4.0  00:04:53

Oracle OLAP API

.  VALID      11.2.0.4.0  00:02:59

Oracle Enterprise Manager

.  VALID      11.2.0.4.0  00:08:52

Oracle XDK

.  VALID      11.2.0.4.0  00:02:50

Oracle Text

.  VALID      11.2.0.4.0  00:02:12

Oracle XML Database

.  VALID      11.2.0.4.0  00:10:46

Oracle Database Java Packages

.  VALID      11.2.0.4.0  00:02:01

Oracle Multimedia

.  VALID      11.2.0.4.0  03:59:06

Spatial

.  VALID      11.2.0.4.0  00:10:10

Oracle Expression Filter

.  VALID      11.2.0.4.0  00:01:53

Oracle Rules Manager

.  VALID      11.2.0.4.0  00:01:05

Oracle Application Express

.  VALID     3.2.1.00.12

Final Actions

. 00:00:01

Total Upgrade Time: 09:05:24

PL/SQL procedure successfully completed.

8. Run @CATuppst.sql to perform upgrade actions that do not require the database to be in upgrade mode.

9. To reccompile any remaining stored procedure or java code 

SQl> @$ORACLE_HOME/rdbms/admin/utlrp.sql ;

Verify the invalid objects count :

select count(*) from dba_invalid_objects ; 

If the pre-upgrade information tool detected INVALID objects and populated teh registry$sys_inv_objs and registry$nonsys_inv_objs tables 

then execute $ORACLE_HOME/rdbms/admin/utluiobj.sql to display only those objects which are newly invalid because of the upgrade process;

10. If you are upgrading a cluster database , then upgrade the database configuration in clusterware

srvctl upgrade database -d db-unique-name -o /new_oracle_home

11.For cluster database change the CLUSTER_DATABASE=TRUE and stop DB and start the database using the srvctl utility

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production