IBM Tivoli Storage Resource Manager: A Practical Introduction

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