自动批量生成AWR报告的脚本

这个脚本可以批量生成多个AWR脚本，只要指定数据库名（RAC环境应该是实例名）和时间段。如 C:/TEMP>perl dba_oracle_awr.pl --instance O02PAW1A --from 201103020700 --to 201103021800 --oracle_home C:/oracle/1020/client_1 --type text 就生成2011年3月2日早上7点到下午6点间的11份AWR报告。

 

当然适当修改脚本可以延伸些额外的功能，比如说你想2个小时生成一份报告，而不是默认的一小时，或者AWR snapshot每半小时生成一次。

 

#!/usr/bin/perl -w
#
# *******************************
# * File: dba_oracle_awr.pl
# *******************************
#  Usage: dba_oracle_awr.pl --instance [instance_name]
#            --from [from time]
#            --to [to time]
#            --dir [awr reports location]
#            --type [report type, html|text]
#            --oracle_home [oracle home]
#            --tns_admin [tns admin]
#   
#         time format: 'yyyymmddhh24miss'.
#         E.g 20110304170000 means 05:00:00pm, Mar 04, 2011
#
# Description: To automatically generate AWR reports in the specified
#              duration with default snapshot interval.
# Example: perl dba_oracle_awr.pl --instance O02DMS1 --from 201103020700
#              --to 201103030700 --oracle_home C:/oracle/1020/client_1 --type text
# 
#

use strict;
use Getopt::Long;
use Term::ReadKey;
use IPC::Open2;

my (
  $instance,
  $from,
  $to,
  $type,
  $oracle_home,
  $tns_admin,
  $sqlplus,
  $username,
  $password,
  $dir,
  $statement,
  $begin_file,
  $end_file,
  $begin_snap,
  $end_snap,
  $report_name,
  $help
);

 

################
# main
################
#
&get_input_params;
&test_connection;
&clean;
&generate_awr;

 

################################
# get script paramters
################################
#
sub get_input_params{

  GetOptions (
    'instance=s' => /$instance,
    'oracle_home=s' => /${oracle_home},
    'tns_admin=s' => /${tns_admin},
 'from=s' => /$from,
 'to=s' => /$to,
 'type=s' => /$type,
 'dir=s' => /$dir,
 'help|?' => /$help
  );
 
  &display_usage if $help;
  &display_usage if ($instance eq "" || ! $instance || $oracle_home eq "" || ! $oracle_home);
  &display_usage if (! $from ||$from eq "" ||! $to||$to eq ""  );
 
  $tns_admin = "${oracle_home}//NETWORK//ADMIN" if (! $tns_admin ||$tns_admin eq "");
#  $dir = "." if ($dir eq "" || ! $dir);
 
  if ($type eq "" ||! $type)
  {
    $type = "html";
  }elsif (lc($type) ne "text" && lc($type) ne "html")
  {
   print "invalid report type: $type/n";
    &display_usage;
  }
 
  $ENV{'ORACLE_HOME'} = $oracle_home;
  $ENV{'TNS_ADMIN'} = $tns_admin;
  $begin_file = "begin_snap.out";
  $end_file = "end_snap.out";
}

 

################################
# display usage
################################
#
sub display_usage{

  my $text = <<ENDTEXT;
  Usage: dba_oracle_awr.pl --instance [instance_name]
            --oracle_home [ORACLE HOME]
            --tns_admin [TNS_ADMIN path]
            --from [from time]
            --to [to time]
            --dir [awr reports location]
            --type [report type, text|html]
   
         time format: 'yyyymmddhh24miss'.
         E.g 20110304170000 means 05:00:00pm, Mar 04, 2011
ENDTEXT

print "$text/n";
exit 0;
}

 

################################
# test connection
################################
#
sub test_connection{

my (
  $pid,
  @out,
  $line
  ); 
 
  if($^O =~ /win/i)
  {
     $sqlplus = "${oracle_home}/bin/sqlplus.exe";
     $sqlplus =~ s#/#//#g;
  }else
  {
    $sqlplus = "${oracle_home}/bin/sqlplus";
  }

  print "/nuser name: ";
  $username = <STDIN>;
  chomp($username);
  ReadMode('noecho');
  print "password: "; 
  $password = ReadLine(0);
  chomp($password);
  print "/n";
  ReadMode('normal');

  $pid = open2(/*READ,/*WRITE, "$sqlplus -s /nolog") or die "Failed to start sqlplus: $!";
  print WRITE<<EOF;
  conn $username/$password/@$instance
  set head off
  set feed off
  select instance_name from v/$instance;
  exit;
EOF

  @out = <READ>;
 
  foreach $line (@out)
  {
   if ( $line =~ /ORA-/ || $line =~ /sp2/i)
   {
     print "Database connection failed: /n";
     print "$line/n";
     print "Program exiting...";
     die;
   }
  }
 # print "DEBUG: Database connected successfully/n";
}

 

################################
# generate AWR reports
################################
#
sub generate_awr{
my
(
  $entry,
  $snap1,
  $snap2
);
 
  $statement = <<ENDTEXT;
  set head off
  set feed off
  spool $begin_file
  select max(SNAP_ID) from DBA_HIST_SNAPSHOT where
    BEGIN_INTERVAL_TIME<=to_date($from,'yyyymmddhh24miss');
  spool off
  spool $end_file
  select min(SNAP_ID) from DBA_HIST_SNAPSHOT where
    END_INTERVAL_TIME>=to_date($to,'yyyymmddhh24miss');
  spool off
ENDTEXT
 
  open ORA, "| $sqlplus -s $username/$password/@$instance" or die "Failed to start sqlplus: $!";
  print ORA "$statement";
  close ORA;
 
  open (BEGIN, $begin_file)|| die "Cannot open $begin_file: $!";
  while (<BEGIN>)
  {
    chomp($_);
    $entry = $_;
    if ( $entry )
    {
      $begin_snap = trim($entry);
    }
  }

  open (END, $end_file)|| die "Cannot open $end_file: $!";
  while (<END>)
  {
    chomp($_);
    $entry = $_;
    if ( $entry )
    {
      $end_snap = trim($entry);
    }
  } 
 
  close BEGIN;
  close END;
 
  $snap1 = $begin_snap;
  while ($snap1 < $end_snap)
  {
   $snap2 = $snap1 + 1;
    if (lc($type) eq "text")
    {
      $report_name = "awrrpt_${instance}_${snap1}_${snap2}.txt";
    } else {
      $report_name = "awrrpt_${instance}_${snap1}_${snap2}.html";
    }
 
#  print "DEBUG Report Name: ${report_name}/n";
 
  open ORA, "| $sqlplus -s $username/$password/@$instance" or die "Failed to start sqlplus: $!";
  print ORA "set term off/n";
  print ORA "define report_type=$type/n";
  print ORA "define num_days=1/n";
  print ORA "define begin_snap=${snap1}/n";
  print ORA "define end_snap=${snap2}/n";
  print ORA "define report_name=${report_name}/n";
  print ORA "/@${oracle_home}//rdbms//admin//awrrpt.sql/n";
  close ORA;
 
  $snap1 = $snap2;
  }
}

 

sub trim($)
{
 my $string = shift;
 $string =~ s/^/s+//;
 $string =~ s//s+$//;
 return $string;
}

 

sub clean()
{
  unlink ($begin_file)if (-e $begin_file);
  unlink ($end_file) if (-e $end_file);
}