Oracle 11g unable to extend datafile but ASM disk group shows free space

Sometimes Oracle Database 11gR2 complains about not being able to extend a (lob) segment with ORA-1691, but ASM monitoring based on USABLE_FILE_MB did not fire. Adding a new data file fails with ASM ORA-15041. I was under the impression this behaviour of ASM deserved some explanation.

Situation

Database Alert Log complains with ORA-1691:

ORA-1691: unable to extend lobsegment MYUSER.SYS_LOB0013128030C00003$$ by 128 in tablespace USERS
ORA-1691: unable to extend lobsegment MYUSER.SYS_LOB0013128030C00003$$ by 8192 in tablespace USERS

Ok, a quick look comparing dba_segments and the tablespace size – it’s full. So let’s extend it with one more datafile:

SQL> alter tablespace USERS add datafile size 2G autoextend on next 1G maxsize 32G;
 alter tablespace IWACS add datafile size 2G autoextend on next 1G maxsize 32G
 *
 FEHLER in Zeile 1:
 ORA-01119: Fehler bei der Erstellung der Datenbankdatei '+ORADATA'
 ORA-17502: ksfdcre:4 konnte Datei +ORADATA nicht erstellen
 ORA-15041: diskgroup "ORADATA" space exhausted

Uh-oh, ORA-15041? But as I well know, the customer is monitoring FREE_MB and USABLE_FILE_MB in v$asm_diskgroup…? Maybe something nasty is going on, let’s check if ASM has to work on balancing:

SQL> select * from v$asm_operation;
no rows selected

Read more…



My COLLABORATE16 look-at’s – an aftermath

From April 10th to April 14th 2016 many Oracle professionals congregated in Las Vegas. The three big U.S. user groups, the IOUG, the OAUG and the Quest User Group once again formed one of the best Oracle Conferences in the world: COLLABORATE16. I was honored to be there as a speaker, and glad to perform two lectures in front of packed rooms (downloads here). Thank you all who made it possible and who attended!

But this post is about what I took home from COLLABORATE16. I never take the time to write down everything. But wherever I go, I take notes what to look at when I am back. And as often, I’d like to share my very personal best-of list of souvenirs from Las Vegas. It’s ordered by time – first session first, and all of them are top notch state-of-the-tech speakers, no quality comments necessary. It’s always a privilege to meet you, thank you all for sharing your experience!

Linux/Unix tools for the DBA

by Tim Gorman

  • sar -w context switching
  • sar -W (virt mem swapping)
  • sar -B (virt mem paging)
  • sar %steal = time stolen by hypervisor
  • sar = sadc (collector) + sadf (formatter)
  • mpstat (per processor stats)
  • mpstat column icsw (involuntary context switches)
  • ipcs interprocess comm data structures or resources
    • shared mem
    • semaphores
    • message queues
  • Oracle sysresv = IPC resources for $ORACLE_SID
  • pmap = process mem mapping display
  • pmap -x for all oracle processes / max of shared plus sum of the privat comp = mem oracle uses
  • jstat = Java VM mem usage displays
  • jstat Stop The World STW means cleaning up old objects due to dependencies / inheritance chains (vs. young ones w/o stopping)
  • jstat -gc (FGC column -> STW events since process start)
  • dtrace – very powerful performance analysis tool
  • adb – obtain stack trace from core dump
  • Oracle alter system dump
  • Oracle sosreport
  • Oracle OSwatcher

Read more…



Oracle SQL showing current expensive queries plus most expensive object from execution plan

Sometimes, it’s useful to see what’s currently going on in your Oracle Database. This query is meant to help with this task, that haunts every DBA from time to time. It’s just using v$sql and v$sql_plan, so no need for Enterprise Edition and Diagnostics Pack.

Key features:

  • find top buffer getters per execution from v$sql
  • add most costly object access from corresponding execution plan (from all cursors in question), using v$sql_plan
  • shows possible duplicates of the latter with same cost in a list aggregate, to reduce number of lines
  • shows the percentage of how much cost impact the most expensive object access has. related to the overall cost of the execution plan.

Exclusions:

  • does not show plans without SQL and no SQL without plan
  • has a limited view on object impact: Accessing objects is rated, but not that they cause bigger join costs etc.

Read more…



Looking forward to speaking at COLLABORATE16 IOUG Forum

Yes, I did it again, submitted and got two papers accepted – speaking at COLLABORATE16 in Las Vegas (#C16LV) is always a highlight of the year!

Collaborate16_Horizontal_Logo

This time, it will be:

And, what I’m very thrilled to do again – helping with the Sunday RAC Attack Workshop as a ninja. We own the night! :)

Please see details of the talks here, and follow my #C16LV posts on twitter!

Big Thanks to IOUG, and see you in Vegas!
Martin Klier



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.