Yearly Archives: 2026

OEM 13.5 – Failed to allocate OCIEnv from Perl corrective actions   Recently updated !

This entry was posted in bug Enterprise Manager Oracle on by

At a client, since Enterprise Manager 13.5 Release 1 Update 30 (RU 30) – the April June 2026 update -, that Corrective Action to extend tablespaces stopped to work.

They got two production application completely frozen due to this!

The symptoms were that the Perl script triggered by OEM Agent could not anymore connect to the database. The error was found for instance at $AGENT_HOME/agent_inst/sysman/log/emagent_perl.trc:

WARN:  instanceDiskPerformanceMetric.pl: Could not connect to sys/(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv0nnnn.xxx.ch)(Port=1521))(CONNECT_DATA=(SID=+ASM))): Failed to allocate OCIEnv

The problem was clear with the Agent version, because by rolling it back, the problem disappeared.

We opened a Service Request, that went its typical way and it is still open. At the end I found the solution myself.

The problem needed to be either with Perl or Client drivers. The Perl was the latest version, seems correctly working.

Then I found the Oracle Support note KB398956 “On OEM 13c : How To Upgrade Perl DB Client Version For 13.5 & 13.4 OEM Agent Home From 12c To 19c”. There, the link for the patch 37099808 to be applied on OEM 13.5 was not working (on 16.07.2026). So I decided to follow the instructions for OEM 13.4.

I downloaded the latest instant client 19.31 and unzipped in a staging area ($STAGING_AREA below).

Then I did what the note above said for OEM 13.4:

emctl stop agent
cd /u00/app/oracle/product/agent/agent_13.5.0.0.0
mv instantclient /tmp/agent_instantclient
cp -ar $STAGING_AREA/instantclient_19_31 .
mv instantclient_19_31 instantclient
cd instantclient
ln -s libclntshcore.so.19.1 libclntshcore.so.12.1
cp -a adrci uidrvci ../bin/
emctl start agent

When testing again, the Corrective Action was back working.

The client is now creating a Gold Image and deploy this agent version everywhere.

Make Oracle tablespace management easier – make sure to have autoextend on and maxsize unlimited

This entry was posted in Oracle on by

On several customers where databases exists since many years, it is common to find datafile and tablespace management to be inconsistent, mainly due to historical reasons and the DBA preferences at the time.

It is common to see a mix of these patterns in a typical 8192 block size database using smallsize tablespaces:

  1. Datafiles of fixed sized, and DBA increases every few months (or when an alert comes) the size by some GB
  2. Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles this new size with autoextend off
  3. Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles with small size but autoextend on
  4. Datafiles with real maxsize and autoextend on

I try always to educate that we should monitor mainly the disk space, not tablespace size. We can monitor the growth, but I don’t want to wake anyone at night because of the tablespace is getting full.

Today I’ve just fixed on the customer databases all the tablespaces, setting all datafiles with autoextend on and maxsize unlimited. It is important to make sure there is a disk space monitor behind it.

To check which tablespaces are impacted by not having the real datafile maximum size, this query can help:

select tablespace_name, round(sum(maxbytes)/1024/1024/1024) current_max_gb, round(sum((power(2,22)-2)-maxblocks)*sum(maxbytes)/sum(maxblocks)/1024/1024/1024) to_possible_max_gb 
from dba_data_files 
where maxblocks!=(power(2,22)-2) 
    or autoextensible!='YES' 
group by tablespace_name 
order by 1;

Here the code to fix this:

begin 
  for df in (select file_id from dba_data_files where maxblocks!=(power(2,22)-2) or autoextensible!='YES') loop
    execute immediate 'alter database datafile '||df.file_id||' autoextend on maxsize unlimited';
  end loop;
end;
/

For smallsize tablespaces, the maximum number of blocks is power(2,22)-2. On internet, it is sometimes written power(2,22)-1, however, when looking at dba_data_files.maxblocks for a unlimited size datafile, the number corresponds to power(2,22)-2.

Ah, for creating and adding datafiles to tablespaces, this is what I use, so it is automatically with autoextend on and unlimited datafile size – both are default:

CREATE TABLESPACE tbs_name;
ALTER TABLESPACE tbs_name ADD DATAFILE;

It expects the usage of OMF – db_create_file_dest parameter is set.

Even better is when OEM Corrective Action is configured correctly, as it takes care of adding datafiles automatically.

On DB 19.31, ORA-600 [kaf lob buffer overwrite]

This entry was posted in bug Oracle on by

After Oracle support did not accepted that there is bug with huge LOB segments and High IO due space manager KTSJ, this time it worked faster.

The move of the Table and LOB to a new Tablespace went ok, but soon we started seeing ORA-600 [kaf lob buffer overwrite] on the alertlog. These happened either from the application or also from DBMS_STATS daily process.

The culprit seems to be to parallel access to the segment. First I noticed the table had degree 4. After setting it back to 1, there were no more problems from the application, but only from DBMS_STATS – which is configured to run parallel. However setting it to run without parallel, does not help.

After sending all logs and a 10046 trace to Oracle, an automatic answer come:

Oracle has matched the symptom ‘ORA-600 [kaf lob buffer overwrite]’ from trace file xxxxx_p000_459134_i152080.trc in SR for version 19.31.0.0.0 to BUG 39180642 with a high level of confidence.

BUG 39180642 (unpublished) is still under investigation by Oracle Development. Update this SR if you wish to be kept informed of the progress of the investigation.

The error seems similar to: “KB348966 – Automatic Stats collection Task failing with ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments: [kafger buffer overwrite]”, where the issue is some kind of corruption or invalid data.

Let’s see what Oracle says.

High IO due space manager (W00x process) doing db sequential read all the time – KTSJ Slave bug? 1

This entry was posted in bug Oracle Oracle 19c on by

On a client DB, after migrating it to OL8, the application started to complaint the DB seems slower. Checking the OEM, there is not much done by the application, but I see a good blue stripe from W00x processes doing sequential reads. Nonstop. This is a 19.31 single-instance database.

After tracing these sessions, they are reading two distinct LOB segments, each between 500 and 700GB, where a lot of deletes happened. All the time “db file sequential read”, up to 3 or 4 W00x parallel processes.

WAIT #0: nam='db file sequential read' ela= 199 file#=476 block#=2460662 blocks=1 obj#=53176 tim=1024371028390

I open an SR with Oracle. They ask more traces and session short stack. The short_stack looks like:

SQL> oradebug setospid 15297
Oracle pid: 28, Unix process pid: 15297, image: oracle@srv0nnnn.xxx.ch (W000)
SQL> oradebug unlimit
Statement processed.
SQL>  oradebug short_stack

ksedsts()+426<-ksdxfstk()+63<-ksdxcb()+872<-sspuser()+236<-__sighandler()<-semtimedop()+14<-sskgpwwait()+245<-skgpwwait()+187<-ksliwat()+2296<-kslwaitctx()+188<-ktsj_slave_main()+944<-ksvrdp_int()+1906<-opirip()+579<-opidrv()+581<-sou2o()+165<-opimai_real()+173<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46

No underscore parameters are set and changing “_enable_space_preallocation” to 0 has no immediate effect.

The tablespace is composed of several smallfiles, EXTENT_MANAGEMENT=LOCAL, ALLOCATION_TYPE=SYSTEM, SEGMENT_SPACE_MANAGEMENT=AUTO. All standard.

The LOB segment is securefile, configured in-row, with several larger values which go out of row.

I ask several times to Oracle: what triggers the KTSJ Slaves processes to fully read huge LOB segments? I do not get a satisfactory answer.

We do not have this problem with all other LOB segments of the DB. The workaround at the end was to defragment the LOBs, moving them to a new tablespace (which we wanted to do anyway). The LOB segment reduced from >500GB to 75G and the Space Manager processes disappeared completely. This lead to the next problem (next post).

I leave this post here just in case someone encounters the same.

Database specific password wallet with 26ai client: the new SEPS_WALLET_LOCATION parameter

This entry was posted in Oracle AI 26ai Security on by

One inconvenient I had in the past with the usage of password wallets for Oracle users was their maintenance. With the years, there was just a lot of old entries. At the end you would do a script that would test all credentials and delete the ones which did not work or there was no more network alias equivalent.

In OCI, to connect to Autonomous Databases, there is since few years a possibility to have a wallet parameter defined at the network connection or tnsnames, using “(SECURITY=(wallet_location=/home/oracle/wallets/databases)))” – sometimes also seen using “my_wallet_directory”. But this only works for TLS connections and using certificates.

At my client I wanted to have a wallet per database, so that at the time of decommissioning, we just delete the whole wallet.

After looking around, I finally found out that using a 26ai client there are new parameters for the connection string and one of them is SEPS_WALLET_LOCATION, which allows to specify the location of the wallet for a specific entry.

This way I can do:

# My DB is ANJO_DB
$ echo $ORACLE_SID
ANJO_DB

# Create wallet
orapki wallet create -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -pwd <wallet_pwd> -auto_login_local

# Add credential
orapki secretstore create_credential -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -pwd <wallet_pwd> -connect_string $ORACLE_SID -username sys -password <sys_pwd>

# Add the wallet location in tnsnames
$ grep $ORACLE_SID $TNS_ADMIN/tnsnames.ora
ANJO_DB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anjovm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ANJO_DB.WSL.HOME))(security=(SEPS_WALLET_LOCATION=/u00/app/oracle/admin/ANJO_DB/wallet)))

# Check with tnsping
tnsping ANJO_DB

TNS Ping Utility for Linux: Version 23.26.2.0.0 - Production on 30-JUN-2026 14:10:10

Copyright (c) 1997, 2026, Oracle.  All rights reserved.

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anjovm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ANJO_DB.WSL.HOME))(security=(SEPS_WALLET_LOCATION=/u00/app/oracle/admin/ANJO_DB/wallet)))
OK (10 msec)

# Connect
$ sqlplus /@ANJO_DB
SQL*Plus: Release 23.26.2.0.0 - Production on Tue Jun 30 14:11:40 2026
Version 23.26.2.0.0

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 30 2026 13:52:44 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.30.0.0.0

SQL>

The documentation about this and other new elements of the tnsnames.ora file in Oracle 26ai are at https://docs.oracle.com/en/database/oracle/oracle-database/26/netrf/local-naming-parameters-in-tns-ora-file.html

ASM diskgroup space calculator for ExaCC

This entry was posted in Cloud Engineer Systems ExaCC ideas OCI Oracle and tagged OCI on by

In ExaCC and probably on Exadata in OCI it is sometimes a bit complex to calculate how much space you need to add to the ASM, as you can only change the total space and the distribution.

When you just want to add 10TB (or GB) space to DATA diskgroup, you need to play a while or do an excel.

With the calculator below, you can now do it easily:

Edit any cell — usable, raw (×3), % of total, or the total row itself. Other values update accordingly.

Lock Disk group Usable space Raw space (×3) % of total
DATA
TB TB %
RECO
TB TB %
SPARSE
TB TB %
Total
TB TB 100%

When looking inside asmcmd, you see the “Raw space”. This is how much from disk space is used. The Usable space is what the users see and what you need to consider.

How to create an audit policy based on user profile? Example using a context

This entry was posted in Audit ideas Oracle on by

The customer wanted to have some audit policies that would be applied only to users who have a certain profile – lets say the profile APP_PROFILE.

Using unified auditing, it is possible to enable a policy for certain roles using AUDIT POLICY policy_name BY USERS WITH GRANTED ROLES role_name;

And you can use context on audit policies using CREATE AUDIT POLICY policy_name ACTIONS <action_list> WHEN q'!SYS_CONTEXT('<context_name>','<context_attr>') = <value>!' EVALUATE BY SESSION;

The user profile is unfortunately not automatically available in the default USERENV context.

The way to have audit policy on users with a certain profile, needs to go via a logon trigger, which either sets a ROLE to a user, or sets a CONTEXT.

I’ve decided on the latter option. For this I need few steps – this is done inside the PDB:

1. Create a context – AUDIT_CONTEXT -, which must say what program is allowed to set it

CREATE OR REPLACE CONTEXT audit_context USING audsys.proc_set_audit_flag;

2. Create the procedure referenced above – AUDSYS.PROC_SET_AUDIT_FLAG, that sets the context

CREATE OR REPLACE PROCEDURE audsys.proc_set_audit_flag IS
    v_profile VARCHAR2(128);
  BEGIN
    SELECT profile INTO v_profile
      FROM dba_users
     WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER');

    IF v_profile = 'APP_PROFILE' THEN
    dbms_output.put_line('Set context AUDIT Y');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'Y');
    ELSE
      dbms_output.put_line('Set context AUDIT N');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
END proc_set_audit_flag;
/

3. Grant access to the procedure

The procedure will be called by low privilege users. So the procedure needs access to DBA_USERS, this is done via an intermediary role ROLE_SET_AUDIT_FLAG; and any user needs to be able to execute the procedure, so it needs to be available to PUBLIC.

CREATE ROLE IF NOT EXISTS role_set_audit_flag;
GRANT select ON dba_users TO role_set_audit_flag;
GRANT role_set_audit_flag TO PROCEDURE audsys.proc_set_audit_flag;
GRANT execute ON audsys.proc_set_audit_flag TO public;

4. Create a logon trigger – TRIGGER_SET_AUDIT_CONTEXT – calling this procedure

CREATE OR REPLACE TRIGGER trigger_set_audit_context
  AFTER LOGON ON DATABASE
BEGIN
  audsys.proc_set_audit_flag;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

5. Use this construct within an audit policy – AUDIT_APP_USERS

CREATE AUDIT POLICY audit_app_users 
ACTIONS PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE 
WHEN q'!SYS_CONTEXT('AUDIT_CONTEXT', 'AUDIT') = 'Y'!' EVALUATE PER SESSION;

AUDIT POLICY audit_app_users;

Conclusion

Now, when I connect with a user having the APP_USER profile, I can see I’ve the right context

MIGUEL@ANJO.PDB1> SELECT * FROM SESSION_CONTEXT;

       NAMESPACE    ATTRIBUTE    VALUE
________________ ____________ ________
AUDIT_CONTEXT      AUDIT      Y

1 row selected.

And on the audit trail we can now see the entries from that policy:

SYS@ANJO.PDB1> select  EVENT_TIMESTAMP, DBUSERNAME, UNIFIED_AUDIT_POLICIES from audsys.unified_audit_trail order by 1 desc fetch first 1 rows only;

                  EVENT_TIMESTAMP    DBUSERNAME            UNIFIED_AUDIT_POLICIES                                                                            
_________________________________ _____________ _________________________ 
29-APR-2026 16:03:46.841520000    MIGUEL           AUDIT_APP_USERS

Oracle Update Advisor in 19c – tutorial of the new easy way to find and download Oracle DB release updates

This entry was posted in Oracle Oracle 19c Oracle AI 26ai Patchset Update and tagged Release Updates on by

Oracle released a new version of its “Oracle Update Advisor”. This tool is part of Oracle AI Database 26ai, allows to anyone to check if their Oracle installation is up to date and download a new Gold Image updated to the latest version.

When using it with Oracle 19c, you need to download the tool “dbcactl“, which complements existing Database Creation Assistant – dbca – with the “managePatches” option – this is the Oracle Update Advisor.

To download dcbactl, you need to go to https://updates.oracle.com/download/6789999.html . While latest available version visible is 26.2.1, it is not downloadable. I did my tests using the version 25.4.1.

(more…)

Alter system kill session “force timeout 0” ? new form of “kill immediate”?

This entry was posted in Oracle Oracle AI 26ai on by

The other day I saw that besides the catcon.pl, there is a catcon_kill_sess_gen.sql:

ls -1 $ORACLE_HOME/rdbms/admin/catcon*
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon_kill_sess_gen.sql
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon.pl
/u00/app/oracle/product/19.30.0/rdbms/admin/catcon.pm
/u00/app/oracle/product/19.30.0/rdbms/admin/catconst.pm
/u00/app/oracle/product/19.30.0/rdbms/admin/catcont.sql

When looking at this file, I noticed it kills catcon session using a “ALTER SYSTEM KILL SESSION ‘sid, serial#’ FORCE TIMEOUT 0”, except if on Oracle 12.1, where still does “IMMEDIATE” instead:

This new syntax “FORCE TIMEOUT x” is first described in Oracle AI 26ai DB documentation (but it seems to work from version 12.2). Basically it theoretically allows the draining of sessions by setting a timeout until the session terminates some task, before killing it.

There are two new elements here: “FORCE” and “TIMEOUT x”.

(more…)

SQL and how Oracle filter the resultset

This entry was posted in Oracle Oracle AI 26ai on by

While reading the very interesting Oracle AI Database 26ai New Features Guide, I got to know that there is a new “QUALIFY” filter clause (there is also a new “FILTER” clause). However, what I found more interesting in the documentation was this sentence:

The order of operations is as follows: FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → FETCH FIRST.

This is somehow a back to the roots and basic SQL information that I had in the back of my head, but never though really end-to-end about.

Here an example of query using all except DISTINCT elements of the operations order:

SELECT region, city, AVG(temperature), AVG(AVG(temperature)) OVER w AS avg_temp_region 
    FROM city, regions 
    WHERE city.region_id = region.region_id 
    GROUP BY region, city 
    HAVING region NOT LIKE 'LISBON' 
    WINDOW w AS (PARTITION BY region)
    QUALIFY avg_temp_region > 10
    ORDER BY region
    FETCH FIRST 5 ROWS ONLY;

I don’t think I could write a SQL like that, but at least now I know how it looks like.