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

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

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.

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.

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.

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.

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.

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

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.

Important note: This procedure is available for 12.1.0.x and prior versions. If you want to patch 12.2, 18c and 19c versions then you must use Fleet Maintenance feature.

Patching the Oracle Database 12.2 using Enterprise Manager Requires the EM DB Plugin 13.2.2 and Fleet Maintenance (Doc ID 2237521.1)

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