Oracle + Open Source

A lot of people mean different things when they discuss Open Source. Some point to freedom from license restrictions, or availability of source code, while others think of it as a means to reduce their bottom line.

For the interests of our discussion today, I thought I will make a brief comment on what I mean. For me Open Source simply means a real + vibrant community. One where ideas, code, scripts, methodologies, and solutions are shared
openly for all the benefit from. New York Oracle User Group certainly represents such a forum, and the Oracle-L email list does as well. Oracle certainly has this type of community and has since I started working with it around
version 7.3.

So with that in mind, I've compiled a list of Open Source tools, scripts and other projects which I think are relevant to Oracle DBAs. Some are traditional GPL projects, while others are just tools + scripts written by DBAs not necessarily in the GNU tradition but nevertheless available for us all
to share.

1. Monitoring Files - fetchlog

Monitoring logfiles is a common requirement of administration, and Oracle is no different. Our primary focus will be on monitoring the alert.log file, however, these methods could apply just as well to the listener.log, application server logs, backup logfiles or even custom pl/sql code that you've written which logs messages to files.

2. System Monitoring - NAGIOS

Nagios is definitely an application that many of you will be interested in. It started out as a generic network monitoring
tool, to watch tcp/ip ports & to make sure services were up and running. It has grown by leaps and bounds, so that now it can monitor everything from http, mail, ssh, disk I/O, and even includes an Oracle plugin.

Of course all of you DBAs out there will quickly outgrow the default Oracle plugin, which simply monitors whether your database is up and running. However, the great thing about Nagios is how easy it is to customize. Custom plugins can be written in your language of choice, C, Perl, Python, other any other that your OS supports. If you can write a script to connect to Oracle it can be used as a plugin for Nagios. It simply has to return output in a specified format. Basically a
number and a message are all you need to return. That number you will compare against in your config file.

3. Change Management - CVS

CVS stands for Concurrent Versioning System, and is a system for managing versions of files. Source code, configuration files or initialization files for your Oracle instances can all be stored in CVS. http://www.gnu.org/software/cvs/

Another new versioning system is becoming more popular now and is called Subversion.

4. Security

Security is the often overlooked area of the enterprise which gets more and more press everyday. Unfortunately it is a lot like backups. While everything is going right, it is ignored and unappreciated, until something goes wrong.

PeteFinnigan.com has some really excellent tools as well, including an audit checker, a default password checker, a bunch of scripts which show roles, and privileges in your database, and a lot more. He also keeps track of the best tools around for security. He mentions a couple of Oracle password cracking tools, a Java tool called OScanner and various tools that check for security vulnerabilities in your Oracle software. This is the one-stop shopping place to go for Oracle Security answers.

At the Operating System level, if some of you do System Administration along with your DBA tasks, you should take a look at Integrit It basically creates checksums on all your binaries, and important files the first time you run it. From then on it will check current checksums against the saved original state and if any are different it will let you know. Such a system could of course be used against your ORACLE_HOME directories to be sure no one has (a) patched Oracle without your knowledge, (b) deleted something (c) relinked without your knowledge and so on and so forth.

Want to test your web-based application for SQL Injection attacks, session hijacking, cross site scripting or other vulnerabilities, get a copy of the Java-based Burp Intruder.

System administrators also think about port scanning, to verify what services machines on their network are supporting. A well known tool for this task is nmap. Of course it can be used to check which known Oracle ports are servicing requests as well. On the other side, there are also tools to watch for port scans from the outside, to get a sense of what parts of your network are being poked from the outside.

5. Real Application Clusters

Thinking of implementing RAC? Take a look at the Global File System project. Redhat's GFS is certified to work with Oracle.

Want to run RedHat Advanced Server but don't need the support services? Take a look at CentOS, which provides free download ISOs created from the same RedHat builds just days after a new version of RedHat comes out. http://centos.org

Some of you may have heard of Oracle's Linux/Firewire project. Started a few years ago by the OSS division headed by Wim Cockerts, it was rooted in the need and desire to provide a very within reach testbed for DBAs who wanted to learn RAC.
And that it does. External firewire drives can be had for as little as $100, add a couple of cheap $500 dell servers a couple of extra network cards, and firewire adapters for about $35 each, and you have yourself a 2 node RAC cluster. Wim's team contributed some changes to the Linux Firewire driver which allowed a SHARED bit to be set. Once that happened, two machines could see the same device, which before was available exclusively to only one machine. They
have also written their own Oracle Cluster File System (OCFS). Once those components are available, you simple install Oracle and setup cluster services, and create your cluster database. Visit oss.oracle.com for more information.

What about running RAC on a laptop, or as they say nodeless? Believe it or not RAC can indeed be run on a single machine. The key is virtualization technology, the ability to have more than one virtual machine on a single server. VMWARE provides such a commercial solution for Win32 and Linux. What about an Open Source version? Xen Source is just such an implementation. Take a look at the details
page and get hacking!

6. General Use Scripts & Tools

orasrp

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

rlwrap

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.

gqlplus

If you're looking for a complete replacement for sqlplus, take a look at gqlplus. It is a drop-in replacement for SQL*Plus, and you would most likely create a symlink so you can still use the same command you're used to. Rename the Oracle binary rather than deleting it!

phporaadmin

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.

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

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

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

DBTop
Scale Abilities has a tool which I've long wondered why nobody has created yet for Oracle. It is a UNIX-like
top utility for the database. Written in Java (anybody want to work on an ncurses version?) it monitors the top sessions to provide you interactive feedback on what is happening in your database.

M2O
Paul Vallee's tool to migrate a MySQL database to Oracle.

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

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

yasql
Yet Another SQL*Plus Replacement, as the name implies is an SQL*Plus-like tool but with history, and better editing.

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

SQL Gotcha
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.

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

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

Open Source Software Repositories:

Source Forge
Freshmeat

Sites for Scripts and Tools:

Pete Finnigan
Ask Tom
Miracle AS
Oak Table
Trivadis

Various Lists:
Oracle-L
Lazy DBA
usenet: comp.databases.oracle.server
usenet: comp.databases.oracle.misc

Weblogs

AMIS Tech Corner
David Aldridge
Niall Litchfield
Mark Rittman
Pete Finnigan