Oracle DB Patching with EM 13.3

I got several interesting questions last week during my presentation about Oracle Enterprise Manager (EM) 13.3 and Exadata at the East Coast Oracle Conference 2019.

One of them was related on how to patch a DB home using EM 13.3 and I’ll try to show it on this post.

First, keep in mind that in order for EM to download patches it needs to be able to connect to the My Oracle Support (MOS) site using valid credentials. There’s an offline patching option well, I won’t cover that setup on this post though.

Once EM is able to communicate with the MOS site, it will automatically (EM job) download the latest patch recommendation for the managed targets. Let’s navigate to Enterprise -> Provisioning and Patching -> Patching and Updates.

Patches and Updates Home Page

The Patches and Updates home page is divided in 4 sections.

  • A) Quick Links will open a new page linked to MOS site
  • B) Patch Recommendation for the managed targets in EM
  • C) Patch Search allows you to search for specific patches
  • D) Plans created are shown in this section

Let’s take a look at the patch recommendations. They can be viewed by classification or target type.

Patch Recommendations

Click on the bar for Database Instance patches to go to the Patch Details page. As shown below the Details page provides an easy to read table with all the recommendations for that target type.

Patch Details Page

Select the patch from the table and automatically the patch details is populated below. In this example we are looking at the latest OJVM PSU for the 12.1.0.2 version.

At the top of the table you have several options:

  • Create a patching plan
  • Add the patch to an existing patching plan
  • Print the patching information
  • Download the patch (to you computer or to the EM Software Library)

I’m going to download this patch to the EM’s Software Library to apply it later.

Download Patches

Next, we need to create a Patch Plan. This plan will contain all the selected patches and will be analyzed against the Oracle Homes in order to find conflicts or potential issues.

Once the Patch Plan is created we can then click on it to navigate to the Patch Wizard. The first page page of the Wizard is the Patch Plan Information page, it allows us to see and amend the Patch Plan name and to grant access to other users and/or roles.

Plan Information Page

The Patches page provides information about the patches currently added to the Plan. You can add and/or remove patches here.

The Deployment Options page let’s you choose if you want to perform an “out of place” (recommended) or an “in place” patching. There are several caveats here:

  • You have to provide the new Oracle Home (OH) name if “out of place” patching is selected
  • You need to have enough free space for the new OH and for the staging directory
  • You need to provide both the normal and the privileged credentials for the host
Plan Deployment Options

The next step is the Patch Plan Validation. Basically EM will validate all the patches against the Oracle Home.

Analysis in Progress

If the validation is successful, you can proceed to deploy the patches to the Oracle Home.

Ready for Deployment

Finally, you have to select if you want to deploy the patch immediately or at a different schedule.

Deploy Confirmation

Wait for the procedure to apply the patches. Once the procedure finishes you will see the success page along with a link to view the details of the deployment.

Deployment Successful

Note that after the patches are successful deployed, the Plan will be locked. Also, you have the option of creating a template of the Plan so you can deploy it to different targets later.

Thanks,
Alfredo

OEM 13c Database Plugin 13.3.2 Caveat

I have done some testing on the OEM 13c DB Plugin 13.3.2 (PG) for the last several months. I highlighted some of the changes in this new version in a previous post:

During the last Dallas Oracle User Group (DOUG) conference I got couple of questions though. I will try to answer them as best as possible.

  • OEM 13c Top Activity page has been deprecated

I don’t know the reason why, but is a fact. Oracle is moving towards getting rid of the Top Activity page and replacing this with ASH analytics. As I keep mentioning… we are so used to Top Activity page and this will be a shocking factor for users.

We know that Internet browsers such Chrome, IE or Firefox will no longer support Adobe Flash starting next year. Oracle is now using JET instead of Flash. But the Top Activity page was solely using Flash.

There’s a trick though. Top Activity link will show at the end of the Performance Home page. So you can still access it (while your browser permits it).

  • ASH Analytics can’t be used for our monitoring screens

This was a really good question. There are many companies that have big screens in their command centers to monitor mission critical databases. The Top Activity page is always shown in those screens and is really handful by having a quick glance on the DB’s performance.

Top Activity page will refresh every 15 seconds but the highlighted time frame (5 minutes) always moves to the very end. This means that we only monitor the last 5 minutes every single time. We notices a different behavior with ASH analytics though.

When ASH analytics refreshes, the highlighted time frame won’t move to the very end. This means that we are only monitoring the same 5 minutes (static). See the picture below taken from my lab running 13.2 version:

ASH Analytics EM 13.2 version

I confirmed this behavior in 12c, 13.1, 13.2 and 13.3 versions of the DB Plugin. But there are good news. This didn’t happen in the latest 13.3 PG version using JET. The highlighted time frame always moves to the very end thus showing information of the last 5 minutes every time.

This is another reason to upgrade to the latest version of EM.

  • You can only use DB 19c as a repository if you are using the latest DB plugin

Yes. OEM 13c PG was released with full 19c support. So, the best thing to do is to upgrade to 13c PG version if you want to monitor or use 19c as your DB repository.

Hope this helps to address your questions.

Thanks,
Alfredo

The Strange Case Of A Missing Agent Gold Image

If you are running Oracle Enterprise Manager (OEM) 13c, you should (must) be using Agent Gold Images (GI). They provide an easy way to manage the lifecycle of you OEM agents from a fancy dashboard.

The problem came while removing a Gold Image Versions in OEM 13.3PG. Guess what? The whole Gold Image disappeared!!!

This is not the expected behavior for this. The point here is that there were agents still subscribed to the GI. So even trying to create a new GI won’t allow us to subscribe those agents as they were still subscribed to the GI that was missing.

Using some Sherlock Holmes’ skills found that the GI was in fact still present in the OMR tables. You can use the query below to see all the GI currently living in your system.

SELECT image_name FROM MGMT$EM_GAI_ENTITIES;

What about the agents still subscribed to this GI?

SELECT * from EM_GI_AGENT_SUBSCRIBE WHERE gold_image_series='<GI Name>';

OK, so there should be something wrong here. After working with Oracle Support they found the issue and provided a patch. Please refer to Patch 28817766.

Apply this patch on top of your OEM 13.3 PG and you should see the GI again. If you are not able to see the GI, then create a new one with the same old name. Everything should look fine after that.

Happy patching!
Alfredo

OEM 13c – Summer 2019 Announcements. You can manage ATP and more!!

Here are some exciting announcements for OEM 13c.

First, keep in mind that you should upgrade your OEM 13c to the latest version (13.3PG). I discussed some new features of this version in a previous post.

Now to the good news.  

1. Oracle Database 19c has been certified as a OEM Repository Database (OMR). This means that is time to upgrade your repository to a long term support release.

  More info : EM 13.3C: Certification of 19c Database as an EM Repository Database and as a Managed Target (Doc ID 2541711.1)    

2. Oracle EM 13.3.2 Bundle Patch (BP) is available. You must keep up with patching if you don’t want to encounter issues down the road.  

More info: Enterprise Manager Cloud Control Database Plugin 13.3.2.0 Bundle Patch Bug List (Doc ID 2554474.1)    

3. The latest EM 13.3.2 BP 3 has support for Oracle Autonomous Database (ATP-D). So, if you are running ATP in dedicated mode and you want to use OEM to manage your DB, is time to patch!!

More info: https://blogs.oracle.com/oem/oracle-enterprise-manager-cloud-control-13c-release-3-db-plugin-133200-with-bundle-patch-3-is-now-available-in-self-update    

Thanks,
Alfredo

OEM 12c/13c Metric Extension – Collection Result Maximum Flood Control

I recently had an issue in OEM 12c while creating a Metric Extension that was returning more than 5000 records. In OEM 12c versions 12.1.0.4 and 12.1.0.5 there a flood control mechanism in order to limit the maximum number of rows for a metric.


You can also verify this in the agent_inst/sysman/log/gcagent.log file:
2018-10-08 13:38:50,330 [66:F119EDFE:GC.Executor.1 (host:hostname:ll_host_config) (host:hostname:ll_host_config:ECM_OS_COMPONENT)] WARN – Result set exceeding min flood control level
The solution is provided in the MOS Doc Id 1499381.1
We didn’t want to proceed to apply the patch but rather we limited the number of rows from the Metric Extension.
This OMS patch is available also for 13.1 version and the bug states that is fixed in 13.2 versions.
Thanks,
Alfredo

OEM 13c – AMP EBS Discovery Error Due To Lack Of Privileges

We recently had to enable monitoring for an EBS 11i system using the Application Management Pack. Even though the EM_MONITOR user was already provisioned by the EBS patch, the discovery was failing due to lack of privileges on several FND tables.
My Oracle Support shows several bugs related to missing privileges like the one below, but I wasn’t able to find one for 11i.

Patch 21951154: 1OFF:12.2.0: READ ACCESS NOT PRESENT FROM EM_MONITOR USER FOR FOLLOWING TABLES

I decided to manually track the missing privileges on these tables and here’s the list I found in order to make the discovery work.
GRANT SELECT ON “APPLSYS”.”AD_APPL_TOPS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”AD_FIXED_ISSUES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”AD_PATCH_DRIVER_LANGS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”AD_PATCH_RUNS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”AD_PATCH_RUN_BUGS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”FND_LOG_EXCEPTIONS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”FND_LOG_UNIQUE_EXCEPTIONS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”FND_NODES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”FND_PROFILE_OPTIONS_TL” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPLSYS”.”FND_USER” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “APPS”.”HR_OPERATING_UNITS” TO “EM_OAM_MONITOR_ROLE”;
GRANT EXECUTE ON “APPS”.”JTF_DIAG_DEPENDENCIES” TO “EM_OAM_MONITOR_ROLE”;
GRANT EXECUTE ON “APPS”.”JTF_DIAG_INPUTS” TO “EM_OAM_MONITOR_ROLE”;
GRANT EXECUTE ON “APPS”.”JTF_DIAG_INPUTTBL” TO “EM_OAM_MONITOR_ROLE”;
GRANT EXECUTE ON “APPS”.”JTF_DIAG_VERSION” TO “EM_OAM_MONITOR_ROLE”;
GRANT EXECUTE ON “APPS”.”JTF_DIAG_VERSION_NT” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”ALL_IND_COLUMNS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”ALL_OBJECTS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”ALL_QUEUES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”ALL_TRIGGERS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_DATA_FILES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_OBJECTS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_PROCEDURES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_SOURCE” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_TAB_PRIVS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_TEMP_FILES” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_USERS” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”DBA_USERS_WITH_DEFPWD” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”GV_$INSTANCE” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”GV_$LOGFILE” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”GV_$SESSION” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”V_$INSTANCE” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”V_$THREAD” TO “EM_OAM_MONITOR_ROLE”;
GRANT SELECT ON “SYS”.”V_$VERSION” TO “EM_OAM_MONITOR_ROLE”;
Hope this helps to your discovery.
Thanks,
Alfredo

OEM 13c Reporting Incorrect Tablespace Space Usage (%) After July 2018 PSU

The topic for today is Tablespace monitoring. OEM 13c started reporting inaccurate values for the Space Usage (%) metric after applying either the April 2018 or the July 2018 Database PSU’s. The way this was detected, is that OEM 13c Corrective Actions started firing here and there after the patches.
Oracle confirmed this is tracked on BUG 26198757.
This patch is available on top of both the April 2018 and the October 2018 PSU’s but is not available for October 2018 PSU. The issue could also manifest itself as huge wait times on “Sync ASM rebalance” event.
Looks like the issue is corrected after applying the patch on top of July 2018 PSU. Below are some MOS realted to this.
Bug 26198757 – dba_tablespace_usage_metrics.used_percent is incorrect after applying 25397136 (Doc ID 26198757.8)
EM13c Space Monitoring Query (sqlid=69p6my4hpdm3j) On dba_tablespace_usage_metrics Timed Out after Db Upgraded To 12.2 (Doc ID 2375714.1)
EM 13c, 12.1.0.5: Enterprise Manager Cloud Control Tablespace Space Used (%) Metric Incorrectly Triggering (Doc ID 2313520.1)
DBA_TABLESPACE_USAGE_METRICS Returns Incorrect Information After applying 12.1.0.2.170418 (25397136) Bundle patch (Doc ID 2289448.1)
Thanks and happy patching,
Alfredo

Exalogic Agents being automatically deleted from OEM 13c

Oracle Enterprise Manager (OEM) 13c is capable of monitoring virtual guest hosts running on Exalogic (virtual configuration). OEM will connect (read-only) to the Oracle VM Manager and get the configuration and status of the VM’s running there. Then you can deploy an agent to those VM hosts and you have a complete monitoring solution for Exalogic.
The problem comes when for whatever reason you have to drop those VM’s from Exalogic but you want to keep the OEM configuration in place. For example, you want to drop the VM from Exalogic and then restore it from backup.
In this case you want to keep your monitoring configuration (metrics, credentials, etc.), but you realize that the agent was removed (along with all targets) as soon as you drop the VM from Exalogic.
This setup is being driven by metric like configuration. This synchronization is happening every minute by default.  This is covered on below Oracle’s document:
The solution was to set this synchronization schedule from every 1 minute to every 1 day.
Thanks,
Alfredo

OEM 13C Useful EMCTL Agent Commands

Here’s a list of some useful emctl commands to manage your OEM 13c agent.
Get the status of the agent:
$ emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version          : 13.2.0.0.0
OMS Version            : 13.2.0.0.0
Protocol Version       : 12.1.0.1.0
Last Reload            : (none)
Last successful upload                       : 2018-05-15 09:04:32
Last attempted upload                        : 2018-05-18 13:12:29
Total Megabytes of XML files uploaded so far : 13.6
Number of XML files pending upload           : 1,956
Size of XML files pending upload(MB)         : 3.64
Available disk space on upload filesystem    : 81.90%
Collection Status                            : Collections enabled
Heartbeat Status                             : OMS is unreachable [not running]
Last attempted heartbeat to OMS              : 2018-05-18 13:12:02
Last successful heartbeat to OMS             : 2018-05-15 09:10:00
Next scheduled heartbeat to OMS              : 2018-05-18 13:12:44
—————————————————————
Agent is Running and Ready
Get the list of targets currently monitored by the agent:
$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[localhost.localdomain.com, host]
[localhost.localdomain.com:3872, oracle_emd]
[Management Services and Repository, oracle_emrep]
[/EMGC_GCDomain/GCDomain/EMGC_OMS1, weblogic_j2eeserver]
[NodeManager_localhost.localdomain.com_1, weblogic_nodemanager]
[oms13c1_1_localhost.localdomain.com_4754, oracle_home]
Get the status of a particular target:
$ emctl status agent target /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Target Name : /EMGC_GCDomain/GCDomain/EMGC_OMS1
Target Type : weblogic_j2eeserver
Current severity state
———————-
Metric        Column name             Key             State           Timestamp
——————————————————————————–
Response      Status                  n/a             CRITICAL        Fri May 18 13:13:25 EDT 2018
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:07:21 2017/254 CRITICAL        Fri Jun 23 16:07:58 EDT 2017
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:08:10 2017/263 CRITICAL        Fri Jun 23 16:12:58 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jul 10 06:06:34 2017/290 CRITICAL        Mon Jul 10 06:08:52 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jun 26 13:04:51 2017/272 CRITICAL        Mon Jun 26 13:08:48 EDT 2017
alertLogAdrIncident adr_problemKey          Tue Jun 27 17:50:54 2017/281 CRITICAL        Tue Jun 27 17:53:48 EDT 2017
jvm           heapUsedPercentage.value n/a             CLEAR           Tue May 15 09:07:27 EDT 2018
jvm_threads   deadlockedThreadCount.value n/a             CLEAR           Tue May 15 09:06:15 EDT 2018
—————————————————————
Agent is Running and Ready
Clear the current status of a target’s metrics:
$ emctl clearstate agent /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Clear target severity state
Now they show as undefined until the next collection happens:
$ emctl status agent target /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Target Name : /EMGC_GCDomain/GCDomain/EMGC_OMS1
Target Type : weblogic_j2eeserver
Current severity state
———————-
Metric        Column name             Key             State           Timestamp
——————————————————————————–
Response      Status                  n/a             UNDEFINED       Fri May 18 13:14:25 EDT 2018
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:07:21 2017/254 UNDEFINED       Fri Jun 23 16:07:58 EDT 2017
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:08:10 2017/263 UNDEFINED       Fri Jun 23 16:12:58 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jul 10 06:06:34 2017/290 UNDEFINED       Mon Jul 10 06:08:52 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jun 26 13:04:51 2017/272 UNDEFINED       Mon Jun 26 13:08:48 EDT 2017
alertLogAdrIncident adr_problemKey          Tue Jun 27 17:50:54 2017/281 UNDEFINED       Tue Jun 27 17:53:48 EDT 2017
jvm           heapUsedPercentage.value n/a             UNDEFINED       Tue May 15 09:07:27 EDT 2018
jvm_threads   deadlockedThreadCount.value n/a             UNDEFINED       Tue May 15 09:06:15 EDT 2018
—————————————————————
Agent is Running and Ready
Force the agent to collect dynamic properties:
$ emctl reload agent dynamicproperties /EMGC_GCDomain/GCDomain/EMGC_OMS1:weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
EMD recompute dynprops completed successfully
Thanks,
Alfredo