Perl 写的巡检数据库的脚本

#!/usr/bin/perl
my $red="e[1;31m";
my $green="e[1;32m";
my $yellow="e[1;33m";
my $normal="e[0m";
use DBI; 
my  $dbName = 'june';  
my  $dbUser = 'test';  
my  $dbUserPass = 'test';  
my  $dbh = DBI->connect("dbi:Oracle:$dbName",$dbUser,$dbUserPass) or die "can't connect to database " ;
$dbh->{LongReadLen} = 100;
$dbh->{LongTruncOk} = 1;

sub section() {
    my $section=shift;
    print ">>>>>$green $section $normal  n";
};
sub get_lock {
§ion("LOCK INFO");
my $hostSql = qq{select  t3.sid,t4.object_name,t3.MACHINE,t3.PROGRAM,t2.ctime 
   from v$process t1, 
        (select sid,type,id1,id2,lmode,request,ctime 
           from v$lock 
          where  sid in (select sid 
                          from v$lock 
                         where TYPE = 'TX' 
                           and LMODE = 6) 
                           and type='TM') t2,
        v$session t3,dba_objects t4 
  where t2.sid = t3.sid  
    and t1.addr = t3.paddr 
    and t2.id1=t4.object_id };
my ($a,$b,$c,$e,$f,$g);
my $selStmt = $dbh->prepare($hostSql);  
$selStmt->bind_columns(undef,$a,$b,$c,$d,$e);  
$selStmt->execute();  
           printf("%-10s%-20s%-40s%-25s%-10sn",SID,OBJECT_NAME,MACHINE,PROGRAM,CTIME);
while( $selStmt->fetch() ){ 
        if ($e > 10){
          printf("%-10s%-20s%-40s%-25s${red}%-10s${normal}%sn","$a",$d,$e);

}};  
$selStmt->finish;  
#$dbh->disconnect or warn "DB disconnect failed: $DBI::errstrn";  
};
#####################################################################
sub get_top_seg{
my ($a,$g);
§ion("TOP SEG");
 my $hostsql=qq{select *  
  from (select segment_name,sum(bytes / 1024 / 1024) 
          from dba_segments 
         group by segment_name 
         order by 2 desc) 
 where rownum < 10}; 
my $selStmt = $dbh->prepare($hostsql);   
$selStmt->bind_columns(undef,$b);
$selStmt->execute();   
       printf("%-50s%-6sn","SEGMENT_NAME","BYTES(MB)");  
while( $selStmt->fetch() ){  
        printf ("%-50s%-6sn",$a,$b);
}   
$selStmt->finish;  

};
############################################################################
sub temp_usg{
§ion("Temp USAGE");
my ($a,$g);
 my $hostsql=qq{select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,round((1-free_blocks/total_blocks) * 100)  from v$sort_segment};  
my $selStmt = $dbh->prepare($hostsql);    
$selStmt->bind_columns(undef,$e,$f); 
$selStmt->execute();    
      printf ("%-20s%-20s%-20s%-20s%-20s%-20sn","TABLESPACE","CURRENT_USERS","TOTAL_BLCOKS","USED_BLOCKS","FREE_BLOCKS","USED_PCT");
while( $selStmt->fetch() ){   
    if ("$f" > 70 ){
     printf ("%-20s%-20s%-20s%-20s%-20s${red}%-20s${normal}%sn","$b","$c","$d","$e","$f")
;
}   else{printf ("%-20s%-20s%-20s%-20s%-20s%-20s%sn","$f")}
 };   
$selStmt->finish;   
 
};
###############################################################################
sub sort_ses{ 
§ion("SORT SESSION"); 
my ($a,$c); 
 my $hostsql=qq{select sid,username,program from v$session where saddr in (select session_addr from V$TEMPSEG_USAGE)};   
my $selStmt = $dbh->prepare($hostsql);     
$selStmt->bind_columns(undef,$c);  
$selStmt->execute();     
      printf ("%-20s%-20s%-20sn","SID","USERNAME","PROGRAM"); 
while( $selStmt->fetch() ){    
   {printf ("%-20s%-20s%-20sn","$c")} 
 };    
$selStmt->finish;    
  
};
################################################################################
sub sort_info{
§ion("SORT INFORMATION");  
my ($a,$d);  
 my $hostsql=qq{select username,SESSION_ADDR,sql_id,segtype from V$TEMPSEG_USAGE };    
my $selStmt = $dbh->prepare($hostsql);      
$selStmt->bind_columns(undef,$d);   
$selStmt->execute();      
      printf ("%-20s%-20s%-20s%-20sn","SESSION_ADDR","SQL_ID","SEGTYPE");  
while( $selStmt->fetch() ){     
   {printf ("%-20s%-20s%-20s%-20sn","$d")}  
 };     
$selStmt->finish;     
   
}; 
#################################################################################
sub db_info{ 
§ion("DATABASE INFORMATION");   
my ($a,$g);   
 my $hostsql=qq{select b.host_name,name,platform_name,b.version,created,log_mode,open_mode from v$database a,v$instance b};     
my $selStmt = $dbh->prepare($hostsql);       
$selStmt->bind_columns(undef,$f,$g);    
$selStmt->execute();       
      printf ("%-20s%-20s%-30s%-20s%-20s%-20s%-20sn","HOST_NAME","DB_NAME","PLATFORM_NAME","VERSION","DB CREATED","LOG_MODE","OPEN_MODE");   
while( $selStmt->fetch() ){      
   {printf ("%-20s%-20s%-30s%-20s%-20s%-20s%-20sn","$f","$g")}   
 };      
$selStmt->finish;      
    
};
#######################################################################################
sub undo_usage{  
§ion("UNDO INFORMATION");    
my ($a,$b);    
 my $hostsql=qq{select tablespace_name,retention from dba_tablespaces where tablespace_name=(select VALUE from v$parameter where name ='undo_tablespace')};      
my $selStmt = $dbh->prepare($hostsql);        
$selStmt->bind_columns(undef,$b);     
$selStmt->execute();        
      printf ("%-20s%-20sn","TABLESPACE_NAME","RETENTION");    
while( $selStmt->fetch() ){       
   {printf ("%-20s%-20sn","$b")}    
 };       

$selStmt->finish;       
my ($c,$d);
my $hostsql=qq{select * from (select sum(bytes)/1024/1024 use_mb,status from dba_undo_extents group by status)};
my $selStmt = $dbh->prepare($hostsql);         
$selStmt->bind_columns(undef,$d);      
$selStmt->execute();         
      printf ("%-20s%-20sn","USED_MB","STATUS");     
while( $selStmt->fetch() ){        
   {printf ("%-20s%-20sn","$d")}     
 };  

if ("$b" eq "NOGUARANTEE"){
my ($e); 
my $hostsql=qq{select nvl((select USE_MB from (select sum(bytes)/1024/1024 use_mb,status from dba_undo_extents group by status) where STATUS='ACTIVE'),0) from dual};
my $selStmt = $dbh->prepare($hostsql);  

$selStmt->bind_columns(undef,$e);       

$selStmt->execute();
   printf ("%-20sn","UNDO USAGE(MB)");
while( $selStmt->fetch() ){         
   {printf ("%-20sn","$e")}      
 };
}else {

my ($f);  
my $hostsql=qq{select nvl((select used_mb 
          from (select sum(bytes) / 1024 / 1024 as used_mb,status 
                  from dba_undo_extents 
                 group by status) 
         where STATUS = 'ACTIVE'),0) + 
       nvl((select used_mb 
          from (select sum(bytes) / 1024 / 1024 as used_mb,status
                  from dba_undo_extents  
                 group by status) 
         where STATUS = 'UNEXPIRED'),0)
  from dual  };
my $selStmt = $dbh->prepare($hostsql);   
$selStmt->bind_columns(undef,$e);        
$selStmt->execute(); 
   printf ("%-20sn","UNDO USAGE(MB)"); 
while( $selStmt->fetch() ){          
   {printf ("%-20sn","$e")}       
 }; 
}
}; 
###############################################################################################
sub get_undo_seg{ 
my ($a,$d); 
§ion("UNDO SEGMENT USAGE"); 
 my $hostsql=qq{select s.sid,s.username,u.name,t.USED_UBLK 
  from v$transaction t,v$rollstat r,v$rollname u,v$session s 
 where s.taddr = t.addr 
   and t.xidusn = r.usn 
   and r.usn = u.usn 
 order by s.username};
my $selStmt = $dbh->prepare($hostsql);    
$selStmt->bind_columns(undef,$d); 
$selStmt->execute();    
       printf("%-15s%-20s%-40s%sn","NAME","USED_UBLK");   
while( $selStmt->fetch() ){   
       printf("%-15s%-20s%-40s%sn","$d");   
}    
$selStmt->finish;   
 
};
######################################################################################################
sub get_tran{  
§ion("TRANSACTION  COUNT");    
my $hostsql=qq{select count(*) from v$transaction};      
my $sth = $dbh->prepare($hostsql);
$sth->execute();
my $count = $sth->fetchrow_array();
print "TRANSACTION  COUNT is $countn";
$sth->finish;
     
}; 
#####################################################################################################
sub get_session{
§ion("SESSION  COUNT");
my $hostsql=qq{select count(*) from v$session};
my $sth = $dbh->prepare($hostsql);
$sth->execute();
my $count = $sth->fetchrow_array();
print "SESSION  COUNT is $countn";
$sth->finish;

};
#######################################################################################################
sub get_tbs{   
§ion("TABLESPACE  USAGE");     
my ($a,$e);  
 my $hostsql=qq{select a.tablespace_name,round(a.total_size,1) "total(M)",   
  round(a.total_size)-round(nvl(b.free_size,0),1) "used(M)",   
  round(nvl(b.free_size,1) "free(M)",   
 round(100- round(nvl(b.free_size,0)/total_size*100,1)) "used rate(%)"   
  from (select tablespace_name,sum(bytes)/1024/1024 total_size    
  from dba_data_files    
  group by tablespace_name) a,   
  (select tablespace_name,sum(bytes)/1024/1024 free_size    
  from dba_free_space    
group by tablespace_name) b    
  where a.tablespace_name = b.tablespace_name(+)    
order by "used rate(%)" };
my $selStmt = $dbh->prepare($hostsql);     
$selStmt->bind_columns(undef,$e);  
$selStmt->execute();     
       printf("%-25s%-20s%-20s%-20s%-20sn","TOTAL(MB)","USED(MB)","FREE(MB)","USED RATE(%)");    
while( $selStmt->fetch() ){    
      if ("$e" > 80){
       printf("%-25s%-20s%-20s%-20s${red}%-20s${normal}%sn","$e");    
     }else
    {
       printf("%-25s%-20s%-20s%-20s%-20s%sn","$e");    
     }
}     
$selStmt->finish;    
  
}; 
################################################################################################
sub getTime{

    my $time = shift || time();
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time);

    $year += 1900;
    $mon ++;
    $min  = '0'.$min  if length($min)  < 2;
    $sec  = '0'.$sec  if length($sec)  < 2;
    $mon  = '0'.$mon  if length($mon)  < 2;
    $mday = '0'.$mday if length($mday) < 2;
    $hour = '0'.$hour if length($hour) < 2;
    my $weekday = ('Sun','Mon','Tue','Wed','Thu','Fri','Sat')[$wday];

    return { 'second' => $sec,'minute' => $min,'hour'   => $hour,'day'    => $mday,'month'  => $mon,'year'   => $year,'weekNo' => $wday,'wday'   => $weekday,'yday'   => $yday,'date'   => "$year-$mon-$mday"
          };

}

my $date = &getTime();
my $now_datetime =$date->{'year'}.'-'.$date->{'month'}.'-'.$date->{'day'}." ".$date->{'hour'}.":00:00";
#print "$now_datatime is $now_datetimen";
my $old_time =$date->{'year'}.'-'.$date->{'month'}.'-'.$date->{'day'}." ".($date->{'hour'} -1).":00:00";
#print "$old_time is $old_timen";
#######################################################################################################
sub get_top_iosql{ 
my ($a,$g,$h,$i); 
§ion("TOP 10 DISK READ SQL"); 
 my $hostsql=qq{select * 
  from (select a.parsing_schema_name,
               sum(a.executions_delta) executions,
               sum(a.DISK_READS_delta) disk_reads,
               sum(a.DIRECT_WRITES_delta) direct_writes,
               round(sum(a.CPU_TIME_delta) / 1000000 / 60,2) cpu_time_min,
               round(sum(a.ELAPSED_TIME_delta) / 1000000 / 60,2) elapsed_time_min,
               round(sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024,2) physical_read_gb,
               round(sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024,2) physical_write_gb,
               substr((select sql_text
                  from v$sqlarea c
                 where c.sql_id = a.sql_id
                   and rownum = 1),1,100)
          from DBA_HIST_SQLSTAT a,DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
           and b.BEGIN_INTERVAL_TIME >=
               to_date('$old_time','YYYY-MM-DD HH24:MI:SS') ---开始时间    
           and END_INTERVAL_TIME <=
               to_date('$now_datetime','YYYY-MM-DD HH24:MI:SS') ---结束时间  
         group by parsing_schema_name,a.sql_id
         order by 7 desc)
 where rownum <= 10};
my $selStmt = $dbh->prepare($hostsql);    
print "$hostsql is $hostsqln";
$selStmt->bind_columns(undef,$g,$h,$i); 
$selStmt->execute();    
       printf("%-15s%-20s%-40s%sn","USED_UBLK");   
while( $selStmt->fetch() ){   
       printf("%-15s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%sn","$g","$h","$i");   
}    
$selStmt->finish;   
 
};
#############################################################################################################
sub get_event{
§ion("WAIT   EVENT");
my ($a,$b);
my $hostsql=qq{select event,count(*) from v$session where event not like ('%idle%')  group by event};
my $selStmt = $dbh->prepare($hostsql);
$selStmt->bind_columns(undef,$b);
$selStmt->execute();
       printf("%-100s%-20s%sn","EVENT","COUNT(*)");
####定义报警的event
my @warn_event=('cursor: pin S wait on X','latch: cache buffers chains','buffer busy waits ','db file scattered read','library cache pin','read by other session','direct path read','log file sync','library cache lock','latch: shared pool','row cache lock','enq: TX - row lock contention','log buffer space','library cache: mutex X','log file parallel write');
while( $selStmt->fetch() ){
      if (grep m/$a/,@warn_event ){
       printf("${red}%-100s${normal}${red}%-20s${normal}%sn","$b");
     }else
    {
       printf("%-100s%-20s%sn","$b");
     }
};
$selStmt->finish;

};
      
&db_info();
&get_lock();
&get_top_seg();
&temp_usg();
&sort_ses();
&sort_info();
&undo_usage();
&get_undo_seg();
&get_tran();
&get_session();
&get_tbs();
&get_event();