RAC database upgrade from 11.2.0.1 to 11.2.0.3

In this post, I’m demonstrating on the steps involved in upgrading the RAC database from version 11.2.0.1 to 11.2.0.3

RAC nodes: 10gnode1, 10gnode2
Current Database version: 11.2.0.1 Enterprise Edition
11.2.0.1 Oracle HOME Path: /u01/app/oracle/product/11.2.0/db_1

As per Oracle’s recommendations, the database is upgraded using an out-of-place upgrade option. So, I’m installing 11.2.0.3 database software
on a new path (/u01/app/oracle/product/11.2.0/db3)
Below are the steps involved in installing the Oracle Database Software version 11.2.0.3 on a new home.

On the first node (10gnode1):
1. Run the runInstaller from the unzipped 11.2.0.3 database software.
2. Select the Skip software updates option.
3. Select install database software only option.
4. Select all the cluter nodes on which the software needs to be installed.
5. Select the lanugage as English and the Edition as per your requirements. My requirement here is that I’m upgrading database from 11.2.0.1
Enterprise Edition database to 11.2.0.3 Enterprise Edition and hence selecting Enterprise Edition.
6. Provide the path where the new installation needs to be installed.
7. Proceed with the pre-requisite checks and if everything success, then proceed with the installation. Else, fix the errors given during the
pre-requisite check and proceed with the installation.
8. As root, run the root.sh on node1 followed by node2.

[root@10gnode1 ~]# /u01/app/oracle/product/11.2.0/db3/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db3

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.
yFinished product-specific root actions.
[root@10gnode1 ~]#

Run the root.sh script on 2nd node (10gnode2):

[root@10gnode2 ~]# /u01/app/oracle/product/11.2.0/db3/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db3

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.

Now let’s perform the pre-upgrade prerequisite checks. Copy the utlu112i.sql script from the newly installed 11.2.0.3 home to a temporary location. Here, I’ve copied the script to location “/u03”.
Execute this pre-upgrade script (utlu112i.sql) from the 11.2.0.1 database.

[oracle@10gnode1 ~]$ sqlplus sys/oracle@srprim1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 31 09:49:32 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create pfile='/u03/upgrd11201_to_11203/initsrprim1.ora' from spfile;

File created.

SQL>
SQL> spool '/u03/preupgd.txt'
SQL>
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-31-2013 09:55:26
Script Version: 11.2.0.3.0 Build: 001
.
*************************************************************
Database:
*************************************************************
--> name: SRPRIM
--> version: 11.2.0.1.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V11
.
*************************************************************
Tablespaces: [make adjustments in the current environment]
*************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 685 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 450 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: 78 MB
.
*************************************************************
Flashback: OFF
*************************************************************
*************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-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: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
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.

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

Perform the recommendations given out by the execution of the pre-upgrade (utlu112i.sql) script.

SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

Also, create a pfile to a temporary location to make necessary changes in order to remove the Cluster parameters.Following are the cluster parameters which need to be commented out before proceeding with the DB upgrade.

 

cluster_database=true
srprim2.instance_number=2
srprim1.instance_number=1
srprim2.thread=2
srprim1.thread=1
srprim2.undo_tablespace='UNDOTBS2'

Stop the database running on all the nodes.

[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl stop database -d srprim

Copy the password file from the 11.2.0.1 RDBMS_HOME/dbs (/u01/app/oracle/product/11.2.0/db_1/dbs) to the newly installed 11.2.0.3
RDBMS_HOME/dbs (/u01/app/oracle/product/11.2.0/db3/dbs) location. Also copy the above created pfile from temporary location (/u03/upgrd11201_to_11203/initsrprim1.ora) to the newly installed 11.2.0.3 RDBMS_HOME/dbs location.

[oracle@10gnode1 ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsrprim1 /u01/app/oracle/product/11.2.0/db3/dbs/
[oracle@10gnode1 ~]$ cp /u03/upgrd11201_to_11203/initsrprim1.ora /u01/app/oracle/product/11.2.0/db3/dbs/

Now, remove the cluster parameters set in the PFILE copied to 11.2.0.3 RDBMS_HOME/dbs location.

[oracle@10gnode1 ~]$ vi /u01/app/oracle/product/11.2.0/db3/dbs/initsrprim1.ora

Comment out the following cluster parameters.

 

#*.cluster_database=true
#srprim2.instance_number=2
#srprim1.instance_number=1
#srprim2.thread=2
#srprim1.thread=1
#srprim2.undo_tablespace='UNDOTBS2'

Startup only 1 instance (SRPRIM1) in upgrade mode on 10gnode1 using the newly modified pfile placed at 11.2.0.3 RDBMS_HOME/dbs location.

[oracle@10gnode1 ~]$ export ORACLE_SID=srprim1
[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db3/bin
[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 10:26:53 2013

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

Connected to an idle instance.

SQL> startup upgrade pfile=$ORACLE_HOME/dbs/initsrprim1.ora
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.

Execute the CATUPGRD.SQL script to upgrade the database dictionaries.

SQL> spool '/u03/upgrd11201_to_11203/catupgrade.txt'
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/catupgrd.sql

Once the above script is executed, the database is shutdown automatically.Cross check if there were any errors reported in the execution of
the above script.

Startup the instance in normal mode now using the same PFILE.

[oracle@10gnode1 bin]$ export ORACLE_SID=srprim1
[oracle@10gnode1 bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 bin]$ cd $ORACLE_HOME/bin
[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:07:25 2013

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

Connected to an idle instance.

SQL> startup pfile=$ORACLE_HOME/dbs/initsrprim1.ora
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.
SQL>
SQL> spool '/u03/upgrd11201_to_11203/catuppst.sql'
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/catuppst.sql

Execute the CATUPPST.SQL script to perform remaining upgrade actions that do not require the database to be in UPGRADE mode.
Check if the components have been upgraded and also the status.

SQL> select comp_id,comp_name,version,status from dba_registry;

COMP_ID  COMP_NAME                     VERSION      STATUS
-------  ----------------------------- -----------  --------
OWB      OWB                           11.2.0.1.0   VALID
APEX     Oracle Application Express    3.2.1.00.10  VALID
EM       Oracle Enterprise Manager     11.2.0.3.0   VALID
AMD      OLAP Catalog                  11.2.0.3.0   VALID
SDO      Spatial                       11.2.0.3.0   VALID
ORDIM    Oracle Multimedia             11.2.0.3.0   VALID
XDB      Oracle XML Database           11.2.0.3.0   VALID
CONTEXT  Oracle Text                   11.2.0.3.0   VALID
EXF      Oracle Expression Filter      11.2.0.3.0   VALID
RUL      Oracle Rules Manager          11.2.0.3.0   VALID
OWM      Oracle Workspace Manager      11.2.0.3.0   VALID

COMP_ID  COMP_NAME                         VERSION    STATUS
-------  --------------------------------- ---------- -------
CATALOG Oracle Database Catalog Views      11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID
JAVAVM  JServer JAVA Virtual Machine       11.2.0.3.0 VALID
XML     Oracle XDK                         11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages      11.2.0.3.0 VALID
APS     OLAP Analytic Workspace            11.2.0.3.0 VALID
XOQ     Oracle OLAP API                    11.2.0.3.0 VALID
RAC     Oracle Real Application Clusters   11.2.0.3.0 VALID

19 rows selected.

Check for the INVALID objects and validate them by executing UTLRP.SQL script.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
6189

SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

Now, once the DB is upgraded, we need to add the other instance (srprim2) into cluster. Hence we need to revert the cluster changes made to
the PFILE. For this shutdown the srprim1 instance and uncomment the cluster parameters in initsrprim1.ora PFILE that were commented earlier.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#*.cluster_database=true
#srprim2.instance_number=2
#srprim1.instance_number=1
#srprim2.thread=2
#srprim1.thread=1
#srprim2.undo_tablespace='UNDOTBS2'

Also, create a global SPFILE that is placed on a shared location to be accessed by all the instances within the cluster.

[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:25:16 2013

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

Connected to an idle instance.

SQL> create spfile='+DATA/srprim/spfilesrprim.ora' from pfile;

File created.

Backup the pfile initsrprim1.ora in the 11.2.0.3 DB Home by renaming it to initsrprim1.ora.bak. Create a new pfile “initsrprim1.ora” in the
same location and add only one entry to it.

SPFILE='+DATA/srprim/spfilesrprim.ora'

We are making this so that the instances (srprim1 and srprim2) are using a global spfile and not local PFILES.

[oracle@10gnode1 bin]$ cd $ORACLE_HOME/dbs
[oracle@10gnode1 dbs]$ mv initsrprim1.ora initsrprim1.ora.bak
[oracle@10gnode1 dbs]$
[oracle@10gnode1 dbs]$ vi initsrprim1.ora
SPFILE='+DATA/srprim/spfilesrprim.ora'

[oracle@10gnode1 dbs]$ ls -lrt
total 20
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Dec 31 10:09 orapwsrprim1
-rw-rw---- 1 oracle oinstall 1544 Dec 31 10:27 hc_srprim1.dat
-rwxr-xr-x 1 oracle oinstall 1467 Dec 31 11:24 initsrprim1.ora.bak
-rw-r--r-- 1 oracle oinstall 39 Dec 31 11:27 initsrprim1.ora
[oracle@10gnode1 dbs]$

Copy the PFILE on 10gnode1 to 10gnode2 and rename it to initsrprim2.ora

[oracle@10gnode1 dbs]$ scp initsrprim1.ora oracle@10gnode2:/u01/app/oracle/product/11.2.0/db3/dbs/initsrprim2.ora
initsrprim1.ora 100% 39 0.0KB/s 00:00
[oracle@10gnode1 dbs]$

Remove the SRPRIM database configuration from 11.2.0.1 RDBMS HOME and configure it on 11.2.0.3 RDBMS HOME.

[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/bin
[oracle@10gnode1 bin]$ ./srvctl remove database -d srprim
Remove the database srprim? (y/[n]) y
[oracle@10gnode1 bin]$
Configuring SRPRIM database on 11.2.0.3 RDBMS HOME.
[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db3/bin
[oracle@10gnode1 bin]$
[oracle@10gnode1 bin]$
[oracle@10gnode1 bin]$ ./srvctl add database -d srprim -o /u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 bin]$ ./srvctl add instance -i srprim1 -n 10gnode1 -d srprim
[oracle@10gnode1 bin]$ ./srvctl add instance -i srprim2 -n 10gnode2 -d srprim
[oracle@10gnode1 bin]$ ./srvctl config database -d srprim
Database unique name: srprim
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db3
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: srprim
Database instances: srprim1,srprim2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

In a simpler method and recommended one, the database configuration and its’ services can be upgraded at srvctl using the “srvctl upgrade database -d db_unique_name -o
$new_ORACLE_HOME” syntax than following the method done above. The above method would not upgrade the services and will have to be created manually on the new oracle home.

[oracle@10gnode1 ~]$ srvctl upgrade database -d srprim -o /u01/app/oracle/product/11.2.0/db3</pre>
<pre></pre>
<pre>

Run srvctl from the database Home to which the database configuration needs to be upgraded.
Startup both the instances and cross check the functionality.

[oracle@10gnode1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ echo $ORACLE_SID
srprim1
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:36:39 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.

SQL>
SQL> show parameter spfile

NAME   TYPE   VALUE
------ ------ ---------------------------------
spfile string +DATA/srprim/spfilesrprim.ora
SQL>
SQL> show parameter cluster_

NAME                       TYPE      VALUE
-------------------------- --------- -----------
cluster_database           boolean   TRUE
cluster_database_instances integer   2
cluster_interconnects      string

[oracle@10gnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:59:14 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

[oracle@10gnode1 ~]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1. Instance status: Open.
Instance srprim2 is not running on node 10gnode2

[oracle@10gnode1 ~]$ srvctl start instance -i srprim2 -d srprim
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1. Instance status: Open.
Instance srprim2 is running on node 10gnode2. Instance status: Open.
[oracle@10gnode1 ~]$

Note that there were services running on both the instances before upgrading. We need to start them as well after the database is upgraded.

[oracle@10gnode1 ~]$ srvctl config database -d srprim
Database unique name: srprim
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db3
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: srprim
Database instances: srprim1,srprim2
Disk Groups: DATA,FRA
Mount point paths:
Services: newsrv
Type: RAC
Database is administrator managed

[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl config service -s newsrv -d srprim
Service name: newsrv
Service is enabled
Server pool: srprim_newsrv
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: srprim1,srprim2
Available instances:

Now that the databases have been upgraded, we can remove the 11.2.0.1 DB Home. But before removing it, make sure that you have copied the
tnsnames.ora file from the 11.2.0.1 DB Home to the 11.2.0.3 HOME. Also make necessary changes to the Environment settings by specifying the
new 11.2.0.3 ORACLE HOME path in the profile files.

 

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.