Monthly Archives: July 2026

Copy Audit and unified audit across databases

This entry was posted in Audit cloning Oracle Security on by

The client had a special request: copy the audit data to another database – once -, for compliance reasons.

The Audit data does not let it copy so easily. Before was usually in SYS schema, now it belongs to a special AUDSYS user. None allows to export data by default.

To copy the audit data from one database to another, there is special expdp argument: “include=audit_trails”. This only works in full export mode:

expdp system@server.domain.ch\pdb_service \
  DIRECTORY=expdp \
  DUMPFILE=audit_data_$(date +%Y%m%d_%H%M).dmp  \
  FULL=Y \
  INCLUDE=AUDIT_TRAILS

On the new database, I’ve imported to a new user “old_audit” and specific tablespace, which I created before the import

SYS@DB1.PDB1> create tablespace unified_audit_ts;
SYS@DB1.PDB1> alter user system quota unlimited on unified_audit_ts;

SYS@DB1.PDB1> create user old_audit no authentication 
    default tablespace unified_audit_ts 
    quota unlimited on unified_audit_ts;

At the end I’ve imported the data, using remap_schema and remove the segment attributes, so all go to the new default tablespace:

impdp system@server.domain.ch\new_pdb \
  DIRECTORY=expdp 
  DUMPFILE=$(ls -tr audit_data*.dmp | tail -n1) 
  REMAP_SCHEMA=sys:old_audit 
  REMAP_SCHEMA=audsys:old_audit 
  TRANSFORM=SEGMENT_ATTRIBUTES:N

OEM 13.5 – Failed to allocate OCIEnv from Perl corrective actions

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.

Update 30.07.2026: the client tried also the solution by installing the patch 37099808. It did not work. Then, the client did install the instantclient manually as decribed above, and it works. Manual install seems the right solution.

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.