IT Blog
I wanted to create a video series covering the basics of Oracle Enterprise Manager 13c. These series are designed to provide insights on how to install, configure and manage Oracle Enterprise Manager 13.5.
This week we are going to cover the installation phase of Oracle Enterprise Manager 13.5.
Hope this is useful.
Thanks,
Alfredo
IT Security is popular topic nowadays! We constantly hear news about data breaches, ransomware, malware, unauthorized access to IT systems, etc. IT organizations are constantly looking to keep their systems, networks and data safe and secure.
Today’s blog is about how Oracle Enterprise Manager (EM) can help Database Administrators to secure and harden the Oracle Databases they manage along with the hosts those databases are running on.
First things first. I strongly recommend to review the Oracle Database 19c Security Guide. This guide provides guidelines on how to secure the Oracle Database, harden the DB access, secure and encrypt the DB data and so.
Now let’s discuss some areas that database administrators should also look at in order to improve their security posture:
All the features that we will be discussing today are part of the Oracle Database Lifecycle Management pack. This pack requires an additional license.
Timely apply security patches
Fleet Maintenance (FM) enables administrators to automate lifecycle activities such as database patching and upgrades. FM is a gold image subscription based model that allows to patch databases with minimum downtime by using out-of-place patching mechanisms. In-place patching is also available if you need to apply an emergency on-off patch.
Administrators have the ability to customize the patching process by adding custom pre/post scripts to patching operations. FM supports single instance, RAC databases, Grid Infrastructure, Multitenant and Data Guard configurations.
One thing to mention is the ability to get security patch recommendations as soon as they are published. EM connects to My Oracle Support (MOS) and checks for the availability of new security patches. As soon as a new security patch is released EM will let you know if your DB estate is compliant or not in terms of these patches.
Monitor database configuration and detect misconfigurations
Configuration and Drift Management helps you monitor the configuration of your DB estate, the hosts on where those DB’s are running as well as the Oracle Homes (OH) for those installations. EM allows you to create your own configuration templates based on the configuration settings you need to enforce. Any misconfiguration or drift away of your template will be automatically reported via the Drift Management dashboard and you can also receive alerts if you choose to.
Corrective Actions (CA) can also be created to automatically fix this misconfigurations in order to comply with the templates and reduce security risks.
How many times administrators issued an ALTER SYSTEM command with SPFILE scope and forgot about it? Well, you will know next time you bring your DB up after maintenance. EM helps you detect these changes before they become a production issue. It also help you track the history of configuration changes, save configuration information at a given time and also allows you to use this configuration information to be compared between targets.
Have you wonder, how many OH’s we have with this specific one-off patch?
How many DB’s we have running on this specific OS version?
Well, EM can help you answer all these questions using this configuration data.
One thing worth mentioning is that EM comes with hundreds on configuration collections. If you need to gather a very specific configuration that is not available out-of-the-box, you can create your own configuration extension and collect this automatically.
Use industry and regulatory standards like STIG and CIS for the Oracle Database
EM provides compliance standards to help customers meet regulatory standards like STIG and CIS. Oracle’s best practices are also included within the compliance framework. There are two available options for analysis.
Each option allow administrators understand where attentions needs to be put in order to harden the DB estate.
Using the compliance framework, EM will provide a score to each associated target along with all the violations that need to be remediated after each evaluation.
I also want to provide links to Oracle LiveLabs workshops available that cover the features discussed above.
Thanks,
Alfredo
In this post I want to show you how Oracle Enterprise Manager 13c using the Cloud Management pack, can be easily integrated with DevOps automation and orchestration tools like Ansible in order to provision Oracle Databases.
These can be either single instance, multi-tenant container databases, pluggable databases, schemas or databases running on high availability clusters and Dataguard configurations. EM 13c implements pre-checks, best practices and processes to provision all these configurations in a secure, automated and controlled fashion.
We have created an step by step laboratory in the Oracle LiveLabs framework. This lab contains a demo environment and a workbook that guide through all the steps. This lab shows you how to provision a PDB using Ansible and Oracle Enterprise Manager 13c. It also shows you how to shutdown, start, resize and check the status of the PDB using Ansible.
With this kind of integration, enterprises can close the loop in terms of DevOps or CI/CD pipeline provisioning. They can provision the Virtual Machines, Application and Oracle Databases automatically.
Follow the link below for the Oracle LiveLabs demo.
Thanks,
Alfredo
Oracle Enterprise Manager (EM) 13.5 RU1 was released last week.
In this post, I want to show you how to apply this RU to your Oracle Enterprise Manager environment.
Let me start by describing the environment that I’m using for this. Oracle Enterprise Manager 13.5 can be deployed both on-premises or in the Oracle Cloud (OCI). Oracle Enterprise Manager 13.5 is available in the OCI’s Marketplace. You have the ability to choose from both single-instance and multi-node EM deployment. This makes the process really straightforward and you can have an environment up and running with couple of clicks.
First of all. You need to download patch 332835392 that contains EM 13.5 RU1. I strongly recommend to take a look at MOS note 822485.1. This Note contains all the information about available RU’s and patches for your EM.
There are some pre-requisites needed before you can patch your EM.
The EM installation that comes from the OCI’s Marketplace already has 19.11 database for the OMR. However, I decided to apply patch 32904851. If you check the amount of patches included between RU11 and RU12, it’s a considerably list. Therefore, is a good idea to apply RU12 for the OMR.
Upgrading OMSPatcher to 13.9.5.0.0 was very straight forward. Follow MOS note 2809842.1 for additional information.
Let’s now patch EM with RU1. I analyzed the patch first.
omspatcher apply -analyze -invPtrLoc /u01/app//em135/middleware/oraInst.loc OMSPatcher.OMS_DISABLE_HOST_CHECK=true
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation. All rights reserved.
OMSPatcher version : 13.9.5.0.0
OUI version : 13.9.4.0.0
Running from : /u01/app//em135/middleware
Log file location : /u01/app//em135/middleware/cfgtoollogs/omspatcher/opatch2021-10-05_13-02-57PM_1.log
OMSPatcher log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/32835392/omspatcher_2021-10-05_13-02-59PM_analy
Please enter OMS weblogic admin server URL(t3s://emcc.marketplace.com:7102):>
Please enter OMS weblogic admin server username(weblogic):>
Please enter OMS weblogic admin server password:>
Enter SYS Password :
Checking if current repository database is a supported version
Current repository database version is supported
Prereq "checkComponents" for patch 32835403 passed.
Prereq "checkComponents" for patch 32941631 passed.
Prereq "checkComponents" for patch 32941696 passed.
Prereq "checkComponents" for patch 32941706 passed.
WARNING: Could not apply the patch "32860349" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" cor
Prereq "checkComponents" for patch 32941575 passed.
Prereq "checkComponents" for patch 32941713 passed.
Prereq "checkComponents" for patch 32840049 passed.
Prereq "checkComponents" for patch 32835412 passed.
WARNING: Could not apply the patch "32941609" because the "oracle.sysman.bda.oms.plugin with version 13.5.1.0.0" core
Prereq "checkComponents" for patch 32941618 passed.
WARNING: Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" cor
Prereq "checkComponents" for patch 32941645 passed.
Prereq "checkComponents" for patch 32941673 passed.
Configuration Validation: Success
Running apply prerequisite checks for sub-patch(es) "32941713,32941618,32941631,32941696,32840049,32941645,32835403,3
Sub-patch(es) "32941713,32941618,32941631,32941696,32840049,32941645,32835403,32941673,32941706,32941575,32835412" are successfully analyzed for Oracle Home "/u01/app//em135/middleware"
Complete Summary
================
All log file names referenced below can be accessed from the directory "/u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-02-57PM_SystemPatch_32835392_1"
Prerequisites analysis summary:
-------------------------------
The following sub-patch(es) are applicable:
Featureset Sub-patches Log file
---------- ----------- --------
oracle.sysman.top.oms 32941713,32941618,32941631,32941696,32840049,32941645,32835403,32941673,32941706,32941575,32835412 32941713,32941618,32941631,32941696,32840049,32941645,32835403,32941673,32941706,32941575,32835412_opatch2021-10-05_13-03-28PM_1.log
The following sub-patches are incompatible with components installed in the OMS system:
32860349,32941609,32941662
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) Could not apply the patch "32860349" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
2) Could not apply the patch "32941609" because the "oracle.sysman.bda.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
3) Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
--------------------------------------------------------------------------------
OMSPatcher Session completed with warnings.
Log file location: /u01/app//em135/middleware/cfgtoollogs/omspatcher/32835392/omspatcher_2021-10-05_13-02-59PM_analyze.log
OMSPatcher completed with warnings.There are some warning for plug-ins that I don’t have deployed in my EM. I can proceed with applying RU1.
Run the following command in the Repository Database as the SYS user.
alter system set job_queue_processes=0 scope=both sid='*';Run the following command in the Repository Database as the SYSMAN user.
exec emd_maintenance.remove_em_dbms_jobs;
commit;Shutdown the OMS.
emctl stop omsApply RU1.
omspatcher apply -invPtrLoc /u01/app//em135/middleware/oraInst.loc OMSPatcher.OMS_DISABLE_HOST_CHECK=true
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation. All rights reserved.
OMSPatcher version : 13.9.5.0.0
OUI version : 13.9.4.0.0
Running from : /u01/app//em135/middleware
Log file location : /u01/app//em135/middleware/cfgtoollogs/omspatcher/opatch2021-10-05_13-10-26PM_1.log
OMSPatcher log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/32835392/omspatcher_2021-10-05_13-10-29PM_deploy.log
Please enter OMS weblogic admin server URL(t3s://emcc.marketplace.com:7102):>
Please enter OMS weblogic admin server username(weblogic):>
Please enter OMS weblogic admin server password:>
Enter SYS Password :
Checking if current repository database is a supported version
Current repository database version is supported
Prereq "checkComponents" for patch 32835403 passed.
Prereq "checkComponents" for patch 32941631 passed.
Prereq "checkComponents" for patch 32941696 passed.
Prereq "checkComponents" for patch 32941706 passed.
WARNING: Could not apply the patch "32860349" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
Prereq "checkComponents" for patch 32941575 passed.
Prereq "checkComponents" for patch 32941713 passed.
Prereq "checkComponents" for patch 32840049 passed.
Prereq "checkComponents" for patch 32835412 passed.
WARNING: Could not apply the patch "32941609" because the "oracle.sysman.bda.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
Prereq "checkComponents" for patch 32941618 passed.
WARNING: Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
Prereq "checkComponents" for patch 32941645 passed.
Prereq "checkComponents" for patch 32941673 passed.
Configuration Validation: Success
Running apply prerequisite checks for sub-patch(es) "32941713,32941618,32941631,32941696,32840049,32941645,32835403,32941673,32941706,32941575,32835412" and Oracle Home "/u01/app//em135/middleware"...
Sub-patch(es) "32941713,32941618,32941631,32941696,32840049,32941645,32835403,32941673,32941706,32941575,32835412" are successfully analyzed for Oracle Home "/u01/app//em135/middleware"
To continue, OMSPatcher will do the following:
[Patch and deploy artifacts] : Apply sub-patch(es) [ 32835403 32835412 32840049 32941575 32941618 32941631 32941645 32941673 32941696 32941706 32941713 ]
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32835403_Sep_16_2021_10_02_49/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941631_Sep_16_2021_10_19_23/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941696_Sep_16_2021_10_19_25/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941706_Sep_16_2021_10_19_27/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941575_Sep_16_2021_10_19_31/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941713_Sep_16_2021_10_19_40/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32840049_Sep_16_2021_10_19_42/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32835412_Sep_16_2021_10_20_01/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941618_Sep_16_2021_10_20_35/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941645_Sep_16_2021_10_20_39/original_patch";
Apply RCU artifact with patch "/u01/app//em135/middleware/.omspatcher_storage/32941673_Sep_16_2021_10_20_42/original_patch";
Register MRS artifact "commands";
Register MRS artifact "targetType";
Register MRS artifact "storeTargetType";
Register MRS artifact "default_collection";
Register MRS artifact "omsPropertyDef";
Register MRS artifact "jobTypes";
Register MRS artifact "SecurityClassManager";
Register MRS artifact "swlib";
Register MRS artifact "procedures";
Register MRS artifact "systemStencil";
Register MRS artifact "discovery";
Register MRS artifact "gccompliance";
Register MRS artifact "derivedAssocs"
Do you want to proceed? [y|n]
y
User Responded with: Y
Stopping the OMS.....
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/stop_oms_2021-10-05_13-53-40PM.log
Applying sub-patch(es) "32835403,32835412,32840049,32941575,32941618,32941631,32941645,32941673,32941696,32941706,32941713"
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/opatch/opatch2021-10-05_13-10-58PM_1.log
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32835403_Sep_16_2021_10_02_49/original_patch"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941631_Sep_16_2021_10_19_23/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.cfw.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941696_Sep_16_2021_10_19_25/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.smf.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941706_Sep_16_2021_10_19_27/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.vt.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941575_Sep_16_2021_10_19_31/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.si.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941713_Sep_16_2021_10_19_40/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.am.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32840049_Sep_16_2021_10_19_42/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.emas.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32835412_Sep_16_2021_10_20_01/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941618_Sep_16_2021_10_20_35/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.emct.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941645_Sep_16_2021_10_20_39/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.ssa.oms.plugin_13.5.1.0.0"
Updating repository with RCU reference file "/u01/app//em135/middleware/.omspatcher_storage/32941673_Sep_16_2021_10_20_42/original_patch" for plugin home "/u01/app//em135/middleware/plugins/oracle.sysman.xa.oms.plugin_13.5.1.0.0"
Registering service "commands" with register file "/u01/app//em135/middleware/sysman/metadata/commands/commands.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_commands_2021-10-05_14-08-58PM.log
Registering service "commands" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/commands/commands.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_commands_2021-10-05_14-09-06PM.log
Registering service "targetType" with register file "/u01/app//em135/middleware/sysman/builtinplugins/oracle.sysman.oh/metadata/targetType/oracle_home.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_targetType_2021-10-05_14-09-15PM.log
Registering service "targetType" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.am.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/targetType/32941713/oracle_dblra.xml" for plugin id as "oracle.sysman.am"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_targetType_2021-10-05_14-09-53PM.log
Registering service "targetType" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/targetType/32835412/oracle_cman.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_targetType_2021-10-05_14-10-06PM.log
Registering service "storeTargetType" with register file "/u01/app//em135/middleware/sysman/metadata/targetType/host.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_storeTargetType_2021-10-05_14-12-18PM.log
Registering service "storeTargetType" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.am.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/targetType/32941713/oracle_dblra.xml" for plugin id as "oracle.sysman.am"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_storeTargetType_2021-10-05_14-12-29PM.log
Registering service "storeTargetType" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/targetType/32835412/oracle_cloud_adw.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_storeTargetType_2021-10-05_14-12-38PM.log
Registering service "default_collection" with register file "/u01/app//em135/middleware/sysman/metadata/default_collection/oracle_emd.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_default_collection_2021-10-05_14-12-49PM.log
Registering service "default_collection" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.am.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/default_collection/32941713/oracle_dblra.xml" for plugin id as "oracle.sysman.am"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_default_collection_2021-10-05_14-13-12PM.log
Registering service "default_collection" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/default_collection/32835412/oracle_cman.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_default_collection_2021-10-05_14-13-21PM.log
Registering service "omsPropertyDef" with register file "/u01/app//em135/middleware/sysman/metadata/omsProperties/definition/OMSPropDefinition.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_omsPropertyDef_2021-10-05_14-13-59PM.log
Registering service "jobTypes" with register file "/u01/app//em135/middleware/sysman/metadata/jobTypes/agentpatch/DeployPatchesOnAgent.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_jobTypes_2021-10-05_14-14-08PM.log
Registering service "jobTypes" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/jobTypes/NGCreateCRSCloneComponent.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_jobTypes_2021-10-05_14-14-21PM.log
Registering service "jobTypes" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/jobTypes/GEFExtractAgent.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_jobTypes_2021-10-05_14-14-37PM.log
Registering service "SecurityClassManager" with register file "/u01/app//em135/middleware/sysman/metadata/security/SecurityClass/dashboard_security_class.xml" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_SecurityClassManager_2021-10-05_14-14-47PM.log
Registering service "swlib" with register file "/u01/app//em135/middleware/sysman/metadata/swlib/patch" for plugin id as "core"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_swlib_2021-10-05_14-14-56PM.log
Registering service "swlib" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/swlib/dbprovision/dbprov" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_swlib_2021-10-05_14-15-09PM.log
Registering service "procedures" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.cfw.oms.plugin_13.5.1.0.0/metadata/procedures/CfwOPCDiscoveryDP.xml" for plugin id as "oracle.sysman.cfw"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_procedures_2021-10-05_14-15-28PM.log
Registering service "procedures" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/procedures/CloudDataMigration.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_procedures_2021-10-05_14-15-38PM.log
Registering service "procedures" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.ssa.oms.plugin_13.5.1.0.0/metadata/procedures/RegisterPDB.xml" for plugin id as "oracle.sysman.ssa"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_procedures_2021-10-05_14-15-56PM.log
Registering service "procedures" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.xa.oms.plugin_13.5.1.0.0/metadata/procedures/XaCreateCluster.xml" for plugin id as "oracle.sysman.xa"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_procedures_2021-10-05_14-16-07PM.log
Registering service "systemStencil" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.am.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/systemStencil/32941713/oracle_dblra.xml" for plugin id as "oracle.sysman.am"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_systemStencil_2021-10-05_14-16-17PM.log
Registering service "systemStencil" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/patched_metadata/13.5.1.0.0/systemStencil/32835412/rac_database.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_systemStencil_2021-10-05_14-16-26PM.log
Registering service "discovery" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/discovery/db_discovery.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_discovery_2021-10-05_14-16-35PM.log
Registering service "gccompliance" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/gccompliance/cis19c.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_gccompliance_2021-10-05_14-16-43PM.log
Registering service "derivedAssocs" with register file "/u01/app//em135/middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/derivedAssocs/dg_assoc_rules.xml" for plugin id as "oracle.sysman.db"...
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_13-53-40PM_SystemPatch_32835392_9/emctl_register_derivedAssocs_2021-10-05_14-17-04PM.log
The job_queue_processes parameter is set to 0 in the repository database. Resetting the job_queue_processes parameter to default value 50 in the repository database to start the OMS. If 50 is not your default value for the job_queue_processes parameter, you should reset it to the preferred value post OMS patching.
Starting the oms
Please monitor log file: /u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_14-17-24PM_SystemPatch_32835392_52/start_oms_2021-10-05_14-17-24PM.log
Complete Summary
================
All log file names referenced below can be accessed from the directory "/u01/app//em135/middleware/cfgtoollogs/omspatcher/2021-10-05_14-17-24PM_SystemPatch_32835392_52"
Patching summary:
-----------------
Binaries of the following sub-patch(es) have been applied successfully:
Featureset Sub-patches Log file
---------- ----------- --------
oracle.sysman.top.oms_13.5.0.0.0 32835403,32835412,32840049,32941575,32941618,32941631,32941645,32941673,32941696,32941706,32941713 32835403,32835412,32840049,32941575,32941618,32941631,32941645,32941673,32941696,32941706,32941713_opatch2021-10-05_13-10-58PM_1.log
The following sub-patches are incompatible with components installed in the OMS system:
32860349,32941609,32941662
Deployment summary:
-------------------
The following artifact(s) have been successfully deployed:
Artifacts Log file
--------- --------
SQL rcu_applypatch_original_patch_2021-10-05_14-00-14PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-03-10PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-03-48PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-04-21PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-04-53PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-05-22PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-05-56PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-06-30PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-07-11PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-07-57PM.log
SQL rcu_applypatch_original_patch_2021-10-05_14-08-28PM.log
MRS-commands emctl_register_commands_2021-10-05_14-08-58PM.log
MRS-commands emctl_register_commands_2021-10-05_14-09-06PM.log
MRS-targetType emctl_register_targetType_2021-10-05_14-09-15PM.log
MRS-targetType emctl_register_targetType_2021-10-05_14-09-53PM.log
MRS-targetType emctl_register_targetType_2021-10-05_14-10-06PM.log
MRS-storeTargetType emctl_register_storeTargetType_2021-10-05_14-12-18PM.log
MRS-storeTargetType emctl_register_storeTargetType_2021-10-05_14-12-29PM.log
MRS-storeTargetType emctl_register_storeTargetType_2021-10-05_14-12-38PM.log
MRS-default_collection emctl_register_default_collection_2021-10-05_14-12-49PM.log
MRS-default_collection emctl_register_default_collection_2021-10-05_14-13-12PM.log
MRS-default_collection emctl_register_default_collection_2021-10-05_14-13-21PM.log
MRS-omsPropertyDef emctl_register_omsPropertyDef_2021-10-05_14-13-59PM.log
MRS-jobTypes emctl_register_jobTypes_2021-10-05_14-14-08PM.log
MRS-jobTypes emctl_register_jobTypes_2021-10-05_14-14-21PM.log
MRS-jobTypes emctl_register_jobTypes_2021-10-05_14-14-37PM.log
MRS-SecurityClassManager emctl_register_SecurityClassManager_2021-10-05_14-14-47PM.log
MRS-swlib emctl_register_swlib_2021-10-05_14-14-56PM.log
MRS-swlib emctl_register_swlib_2021-10-05_14-15-09PM.log
MRS-procedures emctl_register_procedures_2021-10-05_14-15-28PM.log
MRS-procedures emctl_register_procedures_2021-10-05_14-15-38PM.log
MRS-procedures emctl_register_procedures_2021-10-05_14-15-56PM.log
MRS-procedures emctl_register_procedures_2021-10-05_14-16-07PM.log
MRS-systemStencil emctl_register_systemStencil_2021-10-05_14-16-17PM.log
MRS-systemStencil emctl_register_systemStencil_2021-10-05_14-16-26PM.log
MRS-discovery emctl_register_discovery_2021-10-05_14-16-35PM.log
MRS-gccompliance emctl_register_gccompliance_2021-10-05_14-16-43PM.log
MRS-derivedAssocs emctl_register_derivedAssocs_2021-10-05_14-17-04PM.log
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) Could not apply the patch "32860349" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
2) Could not apply the patch "32941609" because the "oracle.sysman.bda.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
3) Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
--------------------------------------------------------------------------------
OMSPatcher Session completed with warnings.
Log file location: /u01/app//em135/middleware/cfgtoollogs/omspatcher/32835392/omspatcher_2021-10-05_13-10-29PM_deploy.log
OMSPatcher completed with warnings.As you can see, the patching process completed. Now I have EM 13.5 with RU1 applied.
There are additional notes I want to mention in case you need to install EM 13.5.
If you need Database Templates 19.11 for EM 13.5 either for Single Instance (SI) or Pluggable Database (PDB), you can find them below.
On the other hand. If you need to upgrade your EM from 13.3 or 13.4 to 13.5, take a look at this upgrade checklist located at MOS note 2761728.1.
Happy patching!!!
Alfredo
This year we started a series of deep dive Webcasts related to Oracle Enterprise Manager 13c including all the monitoring, tuning and manageability tools that the Database Management Packs offer.
I want to condense a list of resources for you so you can take advantage of these Webcasts either live or watching the recorded session.
But there’s still more to come. Next month we are going to be hosting another set of Webcast sessions for you. Don’t forget to register!
Thanks,
Alfredo
Oracle SQL Performance Analyzer (SPA) is part of Real Application Testing pack. We can use SPA to validate changes made to the database system. Some of these changes include gathering statistics, creating SQL Profiles or making parameter changes.
In one of my previous posts I provided detailed information on how to use SPA.
The video below provides better context and flow on how to use SPA to validate if a SQL Profile works as expected.
Thanks,
Alfredo
I’ve been getting many questions about the upgrade process of Enterprise Manager (EM). EM upgrade process is a out-of-place upgrade, this means that you need to install a new Oracle Home (OH) and then perform the upgrade of your EM.
EM 13.4 was just released last week and this is the perfect time to start upgrading your old EM installation to 13.4.
In this post I will show you the upgrade process to 13.4 version. The current environment is as below:
First things first. You need to take a look at the Oracle EM documentation and make sure you comply with all the pre-requisites before attempting the upgrade.
As you can see from my current environment configuration, I’m running Oracle Database 12.1.0.2. I’m going to upgrade the OMR DB to 19C as part of this upgrade. The reason? Pretty simple, 19C is the long term release version. If you want to know more about the lifetime support of Oracle products follow below link.
My first step is to download the Oracle DB 19C software and install an Oracle Home (OH). Please be sure you have a valid backup of your database before starting.
Once the OH is ready I’m going to run the pre-upgrade utility as below:
$ export ORACLE_HOME=/u01/app/oracle/product/12c/dbhome_1
$ /u01/app/oracle/product/19c/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19c/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 5 on 2020-02-05T21:16:38
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: EMREPO
Container Name: emrepo
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
...
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-02-05T21:16:38I’m going to execute the preupgrade_fixups.sql script on the OMR first.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 5 22:24:24 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 5
Generated on: 2020-02-05 21:16:32
For Source Database: EMREPO
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. purge_recyclebin YES None.
2. invalid_objects_exist NO Manual fixup recommended.
3. mv_refresh NO Manual fixup recommended.
4. dictionary_stats YES None.
5. tablespaces_info NO Informational only.
Further action is optional.
6. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.Next steps are to shutdown the database instance, move all the network, parameter and password files from the old OH to the new OH.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
$ cp -p /u01/app/oracle/product/12c/dbhome_1/network/admin/* /u01/app/oracle/product/19c/dbhome_1/network/admin/
$ cp -p /u01/app/oracle/product/12c/dbhome_1/dbs/* /u01/app/oracle/product/19c/dbhome_1/dbs/Migrate the listener to the new OH.
$ export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
$ export PATH=${ORACLE_HOME}/bin:$PATH
$ lsnrctl start
...
The listener supports no services
The command completed successfullyStartup the DB in upgrade mode.
$ sqlplus / as sysdba
SQL> startup upgrade
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 5 22:31:34 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1577054672 bytes
Fixed Size 8896976 bytes
Variable Size 1006632960 bytes
Database Buffers 553648128 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> exitExecute dbupgrade tool.
$ $ORACLE_HOME/bin/dbupgrade
Argument list for [/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
...
------------------------------------------------------
Phases [0-107] End Time:[2020_02_05 22:58:13]
------------------------------------------------------
Grand Total Time: 1490s
LOG FILES: (/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/emrepo/upgrade20200205223311/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/emrepo/upgrade20200205223311/upg_summary.log
Grand Total Upgrade Time: [0d:0h:24m:50s]Start the DB instance and execute the post_upgrade.sql script.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 5 22:58:51 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1577054672 bytes
Fixed Size 8896976 bytes
Variable Size 1056964608 bytes
Database Buffers 503316480 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> @/u01/app/oracle/cfgtoollogs/emrepo/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
...
PL/SQL procedure successfully completed.
Session altered.Modify the compatible parameter to 19C.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.I’m going to also modify some parameters required by EM 13.4.
SQL> alter system set optimizer_adaptive_plans=FALSE scope=spfile;
System altered.
SQL> alter system set "_allow_insert_with_update_check" = true scope=spfile;
System altered.
SQL> alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='*';
System altered.
SQL> alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='*';
System altered.
SQL> alter system set "_px_adaptive_dist_method" = OFF scope=both sid='*';
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='*';
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='*';
alter system set "_optimizer_use_feedback" = FALSE scope=both sid='*';
alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='*';
alter system set "_optimizer_performance_feedback" = OFF scope=both sid='*';
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.Verify that the package SYS.DBMS_OBJECTS_APPS_UTILS is present and valid.
SQL> Select * from all_objects where object_name = 'DBMS_OBJECTS_APPS_UTILS' and object_type = 'PACKAGE BODY' and status = 'VALID';
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
---------- -------------- ----------------------- --------- ---------
TIMESTAMP STATUS T G S NAMESPACE
------------------- ------- - - - ----------
EDITION_NAME
--------------------------------------------------------------------------------
SHARING E O A
------------------ - - -
DEFAULT_COLLATION
--------------------------------------------------------------------------------
D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
- - ------------- ------------- -------------- --------------
SYS
DBMS_OBJECTS_APPS_UTILS
11409 PACKAGE BODY 14-JUN-18 06-FEB-20
2020-02-05:22:37:05 VALID N N N 2
NONE N Y N
N N
1 row selected.Restart the OMR database.
At this point we are ready to launch the EM 13.4 install wizard. Download the EM 13.4 software from the link below.
$ chmod +x em13400_linux64.bin
$ ./em13400_linux64.binI’m going to un-select the security updates box and click Next.
I will skip the software updates and click Next.
The wizard will verify that the host complies with all the required pre-requites. If all the checks passed then click Next.
I’ll chose to upgrade my previous EM installation. There are many options here. You could only install the EM 13.4 software and then upgrade at a later time. This is quite useful when you try to reduce upgrade downtime.
Type the new ORACLE_HOME location for this installation.
Then type the OMR credentials. At this point make sure you have a valid DB and OMS backup.
The wizard will run through a couple of checks and provide recommendations. Based on this I’m going to perform the steps below:
$ ./emctl config emkey -copy_to_repos
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.
After the required operation has been completed, secure the EMKey by running "emctl config emkey -remove_from_repos".$ cd $AGENT_HOME/bin
$ ./emctl stop agent$ cd $ORACLE_HOME/bin
$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down
The next screen is going to show the plugins that are going to be upgraded.
Now you can chose if you need additional plugins to be deployed during the install process.
The next screen will prompt for the Weblog Admin Server password.
Next two screens will ask about BI Publisher shared storage and ports used by EM.
Now you can review all the inputs before starting the install and upgrade process.
Click Upgrade to start the install and upgrade.
The last step is to run the allroot.sh script.
And finally the summary report.
Launch you EM login URL and verify the version in the about page.
Now you should start you EM agent and start upgrading all of them.
All new features on 13.4 are on below link.
Hope this guide helps with the upgrade of your EM to 13.4 version.
Thanks,
Alfredo
Today’s post is about SQL tuning. During my more than 14 years of experience as a Database Administrator I haven’t found a better tool than Enterprise Manager (EM) to monitor Oracle’s DB performance. The “Top Activity” graph has been a constant during all these years.
Note: The EM’s Top Activity page has been deprecated in EM 13.3 PG.
So let’s start from the beginning. If I receive a call or ticket stating that the DB or a process in the DB is slow, the first thing I do is to jump to the “ASH Analytics” page in EM. This page is part of the “Diagnostics” DB pack.
Here I can spot right away SQL_ID 6kd5jj7kr8swv being the top CPU consumer followed by other 3 SQL_ID’s. I’m going to create a SQL Tuning Set (STS) containing all these SQL’s.
From the STS dashboard I’m going to to click on “Create” to launch the wizard.
Step 1 requires a name and owner for the STS.
Note: You can create an empty STS and populate it later.
The next step is to load the STS with SQL’s. I’m going to choose “Load SQL one time only” and select from “Cursor Cache“. This because I’m sure they are loaded in memory. Another possible option is to load them from the AWR repository.
Next is to filter the SQL’s we want top load to our STS. I’m using the SQL ID row along with the IN operator to achieve this.
The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.
And finally the review page. If everything looks right, then click on “Submit“.
Once the STS is created you will be able to see those 4 SQL’s loaded into it. I’m going now to schedule a SQL Tuning Advisor (STA) task to find out if these SQL’s can be tuned for better performance. STA is part of the “Tuning” DB pack.
Let’s review the results of the STA task we just created couple of minutes back. STA examined 4 distinct SQL’s. It only found recommendations for 3 of them with an overall potential benefit of 55 seconds in DB time.
Let’s click on “Show all results” button to see detailed information about the recommendations. The table below shows all 4 SQL’s along with their recommendations. We can spot right away the benefits of tuning SQL ID 6kd5jj7kr8swv. A SQL Profile could be created with a benefit of 57% or an Index with a benefit of 72%.
What do you do here? Do you hit “Implement All SQL Profiles” and cross your fingers hoping the new execution plan are going to be faster?
You don’t know how many times I saw sub-optimal plans generated by SQL Profiles.
There’s actually a better way to handle this. Real Application Testing (RAT) DB pack has a tool named SQL Performance Analyzer (SPA). This tool allows you to play the SQL before and after the changes (SQL Profile) and gives you a detailed report with the results.
In order to do that you’ll have to click on the “Validate All Profiles with SPA” button.
What is going to happen is that SPA will gather data before and after implementing the SQL Profiles and will produce a report.
Note: SPA will actually implement the SQL Profiles. Keep this in mind in a running Production system. RAT has also the capabilities to replay this workload in a Non-Production system. You can implement the SQL Profiles there without harming the Production performance.
Ooops! The SQL Profiles actually have a regression impact of -824%. Look at the Net Impact for the 1st SQL. It has a -830% impact, this means that the SQL Profile produced a sub-optimal plan. After this I will go and disable the recently created SQL Profiles before they cause a bigger impact.
My next step will be to propose the Index creation. This may have different implications depending on the application you are running, the business needs and operating procedures. I decided to implement the index to measure the performance benefit.
Here you can see how the performance was before the SQL Profile (maroon), with the SQL Profile implemented (turquoise) and with the index created (pink).
In summary, SPA is a great tool that helps you validate that the changes made to the DB system are working as expected. SPA not only validates SQL Profiles, it also validates statistics, DB parameter changes, DB upgrades and also you can verify the performance of your DB system with an Exadata storage server simulation.
Thanks,
Alfredo
Oracle Enterprise Manager 13c (EM) Lifecycle Management pack offers a set of tools to help you manage all your data canter targets from a single pane of glass. All these tools are listed on my previous post.
One of this tools is Configuration and Drift Management. What is it and how it works? Well, pretty simple. EM 13c collects configuration data from all the configured managed targets and stores it in the Management Repository database. You can use this configuration data to track, search and compare configuration changes for below targets:
The Configuration tool is located under Enterprise -> Configuration. Let’s take a look at the Search section.
This will take you to the Configuration Search Library. Take a look at the pre-defined searches available for all the target types already mentioned. You can run these searches against your managed targets, but also you can create new custom searches based on your requirements.
Let’s select the first Configuration search from the table and click Run.
The Initialization Parameter Settings search will show all the results of Configuration data collected for all the targets with target type as “Database Instance”.
You can export or print this search results. One important feature to mention is that you can customize this search on the fly. Look at the upper right corner for a button called “Search Using SQL”. This basically allows you to use SQL to customize this search. Let’s click on it.
In the Query section you can customize your search as needed. In this example, I’m removing the “emrepo” target from the search. You can save this customized search if needed.
Another way to access Configuration data is to go to the target’s home page and navigate to Configuration -> Latest. This provides you with just this target’s Configuration data and not for all the managed targets.
This page shows all the Configuration data collected for the target based on the pre-defined search. Any changes made to the search will have an impact on these results.
Configuration Properties tab shows all the configuration items along with the values at the time they were collected. See “Last collected at” for more information. You can navigate to “Immediate Relationship” tab to have insights about the relationship of this target with other managed targets. In this example you can see the DB system, listeners, agent and host related to this target.
The rest of the tabs provide information about membership and usage of the target. Now let’s take a look at the “Actions” button. You can save, export, see history and compare this data against another target of the same type.
Let’s click on “Compare” to make a one-time comparison.
I’m going to select another example database from the list. Keep in mind that this database has DataGuard configured, so I’m expecting to see a lot of differences as result.
Here you go! 89 differences between my 2 example databases. Again, some of the parameters are expected to differ but what about:
This is just an small example on how this tool works and how powerful it can be in order to drive consistency in the configuration of all your managed targets.
But wait a minute… Can you automate these comparisons and send me a report? The answer is yes, that will be part of my next post.
Stay tuned!
Alfredo
Today’s infrastructure management is getting complex. We now have hybrid datacenters, multi-cloud solutions, virtual machines and so on. With all this complexity is difficult to manage configuration consistency, changes and to keep up with security vulnerabilities and patches.
Enterprises realize that is not possible to engage more human resources to overcome these issues that easy. Here is where Oracle Enterprise Manager (EM) can help to meet all these challenges by automating discovery of targets, provisioning, patching, cloning and upgrading databases. But more important, doing all these in a consistent manner and following compliance rules.
Lifecycle Management Pack in EM 13c contains a set of tools that provide:
This EM pack is under an additional license covered under the Lifecycle Management pack. I’ll be covering all these tools one by one in future posts.
Additional information can be found in the Oracle EM Lifecycle Management administration guide:
Thanks,
Alfredo
