Oracle Enterprise Manager 13c – Connection to MOS not working

Oracle Enterprise Manager (EM) 13c has the ability to connect to My Oracle Support (MOS) in order to download patch recommendations, information about new plugins and versions. This is pretty useful in terms of keeping your EM up to date.

There was an issue early this year that prevented the connection from EM to the MOS portal. This has been addressed and a patch is needed in your EM in order to restore this functionality.

Take a look at patch 31233849 and MOS note 2697356.1. This patch is already included on the EM 13c R6 update. My recommendation is to apply the latest RU to your EM 13.4 installation.

Happy patching!!!

Thanks,
Alfredo

Oracle Enterprise Manager Webcast Series

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 Enterprise Manager App for Grafana

Oracle Enterprise Manager 13c provides out-of-box dashboard functionality, so you can see the status of all your targets on a single screen. You also have information about target’s fatal, critical, warning and escalated incidents along with detailed information about them.

But what about additional data that EM is already collecting? What if you create Metric Extensions and you want to display them in the dashboard?

Oracle Enterprise Manager App for Grafana was just released last month. This Grafana plug-in allows you directly connect from Grafana to Oracle Enterprise Manager and make use of the already collected data in order to create custom dashboards.



In this post I want to guide you through the process of installing a test Grafana server and connect that to Oracle Enterprise Manager.

First things first, you need to make sure you install Grafana 6.6.2 or higher and you have Oracle Enterprise Manager 13.4 RU 3 or higher. Let’s now jump into the installation process.

I have a small VM running OL7. I assinged 2 CPUs and 8 GB of RAM. The next step is to install Grafana in the VM.

$ wget https://dl.grafana.com/oss/release/grafana-7.0.5-1.x86                                                                                                                                                                                                                                             _64.rpm
--2020-06-30 19:36:01--  https://dl.grafana.com/oss/release/grafana-7.0.5-1.x86_                                                                                                                                                                                                                                             64.rpm
Resolving dl.grafana.com (dl.grafana.com)... 151.101.138.217, 2a04:4e42:46::729
Connecting to dl.grafana.com (dl.grafana.com)|151.101.138.217|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 49118311 (47M) [application/x-redhat-package-manager]
Saving to: ‘grafana-7.0.5-1.x86_64.rpm’

100%[======================================>] 49,118,311   232MB/s   in 0.2s

2020-06-30 19:36:02 (232 MB/s) - ‘grafana-7.0.5-1.x86_64.rpm’ saved [49118311/49                                                                                                                                                                                                                                             118311]
$ sudo yum install grafana-7.0.5-1.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining grafana-7.0.5-1.x86_64.rpm: grafana-7.0.5-1.x86_64
Marking grafana-7.0.5-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package grafana.x86_64 0:7.0.5-1 will be installed
--> Processing Dependency: fontconfig for package: grafana-7.0.5-1.x86_64
--> Processing Dependency: urw-fonts for package: grafana-7.0.5-1.x86_64
...
--> Running transaction check
---> Package libXau.x86_64 0:1.0.8-2.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                              Arch   Version         Repository    Size
================================================================================
Installing:
 grafana                              x86_64 7.0.5-1         /grafana-7.0.5-1.x8                                                                                                                                                                                                                                             6_64
                                                                          143 M
Installing for dependencies:
 ...
Installed:
  grafana.x86_64 0:7.0.5-1

Complete!

Now is time to start Grafana server in the VM.

$ sudo systemctl daemon-reload
$ sudo systemctl start grafana-server
$ sudo systemctl status grafana-server
● grafana-server.service - Grafana instance
   Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-06-30 19:40:40 GMT; 1s ago
 ...
lvl=info msg="External plugins directory created" logger=plugins directory=/var/lib/grafana/plugins
Jun 30 19:40:40 grafana-s02 systemd[1]: Started Grafana instance.
Jun 30 19:40:40 grafana-s02 grafana-server[11614]: t=2020-06-30T19:40:40+0000 lvl=info msg="HTTP Server Listen" logger=http.server address=[::]:3000 protocol=http subUrl= socket=

Let’s now login to our Grafana server installation. By default the port assigned is 3000, so the URL will be similar to http://<grafana_server>:3000

Grafana Welcome Page

The default credentials are admin/admin. Grafana will ask you to change the admin password right after the first login.

The next step is to download and install the Oracle Enterprise Manager App for Grafana. You can find the download link and documentation below.



There are 2 methods for installing the EM App for Grafana:

  • Using CLI (grafana-cli)
  • Without CLI

For my test, I’m going to install the EM App for Grafana without using the CLI.

$ sudo mv oracle-emcc-app-1.0.2.zip /var/lib/grafana/plugins/
$ sudo unzip /var/lib/grafana/plugins/oracle-emcc-app-1.0.2.zip
Archive:  oracle-emcc-app-1.0.2.zip
   creating: oracle-emcc-app-1.0.2/
   creating: oracle-emcc-app-1.0.2/datasource/
  inflating: oracle-emcc-app-1.0.2/datasource/query_ctrl.js
  inflating: oracle-emcc-app-1.0.2/datasource/Response.js
  inflating: oracle-emcc-app-1.0.2/datasource/EMCCConstants.js
  inflating: oracle-emcc-app-1.0.2/datasource/plugin.json
  inflating: oracle-emcc-app-1.0.2/datasource/QueryResource.js
   creating: oracle-emcc-app-1.0.2/datasource/img/
  inflating: oracle-emcc-app-1.0.2/datasource/img/em_logo.png
  inflating: oracle-emcc-app-1.0.2/datasource/module.js
  inflating: oracle-emcc-app-1.0.2/datasource/BaseQueryResource.js
  inflating: oracle-emcc-app-1.0.2/datasource/MetricQueryResource.js
  inflating: oracle-emcc-app-1.0.2/datasource/Utils.js
  inflating: oracle-emcc-app-1.0.2/datasource/config_ctrl.js
  inflating: oracle-emcc-app-1.0.2/datasource/datasource.js
   creating: oracle-emcc-app-1.0.2/datasource/partials/
  inflating: oracle-emcc-app-1.0.2/datasource/partials/query.editor.html
  inflating: oracle-emcc-app-1.0.2/datasource/partials/config.html
  inflating: oracle-emcc-app-1.0.2/datasource/partials/annotation.editor.html
  inflating: oracle-emcc-app-1.0.2/plugin.json
  inflating: oracle-emcc-app-1.0.2/README.md
   creating: oracle-emcc-app-1.0.2/img/
  inflating: oracle-emcc-app-1.0.2/img/em_logo.png
   creating: oracle-emcc-app-1.0.2/components/
  inflating: oracle-emcc-app-1.0.2/components/config.js
   creating: oracle-emcc-app-1.0.2/components/partials/
  inflating: oracle-emcc-app-1.0.2/components/partials/config.html
  inflating: oracle-emcc-app-1.0.2/module.js
   creating: oracle-emcc-app-1.0.2/dashboards/
  inflating: oracle-emcc-app-1.0.2/dashboards/template_single_em_single_target_type_single_target_name_single_named_credential.json
  inflating: oracle-emcc-app-1.0.2/dashboards/template_single_em_single_target_type_single_target_name.json
  inflating: oracle-emcc-app-1.0.2/dashboards/template_single_em_single_target_type.json
  inflating: oracle-emcc-app-1.0.2/dashboards/template_multi_em.json
  inflating: oracle-emcc-app-1.0.2/dashboards/database_performance_report.json
  inflating: oracle-emcc-app-1.0.2/dashboards/template_single_em_single_target_type_multi_target_name.json
  inflating: oracle-emcc-app-1.0.2/dashboards/database_configuration_report.json
  inflating: oracle-emcc-app-1.0.2/dashboards/template_single_em.json

We now have to restart Grafana server.

$ sudo systemctl restart grafana-server
$ sudo systemctl status grafana-server
● grafana-server.service - Grafana instance
   Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; disabled; vendor preset: disabled)
  ...
Jul 01 16:21:11 grafana-s02 systemd[1]: Started Grafana instance.
Jul 01 16:21:11 grafana-s02 grafana-server[5675]: t=2020-07-01T16:21:11+0000 lvl=info msg="HTTP Server Listen" logger=http.server address=[::]:3000 protocol=http subUrl= socket=

The EM App for Grafana has been installed, now we need to enable it and start using it. Before we do that we need to also enable it in our EM installation first. There 3 emctl command that we need to execute:

$ emctl set property -name oracle.sysman.db.restfulapi.grafana.enable -value true -sysman_pwd ********
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Property oracle.sysman.db.restfulapi.grafana.enable has been set to value true for all Management Servers
OMS restart is not required to reflect the new property value

$ emctl set property -name oracle.sysman.db.restfulapi.grafana.executesql.repository.query.enable -value true -sysman_pwd ********
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Property oracle.sysman.db.restfulapi.grafana.executesql.repository.query.enable has been set to value true for all Management Servers
OMS restart is not required to reflect the new property value

$ emctl set property -name oracle.sysman.db.restfulapi.grafana.executesql.target.query.enable -value true -sysman_pwd ********
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Property oracle.sysman.db.restfulapi.grafana.executesql.target.query.enable has been set to value true for all Management Servers
OMS restart is not required to reflect the new property value

Login back to the Grafana server and navigate to Configuration -> Plugins. Scroll down and look for Oracle Enterprise Manager App and click on the Plugin. You will see a button to “Enable” the plugin.

Enable Oracle Enterprise Manager App Plugin

Now is time to configure our Grafana datasource to connect to our Oracle Enterprise Manager installation. Navigate to Configuration -> Data Sources. Click on “Add data source” button. Scroll down and look for Oracle Enterprise Manager. Click on that and fill the required parameters:

  • Name
  • URL
  • User (do not use SYSMAN)
  • Password

Now click on “Save & Test” and look for a confirmation.

In order to verify that our Oracle Enterprise Manager App for Grafana is working properly we can open one of the sample dashboards. Let’s navigate to Dashboards -> Manage and click on the “Sample – Database Performance Report” dashboard. You should see something similar as below.

Sample Grafana – EM Dashboard

You can copy these sample dashboards and adjust them for your needs. You may also develop your own dashboards by querying all the Repository Views that EM exposes as part of the extensibility framework.

Hope this provides some guidance on how to deploy the Oracle Enterprise Manager App for Grafana.

Thanks,
Alfredo

Finding the Weblogic Password for the Oracle Enterprise Manager Marketplace Image

Oracle Enterprise Manager (EM) 13.4 is available in the Oracle Cloud Infrastructure Marketplace. This image is very flexible and allows you to choose between different EM Deployment sizes ranging from Simple (Single node) to Large (Multi-node) setups.

I decided to deploy a Simple configuration in order to test it out. The deployment wizard prompts you for several passwords including the Enterprise Manager (sysman), Agent registration, Database password, etc. But it never asks for the Weblogic console password. I first thought that the Weblogic console and Enterprise Manager (sysman) password were going to be the same, but for my surprise they weren’t.

The Marketplace image comes with RU 2 deployed and I need to apply the latest RU 3 to this Oracle EM installation and I must know the Weblogic console username and password in order to accomplish this.

In one of my previous posts, I explained how to apply this RU 3 to Oracle EM.



So, the question now is… What are the username and password for the Weblogic console? Is there any way to change them if I don’t know them?

There’s an MOS note available that explains how to change these passwords when the current password is known.

EM 12c , EM 13c: Steps for Modifying the Password for Weblogic and Nodemanager User Accounts in the Enterprise Manager Cloud Control Installation (Doc ID 1450798.1)

But in this case, I don’t know it. For some reason I remembered that someone posted a way to decrypt this password many years ago when we were running EM 12c. I’m glad that the browser search came back with the result I was looking for. Gokhan Atil posted the method in 2015. Below is the link to his post.



Now is time to follow the steps and find that needed password.

[oracle@oms1 em]$ cd /u01/app/oracle/em/gc_inst_134/user_projects/domains/GCDomain/
[oracle@oms1 GCDomain]$ cat servers/EMGC_ADMINSERVER/security/boot.properties
# Generated by Configuration Wizard on Sat May 09 09:30:41 GMT 2020
username={AES}oK4uyE6/MrBQd+38zr+wSk5y4vTMkQjB19ZOri4sDOI=
password={AES}hUM4t0cbEIJRuluMPxGBN3yGom1M9jwxEhNeGByu+8vTnmbG+xOTR
[oracle@oms1 GCDomain]$ vi recoverpassword.java
#####Insert this content#######
public class recoverpassword {
 public static void main(String[] args)
 {
  System.out.println(
  new weblogic.security.internal.encryption.ClearOrEncryptedService(
  weblogic.security.internal.SerializedSystemIni.getEncryptionService(args[0]
   )).decrypt(args[1]));
  }
}
#####Code ends here#########
[oracle@oms1 GCDomain]$ . bin/setDomainEnv.sh
[oracle@oms1 GCDomain]$ javac recoverpassword.java
[oracle@oms1 GCDomain]$ java -cp $CLASSPATH:. recoverpassword \
> $DOMAIN_HOME {AES}oK4uyE6/MrBQd+38zr+wSk5y4vTMkQjB19ZOri4sDOI=
weblogic
[oracle@oms1 GCDomain]$ java -cp $CLASSPATH:. recoverpassword \
> $DOMAIN_HOME {AES}hUM4t0cbEIJRuluMPxGBN3yGom1M9jwxEhNeGByu+8vTnmbG+xOTR
*************

Voila! Now I have both the username and password for the Weblogic console. Now I can proceed and apply the RU 3 to the OMS instance.

Thanks and happy patching!
Alfredo

DevOps, Ansible and Oracle Enterprise Manager

As IT moves towards virtualization, cloud and hybrid environments, containers, agile developments, etc. Automation and orchestration tools are gaining popularity to build and deliver environments faster and efficiently.

In this post, I want to show how Oracle Enterprise Manager (EM) interacts with 3rd party tools and how this can be used to integrate EM’s functionality with DevOps tools like Ansible or Terraform for configuration management and automation.

Oracle Enterprise Manager (EM) offers a complete tool-set for monitoring, provisioning, upgrade and patching, consolidation, compliance, DBaaS, etc., for the Oracle Database. Oracle Enterprise Manager makes use of Plug-ins and managements packs in order to discover, monitor and manage targets. EM’s functionality can be further extended with connectors and the EM’s extensibility framework in order to interact with 3rd party tools.



You can interact with Oracle Enterprise Manager (EM) in several ways:

  • Oracle Enterprise Manager Console
  • Oracle Enterprise Manager Command Line Interface (EMCLI)
  • Management Repository Views
  • Web Service REST APIs
  • Executing SQL via REST APIs (13.4 only)

We can leverage all the EMCLI commands and REST API’s that Oracle EM exposes with tools like Ansible or Terraform for a complete automation and orchestration solution.

One of the requirements for Oracle EM to monitor and manage a target is to deploy an Oracle Management Agent (OMA) to the machine hosting the target. Many organizations use DevOps tools to deploy virtual machines in an automated fashion. We can integrate the Oracle EM’s agent deployment process with these tools. This helps to drive consistency and reduces the efforts and time that administrators spend deploying Oracle Management Agents.

Let’s deploy an OMA to a Linux host using Ansible and Oracle’s EMCLI interface.

First of all, I need to download and install Ansible in a test server. My test server is a small virtual machine running Oracle Linux 7.

[root@ansible-s02 ~]# yum install -y ansible
Loaded plugins: langpacks, ulninfo
ol7_UEKR5                                                | 2.8 kB     00:00
ol7_addons                                               | 2.8 kB     00:00
ol7_developer                                            | 2.8 kB     00:00
ol7_developer_EPEL                                       | 3.4 kB     00:00
ol7_ksplice                                              | 2.8 kB     00:00
ol7_latest                                               | 3.4 kB     00:00
ol7_oci_included                                         | 2.9 kB     00:00
ol7_optional_latest                                      | 2.8 kB     00:00
ol7_software_collections                                 | 2.8 kB     00:00
(1/19): ol7_UEKR5/x86_64/updateinfo                        |  64 kB   00:00
(2/19): ol7_developer/x86_64/primary_db                    | 544 kB   00:00
(3/19): ol7_developer_EPEL/x86_64/group_gz                 |  87 kB   00:00
(4/19): ol7_developer_EPEL/x86_64/updateinfo               | 6.3 kB   00:00
(5/19): ol7_addons/x86_64/updateinfo                       |  91 kB   00:00
(6/19): ol7_ksplice/updateinfo                             | 5.3 kB   00:00
(7/19): ol7_addons/x86_64/primary_db                       | 153 kB   00:00
(8/19): ol7_latest/x86_64/group_gz                         | 134 kB   00:00
(9/19): ol7_ksplice/primary_db                             | 964 kB   00:00
(10/19): ol7_latest/x86_64/updateinfo                      | 2.9 MB   00:00
(11/19): ol7_developer/x86_64/updateinfo                   | 7.2 kB   00:00
(12/19): ol7_UEKR5/x86_64/primary_db                       | 6.9 MB   00:00
(13/19): ol7_oci_included/x86_64/primary_db                | 211 kB   00:00
(14/19): ol7_optional_latest/x86_64/updateinfo             | 1.0 MB   00:00
(15/19): ol7_software_collections/x86_64/updateinfo        | 8.7 kB   00:00
(16/19): ol7_software_collections/x86_64/primary_db        | 4.9 MB   00:00
(17/19): ol7_developer_EPEL/x86_64/primary_db              |  12 MB   00:00
(18/19): ol7_latest/x86_64/primary_db                      |  24 MB   00:00
(19/19): ol7_optional_latest/x86_64/primary_db             | 4.7 MB   00:00
...
Installed:
  ansible.noarch 0:2.8.4-1.0.1.el7

Dependency Installed:
  python-httplib2.noarch 0:0.9.2-0.1.el7
  python-paramiko.noarch 0:2.1.1-9.el7
  python2-jmespath.noarch 0:0.9.4-1.el7
  python3.x86_64 0:3.6.8-13.0.1.el7
  python3-libs.x86_64 0:3.6.8-13.0.1.el7
  python3-pip.noarch 0:9.0.3-7.el7_8
  python3-setuptools.noarch 0:39.2.0-10.el7
  sshpass.x86_64 0:1.06-1.el7

Complete!

Ansible is now installed in the test machine. The Ansible installion will create a directory under “/etc” that contains the configuration, host inventory and roles.

[root@ansible-s02 ~]# cd /etc/ansible/
[root@ansible-s02 ansible]# ls
ansible.cfg  hosts  roles

Our next step is to edit the host inventory file and add both, our Oracle EM server and the machine where we want to deploy the OMA into. This will look similar to my configuration file below.

# This is the default ansible 'hosts' file.
#
# It should live in /etc/ansible/hosts
#
#   - Comments begin with the '#' character
#   - Blank lines are ignored
#   - Groups of hosts are delimited by [header] elements
#   - You can enter hostnames or ip addresses
#   - A hostname/ip can be a member of multiple groups

# Ex 1: Ungrouped hosts, specify before any group headers.

## green.example.com
## blue.example.com
## 192.168.100.1
## 192.168.100.10

# Ex 2: A collection of hosts belonging to the 'webservers' group

## [webservers]
## alpha.example.org
## beta.example.org
## 192.168.1.100
## 192.168.1.110

# If you have multiple hosts following a pattern you can specify
# them like this:

## www[001:006].example.com

# Ex 3: A collection of database servers in the 'dbservers' group

## [dbservers]
##
## db01.intranet.mydomain.net
## db02.intranet.mydomain.net
## 10.25.1.56
## 10.25.1.57

# Here's another example of host ranges, this time there are no
# leading 0s:

## db-[99:101]-node.example.com


[local]
127.0.0.1

[emserver]
10.0.0.28

[emtarget]
10.0.0.11

I also configured a password-less SSH connectivity between the Ansible test machine and both, the EM’s server and the server where I want to deploy the OMA into. After this, I’m going to verify the connectivity from Ansible to both servers.

[root@ansible-s02 ansible]# ansible emserver -m ping -u oracle
 [WARNING]: Platform linux on host 10.0.0.28 is using the discovered Python
interpreter at /usr/bin/python, but future installation of another Python
interpreter could change this. See https://docs.ansible.com/ansible/2.8/referen
ce_appendices/interpreter_discovery.html for more information.

10.0.0.28 | SUCCESS => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python"
    },
    "changed": false,
    "ping": "pong"
}
[root@ansible-s02 ansible]# ansible emtarget -m ping -u oracle
 [WARNING]: Platform linux on host 10.0.0.11 is using the discovered Python
interpreter at /usr/bin/python, but future installation of another Python
interpreter could change this. See https://docs.ansible.com/ansible/2.8/referen
ce_appendices/interpreter_discovery.html for more information.

10.0.0.11 | SUCCESS => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python"
    },
    "changed": false,
    "ping": "pong"
}

I see the SUCCESS flag on both of them. Now is time to develop my .yml file with all the necessary steps to deploy the OMA to the host.

Let’s pause for a minute and review all the available options to deploy an OMA to servers. The list below show’s these options:

  • Oracle Enterprise Manager Console
  • Oracle Enterprise Manager Command Line Interface (EMCLI)
  • Silent Mode
    • Using the AgentPull script
    • Using the agentDeploy script
    • Using the RPM file


I decided to use EMCLI to deploy the OMA to a host in this example. The .yml file to deploy the OMA looks like:

- hosts: emtarget
  vars:
        emagent_port: 3874
        emagent_base: /u01/app/oracle/product/agent134c
  tasks:
    - name: check emagent port
      wait_for: port={{ emagent_port }} state=stopped timeout=1
    - name: creating base directory
      file: path={{ emagent_base }} state=directory owner="oracle" group="oinstall"
      tags: install

- hosts: emserver
  vars:
        emagent_port: 3874
        emagent_base: /u01/app/oracle/product/agent134c
        emagent_hostname: emtarget.oracle.com
        emagent_platform_id: 226
  tasks:
    - name: execute emcli
      command: "/u01/app/oracle/product/omshome/bin/emcli submit_add_host -host_names={{ emagent_hostname }} -platform={{ emagent_platform_id }} -installation_base_directory={{ emagent_base }} -credential_name=\"HOST_ORACLE\" -port={{ emagent_port }} -wait_for_completion"
      register: result

    - name: show results
      debug:
        var: result.stdout

Now is time to execute our Ansible Playbook.

[root@ansible-s02 ansible]# ansible-playbook emcli_deploy_agent.yml -u oracle

PLAY [emtarget] *************************************************************************************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ******************************************************************************************************************************************************************************************************************************************************************************************************
ok: [10.0.0.11]

TASK [check emagent port] ***************************************************************************************************************************************************************************************************************************************************************************************************
ok: [10.0.0.11]

TASK [creating base directory] **********************************************************************************************************************************************************************************************************************************************************************************************
ok: [10.0.0.11]

PLAY [emserver] *************************************************************************************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ******************************************************************************************************************************************************************************************************************************************************************************************************
ok: [10.0.0.28]

TASK [execute emcli] ********************************************************************************************************************************************************************************************************************************************************************************************************
changed: [10.0.0.28]

TASK [show results] *********************************************************************************************************************************************************************************************************************************************************************************************************
ok: [10.0.0.28] => {
    "result.stdout": "Session Name : ADD_HOST_SYSMAN_Jun_26_2020_11:54:55_AM_EDT\nOverAll Status : Agent Deployment Succeeded\n\nHost                 Platform Name  Initialization  Remote Prerequisite  Agent Deployment  Error\nemtarget.oracle.com  Linux x86-64   Succeeded       Succeeded            Succeeded       "
}

PLAY RECAP ******************************************************************************************************************************************************************************************************************************************************************************************************************
10.0.0.11                  : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
10.0.0.28                  : ok=3    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Now let’s verify our agent deployment. I’m going to login to the Oracle Enterprise Manager Console and I should be able to see the details of the deployment.

Oracle Enterprise Manager Agent Deployment Dashboard

Here you go! The OMA was successfully deployed to my target host. I now need to manually execute the “root.sh” script, this because I didn’t specify a privileged credential during the deployment.

Hope this helps to demonstrate the flexibility of Oracle’s EM and also as an example on how to integrate it’s functionality with DevOps tools like Ansible. This is not by any means the best way to deploy an agent for your particular environment but rather an example of how to integrate EM with Ansible.

Thanks,
Alfredo

Applying Oracle Enterprise Manager 13.4 RU 3

Oracle Enterprise Manager (EM) Release Update (RU) 3 for 13.4 version was released early this month.



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.4 can be deployed both on-premises or in the Oracle Cloud (OCI). Oracle Enterprise Manager 13.4 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.



For this example I’m going to be using the on-premises option. This means that Oracle EM 13.4 is also available for download as a VirtualBox image. This image comes as a single-instance EM deployment. Is worth to mention that this image is not recommended for anything but for testing.



I assigned 16 Gb of RAM and 2 virtual CPUs to this VirtualBox VM. Follow the instructions in the README file and start your environment. I also downloaded patch 31299359 from MOS Doc ID 2647078.1

The overall steps are outlined below:

  1. Download and unzip patch 31299359
  2. Verify and make sure you meet the pre-reqs
  3. Stop the Oracle Management Service
  4. Apply the RU patch
  5. Start the Oracle Mangement Service

As I previously mentioned, I downloaded and staged the patch in the Oracle EM VM. Now, let’s make sure we meet the pre-reqs.

This patch requires OPatch to be version 13.9.4.2.2 or higher and OMSPatcher version 13.9.4.0.0 or higher.

$ export ORACLE_HOME=/u01/OracleHomes/Middleware
$ export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/OMSPatcher/
$ cd /u01/OracleHomes/Middleware/
$ cd OMSPatcher/
$ ./omspatcher version
OMSPatcher Version: 13.8.0.0.3
OPlan Version: 12.2.0.1.16
OsysModel build: Thu Oct 25 18:18:12 PDT 2018

OMSPatcher succeeded.
$ cd ../OPatch/
$ ./opatch version
OPatch Version: 13.9.4.2.2

OPatch succeeded.

As you can see from above output, we need to upgrade our OMSPatcher utility. Follow the instructions from MOS Doc ID 2646080.1.

After that, you will be ready to start patching.

$ ./omspatcher version
OMSPatcher Version: 13.9.4.1.0
OPlan Version: 12.2.0.1.16
OsysModel build: Thu Oct 25 18:18:12 PDT 2018

OMSPatcher succeeded.

Let’s now go to the directory where we are staging our patch and execute the OMSPatcher analyze command.

$ cd /home/oracle/Downloads/31299359/
$ omspatcher apply -analyze -property_file /home/oracle/property
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


OMSPatcher version : 13.9.4.1.0
OUI version        : 13.9.4.0.0
Running from       : /u01/OracleHomes/Middleware
Log file location  : /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/opatch2020-06-08_11-54-31AM_1.log

OMSPatcher log file: /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/31299359/omspatcher_2020-06-08_11-54-40AM_analyze.log

Configuration Validation: Success

Running apply prerequisite checks for sub-patch(es) "31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567" and Oracle Home "/u01/OracleHomes/Middleware"...
Sub-patch(es) "31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567" are successfully analyzed for Oracle Home "/u01/OracleHomes/Middleware"


Complete Summary
================


All log file names referenced below can be accessed from the directory "/u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/2020-06-08_11-54-31AM_SystemPatch_31299359_1"

Prerequisites analysis summary:
-------------------------------

The following sub-patch(es) are applicable:

             Featureset                                                                                                   Sub-patches                                                                                                                                        Log file
             ----------                                                                                                   -----------                                                                                                                                        --------
  oracle.sysman.top.oms   31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567   31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567_opatch2020-06-08_11-54-39AM_1.log



Log file location: /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/31299359/omspatcher_2020-06-08_11-54-40AM_analyze.log

OMSPatcher succeeded.

After you validate that there are no conflicts. Proceed to shutdown the Oracle Management Service (OMS).

$ emctl stop oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down

And now let’s execute the OMSPatcher command to apply the RU patch.

$ omspatcher apply -property_file /home/oracle/property
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


OMSPatcher version : 13.9.4.1.0
OUI version        : 13.9.4.0.0
Running from       : /u01/OracleHomes/Middleware
Log file location  : /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/opatch2020-06-08_14-45-59PM_1.log

OMSPatcher log file: /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/31299359/omspatcher_2020-06-08_14-46-08PM_deploy.log                                                                                                                 

Configuration Validation: Success

Running apply prerequisite checks for sub-patch(es) "31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567" and Oracle Home "/u01/OracleHomes/Middleware"...                           
Sub-patch(es) "31187620,31187594,31140458,30851102,31189032,31187677,31193101,31187644,31187611,30851078,31187552,31187567" are successfully analyzed for Oracle Home "/u01/OracleHomes/Middleware" 
                         
...

Deployment summary:
-------------------

The following artifact(s) have been successfully deployed:

               Artifacts                                                      Log file
               ---------                                                      --------
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-06-36PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-11-28PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-12-13PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-13-02PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-13-35PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-14-08PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-14-47PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-15-21PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-15-57PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-16-44PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-17-33PM.log
                     SQL       rcu_applypatch_original_patch_2020-06-08_15-18-24PM.log
            MRS-commands             emctl_register_commands_2020-06-08_15-19-14PM.log
          MRS-procedures           emctl_register_procedures_2020-06-08_15-19-24PM.log
          MRS-procedures           emctl_register_procedures_2020-06-08_15-19-37PM.log
               MRS-swlib                emctl_register_swlib_2020-06-08_15-19-52PM.log
               MRS-swlib                emctl_register_swlib_2020-06-08_15-20-12PM.log
          MRS-targetType           emctl_register_targetType_2020-06-08_15-20-43PM.log
          MRS-targetType           emctl_register_targetType_2020-06-08_15-21-23PM.log
          MRS-targetType           emctl_register_targetType_2020-06-08_15-21-44PM.log
          MRS-targetType           emctl_register_targetType_2020-06-08_15-23-36PM.log
          MRS-targetType           emctl_register_targetType_2020-06-08_15-29-31PM.log
     MRS-storeTargetType      emctl_register_storeTargetType_2020-06-08_15-29-48PM.log
     MRS-storeTargetType      emctl_register_storeTargetType_2020-06-08_15-30-00PM.log
     MRS-storeTargetType      emctl_register_storeTargetType_2020-06-08_15-30-11PM.log
     MRS-storeTargetType      emctl_register_storeTargetType_2020-06-08_15-30-31PM.log
     MRS-storeTargetType      emctl_register_storeTargetType_2020-06-08_15-30-48PM.log
  MRS-default_collection   emctl_register_default_collection_2020-06-08_15-31-00PM.log
  MRS-default_collection   emctl_register_default_collection_2020-06-08_15-31-23PM.log
  MRS-default_collection   emctl_register_default_collection_2020-06-08_15-31-38PM.log
  MRS-default_collection   emctl_register_default_collection_2020-06-08_15-32-48PM.log
  MRS-default_collection   emctl_register_default_collection_2020-06-08_15-33-59PM.log
      MRS-omsPropertyDef       emctl_register_omsPropertyDef_2020-06-08_15-34-14PM.log
      MRS-omsPropertyDef       emctl_register_omsPropertyDef_2020-06-08_15-34-23PM.log
            MRS-jobTypes             emctl_register_jobTypes_2020-06-08_15-34-35PM.log
            MRS-jobTypes             emctl_register_jobTypes_2020-06-08_15-34-49PM.log
       MRS-systemStencil        emctl_register_systemStencil_2020-06-08_15-35-02PM.log
       MRS-systemStencil        emctl_register_systemStencil_2020-06-08_15-35-09PM.log
       MRS-systemStencil        emctl_register_systemStencil_2020-06-08_15-35-18PM.log
        MRS-gccompliance         emctl_register_gccompliance_2020-06-08_15-35-26PM.log
       MRS-derivedAssocs        emctl_register_derivedAssocs_2020-06-08_15-36-05PM.log


Log file location: /u01/OracleHomes/Middleware/cfgtoollogs/omspatcher/31299359/omspatcher_2020-06-08_14-46-08PM_deploy.log

OMSPatcher succeeded.

After the RU patch was successfully applied you’ll need to start the OMS back up.

$ emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

We have now our OMS running 13.4 RU 3 version. But we are not done yet. There’s one important step that we need to perform and is to apply the RU 3 to our agents as well.

In order to do this we need to download patch 31270875 from MOS. All the bug remediation information about this patch is outlined in Doc ID 2657051.1

Note: You don’t have to upgrade the OPatch utility for the agent as is already running the required version (13.9.4.2.2)

$ export ORACLE_HOME=/u01/OracleHomes/agent/agent_13.4.0.0.0
$ cd /u01/OracleHomes/agent/agent_13.4.0.0.0/OPatch
$ ./opatch lsinventory
Oracle Interim Patch Installer version 13.9.4.2.2
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/OracleHomes/agent/agent_13.4.0.0.0
Central Inventory : /u01/app/oraInventory
   from           : /u01/OracleHomes/agent/agent_13.4.0.0.0/oraInst.loc
OPatch version    : 13.9.4.2.2
OUI version       : 13.9.4.0.0
Log file location : /u01/OracleHomes/agent/agent_13.4.0.0.0/cfgtoollogs/opatch/opatch2020-06-09_11-27-05AM_1.log


OPatch detects the Middleware Home as "/u01/OracleHomes/agent/agent_13.4.0.0.0"

Lsinventory Output file location : /u01/OracleHomes/agent/agent_13.4.0.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-06-09_11-27-05AM.txt

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


Interim patches (1) :

Patch  29812738     : applied on Wed Mar 18 12:51:59 EDT 2020
Unique Patch ID:  -1558519023
   Created on 22 May 2019, 02:57:03 hrs PST8PDT
   Bugs fixed:
     20141119, 27548560, 19030178, 24011099, 25423296, 21344996, 19154304



--------------------------------------------------------------------------------

OPatch succeeded.

Now is time to stop the agent and apply the RU patch.

$ cd /home/oracle/Downloads/31270875/
$ ls
30989011  31270875  README.html  README.txt
$ $ORACLE_HOME/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
$ $ORACLE_HOME/OPatch/opatch napply
Oracle Interim Patch Installer version 13.9.4.2.2
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/OracleHomes/agent/agent_13.4.0.0.0
Central Inventory : /u01/app/oraInventory
   from           : /u01/OracleHomes/agent/agent_13.4.0.0.0/oraInst.loc
OPatch version    : 13.9.4.2.2
OUI version       : 13.9.4.0.0
Log file location : /u01/OracleHomes/agent/agent_13.4.0.0.0/cfgtoollogs/opatch/opatch2020-06-09_11-34-58AM_1.log


OPatch detects the Middleware Home as "/u01/OracleHomes/agent/agent_13.4.0.0.0"

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

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '30989011' to OH '/u01/OracleHomes/agent/agent_13.4.0.0.0'

Patching component oracle.sysman.agent.ic, 13.4.0.0.0...
Applying interim patch '31270875' to OH '/u01/OracleHomes/agent/agent_13.4.0.0.0'

Patching component oracle.sysman.top.agent, 13.4.0.0.0...
Patches 30989011,31270875 successfully applied.
Log file location: /u01/OracleHomes/agent/agent_13.4.0.0.0/cfgtoollogs/opatch/opatch2020-06-09_11-34-58AM_1.log

OPatch succeeded.
$ $ORACLE_HOME/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Starting agent .................................... started.

The process is quite simple. Of course, I’m doing this in a test environment but you should do it too before attempting this in a production environment. For your production environment you want to make sure to have proper recovery settings in place and Oracle Management Repository backups.

Hope this helps to understand the patching process.

Happy patching!
Alfredo

Oracle Enterprise Manager Performance Hub

Oracle Enterprise Manager (EM) 13.4 RU 2 has just been released this month. You may noticed that starting from EM 13.3 PG, the Top Activity page has been decommissioned. The Performance Hub page is the one that is available and yes, is not using Flash. If you have concerns about no longer having access to your EM Flash based graphs once the Web browsers stop supporting it, then your best bet is to upgrade your EM system.



Let’s talk about the features available on the Performance Hub page.

First of all, keep in mind that this page requires the Database Diagnostics and Tuning packs. All the data used by this page is provided thanks to Oracle’s instrumentation and stored in the Automatic Workload Repository (AWR). In order to open the Performance Hub page, from the Database Home page navigate to Performance -> Performance Hub -> ASH Analytics.

Oracle Enterprise Manager Performance Menu
ASH Analytics Home Page

I’m dividing the ASH Analytics Home page into 5 main sections:

  1. Performance Hub main timeline (red)
  2. Tab selection between ASH Analytics and SQL Monitoring (blue)
  3. Average Active Sessions graph (yellow)
  4. SQL ID breakdown information (purple)
  5. User Session breakdown information (green)

The Performance Hub main timeline shows the current database performance divided in three classes; CPU utilization, User I/O and Wait. You may try to relate this to the old Top Activity graph, this one has additional functionality though.

Using ASH analytics you can customize your performance analysis by adjusting the timeline selector. Remember that in the Top Activity page this was fixed to 5 minutes.

Customize Your Performance Analysis

This becomes real handy when you try to isolate performance issues in the database. Once you select the desired timeline for you analysis, the bottom of the page will update based on you selection. Only data data falls under you selection will be displayed in sections 3,4 and 5.

On top of this, we still have the classic time related selectors and page refresh rate.

Page Refresh Rate – Time Selector

Using the tab selector you can quickly switch between ASH Analytics and SQL Monitoring.

Tab Selector

If you click on SQL Monitoring, sections 3, 4 and 5 of the ASH analytics page will be replaced with the SQL Monitoring session information table. Keep in mind that the information showed in the table will be limited by the timeline selector that we already discussed. Adjust you selection accordingly.

SQL Monitoring

Let’s now move to the Average Active Sessions section. This section displays detailed information of the current active sessions in the database during the selected period of time. You have the flexibility to adjust the graph by changing the graph’s dimensions. By default the graph will use the Wait Class a the main dimension.

Average Active Sessions

Apart of that you can also adjust the Maximum CPU limit line to show the limit based on the number of CPU’s, the number of CPU cores or you may decide to not show the limit at all.

Maximum CPU Limit Line

Another option is to only show foreground sessions (default) or to also include background sessions. You can even choose on whether show the standard load graph or to switch into a heat map like chart and select as many dimensions as needed.

Average Active Sessions Heat Map

The bottom of the page will display two tables. The one on the left side will show all the SQL information and the one on the right side the session information. You should be pretty familiar with these as the old Top Activity page was showing them as well.

You can also customize the view of these two tables and adjust the dimension. By default is set to the Wait Class.

SQL ID Dimension Selector

Last but not least is the ability to select a SQL ID from the table and either call SQL Tuning advisor or to create a SQL Tuning Set from the same page.

SQL Tuning Advisor / SQL Tuning Set Buttons

As you can see, this is not the old Top Activity page that we were used to, but definitively has more features and offers way more flexibility while working on monitoring or when trying to spot performance issues in the database.

Thanks,
Alfredo

Using SPA To Validate Changes

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.

Using SPA to validate a SQL Profile

Thanks,
Alfredo

Upgrade EM to 13.4

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:

  • Oracle Enterprise Manager 13.PG (single OMS)
  • Oracle Database 12.1.0.2 (OMR)
  • Oracle Linux 7 64-bit

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:38

I’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 successfully

Startup 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> exit

Execute 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.bin

I’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:

  • Copy the emkey to repos
$ ./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".
  • Shutdown the EM agent
$ cd $AGENT_HOME/bin
$ ./emctl stop agent
  • Shutdown OMS
$ 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

Do You Trust SQL Tuning Advisor?

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.

ASH Analytics page

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.

SQL Tuning Sets Menu

From the STS dashboard I’m going to to click on “Create” to launch the wizard.

SQL tuning Sets Dashboard

Step 1 requires a name and owner for the STS.


Note: You can create an empty STS and populate it later.


Create STS – Options

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.

Load SQL’s

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.

Filter SQL’s

The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.

Schedule STS Creation

And finally the review page. If everything looks right, then click on “Submit“.

STS Creating Review

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.

Review SQL Tuning Sets

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.

SQL Tuning Advisor Results

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%.

SQL Tuning Advisor Results Detail

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.

That’s actually me fingers crossed hoping everything works!!!

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.

Validate SQL Profiles with SPA

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.


SPA Report

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