Страницы

понедельник, 17 августа 2015 г.

EM 12.1.0.4 Installing recommended Plug-Ins and Patches

In one of the project we will use DBaaS (Database as a Service) functionality of EM Cloud Control 12c.
We faced with a multiple issue and decided to upgrade EM and install Oracle recommended patches from note:
Enterprise Manager Cloud Control 12c Recommended Plug-Ins and Patches for Database as a Service (DBaaS) (Doc ID 1549855.1)
Specifically:
Patch 20870437: ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
and
Patch 21415432: enterprise manager for oms plugins 12.1.0.4.14
We had vanilla EM 12.1.0.4 installation without any PSU or patches (except Cloud Framework Plug-In version 12.1.0.2.0).

ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU
At the step of installation:
2.9 Run the following command on each OMS instance home to ensure that you do not have any issues with configuration and binary prerequisite checks,
I encountered an error:
[oracle@mgmt 20870437]$ 
/u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  
OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
  
  
OPatchauto version : 11.1.0.10.4
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-07_17-00-41PM_1.log
  
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/20870437/opatch_oms_2015-08-07_17-00-43PM_analyze.log
  
  
  
OPatchauto
 failed to establish JMX connection to weblogic server. This could be 
because of one (or) more of the following reasons:
1. Weblogic admin server URL that manages OMS application may not be right.
2. Weblogic admin server credentials (username, password) may not be right.
3.
 Virtual host configuration. If OMS, weblogic server are on virtual host
 configuration, Please make sure to add 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true to command line and run again. 
(example: /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true)
  
Please check above conditions and if error(s) still persist, Please contact Oracle support.
  
  
[ Error during Get weblogic Admin Server information Phase]. Detail: OPatchauto was not able to find right interview inputs.
OPatchauto failed:
OPatchauto
 failed to establish JMX connection to weblogic server. This could be 
because of one (or) more of the following reasons:
1. Weblogic admin server URL that manages OMS application may not be right.
2. Weblogic admin server credentials (username, password) may not be right.
3.
 Virtual host configuration. If OMS, weblogic server are on virtual host
 configuration, Please make sure to add 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true to command line and run again. 
(example: /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze 
-property_file /u01/app/oracle/oms12/oms/OPatch/wlskeys/property_file 
-invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true 
OPatchAuto.OMS_DISABLE_HOST_CHECK=true)
  
Please check above conditions and if error(s) still persist, Please contact Oracle support.
  
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/20870437/opatch_oms_2015-08-07_17-00-43PM_analyze.log
  
Recommended actions: Please correct the interview inputs and run opatchauto again.
  
OPatchauto failed with error code 231
[oracle@mgmt 20870437]$
I searched through MOS knowledge base and have decided that this error due to the old OPatch version.
I found a note with a same error:
EM12c : OPatchauto fails with : opatchauto finds that weblogic credentials may not be right or admin server is down (Doc ID 1644317.1)
From which I concluded that issue should be resolved after OPatch 11.1.0.11 install.
As I seen from the logs, we have used OPatch version 11.1.0.10.4.
I installed latest OPatch version 11.1.0.x and installation of PSU was ended without any issues.

Enterprise Manager for OMS Plug-Ins 12.1.0.4.14
opatchauto apply -analyze command showed that from 18 sub-patches I could install only 1.
[oracle@mgmt 21415432]$ /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze -invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
 
OPatchauto version : 11.1.0.12.3
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-10_15-52-13PM_1.log
 
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-10_15-52-16PM_analyze.log
 
Please enter OMS weblogic admin server URL(t3s://mgmt.localdomain:7102):>
Please enter OMS weblogic admin server username:> weblogic
Please enter OMS weblogic admin server password:>
 
WARNING: Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "19513319" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20623942" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950018" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167953" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167991" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21174690" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
 
Configuration Validation: Success
 
Running apply prerequisite checks for sub-patch(es) "21167573" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0/cfgtoollogs/opatch/21167573_Aug_10_2015_15_52_13/ApplyPrereq2015-08-10_15-53-08PM_2.log
Sub-patch(es) "21167573" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"
 
Complete Summary
================
 
All log file names referenced below can be accessed from the directory "/u01/app/oracle/oms12/oms/cfgtoollogs/opatch/2015-08-10_15-52-13PM_SystemPatch_21415432_1"
 
Prerequisites analysis summary:
-------------------------------
  
The following sub-patch(es) are applicable:
 
  Oracle Home Name   Sub-patches                                          Log file
  ----------------   -----------                                          --------
         OraHome18      21167573   21167573_ApplyPrereq2015-08-10_15-53-08PM_2.log
 
The following sub-patches are incompatible with components installed in the OMS system:
19060193,19513319,19512418,20466650,20623942,20950018,20950277,21167953,21167965,21167991,21174690,21185095,21324604,21324646,21324654,21324861,21324890
 
--------------------------------------------------------------------------------
 
The following warnings have occurred during OPatchauto execution:
1)  Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
2)  Sub-patch "19513319" skipped for incompatibility with components installed in the OMS system.
3)  Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
4)  Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
5)  Sub-patch "20623942" skipped for incompatibility with components installed in the OMS system.
6)  Sub-patch "20950018" skipped for incompatibility with components installed in the OMS system.
7)  Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
8)  Sub-patch "21167953" skipped for incompatibility with components installed in the OMS system.
9)  Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
10)  Sub-patch "21167991" skipped for incompatibility with components installed in the OMS system.
11)  Sub-patch "21174690" skipped for incompatibility with components installed in the OMS system.
12)  Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
13)  Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
14)  Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
15)  Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
16)  Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
17)  Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
--------------------------------------------------------------------------------
 
OPatchauto Session completed with warnings.
 
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-10_15-52-16PM_analyze.log
 
OPatchauto completed with warnings.
 
[oracle@mgmt 21415432]$
Most of the patches was skipped due to incompatibility checks:
WARNING: Sub-patch "21324890" skipped for incompatibility with components installed in the OMS system.
I searched through MOS knowledge base and found a note:
EM 12c: Applying a System Patch to the Enterprise Manager 12.1.0.4 Cloud Control OMS Oracle Home Fails at Analyze with Error: Sub-patch skipped for incompatibility with components installed in the OMS system (Doc ID 1921337.1) which tell us that:

Such incompatibility errors result from two scenarios:
Case 1:The Bundle Patch is comprised of patches for the OMS Oracle Home as well as Plug-in Homes. If one of these Plugins is not present in the environment, then these patches would be skipped automatically as they are not needed.
Case 2: If the plug-in is present but is of a lower version than the plug-in versions mentioned above, then such errors can also result.


Patch 21415432 (Enterprise Manager for OMS Plug-Ins 12.1.0.4.14) contains patches for the below list of plugins:
  • Enterprise Manager for Cloud: 12.1.0.8.0
  • Enterprise Manager for Fusion Middleware: 12.1.0.6.0
  • Enterprise Manager for Oracle Database: 12.1.0.6.0
  • Enterprise Manager for Fusion Applications: 12.1.0.6.0
  • Enterprise Manager for Storage Management Framework: 12.1.0.4.0
  • Enterprise Manager for Siebel: 12.1.0.5.0
  • Enterprise Manager for Exadata : 12.1.0.6.0
  • Enterprise Manager for Oracle Virtual Infrastructure: 12.1.0.1.0
  • Enterprise Manager for Virtualization: 12.1.0.6.0
  • Enterprise Manager for My Oracle Support: 12.1.0.6.0
  • Enterprise Manager for Chargeback and Capacity Planning: 12.1.0.6.0
  • Enterprise Manager for Apache Tomcat: 12.1.0.3.0
  • Enterprise Manager for Oracle Database: 12.1.0.7.0
  • Enterprise Manager for Storage Management Framework: 12.1.0.5.0
  • Enterprise Manager for Cloud: 12.1.0.9.0
  • Enterprise Manager for Fusion Middleware: 12.1.0.7.0
Reading further the note
EM 12c: Applying a System Patch to the Enterprise Manager 12.1.0.4 Cloud Control OMS Oracle Home Fails at Analyze with Error: Sub-patch skipped for incompatibility with components installed in the OMS system (Doc ID 1921337.1), I have found the emcli commands which can be used to verify versions of plugins installed on the OMS:
./emcli login -username=sysman
./emcli sync
./emcli list_plugins_on_server
I run above commands and found that I have plugin versions which should be patched by the Patch 21415432 (I highlight DB and SMF plugins for simplicity):
OMS name is mgmt.localdomain:4889_Management_Service
Plug-in Name                        Plugin-id                   Version [revision]
Oracle Cloud Framework              oracle.sysman.cfw           12.1.0.2.0
Oracle Database                     oracle.sysman.db            12.1.0.6.0
Oracle Fusion Middleware            oracle.sysman.emas          12.1.0.6.0
Oracle MOS (My Oracle Support)      oracle.sysman.mos           12.1.0.6.0
Oracle Storage Management Framework oracle.sysman.smf           12.1.0.4.0
Oracle Cloud Application            oracle.sysman.ssa           12.1.0.8.0
Oracle Virtual Infrastructure       oracle.sysman.vi            12.1.0.1.0
Oracle Virtualization               oracle.sysman.vt            12.1.0.6.0
Oracle Exadata                      oracle.sysman.xa            12.1.0.6.0
I continued searching through MOS and found the Bug 21287619 : OPATCHAUTO UNABLE TO IDENTIFY INSTALLED PLUGIN_HOME which was without the resolution but provided some ideas for further investigations.
I started to check them.
At first, I verified that I have correct plugin versions in PLUGIN_HOME:
[oracle@mgmt 21415432]$ ls -la /u01/app/oracle/oms12/plugins/
drwxr-xr-x 14 oracle oinstall 4096 Aug  4 15:05 .
drwxr-x--- 16 oracle oinstall 4096 Jul 11  2014 ..
drwxr-xr-x  2 oracle oinstall 4096 Aug  4 15:31 backup
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.cfw.oms.plugin_12.1.0.1.0
drwxr----- 14 oracle oinstall 4096 Aug 10 14:58 oracle.sysman.cfw.oms.plugin_12.1.0.2.0
drwxr-xr-x 18 oracle oinstall 4096 Aug  4 15:03 oracle.sysman.db.oms.plugin_12.1.0.6.0
drwxr-xr-x 18 oracle oinstall 4096 Jul 11  2014 oracle.sysman.emas.oms.plugin_12.1.0.6.0
drwxr-xr-x 13 oracle oinstall 4096 Jul 11  2014 oracle.sysman.mos.oms.plugin_12.1.0.6.0
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.smf.oms.plugin_12.1.0.4.0
drwxr-xr-x 14 oracle oinstall 4096 Jul 11  2014 oracle.sysman.ssa.oms.plugin_12.1.0.8.0
drwxr-xr-x 12 oracle oinstall 4096 Jul 11  2014 oracle.sysman.vi.oms.plugin_12.1.0.1.0
drwxr-xr-x 15 oracle oinstall 4096 Jul 11  2014 oracle.sysman.vt.oms.plugin_12.1.0.6.0
drwxr-xr-x 13 oracle oinstall 4096 Jul 11  2014 oracle.sysman.xa.oms.plugin_12.1.0.6.0
drwxr-xr-x  2 oracle oinstall 4096 Jul 11  2014 tmp
I reviewed opatch/opatchauto lspatches output:
[oracle@mgmt 21415432]$ opatch lspatches
20870437;ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
[oracle@mgmt 21415432]$ opatchauto lspatches
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
Oracle Home:/u01/app/oracle/oms12/oms
20870437;ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)
The following groups of patch(es) are applied as System Patch bundle(s):
20870437
For more details on installed patch(es) in platform, plugin homes, Please do "/u01/app/oracle/oms12/oms/OPatch/opatch lsinventory -details -oh ".
opatch lsinv output:
[oracle@mgmt 21415432]$ opatch lsinv
Oracle Interim Patch Installer version 11.1.0.12.7
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/oms12/oms
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/oms12/oms/oraInst.loc
OPatch version    : 11.1.0.12.7
OUI version       : 11.1.0.12.0
Log file location : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-11_08-41-48AM_1.log
OPatch detects the Middleware Home as "/u01/app/oracle/oms12"
Lsinventory Output file location : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/lsinv/lsinventory2015-08-11_08-41-48AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
EM Platform (OMS)                                                    12.1.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch  20870437     : applied on Mon Aug 10 15:19:39 MSK 2015
Unique Patch ID:  18978384
Patch description:  "ENTERPRISE MANAGER BASE PLATFORM - OMS 12.1.0.4.4 PSU (GENERIC)"
   Created on 2 Jul 2015, 03:07:25 hrs PST8PDT
   Bugs fixed:
     20010360, 20346629, 18599481, 19459173, 20646440, 18830050, 18244680
     19614772, 20492788, 18932882, 19355307, 20263906, 20556489, 20106145
     14301574, 18721562, 19328391, 19535114, 19861538, 19794113, 19604817
     20596437, 19892848, 19218456, 19978077, 18505219, 17843191, 20345612
     20748748, 20024779, 18256983, 19329809, 19300103, 17987955, 18351725
     18903783, 19430853, 19845101, 20870437, 20223681, 20019184, 20263188
     19069940, 19861412, 19136663, 18446798, 19474685, 18765545, 18038059
     17783148, 18041873, 19509780, 19030447, 20933667, 19574756, 19258481
     18938561, 17812648, 20377474, 19572261, 18945007, 20307428, 18388324
     19500609, 17991694, 19137411, 19474929, 19592446, 18710124, 20536095
     19352118, 19557054, 18726708, 19595813, 19170861, 19685443, 18353162
     19289270, 19367214, 18906451, 19458672, 20284029, 18490444, 19766881
     18509136, 20739088, 19511544, 18695961, 19812966, 20576148, 20623258
     19055251, 20260177, 19323634, 19941819, 18725891, 19824587, 19780847
     18359621, 19393211, 19211048, 20505155, 18476364, 18490468, 18247849
     19071602, 19068674, 18490455, 19524568, 19061168, 19077861, 20031287
     18756725, 19281252, 18977955, 18521731, 18485889, 19775544, 18900197
     17834366, 20460507, 19545505, 19823996, 20645335, 20633770, 17407918
     18706824, 20568225, 18805935, 19376082, 19146514, 18490432, 19694744
     18230284, 18726208, 20044313, 18516298, 20642633, 18999400, 20692416
     20392036, 19362386, 19452280, 18356966, 20617058, 19055979, 19830994
     20030493, 19823761, 18834216, 19196979, 19785223, 18389542
  
--------------------------------------------------------------------------------
OPatch succeeded.
I found an answer in an inventory.xml from Central Inventory:
<HOME NAME="oms12c1" LOC="/u01/app/oracle/oms12/oms" TYPE="O" IDX="3">
   <REFHOMELIST>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/jdk16"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.6.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.2.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.7.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.3.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sun.oss7.oms.plugin_12.1.0.4.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.5.0"/>
      <REFHOME LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"/>
   </REFHOMELIST>
</HOME>
Notice, that inventory.xml contains old plugin versions! It's hard to say what exactly leads to a such behaviour and I didn't have enough time for further investigations.
It was enough for me, that inventory.xml dont contain actual plugin versions.

Example 1: oracle.sysman.smf.oms.plugin (Storage Management Framework).
emcli list_plugins_on_server shows version 12.1.0.4:
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.4.0
inventory.xml contains information about versions 12.1.0.2/12.1.0.3:
<HOME NAME="OraHome13" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.2.0" TYPE="O" IDX="24" PLUGIN="T">
<HOME NAME="OraHome16" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.3.0" TYPE="O" IDX="29" PLUGIN="T">
Patch 21415432 contains fixes for SMF plugin versions 12.1.0.4/12.1.0.5.

Example 2: oracle.sysman.db.oms.plugin (Database Plugin).
emcli list_plugins_on_server shows version 12.1.0.6:
Oracle Database                               oracle.sysman.db                12.1.0.6.0
inventory.xml contains information about versions 12.1.0.4/12.1.0.5:
<HOME NAME="oracle_sysman_db11" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.4.0" TYPE="O" IDX="4" PLUGIN="T">
<HOME NAME="OraHome15" LOC="/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.5.0" TYPE="O" IDX="28" PLUGIN="T">
Patch 21415432 contains fixes for Database Plugin versions 12.1.0.6/12.1.0.7.
Due to such inconsistencies opatch reported a message like below:
WARNING: Sub-patch "19060193" skipped for incompatibility with components installed in the OMS system.
I decided to recover lost inventory entities by note:
EM 12c: How to Recover a Lost Inventory Record for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent (Doc ID 1480237.1)

GOAL

In the case that recovering a lost inventory record may be necessary, this note describes how to accomplish this for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent.


First of all, I had take backup of Central Inventory, local inventory, ORACLE_HOME of OMS.
8 plugins from 9 total didn't have an actual version in the inventory.xml.
For each of them, I executed steps from the note:
EM 12c: How to Recover a Lost Inventory Record for the Enterprise Manager 12.1.0.1 Cloud Control OMS and Management Agent (Doc ID 1480237.1)
I remind, that before my actions only 1 sub-patch (from 18) could be applied to the OMS.
And after that, opatchauto analyze showed that 9 sub-patches from 18 could be applied to the OMS. Bingo!
OMS Plug-Ins patches contains fixes for 2 versions of each plugin. And only one of them was installed in my environment.
[oracle@mgmt 21415432]$ /u01/app/oracle/oms12/oms/OPatch/opatchauto apply -analyze -invPtrLoc /u01/app/oracle/oms12/oms/oraInst.loc  OPatchAuto.OMS_DISABLE_HOST_CHECK=true
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
 
OPatchauto version : 11.1.0.12.3
OUI version        : 11.1.0.12.0
Running from       : /u01/app/oracle/oms12/oms
Log file location  : /u01/app/oracle/oms12/oms/cfgtoollogs/opatch/opatch2015-08-11_17-13-05PM_1.log
OPatchauto log file: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-11_17-13-08PM_analyze.log
 
Please enter OMS weblogic admin server URL(t3s://mgmt.localdomain:7102):>
Please enter OMS weblogic admin server username:> weblogic
Please enter OMS weblogic admin server password:>
 
WARNING: Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
WARNING: Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
  
Configuration Validation: Success
 
Running apply prerequisite checks for sub-patch(es) "21324890" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0/cfgtoollogs/opatch/21324890_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-13-58PM_2.log
Sub-patch(es) "21324890" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vi.oms.plugin_12.1.0.1.0"
 
Running apply prerequisite checks for sub-patch(es) "21167573" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0/cfgtoollogs/opatch/21167573_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-01PM_2.log
Sub-patch(es) "21167573" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.cfw.oms.plugin_12.1.0.2.0"
 
Running apply prerequisite checks for sub-patch(es) "21174690" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21174690_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-03PM_2.log
Sub-patch(es) "21174690" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.db.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "21167953" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21167953_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-06PM_2.log
Sub-patch(es) "21167953" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.xa.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "19060193" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/19060193_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-09PM_2.log
Sub-patch(es) "19060193" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.vt.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "20950018" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/20950018_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-11PM_2.log
Sub-patch(es) "20950018" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.emas.oms.plugin_12.1.0.6.0"
 
Running apply prerequisite checks for sub-patch(es) "20623942" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0/cfgtoollogs/opatch/20623942_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-14PM_2.log
Sub-patch(es) "20623942" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.8.0"
 
Running apply prerequisite checks for sub-patch(es) "19513319" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0/cfgtoollogs/opatch/19513319_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-17PM_2.log
Sub-patch(es) "19513319" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.smf.oms.plugin_12.1.0.4.0"
 
Running apply prerequisite checks for sub-patch(es) "21167991" and Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0"...
Please monitor OPatch log file: /u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0/cfgtoollogs/opatch/21167991_Aug_11_2015_17_13_05/ApplyPrereq2015-08-11_17-14-19PM_2.log
Sub-patch(es) "21167991" are successfully analyzed for Oracle Home "/u01/app/oracle/oms12/plugins/oracle.sysman.mos.oms.plugin_12.1.0.6.0"
 
Complete Summary
================
 
All log file names referenced below can be accessed from the directory "/u01/app/oracle/oms12/oms/cfgtoollogs/opatch/2015-08-11_17-13-05PM_SystemPatch_21415432_1"
 
Prerequisites analysis summary:
-------------------------------
 
The following sub-patch(es) are applicable:
 
                                 Oracle Home Name   Sub-patches                                          Log file
                                 ----------------   -----------                                          --------
                       oracle_home_MOS_12_1_0_6_0      21167991   21167991_ApplyPrereq2015-08-11_17-14-19PM_2.log
  oracle_home_Oracle_Fusion_Middleware_12_1_0_6_0      20950018   20950018_ApplyPrereq2015-08-11_17-14-11PM_2.log
                        oracle_home_OV_12_1_0_6_0      19060193   19060193_ApplyPrereq2015-08-11_17-14-09PM_2.log
                                        OraHome18      21167573   21167573_ApplyPrereq2015-08-11_17-14-01PM_2.log
                       oracle_home_OVI_12_1_0_1_0      21324890   21324890_ApplyPrereq2015-08-11_17-13-58PM_2.log
                        oracle_home_OE_12_1_0_6_0      21167953   21167953_ApplyPrereq2015-08-11_17-14-06PM_2.log
                       oracle_home_OCA_12_1_0_8_0      20623942   20623942_ApplyPrereq2015-08-11_17-14-14PM_2.log
           oracle_home_Oracle_Database_12_1_0_6_0      21174690   21174690_ApplyPrereq2015-08-11_17-14-03PM_2.log
                       oracle_home_SMF_12_1_0_4_0      19513319   19513319_ApplyPrereq2015-08-11_17-14-17PM_2.log
  
The following sub-patches are incompatible with components installed in the OMS system:
19512418,20466650,20950277,21167965,21185095,21324604,21324646,21324654,21324861
 
--------------------------------------------------------------------------------
 
The following warnings have occurred during OPatchauto execution:
1)  Sub-patch "19512418" skipped for incompatibility with components installed in the OMS system.
2)  Sub-patch "20466650" skipped for incompatibility with components installed in the OMS system.
3)  Sub-patch "20950277" skipped for incompatibility with components installed in the OMS system.
4)  Sub-patch "21167965" skipped for incompatibility with components installed in the OMS system.
5)  Sub-patch "21185095" skipped for incompatibility with components installed in the OMS system.
6)  Sub-patch "21324604" skipped for incompatibility with components installed in the OMS system.
7)  Sub-patch "21324646" skipped for incompatibility with components installed in the OMS system.
8)  Sub-patch "21324654" skipped for incompatibility with components installed in the OMS system.
9)  Sub-patch "21324861" skipped for incompatibility with components installed in the OMS system.
--------------------------------------------------------------------------------
OPatchauto Session completed with warnings.
 
Log file location: /u01/app/oracle/oms12/oms/cfgtoollogs/opatchauto/21415432/opatch_oms_2015-08-11_17-13-08PM_analyze.log
 
OPatchauto completed with warnings.
Further installation was ended without any issues.

понедельник, 3 августа 2015 г.

Reclaim unused space from MLOG$ segments after MVIEW Refresh

We are using fast-refreshable MVIEWs in some of our database for reporting purposes.
They are works fine most of the time.
However, periodically MLOG size is increased due to different reasons.
Such as: bulk data loading, other abnormal application activities, or delays with MVIEW refresh.
After that, even if you refresh MVIEW, HWM doesn't reset.
You need to reset it manually, using commands such as ALTER TABLE SHRINK/MOVE and so on.
Without resetting the HWM MVIEW refresh performance can be poor.

Good news: there are Patch 11072728 available for some platforms that can reset the HWM without manual intervention.
This patch was described in MOS note Space Not Reclaimed from MLOG$ Segments After MVIEW Refresh (Doc ID 1941137.1)
According to the note, the fix for bug 11072728 will be provided in upcoming 12.2 release.
I have already requested that patch for Solaris SPARC64 on top of 11.2.0.4.4/11.2.0.4.6.

Today I have decided to look closer on Patch 11072728 in one of non-production database.
I want to be sure that patch didn't harm production instance.

Reading the bug readme, I have found that fix for bug is not enabled by default.
You need to set underscope parameter _bug11072728_mv_refresh_truncate_log to 1 to enable it.
Worth to be noted, is that we can change this parameter on the session and on the system level:
SQL> select isses_modifiable,
  2         issys_modifiable
  3    from v$parameter
  4   where name = '_bug11072728_mv_refresh_truncate_log';

ISSES_MODIFIABLE ISSYS_MODIFIABLE
---------------- ----------------
TRUE             IMMEDIATE
Next script I have used to setup test schema:
SQL> grant connect to tc identified by tc;

Grant succeeded.

SQL> grant alter session to tc;

Grant succeeded.

SQL> grant create materialized view to tc;

Grant succeeded.

SQL> grant create table to tc;

Grant succeeded.

SQL> grant unlimited tablespace to tc;

Grant succeeded.

SQL> 
SQL> conn tc/tc
Connected.
SQL> 
SQL> create table t
  2  as
  3  select *
  4    from all_users;

Table created.

SQL> 
SQL> alter table t add constraint t_pk primary key(username);

Table altered.

SQL> 
SQL> create materialized view log on t with primary key;

Materialized view log created.

SQL> 
SQL> create materialized view mv_t
  2  refresh fast
  3  as
  4  select *
  5    from t;

Materialized view created.
    
In the above script I have created table, mat view log on it, and materialized view.
We can see that mat view log is empty, segment has 8 blocks allocated:
SQL> select count(*) from mlog$_t;

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

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8
I update table in loop, 100 times in total:
SQL> begin
  2    for i in 1..100
  3    loop
  4      update t
  5         set created = created
  6       where username <> 'SYS';
  7      commit;
  8    end loop;
  9  end;
 10  /
Check that mat view log is not empty, segment was extended:
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
     19400

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
Let's refresh materialized view now:
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
If the patch 11072728 was not applied or the parameter "_bug11072728_mv_refresh_truncate_log" not set to 1, then the HWM is not reset and the segment space allocated is the same:
SQL> select count(*) from mlog$_t;

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

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
In the production system with a high DML activity materialized view logs can grows to a much higher size.
And the materialized view refresh performance degrades.
As I said previously, before the patch 11072728 you need to reset the HWM manually.
With the patch 11072829 you dont need to do it anymore.
Just the set parameter "_bug11072728_mv_refresh_truncate_log" to 1:
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from mlog$_t;

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

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8
You can see that segment allocated space was decreased.
How does it works under the hood?
I have setup sql tracing and event 10704 (enqueue trace) to investigate it:
SQL> alter session set events 'sql_trace bind=true:10704 level 10';

Session altered.

SQL> 
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
I used below command to filter the irrelevant lines from output:
egrep "ksqgtl \*|ksqrcl: [^r]|^truncate|select count\(\*\) from .*MLOG" orcl_ora_18632.trc
  • ksqgtl - get lock function
  • ksqrcl - release lock function
  • We want to show the truncate command and the select count(*) from MLOG.
Below is the output of egrep command, in which some lines was skipped for brevity:
select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e4-00000000 mode=6 flags=0x401 timeout=0 ***
select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e6-00000000 mode=6 flags=0x401 timeout=0 ***
truncate table "TC"."MLOG$_T"
These commands was executed when the materialized view update already done.
Looks like that new algorithm with MLOG truncate works in following way:
  1. execute old refresh code
  2. check count of rows in MLOG
  3. if zero, lock master table in exclusive mode nowait (TM lock with timeout=0)
  4. check count of rows in MLOG again (because there are can be DML between step 2 and 3)
  5. lock MLOG in exclusive mode nowait (TM lock with timeout=0)
  6. truncate MLOG
At least, an open transaction will prevent the truncation of MLOG.
And that indirectly confirms my assumptions about internal workings of new refresh algorithm. Let's update the 1 row in table in session 1:
SQL> -- session 1
SQL> update t
  2     set created=created
  3   where username='SYS';

1 row updated.
Now we will check space of MLOG before refresh, perform actual refresh and check the space again (in other session):
SQL> -- session 2
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
SQL> 
SQL> select count(*) from mlog$_t;

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

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104
And the truncate of MLOG was not executed.
Looks like, it's safe to use a patch 11072728 in the production environment.
If I will face any issue with that patch in the production, I will update this blog post.