Resolving ORA-19502, ORA-16038 and ORA-27072 Errors in Oracle Database

We recently encountered errors below and there are several common causes.

 ORA-19502: write error on file "/oraarch/TESTDB/1_432678_12436018.dbf", block number 182272 (block size=512)
 ORA-16038: log 2 sequence# 432678 cannot be archived
 ORA-19502: write error on file "", block number (block size=)
 ORA-00312: online log 2 thread 1: '/redo2/TESTDB/TESTDB_1B.rdo'
 ORA-27072: File I/O error

The “ORA-27072: File I/O error” , can occur due to below are common reasons

• Disk issue – This error can also occur if the disk or storage is inaccessible. It might be due to hardware related issues
• File corruption- The file system where database resides might have corrupted.
• Permission issue – If the database user does not have enough permissions, you will get this error.
• Mount failures – when Filesystem not mounted properly

The “ORA-16038” error mainly occurs when archive log file cannot be archived. In this case if the database cannot be able to reuse redo log files, logs cannot switch, the database may hung.

The “ORA-19502” error mainly caused by insufficient disk space or file system full.

In our case, the issue was caused by a full archive log filesystem. 

When archive log file system got full, the redo log archiving failed triggering ORA-16038 and ORA-19502 errors. This eventually resulted ORA-27072 due to failed write attempts

Recommended steps

1. Check the archive log and db_recovery_file_dest destinations

SHOW PARAMETER log_archive_dest;
SHOW PARAMETER db_recovery_file_dest;

If using FRA:

SHOW PARAMETER db_recovery_file_dest_size;

 

2. User should use “df-h” to check the diskspace

User should Pay special attention to:

• Archive destination mount point
• FRA mount point

3. If the file system is full Increase size by extending lun or increasing FRA size.

4. Make sure user run the backup and delete old archive logs

rman target /
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-x';

Here X means number of days

5. In case FRA is full then user should increase the db_recovery_file_dest_size using below example

Check FRA usage using below query

SELECT name, space_limit/1024/1024 MB_LIMIT,
               space_used/1024/1024 MB_USED,
               space_reclaimable/1024/1024 MB_RECLAIMABLE

FROM   v$recovery_file_dest;  

ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

6. Always check for alert.log to review errors and find the root cause.

Look for:

• ARCn errors
• Log switch failures
• Repeated I/O messages

To Avoid this issue in future user can take below measures:

• Monitor FRA usage regularly
• Set up alerting when disk usage exceeds 80%
• Configure proper RMAN retention policy
• Automate archive log deletion after backup
• Separate archive logs from other mount points
• Monitor log switch frequency

In our environment, the archive log filesystem became completely full.

This caused:
ORA-19502 (write failure)
ORA-16038 (cannot archive log)
ORA-27072 (I/O error)

Once disk space was cleared, archiving resumed automatically and the database returned to normal operation.

Thanks & Regards,

https://oracleracexpert.com

ORA-51801 Fix: How to Resolve Vector Dimension Mismatch in Oracle 26ai

As users adopt AI features in Oracle 26ai, I see one error appearing frequently

“ORA-51801: VECTOR dimension mismatch”

Users face this error when the dimension of the vector being inserted or queried does not match the VECTOR column definition. Pls note that Oracle 26ai requires consistency between VECTOR column definition and the embedding being inserted or used in queries.

Here are few common Embedding Dimensions

Model Type                      Typical Dimension
MiniLM                            384 or 768
BERT variants                  768
OpenAI embeddings        1536
Large transformer models  1024+

For example 1: The below table has 768 dimension and inserting 1536, it will result ORA-51801 error.

SQL> CREATE TABLE documents (
id NUMBER,
embedding VECTOR(768)
);

SQL> INSERT INTO documents VALUES (1, :embedding_1536);

ORA-51801: VECTOR dimension mismatch

If you plan to use a 1536-dimension model, you must recreate the table with VECTOR(1536), since VECTOR dimensions cannot be altered directly.

For example 2: User can get ORA-51801 error while querying as well

SELECT * FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vector)
FETCH FIRST 8 ROWS ONLY;

If :query_vector dimension ≠ column dimension user will receive the error.

How to avoid these errors

• Develops should define embedding model centrally and make sure they document its output dimensions.
• Validate dimensions before insert, pls find below example

              if len(embedding) != 768:
                    raise ValueError("Invalid embedding dimension")

You can prevent ORA-51801 by creating a metadata table and validating dimensions at runtime.

 
SQL> CREATE TABLE embedding_config (
model_name VARCHAR2(100),
dimension NUMBER);

If user encounters the issue, then you should check what the VECTOR column dimension is, embedding model output and queries using same model using below query

SQL> SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'DOCUMENTS';

or 

SQL> DESC DOCUMENTS;

The output shows

EMBEDDING VECTOR(768)

Always remember “Your VECTOR column dimension must exactly match your embedding model output”. Note that even single value difference will trigger ORA-51801

Thanks & Regards,

https://oracleracexpert.com

Avoiding ORA-04068 Using RESETTABLE Packages in Oracle 26ai

In Oracle prior Oracle 26ai, if a PL/SQL package is loaded the variable values are retained across multiple calls in the same session. That means the package will keep their state within a user session. However, if a package was recompiled or modified all the active sessions that has the package will receive ORA-04068 errors.

The RESETTABLE clause introduced in Oracle 26ai, it is beneficial to avoid -ORA04068 error when the existing state of the package has been discarded.

The RESETTABLE clause can be used in package or package body during the creation. But note that users cannot be able to use RESETTABLE clause in combination with the SERIALLY_REUSABLE pragma.

Syntax: - CREATE OR REPLACE PACKAGE [BODY] RESETTABLE

Using below sample package and package body for demonstration

SQL> CREATE OR REPLACE PACKAGE sales_pkg AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will use two different sessions for demonstration

Session 1: execute the package
SQL> EXEC sales_pkg.add_sale;

Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1:

SQL> EXEC sales_pkg.add_sale;

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SALES_PKG.ADD_SALE" has been invalidated
ORA-04065: not executed, altered or dropped package "SALES_PKG.ADD_SALE"
ORA-06508: PL/SQL: could not find program unit being called: "SALES_PKG.ADD_SALE"

....

The main reason you are receiving this error because Oracle discards the package state when it is recompiled. When user run next time, it will reinitialize the package.

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

Now we will modify our sample code with RESETTABLE clause

SQL> CREATE OR REPLACE PACKAGE sales_pkg RESETTABLE AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg RESETTABLE AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will repeat the same exercise to observe how the RESETTABLE clause affects the package’s behavior.

Session 1: execute the package

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1: execute the package again

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

In the second scenario, when Oracle detects that a package’s state is no longer valid, it automatically reinitializes the package instead of raising an ORA-04068 error. By using the RESETTABLE clause, the package can safely discard its state and reinitialize without causing any errors to user session.

Key Takeaways from "Oracle AI Foundations Associate" Training

I am pleased to share that I have successfully completed the Oracle AI Foundations Associate training and Certification. This program provided a comprehensive introduction to the world of Artificial Intelligence (AI) from foundational theory to hands-on exploration with Oracle Cloud Infrastructure (OCI) AI services all underscored by the importance of Responsible AI.

1. Core AI Concepts Clarified

The training demystified the distinctions and relationships between Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL).
It also deepened my understanding of major ML types, including:
Supervised Learning – for predictive modeling, regression, and classification.
Unsupervised Learning – for discovering structure and patterns within data.

This foundation helped translate theoretical AI ideas into clear, real-world applications.

2. Generative AI and Large Language Models

A major focus was the rapidly advancing field of Generative AI and Large Language Models (LLMs).
The course emphasized that effective Prompt Engineering is key to obtaining accurate, context-aware outputs. I also explored Retrieval-Augmented Generation (RAG) — a technique that enables LLMs to integrate proprietary and up-to-date enterprise data, making them highly relevant for business use cases.

3. AI in the Enterprise Cloud: The OCI Advantage

Oracle’s approach to AI through OCI stood out as both practical and scalable.

Key components of the portfolio include:

OCI AI Services – pre-built and customizable models such as OCI Vision and OCI Language for image and text analysis.
OCI Generative AI Service – a managed platform offering access to foundational LLMs and fine-tuning capabilities.
OCI Data Science – an environment supporting the full lifecycle of model development, training, and deployment.

This structure clearly illustrates how enterprises can implement AI seamlessly within existing cloud ecosystems.

Building on Responsible AI

Beyond technology, the program emphasized the importance of trustworthy and ethical AI.
Key principles include:
Fairness – identifying and mitigating bias in data.
Transparency (Explainable AI) – ensuring clarity behind AI-driven decisions.
Accountability and Robustness – maintaining reliability, governance, and security.

These pillars ensure that AI adoption remains ethical, credible, and sustainable across industries.

What’s Next

Earning this certification marks an important milestone in my AI learning journey. I now have the foundational knowledge to not only discuss AI confidently but also to apply OCI’s AI tools responsibly in real-world contexts.

I’m looking forward to leveraging these insights on upcoming projects and continuing toward advanced Oracle AI certifications.

For anyone looking to build a strong, industry-recognized foundation in Artificial Intelligence, I highly recommend exploring the Oracle AI training and certification path, it’s a great way to connect theory with practical, cloud-based innovation.

Thanks & Regards,

https://oracleracexpert.com

READ-ONLY PDB users in Oracle 23ai - Secure Multitenancy

Oracle 23ai has introduced a new feature, READ-ONLY PDB user to improve security, developer productivity and performance. This helps multi-tenant environment where data access is critical.

The READ-ONLY user cannot perform any DDL or DML activities.

Create a Read-Only PDB User: To create a Read-Only PDB use the new READ ONLY clause in the CREATE USER statement.

Connect to PDB user
SQL> ALTER SESSION SET CONTAINER = my_pdb;

Create readonly “hr_user”
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;

Grant create session to hr_user
SQL> GRANT CREATE SESSION TO hr_user;

Note that the “hr_user” cannot be able to perform below tasks

• User cannot run INSERT, DELETE, UPDATE or MERGE.
• User Cannot create or modify tables, indexs, views or procedures
• User cannot change roles or privileges
• User cannot modify session-level settings

When you try any of the above user will receive “ORA-28194: Can perform read operations only " error.

Run below view to see the user is read-only or not
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME      READ_ONLY
--------------------  -----------------
HR_USER           YES

SQL> Connect hr_user/paswordxxx;
Connected.

SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only

SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only

Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.

 
SQL> exec REV_SALARY;
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at "HR_USER.REV_SALARY", line 3
ORA-06512: at line 1

The READ-ONLY user can run a SELECT query without any issues.

SQL> SELECT emp_id, emp_name from employee;
EMP_ID EMP_NAME
-------------- ----------------------------
1 test_user1
2 test_user2
3 test_user3
SQL>

The Read-Only PDB Users provide a clean way to enforce non-modifiability of users at the database level. This helps with read intensive applications, as these users restricted to only SELECT and users cannot perform any DDL or DML activities.

Thanks & Regards,

https://oracleracexpert.com