Archive for the 'Cluster and RAC' Category

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…



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…



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 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…



Martin Klier in DOAG.tv Interview: Oracle Standard Edition

A while ago, DOAG.tv interviewed me about Oracle’s Standard Edition and database system migrations. The material was published just recently, so I’d like to share it. Enjoy, and if there are questions, just let me know!

2014-Klier-DOAG-Interview

http://www.doag.org/home/aktuelle-news/article/oracle-database-standard-edition-versus-enterprise-edition.html

I have to add a corrigenda: SE RAC is not – at least not at the moment – limited to a number of nodes. It’s currently limited to CPU sockets, to be exact, 4 of them. So a four-node SE RAC is possible, if I did not completely misunderstand the licencing policies.

Disclaimer: Licensing and pricing here are my OPINIONS, and not a reliable source to make decisions or confront Oracle with. :) If you need some tailored licensing information, feel free to email info-at-performing-db.com for advice.

Regards
Martin



Oracle 11.2 PSU2 Grid Infrastructure stack start … failed to complete at crsconfig_lib.pm line 11814

Oh, sometimes it’s just (own or close-people’s) PEBKAC that costs you time and gray hair… Patching 11.2.0.4.x to PSU2 on Linux was such an issue.

Problem

“opatch auto” with all comfort worked well, applied the patch, but re-starting the clusterware failed with:

Starting CRS... 
Installing Trace File Analyzer 
CRS-4123: Oracle High Availability Services has been started. 
Oracle Grid Infrastructure stack start initiated but failed to complete at 
/u01/app/11.2.0/grid_2/crs/install/crsconfig_lib.pm line 11814.

What happens here? OHASD tries to start up CRSD, but it fails with an exception: permission check failed. Mh, might come from most $GRID_HOME/bin/*.bin files have no executable (“x”) flags set. WTF?
So what.. who’s the culprit? Spent a whole weekend on investigating and re-trying, just to learn that the scripts in the PSU won’t change a flag in bin directory. What do the file parameters look like in the unzipped patch directory. The same. Grml.

Wait… The PSU2 README.html says: “unzip the patch as the grid home owner”. How did the patch directory get to the server…? Oh yes, it was unzipped on the downloading machine (automatically). Woe …

Solution

  1. Download the PSU in zipped format
  2. Copy the ZIP container to the system you’d like to patch
  3. “Unzip the patch as the grid home owner”
  4. Don’t find a “failed to complete at crsconfig_lib.pm line 11814” in your console output
  5. Have an early beer for reading correctly, and not a late one for finding your own mistakes

Be an extremely careful reader and stay safe
Martin




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.