Automated TOAD DB Health Check via Email

whinnycampingMechanics

Nov 5, 2013 (3 years and 7 months ago)

63 views

Automated
TOAD
DB
Health Check
via
Email


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
could


hit the ground running
” each morning
and address

any problems as soon as you read your email.

With “
Toad® DBA Suite for Oracle

you

can
easily
setup
receiving

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

can

also
share
all such
automated
Toad task

definitions

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
-
>Utilities


both shown here.





Think

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
d


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


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
instances


ORWI10
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


v
ia the
T
asks’
S
creen


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



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

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

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




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

that rule’s “
Params
” 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
via

the Toad “
Options
” 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
to

my local Windows XP, hence my “
SMTP Server
” settings. In your case
the settings for
SMTP
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
email
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.




The


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

current
screen’s status
(i.e. me
ta
-
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 “
App:
” field
permits
group
ing

several tasks together under
a single

named c
ollection


known as the application.

The
“Name:
” field permits assigning a name to the current or particular task


known as the action.

Therefore I would now have a new application named “
METHOD
-
#1
” with a single
task named “
DB_HEALTH_CHECK
”.




The


Clock
” 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 “
Camera
” 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
only
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
structure
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
from

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

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 “
METHOD
-
#2
”. We simply press the “
Create new app
” toolbar icon
,

as shown here
,

and
then
perform a right
-
hand
-
mouse action to “
R
ename

that new node to the desired name


because t
he
create icon simply adds an app node called “
App1
”.




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
-
hand
-
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
ic
on and press the left
-
hand
-
mouse button to insert that action into the application. Now you might ask
“Why do I have to press the left
-
hand
-
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
-
hand
-
mouse menu option for properties. Either way, the following screen opens.




Wow


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
absent
? There’s no need for them now.

So then how do we schedule it? We simply choose the right
-
hand
-
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…