ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用PERL 监控公司所有DB TABLESPACE 使用情况

用PERL 监控公司所有DB TABLESPACE 使用情况

原创 Linux操作系统 作者:xhailiang 时间:2007-07-07 00:00:00 0 删除 编辑

起因:因公司DB过多,每个都要设置TABLESPACE 使用量,原先的做法是每台都COPY SCRIPTS 和设置 CRONT JOB.和SENDMAIL,并且一天有时候警告邮件很多比较烦。

想在一台设置一个CRON JOB 固定每小时去CHECK一次所有的DB的TABLESPACE 使用量,然后汇集到一个表格形式发给DBA,这样一天MAIL 量少了,而且格式看起来也很舒服。

在一个DB 中(暂且叫MONITOR DB,这个DB 以后还会用来收集各台SAR 信息,SEGMENT 增长情况信息)建一个C_TAB_DATABASE表,里面记录各DB 的SID ,另建一个与SID对应的要收集TABLESPACE NAME的表。然后使用PERL实现连到每一台去收集TABLESPACE 使用情况(我的第一个PERL程式哦)。


#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/9ir2";
my $p1 = "-" x 15;
my $p2 = "-" x 30;
my $p3 = "-" x 8;
my $p4 = "-" x 8;
my $p5 = "-" x 6;
my $LOGDIR="/home/smmon/bin/";

my $MAILLIST = "hailiang.xiong@study.com";
my $CCMAILLIST = "hailiang.xiong@study.com";
my $DATE = localtime();
my $exceptdb = "SID ";
my $exceptpct = '';
my $hour = `date +'%H'`;
my $pct = '';
#my $filter = '';
if ($hour >= 17 and $hour >"$LOGDIR"TB
S.LOG`}
}
else
{$pct = 85;
$exceptpct = 90;
{`echo "$DATE +$p1+$p2+$p3+$p4+$p5+ |DBNAME |TBS_NAME |SUM_SIZE|FREESIZE| PC
T | +$p1+$p2+$p3+$p4+$p5+" >>"$LOGDIR"TBS.LOG`}
}
my $odbh = DBI->connect( 'dbi:Oracle:monitordb',
'username',
'password',
{ RaiseError => 1, AutoCommit => 0 }
) || die "Database connection not made: $DBI::errstr" ;
my $osth = $odbh->prepare(qq{SELECT UPPER(SID) FROM C_TAB_DATABASE});
$osth->execute();
while (my $SID = $osth->fetchrow_array())
{
my $dbh = '';
my $DATA_SOURCE = "dbi:Oracle:$SID";
eval { $dbh = DBI->connect(
$DATA_SOURCE,
'system',
'password',
{ RaiseError => 1, AutoCommit => 0 }
) || die "Database connection not made: $DBI::errstr";
};
if ($dbh)
{# success connecting
my $tbs = $dbh->prepare( qq{
SELECT substr (d.NAME || ' ', 1, 15) AS
DBNAME,
SUBSTR (a.tablespace_name || ' ', 1, 30) AS
TBS_NAME,
SUBSTR (a.BYTES ||' ', 1, 8) AS
SUM_SIZE,
SUBSTR (TO_CHAR(decode(ROUND(c.BYTES,1),null,0,ROUND(c.bytes,1)))||'
', 1, 8) AS FREE_SIZE,
substr (ROUND (100 * (1 - (NVL (c.BYTES, 0)
/ NVL (a.BYTES, 0))), 2)||' ',1,5) AS USAGE_PCT
FROM (SELECT tablespace_name,SUM (BYTES) / 1024 / 1024 AS BYTES
FROM dba_data_files
WHERE FILE_ID NOT IN (SELECT FILE# FROM V$DATAFILE
WHERE ENABLED='READ ONLY')
GROUP BY tablespace_name) a,
(SELECT f.tablespace_name,SUM (f.BYTES) / 1024 / 1024 AS BYTES
FROM dba_free_space f
GROUP BY f.tablespace_name) c,
v$database d
WHERE a.tablespace_name = c.tablespace_name(+)
and a.tablespace_name not like 'PTS%'
and a.tablespace_name not like 'MESARCH0%'
});
$tbs->execute() or die "ERROR: $tbs->errstr ";
while ( my($DBNAME,$TBS_NAME,$SUM_SIZE,$FREE_SIZE,$USAGE_PCT) = $tbs->fetchrow_array())
{ if ($USAGE_PCT >=$exceptpct and $DBNAME eq $exceptdb )
{`echo "|$DBNAME|$TBS_NAME|$SUM_SIZE|$FREE_SIZE|$USAGE_PCT%| +$p1+$p2+$p3+$p4+$p5+" >>"$LOGDIR"TBS.LOG`}
elsif ($USAGE_PCT >= $pct and $DBNAME ne $exceptdb)
{`echo "|$DBNAME|$TBS_NAME|$SUM_SIZE|$FREE_SIZE|$USAGE_PCT%| +$p1+$p2+$p3+$p4+$p5+" >>"$LOGDIR"TBS.LOG`}
else{}
}
$tbs->finish;
$dbh->disconnect();
}
else { # failure
print "$dbh ";
`echo "Tablespace Usage Alert: Cannot connect to $SID !" | mailx -s "Please check $SID as soon as possible!---$DATE" "$MAILLIST" -c "$CCMAILLIST"`}
}
$osth->finish;
$odbh->disconnect();

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171991/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    414556