Oracletool

Oracletool is a serious piece of weaponry in our Oracle and open source armory. This program is an excellent tool for maintaining Oracle databases. You can use it to help with performance tuning and to perform a variety of database administration functions. Oracletool provides a simple web-based interface to many of the day-to-day maintenance tasks a DBA needs to carry out in order to keep a typical database in good working order. And for those developers who can pluck a SELECT ANY TABLE privilege from the tight clutches of their DBA (good luck!), Oracletool is also a highly useful development utility.

Adam vonNieda first started work on Oracletool in 1997, and it had its first major release in March of 1999. Oracletool enjoys regular releases and maintains independence from any web server-specific modules. Written in Perl, it is completely CGI-driven and makes use of a number of great Perl modules, including Perl DBI and DBD::Oracle. Oracletool is a very straightforward program; its entire wealth of functionality is contained in a single CGI script. This implementation makes Oracletool easy to install and configure.

Oracletool provides a reasonable degree of security as a default, but it also gives you the ability to configure more rigorous security. You can choose your level of security based on your own site’s requirements. The faint of heart will be glad to hear that Oracletool does not modify your database. You can create a user with the SELECT ANY TABLE privilege and rest assured that running Oracletool won’t break your database. Not that you’ll need to worry in any case—Oracletool is well written and behaves consistently.

The main web site for Oracletool is:

http://www.oracletool.com

Installing Oracletool

Before you install Oracletool, you must make sure that the following are already installed on your system:

  • Perl

  • The Perl DBI and DBD::Oracle modules

  • The appropriate Perl security modules for performing cookie encryption. You will need to install these in order for Oracletool to run securely. If you haven’t installed the appropriate modules, Oracletool will send cookies with your Oracle username and password unencrypted. If there is any chance that someone might walk up to your workstation and read that cookie file, you’ll have a security problem.

    For moderate security and at least some level of cookie encryption (which might be appropriate on a reasonably secure intranet), Oracletool is preconfigured for use with the following two Perl security packages:

Digest::MD5

Used with the MD5 Message Digest algorithm

Mime::Base64

Used to encode and decode Base64 strings (specified in RFC 2045, MIME [Multipurpose Internet Mail Extensions])

  • For stronger measures on a full-blown Internet security setup, we advise you also to install these Perl encryption packages:

Crypt::IDEA

Used to access the International Data Encryption Algorithm (IDEA) block cipher

Crypt::CBC

Used to encrypt data with the Cipher Block Chaining (CBC) mode

  • See the Oracletool INSTALL and SECURITY documentation files for details.

Once you’ve installed Perl and the other prerequisite files, you can install Oracletool itself and prepare your system to run it, as follows:

  1. Download the latest stable version of Oracletool from http://www.oracletool.com/download.html. Click on the link appropriate to your current platform.

  2. Unpack the files and read all of the instructions.

  3. As with the other web-based tools, you need to make sure that you have Apache or another suitable CGI-capable web server up and running. Apache is a particular winner because its mod_perl module really makes Perl code fly.

  4. Copy the oracletool.pl and oracletool.sam files into a valid cgi-bin directory (while doing this, rename oracletool.sam to oracletool.ini).

  5. Be sure to edit the oracletool.ini file in order to modify the ORACLE_HOME and TNS_ADMIN environment variables as appropriate for your installation.

In the following steps, we’ll run through a fairly simple initial test installation, just to see how easy it is to get Oracletool up and running, with its wide variety of out-of-the-box functionality:

  1. Create a new Apache site in the following directory:

    /usr/www/site.oracletool

  2. Configure the Oracletool configuration file, /usr/www/site.oracletool/conf/httpd.conf, to contain the following key values (the cgi-bin directory line is particularly important):

    ServerRoot "/usr/www/site.oracletool"
PidFile /usr/www/site.oracletool/logs/httpd.pid
ScoreBoardFile
/usr/www/site.oracletool/logs/httpd.scoreboard
DocumentRoot "/usr/www/site.oracletool/htdocs"
ScriptAlias /cgi-bin/
"/usr/www/site.oracletool/cgi-bin/"
ErrorLog /usr/www/site.oracletool/logs/error_log
CustomLog /usr/www/site.oracletool/logs/access_log common
Port 8666

  3. Run the following commands:

    cd /root/ch7/oracletool-1.2.0
cp oracletool.pl /usr/www/site.oracletool/cgi-bin/
cp oracletool.sam
/usr/www/site.oracletool/cgi-bin/oracletool.ini
cd /usr/www/site.oracletool/cgi-bin/
chmod +x oracletool.pl

    Note that the oracletool.sam file gets renamed to a new oracletool.ini file.

  4. Alter the oracletool.ini file with some necessary changes:

    # Alter as appropriate
ORACLE_HOME = /u01/app/oracle/product/8.1.5

# Comment out if not required (default:
$ORACLE_HOME/network/admin)
#TNS_ADMIN = /home/oracle/admin/sqlnet

    In this case, because the value of our TNS_ADMIN environment variable matches the default, we comment it out. There are many other parameters, including important encryption parameters, in the initialization file, but the pair above are the two main Oracle parameters, so for testing purposes you can probably accept the other defaults. Later on, when you reset the configuration for production purposes, you will want to reset other parameters as appropriate for your own environment. Most of the parameter settings are fairly self-explanatory, and the documentation is thorough, so you should be able to configure Oracletool without too much difficulty.

  5. Once you have performed the other installation and configuration steps, fire up the Apache httpd daemon with the following command:

    /usr/local/apache/bin/httpd -d /usr/www/site.oracletool

    Now you’re ready to start up Oracletool.

Using Oracletool

Oracletool lets you perform the following tasks:

  • Watch sessions for database waits

  • Check the sessions that are the busiest

  • Check out datafiles and see how busy reads and writes relating to each file are

  • Examine currently held locks and various other types of contention

  • Examine Oracle system security and see with crystal clarity which users have which privileges and which roles, and basically just make sure everything is configured as it should be

  • Generate DDL for the existing tables in your database

Tip

If you’re running these web-based applications with a web server on the local machine and your browser is not behaving correctly, this may be because TCP/IP isn’t running for your local loopback device. In this case, for a quick fix, run the following commands as root: 

# Assign loopback (lo) to the standard IP address
/sbin/ifconfig lo 127.0.0.1

# Point a route at the loopback device
/sbin/route add 127.0.0.1

To get started with Oracletool, follow these steps:

  1. To run Oracletool from your favorite browser, simply point the browser to your CGI script, using a URL like this one:

    http://yourhost.com/cgi-bin/oracletool.pl

  2. Next we need to log in to our target database, as shown in Figure 6-6.

  3. Once you have logged into the database successfully, just drill down via the Schema list to check out our SYSTEM user, as shown in Figure 6-7.

Logging in via the Oracletool interface

Figure 6-6. Logging in via the Oracletool interface

Oracletool drilling down into the SYSTEM user’s schema

Figure 6-7. Oracletool drilling down into the SYSTEM user’s schema

In this discussion of Oracletool, we won’t try to explain every menu option displayed on the left side of Oracletool’s extensive user interface. Since there are so many different functions and screens, we’ll just cherry-pick a few favorites; you can check out the others on your own.

One Oracletool function we particularly like is the Datafiles graph shown in Figure 6-8, which shows the I/O for your datafiles.

Graphical representation of Datafiles I/O information

Figure 6-8. Graphical representation of Datafiles I/O information

Another cool feature is the SQL Worksheet, with which you can run SQL*Plus-like commands over the Web. This feature is a very handy way of getting out of some sticky situations. Simply enter the required SQL and press Execute, and the results pop right back within a neatly formatted HTML table!

In Figure 6-9, you can see the generic SQL query execution window on the left side and the results on the right.

Oracletool’s SQL Worksheet facility

Figure 6-9. Oracletool’s SQL Worksheet facility

Perhaps the most useful of Oracletool’s menu options is DB Admin, which provides a variety of database administration and reporting features. Administration features include the following menu choices:

User administration
Session administration
Rollback segment administration
Generate table DDL
Invalid object administration

Reporting features include the following menu choices:

Space report by user
Space report by tablespace/user
Datafile fragmentation report
Object extent report

You can see the DDL generation drill-down in Figure 6-10. We needed to traverse several screens to get to the actual DDL screen shown here, and we haven’t tried to show them all. Those screens give you the ability to scroll through all of a schema user’s objects, ticking off the ones required for DDL generation.

Generating database DDL via DB Admin

Figure 6-10. Generating database DDL via DB Admin

Oracletool also provides a really neat Explain Plan facility. If your user doesn’t have PLAN_TABLE installed, Oracletool installs it automatically for you. For example, if you type the following statement:

select count (*) from dba_tables

Oracletool brings back another neatly formatted HTML table, which you can see in Figure 6-11.

You may sense from our enthusiastic discussion here that we just love Oracletool. It’s a great tool bursting with helpful features—we only wish we’d thought of it first! If this section doesn’t persuade you that it’s worth your time and energy to download and install Oracletool, take a look at the live demo on the Oracletool web site to try out the program first hand.

Oracletool displaying some of its Explain Plan capabilities

Figure 6-11. Oracletool displaying some of its Explain Plan capabilities

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

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