Chapter 5. Operations: Policy, Quotas, and Alerts 235
5.5 Database Alerts
Tivoli Storage Resource Manager for Databases enables you to define Alerts on instances,
databases, and tables. The Probe and Scan jobs output are processed and compared to the
defined alerts. If a threshold is reached, an Alert will be triggered.
Tivoli Storage Resource Manage for Databases uses the standard Alert mechanisms
described in 5.2, “OS Alerts” on page 189.
5.5.1 Instance Alerts
IBM Tivoli SRM for Databases -> Alerting -> Instance Alerts, right-click and select New
alert
lets you define some alerts as shown in Table 5-3. Those Alerts are triggered during the
Probe process.
Table 5-3 Instance Alerts
An interesting Alert is the Archive log contains more than for Oracle, since the Oracle
application can hang if there is no more space available for its archive log. This Alert can be
used to monitor the space used by in this specific directory and trigger a script that will
archive the files to an external manager such as Tivoli Storage Manager once the predefined
threshold is reached. Here is the procedure:
1. We defined an Instance Alert and selected the
Archive log contains more than
condition. We also specified that the script ARCHORA.BAT must be executed when the
Alert is fired. Note the parameters passed to the script.
Alert type Oracle Sybase MSSQL
New database discovered x x
New tablespace discovered x
Archive log contains more than X units x
New device discovered x
Device dropped x
Device free space greater than X units x
Device free space less than X units x
236 IBM Tivoli Storage Resource Manager: A Practical Introduction
Figure 5-97 Instance Alert definition
2. As the archive command must run on the server where Oracle resides, we set Triggering
Computer
in the Where to run pull-down field. This does not means that the script must be
physically copied on the monitored server.
3. On the Instance tab, we selected our Oracle server (
GALLIUM) and we saved the Alert as
ArchiveOracleLog.
Example 5-16 shows a sample script which we have written, ARCHORA.BAT, which will
archive the Oracle logs to a Tivoli Storage Manager server, and then delete them after
archive. It assumes you already have a Tivoli Storage Manager Server and client defined and
configured for your environment. Note this is a sample only, and should be customized and
tested for your environment.
Example 5-16 ARCHORA.BAT - Archive to TSM script
@ECHO OFF
REM Display starting messages
REM -------------------------
echo ARCHORA.BAT starting ...
echo on server %2
echo to archive %3 logs for instance %4
echo Directory to process : %1
echo Expecting %5 files to be archived for a total size of %6
REM Perform basic checks on db type and directory
REM ---------------------------------------------
if not %3 == Oracle GOTO NOTORACLE
if not exist %1 GOTO DIRNOTEXIST
REM Execute archive TSM command
REM ---------------------------
set logfile=ARCHORA.LOG
CD C:\Program files\Tivoli\tsm\baclient
Chapter 5. Operations: Policy, Quotas, and Alerts 237
@echo on
dir %1\ARC*.*
dsmc archive %1\ARC*.* -subdir=no -delete -descr="%2 %3 %4 ArchiveLogs" -verbose
@echo off
if not %errorlevel% == 0 GOTO DSMCERROR
@echo on
dir %1\ARC*.*
echo ARCHORA.BAT ended successfully ...
exit 0
:NOTORACLE
echo Error - Not Oracle database
exit 4
:DIRNOTEXIST
echo Error - Directory does not exist
exit 4
:DSMCERROR
echo Error while running DSMC command
dir %1\ARC*.*
type dsmerror.log
When the Probe job is run against the GALLIUM server, an Alert is fired. You can see its
output in Figure 5-98.
Figure 5-98 Instance Alert output
5.5.2 Database-Tablespace Alerts
To define a Database-Tablespace Alert, select IBM Tivoli SRM for Databases -> Alerting ->
Database-Tablespace Alerts
, right-click, and select New alert. You can define various
monitoring options on your databases as shown in Table 5-3. Those Alerts are triggered
during the Probe process.
238 IBM Tivoli Storage Resource Manager: A Practical Introduction
Table 5-4 Instance alerts
To avoid a Log Full condition, we will define an Alert to monitor log usage on our MS
SQL-Server database. When the log reaches 70% utilization, the Alert will trigger and
perform a backup of the transaction log.
Figure 5-99 Database alert definition
The script specified is SQLBKPLOG.BAT, listed in Example 5-17.
Example 5-17 MSSQL Log backup utility
@ECHO OFF
REM Display starting messages
REM -------------------------
echo SQLBKPLOG.BAT starting ...
echo on server %2
echo Transaction log of %3 database %4/%1 reaches %7 of its capacity
echo Performing transaction log backup
REM Perform basic checks on db type and directory
Alert type Oracle Sybase MSSQL
Database/Tablespace freespace lower than x x x
Database/Tablespace offline x x x
Database/Tablespace dropped x x x
Freespace fragmented in more than n extents x
Largest free extent lower than x
Database Log freespace lower than x x
Last dump time previous to n days x

Get IBM Tivoli Storage Resource Manager: A Practical Introduction now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.