Author: alfredokrieg

Oracle EM Agent Preferred Connect Descriptor

The USE_SID_AS_SERVICE_LISTENER=ON saga

If you are migrating to multi-tenant architecture of the Oracle database, you’ll find out that in order to connect to the pluggable database (PDB) you must use the service name for the PDB. But, what if you have a legacy application that can only use SID instead of the service name?

For that, you can enable the parameter USE_SID_AS_SERVICE_LISTENER in the listener configuration, so the service name will act as an SID.

Problem solved, right? Well, the problem is now with applications that require the service name and not the SID. One of them is Oracle Enterprise Manager (EM). If you turn on this parameter at the listener level then, you won’t be able to connect to the databases and they will show down (red) in the EM console.

So, what can we do? In EM 13.5 RU16 or higher there’s functionality that allows EM to connect to the database by using an Agent preferred connect descriptor. This way, you can provide the connect descriptor using the service name.

One important note to mention is that you must have RU16 or higher applied to both, the OMS and the Agents in order to enable this functionality.

Follow the My Oracle Support Note “EM 13c: How To Enable Agent Preferred Connect Descriptor Support For RAC Instance, Single Instance DB (Doc ID 2963079.1) ” to enable this functionality. You will have to restart your OMS, so be prepared.

After you enable this setting, navigate to the Monitoring Configuration of your target and look for the new Agent preferred descriptor section. It looks like the image below.

Add the proper connect descriptor and viola! EM is now able to connect to your target.

Is always recommended to turn the USE_SID_AS_SERVICE_LISTENER parameter off after migrations and upgrades. Sometimes this is not an option if you have legacy applications though. This feature allows you connect your EM when the parameter USE_SID_AS_SERVICE_LISTENER must be turned on.

Thanks,
Alfredo

Monitor Oracle DBs in RDS With Enterprise Manager

In this blog I want to provide some important steps if you want to monitor Oracle databases running on AWS RDS.

The first step is to have an Oracle Enterprise Manager (EM) installation. This can be on-prem or running on EC2. If you need to setup EM in EC2 you can use one of the pre-built images that already contains an Oracle database. You are going to need this database for the EM repository.
One quick example can be the Oracle database 19c image by cloudimg running on Oracle Linux. Just make sure you add enough space in the filesystem so you can also install the EM software.

Once the EC2 instance is up and running, go ahead and patch the Oracle database with the latest RU (RU28 as of today).

Download the Oracle EM 13.5 software from oracle.com and also download the latest RU available (RU27 as of today).

Install EM 13.5 and patch it with RU27. Follow below videos if you need additional guidance on how to install and patch EM.

Install EM…

Patch em …

Next we are going to follow the instructions that RDS has available for the EM agent installation. Remember that because there’s no actual access to the RDS host, we can’t install the EM agent using the traditional way.

click here…

Verify all the network pre-reqs so your RDS instance can actually reach your EM installation running on EC2. If you EM is running on-prem then a VPN setup will be required.

Now is time to create an option group once all the network pre-reqs are met. The option group is how we actually ask the RDS console to provide us the option to deploy an EM agent. The option for the option group needs to be OEM_AGENT.

Before you deploy the option group to your RDS instance, you will have to provide information about your EM instance, including:

  • EM Host Name
  • EM upload port
  • EM registration key

Then deploy the option group to your RDS instance.

Once the deployment finishes, you will actually have the EM agent up and running in your RDS instance and showing up in the EM console.

The next step is to discover your DB instance and the listener. Follow below link for the documentation.

click here…

Hope this set of steps help you with the monitoring setup of your RDS database with Oracle Enterprise Manager.

Thanks,
Alfredo

Upgrade Enterprise Manager to 24ai

In this post I want to share my experience while executing the upgrade of Oracle Enterprise Manager from 13.5 to 24ai.

As always. Please go ahead and read the Oracle documentation on how to upgrade EM to 24ai.

click here…

My EM demo environment was running EM 13.5 RU21 and the EM repository database 19c RU18. As per of the EM pre-reqs you need to be at least on EM13.5 RU22 and the DB needs to have the latest RU available.

So before even trying to upgrade, I needed to patch my EM. I decided to skip the patch but the UI is really good at reminding it.

So, I patched my EM to RU25 and the EM repository DB to RU26.

Let me execute the 24ai installer and follow the instructions.

Here I decided to upgrade my 13.5 environment instead of creating a new one.
There are 2 options though. Upgrade end-to-end or just install the binaries and proceed with the upgrade later.

NOTE: I chose end-to-end first, but ended going back and selecting the second option. The wizard gives a failure in the pre-reqs as it knows that RU 1 is already available for 24ai and you must install it before attempting the upgrade. I’ll share a MOS note on how to deploy it in subsequent steps.

I skipped the software updates.

Then the UI ran the pre-checks.

The pre-checks failed due the fact that 24ai RU1 needed to be applied.

I had to use below MOS note in order to apply RU1 with the bitonly option.

13.5: How to Apply Release Update on the OMS During the Install/Upgrade (Doc ID 2810169.1)

Select your new 24ai home location.

Type the EM repository connection details.

I performed to upgrade using the SYS DB account.

I had an issue with Plug-ins. My environment had 2 plug-ins deployed that are no longer supported in 24ai. I had to use EMCLI commands to un-deploy them.

click here…

After un-deploying the, I was able to continue.

I just proceed with the upgrade.

Now it’s time to run the ConfigureGC.sh script.

It asks for the system to be upgraded.

Type the EM repository connection details once again.

Select the DB user for the upgrade. In my case SYS was fine.

All the pre-reqs were successful. I selected the default EM ports and proceeded with the upgrade.

NOTE: The wizard will also upgrade the EM central agent. In previous EM upgrades that was a manual step.

It took about an hour for the wizard to complete.

Don’t forget to run the root.sh script on your new agent. I had an issue where EM was throwing a weird error while connecting to DBs.

Failed to ascii decode encrypted-text (status=1) After EM Upgrade (Doc ID 2655512.1)

After that, EM 24ai RU1 is up and running.

Hope that you find this post useful.

Thanks,
Alfredo

Oracle Enterprise Manager 24AI – New Features

Oracle Enterprise Manager 24AI was announced on December 2024 during the Oracle Enterprise Manager Technology Forum 2024. If you would like to see all the Forum’s recordings please register below.

click here…

In this post, I want to provide a quick overview of the most interesting new features of EM 24AI to you.

Oracle EM 24AI provides a set of new features based on 4 focus areas:

  • Platform Modernization
  • Operational Continuity
  • AI Insights
  • Performance and Automation

I’m going to share only the features available as of today with the first release of EM 24AI.

Platform Modernization

  • New Navigation Menu allows you to easily search and access monitoring and administration options from anywhere.
  • Dashboard Enhancements to select the duration of time for which you want to display data.
  • Target Monitoring Using Remote Agents without requiring an agent to be installed on the same host as the targets.
  • Support for Oracle Key Vault and secrets management appliance to store, manage and share security objects and encryption keys.
  • EM pages enhancements using JET to easily access features with a re-organized menu, enabling direct navigation to each component.
  • Data Masking and Subsetting enhancements with a unified console experience.

Operational Continuity

  • Zero Downtime Monitoring that process and handles incident and notification capabilities even during planned maintenance.
  • New Job System Console which provided deeper information regarding your jobs and job system.
  • REST APIs enhancements that include the ability to control target blackouts.

Performance and Automation

  • New swim lanes visualization in ADDM Spotlight that use time-series data aggregated to display findings by overall impact for each ADDM task.
  • New Metrics to Monitor Raft-based Sharding for 23c and later.
  • New DBSAT 3.1 Standards.
  • New SCAP Standards for Oracle Linux 7,8 and 9.
  • Redesigned Plug-Ins for Non-Oracle targets including Microsoft IIS, JBoss EPA, Apache Tomcat and IBM Websphere.

AI Insights features will be coming in future release updates. If you want to learn more about this new release of Oracle Enterprise Manager please click below.

click here…

I’ll cover the upgrade process from Enterprise Manager 13.5 to 23AI in the next blog post.

Thanks,
Alfredo

Extract OCI Resource Status Using APIs

In this blog, I want to show you a way to extract every resource in a compartment along with their status using a Python script and OCI APIs.

There are several pre-reqs that you need to fulfil in order to be able to connect using APIs to OCI. Follow the same pre-reqs as in my previous blog about Metric Extensions.

Step 1 – Prerequisites

click here…

Once you have created an OCI user, setup API Keys and setup your Python host; then, go ahead and create a Python script similar to the one below.

#!/usr/bin/python3

# This is a sample python script that searches resources in a compartment
# Run this script on the client that you want to monitor.
# Command: python script_name.py

import oci,subprocess,os,datetime,json
from pytz import timezone

# using default configuration file (~/.oci/config)
from oci.config import from_file
config = from_file()

# initialize service client with default config file

search_client = oci.resource_search.ResourceSearchClient(config)
query = f"query all resources where compartmentId = '<compartment OCID>' && lifeCycleState != 'AVAILABLE' && lifeCycleState != 'ACTIVE' && lifeCycleState != 'Assigned' && lifeCycleState != 'Running' && lifeCycleState != 'Succeeded' && lifeCycleState != 'Deleted'"
search_response = search_client.search_resources(
    search_details=oci.resource_search.models.StructuredSearchDetails(
        type="Structured",
        query=query,
    ),
    limit=1000,
)
print(f"Compartment has {len(search_response.data.items)} resources")

json_format = json.loads(str(search_response.data.items))

# Iterate through the JSON array
for item in json_format:
    print(item["display_name"], item["lifecycle_state"])

As you can see from the script. We are using OCI Search in order to get all resources in a compartment (<compartment OCID>) and also we all filtering the search to only show resources that are either down, terminated or failed.

Once you get the list of resources in your compartment you can create a text/csv file and share the list with all the interested parties.

Another option is the ability to combine this with the Monitoring Service and create a Metric Extension (ME). The metric extension will hold the list of resources that have that specific lifecycle_state. Once the date is contained in the ME you can send notifications or create a dashboard showing these resources.

Thanks,
Alfredo

Send OCI Alerts Notifications To 3rd Party Systems (Object Storage)

Hi there!

In this blog, I want to show how you can send an OCI Alert Notification to a 3rd party system. The way I describe this tutorial is by sending the notification to an OCI Storage bucket so you can ingest it using the tool of preference.

First of all I would describe the services used:

  • OCI Monitoring Service
  • OCI Streams Service
  • OCI Connector Hub
  • OCI Storage Service

Let’s go an create a Storage bucket in order to hold these notifications.
Create a Storage bucket

Once you have the Storage bucket ready, go ahead and create a Stream pool and a Stream.
Create a Stream Pool

Don’t forget to create the Stream.

Now is time to create an alarm and send the notification to the Stream we just created.
Create an Alarm

During the alarm creation, be sure to select the Stream as a destination.

At this point every single time the alarm generates a notification, it will be send to the Streaming service. Now we need to send this notification from the Streaming service to our Storage bucket. We need to create a Connector Hub for that.
Create a Connector Hub

The Connector Hub configuration will look like below.

After this, the notification that was stored in the Streaming service will be moved to your Storage bucket.

Keep in mind that you need to set several policies for this to work.
Connector Hub policies

Another important consideration is the fact that the Streaming service encrypts the data at rest. So when you open one of those files inside your Storage bucket, you will find them encrypted. You could decrypt them using base64 functions.

Thanks,
Alfredo

Monitor MySQL HeatWave with OCI Database Management

In this post I want to show you how you can monitor your MySQL Heatwave databases with OCI Database Management Cloud Service. Is worth to mention that this Cloud Service provides this functionality free of cost and is automatically enabled for below configurations:

  • Standalone
  • High Availability

Let’s start by creating a new Heatwave instance.

You will notice that Database Management is automatically enabled.

The Database Management service will then publish several metrics related to your Heatwave instance.

You can of course use the alarms and notification services to setup alerts on important metrics.

Last but not least navigate to the Performance Hub page to find out real time performance information for the queries inside your Heatwave instance.

If you want to learn more about this service follow the link below.

click here…

Thanks,
Alfredo

Integrate EM Metrics With Third-Party Tools Using REST APIs

I want to share how to use Enterprise Manager’s (EM) REST APIs to query metric data and integrate with 3rd party tools.

First of all, always follow the documentation. Below is the link to the EM’s REST API functionality.

click here…

Using the EM’s REST API functionality we can query the EM repository using a web service. Let’s follow below steps.

a) Create a new EM user who is going to have the privileges to query the EM’s repository. Navigate to Setup -> Security -> Administrators.

Administrators navigation

b) Create a new Repository User.

Administrator creation

c) I’m going to create a new Administrator named ABC.

Administrator creation

d) Create a new Named Credential. Navigate to Setup -> Security -> Named Credentials.

Named Credential creation

e) Click on Create.

Named Credential creation

f) I’m going to create NC_ABC Named Credential.

Named Credential creation

g) Enable the repository REST APIs in EM. For that execute below emctl command.

emctl set property -name oracle.sysman.db.restfulapi.executesql.repository.query.enable -value true -sysman_pwd "sysman"

Now is time to test our REST APIs.

I’m going to test them out using 2 different mechanisms. For the first one, let’s use CURL.

$ curl -k -u ABC -H "Content-Type: application/json" -d '{"sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "credential": {"DBCredsMonitoring":"NC_ABC"}, "maxRowLimit": 10, "maxColumnLimit": 2}' https://<EM_Host>:<EM Port>/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/query/v1

Here are the results based on my demo environment:

REST API results from CURL

The second test is by using Ansible. If you are not familiar on how to configure Ansible in order to work with EM, please follow the Oracle Live Labs that we created about this.

CLICK HERE…

I’m going to create a new YAML file (test.yml) in order to test the REST API.

---
- name: Using a REST API
  become: false
  hosts: emserver
  gather_facts: false
  tasks:

    - name: Query Metric Settings
      uri:
        url: https://emcc.marketplace.com:7803/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/query/v1
        method: POST
        return_content: yes
        force_basic_auth: yes
        validate_certs: no
        body_format: json
        url_username: ABC
        url_password: *******
        headers:
           Content-Type: application/json
        body:
          {
            "sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "maxRowLimit": 2, "maxColumnLimit": 29
          }
      register: results

    - name: Print returned json dictionary
      debug:
        var: results.json

Let’s execute this using Ansible.

ansible-playbook /home/oracle/ansible/yml/test.yml -u oracle --private-key=~/.ssh/dtkey.ssh

Here the results:

REST API results from Ansible

With this you can get the desired results from the EM’s repository using this REST API.

Thanks,
Alfredo

Extend Your Oracle Monitoring With Configuration Extensions

As soon as you enable the Database Lifecycle Management Pack; Oracle Enterprise Manager will automatically collect hundreds of configuration items of your database estate. However if you need to customize this collection, Configuration Extensions provide a way to identify files and other configuration data that Cloud Control does not already collect.

In this blog, I’ll show you how I created a Configuration Extension (CE) in order to collect DB Services data.

CE’s are found under the Enterprise -> Configuration menus.

While in the CE dashboard, you can create, edit, delete and deploy CE’s using the available menu.

Let’s click the “Create” button.

Using the CE create wizard type a name for the CE, a target type and a Sample Target. As soon as you select “Database Instance” for the Target Type a new option will enable. This option is to select between “Files & Commands” or “SQL”.

In this example I’m going to choose “SQL” and then type the required SQL required to retrieve the data I need from the DB. Let’s type “select name as service_name from dba_services”. Also type “service_name” in the alias section and choose the “Database Query Parser”.

The entries should look similar to the screenshot above.

The data extracted from the DB in the CE is in XML format. We need to add parser rules in order to match and identify nodes in the parsed tree. Click on the number of “Rules” in the table. In this case click on the number “0”.

On the Parser Rule page click “Add” and type this condition and expression:

  • Condition: /root/row
  • Expression: SERVICE_NAME/text()

This will allow the parser to use the text inside the SERVICE_NAME node as the name of the row. Click the return button.

Now we can click on the “Preview” button from the menu.

It will show you the preview output. Similar like this:

Click “OK” and then click “Save”.

Select your newly created CE and click on “Manage Deployments” button. Select the targets where you want to deploy the CEs and click “Apply”.

It will look similar to the screen below:

Navigate to the configuration of your target and verify the CE is correctly pulling the desired configuration data.

Now we have our CE correctly gathering the desired configuration data of our DB.

You can find the documentation for Configuration Extensions below.

click here…

Hope this helps to provide some clarity on how to create Configuration Extensions in Oracle Enterprise Manager.

Thanks,
Alfredo

How To Discover ExaCC/ExaCS in Enterprise Manager – Detailed

In this post I want to share the required steps to discover ExaCC or ExaCS in Oracle Enterprise Manager (EM). I’ll provide as much detail as possible.

  1. Make sure your EM is at least on 13.5 RU 16. If not, please apply RU 16 to EM. Follow this link if you want to know how to patch EM. HERE!
  2. You need to designate a “monitoring” agent for the discovery. Is recommended that this agent sits outside your Exadata rack and has access to the OCI REST APIs. More information HERE!
  3. Make sure this agent has both the Database and the Exadata plugins deployed and is patched to the same RU level as the OMS. HERE!
  4. The agent must be able to reach the OCI REST APIs. There are 3 ways to achieve this.
    a) You can use a Proxy
    b) You can use the OCI Management Gateway
    c) You can have direct network connection

    Below is a list of APIs that you will need access to:
    Either grant access to (*.oci.oraclecloud.com) or individual URLS:
    https://query.<oci_region>.oci.oraclecloud.com
    https://identity.<oci_region>.oci.oraclecloud.com
    https://database.<oci_region>.oci.oraclecloud.com
    https://wss.exacc.<oci_region>.oci.oraclecloud.com
    https://management-agent.<oci_region>.oci.oraclecloud.com
    https://certificatesmanagement.<oci_region>.oci.oraclecloud.com
    https://certificates.<oci_region>.oci.oraclecloud.com
    https://telemetry-ingestion.<oci_region>.oci.oraclecloud.com
    https://auth.<oci_region>.oci.oraclecloud.com
    https://objectstorage.<oci_region>.oci.oraclecloud.com

    You may also test that connectivity by executing:
    $ curl -v https://query.<oci_region>.oci.oraclecloud.com
  5. Create or use an OCI account that has access to read your Exadata racks. These are the policies I have used:
    allow group <domain/group name> to read database-family in compartment <compartment name>
    You can find more information about the required policies HERE!
  6. Setup API Keys for authentication. Please follow instructions in the MOS note below:
    EM13.5: Manage OCI Connectivity Named Credentials Test Failed with Invalid Private Key. (Doc ID 2792126.1)
  7. Create an EM Named Credential using the API Keys created on the previous step. More details HERE!
  8. You also need the proper Storage Server credentials. If you don specify them during the discovery process, Storage Server targets will not be discovered. Instructions on how to retrieve this credential can be found HERE!
    If you can’t retrieve these credentials, please open a Service Request with support.
  9. Discover your Exadata Infrastructure following the discovery wizard. More information HERE!

After finishing the discovery, you should have your Exadata Cloud target in EM. Please wait between 15 to 20 minutes for the target information to be populated.

If your network setup does not allow direct connectivity to the OCI REST APIs then you will have to use an internal Proxy or use the OCI Management Gateway. More information about the OCI Management Gateway can be found HERE!

There’s also a very good post from Simone about setting up the Management Gateway for the EM agents.
https://blogs.oracle.com/observability/post/setup-oem-agents-cloud-mgmt-gw

Update:
You will also have to modify 2 parameters. One at the OMS level and one at the agent level. Please follow below MOS notes:

OEM 13c : SSH Key Credential Test On a Host Target Fails With “Remote operation timed out.” (Doc ID 2415262.1)
EM 13.2: Agent Patching From EM Console Fails With Error “concurrent job tasks limit (50) has been reached” (Doc ID 2476803.1)

Thanks,
Alfredo