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.
Rolling
forward a standby database involves applying incremental backups to synchronize
changes made to the primary database. This process becomes more complicated when
a new datafile isadded to
the primary database. In this article, I will outline the essential steps and
considerations involved in rolling forward a standby database using RMAN
incremental backups after the addition of a datafile on the primary side.
