Checking the Overall Health of a Database

Probably the most important and routine task that any DBA could do is to check on the relative health of their databases. What they want is a one-stop shop report that details exceptions to normal thresholds. In short, they want to click a button and see simple output of only those things that require their attention. Moreover, many DBAs would also like to automate such exception reporting. They would like to receive such reports automatically every morning by e-mail, so that they can start their day with a cup of coffee and a clean review of all their databases' health.

TOAD offers this capability through its Database Health Check screen, shown in Figure 5.21 and launched from the main menu at DBA, Health Check. Keen observers will notice that the Database Health Check offers many features either similar to or the same as some of the screens covered in this chapter. That is correct. In fact, this utility is so valuable that many DBAs now use the Database Health Check in lieu of these other screens. You'll have to judge for yourself whether to use one or the other ”or possibly both.

Figure 5.21. TOAD Database Health Check screen.

The Database Health Check screen enables users to run such an exception report for one or more databases and up to 41 health check options. Some of these options are checked by default and others are not, and some offer users the ability to customize the threshold value. For example, option #9 would find tables with more than six indexes. You can change that value merely by clicking the Adjust text next to its check box, which then launches a pop-up window for customizing the threshold value. After you've selected your databases, options, and thresholds, you merely click the Generate Report toolbar icon and then view the output on the Report Output tab.

Note that running this report creates a background thread to run the report so that you can continue to work within TOAD. So you will see a message that the report is running, and the output will scroll by in the Report Output tab while the report runs. You can cancel the report by simply clicking the Cancel button, which will only display on the screen's toolbar when reports are running. Also note that the Database Health Check creates new and separate database connections rather than using the current TOAD connections. So you will see these additional connections and their associated Oracle processes when monitoring your database server.

To receive the Database Health Check Report Output results by e-mail is a simple, two-step process. First you must select the Email Settings tab in the Database Health Check screen and select when the message should be sent and in what format, as shown in Figure 5.22. The recommendation is to choose Only When 'Bad' Conditions Exist (that is, true exception reporting) and to send the output as HTML. The reason for HTML is because TOAD highlights and color -codes the output in a manner conducive to easy reading and focusing on key problems. Second, and only if the When To Send Email option is enabled, you must visit the TOAD Options category for Email Settings and define all the appropriate information as depicted in Figure 5.23. Remember that the TOAD Options screen can be opened either by using the Toolbox icon on the main toolbar or from the main menu at View, Options.

Figure 5.22. TOAD Database Health Check Email Settings.

Figure 5.23. TOAD E-Mail Options for Database Health.

The ideal scenario would be to have this report ready and waiting for you each morning in your e-mail inbox. That way you could start your day by quickly checking the exception status of all your databases (of course, no e- mails would mean no exceptions). To accomplish this, you must schedule this report to run on your Windows machine by using the Microsoft Task Scheduler as shown in Figure 5.24. For Windows 2000, this is located at Start, Settings, Control Panel, Scheduled Tasks, Add Scheduled Task.

Figure 5.24. Windows Scheduling of TOAD Health Check report.

Note that you're simply scheduling Windows on your PC to run TOAD in command-line mode, where you provide the connection information and a health check (HC) command text file. To produce that file is very simple. You merely need to open the Health Check screen, make all your selections, and then choose Save Options to File under the Save Settings to File drop-down toolbar icon. The generated file will contain everything necessary to start TOAD, connect to the database, and then run this screen. The generated command file will also contain comments on some commands you can add to tailor its behavior (for example, to close the screen and also shut down when the report is complete).