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: http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/

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.

http://utplsql.sourceforge.net/

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.

http://oracledba.ru/orasrp/

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.

http://utopia.knoware.nl/~hlub/uck/rlwrap/

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.

http://sourceforge.net/projects/phporaadmin/

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: http://sourceforge.net/projects/schemadiff/

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.

http://sourceforge.net/projects/orace/

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.

http://hammerora.sourceforge.net/

Toolkit for Oracle

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

http://sourceforge.net/projects/tora/

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.

http://log4plsql.sourceforge.net/

ProDBA

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

http://sourceforge.net/projects/prodba/

SQLGotcha

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.

http://www.marceljan.nl/oracle/scripts/oracle_script_sqlgotcha.html

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.

http://www.iometer.org/

OraRep

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.

http://www.izzysoft.de/?topic=oracle

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(http://www.feedburner.com), and there are even browsers (http://www.flock.com/) which are starting to incorporate it. Personally I prefer web-based sites such as the google homepage, http://www.netvibes.com, or http://bloglines.com/ 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.

http://asktom.oracle.com/

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.

http://oraclesponge.blogspot.com/

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

http://www.petefinnigan.com/weblog/entries/index.html

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

http://www.rittman.net/

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

http://www.niall.litchfield.dial.pipex.com/

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.

http://www.orablogs.com/orablogs/

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

http://blogs.oracle.com/

3. Oracle Discussion Groups

Howard Rogers’ has a forums section with various discussions on Oracle topics. You can find his site at dizwell.com:

http://dizwell.com/main/component/option,com_joomlaboard/Itemid,256/

By far the best email list to watch is the Oracle-L list now hosted on freelists.org 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!!

http://www.freelists.org/webpage/oracle-l

Another great email list is Lazy DBA.

http://www.lazydba.com/

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

comp.databases.oracle.server

comp.databases.oracle.misc

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 Oracle.com, google will surely soon be an even better option.

http://otn.oracle.com/pls/db102/db102.federated_search