Need to monitor DB table space threshold based on an output file in UNIX

[MuthuvelS]

Hi,

The requirement from my customer is to monitor an Oracle table space which is in cluster and the DB team has written a script that will run and write the output on to a file and the output looks similar to the below mentioned. The problem that I face is that when I use a logfile monitor to monitor this I'm unable to compare the threshold value for every table space and generate an alarm. 

Please suggest me a better way to monitor the table space, customer wants to have an alarm whenever the size goes beyond 80% used.

TABLESPACE_NAME PERCENTAGE_USED SPACE_ALLOCATED SPACE_USED SPACE_FREE DATAFILES

------------------------------ --------------- --------------- ---------- ---------- ----------

SYSAUX                                 51.38                                      4000                2055.25            1944.75  2

UNDOTBS2                           32.84                                 102400               33627.5             68772.5 6

or is there anyway we can create a template that runs the script and from the scipt output we can compare the threshold and genereate an alarm.

Thanks,

Muthuvel.S

[Trish_OMW]

Hi.

Do you have DB SPI installed? If so, you can check on policy DBSPI-0206.1/ DBSPI-0203/ DBSPI-0006/ DBSPI-0003?

The will offer different options for you to analyze the table space and set thresholds to trigger messages, but if you do not have this feature you can always use the measurement threshold policy, and select on the threshold tab what type of script you will be running.

You could also make a script and run it with a schedule task policy.

[Shawn Tripet]

Here is one way to do this:

Create two policies, one is a Scheduled Task policy to run a perl script (which is recommended to be deployed to node via Instrumentation) to grab the data from their output file and the other is a Measurement Threshold policy which will receive the data from the perl script. 

The Measurement Threshold policy has a "Shortname" for the Metric you are sending a value for and the name of the Shortname is significant and must be supplied in the script.  See the $polname and $shortname variables in the script which you will have to supply the correct values. 

You also will have to put the proper path to the file in the $DBFILE variable.

Here is a sample perl script to call (please test it first as I did not test it):

--------start perl script--------------

#/usr/bin/perl

my $critthresh = 80 ;

$polname = "MyMeasurementThresholdPolicyName" ;

$shortname = "MyShortnameInPolicy_Metricname" ;

$DBFILE = "/tmp/mydbfile" ;

open(DBFILE) or die("command FAILED") ;

while (my $line = <DBFILE>) {

#       $line = chomp($line) ;        

($tsname,$pctused,$spacealloc,$spaceused,$spacefree) = split(/\s+/,$line) ;

#        print "TableSpace Name is " . $tsname . "\n" ;

#        print "Utiliztion for " . $tsname . " is: " . $pctused . " Percent Used" . "\n" ;

        next if ($tsname eq "TABLESPACE_NAME") ;  

        next if ($tsname eq "------------------------------") ;

        if ($pctused >= $critthresh) {

#       print "Critical triggered for " . $tsname . " with " . $pctused . "Percent Used" . "\n" ;   

        $opcmonout = `opcmon $polname-$shortname=$pctused -object \"$tsname\"`        

        }

}

The opcmon command is the key here.  it will send your value and instance name to the Measurement Threshold policy.

opcmon command Syntax:

opcmon  <MeasurementThreshold PolicyName>-<Shortname>=<MetricDataValue>  -object  "<InstanceName>"

The sample perl script is also attached (note it has not been tested).

Hope this helps!

Shawn Tripet