Oracle Tools

Enterprise Manager 13c – How to apply p23095307: enterprise manager for oms plugins 13.1.1.0.160429

Patch 23095307 is the newest system patch for the Enterprise Manager 13c family. This patch will be applied with the new patch tool OMSPatcher (see blog post http://dbakevlar.com/2016/05/em13c-applying-system-patches-with-the-oms-patcher from @dbakevlar for details). To apply this patch, the OMS has to be stopped.

Prerequisites

  • OMS 13.1.0.0.0
  • OMSPatcher 13.6.0.0.1 – I have installed it in directory /u00/app/oracle/product/em13cr1/OMSPatcher
  • URL / Username / Password from your EM13c Weblogic AdminServer (emctl status oms -details)
  • Patch 23095307 is unzipped and available in a local directory, in my case it’s in /u00/app/oracle/stage

The newest OMSPatcher can be downloaded according the My Oracle Support Note How to upgrade the OMSPatcher to latest version of OMSPatcher (Doc ID 2135028.1)

Patch Verification

For the verification you don’t have to shutdown the OMS. Be sure that your ORACLE_HOME is set to the EM13c installation directory.

As you can see, there is a WARNING in the output that a sub-patch is incompatible with the oracle.sysman.ssa.oms.plugin version 13.1.1.0.0. The SSA.OMS plugin is a plugin for the cloud self service zone. The patch 23092170 is the EM Cloud Plugin Bundle Patch 13.1.1.0.160429. I don’t have installed the cloud plugin during the installation of the EM13 because we don’t use it,  now the OMSPatcher wants this plugin.

Installation of the missing Plugins

To resolve the warning, I have installed the two missing plugins. The Oracle Cloud Application plugin requires the Oracle Virtualization plugin first. Attention: If you install the plugins on the Oracle Management Server, the OMS will be restarted during plugin installation process.

The Oracle Virtualization Plugin

plugin_virtualization

The Oracle Cloud Application Plugin

plugin_cloud

OMSPatcher Re-Analyze

Now the analyze job runs without any warnings or errors. I have removed some lines in the output for better reading.

The problem is solved, the system is ready to patch.

Patch apply

Before you apply the patch, you have to stop the OMS. Then you can apply it.

I have removed some output. if you want to have the full output, you can download it HERE.

Patch successfully applied

The patch is applied, the OMS can be started again. I don’t know why the patch needs the plugins. My understanding from the plugins is to have a small installation, and only the components which will be used should be configured. And at this point, personally I would expect only an INFORMATION  that some components are not installed so they not will be patched and not a WARNING.

The OMSPatcher is a new tool for the EM13c patching, maybe there are some improvements in one of the next releases.

EM13c – Database Monitoring Template with DB Alert Log Metric

The new  DB Alert Log metric

Since the Oracle Enterprise Manager database plugin version 12.1.0.4, there is a new metric “DB Alert Log” available. This metric replaces the existing “Alert Log” metric. The benefit of the new plugin is if an ORA error is detected multiple times in the database alert log based on same error stack, it will be de-duplicated and only one metric alert is generated. Oracle’s recommendation is to replace this metric on the database targets – Changes to the Metric Alert Log Monitoring in Database Plug-in 12.1.0.4 and later (Doc ID 1587020.1).

How to create such metrics  and many more is very well described in Kellyn Pot’Vin blog post serie http://dbakevlar.com/2013/08/em12c-enterprise-monitoring-part-i/

The old metric Alert Log:

alert_log

The new metric DB Alert Log:

db_alert_log

In use the following thresholds for ORA- errors:

  • Critical alerts for ORA-600 and 7445
  • Warning alerts for all other ORA- errors

A basic Monitoring Template

To make my DBA job easier, I have created a basic template which monitors me the most important things. Sure, there are many more things what you can verify in EM13c, but I wanted to keep it simple. Feel free to extend your monitoring templates.

Metric Comparison Operator Warning Threshold Cricital Threshold Collection Schedule
Archive Area Archive Area Used (%) > 80 Every 15 Minutes
DB Alert Log Alert Log Error Trace File Every 5 Minutes
Alert Log Name
Archiver Hung Alert Log Error Contains ORA-
Data Block Corruption Alert Log Error Contains ORA-
Generic Alert Log Error Matches ORA-[0-9]+[^0-9] ORA-0*(600?|7445)[^0-9]
Media Failure Alert Log Error Contains ORA-
Session Terminated Alert Log Error Contains ORA-
Time/Line Number
Incident Generic Incident Matches .* Every 5 Minutes
Operational Error Data Block Corruption Matches .* Every 5 Minutes
Response Status = Down Every 15 Seconds
Tablespaces Full Tablespace Space Used (%) >= 85 97 Every 30 Minutes
Tablespaces Full (dictionary managed) Tablespace Space Used (%) (dictionary managed) >= 85 97 Every 30 Minutes

XML for Import

If you like to use this metric, you can download the XML here: db_monitoring_template_1.0  – just save the code as XML and import the metric in your Oracle EM13c Enterprise Manager.

Verification

After deploying of the template to the targets, verify on the target databases that a) the template is deployed and b) that the collection is enabled.Go to Oracle Database – Monitoring – Metric and Collection Settings (click on image for better view):

metrics

Useful My Oracle Support Notes

Houston – we have deleted all SYS DBMS_SCHEDULER Jobs !

Bad, bad PL/SQL….

A customer of me has executed a simple script to cleanup DBMS_SCHEDULER jobs and programs for an application. Unfortunately he did as user SYS. The content of the SQL script:

All the Oracle internal maintenance jobs and programs like the AUTO_SPACE_ADVISOR_PROG, GATHER_STATS_PROG, FILE_WATCHER_PROGRAM are deleted.

On a 12.1.0.2 single instance database on Linux are per default 19 DBA_SCHEDULER_JOBS and 10 DBA_SCHEDULER_PROGRAMS pre-configured.

Oracle has a MOS note called  How to Rebuild DBMS_SCHEDULER Default Jobs and Autotasks (Doc ID 2089546.1) – but this note is old and incomplete. It does not help to rebuild all the jobs and programs in a 12.1.0.2 database.

After some discussions with My Oracle Support, this way here works to rebuild the jobs. Unfortunately it’s an “offline” task, you have to restart your database in UPGRADE mode. But it works.

1. Make sure that you have a Backup of your Database

2. Restart the Database in UPGRADE Mode

3. Run these Commands as SYSDBA in Order to recreate the missing Scheduler Jobs

All jobs except XMLDB_NFS_CLEANUP_JOB are re-created now. To rebuild the XMLDB_NFS_CLEANUP_JOB you can execute this command as user SYS in SQL*Plus. It’s just an extract from the file ?/rdbms/admin/xdbu102.sql which creates the required job during the regular instance creation process.

4. Restart the Database

5. Run these Queries to verify invalid Objects

6. Verify that DBMS_SCHEDULER Jobs are back

7. Verify DBMS_SCHEDULER Programs are back

Summary

Deleting the SYS DBMS_SCHEDULER jobs is one of these mistakes which can be easily repaired. But during the rebuild action, the database is not available for the end users and this is bad. I’m looking forward that there will be an online solution available maybe in the future.

Another approach could be to execute a FLASHBACK QUERY against the dba_source view to get the DDL for the missing jobs and programs before the delete action. But this has to be tested first.

Oracle Enterprise Manager 13c – Tail your Logfiles

This are my favorite aliases for Enterprise Manager 13c troubleshooting. Just replace $ORACLE_BASE with your own installation directory and add these aliases in your .profile/.bash_profile.

Here is the URL to the Oracle documentation: http://docs.oracle.com/cd/E63000_01/EMADM/logging.htm#EMADM11796 – and feel free to create your own aliases e.g. for the *.OUT logfiles.

Oracle Management Server

NodeManager

Weblogic AdminServer

Weblogic EMGC_OMS1 Managed Server

Weblogic BIP Managed Server

OHS

Enterprise Manager 13c – Disable the BI Publisher

The BI Publisher will be started automatically during the startup process of the Enterprise Manager 13c. You don’t like the BI Publisher or you don’t use it? Save the resources, speed up your startup process, disable it. The password of the database repository owner SYSMAN is required.

Verify the Status – the BI Publisher is up and running

Disable the BI Publisher

Verify the Status again

If you want to enable the BI Publisher again, the command is listed below the emctl output.

Timing

I have tested the start of the Oracle Enterprise Manager in my local virtual machine environment (VMWare Workstation,15GB Memory, 4 Cores, SSD) with and without the BI Publisher. The difference:

BI Publisher enabled – 4 Minutes and 18 Seconds

BI Publisher disabled – 3 Minutes and 6 Seconds

Link to the Oracle Documentation

https://docs.oracle.com/cd/E63000_01/EMUPG/upgrading_oms.htm#CIHGBAJE

Summary

If you don’t use it – disable it.