Automated TOAD DB Health Check via Email


Nov 5, 2013 (4 years and 8 months ago)


Health Check

Wouldn’t it be nice to have an email message every morning in your inbox regarding the relative health
of your databases

specifically only exception type reporting for those that have issues? That way you

hit the ground running
” each morning
and address

any problems as soon as you read your email.

With “
Toad® DBA Suite for Oracle



such automated database health check
problem reports
via email
in just a few simple steps. Furt
hermore, you


all such
Toad task


via email.

The Toad “
Automation Designer
” is the magic behind automating most any Toad task. You can access it
via the main toolbar or from the main menu

both shown here.


of this Automation Designer as a general purpose tool for capturing or defining nearly any task
that you normally perform in Toad via a screen or utility. In this case we’ll only be examining the most
basic Automation Designer functionality offere

to define, run and schedule a single task. However
you can
define very complex groupings of tasks with full programmatic control as well. Regardless
of whether it’s a
or highly complex grouping of tasks, you then name the automated tas

which is considered an application.

There is practically no limit as to what you can automate. In fact if
you embrace the Automation Designer, over time you may define so many such automated tasks that
you’ll manually use the underlying screen themselv
es on rarely. That’s the ultimate in productivity as it
means you’ve automated most, if not all, of the work you do on a routine basis.

So let’s quickly walk through the process of defining an automated database health check for two
of my
Oracle 10g

on Windows
and ORLI10 on Linux

Note well that there are two different
methods by which we can accomplish defining this automated task

so examine both the following two
examples to determine which method that you prefer.

Method #1

ia the

In this example I want to define the automation of a database health check, so that’s the screen that I

just like I would normally do
. So I highlight my two Oracle 10g database instances on the left
hand side

then using
the “
” tab
check or uncheck all the
health check
rules that I want to

as shown here. So far there’s nothing new or different.

Then I switch to the “
” tab and make sure to check the “Always send results by email” rule and
also open

that rule’s “
” pop
up to select the email format and whether it is always sent
or only
when there is a problem as shown here.

Now if I were not sending the output via email, I’d simply press the green arrow toolbar button on this
screen to simpl
y run the database health check. But since I’ve indicated that I want the results by email,
I also need to define my email settings

the Toad “
” screen

as shown here.

So I’ve defined my email settings for the Health Check. In my case I
have also
loaded a freeware SMTP
server on

my local Windows XP, hence my “
SMTP Server
” settings. In your case
the settings for
Server, User Name, Password and Port
will probably be
the same as
your corporate email server that
you normally use with y
our normal email client (e.g. Outlook, Outlook Express, etc.).

You can

test your
settings by pressing the “
Send Test Mail
” button. Once you have it working the way you want,
then you can copy all those settings (except for the Subject) by pressing th
e “
Clone Settings
” button.

Normally I’d be done at this point. Now that the email settings are defined, I could run the health check
report. But we I want to automate this task, thus I need a way to tell Toad to record a snapshot of what
I’ve done on this
screen. Note the two toolbar icons in the bottom left hand corner of the health check
screen shown below.


” icon launches the pop
up window f
or either saving or loading the

screen’s status
(i.e. me
data about what selections have

been made or to set). In this case I need to “
Save as Action
which means that I need to define an application name and action name by which this meta
data can be
referenced. The “
” field

several tasks together under
a single

named c

known as the application.

” field permits assigning a name to the current or particular task

known as the action.

Therefore I would now have a new application named “
” with a single
task named “


” icon launches a pop
up window for scheduling an application’s action. Thus I could schedule

my application and task. Since I had already created them first via the “
” icon, they automatically
fill in as the default choices. Had I skipped the l
ast step entirely, I could have simply entered the names
here and the application and task would have been created as part of this process.

Now I could simply define the daily automatic run schedule as shown in the next two screen snapshots.
So now I h
ave an automatic database health check running every day at 6:00 AM and
sending the
output to me via email if there are problems or issues.

Method #2

via the Automation Designer

The prior method is quick and easy for defining applications th
at contain just a single task or action. But
as you learn the Automation Designer and its true power, you’ll want to define much more complex and
powerful applications

which contain groups of tasks or actions

and which have programming control
s such as conditional statements and loops.

For that and much more, you’ll need to master this
method. And once you get accustomed to it, it actually becomes more straight forward (especially when
defining complex applications) than the prior method.

To us
e this method, I now simply invoke the Automation Designer itself directly (refer back to first two
screen snapshots of the main toolbar and main menu for this). That launches the Automation Designer
screen shown here

and look, there’s the work from meth
od #1

the prior example. Think of the
Automation Designer screen as your “
Schema Browser
” type interface into your applications and their

And much like the database schema browser, the Automation Designer is the central facility for
quickly a
nd easily managing applications and their actions. Therefore it should eventually become your
primary interface when automating things within Toad.

So now let’s create a second application that does exactly the
same thing as the prior example, but now
let’s do it 100% from within the Automation Designer itself. So first we simply create a new application
which we’ll call “
”. We simply press the “
Create new app
” toolbar icon

as shown here

perform a right
mouse action to “

that new node to the desired name

because t
create icon simply adds an app node called “

Now it’s time to add a task or action to this new application. Remember, in this example we’re simply
going to add just a single action

so this may see
m like to harder way of accomplishing this easy task.
But once you start creating complex, multi
action applications

this screen is really the only way to do
it. Note the tabs on the right
side of the screen. All screens, reports or utilities within

Toad that
have been enabled for the Automation Designer will appear under one of these tabs. Note too that
when using method #1 that not all Toad screens will have the two icons in the bottom left hand corner.
That’s because not all of them have been auto
mation enabled

yet. Over time and with user feedback,
the automation enabled list will grow

possibly even someday being for everything in Toad!

So looking over the tabs, I find that the DB Health Check if offered under the “
DB Misc
” tab. Select that
on and press the left
mouse button to insert that action into the application. Now you might ask
“Why do I have to press the left
mouse button?” Why doesn’t it simply just add the action when I
press the toolbar icon? The answer is that later whe
n you’re creating much more complex applications,
you’ll want to be able to place the selected action within the collection and programming structures at
the appropriate or desired location.

After I’ve selected the health check action, my application n
ow looks like this:

I can now either double
click on the newly added action “
DB Health Check1
” or by choosing the right
mouse menu option for properties. Either way, the following screen opens.


that’s exactly the same screen as if I ha
d opened it via the Database health Check from the main
menu. Because in fact it is

it’s just now that Toad knows that whatever you select in that screen is to
be automatically defined as an action. Did you note that the two icons in the bottom left corn
er were
? There’s no need for them now.

So then how do we schedule it? We simply choose the right
mouse menu option to schedule for the application as shown here. That’s it.

Now obviously there a ton more that can be done from the Automation

Designer screen. But that will
have to wait until another blog. So keep coming back to Toad World and reading my blogs

as I’ll be
doing a series on some of the neat stuff you can do with more complex automated applications…