Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Oracle Locator Express

If you do much work with the Oracle database on Windows, and you have 1+N Oracle homes installed, you've probably lamented the fact that the Oracle Home Switcher is no longer included with Oracle.

I can't recall exactly what the tool was called or which version Oracle was at when it ceased to be a part of the installation. I do know that it doesn't work with 10g+.

A little tool called Oracle Locater Express fills this niche nicely, and it does work with 10g. Sorry, have not yet tried it with 11g.

"Oracle Locator Express"

I've used it for several months without issue, save one minor glitch.

Sometimes Oracle 10g Homes are not displayed properly in the list of Oracle Homes to choose from. Other than that, no complaints


A good day today. I was privileged enough to be at the paper selection day for the UKOUG conference in December 2008. For those who don’t know what happens, and perhaps suspect some sort of elite giving themselves presentation slots, here is roughly how it works. 
Firstly a reasonably large group of reviewers from around the world, [...]

hope for the future

So there I was this morning sitting on the tube (UK underground rail system) when a young student got on an sat down beside me. Obviously being both English and a commuter I couldn’t possibly speak to her!, but I did sneak a look at what she was reading. These turned out to be the notes she had made on her course that she was now going over presumably in preparation for exams. The notes this morning were on normal forms, entity relationship and data modelling. 
So there we go, a young female student of IT would be pretty good in itself, the fact that she was studying relational theory was the icing on the cake. There is hope ladies and gentlemen, there is hope.  

The 3 step program

Richard Foote updated his blog today with a description of the 3 step process for troubleshooting technical problems with business systems. Briefly his 3 steps are

  1.  Identify an actual problem that needs addressing, one that’s problematic to the business, not one that only exists in some statistic or in one’s imagination
  2.  Determine what’s actually causing the problem as identified in Step 1.
  3.  Address the specific issue as identified in Step 2.

This started as a comment, but grew a bit. I suspect that most of the time the ‘difficulty’ lies in step 1. Identifying a problem that is causing drag on your employers business. This requires at least: 

  1. understanding the business in the first place.
  2. specifying to a high degree of certainty the issue.
  3. quantifying the impact.

IT staff are notoriously bad at 1) and 3) and business staff are notoriously bad at 2) and 3). For example some colleagues of mine went to a meeting with business users of a core system that has historically suffered significant downtime. We identified and made some infrastructure changes that have reduced the downtime by approximately 40 days a year (that’s right this system was running at circa 80% availability).  The system has been running in it’s new configuration at over 99% availability, and helpdesk calls have all but vanished. The meeting was quite difficult since the business users wanted to complain about the stability of the system. In particular they were upset with the 99% availability statistics because they felt that the stats did not reflect reality, which was that occasionally data was ‘lost’ or application sessions were apparently hung. The fact that other users could continue to work did not mean that the service was available.

This illustrates particularly well my point 2, the technologists involved had understood a problem statement "the system is often unavailable" in terms of the uptime of the application – i.e Can I log on? The business users on the other hand interpreted the exact same statement as meaning "we often encounter unexpected errors when using the application". 

More WAF please…

I’ll get to the point in a minute…  If you’ve spent any time “working” on setting up a home theater in your home, you’ve come across the WAF acronym in many of forums — it stands for Wife Acceptance Factor — and it is a cautionary tale of making sure you keep your setup easy enough so that you don’t end your marriage over your wife’s inability to enjoy Desperate Housewives because she doesn’t like programming the remote :-)

Back before HDTV and satellite TV it was pretty simple here in the US to set up and watch TV.  Generally there was only 1 cable involved (2 if you count the power cable).  If you bought a cable-ready TV you simply connected the cable from the cable company (RG6 coaxial, which carried both audio and video) into your TV and you were in business.  Moving TVs around your house was pretty simple too.  Oh, maybe you had a converter box to watch some pay channels, but for the most part you were good to go — with maybe 50-75 channels in the larger markets.

Things are getting a bit easier lately, but we’ve had a run of complication which I think has seriously slowed the adoption of HDTV and quality sound — the split of audio and video cables — the larger number of cable choices audio-only (optical, coax, patch); video-only (patch, coax, dvi) and mixed (hdmi) along with wonderful new things to learn about like HDCP and DRM.  None of which has made it any easier to make a TV have a good WAF anymore — let alone move them around your house.

The reason I bring this up is due to the shear number of software offers I’ve been getting lately for products that don’t seem to have a compelling ability to simplify things for me or my customers.  They claim to be “better” at some esoteric task, but at the cost of introducing another specialized skill requirement into my customer’s infrastructure.

I got into an interesting discussion with a virtualization consultant the other day who responded to a customer’s concern about the I/O performance of a database on VMWare by installing Virtuozzo for a special system.  I asked why they did that instead of looking to tune the database I/O or maybe scaling up the VMWare hardware or (gasp!) running the database on a dedicated server.  He replied that he recommended Virtuozzo because they wanted everything virtualized and that they didn’t have budget for tuning or new hardware.

Heck, I like Virtuozzo as a virtualization solution and even I thought this solution was all kinds of crazy.

Why add to the customers’ complexity by introducing yet another virtualization technology instead of helping them reduce complexity while still meeting their needs?  The only beneficiary to this appears to be the consultant who can charge fees to maintain this specialized system.  Or maybe the new employee they had to hire to learn and handle this special system.

Personally I got even more upset when I heard that the customer was a public school system — like a public school system needs internal IT complexity instead of simple, reliable systems that do the best job for them.

One-off’s like this are always a challenge — make sure you have a process by which you approve, manage and judge such efforts — understand when one-offs become your new direction, or when they need to be brought back into the fold.


Do you hate arbitrary requirements?  You know the ones — like: the customer account number must be a 10-digit number without any leading zeros and no more than 3 repeated digits?  Don’t you always try to argue the user back into letting you use a simple sequence generator — maybe giving in on the leading zero requirement, but arguing against trying to make sure there aren’t 3 of the same digits in a row? :-)

Maybe if you thought there was a good reason, or authority or research on why that requirement was a good idea, then you’d see it as an interesting challenge rather than a burden?  Maybe if you read a post by Seth Godin about it?

Wonder if the user thinks that some of the database limitations are arbitrary?

So, today’s challenge — implement a stored procedure to generate such a serial number…

More Mass Market Oracle

Almost exactly one year ago, I wrote about the lack of hosted Oracle database packages.  Since then I haven’t seen much movement on that front, until last week, when STRATO AG, a German hosting company started offering a STRATO Oracle Server hosting package based on Parallels Virtuozzo Containers and Oracle Express Edition.  Here’s the press release.  I’m having a bit of trouble finding the package on the STRATO site, but if it’s true it’s another valuable option out there for ways to increase Oracle usage at the low end (where a lot of innovation takes place).  I’m curious how STRATO is handling the Oracle licensing issues…

So, there’s two posts, guess I’m on the blogging bandwagon…

As the subject says, there’s my first two real posts, so, I guess I’m blogging.  I won’t guarantee how active I’ll be here, or how much of what I write will be Oracle, as opposed to other stuff, but, for what it’s worth, here I am.

11g is more deadlock sensitive than 10g?

I ran into a situation over the weekend, where an application and schema, which were stable under, started hitting ORA-00060 deadlocks in, in spite of the fact that no application code changes had occurred.  It seems that 11g was more sensitive to deadlocks in this situation than 10gR2 was.

The situation developed this way.  We are starting to work with 11g.  We have a brand new application that is all new from the ground up, so, we thought we’d give 11g a try, as there is no legacy code base, and it seemed like a good opportunity to get our feet wet with 11g.  Well, we have an RMAN based backup system, so, to be able to backup the new 11g development database, I need to upgrade the RMAN catalog and catalog database to 11g.  When attempting to do so, I ran into an upgrade bug, which, without getting into the ugly details, meant that I needed to restore my catalog database from backup.  Open SR with Oracle, after much going round and round with them, they determined that my best course of action would be to upgrade the 10g database to 11g, and then NOT upgrade the 10g catalog to 11g (which is where I hit the bug initially).  Instead, leave the catalog owned by the ‘RMAN’ user at  Create a new user, ‘RMAN11G’, and create a brand new 11g catalog there.  Now, I can continue to backup all my pre-11g databases to the RMAN user, and when I have an 11g database, I connect to the same rman catalog database, but as RMAN11g, rather than as RMAN.  So, I did the upgrade on Friday night, and everything seemed to go well.  I tried a few archive log backups, just to make sure everything seemed to be fine.

So, that’s a bit of the background.

On Saturday, I start getting pages, backups are failing.  And they’re failing w/ ORA-00060 deadlock detected.  Huh?  I’ve never heard of this happening before.  This makes no sense to me.  A quick look at the backup log, and it appears to happen on catalog resync.  So, I look at a sampling of the trace files, and they all seem to be deadlocking on TM enqueues with mode held ‘SX’ and mode waited on ‘SSX’.  So, this is definitely due to foreign keys referencing unindexed columns.  No doubt in my mind about that.  So, I go over to Steve Adams’ website, and find the script that identifies all the columns used in foreign key relationships that are missing indexes, and I slightly modify that script to generate DDL to create the missing indexes.   I run that, create all the missing indexes, and the deadlock problem goes away.

But, the mystery, in my mind, is why this happened at all.  Note that the deadlocks were happening when a database tried to backup archive logs running version of RMAN connecting to a catalog living in a database.  The same scenario worked fine for months and months, when the catalog was in a database.  So, I upgraded the database, but not the catalog or the RMAN binary that was being used.  That is, the “application” (RMAN binary and RMAN catalog) were not upgraded.

This would seem to imply that 11g is somehow more deadlock sensitive than 10gR2 is?  That strikes me as troublesome….and definitely concerns me, if that’s really the case.  I’m not sure I have enough information to prove that this is the case, but I’m definitely cautious and suspicious, at the moment…..  It wouldn’t be the first time that an Oracle upgrade exacerbated a situation, rather than improve it….

Bit of a stumper….

Well, I finally decided I have something noteworthy to blog about.  This was a bit of a stumper, that we ran into the other day….I did finally get to the bottom of it, and I thought it worth a mention, here.

We have a three node RAC running on DL-585s.

This is a reasonably busy system, but, these boxes have lots of horsepower, so, no serious I/O or CPU bottlenecks are observed.  We seem to be humming along when we hit ORA-257 (archiver error, connect internal only until freed).  I think “Ok, the archivelog backup process is failing to run, and the archivelog area is full”.  But, I find that the 100GB archivelog area is nearly empty.  And we’re still stuck on ORA-257.  What the ….?  Some weird bug, perhaps?  We notice that bouncing the stuck instance frees up the problem…..very strange indeed.

To make a long story short, we opened an SR, uploaded lots and lots of logs, and discovered…..when this server was set up, processes was set to 500, which is too small.  The process table filled up, and at archive time, apparently the archiver spawns a process to talk to ASM  (archivelog area is under ASM), and since the process table was full, it couldn’t do that, so it reports the (misleading) ORA-257 error.  We bumped processes to 1000 for all three nodes, and Voila! Problem solved.

So, anyhow, just thought I’d mention that scenario.  If you hit ORA-257 and your ASM managed archivelog area is not full, think PROCESSES parameter…