Friday, December 9, 2011

Patching an Oracle RAC database

Patching an Oracle RAC database is a little different from patching a single instance database because you have to patch at least two installations (CRS, database and ASM if exist), patch it in certain order, and shutdown and startup CRS services not existing in single instance installations; you can even apply patches with or without shutting down the entire RAC database, and apply them in each node or propagate patches to all nodes executing patches in just one node.

In this example we will patch a two-node Oracle 10g RAC database (10.2.0.1) with ASM, sharing the database and ASM the same home, first to upgrade it to patch 10.2.0.5.0 and finally to PSU 10.2.0.5.4, shutting down the whole RAC database and applying the patches to both nodes from one of them. Usual recommendations apply: read patch's documentation and backup your database and Oracle home before patching.

First, we will download patches 8202632 (patch 10.2.0.5.0), 12419392 (PSU 10.2.0.5.4), and 6880880 (Opatch 10.2.0.5.1), and uncompress it in one node at least:

oracle@myracn1$ ls -la
total 2659400
drwxr-xr-x 4 oracle dba 512 Nov 17 18:20 .
drwxr-xr-x 23 oracle dba 1024 Nov 17 13:03 ..
drwxr-xr-x 5 oracle dba 512 Jun 16 00:42 12419392
drwxr-xr-x 6 oracle dba 512 Nov 17 15:16 Disk1
-rw-r--r-- 1 oracle dba 11319286 Nov 15 21:33 p12419392_10205_SOLARIS64.zip
-rw-r--r-- 1 oracle dba 28674793 Nov 17 18:17 p6880880_102000_SOLARIS64.zip
-rw-r--r-- 1 oracle dba 1320728471 Nov 15 21:36 p8202632_10205_SOLARIS64.zip
-rwxr-xr-x 1 oracle dba 175026 May 19 2010 README.html

Next, shutdown all RAC instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

Next we will launch OUI to patch first the CRS software. It's very easy to follow the instructions of Oracle Universal Installer, you just have to select the CRS home when apply and be sure to select all the RAC nodes to install this patch; everything else is pretty straightforward.

oracle@myracn1$ cd /opt/oracle/parches/Disk1

oracle@myracn1$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10
Passed

Checking Temp space: must be greater than 250 MB. Actual 13017 MB Passed
Checking swap space: must be greater than 500 MB. Actual 15218 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_01-45-53PM. Please wait ...
oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

As the last step of this patch application and before leaving OUI, we have to run the root102.sh script as root after stopping CRS services, in all RAC nodes:

oracle@myracn1$ su -
Password:

root@myracn1# /opt/oracle/crs/bin/crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

root@myracn1# /opt/oracle/crs/install/root102.sh
Creating pre-patch directory for saving pre-patch clusterware files
Completed patching clusterware files to /opt/oracle/crs
Relinking some shared libraries.
ar: writing /opt/oracle/crs/lib/libn10.a
ar: writing /opt/oracle/crs/lib32/libn10.a
ar: writing /opt/oracle/crs/lib/libn10.a
Relinking of patched files is complete.
WARNING: directory '/opt/oracle' is not owned by root
Preparing to recopy patched init and RC scripts.
Recopying init and RC scripts.
Startup will be queued to init within 30 seconds.
Starting up the CRS daemons.
Waiting for the patched CRS daemons to start.
This may take a while on some systems.
.
10205 patch successfully applied.
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully deleted 1 values from OCR.
Successfully deleted 1 keys from OCR.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: myracn1 myracn1-priv myracn1
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
Creating '/opt/oracle/crs/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /opt/oracle/crs/install/paramfile.crs

root@myracn1# exit

At this point we have the CRS software patched in all nodes, therefore we will patch the ASM and database homes next. In this example ASM and the database has the same Oracle home, but otherwise you have to patch first the ASM home and next the database home.

Just in case you didn't notice yet, the 10.2.0.5 patch is the same patch for CRS, ASM and databases, therefore we will apply the same patch almost the same way as before but this time we will select the ASM and database home, of course after shutting down all RAC database instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd -
/opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

oracle@myracn1$ cd -
/opt/oracle/parches/Disk1

oracle@myracn1$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10
Passed

Checking Temp space: must be greater than 250 MB. Actual 12967 MB Passed
Checking swap space: must be greater than 500 MB. Actual 15169 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_02-23-05PM. Please wait ...
oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

Like before, we have to run as root the root.sh script before exiting the Oracle Universal Installer, in all RAC nodes:

oracle@myracn1$ su -
Password:
root@myracn1# /opt/oracle/db/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/oracle/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

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

root@myracn1# exit

At this point we have the 10.2.0.5 patch applied at software level so next step is to start up ASM and RAC services without database instances:

oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2

oracle@myracn1$ ps -fea|grep pmon
oracle 8812 6323 0 15:08:58 pts/1 0:00 grep pmon
oracle 5280 1 0 15:03:28 ? 0:01 asm_pmon_+ASM1

In order to update the database catalog, we will set the CLUSTER_DATABASE parameter as false to start up just one instance; you have to do this only in the instance you will work but the whole database catalog upgrade procedure should be done for every database in the RAC.

Just to be clear, in this example we are working with one ASM and one normal database (myrcdb) in a RAC cluster with two nodes; there is one instance of ASM running in each node (two ASM instances in total), and also one instance of the myrcdb database running in each node (two database instances in total).

Also, you don't have to run a catalog upgrade in an ASM instance, just in database instances.

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:09:17 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 385879712 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14721024 bytes
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;

System altered.

SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Before running the database catalog upgrade script we will start up the instance in upgrade mode, and as a good practice, run the upgrade information script to check if everything is fine to upgrade the catalog.

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:11:33 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 385879712 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14721024 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 11-17-2011 15:13:03
.
**********************************************************************
Database:
**********************************************************************
--> name: myrcdb
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 420 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 402 MB
.... AUTOEXTEND additional space required: 202 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 185 MB
.... AUTOEXTEND additional space required: 5 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.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 Java Packages [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Real Application Clusters [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
.

PL/SQL procedure successfully completed.

Read carefully the report of the Upgrade Information Utility script and make any changes suggested; you can just execute the catalog upgrade script but if you ignore suggestions of this report the upgrade might fail, and you don't want to end with a corrupted catalog, believe me.

After this we will execute the upgrade catalog script; remember, you have to execute this script for every database you have in your RAC cluster, but just in one instance per database:

SQL> spool patch.log
SQL> @?/rdbms/admin/catupgrd.sql

...

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-11-17 17:20:37

1 row selected.

.
Oracle Database 10.2 Upgrade Status Utility 11-17-2011 17:20:37
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.5.0 00:54:11
JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:15:23
Oracle XDK VALID 10.2.0.5.0 00:02:40
Oracle Database Java Packages VALID 10.2.0.5.0 00:01:21
Oracle XML Database VALID 10.2.0.5.0 00:12:22
Oracle Real Application Clusters VALID 10.2.0.5.0 00:00:06
Oracle Workspace Manager VALID 10.2.0.5.0 00:04:02
Oracle interMedia VALID 10.2.0.5.0 00:24:00
Oracle Expression Filter VALID 10.2.0.5.0 00:00:58
Oracle Rule Manager VALID 10.2.0.5.0 00:00:47
.
Total Upgrade Time: 02:04:12

PL/SQL procedure successfully completed.

DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> spool off

As stated in the final message of the catupgrd.sql script check that all the components are present, has a valid status and proper version, and just to be sure check the patch.log file for ORA error messages.

At this point we have almost finished applying the 10.2.0.5 patch, and we will recompile and look for invalid objects:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 17:23:06 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 436211360 bytes
Database Buffers 1157627904 bytes
Redo Buffers 14721024 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-17 17:24:02

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.


...


DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

SQL> column COMP_NAME format a50
SQL> column VERSION format a15
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

COMP_NAME VERSION STATUS
-------------------------------------------------- --------------- -----------
Oracle interMedia 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Real Application Clusters 10.2.0.5.0 VALID

11 rows selected.

If everything looks fine, we just have to restore the CLUSTER_DATABASE parameter value to true and start the RAC database and services:

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2
oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2
oracle@myracn1$ srvctl start database -d myrcdb

oracle@myracn1$ cd -
/opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE myracn1
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE ONLINE myracn2
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE ONLINE myracn2
ora....l1.inst application ONLINE ONLINE myracn1
ora....l2.inst application ONLINE ONLINE myracn2

We finished applying the 10.2.0.5 patch in our Oracle RAC! Now we will apply the PSU 10.2.0.5.4 just to be completely up to date, but first a quick check to the inventory:

oracle@myracn1$ export PATH=$PATH:$ORACLE_HOME/OPatch

oracle@myracn1$ opatch lsinventory
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-04PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_17-48-04PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


Rac system comprising of multiple nodes
Local node = myracn1
Remote node = myracn2

--------------------------------------------------------------------------------

OPatch succeeded.

Next, shutdown all RAC instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

Then, we try to apply the PSU but ...

oracle@myracn1$ cd /opt/oracle/parches
oracle@myracn1$ opatch napply 12419392 -all_nodes
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-44PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
UtilSession failed:

Patch 12419392 requires OPatch version 10.2.0.5.0.
The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.


OPatch failed with error code 73

This is why we downloaded the 6880880 patch; in order to apply the PSU 10.2.0.5.4 we need an OPatch version 10.2.0.5.0 or above.

Anyway, it's very easy to upgrade OPatch, we just have to remove the original OPatch directory and unzip the new in the Oracle Home:

oracle@myracn1$ cd $ORACLE_HOME
oracle@myracn1$ rm -r OPatch
oracle@myracn1$ unzip /opt/oracle/parches/p6880880_102000_SOLARIS64.zip
Archive: /opt/oracle/parches/p6880880_102000_SOLARIS64.zip
creating: OPatch/
creating: OPatch/ocm/
extracting: OPatch/ocm/ocm.zip
creating: OPatch/ocm/lib/
inflating: OPatch/ocm/lib/osdt_jce.jar

...


inflating: OPatch/docs/Prereq_Users_Guide.txt
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/README.txt

oracle@myracn1$ ls -la OPatch/
total 120
drwxr-xr-x 7 oracle dba 512 Nov 17 18:20 .
drwxr-x--- 62 oracle dba 1536 Nov 17 18:20 ..
drwxr-xr-x 3 oracle dba 512 Nov 3 2010 crs
drwxr-xr-x 2 oracle dba 512 Nov 3 2010 docs
-rw-r--r-- 1 oracle dba 23695 Nov 3 2010 emdpatch.pl
drwxr-xr-x 2 oracle dba 512 Nov 3 2010 jlib
drwxr-xr-x 4 oracle dba 512 Nov 3 2010 ocm
-r-x--x--- 1 oracle dba 13252 Nov 3 2010 opatch
-rwxr-xr-x 1 oracle dba 8085 Nov 3 2010 opatch.bat
-rw-r--r-- 1 oracle dba 49 Nov 3 2010 opatch.ini
-rw-r--r-- 1 oracle dba 2576 Nov 3 2010 opatch.pl
drwxr-xr-x 4 oracle dba 512 Nov 3 2010 opatchprereqs
-rw-r--r-- 1 oracle dba 2417 Nov 3 2010 README.txt

With the OPatch upgraded now we are really ready to apply the PSU to all RAC nodes at the same time, and fortunately for this patch we need to answer just a few easy questions:

oracle@myracn1$ cd /opt/oracle/parches

oracle@myracn1$ opatch napply 12419392 -all_nodes
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-23-00PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking conflicts against Oracle Home...
OPatch continues with these patches: 12419392

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y

OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate the patch to the remote nodes.


This node is part of an Oracle Real Application Cluster.
Remote nodes: 'myracn2'
Local node: 'myracn1'
Please shut down Oracle instances running out of this ORACLE_HOME on all the nodes.
(Oracle Home = '/opt/oracle/db')


Are all the nodes ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
Execution of 'sh /opt/oracle/parches/12419392/custom/scripts/pre -apply 12419392 ':


Return Code = 0

Applying patch 12419392...

ApplySession applying interim patch '12419392' to OH '/opt/oracle/db'
ApplySession: Optional component(s) [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] not present in the Oracle Home or a higher version is found.
Backing up files affected by the patch '12419392' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/qecsel.o"


...


Updating jar file "/opt/oracle/db/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_dbObjectsList.class"
Copying file to "/opt/oracle/db/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"

Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/opt/oracle/db/lib/libxml10.a" with "lib/libxml10.a/lpxpar.o"
Updating archive file "/opt/oracle/db/lib32/libxml10.a" with "lib32/libxml10.a/lpxpar.o"

Patching component oracle.precomp.common, 10.2.0.5.0...

Patching component oracle.rdbms.rman, 10.2.0.5.0...
ApplySession adding interim patch '12419392' to inventory

Verifying the update...
Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12419392 are present in Oracle Home.
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman

The local system has been patched and can be restarted.


Patching in all-node mode.

Updating nodes 'myracn2'
Apply-related files are:
FP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt"
DP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt"
MP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt"
RC = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/remote_cmds.txt"

Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt" with actual path.
Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/plsql/lib; /usr/ccs/bin/make -f ins_plsql.mk iwrap ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/network/lib; /usr/ccs/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/precomp/lib; /usr/ccs/bin/make -f ins_precomp.mk proc ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk irman ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

RC file not exist. There are no commands to be run on the remote nodes.

All nodes have been patched. You may start Oracle instances on the local system and nodes 'myracn2'

UtilSession: N-Apply done.

OPatch succeeded.

Applying a PSU is faster than applying a patch that needs OUI, but for this particular PSU we also need to upgrade the database catalog after starting up the RAC database and services:

oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2
oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2
oracle@myracn1$ srvctl start database -d myrcdb

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE myracn1
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE ONLINE myracn2
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE ONLINE myracn1
ora....l1.inst application ONLINE ONLINE myracn1
ora....l2.inst application ONLINE ONLINE myracn2

To upgrade the database catalog, we will run the catbundle.sql script for every database in the RAC but just once; for this example we will run it for the myrcdb just one time:

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 18:43:44 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Generating apply and rollback scripts...


...


Updating registry...

1 row created.


Commit complete.

Check the following log file for errors:
/opt/oracle/db/cfgtoollogs/catbundle/catbundle_PSU_myrcdb_APPLY_2011Nov17_18_44_18.log

As suggested, it might be good to check the log file for ORA errors just to be sure. And as the last step, we will recompile and look for invalid objects:

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-17 18:44:47

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.


...


DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

SQL> exit

That's it! At this point we have an up to date RAC cluster with Oracle 10.2.0.5.4; we had to shut down the database service to apply this patch but this way is easier and quicker than applying a patch with the rolling upgrade method. Doing a rolling upgrade means applying a patch with no downtime, but the patch must be suitable for this method and is more complex and time-consuming than just shutting down a RAC and applying a patch.

Listing the inventory you can check that the 12419392 patch is registered:

oracle@myracn1$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-46-17PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_18-46-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch 12419392 : applied on Thu Nov 17 18:28:53 CST 2011
Unique Patch ID: 13856866
Created on 15 Jun 2011, 22:41:17 hrs PST8PDT
Bugs fixed:
6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356
9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406
10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865
11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739
8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576
7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422
10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700
12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269
12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296
10157402, 11737047



Rac system comprising of multiple nodes
Local node = myracn1
Remote node = myracn2

--------------------------------------------------------------------------------

OPatch succeeded.

By the way, did you notice that there was no PSU for CRS? Usually there is the same patch and PSU for all Oracle Database components (CRS, ASM, databases), and also for all platforms, but in this particular case there was no 10.2.0.5.4 PSU for CRS. If there is, it would be preferably (or even mandatory) to have the same patch and PSU across all Oracle Database components, but this time it's fine this way.

More information:

10.2.0.5 Current Recommended Patches

7 comments:

  1. Superb article..thanks

    -Satya
    http://satya-racdba.blogspot.com/

    ReplyDelete
  2. Thanks for such clear explanation.

    ReplyDelete
  3. Small doubt in PSU we dont have below 2 steps like we have in CPU

    Run the pre-check script
    SQL> start ?/cpu/view_recompile/recompile_precheck_jan2008cpu.sql

    Run the view recompilation script.
    SQL> STARTUP UPGRADE
    SQL> start ?/cpu/view_recompile/view_recompile_jan2008cpu.sql


    ReplyDelete
    Replies
    1. Not all the patches are equal so it's very important to check the README.TXT file and other documentation that comes with the patch. Sometimes the steps change, or the prerequisites might be different than the previous patch, or your particular database setup might trigger one of the known issues related to this particular patch, therefore it's better not skipping reading the patch documentation.

      About those scripts (recompile_precheck_jan2008cpu.sql and view_recompile_jan2008cpu.sql), they seem to be related to a specific patch (Critical Patch Update JAN 2008), therefore that's why they are not included in this particular patch; I have never seen them before. I guess it's better to not running them even if you have that scripts somewhere in your servers.

      Delete
  4. Superb Doc. Thanks a lot, it saved my lot of time.
    Just wondering if you have same doc on Rolling upgrade..?
    Thanks for your time and help.

    -Alok

    ReplyDelete
    Replies
    1. Thanks, I'm glad to know you found this post useful. Unfortunately I have no experience with rolling upgrades, but if I do that later be sure I'll post the procedure here.

      Delete
  5. Np, thank you so much!.

    -Alok

    ReplyDelete