不停机处理oracle超过最大processes数故障

在做oracle数据库管理的时候，经常会有用户遇到超过最大进程数的错误，表现为新的连接无法登入数据库。一致提示超过最大的process数 。其实这个问题，如果用户是测试环境，好解决。直接关闭数据库或者直接kill掉所有的"LOCAL=NO"的进程。

但是很多情况是，用户无法接受停机，或者kill掉所有的远端连接。基于以上情况，写了如下脚本

#!/usr/bin/perl#write by wulei#get the first parameter$arg1="";chomp($arg1);while($arg1 eq ""){  print "please input your first parameter:";  $arg1=;  chomp($arg1);  if($arg1 ne ""){    @temp1=`ps -eo lstart,pid,args | grep '$arg1' | grep -v grep`;    $process_count=`ps -eo lstart,pid,args | grep '$arg1' | grep -v grep | wc -l`;    chomp($process_count);    if($process_count eq "0")    {       $arg1="";      print "we got 0 processes,please retry!\n";      next;    }    print "We will kill $process_count(count) processes\n";    print "All the processes list below!!!!!!!!!!!!!!!!!\n";    print "#############################################################\n";    print @temp1;  }  chomp($arg1);}#get the second parameter$arg2="";chomp($arg2);while($arg2 eq ""){print "\n";print "\n";print "############################################################\n";print "#[null] kill all the process we had got                    #\n";print "#[num ] kill the process start at before sysdate-number    #\n";print "if you want exit,enter 'ctrl+c'                            #\n";print "############################################################\n";print "please input your second parameter:";$arg2=;chomp($arg2);if($arg2 eq ""){   print "Are you sure,to kill all the process above:[y/n]";   $confirm=;   chomp($confirm);   if($confirm eq "Y" or $confirm eq "y")   {   #kill all the process ,we got it   @result=`ps -eo pid,args | grep '$arg1' | grep -v grep`;    print "Kill List !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n";    print "###################################################################\n";     foreach $result (@result)     {     @result1=split(/\s+/,$result);     chomp($result1[0]);     if($result1[0] ne ""){       #`kill -9 '$result1[0]'`;       print $result1[0]." ".$result1[1]." ".$result1[2]."\n";       push(@kill_Queue,$result1[0]);       }     }    $killQueueLen=@kill_Queue;    print "###################################################################\n";    print "We will kill '$killQueueLen' processes!!\n";    print "Are you sure about kill the processes above?[y/n]";    $yesorno=;    chomp($yesorno);    if($yesorno eq "Y" or $yesorno eq "y")     {    print "###################################################################\n";        foreach $kill_Queue (@kill_Queue)        {         print $kill_Queue;         chomp($kill_Queue);         if($kill_Queue ne "")         {          `kill -9 '$kill_Queue'`;         }        }     }    elsif($yesorno eq "N" or $yesorno eq "n")     {        @kill_Queue=();        $arg2="";        next;     }    else     {     print "###################################################################\n";     print "JUEST  Y   or   N!!!!\n";     print "###################################################################\n";     next;   }     print "OK\n";     exit;    }    elsif($confirm eq "N" or $confirm eq "n")    {    exit 0;    }    else    {      print "Please input [y/n]:";      next;    }}else{   if($arg2 =~ /^[+-]?\d+$/)   {    @result=`ps -eo lstart,pid,args | grep $arg1 | grep -v grep`;    my @kill_Queue="";    print "killed list\n";    print "###################################################################\n";    foreach $result ( @result)     {     if($result ne "")      {      @result1 =split(/\s+/,$result);      $time_start=$result1[1]." ".$result1[2]." ".$result1[3]." ".$result1[4];      $format_time=`date -d '$time_start' '+%Y/%m/%d %T'`;      chomp($format_time);      $pro_st_time=`date +%s -d '$format_time'`;      $a1=`date`;      chomp($a1);      chomp($pro_st_time);      chomp($kill_time);      $cur_time=`date +%s -d '$a1'`;       $kill_time=$cur_time-$arg2;      if($pro_st_time > $kill_time)      {         print $result1[5]." ".$result1[6]." ".$result1[7]."\n";         push(@kill_Queue,$result1[5]);      }    }    else    {    next;    }}    $killQueueLen=@kill_Queue-1;    print "###################################################################\n";    print "We will kill '$killQueueLen' processes!!\n";    print "Are you sure about kill the processes above?[y/n]";    $yesorno=;    chomp($yesorno);    if($yesorno eq "Y" or $yesorno eq "y")     {        foreach $kill_Queue (@kill_Queue)        {         chomp($kill_Queue);         if($kill_Queue ne "")         {          `kill -9 '$kill_Queue'`;         }        }     }    elsif($yesorno eq "N" or $yesorno eq "n")     {        $arg2="";        next;     }   }}print "retry";}print "End of the script\n";print "================================================================\n";

脚本的基本功能就是，可以数据要过滤的进程例如"LOCAL=NO"，然后获得所有匹配进程的开始时间和进程内容。然后，需要数据要kill的进程是在当前时间点之前多少秒开始的进程。如果输入null的话。就是kill掉所有匹配的进程。如果输入1000的话，就是kill掉所有在过去1000秒钟开始的标记为"LOCAL=NO"的所有的进程。

执行完过程之后，就应该可以连接到数据库中。调整process参数。保证系统正常运行，然后再查询导致此错误的原因。

这样的话，我们就可以尽可能的减少对系统的影响。

当前脚本知识在linux上测试过，没有在其他类unix系统测试。