Open-source Technologies for Oracle DBAs and Developers
July 12, 2007
As a DBA, you have lots of software, scripts, and applications, which help you with your day-to-day operations. Some you've written yourself, and some may be commercial solutions providing a lot of feature rich assistance to you.
However, there are also times when a script or community created tool really fits the bill. Fortunately, the open-source community around databases, and Oracle specifically, has literally exploded in the last couple of years. Oracle has a page on OTN detailing all of the projects they're involved in, and various community projects. Furthermore, a search on SourceForge yields 52 pages of results, over 500 projects mention Oracle specifically!
That's a lot to sift through, so we're going to present some of the biggest projects here in this two part series.
Libraries and language interfaces
If you're embarking on software development, and at the stage of choosing a language, and development platform, the first thing you'll need to know is what type of database interface is supported. Fortunately, there is a plethora of choices.
If you're scripting in Perl, for purposes of monitoring, or with mod_perl doing web development, look no further than dbd::Oracle, the DBI driver for Oracle. It provides all of the important features and keeps you from having to worry about the intricacies of OCI. Connect, parse, execute, and fetch to your hearts content.
While you're on the Perl bandwagon, you'll definitely want to get a hold of this Apache module. Rather than running your code as a CGI, mod_perl brings the Perl interpreter into the Apache memory space, speeding up executing, caching code, and providing persistence. All good stuff you'll want to take advantage of.
The parallel of mod_perl for pl/sql developers is this great Apache module. Wonder how Apache got into the middle tier of Fusion? Here's a hint. See also the old and much deprecated OWS Killer.
Prefer to do your programming in Python. You're covered here as well.
If you plan to develop an application in C++ there are a number of wrapper libraries to give you OCI functionality. Take your pick from the ones below.
ORA++ OCI Library (http://mywebpages.comcast.net/jimcainadmin/orapp/)
Oracle has been big on the PHP bandwagon now for the last few years. There is even a special PHP Dev Center over at OTN: http://www.oracle.com/technology/tech/php/index.html
So there is lots of support in the PHP space for Oracle.
This library helps you build scalable PHP code for Oracle by giving you shared session functionality.
Tracing + profiling tools
10046 is Oracle's extended tracing facility and is very useful for diagnosing and tracing session activity to pinpoint problems. etprof can be used to scan, profile, and format these output files for easier reading.
This library provides a consistent way to add logging messages to your pl/sql code for later code tracing. Much like adding printf's to your C code, here you can add "entering procedure X" messages at appropriate points in your code, which can help you debug problems later.
Hotsos Oracle Instrumentation Library (http://sourceforge.net/projects/hotsos-ilo/)
Hotsos is big on the scene in using trace data to isolate and pinpoint bottlenecks in the database. With their own pioneering method, this library installs packages in the target database for defining, measuring and utilizing SQL trace data.
GTKO - (http://gsqlr2.sourceforge.net/)
There are quite a few tools that do similar things to this one, but I'd say it's still worth a look. This one includes explain plan, syntax highlighting, statement tracing, and a schema browser. It's a GTK based tool, so will probably build easily on most Linux distros. It's also been updated quite recently, so it is actively being developed.
SchemaDiff - (http://sourceforge.net/projects/schemadiff/)
As a consultant, I've walked into countless sites, and had to review applications, and underlying database schemas. Often there are disparate logins, with overlapping schemas, and no one is quite sure what was originally created to do what. You have to tiptoe around, so as not to interrupt anything on a production system that is already well-oiled and working, but at the same time you want to cleanup as much as possible, and eliminate pieces that aren't necessary anymore. This SchemaDiff tool can fill such a niche. It looks at two Oracle db schemas and will tell you the differences in terms of columns, and datatypes. This can be a handy tool indeed. Caveats to keep in mind though are that it hasn't been updated recently, so may not support 10g well. As always, buyer (of free software!) beware. So review, and test, and modify as needed.
Stay tuned for part 2 of this article, when we'll discuss administration, security, monitoring, and benchmarking tools.