ORA-08176 with DB-link, create table and isolation level SERIALIZABLE

Today I had some fun identifying how a ORA-08176 can happen.
It started with a ticket similar to "we get an ORA-08176 during a select, please fix the database."
After some questions it refined to "we do a CREATE TABLE x AS SELECT in one session, and a SELECT * FROM X@db_link throws the ORA-08176.

That's enough for me to do a test-case, but I needed to change the isolation level to produce the (more...)

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
SELECT *
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
tab1col1 
              FROM   table2)


This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid (more...)

access to CHM raw data – without manipulating the -MGMTDB

In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository (GIMR) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be (more...)

Instance parameters derived from cpu_count – 12.1.0.2

About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a 11.1.0.7 DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's 12.1.0.2 without any PSUs/patches. The machine is the same class as previous, so it took some time.

the (more...)

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...)

anatomy of DBA_FEATURE_USAGE_STATISTICS

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 12.1.0.2.3 + 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.

Problem


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 3.0.0.1 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...)

ORADEBUG DOC 12.1.0.2

this is just an online docu of ORADEBUG DOC in 12.1.0.2.
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`
do
lipo -create ~/instantclient_11_2_{32,64}/$i -output ~/instantclient_11_2/$i || cp ~/instantclient_11_2_64/$i ~/instantclient_11_2/$i
done


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 (more...)