Featured by Oracle Magazine

The Oracle Magazine featured me in its January / February 2016 issue. I really feel honoured and would like to say thank you for the opportunity!

Martin Klier Oracle Magazine Jan Feb 2016

For details, please see the Performing Databases Blog post about the publication.



DOAG noon2noon RAC & Dataguard – Quick Report

There’s always something to learn – for example about Oracle RAC (Real Application Clusters) and Dataguard. And the old-school frontal teaching concept is boring, and more important, ineffective after a few hours of passive listening. So the German Oracle Users Group DOAG organized a “noon2noon” event: “Oracle RAC and Dataguard” this week in Würzburg (January 2 1st-22nd 2016). It was the second noon2noon, after introducing the concept last year with Oracle vs. MySQL. I enjoyed it much last time, so I easily agreed to volunteer as a RAC Attack “Ninja” and tech guy on site for the workshops in general this year.

The response was close to overwhelming – we calculated 25 participants, but ended up with 39, and a PACKED room. Somebody called it a chicken cage, but the atmosphere was great. Thanks to the air condition. :)

DOAG noon2noon RACattack

Everybody is highly motivated, despite the packed room at noon2noon :)

But what’s that noon2noon thing?

Read more…



How to disable Oracle ACFS drivers / registry resource

Sometimes an installed ACFS can cause trouble, especially if we don’t or can’t use it (e.g. when not using an UEK Linux kernel, like with SuSE Linux Enterprise Server SLES). There’s lots of documentation how to create and maintain ACFS file systems, but how to get rid of ACFS at all wasn’t so easy to guess. I had to find out how to disable Oracle ACFS drivers, because the grid infrastructure did not stop successfully when using “crsctl stop crs” or “/etc/init.d/ohasd stop”:
CRS-2799: Failed to shut down resource ‘ora.drivers.acfs’ – and thus, it was not possible to upgrade the grid infrastructure 11.2.0.3 to version 12.1.0.2 (rootupgrade.sh fails on first node, also when trying to stop the CRS for the same reason).

How to disable Oracle ACFS drivers - runInstaller during upgrade

Read more…



Oracle 11g and 12c Cardinality Feedback Leads to Hundreds of Child Cursors

Sometimes, advanced features can bring advanced problems. Seeing a project with hundreds of child cursors with a different execution plan each, is never a pleasure, but there has to be a reason.

In fact, we are talking about the “USE_FEEDBACK_STATS” reason in v$sql_shared_cursor. Its meaning is: “A hard parse is forced so that the optimizer can reoptimize the query with improved optimizer inputs (for example, cardinality estimates)”. The issue was, that the DB executed a plan, got feedback, made a new plan, executed it, got feedback, made a new plan … This way,

Oracle 11g and 12c Cardinality Feedback Leads to Hundreds of Child Cursors!

And too many child cursors is causing all sorts of problems for efficient parsing, as I described some time ago.

What I did to get rid of the child cursor generator in this case:

  • Simplifying the query by removing subqueries (changing them to joins wherever possible). This grossly reduces the degree of freedom for the optimizer.
  • Creating better statistics by using extended statistics (multi column stats to improve combined cardinality). This was the key to success, made the initial guessing good enough for a stable plan.

With this combined, we had less plans, and the plans we got were used more than once.

Just the picture was specific to a recent version, but the reason was always there. The invalidation reason just surfaced it. Fixed it with quite a traditional approach, as I usually do – not fussing around with ADDM and stuff.

I’m a strong believer in the CBO’s dynamic. So please treat your Cost Based Optimizer nicely – you can’t beat it into submission. Make it see the facts, and it will behave well without strings attached.

Sorry that I can’t show you a reference case today. Hope this gives you an idea anyway, and if you have questions, just let me know.
Martin



What is a “RAC Battle”?

RAC Battle [ræk ˈbæt̬l̩]

What is a “RAC Battle”? It is a format of presenting technology – two experts, battling against each other on pros and cons of Oracle Real Application Cluster. Björn Rost (Oracle ACE Director) and Martin Klier (Oracle ACE)

Björn Rost -  What is a "RAC Battle"?  Martin Klier - What is a "RAC Battle"?

will fight

Wednesday November 18th, 2015
11 am
Nürnberg CCN (DOAG Konferenz 2015)
Room St. Petersburg

Who will be pro? Who will be con? We don’t know, we will decide by lot in front of the audience.

Be there, to see a technology event at its best in rounds, with no strings attached. Are you afraid of violence? Stay calm, Johannes Ahrends (Oracle ACE) will be the referee to avoid bloodshed.

Johannes Ahrends - What is a "RAC Battle"?

Here’s the official RAC Battle link from DOAG.



Oracle Database 11.2 Extended Support Waived

As announced today, Oracle waived the Extended Support for the period of February 2015 until May 2017.

Oracle Database 11.2 Extended Support Waived

See the paper with details here.



Oracle 12c in practice – customer report in Cologne #12cjetzt

From Beta to Production – Oracle 12c at TGW
In October, I’ll speak at the Oracle #12jetzt (#12cnow) symposium in Köln (Cologne) about the introduction of 12c as standard palttform at our ISV customer TGW Software Services.

12c jetzt Symposium Header

I had the privilege to continuously working on the 12c product evaluation and introduction from availability of 12c beta2 until today. My company, Performing Databases, played the key role for creating and developing the guidelines of operation, configuration and licensing. My example will be the “new” development environment, to show the first steps of the project. The highlight of the talk will be the report about our migration from 9i to 12c at S.Spitz GmbH, the biggest food vendor in Austria.

When?
October 15th
approx. 11am

Where?
Hyatt Regency Köln
Kennedy-Ufer 2A
50679 Köln
Germany

Please see the agenda here.

Link to the presentation here.



Oracle dbms_workload_repository.awr_global_report_text fails with ORA-06553 PLS-307

My last post was about generating AWR reports from SQL – not a very complicated case. “But” – little word, big meaning. And suddenly we get a strange error message.

In my example, I was especially interested in a Real Application Cluster’s “global” report. The subprogram call for that purpose is dbms_workload_repository.awr_global_report_text(), accepting the DBID, begin snapshot, end snapshot, an option field, and, l_inst_num for the instance number. Regarding the latter, Oracle documentation says: “List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available …”

But when I did that, I got an error that felt strange to me:

ORA-06553: PLS-307: too many declarations of ‘AWR_GLOBAL_REPORT_TEXT’ match this call

Read more…



How to create an Oracle AWR report with SQL and PL/SQL

AWR is a great tool (but needs diagnostics pack). It can create very useful reports for performace analysis over a given period. Most people will pull the AWR Report  from the OS level, with ‘@?/rdbms/admin/awrrpt.sql” (or one of the other scripts there). But sometime it’s not possible to access a database server’s OS level, or gain permission to do so. So we also can create the whole bunch of possible AWR and ASH reports from SQL level, too. We can have it in text or HTML, it’s just a matter of personal taste. I always preferred the text version, but there are many facts pro HTML. Anyway, here is my cheat sheet how to do it, and the difference in RAC.

What we have to know

Our database ID (DBID):

select dbid 
  from v$database;

The period we are interested in, spoken in snapshot IDs:

select /*+ FIRST_ROWS */ * 
  from DBA_HIST_SNAPSHOT
  order by snap_id desc, instance_number desc;

How to create an Oracle AWR report with SQL and PL/SQL

SELECT OUTPUT
FROM TABLE (dbms_workload_repository.awr_report_text(
 l_dbid=>123456789,
 l_inst_num=>1,
 l_bid=>24142,
 l_eid=>24143
 )
);

Read more…



Oracle 12c RMAN delete obsolete behaves interactively in DBMS_SCHEDULER job type BACKUP_SCRIPT

Situation

In Oracle Database 12c, we have the long-missed feature of DBMS_SCHEDULER job type “BACKUP_SCRIPT”, that allows us to create backup jobs without creating them as an OS file, and without need for a wrapper script that’s called by a job type EXECUTABLE (as we usually did that in the last years).

Sometimes we also have to use the RMAN command “delete obsolete” to get rid of unneeded backup pieces. When executed interactively, it comes back with the question “Do you really want to delete the above objects (enter YES or NO)?”. But when running from a script, it just deletes:

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           67     30-JUN-15 
Backup Piece         67     30-JUN-15          +ORAFRA/...nnndf...
deleted backup piece
backup piece handle=+ORAFRA/...nnndf0_tag...
Deleted 1 object
Recovery Manager complete.

Problem

Strange is, that when using it within a DBMS_SCHEDULER job type BACKUP_SCRIPT, “delete obsolete” thinks it was running interactively, and the scheduler seems to compensate it, by simply answering “NO” – what leaves us with a growing number of old backup pieces!
(Taken from v$rman_output):

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           61     30-JUN-15
Backup Piece         61     30-JUN-15          +ORAFRA/.../nnndf0_tag....
RMAN-06546 Error occurred getting response - assuming NO response
 Read more...