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.

Administration 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!

jDUL/DUDE – (http://sourceforge.net/projects/jdul/)
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 paddle.

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

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.

Security Tools

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 administrator’s 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.

Benchmarking

Hammerora (http://hammerora.sourceforge.net/index.html)
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.

Simora (http://www.scaleabilities.co.uk/content/view/27/60/)
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.

Monitoring Tools

incanto (http://incanto.sourceforge.net/)
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, Microsoft’s 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.

Conclusion

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers