handling disks for ASM – when DB, Linux and Storage admins work together

A proper management of ASM Disks can be a complicated task.

On DOAG2015 I discussed with Martin Bach about the concept in my current company, where we implemented a setting which is consistent, robust and enables Storage, Linux and DB admins to work together easily.

As we started to think about ASM when 10.1 was out we tried to evaluate our possibility. asmlib was discarded quite early as it only increased complexity without additional (more...)


In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to
display information about database feature usage statistics.
That's all I can find in The (12.1) Docu.

Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:

Still for me it's interesting what's behind the view, and how it's content is populated.

So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
SELECT  (more...)

12c datapatch can fail with manual out of place patching

datapatch can fail if doing manual out of place patching, when the new ORACLE_HOME is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME, which is included in something else (like a PSU) in the new ORACLE_HOME.
In my specific situation the old O_H had PSU + some one-off + Patch:21355879.
This Patch is not needed anymore in my new O_H (more...)

12c datapatch – take care of parallel patching

datapatch is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.


Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress (more...)

SQL Plan Directives and result cache affects performance

In my current company we are preparing a migration of our billing application to a new version. During this migration there are the ordinary changes of infrastructure as well. Application servers from HPUX to Linux, database servers from RH5 to RH6, different storage subsystem, Oracle from 10.2 to 12c, different application partitioning, and so on ...
At least from the management perspective the expectation is clear: everything is shiny new and costed a lot of (more...)

Poor mans getTracefile

As you might know already, I'm a big fan of Oracle Traces. Consequently I'm a fan of Method-R tools to work with these files as well.
A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to (more...)

SQL Developer 4.1 with Method R Trace 3

These days SQL Developer 4.1 GA was released. If you use SQL Developer, I'd recommend an update.

Nearly at the same time Method R Trace extension for SQL Developer was released as well. Especially for developers that's a very important news - at least if they concern about performance. Method R Trace
A new Version of this extension is needed as SQL Developer changed it's extension framework at the upgrade from version 3 (more...)


this is just an online docu of ORADEBUG DOC in
The general comments from Tanel Poder apply to this version as well.

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - (more...)

enhanced SQL trace

As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits.
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)

Instrumentation still needed

Recently I received an email with an simple content:
"It" is slow again :-)
The email explained a package to be slow.

Ok, I started with my "not enough information" reply and an email conversation began.

We are still not ready to identify the problem, but as there is no (more...)

No Adaptive Cursor Sharing for collections

Recently I was hunting a performance problem at work.

A developer read about adaptive cursor sharing and therefore guessed, the optimizer would know about the number of rows when he passes a collection for a table(:bind) function. I can totally understand it, as there where no limitations in the statement (more...)

Resetting Your Oracle User Password with SQL Developer on OSX

This post started as a shameless copy of Jeff Smiths Resetting Your Oracle User Password with SQL Developer - but it ended in a trilogy about OSX, Oracle and SQLDeveloper.
In my first two posts I made myself comfortable with Oracle instant client on my MacBook. Now I want to use it for something more serious, like changing passwords.

Even with all the modifications from previous post SQLDeveloper still does not show the Reset Password... option. The reason: it just don't know about the library correctly.

First the PATH as shown in previous post is not set in an (more...)

setting environment variables for sqlplus on OSX

In my previous post I showed how to create an universal binary for Oracle instant client so I don't need to care about 32/64 bit anymore.

Now I try to show how to set some environment variables so I only need to enter sqlplus and can start my work without preparing anything first.

I know I could do this in my local .profile, but I was looking for a more centralized way to provide them, maybe on a shared desktop where basic applications are installed just once.

This is all about OSX Mountain Lion - I'm working on 10. (more...)

Oracle client universal binaries for OSX

Recently Oracle announced a new Oracle Instant Client for OSX. As always there is a 32-bit and a 64-bit version available.
I was curious if they can be merged together to an universal binary. The short answer: yes, they can.

It all starts with downloading the instant client zips for 32 and 64 binary. I loaded basic and sqlplus and unzipped them into 2 directories instantclient_11_2_32 and instantclient_11_2_64. I also created a target directory instantclient_11_2.

Then the simple script
for i in `ls instantclient_11_2_32`
lipo -create ~/instantclient_11_2_{32,64}/$i -output ~/instantclient_11_2/$i || cp ~/instantclient_11_2_64/$i ~/instantclient_11_2/$i

did the work with this (more...)

about traveling

At the moment I am trying to travel to Edinburgh for a week of Exadata Prove of Concept.
The basic idea was to leave Vienna on sunday evening and arrive in Edinburgh on sunday night.
As I tried to do an online checkin on Saturday evening - I discovered the flights where shifted to monday evening. - That was not my initial plan as one day missing on a 5 days PoC is not so nice.
After some research I discovered my initial flight from Vienna to Amsterdam was cancelled. But there was still an earlier flight on sunday lunchtime. (more...)

side channel attack on ORA-00942

Oracle Databases has a powerful set of grants and permissions. One of the easy philosophies behind it is just to hide anything a user is not allowed to see. Technically this leads to an error message
ORA-00942: table or view does not exist.
More precisely it should give a text like table or view does not exist or you are not allowed to access it.
For an ordinary user/schema separation there might be no big difference: If user A can not read table B.TAB it's of no value for user A whether the object does not exist or is just not (more...)

who cares if a listener is dying

In this post I try to show what's going on if a local listener dies in a 11gR2 RAC environment. My basic question is: When does (a) SCAN-Listener knows the local Listener disappeared?
My testcase (a sandbox):

  • A 2-node RAC - all actions are run on node 1, if not explicit defined.
  • My test-DB is called TTT04 (Test, you know?)
  • I have 3 SCAN listeners there, but I want to make the test-case easier so I do pin down my connection string to only one SCAN-listener (it's SCAN2 in my case):
    TTT04_bx =
    (ADDRESS = (PROTOCOL = TCP)(HOST = (more...)

how to secure CMAN against CVE-2012-1675 – or an easier method than ASO

In the Oracle DBA World at the moment CVE-2012-1675 is a great issue. Oracle announced some methods how to secure existing systems. But these are sometimes not that easy, and there is no backport for older systems.
As I investigated the problem how to secure a connection manager I was hinted at Note:1455068.1.
The solution is somewhat easy: Only allow incoming connections to your systems. e.g.

In a well designed environment where you can separate your DB Servers from others at low network layers, a set of CMAN (more...)

do not touch if you do not know for sure

Oracle provides and documents a huge load of possibilities and functions for nearly every purpose. For me it is impossible to know all of them. Even to know such an area exists is hard.
But still sometimes these functions Oracle does not document for customers purpose seems to be more attractive than those officially available.
One of these attractive packages is DBMS_SYSTEM. You will not find any description of this package in the official Oracle documentation. There are some small traces available, but nothing really useful.
Oracle also have quite clear words about using such unofficial, and hidden, packages:
In (more...)