perl協程運算元據庫

liiinuuux發表於2016-09-26
perl用open2開啟兩個管道，接管sqlplus的標準輸入輸出，管理資料庫。

點選(此處)摺疊或開啟

  1. use IPC::Open2;
  2. use POSIX;

  4. sub conn
  5. {
  6.      my $r = shift;
  7.      my $w = shift;
  8.      my $conn_str = shift;
  9.      my $pid = open2($r, $w, "sqlplus -S ".$conn_str) or die "$!";
  10.      print $w "set lin 200\n" or die "$!";
  11.      print $w "set pages 0\n";
  12.      print $w "set feedback off\n";
  13.      $pid;
  14. }

  16. sub exec_sql
  17. {
  18.      my $r = shift;
  19.      my $w = shift;
  20.      my $sql = shift;
  21.      my @ret;
  22.      if($sql !~ /;$/){$sql .= ";";}
  23.      print $w $sql."\n";
  24.      print Writer "select 'OK' from dual;\n";
  25.      while(<Reader>)
  26.      {
  27.           chomp;
  28.           trim;
  29.           if(/^OK$/) {last;}
  30.           push @ret, $_;
  31.      }
  32.      @ret;
  33. }

  35. sub exec_sql_single_row
  36. {
  37.      my $r = shift;
  38.         my $w = shift;
  39.      my $sql = shift;
  40.      my @ret = &exec_sql($r, $w, $sql);
  41.      shift @ret;
  42. }

  44. sub get_param
  45. {
  46.      my $r = shift;
  47.         my $w = shift;
  48.      my $param = shift;
  49.      my $sql = "select value from v\$parameter where name = '$param'";
  50.      my @ret = &exec_sql($r, $w, $sql);
  51.      shift @ret;
  52. }

  54. sub date_format
  55. {
  56.      my $r = shift;
  57.         my $w = shift;
  58.      my $date = shift;
  59.      my $from_format = shift;
  60.      my $to_format = shift;
  61.      my $sql = "select to_char(to_date('$date', '$from_format'), '$to_format') from dual";
  62.      &exec_sql_single_row($r, $w, $sql);
  63. }

  65. sub get_db_time
  66. {
  67.      my $r = shift;
  68.      my $w = shift;
  69.      my $format = shift;
  70.      my $add = shift;
  71.      my $sql = "select to_char(sysdate $add, '$format') from dual";
  72.      &exec_sql_single_row($r, $w, $sql);
  73. }

  75. sub get_alert
  76. {
  77.      my $r = shift;
  78.         my $w = shift;
  79.      my $bdump = &get_param($r, $w, "background_dump_dest");
  80.         my $sid = &get_param($r, $w, "instance_name");
  81.         my $logfile = $bdump."/alert_".$sid.".log";
  82. }

  84. sub get_alert_time
  85. {
  86.      my $add_days = shift;
  87.      @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
  88.         @days = qw(Sun Mon Tue wed Thu Fri Sat);
  89.      my $str = strftime "%m-%d-%w", localtime(time + $add_days*60*60*24);
  90.      my($mm, $dd, $day) = split /-/, $str;
  91.      my $ret = "$days[$day] $months[$mm-1] $dd";
  92. }

  94. sub check_alert
  95. {
  96.      my $r = shift;
  97.      my $w = shift;
  98.         my $check_days = shift;
  99.      my $logfile = &get_alert($r, $w);
  100.         my $start = &get_alert_time($check_days * -1);
  101.         my $flag = 0;
  102.         my $last_time = "";
  103.      my @ret;
  104.         open(ALERT, $logfile) or die "open $logfile error: $!";
  105.         while(<ALERT>)
  106.         {
  107.                 if(/^$start/)
  108.                 {
  109.                         $flag = 1;
  110.                         $last_time = $_;
  111.                 }
  112.                 if($flag && /ORA-|Err/)
  113.                 {
  114.                         push @ret, "$last_time";
  115.                $last_time = "";
  116.                         push @ret, $_;
  117.                 }
  118.         }
  119.         close ALERT;
  120.      @ret;
  121. }

  123. sub check_logfile
  124. {
  125.         my $logfile = shift;
  126.      my $start = shift;
  127.      my $parts = shift;
  128.      my $flag = 0;
  129.      my @ret;
  130.      my $tmp_line;
  131.      open(LOGFILE, $logfile) or die "open $logfile error: $!";
  132.      while(<LOGFILE>)
  133.      {
  134.           $tmp_line = $_;
  135.           if(/^$start/)
  136.                 {
  137.                         $flag = 1;
  138.                         $last_time = $_;
  139.                 }
  140.           if( $flag && $tmp_line =~ /$parts/)
  141.           {
  142.                push @ret, $_;
  143.           }
  144.      }
  145.      close LOGFILE;
  146.      @ret;
  147. }

  149. my $r = \*Reader;
  150. my $w = \*Writer;

  152. my @ret;
  153. my $pid = &conn($r, $w, "/ as sysdba");

  155. print "logfiles:\n";
  156. @ret = &exec_sql($r, $w, "select member from v\$logfile order by 1");
  157. foreach (@ret)
  158. {
  159.      print $_."\n";
  160. }


  163. $sid = &get_param($r, $w, "instance_name");
  164. print "sid = $sid\n";

  166. $date = &exec_sql_single_row($r, $w, "select sysdate from dual;");
  167. print "date = $date\n";

  169. $str = &date_format($r, $w, "2014-1-1 23:11:44", "yyyy-mm-dd hh24:mi:ss", "dy mon dd hh24:mi");
  170. print "$str\n";

  172. $str = &get_db_time($r, $w, "yyyymmdd", -100);
  173. print "$str\n";

  175. my $timeStr1 = strftime "%Y-%m-%d %H:%M:%S", localtime;
  176. print $timeStr1."\n";

  178. print "alert time ".&get_alert_time(-22)."\n";

  180. print "check alert <".&get_alert($r, $w).">\n";
  181. @errs = &check_alert($r, $w, 22);
  182. foreach(@errs)
  183. {
  184.       print ;
  185. }

  187. @errs = &check_logfile(&get_alert($r, $w), &get_alert_time(-22), "^ORA-|Err");
  188. foreach(@errs)
  189. {
  190.         print ;
  191. }

  193. close Reader;
  194. close Writer;
  195. waitpid $pid, 0;











來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-2125606/，如需轉載，請註明出處，否則將追究法律責任。

相關文章