Section 4.2. Oracletool | Perl for Oracle Dbas

4.2 Oracletool

Oracletool, developed by Adam vonNieda as a tuning, monitoring, and general database administration tool, is one of the best Perl CGI applications you'll find anywhere . Oracletool provides a simple web-based interface to many of the day-to-day maintenance tasks an Oracle DBA needs to keep a typical database in good working order. (It's also a very useful development utility.)

Oracletool provides a reasonable degree of security as a default, and 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.

To learn more about Oracletool, visit the following page:

http://www.oracletool.com/

In the following sections we'll show how to install Oracletool and try out a few of its options. There is much more to learn about Oracletool, however. You can find a more detailed discussion in Oracle & Open Source , and you can browse freely through the tool's many helpful menus and screens to explore its capabilities.

4.2.1 Installing Oracletool

The instructions for installing Oracletool are virtually identical under Unix and Win32. Get hold of the latest tarball, such as oracletool-2.0.tar.gz , and unpack it into a local directory. Good instructions come with the download. We'll provide the usual two summaries here.

4.2.1.1 Installing Oracletool on Unix

Copy the oracletool.sam file to your ../cgi-bin/ directory under Apache. Rename it to oracletool.ini . For instance:
```
 $ cp  oracletool.sam  /usr/local/apache/cgi-bin/oracletool.ini 
```
Now edit this file. Basically, ensure that ORACLE_HOME is set for the use of Perl DBI and that TNS_ADMIN is set so Oracletool can get hold of your target databases. ^[6] (The TNS_ADMIN value will default to $ORACLE_HOME/network/admin , but there's no harm done making sure it's clear to anyone later maintaining the file.)

^[6] Note that the parameters in oracletool.sam are not Perl variables ; they are more in the style of Java .ini parameters. (See Table 4-1 for more initialization parameters.)
```
 ORACLE_HOME = /opt/oracle/product/9.0.1 TNS_ADMIN = /opt/oracle/product/9.0.1/network/admin 
```
Now copy oracletool.pl itself to your ../cgi-bin/ directory, without renaming it this time (unless you'd prefer a .cgi suffix):
```
 $ cp oracletool.pl /usr/local/apache/cgi-bin/oracletool.pl 
```
Make sure that the first line of the oracletool.pl script points to the right Perl executable:
```
 #!/usr/bin/perl 
```
We also had some problemettes connecting orcl.world and Oracletool to Oracle9 i , on SuSE 7.3 Linux, but there are a range of connection options you can try near the top of oracletool.pl . For instance, we replaced the following line, which was deliberately stripping out .world suffixes:
```
 my %hash =     map { (split(/\  .world  /i,(split(':'))[-1]))[0] , undef }         DBI->data_sources('Oracle') 
```
We used one of the commented-out simpler alternatives. Problem solved :
```
 my %hash =      map { (split(':'))[-1] , undef } DBI->data_sources('Oracle') 
```
Once you've solved any rare teething problems like this, you should secure oracletool.pl by changing its permissions, as with the Unix chmod command:
```
 $ chmod 755 oracletool.pl 
```
Now ensure that your Apache web server and target Oracle database are running. Type the following into your browser location field:

http://localhost/cgi-bin/oracletool.pl
You'll be greeted with the screen sequence displayed in Figure 4-3.

4.2.1.2 Installing Oracletool on Win32

For Win32, follow these steps:

Copy the oracletool.sam file to your ..\cgi-bin\ directory under Apache. Rename it to oracletool.ini :
```
 C:> copy oracletool.sam     C:\Program Files\Apache Group\Apache\cgi-bin\oracletool.ini 
```
Now edit this file, in the same way as on Unix. Make sure ORACLE_HOME is set for the use of Perl DBI if your Win32 platform needs it, and that TNS_ADMIN is set so Oracletool can get hold of your target databases:
```
 ORACLE_HOME = C:\ORANT      # You may not need to set this on Win32 TNS_ADMIN = C:\ORANT\NET80\ADMIN 
```

Now copy oracletool.pl itself to your ..\cgi-bin\ directory:

 C:> copy oracletool.pl     C:\Program Files\Apache Group\Apache\cgi-bin\oracletool.pl

Make sure that the first line of the oracletool.pl script points to the right Perl executable. For Win32 using ActivePerl, that's usually:
```
 #!/perl/bin/perl 
```
If you're running on NTFS or a similarly secure NT-based filesystem, you should secure oracletool.pl by changing its permissions via your security system to have the equivalent of 755 status on Unix.
Now ensure that your Apache web server and target Oracle database are running. Then type the following into your browser location field to get to the screens displayed in Figure 4-3:

http://localhost/cgi-bin/oracletool.pl

Figure 4-3. Logging into Oracletool on Win32 and Linux

Table 4-1. Main Oracletool initialization parameters

Parameter	Description
ORACLE_HOME	Enables Perl DBI to connect to Oracle
TNS_ADMIN	Tells Oracletool where to find your tnsnames.ora file
EXPIRATION	Cookie expiration time (defaults to one year)
ORACLENAMES	Uncomment if using Oracle* Names
DEBUG	Sends debug information to a nominated log file
LOGGING	Similar to debug, but for standard logging information
LOG	Full path of log file required by DEBUG and LOGGING
AUTO_REFRESH	Determines screen refresh rate in seconds
LIMIT_SEARCH	Limits various searches (to keep resource use down)
ENCRYPTION_STRING	Used to encrypt passwords; should be made unguessable
ENCRYPTION_METHOD	Determines whether IDEA or Blowfish is used in level 2 security

4.2.1.3 Preferences and privileges

Once you've connected to a database, you can change the Oracletool look and feel by selecting one of the theme options from the Preferences menu. The following Oracle user privileges are also required to run Oracletool's selection reports :

Oracle7, Oracle8, Oracle8 i: SELECT ANY TABLE
Oracle9 i: SELECT ANY TABLE, SELECT ANY DICTIONARY

To obtain DBA reports, the user must also possess the DBA privilege. Once you're all sorted out, welcome to Oracletool!

4.2.1.4 Enhanced security

You'll notice that passwords are being stored inside cookies, which means you don't have to keep logging on. To protect these cookies, there are three levels of Oracletool security, and the program figures out ahead of time which extra Perl security modules you have installed. It then chooses the security level accordingly :

Level 0

If you lack the security modules discussed as follows , you'll be at this security level. Passwords are stored in cookies, in plain text ” for example:

 mydb.sessionid  system~manager

Level 1

The username, password, and encryption string are MD5-encoded into a single string. The default encryption string is stored within oracletool.ini :

 ENCRYPTION_STRING =  changeme

Obviously, you may wish to alter this string. We changed ours to drinkme , and this turned our cookie password string into:

 mydb.sessionid  c3lzdGVt-bWFuYWdlcg%3D%3D-FbMoQ1xyHjwXuKU3aTIL3g%3D%3D

The reason Oracletool did this was because two security modules, created by Gisle Aas, come preinstalled automatically with ActivePerl:

Digest::MD5
MIME::Base64

You'll have to install these manually with Unix. (See later for details.)

Level 2

This level uses the IDEA or Blowfish block ciphers (both use extremely secure algorithms). Oracletool defaults to the IDEA algorithm within oracletool.ini :

 ENCRYPTION_METHOD =  idea

If you'd like to use Blowfish instead, change idea to blowfish in oracletool.ini . You'll need the following modules for level 2 security:

Digest::MD5
Crypt::IDEA or Crypt::Blowfish , both by Dave Paris
Crypt::CBC , by Lincoln Stein

As of this writing, some of the Crypt-* modules mentioned previously, were not available under ActiveState ; you should check out the current situation at:

http://aspn.activestate.com/ASPN/Downloads/ActivePerl/PPM/Packages

You can get the Unix packages via the three following addresses:

http://www.cpan.org/authors/id/GAAS/

http://www.cpan.org/authors/id/D/DP/DPARIS/

http://www.cpan.org/authors/id/LDS/

You can also find out your current security level from the main Oracletool menu by selecting the About option on the main menu

Given a choice between Crypt::IDEA and Crypt::Blowfish , we recommend that you opt for the latter. Since around 1999, Version 1.01 of Crypt::IDEA has had some build problems with Perl, particularly with Perl 5.6.1 on some flavors of Linux (though it's possible that this been resolved with later versions of either Perl or Crypt::IDEA ). This problem occurred because Perl used to "pollute" the namespace of C-based modules. The problem was fixed in Perl 5.6; however, some modules had come to rely upon this "feature."

4.2.2 Using Oracletool

We won't provide a detailed description of Oracletool here because ample documentation is available in your Oracletool download. Simply point your browser at the relevant directory where you unpacked Oracletool, and view the following file:

file:///C/MyOracletoolUnpackDirectory/oracletool-2.0/doc/index.htm

We suggest that you wander through the different Oracletool DBA options and check out the program's many capabilities (one of them, the fragmentation monitoring option, is shown in Figure 4-4).

Oracletool was designed to be as concise and straightforward as possible. (One way it avoids "code bloat" is to limit itself to monitoring, rather than changing, its target databases.) To this end, Oracletool requires Perl DBI and DBD::Oracle as the only extra Perl modules beyond the standard module set for Perl 5.6.

The requirements code block for the 21,000+ line oracletool.pl file is simply:

Figure 4-4. The Oracletool fragmentation feature

 require 5.003;    use strict; use CGI qw(:standard);   # CGI, File::Basename and FileHandle use File::Basename;      # all standard built-in Perl modules! :-) use FileHandle;    if (! eval "require DBI") {    ErrorPage("It appears that the DBI module is not installed!"); }

Everything else is also contained within this single CGI script, except the initialization values held in oracletool.ini .

Not only does Oracletool currently offer a lot of features (for a summary of current features, see Table 4-2), but its author is continually adding even more capabilities. You can participate in its growth by emailing new ideas to Oracletool's creator via adam@oracletool.com.

Table 4-2. Major features of Oracletool 2.0

Feature	Description
Schema list	Drill-down screen used to examine each individual schema.
Session info	Various session-based reports and the ability to view sessions.
Tablespaces	Large tablespace report and access to tablespace allocations graph (see Figure 4-5).
Datafiles	Datafiles report, plus access to a datafiles I/O chart.
Redo / Archives	Online redo log information, including archiving status.
Rollback segs	Access to various reports on rollback segments and transactions.
Perf / memory	Memory and SQL allocations, multithreaded server (MTS) use, and shared pool flushing.
Locks / contends	Checks on object lock contention and session wait information.
Explain plan	Online form to check SQL explain plans (Oracletool will install PLAN_TABLE for you automatically, if it is unavailable).
SQL Worksheet	Ability to enter and execute multiple SQL statements, online. SELECT statements produce formatted reports, and DML is executed.
Security	Reports on roles, profiles, auditing, and other security concerns.
Controlfiles	Various control file reports, including a breakdown of record types.
Init parameters	Report on all current INIT.ORA parameters, including descriptions.
Recent events	Various instance reports including log switches, and startup times
Preferences	Customization screens displayed for fonts and themes.
DB Admin	Many different reports and options, including: User administration Session administration Rollback segment administration Generate table DDL Invalid object administration Parameter administration Job Scheduler (DBMS_JOB) Space report by user Space report by tablespace / user Datafile fragmentation report Object extent report
Monitoring	Oracletool database monitoring system.
Change connection	Connection screen to other databases.
My Oracletool	Ability to add your own scripts to the Oracletool SQL repository.

Figure 4-5. Oracletool's tablespace allocation

In the following sections, we'll look at a few Oracletool features we especially like.

4.2.2.1 My Oracletool

The My Oracletool feature was recently added to Oracletool. Using this feature, you can add your own SQL scripts, store them within a target repository, and execute them later whenever you wish. One such script is shown in Figure 4-6.

Figure 4-6. My Oracletool in action

4.2.2.2 Oracletool monitoring

Version 2.0 of Oracletool also added the ability to monitor databases by utilizing a PL/SQL-based framework. Oracletool checks for common database problems, such as inadequate tablespace usage, resource contention, and so on. When these problems reach certain thresholds, a warning email is delivered to a configured pager's email address via a nominated email server. See Figure 4-7 for an example.

Figure 4-7. Setting up Oracletool monitoring

The way Oracletool is designed, the main server has certain PL/SQL procedures installed on it, and the target clients have other procedures installed. This framework is held together by database links. Because of this design, OS daemons are unnecessary. You schedule the monitoring tasks either via Oracle's built-in DBMS_JOB package or via an OS cron script like this one:

 #!/bin/sh   ORACLE_BASE=/u01/oracle ORACLE_HOME=$ORACLE_BASE/8.1.7 TNS_ADMIN=$ORACLE_HOME/network/admin PATH=$PATH:$ORACLE_HOME/bin   export ORACLE_BASE ORACLE_HOME TNS_ADMIN PATH   sqlplus -s ot_monitor/ot_monitor_password@server <<EOF    exec ot_monitor_server.checkall; EOF

The monitoring server must have a JServer release so that it is able to send emails via the DBMS_SMTP package; the result is that you must be running at least Oracle8 i ; note, however, that the clients need only be running Oracle8.