这个脚本可以批量生成多个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, 2011ENDTEXT
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 offENDTEXT 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);}
