Open-source Technologies for Oracle DBAs and Developers - Part 2
July 17, 2007
The open-source community around databases, and Oracle specifically, has
literally exploded in the last couple of years. The final installment of this two -part article covers open-source administration, security, benchmarking and monitoring tools.
JOraStat - admin - (http://sourceforge.net/projects/jorastat/)
This tool is designed to
help you forecast changing database needs. If you plot trends in your
database, you're better able to anticipate growth, and plan for it. Using Java
as a frontend, this tool integrates into the database with some pl/sql stored
procedures. The project page on SourceForge is active, so you're likely to see
changes and updates fairly regularly. It is still in beta though, so your
mileage may vary.
EasyStandby for Oracle - (http://sourceforge.net/projects/shana)
Last month we wrote an
article for Database Journal about creating a manual standby database on Oracle
standard edition. Normally one would need to get the Enterprise Edition of
Oracle, which includes DataGuard, the fully loaded standby solution from
Oracle. However, that can be prohibitive, as EE is 10x the price of SE per
processor. Though not a supported solution from Oracle, with enough testing,
this could prove to be a useful and helpful one for your organization. Take a
look at the code, test it for a few months in your sandbox development
environment, and if it serves your purposes, you may be in luck!
Anyone who's ever loaded
data into their Oracle database has probably had the need to "unload"
data, or dump it to a flat file. This toolset will surely suit your needs, and
make life simpler. Though Oracle doesn't provide a tool specifically to do this
as they do sql*loader to get data in, it's not prohibitively difficult. Tom Kyte
also has a number of solutions on his Ask Tom site. (http://asktom.oracle.com/tkyte/flat/index.html)
Oracle Database Dashboard (http://odd.deskweb.nl/)
This tool is another
monitoring system, but built specifically for Oracle and the types of things DBAs
want to monitor. You can monitor running queries and sessions, review system
parameters, and so on. It is fairly simple, and being an open-source tool, you
can customize it to your heart's content!
Noguska Oracle to
MySQL Data Converter - (http://sourceforge.net/projects/nog-omdc/)
Here's an interesting tool
that might surprise you. Folks are often in a position to move data between
databases, either from MySQL to Oracle or often in the reverse direction.
Here's a tool which can help you with that task. Create a matching table in
MySQL with columns and datatypes fitting your Oracle table, then use a php
script to enter db authentication info, and off we go!
Easy Data Extract (http://sourceforge.net/projects/easydataextract/)
Here's a tool which I would
put squarely in the experimental category. A bit of background: Oracle
datafiles are regular datafiles to the operating system. For instance, if you
were to open them in a hex editor you'd see bits of data, some resembling data
from your tables, scattered throughout the file. The format of these files
however is proprietary, and closely guarded. For instance the format of headers,
and blocks, and so on is not published. However with a little ingenuity a good
programmer can reverse engineer what is there. That's exactly what the folks
behind this tool have done. For all the reasons I've detailed above, however,
I would caution using this on any production system. Besides the fact that the
format of Oracle datafiles can and often does change between major version
releases, if not minor releases, this whole concept is also just not
supported. Now on the positive side you're only extracting data, so you're not
writing those datafiles which would be much more dangerous. But nevertheless
the data you extract is still prone to idiosyncrasies and changes in formats.
So if something goes wrong, you're up the proverbial creek without an Oracle
LobShooter - (http://oraload.sourceforge.net/)
In Oracle, if you have large
objects, such as binary files, images, or very large text, and you want to
store them in the database, you use LOBs or large object datatypes. BLOB is
for binary data, and CLOB is for character data. They're differentiated so you
can search the text, whereas binary data one cannot readily search. This tool
steps in to fill the need of moving this data more easily into and out of your
Oracle-Admin - (http://sourceforge.net/projects/oracleadmin/)
If you've ever wanted to quit
the command line, and use a web-based tool to manage your Oracle database, OEM
is probably the tool you considered. It's from Oracle, and well supported, and
chances are it has all the features you want. But it can be unwieldy, and
consumes a lot of system resources. Plus it's not one you can dig into and
play around with. Enter Oracle-Admin, a community based tool to provide such a
web interface. Find something you want to add, write a bit of code, and submit
it, and maybe it'll become part of the project.
Oracle Password Repository (http://sourceforge.net/projects/opr)
This tool allows you to keep
your passwords in one place rather than in your backup and other administrative
scripts. This prevents you from passing them on the command line, and
furthermore keeping them all in one place makes them easier to update when you
need to change them.
Oracle Password Checker (http://www.red-database-security.com/software/checkpwd.html)
Password crackers aren't
just for hackers. They should be an important part of every administrators
arsenal of tools. Alexander Kornbrust's tools are available for various
platforms, and are sure to be eye opening, if not database opening!
Oracle Auditing Tools (http://www.cqure.net/wp/?page_id=2)
This collection of Java
based tools works on Linux and Windows clients and provides a password guesser,
query tool, listener check and much more.
Pete Finnigan's Site (http://www.petefinnigan.com/tools.htm)
For more on all things
Oracle + security related, I would highly recommend checking out Pete's site.
There are many other tools including password crackers, filesystem and OS
scanners, listener config testers, and more. There is also a thorough and
up-to-date listing of commercial solutions there as well.
Most Oracle database tools
have boring or unexciting names, so this one really jumps out at you. If you
want to do load testing of your database server, this tool offers a lot of
options. TPC-C & TPC-H tests, multi-user tests, web load testing and a lot
more are included.
Founding member of the Oaktable
Network, James Morle brings us a tool for creating simulations from tracefile
data for use in benchmarking your database applications.
Apache ANT is a java based
build tool which replaces the likes of Make and its various offspring. Incanto
provides ANT tasks for accessing Oracle tools such as SQLPlus and Export/Import
inside of your Ant build files.
Ad Oracle Manager (http://sourceforge.net/projects/oraman/)
AD is otherwise known as
Active Directory, Microsofts renaming of the infamous LDAP or lightweight
directory access protocol. This tool allows you to manage users, tables, and
views from your database using Active Directory.
This tour of Oracle related
open-source applications has hopefully only whetted your appetite for more.
There are an incredible number of tools and active projects on SourceForge which
mention some type of Oracle support that I didn't even touch upon here. I
encourage everyone to head on over to sourceforge.net and do some searching of
your own. Also keep an eye on the Oracle-L email list as well. There are
plenty of postings there of code snippets, and SQL solutions which though not
released as projects, and presented as official "open-source" solutions,
are nevertheless community based and community-driven technology which is
really what open-source is all about.
See All Articles by Columnist Sean Hull