Tag: Database

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

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:

Thanks,
Alfredo

From AWR to Answers: Building a Local RAG Assistant with Ollama and Oracle AI Database 26ai

Oracle’s database instrumentation generates a huge amount of performance telemetry and AWR reports can still feel like a wall of text when you just want clear, targeted answers. In this post, I show how I turned a 24‑hour AWR report into an interactive performance tuning assistant using a local LLM (via Ollama on Windows 11) and Oracle AI Database 26ai running inside a VirtualBox VM.

This experiment was inspired by Lorenzo De Marchis’ “From theory to practice: building a RAG pipeline with LangChain and Oracle AI Database 26ai in Python,” where Oracle AI Database 26ai acts as the vector store behind a RAG pipeline. I kept the same core idea—Oracle as the vector database, Python as the glue—and code but adapted it to a hybrid lab setup with Ollama, VirtualBox, and an AWR‑specific use case. But also inspired by several customers that during last year’s Oracle AI World mentioned they are uploading AWR reports to online AI LLM’s to ask questions about them.

lorenzo’s post

Rather than manually scrolling through an AWR HTML report, I can now ask questions like “Summarize the top performance issues in this 24‑hour period” and let the assistant answer using only the information embedded from that report.

Architecture overview

At a high level, the architecture combines a Windows host, a VirtualBox VM, and Oracle 26ai’s native vector search.

  • Windows 11 gaming PC
    Runs Ollama as the local inference server. I chose this PC because I have an NVIDIA RTX3060 GPU and may retrieve results faster.
  • VirtualBox VM running Oracle Linux 9
    Hosts Oracle AI Database 26ai, which stores the AWR report, embedding vectors, and metadata in regular tables plus a vector index and hosts the Python application that orchestrates retrieval and generation.
  • Python RAG layer
    Connects to Oracle AI Database 26ai via the Python LangChain driver, performs similarity search using SQL over the vector column, and calls Ollama’s HTTP API to generate grounded answers.

Conceptually, the design mirrors Lorenzo’s rag.py module: the database is responsible for similarity search, and the language model is responsible for natural‑language generation. This keeps data, embeddings, and retrieval inside Oracle, while the LLM remains fully local on your own hardware.

This is very important if you want to prevent your data from leaving your premises or avoid token fees.

Why AWR plus a local RAG assistant?

AWR reports are extremely rich: they summarize instance statistics, wait events, time model data, and “Top SQL” sections between two snapshot IDs. They are necessary for serious performance diagnostics, but they are not always friendly to read under time pressure.

A local RAG assistant addresses three pain points:

  • Navigating complexity: Instead of remembering exactly which section covers which metric, you can ask natural questions such as “Which wait events dominate DB time?” or “Do we have CPU or I/O bottlenecks?” and let retrieval pick the right section.
  • Keeping data on‑premises: AWR content is operationally sensitive. By running Oracle AI Database 26ai in a VirtualBox VM and Ollama on a local PC, you keep all data and prompts within your controlled environment.
  • Experimenting with models: Ollama makes it easy to try different LLMs (Llama 3, Mistral, etc.) against the same AWR corpus by changing a model name, without rebuilding your pipeline.

The result is not meant to replace expert tuning but to act as a fast‑feedback assistant that helps you spot patterns and narrow down questions more quickly.​

Preparing the AWR data

Generating a 24‑hour AWR report

First, I collected the baseline AWR report from the test database. Oracle documents the standard scripts for generating AWR reports, such as awrgrpt.sql and awrrpt.sql, which can output HTML or text. However, I’m using the DBMS_WORKLOAD_REPOSITORY package to generate the report in text format.

From SQL*Plus on the test database:

SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_text(db_id, inst_id, start_snap, end_snap));

This produces a text output that I can easily spool into a file like awr_123_145.txt that contains all the usual sections (Instance Efficiency, Load Profile, Top Timed Events, SQL ordered by Elapsed Time, and so on).

I then copy this TXT file into a working directory accessible from the machine where I run the Python ingestion script (in my case, the Linux host where I also run Oracle and the Python RAG code).

Ingesting and chunking the AWR report with Python and LangChain

Next, we use Lorenzo’s Python ingest.py to:

  1. Parse the AWR text file.
  2. Split it into meaningful chunks.
  3. Generate embeddings for each chunk.
  4. Insert the chunks and embeddings into the Oracle AI Database using the Oracle Python driver.

I also needed to modify the ingest.py file because Ollama LLM is hosted in a deifferent machine. So, I installed Ollama locally in the Linux host and then modified the configuration setting the OLLAMA_HOST variable in order to connect to my AIServer hosts (Windows 11).

OLLAMA_HOST=http://AIServer:11434

In a production‑quality version, you would parse the report header to extract snapshot timestamps and normalize them into TIMESTAMP columns. You might also refine the chunking strategy to align with specific AWR sections such as “SQL ordered by Elapsed Time” or “Top Timed Events” for more precise retrieval.

Building the retrieval query in Oracle AI Database 26ai

At query time, I use Lorenzo’s rag.py script. I just pass the prompt and receive the output from the LLM using RAG augmentation from the Oracle database.

$ python3.11 -m app.rag "Summarize the main performance issues highlighted in this AWR"

Example questions for the AWR assistant

Once the pipeline is in place, you can treat the AWR report as a conversational knowledge base. Here are some practical queries that work well:

  • “Summarize the main performance issues highlighted in this AWR.”
  • “Which wait events contributed most to DB time, and during which hours were they highest?”
  • “List the top 5 SQL statements by elapsed time and explain in plain language what they are doing.”
  • “Does the report suggest CPU bottlenecks, I/O bottlenecks, or something else?”
  • “Based on this report, what are three concrete tuning actions I should consider first?”

A typical interaction might look like this (paraphrased):

  • Question: “What are the top three tuning actions suggested by this AWR?”
  • Assistant: “The top three tuning actions suggested by this AWR are:
  • Investigate the cause for high “Disk file I/O Calibration” waits.
  • Investigate the cause for high “Disk file I/O Calibration” waits in Module “DBMS_SCHEDULER”.
  • Investigate the cause for high “Disk file I/O Calibration” waits in Service “SYS$USERS”.”​

Because the assistant is restricted to the retrieved chunks, its answers remain grounded in actual AWR sections rather than hallucinated generic advice.

Here’s the final prompt after tuning it several times.

You are an expert Oracle Database Administrator with deep expertise in performance analysis and AWR report interpretation. Your task is to thoroughly analyze the provided AWR report and deliver a comprehensive performance analysis.

ANALYSIS REQUIREMENTS:

  1. TOP EVENTS IDENTIFICATION:
  • Extract and rank the top 10 wait events from the AWR report
  • Identify the top 5 SQL statements by elapsed time and CPU time
  • Highlight any significant changes in system metrics compared to previous snapshots
  1. ROOT CAUSE ANALYSIS:
  • For each top event, explain:
    • What type of wait event it is (CPU, I/O, Latch, Lock, etc.)
    • Why it’s occurring (resource contention, inefficient queries, configuration issues)
  • The impact on database performance
  • Analyze the correlation between events and SQL statements
  1. DETAILED RECOMMENDATIONS:
  • Provide specific, actionable steps to resolve each issue
  • Include SQL optimization suggestions for top SQL statements
  • Recommend database configuration changes if needed
  • Suggest hardware or infrastructure improvements if applicable
  • Include monitoring recommendations to prevent recurrence
  1. REPORT STRUCTURE:
  • Executive summary of key findings
  • Detailed event analysis with root cause explanation
  • Prioritized remediation steps
  • Performance improvement estimates
  • Follow-up actions and monitoring requirements

OUTPUT FORMAT:

  • Clear, technical language appropriate for DBA audience
  • Include specific Oracle commands where applicable
  • Provide justification for each recommendation
  • Highlight critical issues requiring immediate attention

INPUT: AWR data is already ingested. Use RAG.

Provide a comprehensive analysis that enables the DBA to quickly identify, understand, and resolve performance bottlenecks.

**Performance Analysis Report**

**Top Events Identification**

1. Top 10 wait events:
        * db file sequential read (14.34%)
        * db file scattered read (12.55%)
        * latch: cache buffers chains (8.42%)
        * latch: enqueue (5.67%)
        * CPU time (4.22%)
        * latch: shared pool (3.91%)
        * wait on library cache (3.56%)
        * enq: TX - row lock contention (2.95%)
        * latch: redo allocation (2.73%)
        * log file sync (2.46%)
2. Top 5 SQL statements by elapsed time:
        * SQL_ID "r6swge3rhwsa3" (96% of database time)
        * SQL_ID "ws4dr3ag24rfg" (100% of database time)
        * SQL_ID "5tm1ruvkx3wsx" (4% of database time)
        * SQL_ID "tf4dus7dsfgh" (2% of database time)
        * SQL_ID "gv3dsh4jklmn" (1% of database time)
3. Significant changes in system metrics:
        * Total database time was 7 seconds.
        * Average number of active sessions was 0.

**Root Cause Analysis**

1. db file sequential read (14.34%):
        * This wait event indicates that the database is spending a significant amount of time waiting for sequential reads from disk.
        * Possible causes:
                + Inefficient queries or indexes.
                + Insufficient disk space or slow disk performance.
                + Configuration issues (e.g., inadequate block size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
2. db file scattered read (12.55%):
        * This wait event indicates that the database is spending a significant amount of time waiting for scattered reads from disk.
        * Possible causes:
                + Inefficient queries or indexes.
                + Insufficient disk space or slow disk performance.
                + Configuration issues (e.g., inadequate block size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
3. latch: cache buffers chains (8.42%):
        * This wait event indicates that the database is spending a significant amount of time waiting for latches on the cache buffers chains.
        * Possible causes:
                + Insufficient cache size or inadequate memory allocation.
                + Inefficient queries or indexes.
                + Configuration issues (e.g., inadequate latch size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
4. latch: enqueue (5.67%):
        * This wait event indicates that the database is spending a significant amount of time waiting for latches on the enqueue structure.
        * Possible causes:
                + Insufficient enqueue size or inadequate memory allocation.
                + Inefficient queries or indexes.
                + Configuration issues (e.g., inadequate latch size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.

**Detailed Recommendations**

1. Investigate the PL/SQL statement with SQL_ID "r6swge3rhwsa3" for possible performance improvements.
        * Supplement the information given here with an ASH report for this SQL_ID.
2. Optimize the queries and indexes related to the SQL statements with SQL_ID "r6swge3rhwsa3" and "ws4dr3ag24rfg".
        * Use the SQL Tuning Advisor to identify and optimize these queries.
3. Review and adjust the database configuration to ensure adequate cache size, memory allocation, and latch size.
4. Consider adding more disk space or upgrading the disk performance to reduce wait times for sequential and scattered reads.
5. Review and adjust the database configuration to ensure adequate enqueue size and memory allocation.

**Monitoring Recommendations**

1. Monitor the wait times and latch waits for the identified SQL statements and queries.
2. Monitor the cache size, memory allocation, and latch size to ensure they are adequate.
3. Monitor the disk space and performance to ensure they are sufficient.

**Performance Improvement Estimates**

1. Estimated benefit of optimizing the PL/SQL statement with SQL_ID "r6swge3rhwsa3" is 0.02 active sessions and 15.56% of total activity.

**Follow-up Actions and Monitoring Requirements**

1. Schedule regular monitoring and analysis of the identified SQL statements and queries.
2. Review and adjust the database configuration as needed to ensure adequate resources.
3. Consider adding more disk space or upgrading the disk performance to reduce wait times for sequential and scattered reads.

Note: This report is a summary of the top events and recommendations. For a detailed analysis, please refer to the original AWR report and ADDM report.

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

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

Patch Oracle Databases With Ansible and Enterprise Manager 13c

In a previous post I show you how you can integrate DevOps automation and orchestration tools to provision Oracle databases by leveraging Enterprise Manager (EM) and the Cloud Management Pack (CMP). Once provisioned, databases need to be fully maintained in terms of monitoring but most precisely patching.

Patching databases decreases the risk of breach by timely mitigating vulnerabilities, but in can be a daunting task for organizations. Manual patching is time consuming and error prone. Home grown scripts are difficult to maintain and they increase maintenance cost. So the question is, how can I automate the patching process and even better, how can I integrate it with my current orchestration workflow?

Let me explain how you can achieve all this by making use of Oracle’s Database Lifecycle Management Pack (DBLM) and CMP. DBLM’s Fleet Maintenance help us patch and upgrade Oracle databases at scale with minimum downtime. It makes use of Gold Images and out-of-place patching in order to achieve this.

Fleet Maintenance Benefits

All this functionality can be integrated with CMP’s DBaaS in order to provide and end-to-end automation solution. DBaaS exposes REST APIs that we could then call using the automation tool of choice. Database Administrators, end users or 3rd party tools can then use these features to patch Oracle databases.

DBaaS Automation Diagram

Do you want to learn more about this and even be able to try it? We’ve created an Oracle LiveLabs that cover’s all this functionality. This lab will guide you through the request of a PDB, setup DBaaS configuration, setup Fleet Maintenance and finally patch the PDB.

Follow the link below for the Oracle LiveLabs workshop.

click here…

If you are planning on attending to Oracle Cloud World this year and you want to learn more about this consider attending my session.

LRN3519: Deploy and Manage Oracle Databases with Ansible, Oracle Enterprise Manager 

See you in Vegas!!!

Thanks,
Alfredo

Improve Oracle Database Security With Enterprise Manager 13c

IT Security is popular topic nowadays! We constantly hear news about data breaches, ransomware, malware, unauthorized access to IT systems, etc. IT organizations are constantly looking to keep their systems, networks and data safe and secure.

Today’s blog is about how Oracle Enterprise Manager (EM) can help Database Administrators to secure and harden the Oracle Databases they manage along with the hosts those databases are running on.

First things first. I strongly recommend to review the Oracle Database 19c Security Guide. This guide provides guidelines on how to secure the Oracle Database, harden the DB access, secure and encrypt the DB data and so.

click here…

Now let’s discuss some areas that database administrators should also look at in order to improve their security posture:

  • Timely apply security patches
  • Monitor database configuration and detect misconfigurations
  • Use industry and regulatory standards like STIG and CIS for the Oracle Database

All the features that we will be discussing today are part of the Oracle Database Lifecycle Management pack. This pack requires an additional license.

Timely apply security patches

Fleet Maintenance (FM) enables administrators to automate lifecycle activities such as database patching and upgrades. FM is a gold image subscription based model that allows to patch databases with minimum downtime by using out-of-place patching mechanisms. In-place patching is also available if you need to apply an emergency on-off patch.

Administrators have the ability to customize the patching process by adding custom pre/post scripts to patching operations. FM supports single instance, RAC databases, Grid Infrastructure, Multitenant and Data Guard configurations.

One thing to mention is the ability to get security patch recommendations as soon as they are published. EM connects to My Oracle Support (MOS) and checks for the availability of new security patches. As soon as a new security patch is released EM will let you know if your DB estate is compliant or not in terms of these patches.

more info…

Monitor database configuration and detect misconfigurations

Configuration and Drift Management helps you monitor the configuration of your DB estate, the hosts on where those DB’s are running as well as the Oracle Homes (OH) for those installations. EM allows you to create your own configuration templates based on the configuration settings you need to enforce. Any misconfiguration or drift away of your template will be automatically reported via the Drift Management dashboard and you can also receive alerts if you choose to.

Corrective Actions (CA) can also be created to automatically fix this misconfigurations in order to comply with the templates and reduce security risks.

How many times administrators issued an ALTER SYSTEM command with SPFILE scope and forgot about it? Well, you will know next time you bring your DB up after maintenance. EM helps you detect these changes before they become a production issue. It also help you track the history of configuration changes, save configuration information at a given time and also allows you to use this configuration information to be compared between targets.

Have you wonder, how many OH’s we have with this specific one-off patch?

How many DB’s we have running on this specific OS version?

Well, EM can help you answer all these questions using this configuration data.

One thing worth mentioning is that EM comes with hundreds on configuration collections. If you need to gather a very specific configuration that is not available out-of-the-box, you can create your own configuration extension and collect this automatically.

more info…

Use industry and regulatory standards like STIG and CIS for the Oracle Database

EM provides compliance standards to help customers meet regulatory standards like STIG and CIS. Oracle’s best practices are also included within the compliance framework. There are two available options for analysis.

  • Rule based analysis
  • Real-time change

Each option allow administrators understand where attentions needs to be put in order to harden the DB estate.

Using the compliance framework, EM will provide a score to each associated target along with all the violations that need to be remediated after each evaluation.

more info…

I also want to provide links to Oracle LiveLabs workshops available that cover the features discussed above.

Thanks,
Alfredo

Oracle Enterprise Manager 13c Snap Clone Demo

The database cloning process can be often time consuming, resource intensive and expensive especially for large multi-terabyte databases. This can lead to miss project deadlines or to cause that storage costs grow out of control.

Enterprise Manager 13c Snap Clone instant database cloning allows administrators to create fully functional copies of databases using the capabilities of the underlying storage layer. Snap Clone is also capable to use Data Masking Definitions in order to securely manage test data.

Below video is a demonstration on how to setup Snap Clone with the ZFS storage appliance in order to clone either single instance or pluggable databases (PDBs).

For additional information about Snap Clone capabilities click below.

Click here…

Thanks,
Alfredo

Oracle Database Performance Diagnostics, Tuning and Validation with Oracle Enterprise Manager 13c Workshops

Last month we held 2 workshops where we went through some capabilities of Oracle Enterprise Manager 13c that help with performance diagnostics, tuning and validation for the Oracle Database.

If you were not able to attend and you want to learn about these capabilities, I recommend you take a look at the recording using the link below.

Session 1: August 4th 2021

Click here…

Session 2: August 25th 2021

click here…

Thanks,
Alfredo