Thursday, October 17, 2013

Patching 12c (12.1.0.1) RAC with October 2013 PSU

First critical patch update for 12c was released Oct 15 2013. This post looks at the difference in patching 12c RAC environment (with role separation) compared to 11.2 environment. The environment used for patching is the environment that was upgraded from 11.2 to 12c.
First thing to notice is the name of the patch. On the readme.html that is included in the patch it is referred to as "Oracle Grid Infrastructure System Patch" instead of "Oracle Grid Infrastructure Patch Set Update" (More jargon to converse with!). However in the PSU and CPU availability document (1571391.1) it is still referred to as PSU (GI 12.1.0.1.1 PSU Patch 17272829). "GI System Patch" is used throughout the readme.html document so it's pretty safe to assume that's how the 12c patches going to be referred from now on.
Opatch auto option has been merged into one single command called "opatchauto".
However it is still possible to apply the patch manually. But at the time of this post (16/10/2013) the document with instruction for manual patch apply/rollback (1591616.1) is not available on MOS though the readme.html mentions it (shouldn't this be available before patches are released?). When this become available follow it for manual patch apply. In mean time as a workaround generateSteps option could be used to list the steps used by opatchauto
/opt/app/12.1.0/grid/OPatch/opatchauto apply  /usr/local/patch/17272829  -ocmrf ocm.rsp  -generateSteps
OPatch 12.1.0.1.2 or later is needed to apply this patch. Installing new OPatch on GI_HOME causes the following
unzip p6880880_121010_Linux-x86-64.zip
  ..
  inflating: OPatch/operr
error:  cannot create PatchSearch.xml
        Permission denied
File PatchSearch.xml is to be copied (or unzipped) to GI_HOME outside the OPatch directory and since GI_HOME has restrictive permission unzipping as grid user causes the above error. The file could be copied manually as root user into GI_HOME or ignore the error (this caused no issue when installing the patch). Looking inside the PatchSearch.xml file it seem this might be used to get the OPatch from MOS (has urls of MOS and OPatch including CSI number). No such issue installing the new OPatch on ORACLE_HOME.
Next issue is related to patch location. Readme.html mentions to use "PATH_TO_PATCH_DIRECTORY" in the opatchauto command. PATH_TO_PATCH_DIRECTORY is the location where the patch was unzipped. This is same as the 11.2. However this location is not recognized by the opatchauto command and complains of the missing bundle.xml file.
[grid@rhel6m2 patches]$ pwd
/usr/local/patches  <<-- this becomes the PATH_TO_PATCH_DIRECTORY (same as 11.2 as shown here)
[grid@rhel6m2 patches]$ ls
p17027533_121010_Linux-x86-64.zip
[grid@rhel6m2 patches]$ unzip p17027533_121010_Linux-x86-64.zip
[grid@rhel6m2 patches]$ su <-- preparing to run opatchauto as root user
[root@rhel6m2 patches]# /opt/app/12.1.0/grid/OPatch/opatchauto apply /usr/local/patches -ocmrf ocm.rsp

Parameter Validation: Successful

Patch Collection failed: Invalid patch location "/usr/local/patches" as there is no bundle.xml file in it or its parent directory.

opatchauto failed with error code 2.
So using the location where the patch was unzipped doesn't work unlike 11.2. Give the full path to the patch directory
[root@rhel6m2 patches]# /opt/app/12.1.0/grid/OPatch/opatchauto apply /usr/local/patches/17272829 -ocmrf ocm.rsp

OPatchauto version : 12.1.0.1.2
OUI version        : 12.1.0.1.0
Running from       : /opt/app/12.1.0/grid

opatchauto log file: /opt/app/12.1.0/grid/cfgtoollogs/opatchauto/17272829/opatch_gi_2013-10-16_15-39-34_deploy.log

Parameter Validation: Successful
...
Apply of patch progress.
Also worth noting is that along with opatchauto keyword apply must be given without it a syntax error occurs
[root@rhel6m1 patches]# /opt/app/12.1.0/grid/OPatch/opatchauto /usr/local/patches/17272829 -ocmrf ocm.rsp
OPatch Automation Tool
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Syntax Error... Unrecognized Command or Option (/usr/local/patches/17272829): 1st argument must be one of the following:
   apply
   rollback
   version
   ..
Section 2.3 on the readme.html does mention apply keyword in the commands but in 2.4 Patch installation section the apply key word missing. This is another difference compared to 11.2 where there was no apply key word when opatch auto option was used. Rollback commands on section 2.7 are also incorrectly listed. Correct rollback commands are listed on section 2.3.
The readme.html for GI system patch doesn't list any post installation task such as loading modified SQLs. This is automatically run as part of the patch apply. Once the patch is applied on the last node of the RAC the registry history is updated
SQL> select * from dba_registry_history;

ACTION_TIME                    ACTION     NAMESPACE  VERSION            ID BUNDLE_SER COMMENTS
------------------------------ ---------- ---------- ---------- ---------- ---------- ------------------------------
12-AUG-13 04.28.26.378432 PM   UPGRADE    SERVER     12.1.0.1.0                       Upgraded from 11.2.0.3.0
12-AUG-13 04.34.09.496894 PM   APPLY      SERVER     12.1.0.1            0 PSU        Patchset 12.1.0.0.0
16-OCT-13 04.05.54.514261 PM   APPLY      SERVER     12.1.0.1            1 PSU        PSU 12.1.0.1.1
SQL apply is logged in dba_registry_sqlpatch table
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select * from dba_registry_sqlpatch;

  PATCH_ID ACTION     STATUS          ACTION_TIME                    DESCRIPTIO LOGFILE
---------- ---------- --------------- ------------------------------ ---------- --------------------------------------------------------------------------------
  17027533 APPLY      SUCCESS         16-OCT-13 05.54.42.295071 PM   sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_CDB12C_
                                                                                CDBROOT_2013Oct16_17_51_30.log
Each PDB will also have its own log file entry in the dba_registry_sqlpatch view
SQL> alter session set container=pdb12c;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB12C

SQL> select * from dba_registry_sqlpatch;

  PATCH_ID ACTION     STATUS          ACTION_TIME                    DESCRIPTIO LOGFILE
---------- ---------- --------------- ------------------------------ ---------- --------------------------------------------------------------------------------
  17027533 APPLY      END             16-OCT-13 05.54.44.488402 PM   sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_CDB12C_
                                                                                PDB12C_2013Oct16_17_51_49.log
Even the pdb$seed database could be queried this way to confirm that it is also updated with the SQL changes made by the patch. Any new PDB created using the seed PDB also gets these modification and no patch post installation work is necessary.




Full output of running the opaatchauto is given below
[root@rhel6m1 17272829]# /opt/app/12.1.0/grid/OPatch/opatchauto apply `pwd` -ocmrf ../ocm.rsp
OPatch Automation Tool
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

OPatchauto version : 12.1.0.1.2
OUI version        : 12.1.0.1.0
Running from       : /opt/app/12.1.0/grid

opatchauto log file: /opt/app/12.1.0/grid/cfgtoollogs/opatchauto/17272829/opatch_gi_2013-10-16_14-23-50_deploy.log

Parameter Validation: Successful


Grid Infrastructure home:
/opt/app/12.1.0/grid
RAC home(s):
/opt/app/oracle/product/12.1.0/dbhome_1

Configuration Validation: Successful

Patch Location: /usr/local/patches/17272829
Grid Infrastructure Patch(es): 17027533 17077442 17303297
RAC Patch(es): 17027533 17077442

Patch Validation: Successful

Stopping RAC (/opt/app/oracle/product/12.1.0/dbhome_1) ... Successful
Following database(s) were stopped and will be restarted later during the session: std11g2

Applying patch(es) to "/opt/app/oracle/product/12.1.0/dbhome_1" ...
Patch "/usr/local/patches/17272829/17027533" successfully applied to "/opt/app/oracle/product/12.1.0/dbhome_1".
Patch "/usr/local/patches/17272829/17077442" successfully applied to "/opt/app/oracle/product/12.1.0/dbhome_1".

Stopping CRS ... Successful

Applying patch(es) to "/opt/app/12.1.0/grid" ...
Patch "/usr/local/patches/17272829/17027533" successfully applied to "/opt/app/12.1.0/grid".
Patch "/usr/local/patches/17272829/17077442" successfully applied to "/opt/app/12.1.0/grid".
Patch "/usr/local/patches/17272829/17303297" successfully applied to "/opt/app/12.1.0/grid".

Starting CRS ... Successful

Starting RAC (/opt/app/oracle/product/12.1.0/dbhome_1) ... Successful

SQL changes, if any, are applied successfully on the following database(s): std11g2

Apply Summary:
Following patch(es) are successfully installed:
GI Home: /opt/app/12.1.0/grid: 17027533, 17077442, 17303297
RAC Home: /opt/app/oracle/product/12.1.0/dbhome_1: 17027533, 17077442

On a system with PDBs that have dynamic services created for them, stopping RAC step will have the following output listing the service
Stopping RAC (/opt/app/oracle/product/12.1.0/dbhome_1) ... Successful
Following database(s) were stopped and will be restarted later during the session: -pdbsvc,cdb12c
If there are no services created for the PDBs then only the CDB is mentioned in the output
Stopping RAC (/opt/app/oracle/product/12.1.0/dbhome_1) ... Successful
Following database(s) were stopped and will be restarted later during the session: cdb12c

Apply has the option of analyze which says
-analyze
              This option runs all the required prerequisite checks to confirm
              the patchability of the system without actually patching or
              affecting the system in any way.
Even though it says "runs all the required prerequisite checks to confirm the patchability" this seem not be the case. Analyze could suceed and actual patch apply could fail.
[root@rhel12c2 patch]# /opt/app/12.1.0/grid/OPatch/opatchauto apply /usr/local/patch/17272829 -ocmrf ocm.rsp -analyze
OPatch Automation Tool
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

OPatchauto version : 12.1.0.1.2
OUI version        : 12.1.0.1.0
Running from       : /opt/app/12.1.0/grid

opatchauto log file: /opt/app/12.1.0/grid/cfgtoollogs/opatchauto/17272829/opatch_gi_2013-10-17_11-28-37_analyze.log

NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.

Parameter Validation: Successful

Grid Infrastructure home:
/opt/app/12.1.0/grid
RAC home(s):
/opt/app/oracle/product/12.1.0/dbhome_1

Configuration Validation: Successful

Patch Location: /usr/local/patch/17272829
Grid Infrastructure Patch(es): 17027533 17077442 17303297
RAC Patch(es): 17027533 17077442

Patch Validation: Successful

Analyzing patch(es) on "/opt/app/oracle/product/12.1.0/dbhome_1" ...
Patch "/usr/local/patch/17272829/17027533" successfully analyzed on "/opt/app/oracle/product/12.1.0/dbhome_1" for apply.
Patch "/usr/local/patch/17272829/17077442" successfully analyzed on "/opt/app/oracle/product/12.1.0/dbhome_1" for apply.

Analyzing patch(es) on "/opt/app/12.1.0/grid" ...
Patch "/usr/local/patch/17272829/17027533" successfully analyzed on "/opt/app/12.1.0/grid" for apply.
Patch "/usr/local/patch/17272829/17077442" successfully analyzed on "/opt/app/12.1.0/grid" for apply.
Patch "/usr/local/patch/17272829/17303297" successfully analyzed on "/opt/app/12.1.0/grid" for apply.

SQL changes, if any, are analyzed successfully on the following database(s): cdb12c

Apply Summary:
Following patch(es) are successfully analyzed:
GI Home: /opt/app/12.1.0/grid: 17027533, 17077442, 17303297
RAC Home: /opt/app/oracle/product/12.1.0/dbhome_1: 17027533, 17077442

opatchauto succeeded.

<<------ Running of actual patch command ----------->>
[root@rhel12c2 patch]# /opt/app/12.1.0/grid/OPatch/opatchauto apply /usr/local/patch/17272829 -ocmrf ocm.rsp
OPatch Automation Tool
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

OPatchauto version : 12.1.0.1.2
OUI version        : 12.1.0.1.0
Running from       : /opt/app/12.1.0/grid

opatchauto log file: /opt/app/12.1.0/grid/cfgtoollogs/opatchauto/17272829/opatch_gi_2013-10-17_11-32-12_deploy.log

Parameter Validation: Successful

Grid Infrastructure home:
/opt/app/12.1.0/grid
RAC home(s):
/opt/app/oracle/product/12.1.0/dbhome_1

Configuration Validation: Successful

Patch Location: /usr/local/patch/17272829
Grid Infrastructure Patch(es): 17027533 17077442 17303297
RAC Patch(es): 17027533 17077442

Patch Validation: Successful

Stopping RAC (/opt/app/oracle/product/12.1.0/dbhome_1) ... Successful
Following database(s) were stopped and will be restarted later during the session: -pdbsvc,cdb12c

Applying patch(es) to "/opt/app/oracle/product/12.1.0/dbhome_1" ...
Patch "/usr/local/patch/17272829/17027533" successfully applied to "/opt/app/oracle/product/12.1.0/dbhome_1".
Patch "/usr/local/patch/17272829/17077442" successfully applied to "/opt/app/oracle/product/12.1.0/dbhome_1".

Stopping CRS ... Successful

Applying patch(es) to "/opt/app/12.1.0/grid" ...
Command "/opt/app/12.1.0/grid/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_patchList -local  -invPtrLoc /opt/app/12.1.0/grid/oraInst.loc -oh /opt/app/12.1.0/grid -silent -ocmrf /usr/local/patch/ocm.rsp" execution failed:
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.

Log file Location for the failed command: /opt/app/12.1.0/grid/cfgtoollogs/opatch/opatch2013-10-17_11-39-04AM_1.log

[WARNING] The local database instance 'cdb12c2' from '/opt/app/oracle/product/12.1.0/dbhome_1' is not running. SQL changes, if any,  will not be applied. Please refer to the log file for more details.
For more details, please refer to the log file "/opt/app/12.1.0/grid/cfgtoollogs/opatchauto/17272829/opatch_gi_2013-10-17_11-32-12_deploy.debug.log".

Apply Summary:
Following patch(es) are successfully installed:
RAC Home: /opt/app/oracle/product/12.1.0/dbhome_1: 17027533, 17077442

Following patch(es) failed to be installed:
GI Home: /opt/app/12.1.0/grid: 17027533, 17077442, 17303297

opatchauto failed with error code 2.
Log files list the failed steps and has commands that could be manually executed.
-------------------Following steps still need to be executed-------------------

/opt/app/12.1.0/grid/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_patchList -local  -invPtrLoc /opt/app/12.1.0/grid/oraInst.loc -oh /opt/app/12.1.0/grid -silent -ocmrf /usr/local/patch/ocm.rsp (TRIED BUT FAILED)

/opt/app/12.1.0/grid/rdbms/install/rootadd_rdbms.sh

/usr/bin/perl /opt/app/12.1.0/grid/crs/install/rootcrs.pl -postpatch
Executing the first command shows how much free disk space must be available before the patch apply
[grid@rhel12c2 patch]$ /opt/app/12.1.0/grid/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_patchList -local  -invPtrLoc /opt/app/12.1.0/grid/oraInst.loc -oh /opt/app/12.1.0/grid -silent -ocmrf /usr/local/patch/ocm.rsp
Oracle Interim Patch Installer version 12.1.0.1.2
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/app/12.1.0/grid
Central Inventory : /opt/app/oraInventory
   from           : /opt/app/12.1.0/grid/oraInst.loc
OPatch version    : 12.1.0.1.2
OUI version       : 12.1.0.1.0
Log file location : /opt/app/12.1.0/grid/cfgtoollogs/opatch/opatch2013-10-17_11-42-52AM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(10578.277MB) is not available.
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.
Log file location: /opt/app/12.1.0/grid/cfgtoollogs/opatch/opatch2013-10-17_11-42-52AM_1.log

OPatch failed with error code 73

Unlike the RAC environment single instance database requires running the "loading modified SQLs" manually. 12c provides the datapatch tool for this purpose unlike in 11.2 where catbundle script was run for the same purpose. All databases (CDB and PDB) are updated.
[oracle@rhel6m1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Mon Oct 21 16:58:15 2013
Copyright (c) 2013, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches:
  PDB CDB$ROOT:
  PDB PDB$SEED:
  PDB PDB12C:
  PDB PDB12CDI:
Currently installed C Patches: 17027533
For the following PDBs: CDB$ROOT
  Nothing to roll back
  The following patches will be applied: 17027533
For the following PDBs: PDB$SEED
  Nothing to roll back
  The following patches will be applied: 17027533
For the following PDBs: PDB12C
  Nothing to roll back
  The following patches will be applied: 17027533
For the following PDBs: PDB12CDI
  Nothing to roll back
  The following patches will be applied: 17027533
Adding patches to installation queue...
Installing patches...
Validating logfiles...
Patch 17027533 apply (pdb CDB$ROOT): SUCCESS
  logfile: /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_ENT12C_CDBROOT_2013Oct21_16_58_30.log (no errors)
Patch 17027533 apply (pdb PDB$SEED): SUCCESS
  logfile: /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_ENT12C_PDBSEED_2013Oct21_16_59_06.log (no errors)
Patch 17027533 apply (pdb PDB12C): SUCCESS
  logfile: /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_ENT12C_PDB12C_2013Oct21_16_59_32.log (no errors)
Patch 17027533 apply (pdb PDB12CDI): SUCCESS
  logfile: /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_apply_ENT12C_PDB12CDI_2013Oct21_16_59_55.log (no errors)
SQL Patching tool complete on Mon Oct 21 17:00:30 2013
Each container could be queried to check the status of the apply.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select * from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS          ACTION_TIME                  DESCRIPTIO LOGFILE
---------- --------------- --------------- ---------------------------- ---------- ----------------------------------------------------------------------
  17027533 APPLY           SUCCESS         21-OCT-13 05.00.27.856979 PM sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_app
                                                                                   ly_ENT12C_CDBROOT_2013Oct21_16_58_30.log

SQL> ALTER SESSION SET container = pdb$seed;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED
SQL>  select * from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS          ACTION_TIME                  DESCRIPTIO LOGFILE
---------- --------------- --------------- ---------------------------- ---------- ----------------------------------------------------------------------
  17027533 APPLY           SUCCESS         21-OCT-13 05.00.29.488402 PM sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_app
                                                                                   ly_ENT12C_PDBSEED_2013Oct21_16_59_06.log                       

SQL> ALTER SESSION SET container = pdb12c;
Session altered.
                       
SQL> show con_name

CON_NAME
------------------------------
PDB12C
SQL> select * from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS          ACTION_TIME                  DESCRIPTIO LOGFILE
---------- --------------- --------------- ---------------------------- ---------- ----------------------------------------------------------------------
  17027533 APPLY           SUCCESS         21-OCT-13 05.00.30.823562 PM sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_app
                                                                                   ly_ENT12C_PDB12C_2013Oct21_16_59_32.log
                      
SQL> ALTER SESSION SET container = pdb12cdi;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB12CDI
SQL> select * from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS          ACTION_TIME                  DESCRIPTIO LOGFILE
---------- --------------- --------------- ---------------------------- ---------- ----------------------------------------------------------------------
  17027533 APPLY           SUCCESS         21-OCT-13 05.00.30.996406 PM sqlpatch   /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/17027533/17027533_app
                                                                                   ly_ENT12C_PDB12CDI_2013Oct21_16_59_55.log

Useful metalink notes
Known Patching Issues for the Oct 15 PSU, Oracle Database 12c R1 using opatchauto and EM [ID 1592252.1]

Update 17 January 2014
More Useful metalink notes
Supplemental Readme - Patch Installation and Deinstallation For 12.1.0.1.x GI PSU [ID 1591616.1]
Example: Manually Apply a 12c GI PSU in Cluster Environment [ID 1594184.1]
Example: Manually Apply a 12c GI PSU in Standalone Environment [ID 1595408.1]
Example: Applying a 12c GI PSU With opatchauto in GI Cluster or Standalone Environment [ID 1594183.1]
What's the sub-patches in 12c GI PSU [ID 1595371.1]