Chapter 12. Building a Database Repository with the PDBA Toolkit

This chapter focuses on another important Oracle database administration requirement: the need to keep track of the many changes made to an Oracle database — changes to tables, indexes, roles, schemas, and other database objects. As part of building the Perl toolkit, we decided to create a repository, a central place in which to store all kinds of database changes. By centralizing the storage of changes in this way, we can easily perform such administrative tasks as tracking table changes over time, restoring last week's user passwords, recreating database roles as they appeared last month, determining the effect of major index changes on SQL execution plans, and comparing a schema against itself from a month ago. By providing a way to go back in time to compare today's database with last week's or last month's, we can often determine why programs that ran efficiently last week are now crawling — we can achieve something that looks a lot like time travel!

Using the PDBA repository does impose some additional overhead on your use of the toolkit. You will need to install it separately and perform some customization, as described in this chapter. If you don't want to use the repository — at least at this point in time — you can simply skip this chapter. (But we hope you'll consider coming back to it in the future: using the repository does provide Oracle DBAs with very helpful information.)

The repository uses the Oracle data dictionary as the source of much of its information, freezing certain dictionary images on a regular basis and storing them over time. This chapter describes how to install the repository scripts and tables, load the repository with data, and use it to report on a variety of different kinds of database changes. We'll divide the discussion as follows:

Structure of the repository

We will introduce the tables required to hold database information in the repository.

Installing the repository

We'll describe how to install the repository for both Unix and Win32 systems (in both standard form and for Oracle's locally managed tablespaces — LMTs).

Loading the repository with data

We'll show how to collect the baseline data needed for the repository via the baseline.pl script and run some tests on the archived dictionary data.

Reporting on database changes

We'll show a number of different reports illustrating how you can use the spdrvr.pl script to detect changes in database parameters and objects such as indexes and sequences over time.

Reporting on SQL execution plans

We'll also show reports illustrating how you figure out why database performance problems are occurring by retrieving SQL from a previous period and comparing the old execution plan against the latest version. We'll describe the sxp.pl, sxpcmp.pl, and sxprpt.pl scripts.