The Kudzu Files

Sliced And Diced By Occam's Razor

I got an email from one of our users yesterday - unable to connect to their test database. I have a small universe of test databases. There's the near-clone of the current production database, the near-clone of the previous version of the production database which was upgraded to the current version as a final test of the upgrade procedures; there's the database being used to test the new module supporting the National Emissions Inventory, and a clone of that database being used for performance testing. There's the database being used to test the next version of the application, and the database being used for Oracle10g verification and SQL/XML development. I finally managed to kill off two databases that were being used by two different groups doing separate draft permit development. And those are just the test databases for our primary application. Then there's the primary production database itself, and three databases supporting other applications, and two test databases for those. It's not like we're not that big a place! I need a database of passwords for all the different databases.

So, I received an email from one of the users of one of these test databases. They're in an office in another part of town, and they couldn't connect to the particular test database they wanted to hit. I had them try a couple of things, with no success, so after lunch today I went to that office. Start the application, "cannot resolve service name". Check the database ID, its OK. Ping the server, OK fine. Tnsping the database, it's OK. Try the app again, make sure I saw the error correctly, yes, I did. Change the database ID to a different database on the same server. OK fine. Change back, nada. Telnet to the server, stop the listener, restart the listener. Try the app again. Annnkkkk! Check the tnsnames file. It's good. Fire up SQL*Plus, try to connect to the target database. Nothing. Try to connect to another database on that server. OK fine. Fire up the Oracle net config utility. Test the connection. Can't test it, I'm using a redirect in the tnsnames file. Rats! Fire up the app again, because I can't think of what the problem might be, stall for time while I think. Still won't connect. Not surprised. Check the connection info again, compare the port numbers. OK fine. At this point I'm pretty baffled. I telnet to the server again, decide to connect to the database directly.

The database was down. The database was down. I'd shut it down a week or so ago when I was setting up another database on that server, and hadn't brought it back online. I spent an hour trying all sorts of remedies to various connectivity hypotheses, and the problem was the thing I should have checked first, the most obvious and therefore the simplest solution. The database was down. Occam's Razor. Numquam ponenda est pluralitas sine necessitate. You should never focus on the solutions to a problem before you've focused on the problem itself. Forests and trees.

Numquam ponenda est pluralitas sine necessitate. Indeed.

Netherworld Musings

Inner life of trees
Who knows what they think about
Not databases

Of Interest To Database Geeks

Janus Software has an open-source SQL database product which can be run in what they call "Oracle mode", essentially (according to Janus) replacing Oracle. The database product is called Firebird; the Oracle-mode version is called Fyracle. Since it's open source, it's free, or at least has no licensing costs, and apparently is supported on Linux, Solaris, HP-UX, and Windows at least. They even claim to be PL/SQL compatible:

I won't be suggesting this as a replacement for my Oracle production databases anytime soon, but it might be worth looking at for some applications.

Of Interest To Oracle and Java Developers

Robert Vollman touches on one of the age-old debates in Oracle database development - where should the business logic reside? Oracle developers, and especially Oracle DBAs, tend to want as much of an application as possible living within the database. Java developers tend to want the logic in the application code. Apart from the strict technical considerations (code efficiency and capability), I can think of three issues that might affect the decision.

• Portability, or to use an old term from the 90s, platform-independence: assuming you are wedded to Oracle as your database platform, then capturing the business logic in stored procedures (either PL/SQL or Java) minimizes the functionality of the front-end application, making a front-end rewrite of the application a less threatening possibility.

• Reuse. It's not uncommon to have a mix of client-server, web-client, and reporting-tool applications all hitting the same database. If your logic is maintained in the application layer, then parts of the application logic have to be reproduced in potentially all three front-ends. Sharing stored procedures can greatly reduce this duplication of logic.

• Maintenance: who will be maintaining your application, and how available are the skillsets? If the front-end is written by a consultant team, for a database supported by your in-house staff, then it makes sense to stack as much of the business logic as possible in the domain known and supported by your in-house staff - the database itself. You don't want to have to start writing new contracts every time something needs to be tweaked. If it the application is completely written and suported in-house, the equation can change.

So I suppose there's no correct answer. As a DBA, I tend to lean towards using the capabilities of the database to the utmost, which means capturing the business rules in stored procedures. It's not as much fun for the Java guys, or the C# guys, but makming them happy isn't in my job description.

Oracle Backups

Jeff Hunter's Backup Top 11 rules are worth reading if you're responsible for Oracle databases. As is this from one of the comments:

"The responsibility of the dba is not to backup the database, but to restore it."

Paranoia is a useful trait for an Oracle DBA. Very useful.

Why Do People Hate Oracle?

Well, for starters, not everybody hates Oracle. I don't - it's been a nice career builder for me. But some people apparently do. Philip Howard of Bloor Research writes:

"I have on my bookshelf a book called "Why do people hate America?" by Ziauddin Sardar and Merryl Wyn Davies, which explores the cultural and other reasons why large segments of the world - in the Middle East, the developing world and Europe - really do hate America. It occurred to me that while there are a good many people that loathe Microsoft, this is typically at the individual user level, whereas it is Oracle that tends to be the target for competitive vendors."

Read the entire article here.

He makes some valid points. Oracle is good at what it does, but it's expensive. And it's become ever more massive and complicated to install and run. And while the database itself has always been solid, I've long felt that their front-end tools (now called Forms, Reports, and Discoverer) were clumsy, too expensive, and just simply not up to the level if competing tools. It's difficult, however, to see Microsoft, IBM, or Computer Associates making much headway against Oracle in the foreseeable future. There's just too much invested - money, resources, skillsets, deployed applications - to see much change coming. With databases, Oracle is still the Borg.

Oracle un-tarred

In my early days of Unix, 1985 or thereabouts, when I needed to create an archive I used cpio. I don't really remember why I used cpio instead of tar. Tar seemed to be the ubiquitous Unix archiving command. I ran across few fellow cpio-ers. So at some point, I gave in and started using tar. I rarely use cpio now, and almost never run across a reference to it. So I find it curious that Oracle, in the midst of distributing their patchsets for Oracle 9i (9.2), suddenly switched from distributing as tarballs, to distributing as cpio archives. It's like a flashback to the Reagan Administration.

Life Unscripted

No, this has nothing to do with TLC. Sometimes, you'll find yourself in the midst of a day that's just nice. You feel good, there are no crises, birds are singing, happy little forest creatures are, well, happy. And then, of course, you find out how foolish you are to think that the day was nice. This day became one of those right before lunch, when one of our mid-level managers walked in my office and asked if there wasn't a way I could restore the database to the way it was at about 9:30 this morning. Well, yes, there is, of course - that's part of what you pay for with Oracle. But it definitely wouldn't qualify as painless. He and his partner in crime had fired off a new procedure to do some updates, without really testing it first. It careened it's way through the database like a car that's lost it's steering in a crowded marketplace. Fortunately, I was able to restore the corrupted tables without having to roll the database back. Unfortunately, that process took until 5:30 this afternoon, which means that in addition to missing lunch, I also didn't get several other things done that needed doing. And since most everyone was gone by the time I was finished, there was no one around to proclaim me a hero. Such is life....