Archive for the 'Oracle' Category

Speaking at IOUG COLLABORATE 12: “Resolving child cursor issues resulting in mutex waits”

On April 22-26, 2012 the Independent Oracle Users Group (IOUG) is holding the COLLABORATE 12 forum in Mandalay Bay Convention Center, Las Vegas (US-NV).

The COLLABORATE is a widely known event in the Oracle Community, and attracts lots of Oracle guys and girls from all over the world. I feel honored to contribute a presentation about how to detect and resolve child cursor issues resulting in mutex wait events. It will be Lesson #893 as part of the “Oracle Internals & Performance Bootcamp”, which is maintained by Craig Shallahamer. Here comes the official excerpt from the IOUG session planner:

 

Thursday, April 26, 2012

#893 – „Resolving child cursor issues resulting in mutex waits“

Read more…



DOAG Conference 2011 – Impressions and Look-at’s

Once again this year, the German Oracle Users Group has its annual conference and exhibition in Nuremberg. (DOAG Konferenz und Ausstellung 2011, Nürnberg). Being there is nearly a must for Oracle guys in German speaking countries.

As usual, here comes my unordered, incomplete and ad-hoc list of things I wrote down to have a closer look at in the next year, coming up during or from random talks I attended.

Day 1 (Tue 15.11.2011)

  • AVG_ROW_LENGTH of a table vs. Blocksize
  • CHAIN_CNT
  • analyze table X validate stucture cascade
  • Linux: Transcendent Memory
  • Linux: CleanCache and zcache
  • Linux: Cgroups
  • Linux: Transparent Huge Pages (wow!)
  • Linux: DTrace
  • Linux proprietary: Ksplice
  • View: registry$history for the REAL version number
  • Rolling Upgradable patches means minimal downtime on on Single Instance DBs, in combination with Out-of-Place-Upgrade)
  • Bug 10187168 in PSU 11.2.0.2.2 (_cursor_features_enables=1026
  • Typeset conversions: CSscan and DMU utilities
  • AIX patch following note 1246995 (Memory Footprint)

Read more…



Oracle Clusterware 11.2: ASM crashes at startup

These days, a customer’s Oracle Clusterware (2 nodes) crashed one ASM instance at every startup.

More Facts:

  • It was not possible to start it manually, too.
  • The CSSD was running.
  • For obvious reasons, CRSD did not start.
  • The other ASM instance in the cluster recognized CLUSTER RECONFIGURATION for a short period of time.

The ASM Alert Log file looked like:

Sun Nov 13 13:44:08 2011
 MMNL started with pid=21, OS id=7783
 lmon registered with NM - instance number 2 (internal mem no 1)
 Sun Nov 13 13:46:05 2011
 System state dump requested by (instance=2, osid=7684 (PMON)),
         summary=[abnormal instance termination].
 System State dumped to trace file /u01/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_diag_7706.trc
 Sun Nov 13 13:46:05 2011
 PMON (ospid: 7684): terminating the instance due to error 481
 Dumping diagnostic data in directory=[cdmp_20111113134605], requested by (instance=2, osid=7684 (PMON)),
         summary=[abnormal instance termination].
 Instance terminated by PMON, pid = 7684

Strange problem. Looking up device permissions, read write tests, rebooting the cluster in a downtime window – nothing.

To make a long story short: The NTP daemon did not get his time synchronisation, but was running. Thus, CTSS was in observer mode, and server time started drifting apart. Fixing NTP, fixed the cluster.

Nota bene
Martin



Oracle: Manage the lifetime of trace files et al. with ADRCI (purge interval)

Oracle Database 11g and above offers a great feature by managing its trace files by itself. In combination with the new “incident” system, a tool was necessary to handle those without enterprise manager: ADRCI. It has several other cool functions, such as a tail-like log file display, but today I just want to show a very short reference for the lifetime management of trace files and their friends.

1. Basics

The file lifetime for alerts, traces, incidents and dumps is calculated staring from the last modification done by Oracle. For example, a trace file can be used for hours (and days), but it will be deleted X hours after creation. Following this logic, a running logfile like alert.log will never be purged.

Keep in mind, the way how to read and specify times is confusing: They show in hours, and you configure in minutes.

2. Look up current configuration

Of course it’s useful to see how we are confugured. SHORTP_POLICY is for trace files and other things generated automatically. Incidents and other stuff done by purpose is cleaned following the LONGP_POLICY. The other fields (there are more than I am showing here) should be self-explanatory.

Read more…



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…



Oracle ASM / CloudFS licensing policy

Sad to see, at least from June 1st 2011 the new Grid Infrastructure ASM features “ACFS” (ASM cluster file system) and “ADVM” (ASM dynamic volume manager) are now licensed as “Cluster FIle System” (ClusterFS).

As far as I can see from the Technology Global Price List, it’s US$ 5000 per CPU, US$ 1100 perpetual. Alternatively, it’s US$ 100 per Named User Plus (NUP), minimum licensing 25 users, US$ 22 perpetual.

Citation from the Features and Editions page:

A restricted use license to use Oracle Cloud File System is included with all editions of the database specifically for storing Oracle Database-related configuration files, including Oracle Database software binaries and homes, Oracle Database software administrative files, and Oracle Database software diagnostic files. Customers wishing to store their own data files, or non-Oracle Database software files including data files, binaries, administrative files, and diagnostic files, in Oracle Cloud File System must separately license Oracle Cloud File System.

Well, another good feature becoming expensive, it’s a figures-driven company. :)

Yours
Usn

PS: No official Oracle figures here, just for orientation.



Oracle Performance Firefighting with Craig Shallahamer (Frankfurt, April 2011)

In early April, I had the honour to attend Craig Shallahamer’s Oracle Performance Firefighting and Advanced Performance Analysis courses. It was a great opportunity, since he came to Frankfurt which is just a three hours drive away. It was the second time meeting him – and it was great, again. The last two days have been a bit dry by their theoretical nature, and the last exercises on friday are only of limited use for me. But especially the first three days of firefighting have been really useful and most valuable due to their basic technology insights and the teached strategies “how to be sure”.

Craig in Frankfurt 2011

Craig Shallahamer in Frankfurt 2011

Read more…



Terrifying Execution Plan with cost of 18E (exa)

The newest piece of my bloomer collection is a query with an execution plan that has 18E of cost. I am no believer in absolute cost numbers, but this one is just nice.

This exa-executionplan is the new definition of Exadata….

Keep your fingers off ;)
Usn



DOAG Konferenz 2010 – Look at’s

For the last three days, I have been in Nuremberg, attending the DOAG Konferenz 2010 (annual conference of the german Oracle user group). This year, my list of “Look at’s” is way shorter than last years list was. It’s not the fault of the event, I just did not write down that many facts.

My general impression was, that the number of talks intersting for DBAs was limited, but all talks I attended have been of high technical quality. Speakers may be more or less gifted, but I am enough of a technican to read behind the presentation to appreciate it.

Here’s my list. Not comprehensive, but this is my basic “lessons to take”:
Day 1:

  • Carefully decide how far to rely on RAT (Real Application Testing) feature, it’s not the Golden Bullet.
  • Once again, Oracle changed an established name: FRA is now “Fast Recovery Area”
  • Use RMAN’s “configure for” command in a dataguard environment
  • Use auto-delete for archived redo logs
  • Calculate your next migration with Oracle GoldenGate licensing
  • Son Of A Preacher Man: stay an IOPS evangelist, if you happen to be a DBA

Day 2:

  • Dive deep into SQL*Net / TNS / tcp optimization. Thanks to Dr. Mensah for the enlightenment!
  • Oracle now SUPPORTS the RAC on VMware. No certification, but at least something to take home.
  • Dear Larry, please make Oracle-at-VMware licensing accept VMware-side CPU assignment, FINALLY!
  • Most constraints can have an optimizer upside! Thanks, Mr. Senegacnik!
  • 11.2.0.2 has lots of new unexpected features. Unusually. Both in RAC and single instance. Nice!

Day 3:

  • Immediately look at the 11.2.0.2 “hang manager”. Thanks, Markus Michalewicz!
  • Try a $ORACLE_HOME in ACFS on your next RAC test, looks cute.
  • Oracle Clusterware process architecture is confusing, but changes worse in 11.2.0.2, for the first glance. Docs are still a mess, but Dr. Grebe unwired lots of them. Maybe the highlight of my conference 2010!

to be continued…

Best regards
Martin



Oracle Database patch set 11.2.0.2 is out

For all who may concern ;)

Oracle 11.2.0.2 is out for Linux x86 and Linux x86_64

It’s patch set 10098816!

Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g release 2, patch sets are full installations that replace existing installations.

Have a look at Document ID 1189783.1 please.

EDIT: Being withdrawn within hours after publishing, the patch is back available now at support.oracle.com.

Be careful!
Martin Klier




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.