delete_partition.pl 刪除分割槽

perfychi發表於2013-02-23
delete
#!/usr/bin/perl
$ENV{'ORACLE_HOME'}='/oracle/product/10.2.0';
$ENV{'PATH'}="/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/bin:$ENV{'ORACLE_HOME'}/bin";
$ENV{'TERM'}='vt100';
our $warehouse='warehouse/warehouse@192.168.1.18/XXXdw.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
        '192.168.2.13' => ['product/xxx@192.168.2.13/XXXdb',2,'BEIJING'],
#       '192.168.2.14' => ['product/xxx@192.168.2.14/XXXdb',2,'BEIJING'],
       '192.168.3.13' => ['product/xxx@192.168.3.13/XXXdb',2,'HANGZHOU'],
#        '192.168.3.14' => ['product/xxx@192.168.3.14/XXXdb',2,'HANGZHOU'],
        i'192.168.4.13' => ['product/xxx@192.168.4.13/XXXdb',2,'FUZHOU'],
#       '192.168.4.14' => ['product/xxx@192.168.4.14/XXXdb',2,'FUZHOU'],
        '192.168.6.13' => ['product/xxx@192.168.6.13/XXXdb',2,'GUANGXI'],
#        '192.168.6.14' => ['product/xxx@192.168.6.14/XXXdb',2,'GUANGXI'],
        '192.168.7.13' => ['product/xxx@192.168.7.13/XXXdb',2,'SICHUAN'],
#        '192.168.7.14' => ['product/xxx@192.168.7.14/XXXdb',2,'SICHUAN'],
        '192.168.8.13' => ['product/xxx@192.168.8.13/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
#        '192.168.8.14' => ['product/xxx@192.168.8.14/XXXdb.hebei.XXX.com.cn',2,'HEBEI'],
       '192.168.9.13' => ['product/xxx@192.168.9.13/XXXdb',2,'SHAN1XI'],
#        '192.168.9.14' => ['product/xxx@192.168.9.14/XXXdb',2,'SHAN1XI'],
        '192.168.10.13' => ['product/xxx@192.168.10.13/XXXdb',3,'LIAONING'],
#        '192.168.10.14' => ['product/xxx@192.168.10.14/XXXdb',2,'LIAONING'],
         '192.168.11.13' => ['product/xxx@192.168.11.13/XXXdb',5,'HENAN'],
#        '192.168.11.14' => ['product/xxx@192.168.11.14/XXXdb',2,'HENAN'],
  '192.168.12.13' => ['product/xxx@192.168.12.13/XXXdb',2,'SHAN3XI'],
#        '192.168.12.14' => ['product/xxx@192.168.12.14/XXXdb',2,'SHAN3XI'],
#        '192.168.13.13' => ['product/xxx@192.168.13.13/XXXdb',2,'JILIN'],
        '192.168.13.14' => ['product/xxx@192.168.13.14/XXXdb',3,'JILIN'],
        '192.168.14.13' => ['product/xxx@192.168.14.13/XXXdb',2,'SHANDONG'],
#        '192.168.14.14' => ['product/xxx@192.168.14.14/XXXdb',2,'SHANDONG'],
        '192.168.15.13' => ['product/xxx@192.168.15.13/XXXdb.jiangxi.XXX.com.cn ',4,'JIANGXI'],
#        '192.168.15.14' => ['product/xxx@192.168.15.14/XXXdb.jiangxi.XXX.com.cn ',2,'JIANGXI'],
#        '192.168.16.13' => ['product/xxx@192.168.16.13/XXXdb',2,'JIANGSU'],
        '192.168.16.14' => ['product/xxx@192.168.16.14/XXXdb',2,'JIANGSU'],
        '192.168.17.13' => ['product/xxx@192.168.17.13/XXXdb.shanghai.XXX.com.cn ',4,'SHANGHAI'],
#       '192.168.17.14' => ['product/xxx@192.168.17.14/XXXdb.shanghai.XXX.com.cn ',3,'SHANGHAI'],
  '192.168.18.13' => ['product/xxx@192.168.18.13/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],  
#       '192.168.18.14' => ['product/xxx@192.168.18.14/XXXdb.yunnan.XXX.com.cn',2,'YUNNAN'],
        '192.168.19.13' =>  ['product/xxx@192.168.19.13/XXXdb',2,'NEIMENGGU'],
#        '192.168.19.14' => ['product/xxx@192.168.19.14/XXXdb',2,'NEIMENGGU'],
        '192.168.20.13' =>  ['product/xxx@192.168.20.13/XXXdb',2,'HUBEI'],
#        '192.168.20.14' => ['product/xxx@192.168.20.14/XXXdb',2,'HUBEI'],
        '192.168.21.13' =>  ['product/xxx@192.168.21.13/XXXdb',2,'ANHUI'],
#        '192.168.21.14' => ['product/xxx@192.168.21.14/XXXdb',3,'ANHUI'],
       '192.168.22.13' =>  ['product/xxx@192.168.22.13/XXXdb',3,'XINJIANG'],
#        '192.168.22.14' => ['product/xxx@192.168.22.14/XXXdb',3,'XINJIANG'],
        '192.168.23.13' =>  ['product/xxx@192.168.23.13/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
#        '192.168.23.14' => ['product/xxx@192.168.23.14/XXXdb.chongqing.XXX.com.cn',3,'CHONGQING'],
        '192.168.24.ningxia' =>  ['cm_ningxia/xxx@192.168.24.13/XXXdb',3,'NINGXIA'],           
#        '192.168.24.ningxia' =>  ['cm_ningxia/xxx@192.168.24.14/XXXdb',3,'NINGXIA'],
        '192.168.24.hainan' =>  ['cm_hainan/xxx@192.168.24.13/XXXdb',4,'HAINAN'],
#        '192.168.24.hainan' =>  ['cm_hainan/xxx@192.168.24.14/XXXdb',4,'HAINAN'],
        '192.168.24.xizang' =>  ['cm_xizang/xxx@192.168.24.13/XXXdb',3,'XIZANG'],
#        '192.168.24.xizang' =>  ['cm_xizang/xxx@192.168.24.14/XXXdb',3,'XIZANG'],
        '192.168.26.13' =>  ['product/xxx@192.168.26.13/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
#        '192.168.26.14' =>  ['product/xxx@192.168.26.14/XXXdb.heilongjiang.XXX.com.cn',3,'HEILONGJIANG'],
        '192.168.27.13' =>  ['product/xxx@192.168.27.13/XXXdb',3,'GUIZHOU'],
#        '192.168.27.14' =>  ['product/xxx@192.168.27.14/XXXdb',4,'GUIZHOU'],
        '192.168.28.13' => ['product/xxx@192.168.28.13/XXXdb',14,'TIANJIN'],
#        '192.168.28.14' => ['product/xxx@192.168.28.14/XXXdb',14,'TIANJIN'],
        '192.168.29.13' => ['product/xxx@192.168.29.13/XXXdb',14,'HUNAN'],
#        '192.168.29.14' => ['product/xxx@192.168.29.14/XXXdb',14,'HUNAN'],
        }
);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
my $date_id=sprintf "%d%02d%02d",$year+1900,$mon+1,$mday;
open(LOGFILE, ">/home/oracle/admin/dailycheck/delete_partition$date_id.log");
printf LOGFILE "this script. start at %04d-%02d-%02d %02d:%02d:%02d\n\n",$year+1900,$mon+1,$mday,$hour,$min,$sec;
foreach $table (keys(%station))
{
    printf LOGFILE "drop  table $table partition:
\n";
    foreach $ip (sort keys %{$station_id{$table}} )
    {
        $connstr=$station{$table}{$ip}[0];
        $days=$station{$table}{$ip}[1];
        $station_id=$station{$table}{$ip}[2];
       delte_partition($ip,$connstr,$table,$days,$station_id);
    }
}
close LOGFILE;
`/home/oracle/admin/dailycheck/delete_partition_guangzhou.pl`;

@ARGV=("/home/oracle/admin/dailycheck/delete_partition$date_id.log");
$^I =".bak";
while (<>)
{
s/^([A-Z]*\n)/$1
\n/;
s/P\d+/$&/g;
s/\n/
\n/;
print ;
}
`cat /home/oracle/admin/dailycheck/delete_partition$date_id.log| formail -I "From:oracle\@warehousep" -I "MIME-Version:1.0" -I "Content-type:text/html;charset=gb2312" -I "Content-Language:zh-cn" -I 'Subject:'¸÷¹¤×÷Õ¾PROCESSING_LOGSºÍSMSG_LOGS±í·ÖÇøɾ³ýÈÕÖÖ¾| /usr/sbin/sendmail -oi oracle_ops\@7500.com.cn`;
#---------------delete__partition.sh-----------------------------------#
#--    delte station($ip)  partition  before $days days               -#
#--   the default partition peridor is week.if you want change the    -#
#--     default period plz change the SQL                             -#
#--  author: zhaoxin                                                  -#
#--                                                                   -#
#----------------------------------------------------------------------#
sub delte_partition {
if(@_!=5){die "THE PARAMETER NOT EQULE 5  ";}
my ($ip,$connstr,$table,$days,$station_id)=@_;
my $sql;
print LOGFILE "$station_id\n";
my @partitionlist=`sqlplus -s /nolog <connect $connstr
set line 200
set feedback off
set pagesize 10000
set head off
SELECT partition_name
  FROM user_tab_partitions
 WHERE UPPER (table_name) LIKE UPPER ('$table')
   AND SYSDATE - TO_DATE (SUBSTR (partition_name, 2, 9), 'yyyymmdd') > $days
and length(partition_name)=9;
exit
EOF
`;
chomp(@partitionlist);
if (@partitionlist<2)
{
 print logfile "$ip: there is no partition need to drop before $days days now \n";
return;
};
# print LOGFILE "can drop partition is :@partitionlist\n";
for ($i=1;$i<=$#partitionlist;$i++)
{
my @l_record_cnt=`sqlplus -s /nolog <connect $connstr
set line 200
set feedback off
set head off
select count(*)  from  $table partition($partitionlist[$i]);
exit
EOF
`;
chomp @l_record_cnt;
$l_record_cnt[1]=~s/\s//g;
my $table_arch=$table.'_arch';
my @g_record_cnt=`sqlplus -s /nolog <connect $warehouse
set line 200
set feedback off
set head off
SELECT COUNT (*)
  FROM $table_arch  subpartition ($partitionlist[$i]_$station_id);
exit
EOF
`;
chomp @g_record_cnt;
$g_record_cnt[1]=~s/\s//g;
print LOGFILE "partition  $partitionlist[$i] record on station is:$l_record_cnt[1]\t";
print LOGFILE " on warehouse is:$g_record_cnt[1]\n";
if ($g_record_cnt[1]==$l_record_cnt[1] && $g_record_cnt[1]=~/^\d/)
{
my $feedback=`sqlplus -s /nolog <connect $connstr
alter table $table drop partition $partitionlist[$i];
exit
EOF
`;
        if($feedback=~m/Table altered/g)
                {print LOGFILE "drop successful\n "; }
        else
                {print LOGFILE "$feedback
";}
        }
else
        {print  LOGFILE "The record is not equal PLZ check*****\n";}

}
return;
}
 

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

相關文章