more fun with ages

On-call duties sometimes leads to interesting results.
I have to observe a restore running (or to be more precise wait for a tape library to find the right tapes and put them into drives.
This gives me time to catch up my news aggregator. And some time to play with interesting news.
One interesting post I stumbled across is

Franck Pachos After IoT, IoP makes its way to (more...)

spfile parameters derived from cpu_count

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version and
To simulate a machine with sufficient CPUs, I had to use a trick described in a previous post. Using _disable_cpu_check threw an ORA-7445:[ksucre] in 12.2 with cpu_count >126.


Age – hard to calculate

The concept of age seems quite simple.
Right now I'm 42 years old.
I can be more precise and define it as 42 years, some months and several days.
And at a given date I will be 43 years old, and months and date are reset to 0.
Unfortunately it's hard to calculate with these numbers.
For those who wants to go into detail I'd recommend ISO 8601 Data elements and interchange formats. Unfortunately I don't have access (more...)

real virtual CPUs

Some software changes it's behavior based on capabilities of the system it's running on.
but sometimes it's interesting to check how a software would heave on a different system, which is not at hand right now.

On Linux, a lot of information about the current system can be found in /proc and /sys.
These filesystems are virtual, so they can not changed easily with an editor.

In my case I want to simulate  a lot (more...)

Method R Profiler on x86_64 Linux with HiDPI

For a new installed Linux Laptop I tried to install Method R Profiler (and Tools).
But it was not as smooth as expected.

mrprop_wrapper failed with

/opt/mrprof/mrprof_wrapper: line 45: /opt/mrprof- No such file or directory
But the file is there, and it's executable.
So the Error is slightly misleading.
Also strace showed a similar error:
execve("/opt/mrprof-", ["/opt/mrprof-"...], [/* 62 vars */]) = -1  (more...)

don’t look to close, it might change the results

We all know, if we try to look to close on some details, the results we get may change from the original observation.
This is also sometimes true for Oracle Trace events:
During analyses of a query with XMLTYPE which fails with ORA-12850 (in a 4 node RAC DB), Oracle Support asked for some trace files. the query itself is something like
FROM gv$lock l
JOIN gv$session s
... ) )

and (more...)

oratop with TFA

We are facing an issue where Oracles Trace File Analyzer creates high load on some servers.
During the discussion with Oracle Support I installed the latest version of TFA which is right now.
With this version, oratop - which oracle calls
oratop is a utility similar to the unix OS utility top which gives a overview of database performance and can be used in combination with top to get a (more...)

manage ORACLE_HOMEs and patches in a centralised way

Managing many ORACLE_HOMEs in a complex environment can be tricky, especially when questions about their patches & bugs or comparisons should be done.

It's required to understand the different types of patches available. This can be (according to the documentation & my interpretation). Especially the differences between SYSTEM PATCH (in our case only Bundle Patches), COMPOSITE and SINGLETON patches, and BUGs - of course.

A way to handle all the information is to (more...)

I went to a conference – and all I can post about is this picture

I was on DOAG and UKOUG conferences this year and hold some presentations there.
Of course I attended even more presentations and learned a lot of new things.
But I did not blog about it for some good reasons:
The presentations I attended are done already. Which means the presenters did a lot of research and put it all into great presentations and abstracts - so why should I create a weak copy of their (more...)

Backup stalled due to ASM rebalance stuck

I hit an issue where a full backup took much longer than normal.
In this case there was no alarm yet as no threshold was reached. But I worked on the DB for some other reason and out of a habit I most often start a ASH viewer whenever I work on a system - even if I only check data, it's worth to have an eye on the system.
In this case I saw (more...)

prelink makes instance unusable

I spent a recent night hunting an issue which was non trivial to detect.
On a 4 node cluster 2 nodes got Linux patches and were rebooted. This was done after 10pm for minimal business impact. (The application is written by Oracle - so it's not fully TAF aware).
The DBs started fine, all services were available.
At 3:50 in the morning I got a call some processes show strange errors.
As I connected to (more...)

Oracle Bundle Patches – release timing

I have to invest much of my time (at work) into Oracle and managing of patches, merge patches, patch requests and similar things.
At this task I identified an interesting (but probably worthless) information:
It seems a Bundle Patch (for Version 12.1) is defined approximately (at least) 40 days before it's released.
At least the BP follows this rule.

I have requested a lot of one-off and merge patches (more...)

digging into mrskew internals

I'm spending some time working with Method-Rs trace file analyzer mrskew.
As the requirements are beyond simple analyses (here an example about the uncommon things to do) I'm consulting the documentation and all available information in detail. After many readings I started to take this part of the documentation serious:

Wherever mrskew requires an expression (such as in --group and --where option arguments), you may use any valid Perl expression consisting of:

wrong permission on shm kills JAVA_JIT

We found a lot of trace files from several DBs on one of our DB Servers.
They look like:

*********_ora_26444.trc or *********_m000_5598.trc
*** 2017-09-08 15:11:29.181
*** SESSION ID:(632.5995) 2017-09-08 15:11:29.181
*** CLIENT ID:(SYSADMIN) 2017-09-08 15:11:29.181
*** SERVICE NAME:(****_****) 2017-09-08 15:11:29.181
*** MODULE NAME:(*::***:******.****.***.****.******) 2017-09-08 15:11:29.181
*** ACTION NAME:(/) 2017-09-08 15:11:29.181

Map_Length = 4096
Map_Protection = 7
Flags = 1
File_Offset = (more...)

SecureFile LOB – managing free blocks

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert SecureFile LOB - the 1st update and SecureFile LOB - more inserts.

It starts with some inserts:
insert into berx.TEST_BLOBS
select 4, comments, file_content
from berx.source_blobs
where id=4;


insert into berx.TEST_BLOBS
select 5, comments, file_content
from berx.source_blobs
where id=1;

insert into berx.TEST_BLOBS
select 6, comments, file_content
from berx. (more...)

SecureFile LOB – more inserts

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert and SecureFile LOB - the 1st update

The next insert is a small LOB with 2 bytes again:
insert into berx.TEST_BLOBS
select 2, comments, file_content
from berx.source_blobs
where id=2;
--2 byte
The table row points to a new block in the LOB segment:
LOBID = 00000001000099BF67D1

SecureFile LOB – the 1st update

This blog post continues with all the objects and settings from SecureFile LOB - the empty table and SecureFile LOB - the 1st insert

the statement is quite easy:
update berx.TEST_BLOBS
set (comments, file_content) =
(select comments, file_content
from berx.source_blobs
where id=4)

The new CLOB is about 11.1 kb in size - so it will require more than 1 block.

Fortunately the number of extents did not change.

New information needs to be gathered (more...)

SecureFile LOB – move online in 12.2

Today we had a "new features in 12.2" workshop. Thank you Mathias Zarick for this great event.
One of the new features was Online Table Move. But the doc was quite unspecific if this also includes LOBs:
Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
Data maintenance on nonpartitioned tables does not (more...)

SecureFile LOB – the 1st insert

This blog post continues with all the objects and settings from SecureFile LOB - the empty table.

The insert of one row with a small (1byte) LOB increases the complexity.
berx.source_blobs contains some BLOBs of different size.
insert into berx.TEST_BLOBS
select 1, comments, file_content
from berx.source_blobs
where id=1; --1 byte


we have a new extent now

col owner for A10  
col segment_name for A30
select owner, segment_name, file_id, block_id, blocks
from (more...)

SecureFile LOB – spfile parameters

Here is a list of all the spfile parameters I found (and guess are) related to SecureFile LOBs.

This Post will be updated with additional information whenever I collect them.


Default value




DBFS Link allows implicit fetch on modify - only on SecureFiles



Enable securefile flashback optimization



undocumented parameter for internal use only


0 12. (more...)