Chapter 6. Building Web-Based Oracle Applications

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:

Karma

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.

Oracletool

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

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.

DB_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 and PhpOracleAdmin

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

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

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

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/

Installing 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:

  • Perl

  • The Perl DBI and DBD::Oracle modules

  • The Mail::Send Perl module (if you will be using Karma’s email notification facilities). Mail::Send is part of the MailTools module on CPAN; it can be found under Graham Barr’s Perl CPAN site at:

http://www.perl.com/CPAN-local//authors/id/G/GB/GBARR/

To install Karma, follow these steps:

  1. Download Karma from one of the sites listed previously.

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

  3. Issue the following as root:

    $ make install

  4. 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:

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

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

  3. Once you’ve done all this, you should be able to use the karmactl utility to ignite the daemon:

    $ karmactl -s

  4. You may also want to specify the location of the configuration file like this:

    $ karmactl -s -c /home/oracle/karma.conf

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

Using Karma

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

alog

alert log

Monitors errors in the alert log file (karmagentd )

hitr

hitratios

Regularly checks the buffer cache hit ratio

rdlg

redo log

Monitors redo log file switching contention

rlbk

rollback

Reports on rollback segment contention

ssql

slow sql

Monitors for slow SQL (by number of disk reads)

exts

max extents

Checks objects nearing their maximum extents

ltch

latch contention

Checks latch contention

frag

fragmentation

Monitors tablespaces for fragmentation

mts

multi-threaded server

Looks for multi-threaded server contention

tbsp

tablespace quotas

Supervises tablespace quotas (not user quotas)

repq

replication queue

Monitors deferred transaction queue

rper

replication errors

Monitors deferred transaction error queue

os

os statistics

Monitors load average of target database machine (karmagentd )

up

up/down status

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:

  • Email notification about database problems. Getting email from Karma gives you early warning of problems and the chance to solve them before anyone else even notices them. This feature can be an absolute godsend to the DBA.

  • The ability to monitor multiple databases.

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!

Karma home page, displaying a demo of Karma in action

Figure 6-1. Karma home page, displaying a demo of Karma in action

Using Karma for email notification

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]

Running Karma on Windows

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.

Browsable help available for Karma

Figure 6-2. Browsable help available for Karma

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

Using Karma with multiple databases

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:

  1. Create two databases under Linux.[35]

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

  3. 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.)

  4. Make sure that the ORACLE_HOME environment variable is set correctly for Perl DBI.[36]

  5. 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 Karma program’s initial screen following our first basic installation

Figure 6-3. The Karma program’s initial screen following our first basic installation

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.

Drilling down into the Database Name address and the tbsp green flag

Figure 6-4. Drilling down into the Database Name address and the tbsp green flag

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.

Drilling down on hit ratios and fragmentation flags

Figure 6-5. Drilling down on hit ratios and fragmentation flags

Extending Karma

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset