Patching OEM 13c Cloud Control

Some quick notes on patching OEM. Any errors please let me know, I had to install these updates very quickly as they covered a critical security vulnerability.

We need the latest OMSPatcher, OPatch (the right one for OEM Cloud Control) and the correct EM patch.

13.2.0.0.190416 Enterprise Manager Base Platform Patch Set Update (PSU) Readme for Oracle Management Server (OMS) (Doc ID 2528034.1)


29433916 EMBP Patch Set Update 13.2.0.0.190416 System Patch

EM 13.2 How to Upgrade the Enterprise Manager 13.2 & 13.3 Cloud Control OMSPatcher to the Latest Version (Doc ID 2203007.1)

 Additional notes here :

https://dbakevlar.com/2016/05/em13c-applying-system-patches-with-the-oms-patcher/

-- ---------------------------------------------------
-- patching the OEM server
-- ---------------------------------------------------
-- read me https://updates.oracle.com/Orion/Services/download?type=readme&aru=22826464
-- https://dbakevlar.com/2016/05/em13c-applying-system-patches-with-the-oms-patcher/
--
--
-- ---------------------------------------------------
-- patch OMSPATCHER
-- ---------------------------------------------------
-- https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=291979085412865&id=2203007.1&_afrWindowMode=0&_adf.ctrl-state=crcydkj0u_53
-- /u99/media/oracle/OMSPATCHER/p19999993_133000_Generic.zip
export OMS_HOME=/u01/app/oracle/oms13c
mv $OMS_HOME/OMSPatcher $OMS_HOME/OMSPatcherOLD_GS
unzip /u99/media/oracle/OMSPATCHER/p19999993_133000_Generic.zip -d $OMS_HOME
export ORACLE_HOME=$OMS_HOME
cd $OMS_HOME/OMSPatcher
./omspatcher version
-- ---------------------------------------------------
-- patch OPATCH
-- ---------------------------------------------------
-- https://updates.oracle.com/download/6880880.html
-- OPatch for OEM is completely different to installing opatch to the databases
export OMS_HOME=/u01/app/oracle/oms13c
export ORACLE_HOME=$OMS_HOME
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OMSPatcher:$PATH:
emctl stop oms
emctl status oms


 -- if the path doesn't reference a java run time then add one to the path
find /u01 -name java
 -- add found path to the PATH
 -- for example PATH=$PATH:/u01/app/oracle/grid/jdk/jre/bin
-- unzip the OPatch to the media area
cd /u99/media/oracle/OPATCH
unzip p6880880_139000_Generic.zip
-- install the OPatch

java -jar ./6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME


If you hit memory problems with java you can use the -XX option:

java -jar -XX:MaxDirectMemorySize=55m ./opatch_generic.jar -silent oracle_home=$ORACLE_HOME

 -- check version
cd $ORACLE_HOME/OPatch
./opatch version
./opatch lspatches

emctl start oms
emctl status oms

-- ---------------------------------------------------
-- Applying the actual OMS patch
-- ---------------------------------------------------
-- https://updates.oracle.com/Orion/Services/download?type=readme&aru=22826464
-- check listener is up and database is up.

lsnrctl status
export ORACLE_SID=oemnyqa

sqlplus / as sysdba
select instance_name,status from v$instance;
exit

-- set oms home
export ORACLE_HOME=/u01/app/oracle/oms13c
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OMSPatcher:$PATH

-- check oms is up emctl status oms
emctl status oms

-- (go into oem from web page and check can login)
-- check oms version is 13.9....

cd $ORACLE_HOME/OPatch

[oracle@oem01 OPatch]$ ./opatch version

OPatch Version: 13.9.3.3.0
OPatch succeeded.


-- check omspatcher version is 13.8.0.0.3

cd $ORACLE_HOME/OMSPatcher

-- check ORACLE_HOME is oms home /u01/app/oracle/oms13c

[oracle@oem01 OMSPatcher]$ ./omspatcher version
OMSPatcher Version: 13.8.0.0.3
OPlan Version: 12.2.0.1.6
OsysModel build: Tue Sep 04 18:15:05 PDT 2018
OMSPatcher succeeded.



echo $ORACLE_HOME
-- check PATH points to OMS bin and OMSPatcher
-- eg /u01/app/oracle/oms13c/bin:/u01/app/oracle/oms13c/OMSPatcher:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12.2.0/dbhome/bin
echo $PATH

-- unzip the patch to the media folder
cd /u99/media/oracle/EMBP
unzip p29433916_132000_Generic.zip

-- test OMS PATCHER patch
-- if you want to you can create a properties file, so just answer the prompts
omspatcher apply /u99/media/oracle/EMBP/29433916 -analyze
-- press return to "Please enter OMS weblogic admin server URL(t3s://ussanqnycoem01:7102):>"
-- the username and password are in keepass QA->OEM weblogic admin server console
-- check the result is "success"
-- stop oms

emctl stop oms

unzip
omspatcher apply /u99/media/oracle/EMBP/29433916

-- start oms

emcli login -username=sysman -password=XXXXXX
Login successful
Synchronize emcli with the OMS

$ emcli sync
Synchronized successfully
Logout EM

$ emcli logout
Logout successful


-------------------------------------------------------------------------------------------
-- NOW PATCH DATABASE
-------------------------------------------------------------------------------------------
-- https://updates.oracle.com/Orion/Services/download?type=readme&aru=22821655
su - oracle
-- check oracle home
echo $PATH
--
-- check opatch version > 12.2.0.1.14
cd $ORACLE_HOME/OPatch
./opatch version
-- check path contains certain programs
which make
which ar
which ld
which nm
-- unzip patch
cd /u99/media/oracle/DB
unzip p29314339_122010_Linux-x86-64.zip
cd 29314339
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
-- stop oms
/u01/app/oracle/oms13c/emctl stop oms

export ORACLE_SID=oemnyqa
sqlplus / as sysdba
shutdown immediate;

lsnrctl stop

cd /u99/media/oracle/DB/29314339

$ORACLE_HOME/OPatch/opatch apply

sqlplus /nolog
connect / as sysdba
startup
quit

-- apply the data changes

cd $ORACLE_HOME/OPatch
./datapatch -verbose

-- recompile any invalis objects
cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba
@utlrp.sql

/u01/app/oracle/oms13c/emctl start oms




+++++++++++++++++++++++++++

On checking the patch recommendation in OEM we are still missing 

Patch 29204657: WLS PATCH SET UPDATE 12.1.3.0.190416

Hit a problem with the patch, basically :

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
18974665, 29204657

Logged an SR with Oracle who came back with :

18974665 fix is already in the WLS PSU 29204657.

Please rollback patch 18974665, and apply WLS PSU 29204657.

1) Rollback WLS one-off patch (patch 18974665): $ORACLE_HOME/OPatch/opatch rollback -id 18974665

2) Apply the WLS Patch (patch 29204657) according to the instructions in the patch README file.

Refer:

Please rollback Conflict With Patch 18974665 When Applying WebLogic Server 12.1.3 PSU ( Doc ID 2295478.1 )  


Rollback the patch and applied the 29204657 successfully. Happy Days ✔


+++++++++++++++++++++++++++

When checking the OEM patch - check you server architecture and java version match I.e. in this case 64bit otherwise you will see lots of errors re the java version.



Either put java home in the $PATH or use the "-jdk" option on opatch

[oracle@usnyssmtaoem01 OPatch]$ arch
x86_64


[oracle@usnyssmtaoem01 OPatch]$ /u01/app/oracle/middleware/oracle_common/jdk/bin/java -version
java version "1.7.0_111"
Java(TM) SE Runtime Environment (build 1.7.0_111-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.111-b13, mixed mode)

[oracle@usnyssmtaoem01 OPatch]$ ./opatch lsinventory -jdk /u01/app/oracle/middleware/oracle_common/jdk/

Oracle Interim Patch Installer version 13.9.3.3.0
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/middleware
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/middleware/oraInst.loc
OPatch version : 13.9.3.3.0
OUI version : 13.9.1.0.0
Log file location : /u01/app/oracle/middleware/cfgtoollogs/opatch/opatch2019-06-13_04-21-31AM_1.log

OPatch detects the Middleware Home as "/u01/app/oracle/middleware"

Lsinventory Output file location : /u01/app/oracle/middleware/cfgtoollogs/opatch/lsinv/lsinventory2019-06-13_04-21-31AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: usnyssmtaoem01
ARU platform id: 226
ARU platform description:: Linux_AMD64


Interim patches (22) :

Patch 29433916 : applied on Fri May 31 06:36:00 EDT 2019
Unique Patch ID: 22826464
Patch description: "EMBP Patch Set Update 13.2.0.0.190416"
Created on 8 Apr 2019, 03:43:13 hrs PST8PDT
Bugs fixed:
28634622, 26224920, 29427226, 29295784, 28536777, 28524707, 28919063
29049300, 29132445, 27810593, 28749611, 27810608, 26142955, 28818634
28459757, 27156728, 26477226, 28126885, 28247894, 25925447, 28695777
27549803, 27136340, 26823968, 27551630, 27244426, 27804466, 24691913
25784796, 25221636, 25202643, 28202885, 28703948, 27829797, 27721767
.....

No comments:

Oracle SQL Tuning Advisor

This is very useful :

Snapper and this  SQLd360, SQL diagnostics collection made faster

Great site on how to use the tuning advisor :

https://thehelpfuldba.com/index.php/2018/01/24/instructions-to-run-the-sql-tuning-advisor/

but as websites have a nasty way of disappearing :


 Findings and Recommendations
          ----------------------------
 
Finding 1: Top SQL Statements
Impact is .22 active sessions, 95% of total activity.
-----------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .22 active sessions, 95% of total activity.
   ----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "gbwan1836hj3c".
      Related Object
         SQL statement with SQL_ID gbwan1836hj3c.
         SELECT time_key + (:"SYS_B_00" / :"SYS_B_01") period,
         in_value,
         out_value
         FROM  (SELECT time_key,

Create an SQL Tuning task as follows:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'gbwan1836hj3c',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => 'gbwan1836hj3c_tuning_task',
                          description => 'Tuning task1 for statement gbwan1836hj3c');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Run the tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gbwan1836hj3c_tuning_task');

The above will take a couple of minutes. Depending on the complexity of the query, the time_limit may need to be increased.

After the task is complete, query the output:


set long 65536
set longchunksize 65536
set linesize 100



select dbms_sqltune.report_tuning_task('gbwan1836hj3c_tuning_task11') from dual;

No comments:

Patch 29314339 - Database Apr 2019 Release Update 12.2.0.1.190416

Quick notes on applying latest Oracle Patch on standalone database

 This post is superseded - see this link:

https://www.durham.computer/2019/06/oracle-psu-patch.html

NB - This patch is not to be installed to an environment that has a Grid Infrastructure (GI) home. Please refer to the following My Oracle Support Document 756671.1 to determine the appropriate Grid Infrastructure patch to be installed.

Ensure OPatch is a supported version and run opatch from the Oracle Home using the directory where the latest patch has been applied

i.e.

$ cd /u01/software/patch_apr2019/29314339

$ /u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.2.0.1/dbhome_1

Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.17
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2019-05-09_13-23-27PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following active executables are not used by opatch process :

/u01/app/oracle/product/12.2.0.1/dbhome_1/lib/libskgxp12.so
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/oracle

Following active executables are used by opatch process :

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2019-05-09_13-23-27PM_1.log

OPatch failed with error code 73


This was because we had an oracle database running, so we shutdown the database and ran the prereq which should have been ran in the first place :

/u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2019, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.17
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2019-05-09_13-48-08PM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 29314339 passed.

OPatch succeeded.

Now able to run the patch

/u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.2.0.1/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0.1/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.17
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2019-05-09_13-43-05PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 29314339

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

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0.1/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '29314339' to OH '/u01/app/oracle/product/12.2.0.1/dbhome_1'
ApplySession: Optional component(s) [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.precomp.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...

....etc

Patching component oracle.sdo, 12.2.0.1.0...

Patch 29314339 successfully applied.

 Log file location: /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2019-05-09_13-43-05PM_1.log

OPatch succeeded.

Startup the database and apply the datapach as per the README.html in the patch directory.

Load Modified SQL Files into the Database

[oracle@ol7-122-dg1 OPatch]$ ./datapatch -verbose

 SQL Patching tool version 12.2.0.1.0 Production on Thu May 9 13:51:46 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10215_2019_05_09_13_51_46/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
ID 190416 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
29314339 (DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416)

Installing patches...

Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10215_2019_05_09_13_51_46/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Thu May  9 13:53:29 2019

SQL> select patch_id, status, description from dba_registry_sqlpatch
  2  /
  PATCH_ID STATUS
---------- -------------------------
DESCRIPTION
--------------------------------------------------------------------------------
  29314339 BEGIN
DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416


Please check for any invalid objects and run ultrp as per the readme if you have any.

Subscribe to: Posts (Atom)