Oracle Database Administration with Orac

Database Administration got you down? Here's a tool to help you out.

Orac is an open-source database administration tool written in Perl/Tk. It was written primarily by and for DBAs (database administrators). However, it will also be very useful to developers and anyone else who wants to understand more about how (and how well) their database is working.

Orac provides much of the functionality that any DBA could want. It includes scripts that help in managing physical database files, users, database objects (such as tables, views, sequences, etc.) and scripts that help tune the database and resolve “locking” conflicts.

Orac builds on this collection of widely available SQL scripts by providing a nice GUI and a logical organization of the scripts. Orac is extensible, of course, in the sense that the source code is readily available. Even better, though, Orac has an easy method of adding support for more SQL scripts without editing even a single line of code. So for those DBAs out there who already have their favorite scripts, Orac is even more useful.

By the way, the Orac program was named after a supercomputer in the BBC science fiction television series “Blake's 7” and in no way takes its name from the Oracle database or the Oracle Corporation. No affiliation with Oracle Corporation is intended or implied.

Why Use Orac

Many experienced DBAs manage databases almost exclusively with command-line utilities. Oracle provides a number of tools such as sqlplus for querying the database, svrmgrl for startup and shutdown of the database and sqlldr for loading ASCII files. Like the Oracle database, these simple command-line tools have proven themselves solid, reliable and efficient.

Unfortunately, the same cannot always be said for the various high-end database administration tools on the market. Configuration and setup can be difficult. They are often slow, and at times it may not be clear exactly what the tool is doing. Some commercial DBA tools even require the use of a proprietary scripting language. (By the way, I'm running Oracle 8.0.5, WordPerfect 8 and Orac 1.1.11 on a Pentium 75/64M, and it works just fine for single-user experimentation.)

Orac provides an elegant way to capture and organize the various scripts many DBAs need to do their job. It represents a middle ground between having a bunch of ad-hoc scripts executed at the command line and the complicated commercial tools.

There will always be a role for the commercial offerings, of course, and you might eventually decide to purchase one. However, the knowledge gained from having used a free tool like Orac in a real-world setting can only be a benefit.

How Orac Works

Orac is a Perl script that performs two basic tasks. It retrieves information from the database and presents the information to the user. A couple of important Perl modules are used to do this work. First, DBI.pm is used to make the connection to the database. Here is a simple code snippet that connects to the Oracle database called ORA1 and gets a list of files that make up the database.

#!/usr/bin/perl -w
use DBI;
$dbh = DBI->connect('ORA1','ADMIN','ADMINPASS','Oracle');
$sth = $dbh->prepare
   ("select file_name from dba_data_files");
$sth->execute;
while (@row = $sth->fetchrow()) {
   print "File Name: @row\n";
}
$sth->finish;
$dbh->disconnect;
exit;

Of course, Orac reads its SQL from a file instead of hard-coding the statement into the Perl script, but the basic principles remain the same.

Listing 1.

Once the needed data are in hand, it is fairly straightforward to display them using the routines in the Tk module. The script in Listing 1 is similar to the one above, but instead of using a simple print to send the information to standard output, it uses Tk to display the results in the X Window System. Figure 1 shows the results from running the Perl/Tk script.

Figure 1. Output Display of Listing 1

Again, Orac uses Tk in a very flexible way. Orac loads its menus from a text file after the program starts. To recap, Orac loads both the SQL scripts it executes and the menus making up the program from text files, after the program starts. Any idea where this might lead? More on this later. Figure 2 shows how SQL gets executed in Orac.

Figure 2. How SQL is Executed by Orac

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Oracle Database Administration with Orac

Anonymous's picture

Sir,
I have configured Tk to run with DBI for oracle on windows. But I am having difficulty running orac, though, I tried running the code under listing 1 for one of my remote oracle databases, and it runs fine.
Any help would be highly appreciated.
email:
sean.roy@spectrumscm.ca

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix