Oracle Enterprise Manager - alfredokriegdba.com

Creating An Oracle Database Home Gold Image

In today’s post I want to show how to create an Oracle database home gold image (GI). I will cover the steps I usually follow, the checks I perform and the options to create the GI and finally automate it.

I’ll start by stating that I’m using an old database Oracle home for this example. This home is from a DB running 19c RU 17. The idea is to patch it to RU32 (July 2026) and apply the September MRP (September CSPU).

Download the latest OPatch and the July 2026 RU patches. Stage them and upgrade OPatch. At the time of this post OPatch version 12.2.0.1.53 is the latest available for 19c. Let’s verify if it can work with July 2026 RU.

$ $ORACLE_HOME/OPatch/opatch prereq CheckMinimumOPatchVersion -ph /home/oracle/39472050/
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-21-36PM_1.log

Invoking prereq "checkminimumopatchversion"
Prereq "checkMinimumOPatchVersion" passed.
OPatch succeeded.

Let’s now check for conflicts.

$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/39472050/
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-22-14PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

At this point I’m ready to apply July 2026 RU to this OH. I’m going to shutdown all instances running, listeners and then proceed to apply the RU.

$ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-25-07PM_1.log

Verifying environment and performing prerequisite checks...
-----------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-26-04PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   39472050
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/db/19c/sales')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '39472050' to OH '/u01/db/19c/sales'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.datamining, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.java.sqlj.sqljruntime, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.util, 19.0.0.0.0...
Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
Patching component oracle.assistants.acf, 19.0.0.0.0...
Patching component oracle.assistants.deconfig, 19.0.0.0.0...
...
Patching component oracle.jdk, 1.8.0.201.0...
Patch 39472050 successfully applied.
Sub-set patch [34419443] has become inactive due to the application of a super-set patch [39472050].
Sub-set patch [31222103] has become inactive due to the application of a super-set patch [39472050].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-26-04PM_1.log

OPatch succeeded.

After this I’m going to verify all the log files and look for any errors or warnings. I’ll then start the DB and the listener if the installation is successful.

Let’s now do a sanity check for datapatch.

$ ./datapatch -sanity_checks
SQL Patching sanity checks version 19.32.0.0.0 on Fri 09 Oct 2026 06:39:50 PM GMT
Copyright (c) 2021, 2026, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/19c/cfgtoollogs/sqlpatch/sanity_checks_20261009_183950_24361/sanity_checks_20261009_183950_24361.log

Running checks
JSON report generated in /u01/app/19c/cfgtoollogs/sqlpatch/sanity_checks_20261009_183950_24361/sqlpatch_sanity_checks_summary.json file
Checks completed. Printing report:

Check: Database component status - INFO
  There should be no INVALID components prior patching due to risk of potential errors.
  Listed components have a non-valid status.
  For INVALID components: Run '?/rdbms/admin/utlrp.sql' (per container) to try recompiling invalid objects and restore component status.
  For other non-valid status, depending on patch content warnings may be returned.
...
Check: GoldenGate triggers - OK
Check: Logminer DDL triggers - OK
Check: Check sys public grants - OK
Check: Statistics gathering running - OK
Check: Optim dictionary upgrade parameter - OK
Check: Symlinks on oracle home path - OK
Check: Central Inventory - OK
Check: Java Virtual Machine Enable - OK
Check: Oracle Database Vault Enabled - OK
Check: Queryable Inventory database directories - OK
Check: Queryable Inventory locks - OK
Check: Queryable Inventory package - OK
Check: Queryable Inventory external table - OK
Check: Imperva processes - OK
Check: Guardium processes - OK
Check: Locale - OK

Refer to MOS Note 2975965.1 and debug log
/u01/app/19c/cfgtoollogs/sqlpatch/sanity_checks_20261009_183950_24361/sanity_checks_debug_20261009_183950_24361.log

SQL Patching sanity checks completed on Fri 09 Oct 2026 06:40:00 PM GMT

Verify the logs to look for issues or warnings. Execute datapatch if no errors found.

$ ./datapatch
SQL Patching tool version 19.32.0.0.0 Production on Fri Oct  9 18:40:43 2026
Copyright (c) 2012, 2026, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/19c/cfgtoollogs/sqlpatch/sqlpatch_25314_2026_10_09_18_40_43/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done
...
Installing patches...
Patch installation complete.  Total patches installed: 7
...
SQL Patching tool complete on Fri Oct  9 19:00:06 2026

Let’s verify logs to look for errors or warnings. At this point we can cleanup our OH. Let’s list any inactive patches and cleanup before continuing with the September MRP.

$ $ORACLE_HOME/OPatch/opatch util listorderedinactivepatches
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-51-27PM_1.log

Invoking utility "listorderedinactivepatches"
List Inactive patches option provided
The oracle home has the following inactive patch(es) and their respective overlay patches:

The number of RU chains is  2
***** There are 4 inactive RU patches in chain 1
-Inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 GMT 2019, with no overlays
-Inactive RU/BP 30869156:Database Release Update : 19.7.0.0.200414 (30869156), installed on: Wed May 13 17:09:52 GMT 2020, with no overlays
-Inactive RU/BP 34133642:Database Release Update : 19.16.0.0.220719 (34133642), installed on: Mon Apr 03 11:37:24 GMT 2023, with no overlays
-Inactive RU/BP 34419443:Database Release Update : 19.17.0.0.221018 (34419443), installed on: Mon Apr 03 12:41:37 GMT 2023, with overlays: 31222103
-Active RU/BP 39472050:Database Release Update : 19.32.0.0.260721 (39472050), installed on: Fri Oct 09 18:30:36 GMT 2026, with no overlays

***** There are 2 inactive RU patches in chain 2
-Inactive RU/BP 30805684:OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684), installed on: Wed May 13 17:13:27 GMT 2020, with no overlays
-Inactive RU/BP 34086870:OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870), installed on: Mon Apr 03 11:32:07 GMT 2023, with no overlays
-Active RU/BP 34411846:OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846), installed on: Mon Apr 03 12:37:43 GMT 2023, with no overlays

OPatch succeeded.

$ $ORACLE_HOME/OPatch/opatch util deleteinactivepatches
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_18-53-58PM_1.log

Invoking utility "deleteinactivepatches"
Inactive Patches Cleanup option provided
Delete Inactive Patches .......
***** There are 4 inactive RU patches in chain 1
***** 3 inactive patches will be deleted
-To be deleted inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 GMT 2019, with no overlays
-To be deleted inactive RU/BP 30869156:Database Release Update : 19.7.0.0.200414 (30869156), installed on: Wed May 13 17:09:52 GMT 2020, with no overlays
-To be deleted inactive RU/BP 34133642:Database Release Update : 19.16.0.0.220719 (34133642), installed on: Mon Apr 03 11:37:24 GMT 2023, with no overlays
-To be retained inactive RU/BP 34419443:Database Release Update : 19.17.0.0.221018 (34419443), installed on: Mon Apr 03 12:41:37 GMT 2023, with overlays: 31222103
-Active RU/BP 39472050:Database Release Update : 19.32.0.0.260721 (39472050), installed on: Fri Oct 09 18:30:36 GMT 2026, with no overlays
***** There are 2 inactive RU patches in chain 2
***** 1 inactive patches will be deleted
-To be deleted inactive RU/BP 30805684:OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684), installed on: Wed May 13 17:13:27 GMT 2020, with no overlays
-To be retained inactive RU/BP 34086870:OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870), installed on: Mon Apr 03 11:32:07 GMT 2023, with no overlays
-Active RU/BP 34411846:OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846), installed on: Mon Apr 03 12:37:43 GMT 2023, with no overlays
Do you want to proceed? [y|n]
y
User Responded with: Y
Deleted RU/BP patch: 29517242
Deleted RU/BP patch: 30869156
Deleted RU/BP patch: 34133642
Deleted RU/BP patch: 30805684

OPatch succeeded.

Let’s apply the September 2026 MRP on top of the July 2026 RU. Shutdown the instances and listener. Then verify for conflicts and apply the MRP.

$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /home/oracle/39971265/
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.
PREREQ session

Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_19-14-19PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

$ $ORACLE_HOME/OPatch/opatch napply .
Oracle Interim Patch Installer version 12.2.0.1.53
Copyright (c) 2026, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/db/19c/sales
Central Inventory : /u01/app/inventory
   from           : /u01/db/19c/sales/oraInst.loc
OPatch version    : 12.2.0.1.53
OUI version       : 12.2.0.7.0
Log file location : /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_19-15-44PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   39487170  39570655  39570736  39661089  39750798  39761039  39761126  39763853  39779336  39833225  39890510  39897693  39901922  39935553  39951966

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/db/19c/sales')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '39487170' to OH '/u01/db/19c/sales'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
...
Patches 39487170,39570655,39570736,39661089,39750798,39761039,39761126,39763853,39779336,39833225,39890510,39897693,39901922,39935553,39951966 successfully applied.
Log file location: /u01/db/19c/sales/cfgtoollogs/opatch/opatch2026-10-09_19-15-44PM_1.log

OPatch succeeded.

Verify the logs. Start the instances and listener. Then execute a datapatch sanity and a datapatch on the DB.

$ ./datapatch
SQL Patching tool version 19.32.0.0.0 Production on Fri Oct  9 19:31:20 2026
Copyright (c) 2012, 2026, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/19c/cfgtoollogs/sqlpatch/sqlpatch_22646_2026_10_09_19_31_20/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done
...
SQL Patching tool complete on Fri Oct  9 19:31:44 2026

Let’s create the gold image. We can use the standard runInstaller method or we can use an automation tool like Oracle Enterprise Manager Fleet Maintenance.

For the first gold image we are going to use the runInstaller method.

$ $ORACLE_HOME/runInstaller -silent  -createGoldImage -destinationLocation /home/oracle
Launching Oracle Database Setup Wizard...

Successfully Setup Software.
Gold Image location: /home/oracle/db_home_2026-10-09_07-01-14PM.zip

After this we already have an Oracle home gold image created that we can use for later deployment. Let’s also show an automated way to create and manage the gold images using Oracle Enterprise Manager (EM).

Using the Oracle Database Lifecycle Management pack, we can easily manage gold image using the console. If you navigate to the Database targets page and click on the Administrator menu and then select Fleet Maintenance Hub, you can access the Fleet Maintenance functionality.

Then navigate to Patch Recommendations for Images.

Enterprise Manager integrates with My Oracle Support (MOS) to identify recommended and missing patches for existing Gold Images, helping DBAs maintain current, secure, and consistent software baselines. Administrators can create and manage Gold Image versions, promote approved versions, and deploy them across subscribed targets using automated, out-of-place patching workflows. This approach reduces configuration drift, simplifies patch compliance tracking, minimizes manual effort, and enables organizations to adopt a more frequent and consistent security patching cadence.

MORE info…

This covered few options of creating and maintaining Oracle database home gold images.

Thanks,
Alfredo

EM Database Job Error After Upgrade to 24ai

Today I want to talk about an error that I haven’t seen before until I upgraded my demo Enterprise Manager environment to the latest 24ai version. I went through the standard process of upgrading the OMs and then upgraded the agents. Everything went fine expect that any jobs executed against a database or a host were failing with:

Failed to ascii decode encrypted-text (status=1)

Some jobs finished correctly, however that error was still present on the job log file. I came to know by using KB656298 – Failed to ascii decode encrypted-text (status=1) After EM Upgrade that I totally forgot to execute root.sh script after the upgrade.

One thing I need to mention is that some agents worked fine and some of them I needed to restart after executing root.sh.

The KB note doesn’t mention anything about the need of restarting the agent. Next time I need to make sure I execute all the needed steps after upgrading the agents.

Thanks,
Alfredo

Patch & Protect Your Oracle Database From AI-Enabled Threats

We hear more and more about the benefits of using AI models in terms of efficiency and speed innovation for the software industry. One thing that we also started hearing last month is that new models also offer new ways to exploit security vulnerabilities by bad actors.

Oracle Database Engineering is actively working with leading AI model providers in order to continuously discover vulnerabilities using advanced AI techniques to rapidly remediate and deliver fixes via the existing database release updates.

What does this mean to you?

Patch, patch and patch TODAY!
You must apply the quarterly release update (RU) promptly, this is RU31 for 19c (19.31) or RU2 for 26ai (23.26.2). If you are not in 19c or 26ai yet you must upgrade your database as soon as possible. Also make sure your client libraries are upgraded to 19c or 26ai.

Changes are also coming on the way and frequency Oracle delivers security patches. From the Accelerating Vulnerability Detection and Response blog:

Oracle is expanding how security fixes are delivered to customers with a monthly Critical Security Patch Update (CSPU), starting in May 2026. CSPUs provide targeted fixes for critical security issues, allowing customers to address high-priority vulnerabilities without waiting for the next quarterly release. Each CSPU is smaller and more focused, making it easier to apply critical fixes quickly. Quarterly Critical Patch Updates will continue to include all fixes released in prior CSPUs.

This approach enables customers to apply critical fixes more quickly on premises, while continuing to support established quarterly patching cycles through cumulative updates. All patches are applied automatically in Oracle-managed cloud environments.

Review recent communication information

This information is now available through several channels including blog posts & MOS.

What can you do to patch easier & faster?

As Mike Dietrich mentioned is his post, we live in an interesting and challenging era that we can’t stop but we should rather be prepared. For Oracle Database Administrators this means to have all your Oracle databases upgraded and patched to either 19.31 or 23.26.2. Patch NOW!

To make this process easier use automation tools like Oracle AutoUpgrade, FPP or Oracle Database Lifecycle Management in OEM.

If you have additional questions contact your Oracle Sales representative or your Oracle Architect.

UPDATE (May 5th):

Integrated Cyber Center issued a note on the upcoming CSPUs.

Beginning May 28, 2026, Oracle will deliver a Critical Security Patch Update (CSPU) each month. CSPUs provide targeted fixes for critical vulnerabilities in a smaller, more focused format, allowing customers to address high-priority issues without waiting for the next quarterly release.

Support has also issued Product-Specific guidance notes. I recommend to take a look at the general note as there are links to several specific products:

Protect Oracle Products from AI Security Threats ALERT28

Thanks,
Alfredo

OEM Incidents and AI Vector Search

In this post I want to provide a practical use of the AI Vector search functionality in the Oracle database. The use case is to be able to use AI Vector search on data that Oracle Enterprise Manager (EM) produces. In this case incidents data. The idea is to be able to search incidents using vectors in Oracle.

But what is Vector search and why we want to use it?
Vector search is a feature that enables searching data by semantics or meanings and values. We want to use it in combination with an LLM in order to ask questions about our data. What kind of data? In this case EM’s incident data.

more here…

There are several challenges that we’ll have to work on in order to be able to use AI Vector search. The first challenge is that AI Vector search is a feature of the 26AI (former 23AI) database. Oracle EM doesn’t support 26AI as a repository database. Oracle EM 24AI only supports 19.22 as a repository database as of today. So the question is, how we can me this work?

We will have to use an intermediate Oracle database for the AI Vector functionality. I decided to use the 26AI free version of the Oracle database. You can download a VirtualBox appliance image and run it in your personal computer. There are several limitations of the 26AI free version but it works for my use case.

download here…

Now that I have the 26AI appliance and database running, I need to bring the Oracle EM Incident data over. There are several ways to extract the data:

Connect through SQL Plus to the repository database
Use REST APIs
Use EMCLI

I decided to use REST APIs as it gives me the data in JSON format. Then I can format the in any shape or form needed.

Oracle EM exposes several REST APIs so we can consume data on external systems. There’s a whole framework behind these REST APIs.

more here…

We are going to call below REST API from the host where the 26AI free database is running.

$ curl -X GET -u sysman:<password> -H "Content-Type:application/json"  https://EM_HOST:EM_CONSOLE_HTTPS_PORT/em/api/incidents --insecure

This produces a JSON output with the first set of all open incidents with the most recently created incidents listed first.

I can create a Shell script that executes this CURL command, obtains the data in JSON format and then executes jq in order to convert it into CSV format. It will look similar to this:

#!/bin/bash

# Fetch JSON data using curl

JSON_DATA=$(curl -X GET -u sysman:<password> -H "Content-Type:application/json"  https://EM_HOST:EM_CONSOLE_HTTPS_PORT/em/api/incidents --insecure)

# Define the jq filter to extract and format data into CSV
# @csv: Convert the array into a CSV-formatted string
JQ_FILTER='.[].results | [.id, .displayId, .message, .severity, .status, .isOpen, .timeCreated, .timeUpdated, .target_names] | @csv' # Example filter

# Extract data and format as CSV using jq
CSV_CONTENT=$(echo "$JSON_DATA" | jq -r "$JQ_FILTER")

# Define CSV header (adjust to match your selected fields)
CSV_HEADER="id,displayId,message,severity, status, isOpen, timeCreated, timeUpdated, target_names"

# Output to a CSV file
echo "$CSV_HEADER" > incidents.csv
echo "$CSV_CONTENT" >> incidents.csv

echo "CSV data saved to incidents.csv"

We need now to insert this data into the 26AI free database and create the Vectors. Before we do that we need to find a way to generate the vectors from our incident data. For that we will use a model provided by Oracle named all-MiniLM-L12-v2.

Let’s download and load the model into our 26AI free database.

$ mkdir -p /home/oracle/onnx
$ cd /home/oracle/onnx

$ wget https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/VBRD9P8ZFWkKvnfhrWxkpPe8K03-JIoM5h_8EJyJcpE80c108fuUjg7R5L5O7mMZ/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2_augmented.zip

$ unzip -oq all_MiniLM_L12_v2_augmented.zip
$ sqlplus sys as sysdba

SQL> create user em_inc identified by password quota unlimited on users;
SQL> grant create session, db_developer_role, create mining model to em_inc;

SQL> create or replace directory model_onnx as '/home/oracle/onnx';
SQL> grant read, write on directory model_onnx to em_inc;
SQL> begin
  dbms_vector.drop_onnx_model (
    model_name => 'ALL_MINILM_L12_V2',
    force => true);

  dbms_vector.load_onnx_model (
    directory  => 'model_onnx',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'ALL_MINILM_L12_V2');
end;
/
SQL> select model_name, algorithm, mining_function from   user_mining_models where  model_name = 'ALL_MINILM_L12_V2';

MODEL_NAME                     ALGORITHM  MINING_FUNCTION
------------------------------ ---------- ---------------
ALL_MINILM_L12_V2              ONNX       EMBEDDING

At this point I can generate the Vector embeddings for the incident data.

more here…

We will now import our incident data from the CSV file and create the vector.

SQL> conn em_inc/password
SQL> create table incidents as
select id, displayId, message, severity, status, isOpen, timeCreated, timeUpdated, target_names
from   external (
         (
           id  varchar2(40),
           displayId        number(6),
           message   varchar2(500),
           severity   varchar2(100),
           status  varchar(400),
           isOpen  varchar(50),
           timeCreated varchar(400),
           timeUpdated varchar(400),
           target_names varchar(1500)
         )
         type oracle_loader
         default directory model_dir
         access parameters (
           records delimited by newline
           skip 1
           badfile model_dir
           logfile model_dir:'incidents_ext_tab_%a_%p.log'
           discardfile model_dir
           fields csv with embedded terminated by ',' optionally enclosed by '"'
           missing field values are null
        )
        location ('incidents.csv')
        reject limit unlimited
      );

SQL> alter table incidents add (
  incidents_vector vector
);

SQL> update incidents
set incidents_vector = vector_embedding(all_minilm_l12_v2 using message as data);

SQL> commit;

Let’s now perform a Vector similarity search using the VECTOR_DISTANCE function. This first query will do a search on “Incidents related to agents” query Vector.

SQL> SELECT vector_distance(incidents_vector, (vector_embedding(all_minilm_l12_v2 using 'Incidents related to agents' as data)), EUCLIDEAN) as distance,
       message
FROM   incidents
order by distance
FETCH EXACT FIRST 10 ROWS ONLY;

DISTANCE	MESSAGE
0.98592836033379427	Process em_agent crashed unexpectedly.
0.98592836033379427	Process em_agent crashed unexpectedly.
0.98592836033379427	Process em_agent crashed unexpectedly.
1.08642161802020090	Number of Active Agents crossed the critical threshold (1). Current value: 0
1.09352931649992050	Agent Unreachable (REASON = Unable to connect to the agent at https://host:3872/emd/main/ [Connection refused]). Host is reachable.
1.09352931649992050	Agent Unreachable (REASON = Unable to connect to the agent at https://host:3872/emd/main/ [Connection refused]). Host is reachable.
1.09352931649992050	Agent Unreachable (REASON = Unable to connect to the agent at https://host:3872/emd/main/ [Connection refused]). Host is reachable.
1.23613168789133000	Process ora_pmon crashed unexpectedly.
1.23613168789133000	Process ora_pmon crashed unexpectedly.

Let’s analyze these results. The shortest distance in the Similarity Vector search is for the message containing the same exact word “agent”, the second closest distance is for messages having the word “Agent” and the third one is for messages not having the word “a(A)gent”.

more here…

With this functionality we can perform Similarity Vector searches on our EM incident data.

Thanks,
Alfredo

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

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