 |
 |
 |
 |
 |
 |
 |
 |
|
||||||||||||||||
Orac ResourcesPlease use the following mailing list resources to find out more about Orac:For information on the installation, configuration and use of Orac you can subscribe to the Orac User's mailing list by sending an empty e-mail (with the word subscribe on the subject line) to: manage-orac-dba-users-subscribe@listserv.kkitts.com For those interested in following the developer discussion (or perhaps even developing for Orac!) a developers list is available. Please send an empty e-mail (with the word subscribe on the subject line) to: manage-orac-dba-developers-subscribe@listserv.kkitts.com The following article on Orac appeared in the November issue of Linux JournalOracle Database Administration with Orac
rac is an open source database administration tool written in
perl/Tk. It was written primarily by and for DBAs. However, it
will also be very useful to developers and anyone else that 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.) as well as 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 additional SQL scripts without even editing a single line of code! So for those DBAs out there that already have their favorite scripts Orac is even more useful. By the way, the Orac program was named after a super computer in the BBC science fiction television series "Blake's 7" and in no way take's its name from the Oracle database or the Oracle Corporation. No affiliation with Oracle Corporation is intended or implied. Why Use OracMany experienced DBAs manage databases almost exclusively with command line utilities. Oracle provides a number of tools like 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 that are 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.15 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 that 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 that are available. 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 benefit you should you eventually decide to make that choice. How Orac WorksOrac is a perl script that performs two basic tasks. It retrieves information from the database and presents the information to the user. There are a couple of important perl modules that it uses to do this work. First, the 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.
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.
Once the needed data is in hand it is fairly straightforward to display it using the routines in the Tk module. The following script 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 under X windows.
use DBI;
use Tk;
my $mw = MainWindow->new;
$mw->title("Oracle Datafiles");
$mw->Button
(-text => "Exit",
-command => sub { exit })->pack(-side => 'bottom');
$lb = $mw->Listbox
(-selectmode => "single", -width => 48)->pack();
$dbh = DBI->connect('ORA1,'ADMIN','ADMINPASS','Oracle');
$sth = $dbh->prepare
("select file_name from dba_data_files");
$sth->execute;
while (@row = $sth->fetchrow()) {
$lb->insert('end', @row);
}
$sth->finish;
$dbh->disconnect;
MainLoop;
exit;
Figure 1 shows the Output Display from the perl/Tk script (above)
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 that it executes and the menus that make up the program from text files after the program starts. Any ideas where this might lead? More on this later. Figure 2 shows how SQL gets executed in Orac.
Orac in ActionThere are a number of common tasks that DBAs face such as the management of users, database performance and, of course, the actual database files. We'll take a look at the last item, database file management, to show how Orac could be used to make this task easier.A full explanation of Oracle storage concepts is beyond the scope of this article. In short, though, a database is composed of Tablespaces which can contain multiple DataFiles. A tablespace is composed of 1 to n DataFiles. Each of these DataFiles contains the actual database information for: Tables, Views, Stored Procedures, etc. Typically, the data is segregated is such a way that "System" related information is stored in a different Tablespace/DataFile than application related data. Since DataFiles are fixed in size at database creation time DBAs must monitor the available space and add or expand the DataFiles before they run out of room. Newer versions of Oracle, by the way, have more sophisticated space management techniques that alleviate some of these problems.
Figure 3 shows a list of Tablespaces in the database and how much
free space remains. Orac has summed the total space for each
Tablespace. In other words, if a Tablespace is composed of three
DataFiles then the total space available in the three files is
displayed. This brings up another great feature of Orac. Each
report includes a button called "See SQL" that displays the exact
query that was run to generate the report. If there is ever any
question about how a report was generated you can get to the
actual source quickly and make the needed improvements or
corrections.
As mentioned earlier, Orac loads both the SQL and its user interface from a text file at startup. Did you guess where this was leading? Orac is perfectly capable of loading a user interface and the related SQL for databases other than Oracle! In fact, developers are hard at work on Informix and some work has also been done for Sybase. The Orac team would very much like to see additional databases such as MySQL, mSQL and PostgreSQL supported in the future and we're actively looking for volunteers to help out. Another area developers are hard at work on is the dish (Database Interface Shell). This module provides the user with a way to enter ad-hoc SQL into the database. The initial module has already been coded and is being tested now. By the time this article sees print it is likely that most of the bugs will have been worked out. While parts of Orac make use of Tk to draw some primitive graphs there is certainly room for improvement. In the near future Orac will make use of the functionality in the GD and GIFgraph perl modules to provide better charting and graphing capabilities. These are only a few of the areas where work is in progress. The Orac team is actively soliciting feedback from anyone and everyone that would like to make Orac a better program. Credits and Additional InfoOrac is the creation of a dedicated group of volunteer programmers led by Andy J. Duncan. Some of the other major contributors include Kevin Brannen and Thomas Lowery but many others to numerous to mention have contributed as well. A list of most of the contributors is included in the README file that accompanies the program. You can find out more about Orac including installation hints and tips and mailing list information from the Orac home page listed below.For more information about using Oracle on Linux you might look at some of the following links:
About the Author Kevin L. Kitts is the Senior Oracle DBA at the Howard Hughes Medical Institute in Chevy Chase, MD. In his spare time he enjoys working with Linux software including perl, DBI/DBD and Tk and converting MS Access databases to Oracle Web applications on Linux. This article Copyright 1999 Kevin L. Kitts Questions or Comments? kkitts@his.com |
