Perl管理Oracle数据库-优快云博客

perl用open2打开两个管道，接管sqlplus的标准输入输出，管理数据库。

点击(此处)折叠或打开

use IPC::Open2;
use POSIX;
sub conn
{
my $r = shift;
my $w = shift;
my $conn_str = shift;
my $pid = open2($r, $w, "sqlplus -S ".$conn_str) or die "$!";
print $w "set lin 200\n" or die "$!";
print $w "set pages 0\n";
print $w "set feedback off\n";
$pid;
}
sub exec_sql
{
my $r = shift;
my $w = shift;
my $sql = shift;
my @ret;
if($sql !~ /;$/){$sql .= ";";}
print $w $sql."\n";
print Writer "select 'OK' from dual;\n";
while(<Reader>)
{
chomp;
trim;
if(/^OK$/) {last;}
push @ret, $_;
}
@ret;
}
sub exec_sql_single_row
{
my $r = shift;
my $w = shift;
my $sql = shift;
my @ret = &exec_sql($r, $w, $sql);
shift @ret;
}
sub get_param
{
my $r = shift;
my $w = shift;
my $param = shift;
my $sql = "select value from v\$parameter where name = '$param'";
my @ret = &exec_sql($r, $w, $sql);
shift @ret;
}
sub date_format
{
my $r = shift;
my $w = shift;
my $date = shift;
my $from_format = shift;
my $to_format = shift;
my $sql = "select to_char(to_date('$date', '$from_format'), '$to_format') from dual";
&exec_sql_single_row($r, $w, $sql);
}
sub get_db_time
{
my $r = shift;
my $w = shift;
my $format = shift;
my $add = shift;
my $sql = "select to_char(sysdate $add, '$format') from dual";
&exec_sql_single_row($r, $w, $sql);
}
sub get_alert
{
my $r = shift;
my $w = shift;
my $bdump = &get_param($r, $w, "background_dump_dest");
my $sid = &get_param($r, $w, "instance_name");
my $logfile = $bdump."/alert_".$sid.".log";
}
sub get_alert_time
{
my $add_days = shift;
@months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
@days = qw(Sun Mon Tue wed Thu Fri Sat);
my $str = strftime "%m-%d-%w", localtime(time + $add_days*60*60*24);
my($mm, $dd, $day) = split /-/, $str;
my $ret = "$days[$day] $months[$mm-1] $dd";
}
sub check_alert
{
my $r = shift;
my $w = shift;
my $check_days = shift;
my $logfile = &get_alert($r, $w);
my $start = &get_alert_time($check_days * -1);
my $flag = 0;
my $last_time = "";
my @ret;
open(ALERT, $logfile) or die "open $logfile error: $!";
while(<ALERT>)
{
if(/^$start/)
{
$flag = 1;
$last_time = $_;
}
if($flag && /ORA-|Err/)
{
push @ret, "$last_time";
$last_time = "";
push @ret, $_;
}
}
close ALERT;
@ret;
}
sub check_logfile
{
my $logfile = shift;
my $start = shift;
my $parts = shift;
my $flag = 0;
my @ret;
my $tmp_line;
open(LOGFILE, $logfile) or die "open $logfile error: $!";
while(<LOGFILE>)
{
$tmp_line = $_;
if(/^$start/)
{
$flag = 1;
$last_time = $_;
}
if( $flag && $tmp_line =~ /$parts/)
{
push @ret, $_;
}
}
close LOGFILE;
@ret;
}
my $r = \*Reader;
my $w = \*Writer;
my @ret;
my $pid = &conn($r, $w, "/ as sysdba");
print "logfiles:\n";
@ret = &exec_sql($r, $w, "select member from v\$logfile order by 1");
foreach (@ret)
{
print $_."\n";
}
$sid = &get_param($r, $w, "instance_name");
print "sid = $sid\n";
$date = &exec_sql_single_row($r, $w, "select sysdate from dual;");
print "date = $date\n";
$str = &date_format($r, $w, "2014-1-1 23:11:44", "yyyy-mm-dd hh24:mi:ss", "dy mon dd hh24:mi");
print "$str\n";
$str = &get_db_time($r, $w, "yyyymmdd", -100);
print "$str\n";
my $timeStr1 = strftime "%Y-%m-%d %H:%M:%S", localtime;
print $timeStr1."\n";
print "alert time ".&get_alert_time(-22)."\n";
print "check alert <".&get_alert($r, $w).">\n";
@errs = &check_alert($r, $w, 22);
foreach(@errs)
{
print ;
}
@errs = &check_logfile(&get_alert($r, $w), &get_alert_time(-22), "^ORA-|Err");
foreach(@errs)
{
print ;
}
close Reader;
close Writer;
waitpid $pid, 0;