Category Archives: Technical Article

Oracle & Open Source Projects – The Interviews

Back in 2000 I recall searching for Open Source projects to cover in the book I wrote Oracle and Open Source. My co-author and I found seven web-based applications, a few with Perl and Tcl, four Java tools, and five GTK applications. A search for the keyword “Oracle” on freshmeat a popular Open Source project indexing page, now yields an incredible 184 projects. Granted some may be libraries, or supporting components, but its a heck of a lot of activity.

This story is really about tinkerers, folks that like to play with technologies, and offer up their creations to everyone. Some

end up with serious projects on their hands, and a large following. When Oracle ported to Linux, it was a moment for the tinkerers to get busy.

We’ve managed to contact the authors of six Oracle Open Source projects, and ask them a few questions about their projects, and how they got started. The authors include:

Ljubomir Buturovic, author of gqlplus

Itzchak Rehberg, author of OraRep

Jeff Horwitz, author of extproc_perl

Paul Vallee, the author of

Tim Strehle, author of the OracleEditor

Clausen Yngve, author of ora2html

In 1998 Oracle released it’s Enterprise database for Linux. Truth be told, there were a few of us out there trying to get Oracle 7 SCO binaries working under a Linux emulator and had some success. Nevertheless rumors were running around on the Oracle DBA email lists that inside Oracle, porting had already been finished, and it was just a matter of time. So October of 1998 was an exciting moment, for it meant that tinkerers could start really getting their hands dirty with Oracle. And that they did. Around that time I started an Open Source project called Karma solely to monitor Oracle databases. It got some attention but further, drew me into Open Source development, where before I had just been an enthusiast who utilized the tools and applications everyday. Then in 2000 I started to collaborate on a book for O’Reilly entitled Oracle and Open Source. This was released in April 2001, and brought my attention to the growing world of Open Source applications and tools for Oracle.

With that introduction, here are the six questions we asked each of the authors.

1. Some people got their start with Open Source at a University, doing development with gcc, or using other GNU tools, and naturally gravited to Linux when the project began. When did you first get involved with open source software, and what were your first experiences?

2. When did you first decide to start your own open source project and what motivated you most?

3. In your own words, what does you application, tool or library do? What are it’s primary or outstanding features?

4. How did you get involved with Oracle? Were you using the Oracle database before Oracle ported to Linux? As a developer or DBA?

5. Where do you see your development efforts with Oracle moving in the future? What would you like to see happen to your project?

6. What might you like to see Oracle doing more of to encourage development of Open Source applications?

Ljubomir Buturovic, author of gqlplus answers the six questions as follows:

1. I first started using Emacs and gcc in 1992, doing research at Boston University. Later on, as a UNIX developer in 1995, a colleague told me about a free UNIX variant you could run on your own home computer. That’s how I got involved with Linux.

2. I wrote this tool (gqlplus) because I needed it to improve my work productivity. Then I decided I should release it for two reasons:

– it is a relatively simple utility which would not be too difficult to maintain

– a couple of colleagues told me that they like it and that other Oracle users may benefit from it

3. In a word, gqlplus is sqlplus with command-line editing, name completion and command history. As most Oracle users know, sqlplus is somewhat inconvenient when it comes to repeating past commands, correcting typing mistakes, issuing slightly different queries etc. gqlplus alleviates this problem by incorporating SQL command editing similar to UNIX shell (bash/tcsh). In addition, it also has table-name and column-name completion, so you don’t have to remember long or cryptic names. And it’s a drop-in replacement for sqlplus, meaning (almost) everything works exactly the same as sqlplus, except that there is the new added functionality.

4. I’ve been using Oracle on various UNIX system since 1996 as a developer. I have only recently – 2004 – tried running Oracle on Linux.

5. I would like to see Oracle incorporate this functionality in sqlplus, which would make my project obsolete :-) Not that I don’t want to maintain it, but there are some functions which can only be supported by changing sqlplus internally, so that’s what I would prefer Oracle to do.

6. I would suggest continue improving support for a wider range of Linux distributions, for example Fedora. Right now Oracle installation on Fedora is decidedly non-trivial exercise which may discourage some potential users

Itzchak Rehberg, author of OraRep had this to say:

1/2. That is already about 10 years ago, and I was running OS/2 at that time. I missed some little utilities, so I decided to write them on my own. Since I was using the script language Rexx for this, these utilities became Open Source as a side effect.

After having started developing my own tools this way, I found Open Source very useful: I could take ideas and even routines from other projects easily without any “permission” or license problems. I was using other Open Source programs, and this way could give back something to the community. So I decided to take the GPL for all of my developments, as far as possible.

Meanwhile I switched to Linux some years ago, and mainly develop in PHP. I ran and run multiple projects for different purposes, all of them are using the GPL. A list of programs/scripts and more details on them you can find at:

– (my private and personal site)

– (my business site)

3. Since I had to answer this question for more than one or two applications, please refer to the links quoted for question 1.

4. I was working for a company about 5 years ago, mainly as a developer for PHP based web applications. Since the company was running not only MySQL (I was already used to), but also Oracle databases, I got involved into that. First by writing PHP applications that accessed Oracle databases, together with the problem setting up PHP accordingly. Later on, I became the Oracle DBA for the company by running through the Oracle certification program (becoming an OCP DBA). That was after Oracle was ported to Linux: While the production databases where running under Solaris 8, some of our test databases have been set up under Linux.

5. Not easy to answer globally. For me personally, I would like to see an easier integration of Oracle into PHP, e.g. by providing an appropriate module that would be easy to plug in. At the moment, it is quite tricky to set it up, and you can’t do that easily with an RPM paket or the like but rather have to re-compile everything from the scratch. If the PHP setup for the Oracle connections would be as easy as copy a few files and add a line to the php.ini (i.e. setting up an extension), I would provide Oracle support to more of my applications, which right now only support MySQL and PostgreSQL, which belong to the “shipped” PHP contributions.

6. See above. PHP got quite common for web development. Oracle databases are quite common in commercial environments. To bring both together, and encourage developers to support Oracle databases with their PHP/database driven applications, an easy integration of Oracle into PHP would help a lot!

These answers are from Jeff Horwitz, author of extproc_perl.

1. That’s exactly where I got started. I was a student programmer at the University of Michigan when I was first exposed to open source software. At first I was limited to just downloading, porting, and compiling various packages, but in time I began writing my own software. In 1997 I wrote my first open source package, Authen::Krb4, which provides a Perl API to Kerberos 4.

2. Each project is different, but I assume you want to talk about extproc_perl, which is the only Oracle project on my plate. As a system administrator, I work very closely with our DBA. Knowing my experience with Perl, he commented to me one day, “Gee Jeff, wouldn’t it be nice if I could use regular expressions in my select statement?” Now, this was back in 2001, long before 10g’s regular expression support. I took that as a personal challenge, and that night, extproc_perl was born.

3. extproc_perl embeds a Perl interpeter in an Oracle external procedure, allowing you to write stored procedures in Perl. Among other things, it supports per-session interpreter persistence, the ability to query/update the database within the existing transaction, and automatic type conversion between Oracle and Perl. The most intriguing feature though, is the ability to leverage the vast number of modules in CPAN (the official Perl module archive) for your stored procedures.

4. I’ve been a system administrator at a university and two companies that all happened to use Oracle. I think the first time I actually developed anything for it was back in 1997, well before the Linux port. But I’m still just a sysadmin, and I rely on my friendly DBA for most database tasks. For someone who has done what I’ve done with Oracle, I know surprisingly little about it!

5. As I’m involved with several open source projects, my involvement on each of them, including extproc_perl, tends to ebb and flow like the tides. Development stalled on extproc_perl in mid-2004 as I began work on a new project, but I expect to get back into the swing of things for another release in 2005.

6. Get the word out. Publicize projects that use Oracle in an open source environment. Most people are simply not aware of the quality open source projects that are available to them, and rely on vendors like Oracle to tell them how to do X, Y, and Z. That’s all well and good for what Oracle does best, but as we like to say in the Perl world, there’s more than one way to do it.

We interview Paul Vallee, the author of, and here’s what he had to say.

1. Well, at Pythian we’ve been running Oracle on Linux for our customers since around 1999, so our experience with open source software started primarily as a beneficiary. We really love the cost savings and flexibility it provides, not to mention the dramatic pace of feature improvements all along the stack.

2. When we first wrote, it was in support of a single migration and interfacing project for a client. Our contract with the client had us owning the intellectual property we built, and yet we had no anticipated use for the software once the project was over. What a waste! So we decided to open-source it because we felt that that would allow the work to be reused in other shops.

3. Well, is a very simple tool that does a very simple thing: it takes mysql create syntax and parses it, then outputs it back into Oracle-compatible create syntax.

4. I first worked with Oracle when it was Oracle 5 on… believe it or not… PC-DOS! I was a developer tasked with a statistical macrosimulation analysis of defense human resources data and we had Oracle 5 and SAS to choose from. I’m embarassed now to admit that for this purpose, SAS beat out Oracle handily. However, since then I’ve continuously worked with Oracle primarily as a DBA and DBA manager. In 1997, I co-founded Pythian Remote DBA and to this day our primary support focus is the Oracle database.

5. Honestly, I am amazed at how much interest the project receives and how many hits I get on our website directly related to m2o. However, if there’s one bone of contention or disappointment is the ratio of feature quibbles to patches I get – Most of the feedback I receive sounds a lot more like “your software didn’t handle this correctly” and not a lot like “here’s a patch that fixes this Oracle 10 issue for you”. I believe that the average user of open-source software has become less and less willing to contribute back over the years.

6. Oracle could open-source their legacyware. For instance, Oracle advanced replication has been completely rewritten since the Oracle 7 days when it was delivered in wrapped PL/SQL. However – that replication totally worked! No doubt their new replication is better – so why haven’t they open-sourced the Oracle 7 PL/SQL yet? We could be riffing off that and building fancy new things overlaid on top of it. Replication is just one example of software Oracle has shelved instead of releasing. It would be trivial to come up with more.

The following answers are from Tim Strehle, author of the OracleEditor tool.

1. Back in university, and during my first day job, I got in touch with some commercial Unix flavors – coming from Windows (where I developed Microsoft Access applications), this was a scary world.

So I knew there would be a lot to learn when I started working for Digital Collections. Their first programs had been developed on NeXT computers; later they moved to a client/server model with Unix servers and Mac and Windows clients, all software being written in C. But at the time I joined them, their next major software release was to be a web application written in PHP, running on Unix, Apache and Oracle.

The only thing I knew about PHP 3 was its website, and simply from browsing the online manual, you could tell that PHP would be a good choice for developing web applications. Even at that time it supported so many databases, had lots of useful-looking extensions – and the best online language and function reference I had seen so far.

I was able to learn PHP incredibly fast, and while the PHP manual contributed to that success, it helped even more that my boss, Thies C. Arntzen, was one of the core PHP developers and an Apache Software Foundation member. He taught me the foundations and the tricks of the language, and it was fascinating to see how a PHP feature request or bug discussed with Thies resulted in a solution implemented in the next official PHP release. I really appreciate the privilege of having learned PHP directly from a PHP genius. It has been a great introduction into the world of open source software…

2. Like probably most software developers, from time to time there’s a need to build a tool for in-house use. We wouldn’t mind giving these tools away for free, but usually they aren’t polished or generic enough to be useful for others.

In early 2003, while building a new in-house tool, I decided to take the time to make it useful for people outside our company as well. While I was curious to find out what kind of feedback I would receive, my main motiviation was that I wanted to give something back: I’m making a living building (closed source) software on open source projects like Linux, Apache and PHP, so it felt right to contribute some free software myself.

3. My company’s (commercial, closed source) software was running on Oracle on Solaris, HPUX, Irix and AIX; I was developing on Oracle 8 on Solaris. The advent of Oracle on Linux was very exciting for us, it made it so much easier to run Oracle on relatively cheap hardware.

4. I hope that my OracleEditor.php script will continue to attract new users, and perhaps new co-developers – and I love getting suggestions for improvement.

Oracle has proven to be a solid foundation to build upon, so there’s a promising future for Oracle-based tools like mine.

5. For years, you felt like a niche developer when building PHP software on Oracle. This feeling has really changed with the advent of Oracle’s Open Source Developer Center, the JDeveloper 10g PHP Extension and the announcement that PHP would be included in Oracle Application Server 10G. That’s been very encouraging, and all I wish for is that the Open Source Developer Center stays alive and keeps getting frequent updates. Good work!

6. I’m looking forward to seeing what you’re making out of these interviews.

The following answers are from Clausen Yngve, author of ora2html.

1. The first time I laid my hands on anything open source, was around ’96, when I attended a course in OS Design at the University of Tromso (In Norway). To provide us with a safe environment for programming (safe as in not ruining things for other people :-), we were provided with laptops running Red Hat linux. The transition from using win* products was eased somewhat by the fact that the univeristy was running UX terminals, and I had been using them for regular work and programming for about a year in advance. But I’m not going to claim the switch was without failures 😉 I was slightly tempted to convert my home-PC to Linux or BSD at the time, but it just seemed a little bit too advanced for me. And I couldn’t play Doom and Quake running Linux 😉

At the time I didn’t really give much thought to what was open source or not – and I guess I didn’t _really_ start using Linux/GNU/Open Source software seriously until ’98 when I started working as a unix sysadmin. From there on I’ve been using open source software on a regular basis.

2. In early 2002 it started to dawn on me that there were a lot of things I didn’t know about configuration, features and possibilities in the oracle database – and I didn’t have a clue where in the database to look for information about all the stuff I didn’t know about :-) At the same time, I faced the challenge of keeping track of a growing number of databases in my company. Keeping manual track of a handful of databases isn’t too bad, but after looking up version and options information for 50 databases, you sort of loose interest 😉

I eventually decided to create an automated routine for extraction of general database information to help me in my daily work. Inspired by the cfg2html project (Where a few of my friends were involved), I decided to share my utility just in case somebody else would find it useful.

3. My utlity is a small-ish shell script that collects information about configuration and setup of oracle software and databases installed on a server. It logs on to the available databases and runs checks on different aspects of database operation, including memory config, storage utilization, security, installed options, versions … and so on. The end result is saved as an html file for easy browsing.

It’s mainly a utility geared towards DBAs that want an easy method of getting system information. I’ve found it to be a useful source of reference information in administrative tasks like capacity planning, database upgrade/migration, space management and security auditing. It could probably be useful in disaster/recovery scenarios also.

I recommend running it from cron with automated transfer of the end result to a web server. That makes the info easily accessible. I’ve also found the source code to be a good place for cannibalizing parts for other DBA-related scripts 😉

4. I’ve been working with Oracle since ’98 when I started working as a unix sysadm with part-time DBA responsibility. That was on an HP platform. I’m not sure which Oracle version was the first to be ported to linux, but I did try the 8.0 release. I’ve never done much work on Oracle/Linux combinations, though.

5. My project is really just a small utility that doesn’t require much effort to keep updated. These days it’s just updated as a result of discovered bugs, or upon requests. There are still 10g features I’ve omitted from my utility, but until I see a real use for the info, I’ll delay it.

6. Hm. I don’t think I have any well-founded thoughts on that issue. Yet 😉

Tools for the Intrepid DBA

Dear intrepid DBAs, as you go about your day-to-day work, and struggle with requests from every direction take heed of some of these tools and tips and it just might give you enough time for a long lunch, or better yet, a trouble free vacation!

1. Tools to Help You

Take a look at some of these tools, and toolkits. I’m sure you will find some useful technology that will help you with your day-to-day needs.

a. Andy Duncan & Jared Still’s Perl Toolkit

Andy & Jared put together a phenomenal book in August 2002 called “Perl for Oracle DBAs”. All the ins and outs of using Perl with Oracle, are covered. A large part of the book follows the wonderful Perl DBA Toolkit which you can download here:

b. Steve Feurstein’s PL/SQL Unit Testing Framework

Steve Feurstein has authored many books on PL/SQL, Oracle’s first choice for encapsulating code and application logic in your database. Now you’ll have a framework for automated testing of your code.

c. Use Remote Diagnostic Assistant

The first time I ran into the RDA was at a NYOUG presentation a few years back. I don’t recall the presenter, only that what they described seemed to be a tool that I’d always been looking for. I didn’t waste anytime downloading it and giving it a try. This is indeed good stuff.

RDA combines a lot of things into one HTML presentation, from an overview of your system, and it’s environment, to database layout, parameters, tuning information, wait events, and on and on. It is particularly helpful when you come upon a database for the first time, and need to get a birds eye view of in, all in one snapshot. Also, for systems where you’ve been called in to diagnose problems, but for which you nevertheless don’t have direct access, you can ask for the output of RDA and review that at your leisure.

You can find it on Metalink here.

d. Various Other Tools and Applications

There are lots and lots of other tools and applications out there, and here’s a quick rundown of some of my favorites:

Oracle Session Resource Profiler

The Oracle Session Resource Profiler will assist you with understanding what is going on in an Oracle Session.

Readline Wrapper

The Readline Wrapper is one of the greatest tools I’ve discovered recently. I read about it on Howard Roger’s website some time back. It wraps the underlying readline call to provide history and command completion to programs that don’t already have them. SQL*Plus in all the years it has been in use, strangely still has not been brought into the 21st Century. So this tool can help. The 10g version of SQL*Plus may have improved somewhat.


Based originally on phpMyAdmin which was a web-based MySQL administration tool, phporaadmin allows you to modify data in your database, create objects and do various administrative tasks.

Ever wanted to compare two schemas for differences? You could write some convoluted SQL to query the data dictionary, perform various UNION, INTERSECT, MINUS, and JOIN operations, and get some answers. Or you can just download a copy of Oracle Schemadiff and let it do the dirty work for you. Last updated at the end of 2002, it probably lacks knowledge of 10g.

schemadiff Homepage:

Oracle Code Editor

Oracle Code Editor, allows you to view the PL/SQL stored procedures in your database, and provides color editing, and much more.


Want to pound on your database server? Take a look at HammerOra. It includes support for 8i, 9i, and 10g, and provides a TPC-C benchmark test. It can run on Windows or Linux.

Toolkit for Oracle

Here’s a developers toolkit which includes PL/SQL debugger, editor with syntax highlighting, and a schema browser.


Want to log messages from your PL/SQL code for debugging? Want to make sure a rollback doesn’t impede debug messages. This is the package for you. Connor McDonald has a similar tool library called DEBUG.


ProDBA is another developer tool written in Java. It provides an editor, and schema browser.


SQLGotcha eliminates some of the dirty work of tracking down sid + serial# when you want to trace a session. Also works with 10046 trace events.

O Meter

Want to keep an eye on the I/O your operating system is doing. Take a look at IO Meter. It works on all sorts of operating systems, and processors.


Izzysoft offers this tool to help generate HTML reports from Oracle. They also have an OraGen tool for creating new databases, and one for HTML formatting Statspack data.

2. Oracle Blogs

Although weblogs have been a phenomenon taking the internet by storm for quite a few years now, the fire has recently spread to the Oracle space. The first thing you’ll probably do is start visiting these sites, and then bookmarking them, and then checking back every so often to see if your favorites have been updated. But after a while this can be tedius, or worse you can miss new posts on a site that you’d rather not. RSS to the rescue!

RSS stands for Really Simple Syndication and it allows you to “subscribe” to your favorite blogs. Now there are desktop applications that provide RSS readers(, and there are even browsers ( which are starting to incorporate it. Personally I prefer web-based sites such as the google homepage,, or These sites will even work on your mobile phone!

Ask Tom, though not strictly a weblog, is nonetheless an excellent forum to turn to for ongoing Q&A about Oracle technology, and how it all works.

David Aldridge’s Oracle Sponge weblog provides all sorts of interesting discussions, from new features, to odd quirks, and everything inbetween. Expect good technical content, and thorough discussion.

Pete Finnigans site discusses all things related to Oracle security. It also features a very good weblog for day-to-day discussion and updates.

Mark Rittman is another Oracle luminary who I would recommend paying attention to. His weblog discusses Business Intelligence, Reporting, and Data Warehousing with Oracle.

Niall Litchfield’s Oracle weblog mixes plenty of heavy technical Oracle posts, with the occaisional lighter side postings as well.

There are many, many more blogs on topics relating to Oracle. Brian Duffs Orablogs site aggregates all of those blogs, and you can get an RSS feed from him, for a really interesting and up-to-date mix of what is happening with Oracle technology.

Also, Oracle Technology Network now has a blogs homepage which is really worth checking out as well.

3. Oracle Discussion Groups

Howard Rogers’ has a forums section with various discussions on Oracle topics. You can find his site at,com_joomlaboard/Itemid,256/

By far the best email list to watch is the Oracle-L list now hosted on Though you will find inane banter, and the occaisional flame war, by far the bulk of discussions are technical, and some of the brightest and best in the Oracle world post and contribute. Highly recommended!!

Another great email list is Lazy DBA.

And let’s not forget the Usenet groups which have been around forever. These are the two main ones for Oracle.

4. Federated Search

I’m sure you’ve all searched the Oracle documentation from time to time, and wondered which version of Oracle contained which feature or option, and wished you had a way to search all the Oracle documents at once. Now you have just such a search with the Oracle Federated Search. Of course with the documentation moving to publicly indexable section of, google will surely soon be an even better option.

MySQL Disaster Recovery

Like all databases, MySQL needs a disaster recovery plan. In this article we discuss some specific experiences at a client site where disk errors began to cause database problems, and how the disk was replaced, and the database recovery process.

MySQL is a great database, and for this client, 2000 subscribers and an average of 200,000 hits per month, it is more than enough. In our case we are using MyISAM tables, which do not support transactions. What that means is that as soon as you insert, update, or delete data, it is immediately reflected in the database files. Discussions of what context this is relevant and sufficient for your application are beyond the scope of our discussions here.
Discovering the problem

The application first starting showing trouble with errors on the website about sessions. My first thought was the database itself could be down, so I checked for running processes:

$ ps auxw | grep mysql

Seeing that the processes were running, I thought one might be hung, so I stopped and started the database just to be sure. This is a quick process, so wouldn’t impact things much. Besides most frontend users were probably experiencing the session problem, since almost every page you view on the site checks your session identifier:

$ /etc/rc.d/init.d/mysql stop

$ /etc/rc.d/init.d/mysql start

The session problem continued to rear it’s ugly head, so I looked into table checking. First I ran myisamchk on the sessions table:

$ cd /var/lib/mysql/my_db_name/

$ myisamchk activesessions.MYI

The name of the table in this case is “activesessions” and the name of the database for my example purpose is “my_db_name”. Checking on the frontend, the site began to work again, so I thought the problem was solved.
Within fifteen minutes, I’m getting paged by the client again, and realize the problem is not solved, and I’m starting to worry a bit. This is not normal behavior, and I’m worrying about corruption. I shutdown the database, and do a thorough repair of all tables:

$ myisamchk -r -s --update-state *.MYI

I startup the database again, and find that there is still intermittent problems. I’m also starting to check the logfiles. /var/log/mysqld.log and find that the database is crashing, and then being restarted every few seconds. Corruption I wonder? I verify that our backups are intact, then start looking further afield. I check /var/log/messages and find something serious:

Mar 12 01:58:51 www kernel: hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }

Mar 12 01:58:51 www kernel: hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=857785, sector=857712

Mar 12 01:58:51 www kernel: end_request: I/O error, dev 03:01 (hda), sector 857712

Mar 12 01:58:51 www kernel: hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }

Mar 12 01:58:51 www kernel: hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=857785, sector=857720

Mar 12 01:58:51 www kernel: end_request: I/O error, dev 03:01 (hda), sector 857720

Immediately I call support, and discuss the problem.
Replacing the Disk

Luckily the disk is still working well. That means a “ghost” of the disk can be done to a new disk without errors. If the disk had crashed, it would have been a much more difficult recovery. The support folks go ahead and do the ghost procedure after shutting down the machine completely, which is something roughly akin to:

$ dd if=/dev/hda of=/dev/hdb bs=1024

The unix command “dd” does a device to device copy, and will copy every block without change. The support folks may have a hardware solution which does this.
Repairing the Database

After the machine came back online, I shutdown the database again (it starts automatically at boot).

$ myisamchk -r -s --update-state *.MYI

Once that’s done, I startup the database:

$ /etc/rc.d/init.d/mysql start

Problem solved.

Going forward it couldn’t hurt to write some scripts for Nagios to watch /var/log/messages for errors. Searching for the string “UncorrectableError” would make sense. In addition, a script to monitor the results of myisamchk would also be a good idea.

MySQL recovered fine from this brush with disaster, but one may not always be so lucky to have the disk repair be so straightforward. That’s why the dumps of the database, and regular backups are so important. Be proactive, or learn Murphy’s Law the hard way. One way or the other we all run into these issues sooner or later.

Oracle 9iRAC – Clustering on Linux/Firewire


Ever since the announcement of Oracle 9i, Oracle’s Real Application Clustering feature has created quite a stir. For those not familiar, 9iRAC is a complete overhaul of Oracle Parallel Server (OPS) from previous versions of the database into a workable product.

For many DBA’s, however, this technology is completely out of reach. Without an employer who has already committed to OPS and wants to upgrade, or a client who would like to venture into the unknown, there’s no way to get ahold of an environment on which to test it. The lowest entry option for clustering technology has been Fibre Channel. Unfortunately cost is prohibitive.

Enter Oracle’s new Linux Firewire project. To some, this announcement is as exciting as Oracle’s first announcement of a port of their RDBMS to the Linux platform. Through the release of various Open Source software components, such as a modified ieee1394 driver for sharing external Firewire disks, a clustered filesystem (OCFS), as well as a number of other interesting components, this platform is now within our reach at very low cost.

Part 1 – Introduction

Part 2 – Basic Costs + Hardware Platform Outline

Part 3 – Software Requirements, Versions, etc

Part 4 – Initial Oracle Setup

Part 5 – Firewire + OCFS Setup

Part 6 – Cluster Manager Setup

Part 7 – Cluster Database Setup

Part 8 – Review of Clustered Features + Architecture

Part 9 – A quick 9iRAC example

Part 10 – Summary

Part 2: RAC/Linux/Firewire – Basic Costs + Hardware Platform Outline

Basic Costs + Hardware Platform Outline

In my test environment, I bought the following equipment. Note that although RedHat Advanced Server seems to be required, I worked with the development team to get it working without that distribution, and included RPMs. If you want to get a copy, get the developer release. I listed that as well, though I didn’t use it.

  • 2x emachines T2460 $650 each link
  • 2x Inland Firewire PCI card $25ea from Fry’s (includes 6pin to 4pin cables) link
  • 1 Pyro 1394 Firewire cabinet $150 (includes 2 + 1 6pin to 6pin cable) ** link
  • 1 Maxtor 7200RPM 60GB ADA/EIDE harddisk $80 link
  • 1 2meter 6pin to 6pin 1394 cable ($10)
  • 1 copy of RedHat AS 2.1 Developer Edition $60 link
  • 1 firewire hub (only for 3+ nodes) $40-$80

You can use just about any EIDE HD which is compatible with the cabinet you get, and these are just the ones I got, so there is some flexibility in cost. Also, I got this stuff from a Fry’s store when I was in California. They have an online store at Fry’s. I would also recommend checking Sparco online as they have pretty good prices, and I’ve had a lot of luck with them here.

** Arup Nanda notes that you must use a firewire enclosure which has a chipset that supports multi-user. I would suggest

checking Tom’s Hardware Guide for details.

Part 1 – Introduction

Part 2 – Basic Costs + Hardware Platform Outline

Part 3 – Software Requirements, Versions, etc

Part 4 – Initial Oracle Setup

Part 5 – Firewire + OCFS Setup

Part 6 – Cluster Manager Setup

Part 7 – Cluster Database Setup

Part 8 – Review of Clustered Features + Architecture

Part 9 – A quick 9iRAC example

Part 10 – Summary

Tracking the Wily Proxy Hackers

Recently the server that hosts our business was hacked. This interrupted the service of twelve different websites we host, as well as our corporate mail. Needless to say it caused us plenty of headaches, sleepless nights, and frustrating hours. In retrospect, however it has instilled a greater appreciation for computer security, a greater awareness, and further, a stronger perseverence to keep the systems locked down.
Watching the news these days, and sites like Security Focus can be disheartening to say the least. SPAM is at an all-time high, windows viruses, trojans, and malware are wreaking havoc to corporate intranets, and the internet at large, and the situation only seems to get worse. Running a server on the internet nowadays is like opening shop in New York City back in the days of street crime and daily trouble.
Unfortunately some of us in the Unix and Macintosh world have grown a bit too confident. With all of the vulnerabilities being found in various versions of Windows, IIS and Internet Explorer, folks on the other side of the fence figure they have less to worry about. We may have less to worry about, but that certainly doesn’t mean nothing. So here is the story of what happened to us, and what we did about it.
We upgraded our systems in December of 2004, and figuring Mandrake 9.2 was more stable than 10.x we installed that. We spent the time recovering all of our websites from backups, rsyncing things accross the internet. Each website has it’s own document root as well as specific configuration lines in the Apache httpd.conf file. In addition the mail server had to be configured, as well as DNS changes. Lastly once the system was up and running, we mirrored everything on root for redundancy and protection of loss of a single drive. All told we spent about 30+ hours but we were back up and running soon enough. A lot of the bulk of that time was spent moving data accross the internet, and was unattended.
Around the end of January we started seeing some spikes in hits on some of our sites, but didn’t think much about it. A few weeks went by, but generally the systems were behaving normally, but starting to be a bit slow. By mid-February we were starting to have problems. The network we are hosting on was having trouble with bandwidth, browsing, and experiencing outages of their own. We also showed up on the Composite Blocking List and the Spamhaus List.
When that happened it opened our eyes, if only a bit. We knew something was happening which was originating from that network. So we did two things. First we tested our Postfix mail server for Open Mail Relay. We had experienced this a year earlier with a qmail misconfiguration, and since it is quite common, thought this might be the problem. However, we were setup correctly, and that was not the issue. Next, we scanned all of the windows and Macintosh machines on that network for viruses, trojans, and so on. We found a couple of things, and fixed them. We then removed ourselves from the CBL + spamhaus lists.
Once again our mail was flowing out, but a day later, the problem struck again. Being the Unix folks we our, we starting pointing fingers at the Windows machines. Sometimes Norton, MacAfee et al. don’t catch all the viruses. We suspected those pesky windows machines to be the culprit. Many of the malware programs that Windows users unwittingly install on their machines relay spam so that spammers can send email out anonymously. So your windows machine is coopted as a spam host, sending out thousands of messages a minute.
To get around the problem in the short term, we contacted some associates of ours, to relay mail through them. This is different than an open mail relay, since you are specifically requesting permission to send mail through another agent. So we could once again send mail, and our problem was temporarily solved. However, our server got slower, and so did our websites. It got to the point where the network hosting our server couldn’t send outbound traffic, or visit websites. Quite a problem.
The admin managing that network contacted Verizon, the broadband provider, and discussed the problem with their tech department. They suggested unplugging machines on the network one-by-one, until the traffic spike subsided. He proceeded to do just that, and what do you know but when our server was unplugged, the bandwidth usage dropped to ZERO. The support rep suspected we were streaming audio or video files, which of course we were not, so the only obvious conclusion was spam.
What to do, well first hide your head between your tail, and admit that your unix server has been hacked is a start. Next we rebuilt the server with Mandrake 10.1. There were some vulnerabilities in SSH that we were using, as well as Apache, and PHP, so upgrading to the latest Mandrake distro version upgraded all these packages in one go. We broke our mirrored drives, and installed Mandrake on one of them, and the did a disk to disk copy of all the data from /home to the new drive. Once that was complete we started up again, and things were looking good.
Back on the internet, things started slowing down again, so we started monitoring our Apache logs. We saw some strange activity in there, so blocked HTTP at the router, and found the performance problems, and bandwidth problems eliminated. So we knew there was something wrong with Apache. We searched for bugs, but didn’t find anything too heinous. Upon closer examination of the logs, however, we found strange redirects to port 25 on other machines. How was that happening?
Apache has a facility for acting as a proxy. That is it can get webpages, and in fact make other requests of remote machines, and proxy those requests back to an originating source. Imagine standing on a mountain top. You can see to the other side of the mountain, and are reading smoke signals from a village there. You then send those same smoke signals to the next village over. They can read your smoke signals, but don’t know the identity of the sender, only that you’re sending a message to them. You can understand the message, but can’t determine the sender. Proxying with internet based servers works much the same way. In fact the Open Mail Relay we discussed above is exactly that, which is why it’s so important that it be closed.
So we looked over these logs and found strangely that Apache was doing the same thing! In fact Apache was an open mail relay, and open proxy in general. This mod_proxy module came preinstalled with our apache, and though we did not configure it, it was working none the less. So we researched the issue, and found it was not considered a bug. It was in fact part of the software that when configured correctly can come in quite handy. Of course we didn’t need it, so we spent some time disabling through configuration changes in the httpd.conf. Despite these changes, we were still seeing some traffic, so we decided to play rough. We recompiled apache from scratch with the module completely disabled. Further attempts to configure httpd.conf using that module failed, proving to us that it was indeed no longer present in the software.
We disabled the block at our router, and watched things for a couple of days. We were still seeing funny traffic. Paranoid at this point, we blocked at the router, to analyze the logs some more. We could not figure out how this might still be happening, and checked the PHP forums for bugs related to this. Finding none, and not wanting to just start recompiling modules at random, we looked at the logs again.
We found that our server, when making a failed request, was redirecting the user to our homepage. So the proxy requests were failing, but redirecting the user to our homepage. Checking the stats confirmed this. We received 5000 hits that day, a 1000% above normal. Realizing these scans and attempts to proxy were failing, we began to relax. Knowing we were probably on some spammers top-10 hacked sites in North America list, we also figured that their automated systems would remove us from such a list once our server stopped server proxy requests. And that’s exactly what we found. After a couple days the hits dropped off to 2500, and then back below 1000 before weeks end.

Part 3: Rac/Linux/Firewire – Software Requirements, Versions, etc.

Software Requirements, versions, etc

Advance notice for beginners out there. I’ve installed Linux probably 20-30 times over the last 10 years, and it’s gotten a lot easier, however, you’ll need to have some decent skill at kernel installations. I’ll outline what I did that finally worked, but I’ll also say that I tried getting this setup to work on a couple of other boxes before I ended up using the emachines ones. I think the kernel which the Linux Firewire team has compiled and patched to their liking is pretty finicky, hardware-wise.

RedHat 8.0

I can’t go into the details of installing Linux in this short article, and there are plenty of resources on the web to help you. You’ll need to get that up and running first, with the 1394 card in the box.


Oracle is a beast to install, and challenging for the beginner. Luckily there are a number of resources on the web.


This patch is a *REQUIRED* patch. The guys on the Linux Firewire project assure me that the Oracle Cluster Manager has a lot of bugs and won’t work straight out of the box from the release. Here’s a little twist. Although this is an *OPEN SOURCE* project, and the source is all free, and you can download Oracle and play with it to your heart’s content, to download a patch, you need access to Oracle’s Metalink, which requires you to have a software license. Not much I can say here folks. Use your imagination on getting ahold of this patch. Maybe if you place nice, someone on an email list will post it temporarily for you or something. It’s 235M. :-)

Login to Metalink (some like to call it metastink)

Click on the “patches” link along the left.

Search for patch 2632931

Also, take a look at this note:

Doc ID: Note:217811.1

Subject: ALERT: Patchset for Linux Does Not

Apply Correctly in the RAC Environment



Content Type: TEXT/PLAIN

Creation Date: 07-NOV-2002

Last Revision Date: 08-JAN-2003

Part 1 – Introduction

Part 2 – Basic Costs + Hardware Platform Outline

Part 3 – Software Requirements, Versions, etc

Part 4 – Initial Oracle Setup

Part 5 – Firewire + OCFS Setup

Part 6 – Cluster Manager Setup

Part 7 – Cluster Database Setup

Part 8 – Review of Clustered Features + Architecture

Part 9 – A quick 9iRAC example

Part 10 – Summary

Your Database – A Long-Haul Truck Or A Sports Car?


Databases, even those running high-powered software like Oracle, can be incredibly touchy, demanding, and even fragile in their own way. For that reason we must take care to optimize and tune them based on characteristic usage.

Data Warehouse vs OLTP

In broad terms, database applications are divided into two large classes, Data Warehouse, and OLTP (online transaction processing – what a mouthful!). For the purposes of this dicussion, let’s call them a heavy lifting truck, and sports car. Now both have powerful engines, but they’re used for very different purposes.

Our Data Warehouse is characterized by large transactions, huge joins, all of which work to produce very large usually one-off reports. The reports may be run a handful of times. These databases do mostly read-only activity, occaisionally performing large dataloads to add to the archive of data.

On the other hand OLTP databases are characterized by thousands or tens of thousands of very small transactions. Web sites for instance, exhibit this characteristic. Each transaction is doing something quite small, but in aggregate, thousands of users put quite a heavy, and repeated load on the database, and they all expect instantaneous response!

We provide these two very different types of databases by laying out the database for its characteristic usage, and then tuning relevant parameters appropriately. We may allocate more memory to sorting, and less to the db cache for a Data Warehouse, whereas a large db cache might help us a lot with an OLTP application. We may enable parallel query, or partition large tables in our Data Warehouse application.

Choose One Or The Other

If you have a database serving a web-based application, and you are trying to do large ad-hoc reports against it, you will run into trouble. All that memory you’ve setup to cache small web transactions, will get wiped out with the first large report you run. What’s more the heavy disk I/O you perform reading huge tables, and then sorting and aggregating large datasets will put a huge load on your database which you setup specifically for your web application.


There are lots and lots of parameters and features in Oracle best suited to one or the other type of application, to the point where your database really will look like a sports car or a long-haul truck when you’re done tuning it. For that reason it is really essential that these types of applications be divided up into separate instances of Oracle preferrably on separate servers.

Part 4: RAC/Linux/Firewire – Initial Oracle Setup

Initial Oracle Setup

Follow these instructions to get Oracle up and running on your new Linux box.

1. Setup the oracle account, and environment as follows.

Create an oracle user on your linux box:

$ adduser oracle

2. Login to the oracle account and edit .oraenv9i as follows (assumes bash):

# oracle environment variables

export ORACLE_BASE=”/home/oracle”

export ORACLE_HOME=”/home/oracle/product/9.2.0″

# EAST on machines #2




export LD_ASSUME_KERNEL=2.2.5

# US7ASCII is the default, but WE8ISO8859P1 support more languages


export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export ORACLE_TERM=xterm

export ORACLE_OWNER=oracle

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export TNS_ADMIN=$ORACLE_HOME/network/admin

# run the RH compatability stuff

. /usr/i386-glibc21-linux/bin/

# setup Java

export JAVA_HOME=/usr/local/java



Add this line to your .bash_profile:

. /home/oracle/.oraenv9i

Next install the glibc backward compatability libs per







3. Next install JDK.

4. Edit the hosts file if necessary to include two machines on your local network

by name. I used “utopia” and “zenith”.

5. Next run the Oracle installer as follows (assuming you opened the packages in

/tmp). If you’re not on the X-window console, connect with SSH and you should ha

ve x tunneling by default, and can display remotely:

$ ./tmp/Disk1/install/linux/runInstaller

First install the cluster manager. You’ll be prompted for the local and remote h

ostname, a quorum disk, as well as some other things. Doesn’t matter what you en

ter right now, as we’re going to go back and edit those files and do things by ha

nd anyway.

6. Next go through the software install. Here are some other notes with

various weblinks. Be sure to select “Enterprise Edition” and also “Software Only


I got started with this Oracle 8 install doc which details which compatability li

braries you’ll need, how to setup the Oracle account, environment variables, and



I ran into some trouble with LD_ASSUME_KERNEL, and compat libs:

You’ll encounter problems with the context makefile, and get this error “Error in

invoking target install of makefile /opt/oracle/product/9.2.0/ctx/lib/ ins_ctx.m

k”. Sad but true, this is *NORMAL* behavior. I suspect making the installer run

flawlessly isn’t at the top of Oracle’s priority list.


I also encountered problems with Oracle Net Configuration Assistant “Can’t find $

ORACLE_HOME/jre/1.1.8/bin/../bin/i586/green_threads/jre” and I added this symlink


$ ln -s $ORACLE_HOME/jre/1.1.8/bin/i686

I got a simlar error for

$ ln -s $ORACLE_HOME/jre/1.1.8/lib/i686

Though you’ll probably want to create your own database from scratch later, it’s

sometimes instructive to let the Oracle Database Configuration Assistant create a

starter one for you, and look at what options they use. As with the rest, you’l

l run into an error. This time it is “ORA-27123 unable to attach to shared memor

y segment: Oracle Database Configuration Assistant”. Fix it by doing the followi

ng as root on your linux box:

$ cat /proc/sys/kernel/shmmax


$ echo `expr 1024 * 1024 * 1024` > /proc/sys/kernel/shmmax

$ cat /proc/sys/kernel/shmmax


Obviously you’ll have to go through this process (living hell?) on both boxes you

‘ll be using in your cluster.

Next rerun the installer and specify the location of the itty bitty 235M patch, a

nd install that.

7. Enable RAC in Oracle9i

The Real Application Cluster feature is *NOT* enabled by default. Here’s how you

enable it:

As the oracle user:

$ cd $ORACLE_HOME/rdbms/lib

$ make -f rac_on

$ make -f ioracle

As root set permissions on rac_on

$ chown oracle /etc/rac_on

$ chgrp dba /etc/rac_on

Part 1 – Introduction

Part 2 – Basic Costs + Hardware Platform Outline

Part 3 – Software Requirements, Versions, etc

Part 4 – Initial Oracle Setup

Part 5 – Firewire + OCFS Setup

Part 6 – Cluster Manager Setup

Part 7 – Cluster Database Setup

Part 8 – Review of Clustered Features + Architecture

Part 9 – A quick 9iRAC example

Part 10 – Summary

Part 5: RAC/Linux/Firewire – Firewire + OCFS Setup

Firewire + OCFS Setup

In this installment, we’ll discuss how to get the Firewire drive shared between your two Linux boxes.

8. Test Firewire drive

At this point you can test the firewire drive if you like, with the standard Linux driver. You won’t be able to share the drive between the two nodes yet, however.

As root do the following:

$ modprobe ohci1394

$ modprobe ieee1394

$ modprobe sbp2

$ modprobe scsi_mod

Grab a copy of from here and run it:

run rescan scsi bus

Now partition it with fdisk:

$ fdisk /dev/sda

Now try making an ext2 filesystem with mke2fs

$ mke2fs /dev/sda1

Now mount it

$ mount -t ext2 /dev/sda1 /mnt/test

Now unmount it

$ umount /mnt/test

9. Linux Kernel Setup w/Firewire patch

The Linux kernel is a complex beast, and compiling it can often be a challenge. Though I like rolling my own, I downloaded the patched firewire source distro off of OTN, and try as I might, I could not get those compiled kernels to work. If anyone *DOES* get it to work, please send me their “.config” from the kernel source directory. Also I’ve tried to encourage the Oracle/Linux Firewire team to build a patch-only distro which can be applied against a standard Linux source tree. No luck yet.

Assuming you’re not going to roll your own, just download linux-2.4.20rc2-orafw-up.tar.gz from here:

Move to the “/” or root directory, and untar the file:

$ tar xvzf linux-2.4.20rc2-orafw-up.tar.gz

Edit your /etc/lilo.conf or /etc/grub.conf file to include the new kernel. Do *NOT* make it the default kernel, it may not boot.

Reboot. If you come up again, you’re in luck, the kernel works for your machine. Next you want to edit your /etc/modules.conf to include these lines:

# options for oracle firewire patched kernel

options sbp2 sbp2_exclusive_login=0

post-install sbp2 insmod sd_mod

post-remove sbp2 rmmod sd_mod

As root, load the modules like this:

$ modprobe ieee1394

$ modprobe ohci1394

$ modprobe ide-scsi

$ modprobe sbp2

$ modprobe scsi_mod

If you’re having trouble seeing the device, grab a copy of from here:

If you want to partition, now is a good time. Use fdisk as root like this:

$ fdisk /dev/sda

If you have other SCSI devices, it may be /dev/sdb or dev/sdc and so on.

10. Go through steps 1-8 on node 2

11. Cluster Filesystem setup (OCFS)

If you wanna play around, use mke2fs on the one of the partitions you created with fdisk, and then mount the partiton on machine a. Then mount the partition again on machine b. Create a file on one of the two boxes. The other machine *WON’T* reflect it. This is equivalent to unplugging a disk which is mounted, such as a USB device, or some such. You can and probably *HAVE* corrupted the filesystem. That’s ok, because we don’t have anything important on the disk yet. Ok, unmount on both machines. If you have trouble, you may need to reboot.

Having gone through the above example, you know why OCFS is so important. Ok, now the fun part. Install OCFS. There are good docs to be found in the linux_ocfs.pdf file here:

Without RedHat Advanced Server, the RPMs are *NOT* going to work. Just grab a copy of ocfs-1.0-up.o and put it in

Use ocfstool to create the /etc/ocfs.conf file. The pdf doc listed above is pretty good at explaining this.

Load the ocfs kernel module with load_ocfs. If everything goes right it will tell you like this:

$ cd /lib/modules/2.4.20-rc2-orafw/kernel/fs

$ load_ocfs

/sbin/insmod ocfs node_name=zenith
ip_address= ip_port=7000 cs=1865 guid=72C2AF5CA29FA17CB9CB000AE6312F24

Using /lib/modules/2.4.20-rc2-orafw/kernel/fs/ocfs.o

Next make the filesystem. ocfstool can do this too.

$ mkfs.ocfs -F -b 128 -L /ocfs -m /ocfs -u 1001 -g 1001 -p 0775 /dev/sda1

And finally mount the filesystem!

$ mount -t ocfs /dev/sda1 /ocfs

$ df -k

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/hda2 55439548 20835260 31788096 40% /

/dev/hda1 101089 18534 77336 20% /boot

none 112384 0 112384 0% /dev/shm

/dev/cdrom 122670 122670 0 100% /mnt/cdrom

/dev/sda1 60049024 30080 60018944 1% /ocfs

12. Perform step 10 on node 2.

13. Test ocfs

Here we quickly verify that a file created on one instance is viewable on another.

On node1 do:

$ cd /ocfs

$ touch mytestfile

On node2 do:

$ cd /ocfs

$ ls



You’ll see to your astonishment that the file is now visible on node 2!

Part 1 – Introduction

Part 2 – Basic Costs + Hardware Platform Outline

Part 3 – Software Requirements, Versions, etc

Part 4 – Initial Oracle Setup

Part 5 – Firewire + OCFS Setup

Part 6 – Cluster Manager Setup

Part 7 – Cluster Database Setup

Part 8 – Review of Clustered Features + Architecture

Part 9 – A quick 9iRAC example

Part 10 – Summary