The world of web-based Oracle applications is bursting at the seams these days, with a well-rounded and growing list of tools ranging from snapshot-based performance monitoring tools and database administration gizmos to data browsing and remote online transaction processing (OLTP) systems.
What are web-based applications? We loosely define such applications as those whose output or interface is viewed through a web browser. Some of these applications are Perl-based CGI scripts; others are just SQL scripts whose output is HTML. Chapter 5, describes the fundamental technologies on which these applications are built. If you’ve read that chapter, you will have a solid foundation for understanding the web-based Oracle applications described in this chapter.
The advantage to employing a web solution is that you need to deploy it only once, at one web address, and thereby provide a tool that anyone with a web browser can access from anywhere on the network. This is a powerful application paradigm indeed, especially for busy administrators who may manage dozens of databases and who want to maintain a satellite’s-eye view of what’s going on. If you deploy over the Internet, as well as over your corporate intranet (with the appropriate security measures, of course), you’ll be able to administer your systems much more simply and effectively, especially if you’re away from your base system, need instant access, and sometimes have difficulty remotely logging in to your intranet. Web-based applications are also useful to the many non-technical people at your site who need different levels of access to browse the database, but who don’t want to know, or don’t need to know, SQL.
There are many different types of web-based applications available in the Oracle world. In this chapter, we’ll focus on a number of excellent applications, highlighting those that help demonstrate a variety of open source implementation strategies. Check out the web sites mentioned in the preface for pointers to other helpful open source web-based applications for Oracle. We’ll cover the following applications in this chapter:
A robust Oracle database monitoring application, Karma offers the ability to notify the DBA by email when database problems occur, and it provides a single place to keep track of many different databases. Karma is written in Perl, but unlike some of the other Perl-based applications described in this chapter, it’s not a CGI. Instead, it runs as a daemon, or background process, generating HTML pages in a specified location.
This tool focuses on performance monitoring, but it also provides other database administration features. It is written in Perl and implemented as one CGI script. It uses the Perl DBI and, like other Perl CGI scripts, can take advantage of the mod_perl module with Apache.
OraSnap provides snapshot-based performance monitoring and statistics gathering. OraSnap works somewhat like Oracle’s own utlbstat and utlestat utilities, but it provides much more sophisticated reports, and it’s all presented in a web browser. This application stands out from the others in that it does not use any web-based programming technologies per se. OraSnap’s ingenious approach is simply to wrap the various SQL scripts in printable HTML tags, so the resulting output is valid HTML that can be understood by a web browser.
This tool provides a way to browse tables, search for columns, and add data to a database. DB_Browser was built using the Perl DBI and provides an interesting example of how to build a database-independent application. As a Perl CGI script, it takes good advantage of the mod_perl Apache module.
PhpMyAdmin provides a simple way to browse a schema, modify a table, add and delete rows, and run SQL queries against your database. PhPMyAdmin is a PHP-based MySQL database administration tool. Work is just beginning on PhpOracleAdmin, the long-awaited Oracle version of this tool.
WWWdb provides a mechanism for searching for text inside database tables. It is somewhat like Oracle’s ConText (now renamed Intermedia) product, but it is a CGI-based script written in Perl.
Big Brother is essentially a general network monitoring tool that includes support for monitoring Oracle databases. Big Brother is particularly useful to those of us who have system administration, as well as Oracle database administration, responsibilities. Big Brother is written in C and uses various Unix networking technologies to monitor a disparate network of computers and the services running on them.
Karma is an open source tool that can help Oracle DBAs with their daily work. The program is especially helpful in automating the tracking of important but tedious-to-collect information—information that you need to know but are too busy to gather personally. Karma’s comprehensive configuration capabilities let you select the particular features and database events to monitor and how often and how strictly to monitor them. You can also break up your many databases into groups, each with their own monitoring criteria and thresholds.
Even if you don’t have the time to keep checking Karma’s continuously updated web page, the product’s new email notification capability will email you with alerts about potential database problems. This way, you’ll find out when something goes wrong before anyone else notices it (and the suits need never know!).
The Karma project has been in development since July 1999, when Sean Hull, your faithful author, first started work on it, and it has grown exponentially in popularity since its first release. Its feature list has expanded too, as well as its stability, driven particularly by the comments and bug fixes of its ever-widening group of users. Karma is written in Perl and runs as a daemon, generating HTML pages in a location you can specify.
The main web site for Karma is:
http://www.iheavy.com/karma/ |
Karma’s
installation
follows the pattern of many Perl-based tools, so if you’re
familiar with the perl Makefile.PL
drill
described in Chapter 2, you shouldn’t have
too much trouble installing Karma. Before you install Karma, you must
make sure that the following are installed on your system:
http://www.perl.com/CPAN-local//authors/id/G/GB/GBARR/ |
To install Karma, follow these steps:
Download Karma from one of the sites listed previously.
Unzip the zipped Karma file and install it. There is a standard Perl
Makefile.PL
included with Karma, so the actual
installation should be fairly simple:
$ perl Makefile.PL $ make test
Issue the following as root:
$ make install
Once that’s done, Karma’s scripts should be installed in
/usr/bin
, /usr/local/bin
,
or wherever you’ve configured Perl to put its scripts. You can
find the main Karma script like this:
$ which karmad
Karma provides a wide variety of
configuration options. Once
you’ve installed Karma, you can select these options by editing
the program’s configuration file. The
karma.conf
file included with Karma should go a
long way towards explaining the intricate features of the product and
how to configure it, but you might want to get started quickly and
easily with the simpler default basic.conf
file.
The two important lines in the basic.conf
file
are these:
karma:*:UNA:karma:amrak doc_root:/home/shull/karma/karma098/doc_root
Edit these lines as follows:
The first line specifies the individual database to monitor. Edit the
third field on that line, UNA, to be the same as the equivalent name
in the tnsnames.ora
file of the database
you’ll be monitoring, as in:
UNA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY =UNA
) (PROTOCOL = TCP) (Host = 127.0.0.1) (Port = 1521) ) ) (CONNECT_DATA = (SID =UNA
) ) )
The last two fields on the first line specify your username and password respectively.
If you want to add more databases to monitor, simply add more lines. Copy the line provided and change the appropriate names to protect the innocent.
Now edit the second line in the configuration file, the
doc_root
line, to indicate where you want
karmad
to generate the
karma.html
file—the watershed
HTML file, from which all the other
drill-downs start. If this isn’t the default, note that there
are many supporting files that you will have to copy manually to that
location. These are currently stored within your default Karma
install directory, /doc_root
.
Once you’ve done all this, you should be able to use the
karmactl
utility
to ignite the daemon:
$ karmactl -s
You may also want to specify the location of the configuration file like this:
$ karmactl -s -c /home/oracle/karma.conf
Make sure that Karma is running. You can use the
Unix ps
command (or an
equivalent command on your system) to do this. If there are
environment
variables that aren’t set, you may need to debug things by
checking the karma.log
file. You can also check
the status by typing:
$ karmactl -t
You should see a small report about what is being monitored, how often, and what the current status is for each item monitored.
You can configure many different aspects of Karma, and we won’t
go into all of them here, but the karma.conf
file provided with the product should serve as a good example. Notice
that you can separate your various databases into groups, each with
their own preferences. The basic.conf
file
simplifies all this by just using the default “*” group.
This may be all that you will need for your own system. If not, check
out the product’s documentation.
There is no shortage of documentation with Karma. If you click on the help “?” link, you’ll find various links to online documents, as we’ll show later.
Karma lets you monitor a great many database statistics and characteristics. Table 6-1 summarizes these and specifies the names used to request them. Later in this section we’ll show some examples illustrating how to interpret these statistics.
Table 6-1. Karma Monitorables
Name on Screen |
Full Name |
Description |
---|---|---|
|
|
Monitors errors in the alert log file
( |
|
|
Regularly checks the buffer cache hit ratio |
|
|
Monitors redo log file switching contention |
|
|
Reports on rollback segment contention |
|
|
Monitors for slow SQL (by number of disk reads) |
|
|
Checks objects nearing their maximum extents |
|
|
Checks latch contention |
|
|
Monitors tablespaces for fragmentation |
|
|
Looks for multi-threaded server contention |
|
|
Supervises tablespace quotas (not user quotas) |
|
|
Monitors deferred transaction queue |
|
|
Monitors deferred transaction error queue |
|
|
Monitors load average of target database machine
( |
|
|
Checks that the target database is up and running |
Karma can be very simple to use for DBAs who have straightforward administrative needs, but it offers an enormous amount of flexibility for those with complicated configurations. As we’ve mentioned, two of the most important features of Karma are the following:
We’ll describe these features in the following sections.
Like the Big Brother tool, described later in this chapter, Karma allows you to collect statistics about a number of different entities (i.e., your databases) and present them all in one simple table. This table then becomes your central command bunker, your first point of defense against the underwater sea wolves menacing the convoys of information tracking across your databases.
Figure 6-1 shows Karma’s main screen in all its glory. Here, Karma is monitoring seven different databases in five different cities!
Email notification is fairly simple to configure, and the facility is very flexible, so if you have different DBAs responsible for different groups, Karma can handle that as well. In this example, we’re configuring for the default group:
notify_email:full
:[email protected],[email protected] notify_alert:1:fragmentation,up,hitratios,alertlog notify_warn:1:hitratios,fragmentation
In the first line, notice that the users [email protected] and [email protected] will receive email messages.
Notice too that the size of the email message is specified as
full
. This means that a
full-length message will be sent to
those email addresses.
In the second line, we’re asking that alert
messages be sent only if there are
problems with specified types of
statistics:
fragmentation
, up/down
status
(whether the database is up or not),
hitratios
, and alertlog
(see Table 6-1 for a description of these and
other database statistics). If those specified statistics hit only
the warning level threshold, Karma will not send
email.
In the third line, however, we see that for
hitratios
and
fragmentation
, warning threshold messages will
be mailed.
If you want a short message instead (which is potentially useful for
email addresses of text pagers or small-screen PDAs, for those
handling 24 × 7 support), you could
configure the
notify_email
line like this:
notify_email:short:[email protected],[email protected]
Given the
cross-platform nature of Perl, it is fairly
simple to get most programs to work on the Win32 platform. With a few
exceptions, this is also true of Karma. Unfortunately, there are a
few Unix-only system facilities used to make the
karmactl
tool work that currently don’t
translate well to the Win32 world. Those include various
kill
signals, as well as the
fork
system call (to allow
karmad
to run in the background). True, there
are ways to implement these on Win32, but they haven’t yet been
been implemented within Karma. For now you run
karmad
directly on Win32 like this:
DOS> perl karmad -c karma.conf
See the various Karma online documents (available for download) for more Win32-specific information. These documents, along with many other help files, are accessible via the question mark (?) drill-down, as shown in Figure 6-2.
If the web-based online documentation isn’t sufficient, you may
be happy to find the /pod
directory in the Karma
installation directory. It contains all the various original
documents that eventually turn into text and HTML; these documents
started life as Perl POD documents (Plain Old Documentation).
Let’s look at an example of using Karma with multiple databases. In the
following example we’ll use the
absolute basic setup, as provided via
the basic.conf
file, and run Karma through some
initial simple trials:
Create two databases under Linux.[35]
After saving the original, more complex
karma.conf
file as
karma.conf.old
(which may be useful later on),
add the following lines to the basic.conf
file,
and then save it as karma.conf
:
# # karma line specifies database to connect to # The "*" means use the default preference group, and "UNA" is the # name of the db (tnsnames.ora), then user and password are specified # karma:*:orcl
:system:manager karma:*:mydb
:system:manager doc_root:/root/ch7/karma-1.0.0/doc_root
These two database entries match up to our two complementary entries
in the $ORACLE_HOME/network/admin/tnsnames.ora
file, as follows:
orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host=localhost.localdomain)(Port= 1521)) (CONNECT_DATA = (SID = orcl)) ) mydb = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host=localhost.localdomain)(Port= 1521)) (CONNECT_DATA = (SID = mydb)) )
The doc_root
line points to the place where we
want our HTML files to be created. (In a web server situation, this
might be something like
/usr/www/site.karma/docs.
)
Make sure that the ORACLE_HOME environment variable is set correctly for Perl DBI.[36]
Finally, run the Karma program:
$ cd /root/ch7/karma-1.0.0 $ ORACLE_HOME=/u01/app/oracle/product/8.1.5 ; export ORACLE_HOME $ bin/karmactl -s -c karma.conf
This program can now repeatedly generate appropriate HTML files in
timed loops, which you can use either in conjunction with a web
server such as Apache or, as in this basic example, merely by
pointing a browser at the files directly (as detailed in the
README
, INSTALL,
and
QUICKSTART
setup files). This gives us our
output, as shown in Figure 6-3.
The first screen provides numerous drill-downs into further
information. You may notice from the key in Figure 6-1 that several of the checks are set to
“No Status.” This is because we are using the minimal
configuration. If you read through the documentation listed in Figure 6-2 and follow the various instructions,
you’ll learn how to activate many more of these configurable
checks. For now, in Figure 6-4, we’ve drilled
down into the ORCL
address to see what that
gives us, plus the first green flag, tbsp
, for
tablespace figures.
Finally, to wrap up our Karma example, we’ve drilled down on the first red flag (on hitratios) as generated by our example code, and also on our first yellow flag (for a fragmentation report) to find out what alert and warning figures will reveal, as shown in Figure 6-5.
Karma is a good tool, but there is plenty of room for improvement. It has been some time since Version 1.0.0 was released, and development is just beginning on Version 2.x. Sean would certainly like to hear from potential contributors, especially those who have experience programming Perl under Win32. That is where the biggest problems with Karma remain.
[35] See Appendix A, Oracle8i on Linux, for instructions.
[36] We’ve assumed here that you’ve already set up Perl and Perl DBI. This particular Karma installation is not using notification, so in this case the MailTools module is not required.