Archive for the 'Cluster and RAC' Category

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 */ * 
  order by snap_id desc, instance_number desc;

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

FROM TABLE (dbms_workload_repository.awr_report_text(

Read more…

Martin Klier in Interview: Oracle Standard Edition

A while ago, 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!


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 for advice.


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

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


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


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

Oracle Clusterware issue: USM driver install actions failed (oracleoks.ko)

As I already said in my last post about “Can’t install ohasd service“, setting up Oracle Clusterware on SuSE Linux Enterprise Server (SLES) SP2 should work flawlessly, but sometimes it does not. :) This time, it was about the USM drivers.

USM driver install actions failed
/u01/app/grid/11.2.0/perl/bin/perl -I/u01/app/grid/11.2.0/perl/lib 
/u01/app/grid/11.2.0/crs/install/ execution failed

USM drivers are components (Kernel object files, extension .ko) enabling ACFS – I don’t use it on this system, but (in fact, needs a decent directory structure related to the Linux Kernel version: Again, the log file “$GRID_HOME/cfgtoollogs/crsconfig/rootcrs_<hostname>.log” was my friend: It unveiled, that the problem was somewhat related to loading oracleoks.ko. And this file is in directory “$GRID_HOME/install/usm/Novell/SLES11/x86_64/<your-kernel-version>/default/bin”. Trouble is, that good old SLES 11 SP2 has a Kernel that was not foressen by the Oracle folks implementing this piece of software.

Read more…

Oracle Clusterware fails: Can’t install ohasd service: Inappropriate ioctl for device line 5427

Setting up Oracle Clusterware on SuSE Linux Enterprise Server (SLES) SP2 should work flawlessly, but sometimes it does not. :) It turned out that this would become a pair of blog entries. Second one is about “USM driver install actions failed (oracleoks.ko)“. But step by step. On Saturday morning, failed with the following error:

Failed to install ohasd startup script, error: Can’t install ohasd service: Inappropriate IOCTL (I/O-Control) for device

Can’t install ohasd service: Inappropriate IOCTL (I/O-Control) for device at /u01/app/grid/11.2.0/crs/install/ line 5427.

/u01/app/grid/11.2.0/perl/bin/perl -I/u01/app/grid/11.2.0/perl/lib -I/u01/app/grid/11.2.0/crs/install /u01/app/grid/11.2.0/crs/install/ execution failed

There are several “My-Oracle-Support” (MOS) entries (bug notes and documents) for failing in, but not for line 5427 – and the line really matters! This script does a lot, and usually different things in different lines. :)

Whenever dealing with malfunctions, the rootcrs logfile ($GRID_HOME/cfgtoollogs/crsconfig/rootcrs_<hostname>.log) is your best friend. It appears in a not-too-verbose style, and if invokes OS- or third party commands, it quotes those outputs in a useful way – Bravo Zulu for the Oracle scripters here.

In my particular case, the problem was related to Linux’ insserv command, thats used to integrate ohasd into the SYS V startup script structure. My IBM Storage Manager Agent (service SMagent) and Oracle’s Trace File Analyzer (service init.tfa) had a dependency loop (dumbass SMagent depends on $all, /*NO COMMENT*/). In my case, I happily removed the $all dependency, and off it went.

Good luck with your GI

Oracle Grid Infrastructure: How to recover from a messed up ASM/CSSD diskstring

Oracle Grid Infrastructure 11.2 with voting files and OCR in an ASM diskgroup can be a little tricky if you mess up the voting file voodoo. You know the basic situation?

With Oracle Grid Infrastructure aka Oracle Clusterware, we are storing your cluster quorum and config repository (OCR) into a disk group. But CSSD needs the voting files before ASM is online: At its startup, CSSD scans the device headers of every device in the disk string (configured by you at ASM initial setup time). If it can find at least two valid voting files, the party takes place. Otherwise, your CSSD will cycle with appropriate error messages in $GRID_HOME/log/hostname/log/cssd/ocssd.log for each loop.

This is where I did find myself today: I changed the ASM diskstring to an insane value, and whoops – at next reboot, my Node1 cycled its CSSD forever in a few minute’s interval, and Node2 was caught in a rock solid reboot loop. Looking up the CSSD logfile, I saw that the CSSD had trouble identifying its voting files. (In fact, there have been multiple devices pointing to the same physical device. Interestingly, thus, CSSD dropped both of them. But this is not the issue of this post.)

Now, tell me, how do you change back the ASM disk_string parameter without having ASM running, and with no CSSD available, which is necessary to start it? And how do we tell the CSSD, that’s running fairly in advance of ASM, to scan the right devices?

Read more…

Lecture “Highly available Communications Server” (Oracle Database) at DOAG conference 2007


I’ll give a lecture about high availability with Oracle RAC and Dataguard at DOAG Conference 2007 (annual conference of the German Oracle User’s Group). The talk will be in German. (“Ein hochverfügbarer Kommunikationsserver mit RAC, ASM, redundant shared media und DataGuard”)

Thursday, November 22nd 2007

CCN CongressCenter Nürnberg Ost
D-90471 Nürnberg
Room: “Riga”

Presumably it will be lecture track 7, but this may be subject to change.

Klier_HochverfuegbarerKommunikationsserver (paper)

Klier_HochverfuegbarerKommunikationsserver (slides)

RAC Howto (German) (.pdf)


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.