9.1 Introducing the PDBA Toolkit

Your authors have been Oracle DBAs for many years. Over that time we've encountered our share of frustration with database problems and inadequate tools. We've also ended up writing literally thousands of ad hoc scripts to diagnose and fix problems on the fly. We've checked database connectivity, monitored the Oracle alert log, wrestled with password management, rebuilt indexes, and endured the drudgery of creating thousands of user accounts. We've used many programming languages and applications. Some we've loved and others we've hated. Some did the trick, but at a huge cost in money and complexity. Then we discovered Perl and realized how helpful it could be in developing those quick scripts so essential to an Oracle database administrator. Perl — and the modules that connect it to Oracle — gave us the keys to the kingdom!

The Perl DBA Toolkit is our own ongoing open source contribution to the world of Perl and Oracle. We have pooled our own script libraries and modules in order to build a cohesive Perl tool library for you to use in performing Oracle database administration tasks. Many of the scripts included here are integrated versions of those we've used on a regular basis in our DBA activities over the years. Others have been on our wish lists for a very long time, and this book has finally inspired us to transfer these wishes into reality.

It's up to you how you want to use this toolkit. It serves as a complete, standalone application, and it also provides a helpful template for you to use in doing Perl programming of your own. In building this toolkit, we've tried to demonstrate the flexibility and power of this amazing language by putting together, for your enjoyment, a living, breathing network of Perl coding examples (in addition to providing a huge array of Oracle database administration operations, of course).

The toolkit contains two distinct types of programs:

Perl scripts

Standalone scripts, written in Perl, that perform some distinct function; examples include adding a new user to the database, rebuilding an index, or populating the toolkit repository with data from the Oracle data dictionary.

Supporting modules

Underlying modules, also written in Perl, that provide more basic functionality that is shared by multiple scripts; examples include finding and loading various types of configuration files and processing command-line arguments.

Tables 9-1 through 9-5 list the toolkit scripts that are installed automatically, along with the supporting modules (listed in Table 9-6) when you install the toolkit. All toolkit scripts are shown here except for a few additional scripts used only for setting up the repository (described in Chapter 12) and for demonstration purposes.

We describe the routine database administration scripts (Chapter 10) before the monitoring scripts (Chapter 11) and repository scripts (Chapter 12) because they provide general-purpose functionality. However, if you use the monitoring and/or repository functionality of the toolkit, we recommend that you install and run those scripts before using any other toolkit scripts. Essentially, the monitoring scripts provide a first perimeter of security for your database, and the repository scripts provide a second. For the safest possible database operation, it's best to install and run these scripts before undertaking routine database administration.

Table 9-1 lists the scripts that are associated with the password server described in Chapter 13. (See Section 9.5.3 and Section 9.5.4, later in this chapter.)

Table 9-1. Chapter 9 scripts — Password encryption

Script	Description
pwd.pl	Password server daemon that encrypts passwords via a TCP socket; works remotely with the other Perl scripts via the toolkit module set.
pwc.pl	Client that remotely retrieves encrypted passwords from the password server, easing the secure database access overhead imposed by other scripts.
pwd_service.pl	Installs the password server as a service on Win32.

Table 9-2 summarizes the database administration scripts found in Chapter 10. These scripts perform a wide variety of DBA tasks, including managing user accounts (e.g., creating new users from the command line, creating new users via duplicated accounts, creating multiple accounts with automatically mailed passwords), maintaining indexes, killing sniped database sessions, managing extent usage, and extracting DDL and data (e.g., creating delimited data dump files for SQL*Loader transfer).

Table 9-2. Chapter 10 scripts — Routine database administration

Script	Description
ddl_oracle.pl	Generates the DDL necessary to recreate schemas, tables, indexes, views, PL/SQL, materialized views, and other objects.
sqlunldr.pl	Dumps entire schemas to comma-delimited files and generates the SQL*Loader scripts necessary to reload them. Also dumps LONG RAW and BLOB objects, converting them to hex format via the Oracle HEX_TO_RAW function in the SQL*Loader control file in order to convert the data back into binary format.
create_user.pl	Creates Oracle users from the command line. You can create a user and assign passwords, tablespaces, and privileges, all with one easy command-line call. Best of all, you can use this script to preconfigure different groups of runtime privileges.
drop_user.pl	Drops a database user by first dropping all of the users' tables and indexes before dropping the account. Doing so avoids most of the resource-intensive SQL recursion incurred when dropping an account containing many tables and indexes.
dup_user.pl	Duplicates an account, with the source user's system privileges, object privileges, roles, and quotas assigned directly to the target user.
my_script.pl	This is a demonstration script used in the explanation of the PDBA::OPT module.
mucr8.pl	When creating a large number of users, this utility creates them all with a single operation. Configurable permissions are granted, and the passwords automatically generated get emailed back to the new account owners.
kss.pl	Kills sniped sessions. (We'll explain what these are, and why you would want to kill them, in Chapter 10. )
kss_NT.pl	Win32 version of kss.pl.
kss_service.pl	Used to create an appropriate snipe-killing service on Win32.
idxr.pl	Determines if an index should be rebuilt and, if so, rebuilds it. Checks on a per-schema basis, and is configured to check indexes based on days since the index was last analyzed. A configurable time limit is imposed, which allows index rebuilds to fit within a predefined time schedule.
maxext.pl	Monitors the size and number of extents in tables and indexes. If they're nearing a maximum allowed or if the object will be unable to extend because of limited free space, it notifies the DBA. This script is most useful for databases that use dictionary-managed extents.

Table 9-3 lists the monitoring scripts described in Chapter 11. These will help you maximize the availability of your databases by alerting you to problems — both error conditions reported in the Oracle alert log and problems with database connectivity.

Table 9-3. Chapter 11 scripts — Database monitoring

Script	Description
chkalert.pl	Daemon that monitors Oracle alert logs for error conditions and notifies the DBA via either email messages or pager calls. Oracle's alert.log files contain important error messages as well as a log of database startup and shutdown messages.
chkalert_NT.pl	Win32 version of chkalert.pl.
chkalert_service.pl	Utility script that creates a Win32 service for chkalert_NT.pl.
dbup.pl	Working alongside chkalert.pl, a highly configurable database connectivity monitor that checks to see if databases are up and available.
dbup_NT.pl	Win32 version of dbup.pl.
dbup_service.pl	Creates the Win32 service for dbup_NT.pl.
dbignore.pl	Utility script used with dbup.pl to temporarily disable connectivity checks on an individual database (e.g., while maintenance is being performed).

Table 9-4 summarizes the PDBA repository scripts contained in Chapter 12. These scripts compare different database schema versions over time, detecting database changes (official or otherwise). They also store SQL execution plans within a library cache; doing so allows the scripts to compare the current execution plan with plans previously collected; this way, the scripts can report on changed execution plans and the reasons behind the changes.

Table 9-4. Chapter 12 scripts — repository and DDL "time travel"

Script	Description
baseline.pl	Creates the baseline for the PDBA repository (described inChapter 12), establishes "time travel" control of DDL (Data Definition Language), and stores the entire database structural change record across time boundaries.
spdrvr.pl	Perl driver for SQL*Plus that reports on information created by baseline.pl.
sxp.pl	Collects and stores SQL statements from the data dictionary and generates accompanying execution plans for later comparison with other plans.
sxpcmp.pl	Examines the current SQL statements, generating execution plans.
sxprpt.pl	Generates reports based on the stored SQL and execution plans.

Table 9-5 lists the scripts described in Chapter 13. In particular, you'll find in that chapter a line-by-line examination of the dba_jobsm.pl script, providing a detailed example of how the PDBA Toolkit is used in a Perl script.

Table 9-5. Chapter 13 scripts — extending the PDBA Toolkit

Script	Description
dba_jobs.pl	Reports on the status of jobs in a database.
dba_jobsm.pl	Reports on the status of jobs in multiple databases.
null_test.pl	Test script used in explanation of extending the PDBA Toolkit.

9.1.1 Supporting Modules

We've written most of the PDBA Toolkit's functionality in the form of encapsulated Perl modules. These modules are called by many of the scripts in the toolkit. Our purpose was to both encourage code reuse and simplify the creation of new scripts. Table 9-6 provides a summary of the modules; we'll discuss them in some detail in the following sections.

Note that these modules are also available for use in scripts you develop yourself. You will find that taking advantage of this ready-made code will speed your own development process. If you decide to create your own scripts, you'll find that using these modules will dramatically reduce the amount of code you'll need to write yourself.

Table 9-6. PDBA Toolkit supporting modules

Module	Description
PDBA::CM	Connection manager that simplifies Perl-to-Oracle connectivity.
PDBA::ConfigFile	Finds and opens configuration files.
PDBA::ConfigLoad	Finds, opens, parses, and loads configuration files into memory.
PDBA::DBA	Designed for DBA-specific tasks; many are data-dictionary related.
PDBA::Daemon	Runs Perl script daemons on Unix.
Win32::Daemon	This module, by Dave Roth, is included here because it is so important to toolkit daemon services on Win32 systems.
PDBA::GQ	Generic query module that simplifies single-table queries.
PDBA::LogFile	Creates and locks log files; used by many scripts in the toolkit to perform logging actions.
PDBA::OPT	Processes command-line arguments unhandled by calling scripts.
PDBA::PWC	Password client module.
PDBA::PWD	Password server module.
PDBA::PWDNT	Password server modules for Win32.
PDBA::PidFile	Used to control script execution.
PDBA	Modular collection of widely used methods.