In
my previous blog post, we explored how Oracle 26ai completely
eliminates lock contention for numeric attributes using Lock-Free
Reservations. But what happens when you have critical business logic that must
rely on standard row locks, and a background process blocks a time-sensitive
VIP operation?
Traditionally,
Oracle operates on a strict first-come, first-served model. If a
low-priority batch script updates a row first, a high-priority executive or
customer-facing transaction is forced to sit in a queue waiting on enq: TX -
row lock contention.
With
Priority Transactions in Oracle 26ai, you can explicitly define
transaction importance and set threshold policies. If a high-priority session
hits a row locked by a lower-priority transaction, Oracle will automatically
abort and roll back the blocking session after a configured wait period.
In this
article, I will compare traditional row-level locking with Prioritized
Transactions using practical examples and show how transaction priorities can
change concurrency behavior under contention.
This article is
part of a small series on modern concurrency features in Oracle 26ai, including
Lock-Free Reservations, Prioritized Transactions, and Value-Based Concurrency
Control.
Last
week, one of my clients experienced an issue with Oracle LogMiner after a table
structure change. The generated SQL appeared to be incorrectly reconstructed,
raising concerns about possible corruption in the LogMiner output.
Before
looking at the incident, it is useful to briefly understand what Oracle
LogMiner does. LogMiner is an Oracle database utility that allows DBAs and
applications to analyze redo and archived redo logs and reconstruct the
database changes recorded in them. It is commonly used for troubleshooting,
auditing, data recovery, and CDC (Change Data Capture) solutions.
In
this case, the affected CDC environment was using LogMiner to capture changes
from the database. The problem appeared after a deployment modified the
following table:
USER.TRANSACTION
The
table changed from 21 columns to 32 columns, with 11 new columns added.
The
first important clue came from the Oracle alert log, which showed a significant
increase in parsing errors around the same time:
The table's LAST_DDL_TIME was also:
These
timestamps helped connect the application problem with the table structure
change.
The
investigation then focused on how LogMiner was obtaining its dictionary
information and how it handled redo generated before and after the structural
change. This ultimately led us to the root cause and the appropriate
solution.
Handling concurrent updates has always been a challenge in
high-volume transactional systems. Consider a banking application where
hundreds of transactions attempt to credit or debit the same account at the
same time. With traditional row-level locking, once one session updates the
account balance, other sessions must wait until the transaction commits or
rolls back. As concurrency increases, this waiting can become a bottleneck,
resulting in lock contention, increased response times, and events such as enq:
TX - row lock contention.
One of the newer Oracle features designed to address this problem
is Lock-Free Reservations. Instead of relying on immediate row locking for
certain numeric updates, it allows concurrent transactions to reserve resources
while preserving transactional consistency.
In this article, I will compare traditional row-level locking with
Lock-Free Reservations using practical examples and see how each approach
behaves under concurrent load.
This article is part of a small series on modern concurrency features in Oracle 26ai, including Lock-Free Reservations, transaction prioritization, and value-based concurrency control.
Recently, I had
a task to refresh our UAT environment using a PDB from the production database
hosted in Oracle Cloud Infrastructure (OCI).
At first, the
activity looked straightforward. The plan was to perform a remote PDB clone
from Production to UAT. However, there was one challenge from the beginning:
the source and target databases were located in different OCI VCNs.
After
establishing connectivity between the environments, I started the cloning
process. The clone started successfully, but after approximately one hour, it
appeared to stall, and no significant progress was observed.
After some
investigation and testing, I identified two important factors that affected the
cloning process:
The target DBCS was running with only 1
OCPU.
The production database had an hourly
archive log backup and a delete job running through Commvault.
In this
article, I will share the architecture, troubleshooting process, and lessons
learned from this refresh activity.
In Oracle
environments that do not use Oracle ASM, databases are not configured to start
automatically after a server reboot. To automate database startup and shutdown,
administrators commonly use Oracle's standard dbstart and dbshut
utilities.
Although these
tools provide basic automation, they do not always verify that the database has
successfully reached the OPEN state, and they do not provide built-in
notification when startup failures occur.
This article
demonstrates how to combine dbstart/dbshut, systemd, startup
validation checks, and automated email alerts to create a reliable Oracle
database auto-start framework. The approach is compatible with Oracle Linux 7,
8, and 9 and can be used with Oracle database versions that support the
standard dbstart/dbshut utilities. It also includes a safe testing
method for validating the email alert system without affecting a production
database.
In this blog
post, I design:
An Oracle auto-start service using systemd
Proper shutdown control
An email alert system for service failures
A safe test service to validate the
alerting mechanism without production risk
This design is simple, stable, and suitable for Oracle Linux production environments.
In Part 2 of this series, I described how I upgraded several Solaris Non-Global Zones using an out-of-place upgrade method. Because the zones did not have enough temporary working space for a direct IPS upgrade, I copied the zone filesystems to a larger storage area, performed the package update there, and then synchronized the updated files back to the original zones.
The approach worked, but it also introduced additional manual steps compared to a standard upgrade. As with many administrative tasks, the technical procedure itself was not the biggest challenge. The real challenge was making sure each step was performed against the correct zone and the correct filesystem.
During the upgrade process, I made two mistakes that led to some unexpected troubleshooting. First, I accidentally synchronized files from the wrong upgraded zone image, causing one Non-Global Zone to boot with another zone's hostname, IP address, and listener configuration. Later, while correcting the network settings, I mistakenly executed an IP configuration command in the Global Zone instead of the target Non-Global Zone, which immediately disconnected my SSH session and required recovery through the server's ILOM console.
Fortunately, neither issue resulted in data loss, and both were recoverable. This article documents what happened, how the problems were identified, the recovery steps I followed, and the lessons I took away from the experience.
In Part 1 of this series, I covered the process of upgrading the
Global Zone from Solaris 11.4 SRU22 to SRU90. Once the Global Zone upgrade was
completed successfully, the next task was upgrading the Non-Global Zones
running on the same server.
According to Oracle's recommended approach, upgrading Non-Global
Zones is usually a straightforward process. If the zone has sufficient free
space available, the update can normally be completed by mounting the zone,
updating the packages, and then booting the zone.
In many environments, this method works without any issues.
However, during this project I encountered an unexpected problem. Even though
several Non-Global Zones had more than 12 GB of free space available, the
standard upgrade procedure repeatedly failed. This article documents the issue
I encountered, the troubleshooting process, and the workaround that ultimately
allowed the upgrades to complete successfully.
Upgrading Solaris is never very simple work. It is not like just
running one command and everything finishes. Especially when you are going from
Solaris 11.4.22 to 11.4.90, it becomes more complicated and needs more careful
thinking. In this situation, it is not only about updating packages but also
about understanding how Global Zone (GZ) and Non-Global Zones (NGZs) are
working together and how this relation will change.
One of the main problems in this upgrade is something called a
"Sync-Linked" exception. This happens when dependencies between
packages are very tight, and the system does not allow upgrading all zones
together in one step. Because of this, we cannot follow the normal upgrade
path. We need to separate the process and let Global Zone go first, independent
of others.
In this blog series, I will go through the complete upgrade process
from Solaris 11.4.22 to 11.4.90 and share the steps I followed in my
environment. I will explain how to upgrade the Global Zone and Non-Global
Zones, what issues I faced during the process, and how I solved them. I will
also cover some mistakes that can happen during the upgrade and the recovery
steps that helped me bring the system back when things did not go as expected.
My hope is that this real-world experience can help others who are planning a
similar Solaris upgrade.
Talking to your data is no longer a future idea. With Oracle AI
Database 26ai, Oracle has made it possible to use natural language instead
of writing long SQL queries. You can ask questions in simple English and get
answers directly from the database.
However, there is a problem.
In the Oracle 26ai Free VirtualBox Appliance, Select AI
does not work as expected. Some required AI packages are missing, and even
after creating and compiling them manually, DBMS_CLOUD_AI.CREATE_PROFILE still
does not work.
The reason is simple: the Free VirtualBox Appliance does not
currently support Google Gemini or OpenAI through native Select AI. The
feature is present, but external AI providers are not yet enabled in this
environment.
This is not a setup mistake. It is a limitation of the current Free
release.
In this blog, I will show how to solve this by building our own AI
connection using PL/SQL and UTL_HTTP. This method gives full control,
works without Select AI, and allows us to use Google Gemini directly
from Oracle 26ai.
This
is Part 4 of a four-part blog series on replicating data from an
on-premises Oracle Database to Oracle Cloud Infrastructure (OCI) using Oracle
GoldenGate.
In
this final blog, we focus on establishing secure connectivity between the
on-premises and OCI GoldenGate environments using NGINX. Once connectivity
is in place, we create and configure the Extract and Replicat processes to
enable real-time data replication from the on-premises source to the OCI target
database.
This
is Part 3 of a four-part blog series that demonstrates how to replicate
data from an on-premises Oracle Database to a database running on Oracle Cloud
Infrastructure (OCI) using Oracle GoldenGate.
In
this blog, we focus on connecting Oracle GoldenGate to the source and
target databases. Specifically, we configure the on-premises GoldenGate
deployment to connect to the source database and the OCI GoldenGate deployment
to connect to the target OCI DBCS database. These connections are critical
prerequisites before enabling data movement between environments.
This
is Part 1 of a four-part blog series that demonstrates how to replicate
data from an on-premises Oracle Database to a database running on Oracle Cloud
Infrastructure (OCI) using Oracle GoldenGate.
In
this blog, we focus on configuring Oracle GoldenGate in the on-premises
source environment, including database prerequisites, user setup, and
preparing the source database for change data capture.
Oracle
Cloud Infrastructure (OCI) provides Bastion as a secure and convenient way to
access private resources without exposing them directly to the internet.
Recently, while trying to connect to a Windows Server 2012 instance via RDP
through a Bastion host, I encountered connection issues. Despite setting up SSH
port forwarding, the RDP session would not establish. This experience
highlighted an important consideration: while Bastion is ideal for many
scenarios, there are situations where it may not work, and OCI’s Console
Connection (VNC) can be a reliable alternative.
In
this blog, I’ll walk you through the root cause of the Bastion failure, explain
why it occurs, and guide you on how to use Console connections to access
Windows instances.
I
recently encountered the frustrating "ORA-30012: undo tablespace
'UNDOTBS1' does not exist or is of wrong type" while attempting
to convert my Data Guard Physical Standby into a writable Snapshot Standby for
an important testing cycle. In my setup, the Primary database was a RAC running
on Exadata Database Service on Dedicated Infrastructure, while the
Standby was a single-instance deployment on DBCS. This mixed
environment introduced subtle configuration differences, and one of those
differences caused the ORA-30012 failure during the snapshot conversion. In
this post, I’ll walk through how I investigated the issue, identified the
undo-related mismatch on the standby, and applied the correct fix to enable the
Snapshot Standby conversion successfully.
Recently, I encountered an issue on a DBCS Data Guard standby database where the alert log reported the error ORA-29771. After seeing this error, I checked the Data Guard status and noticed that the standby had developed a significant apply lag, even though redo apply was still running.
In this blog, I’ll explain the symptoms I observed, how I investigated the cause of the ORA-29771 error, and the steps I took to restore normal standby performance.
Running Oracle databases on Oracle Database Cloud Service (DBCS) usually provides a stable and well-automated environment, but unexpected platform-level issues can still occur—especially when critical OS-level components are modified or removed. Recently, I encountered a serious problem where a DBCS compute node failed to start the database, and the stack repeatedly threw an ORA-07445 error during the startup process.
At first glance, the issue looked like a typical software or patch conflict, but after deeper investigation, it turned out to be something far more fundamental: the swap mount point and related configuration had been cleared or removed on the compute instance. Without an active swap device, several Oracle background processes failed during initialization, resulting in the ORA-07445 crash.
In this blog, I’ll walk you through the symptoms, diagnostic steps, and the exact solution that restored the instance.
A bigfile tablespace is a tablespace
with a single, but large datafile. Traditional small file tablespaces, in
contrast, typically contain multiple datafiles, but the files cannot be as
large. Making SYSUAX, SYSTEM, and USER tablespaces bigfile by default will
benefit large databases by reducing the number of datafiles, thereby
simplifying datafile, tablespace, and overall global database management for
users.
For
decades, databases have excelled at storing and retrieving structured data, but
they struggled when it came to understanding similarity, context, or meaning.
Traditional SQL queries rely on exact matches and predefined relationships,
which makes searching unstructured or semantically rich data difficult. Oracle
23ai changes this model by introducing native AI Vector Search directly inside
the database. With vector embeddings stored and indexed alongside traditional
data, Oracle enables similarity-based searches that go beyond keywords and
exact values. This allows applications to find results based on meaning,
relevance, and proximity in vector space, making it possible to search text,
documents, images, and other complex data types in a far more intuitive and
powerful way, all using SQL.
AI Vector Search
Instead of searching for exact words, Vector Search allows you to
search for concepts. By using the new VECTOR data type, you store
"embeddings"—mathematical representations of data. This allows you to
perform a "similarity search." For example, if you search for
"staffing issues," the database can find documents about "low
headcount" or "recruitment delays" because it understands they
are conceptually related.
Example:
Assume you have a table called docs that stores documents along
with their vector embeddings.
To see the power of Vector Search, I need a larger dataset. With
only one row, the "closest" result is always the same one! Here is a script to quickly populate my docs table with 100 rows
of synthetic data. I’ve used a CONNECT BY loop and DBMS_RANDOM to generate
unique titles and random vector coordinates so that the similarity search
actually has something to compare.
INSERT INTO docs (id, title, embedding)
SELECT
level + 1,
'Tech Manual Part ' || (level + 1),
-- Generating a random 3-dimensional vector string like '[0.12, -0.45, 0.88]'
'[' ||
ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ',' ||
ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ',' ||
ROUND(DBMS_RANDOM.VALUE(-1, 1), 2) || ']'
FROM dual
CONNECT BY level <= 100;
COMMIT;
Let’s take a look at the table structure with this query:
set line 200;
col ID for 9999;
col TITLE for a20;
col EMBEDDING for a50;
set pagesize 5000;
select * from docs;
Instead of writing a complex text search, you can ask the database
to find documents that are conceptually similar to a given idea.
What is the following query doing
The vector [0.1, 0.5, -0.2] represents the
concept you are searching for (for example, something related to staffing
or workforce challenges).
VECTOR_DISTANCE calculates how close each
document’s embedding is to that concept using cosine similarity.
Documents with the smallest distance are
the most closely related in meaning.
The query returns the top 3 documents
that best match the idea, not based on keywords, but on similarity.
-- Searching for documents similar to a specific concept
SELECT title
FROM docs
ORDER BY VECTOR_DISTANCE(embedding, '[0.1, 0.5, -0.2]', COSINE)
FETCH FIRST 3 ROWS ONLY;
In practice, this means a search for “staffing issues” could return
documents talking about low headcount, hiring delays, or resource shortages,
even if the exact phrase never appears. The database understands the
relationship between these ideas.
Conclusion
AI
Vector Search in Oracle 23ai represents a major shift in how databases handle
modern data workloads. By embedding vector storage, indexing, and similarity
search directly into the database engine, Oracle eliminates the need for
external vector stores or separate AI infrastructure. This keeps data secure,
reduces architectural complexity, and improves performance by allowing vector
queries to run where the data already lives. As a result, Oracle 23ai enables
developers and data teams to build smarter, AI-driven applications using
familiar SQL tools while unlocking semantic search capabilities that were
previously difficult or costly to implement. Vector search is no longer an
add-on; it is now a core database capability.
Oracle 23ai clearly shows that Oracle is putting
developers first when it comes to SQL. For a long time, writing Oracle SQL
meant putting up with extra clutter—things like always using FROM DUAL or
repeating the same expressions again and again in GROUP BY clauses. It worked,
but it often made simple queries feel unnecessarily complicated.
With the latest releases, Oracle is clearly trying to
make SQL easier and more pleasant to use. The focus is on writing cleaner, more
natural code with less repetition. In this blog, I will look at four small but
powerful improvements that remove a lot of that old hassle and let you focus on
the data and logic instead of outdated syntax rules.
SELECT without FROM
Previously, Oracle required a FROM clause for every SELECT
statement, forcing developers to use the DUAL to retrieve constants, system
dates, or perform calculations. Now, you can simply SELECT what you need. It is
cleaner, shorter, and matches the behavior of PostgreSQL and SQL Server.
Example
In this example, we retrieve the current system date, add
a simple label, and calculate a projected value, all without using DUAL.
SELECT
SYSDATE AS current_time,
'HR_REPORT' AS report_tag,
(100 * 1.15) AS projected_value;
VALUES Clause Inside SELECT
The VALUES clause used to be limited to INSERT
statements, but Oracle 23ai elevates it into a powerful table constructor. You
can now use VALUES directly inside a SELECT, effectively creating a small
in-memory table on the fly.
This is especially useful when you need a temporary
lookup set without creating a table, using UNION ALL, or relying on temporary tables.
Example:
In this example, the VALUES clause is used to create a temporary,
in-memory table directly inside the query. Think of it as a small dataset
that exists only for the duration of this SELECT. Each row in the VALUES list
represents an employee ID paired with a performance rating.
The alias v(emp_id, rating) gives names to the two
columns created by the VALUES clause. This is important because it allows
Oracle to treat the result just like a normal table, with clearly defined
column names.
The query then joins this temporary table to the EMPLOYEES
table using a standard JOIN. The join condition matches employees.employee_id
with v.emp_id, ensuring that only employees listed in the VALUES clause are
returned.
As a result, the query displays each employee’s last name
alongside the temporary rating assigned in the VALUES list.
SELECT e.last_name, v.rating
FROM employees e
JOIN (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
ON e.employee_id = v.emp_id;
---The following is another syntax for it with the same output
SELECT e.last_name, v.rating
FROM employees e, (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
where e.employee_id = v.emp_id;
GROUP BY ALL
Complex reports often require grouping by many columns,
which means copying those same column names into the GROUP BY clause. This is
tedious and error-prone. GROUP BY ALL solves this by automatically grouping on
every non-aggregated column in the SELECT list.
Example
This query calculates the average salary for each
combination of department, job, and manager. It uses GROUP BY ALL to
automatically group by all the non-aggregated columns in the SELECT list, so
you don’t have to list them again manually.
The HAVING clause then filters the grouped results,
keeping only those groups where the average salary is greater than 5,000. In
addition, this example shows that in Oracle 23ai, you can use a column alias
(avg_sal) directly in the HAVING clause, which makes the query shorter and
easier to read.
SELECT
d.department_name,
e.job_id,
e.manager_id,
ROUND(AVG(e.salary), 2) AS avg_sal
FROM employees e , departments d
Where e.department_id = d.department_id
GROUP BY ALL
Having avg_sal >5000;
Conclusion
Oracle 23ai and 26ai make it clear that Oracle is
focusing more on developers and their day-to-day work. These updates don’t
change the power of Oracle SQL, but they make it much easier and nicer to use.
By removing extra syntax, writing SQL becomes simpler, clearer, and less
error-prone.
Whether you’re creating reports, running analytics, or
just querying data, these improvements will quickly feel natural. After using
them, the older ways of writing SQL will seem outdated, and that’s a good
thing.
.png)
.png)
