Tuesday, August 23, 2011

RDBMS Online Patching

(originally published at Pythian)
If you support Oracle RDBMS 11.2.0.2 and want to zero downtime applying
patches to databases then it is time to have a look at new possibility
of Online Patching delivered with 11.2.0.2 version and described here
RDBMS Online Patching Aka Hot Patching [ID 761111.1]

The syntax is "opatch apply online -connectString ... " but I could not find description
of the online option (only -connectString) using opatch -help (version 11.2.0.1.6)
although based on the note it is available since 11.1.0.6 version of opatch

And while going through README for the patch I found the blog
about online patching - Applying online patch on 11gr2
which helped me greatly to get proper syntax for online patching.

I had test RAC database with applied PSU3 and checked if there
were any online patches available on MOS. I found quite a few and downloaded
several of them to check what information "opatch query" will show.

Surprisingly, "opatch query" did not show any of them to be applicable online
but all of them had online directory with .pch file underneath and all
of them had "Online Installable Patch" in their README
for example 10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]
[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query .

Invoking OPatch 11.2.0.1.6
...
Need to shutdown Oracle instances: true
Patch is roll-backable: true
Patch is a "Patchset Update": false
Patch is a rolling patch: true
Patch has sql related actions: false
Patch is an online patch: false
Patch is a portal patch: false
Patch is an "auto-enabled" patch: false

List of platforms supported:
226: Linux x86-64

List of prereq patches:
12419331

List of overlay patches:
12419331

List of bugs to be fixed:
10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]
...
OPatch succeeded.

[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query -is_online_patch .

Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_01-41-03AM.log

--------------------------------------------------------------------------------
Patch is an online patch: false


OPatch succeeded.

[oracle@r1 10040035]$ tree .

.
|-- README.txt
|-- etc
| |-- config
| | |-- actions.xml
| | |-- deploy.xml
| | `-- inventory.xml
| `-- xml
| |-- GenericActions.xml
| `-- ShiphomeDirectoryStructure.xml
|-- files
| `-- lib
| `-- libserver11.a
| `-- kcl.o
`-- online
|-- etc
| |-- config
| | |-- actions.xml
| | |-- deploy.xml
| | `-- inventory.xml
| `-- xml
| |-- GenericActions.xml
| `-- ShiphomeDirectoryStructure.xml
`-- files
`-- hpatch
`-- bug10040035.pch

I have been able to apply all of them without any downtime and all
of them appeared to be ENABLED on both instances:
r1221> oradebug patch list


Patch File Name State
================ =========
bug9795214.pch ENABLED
bug10040035.pch ENABLED
bug10222480.pch ENABLED
bug11670161.pch ENABLED
bug11853815.pch ENABLED
bug9829397.pch ENABLED


Interesting enough that either for RAC or for standalone database
"opatch apply online" was converted to RAC mode "-all_nodes"
although patches were applied properly for both clustered and non-clustered
environments.
RAC database:
[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply \

online -connectString r1221:sys:pwd:r1,r1222:sys:pwd:r2
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '10040035' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
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
Backing up files...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10040035.pch', on database 'r1221'.


Patching in all-node mode.

Updating nodes 'r2'
Apply-related files are:
FP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt"
DP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/remote_cmds.txt"

Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_files.txt.instantiated" by replacing $ORACLE_HOME
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt"
with actual path.
Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_dirs.txt.instantiated" by replacing $ORACLE_HOME
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt"
with actual path.
Propagating directories to remote nodes...
Installing and enabling the online patch 'bug10040035.pch', on database 'r1222' on node 'r2'.

Patch 10040035 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log

OPatch succeeded.


non-RAC database
[oracle@oel5 11800959]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply online -connectString d122:sys:pwd:

Invoking OPatch 11.2.0.1.1

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


Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_15-27-30PM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '11800959' to OH '/u02/app/oracle/product/11.2.0/dbhome_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '11800959' for restore. This might take a while...
Backing up files affected by the patch '11800959' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug11800959.pch', on database 'd122'.

ApplySession adding interim patch '11800959' to inventory

Verifying the update...
Inventory check OK: Patch ID 11800959 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 11800959 are present in Oracle Home.

OPatch succeeded.

I think that conversion a little bit misleading for RAC database patching
because credentials should be entered for all instances of clustered database
and if you do not type it for one of them, there would not be any errors,
execution will be finished but patch will be applied only for nodes which credentials
are provided.

Online patches can be easily managed by oradebug and can be turned
on and off at each instance of RAC database or for the whole non-RAC database.
However "opatch lsinventory" does not show status of online patch
only if it is applied.
d122> oradebug patch list


Patch File Name State
================ =========
bug11800959.pch ENABLED

d122> oradebug patch disable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch DISABLED

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch ENABLED

d122> oradebug patch disable bug11800959.pch term
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch DISABLED

[oracle@oel5 u01]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.1

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


Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-30_09-36-16AM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-08-30_09-36-16AM.txt

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

Oracle Database 11g 11.2.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch (online) 11800959: applied on Mon Aug 29 15:27:55 EST 2011
Unique Patch ID: 13914499
Created on 8 Jul 2011, 03:51:56 hrs PST8PDT
Bugs fixed:
11800959



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

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch ENABLED


Oracle Support encourages to apply online patches only in cases when
downtime can not be scheduled immediately but strongly recommends to replace
them with normal patches that requires shutdown of instances.

And all my tests were done on test databases so I have not
taken any risk yet to do it on production. But the feature is very useful
and number of patches being steadily increased over last releases.

Have a good day!

Monday, August 22, 2011

CheckSystemSpace ignorance

(originally published at Pythian)
Every installation is unique. It can be the same software and hardware
but one installation will squease you like a lemon in jucier another one
will make you feel that you and only you is the boss here.

The way how it behaves depends not only on stars location and on
Saturn position in Zenith but also on how scripts finished, what SA did not
implement and change and how vigorously developers spent a day.

In any case you should put all your heart to the process either it is GUI
or silent installation, feel the breath of installer and give heed to every
entry appeared in the output log.

But I digressed...

Today the post is about one of the hidden parameters for OPatch which
is the main contributor to non-simple process of patching.

11.2.0.2.3 Grid Infrastructure patch failed at one of test RAC systems
and only log showed that CheckSystemSpace triggered the failure.
It was asking for ~3Gb of free space, but file system did not have that much.

Recalling -ingoreSysPreReqs option for runInstaller, I checked MOS
trying to find any option/parameter/variable that can allow to ignore
system space check to go further with installation.
Of course, if it was production system I would rather clean it or increase
space, but for sake of testing I decided to look for any option
that would "trick" opatch utility.

And finally it was found and helped me in that OPATCH_DEBUG variable
which after setting to true forced opatch to splash out lots of detailed
information together with list of different variables.

SKIP_VERIFY and SKIP_VERIFY_SPACE were among them and
I added SKIP_VERIFY_SPACE to opatch command line and output
was changed to another one but paying no heed to space issue.

before
/bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch napply  \

/home/oracle/install/patches/11.2.0.2.3GI/12419353 -local -silent \
-ocmrf /home/oracle/install/patches/ocm.rsp \
-oh /u01/app/grid/11.2.0 '
...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(3131607220) is not available.
UtilSession failed: Prerequisite check "CheckSystemSpace" failed.
...

after
[root@r1 ~]# /bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch napply \

/home/oracle/install/patches/11.2.0.2.3GI/12419353 -local -silent \
-ocmrf /home/oracle/install/patches/ocm.rsp -oh \
/u01/app/grid/11.2.0 \
OPatch.SKIP_VERIFY_SPACE=true '
...
Prerequisite check "CheckApplicable" failed.
The details are:
....


And if it is used with CheckSystemSpace of prereq it does not avoid the check
anyway
/bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch prereq  \

CheckSystemSpace \
-phBaseDir /home/oracle/install/patches/11.2.0.2.3GI/12419353 \
OPatch.SKIP_VERIFY_SPACE=true '
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6

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

PREREQ session


Oracle Home : /u01/app/grid/11.2.0

Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/grid/11.2.0/cfgtoollogs/opatch/opatch2011-08-25_23-46-59PM.log

Invoking prereq "checksystemspace"


Prereq "checkSystemSpace" failed.


The details are:

Required amount of space(3131607220) is not available.

OPatch succeeded.


Worth to note that those parameters can be added to "opatch napply"
or to "opatch apply" execution but not to "opatch auto" call
[root@r1 11.2.0.2.3GI]# /u01/app/grid/11.2.0/OPatch/opatch \

auto /home/oracle/install/patches/11.2.0.2.3GI \
-oh /u01/app/grid/11.2.0 OPatch.SKIP_VERIFY=true \
OPatch.SKIP_VERIFY_SPACE=true
Executing /usr/bin/perl /u01/app/grid/11.2.0/OPatch/crs/patch112.pl
-patchdir /home/oracle/install/patches -patchn 11.2.0.2.3GI
-oh /u01/app/grid/11.2.0 OPatch.SKIP_VERIFY=true
OPatch.SKIP_VERIFY_SPACE=true
-paramfile /u01/app/grid/11.2.0/crs/install/crsconfig_params
opatch auto log file location is
/u01/app/grid/11.2.0/OPatch/crs/../../cfgtoollogs/opatchauto2011-08-26_00-15-44.log
Invalid extra options passed: OPatch.SKIP_VERIFY=true
OPatch.SKIP_VERIFY_SPACE=true
Usage:
...


Have a good day!