#!/usr/bin/perl
$ENV{'ORACLE_HOME'}='/oracle/product/10.2.0.1';
$ENV{'PATH'}="/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/bin:$ENV{'ORACLE_HOME'}/bin";
$ENV{'TERM'}='vt100';
our $warehouse='ct_warehouse/ct_warehouse@192.168.1.14/XXXdb.center.XXX.com.cn';
our %station= (
'SMSG_LOGS' => {
       '192.168.64.jiangsu' => ['ct_jiangsu/xxx@192.168.64.13/XXXdb',3,'JIANGSU'],
       '192.168.64.shandong' => ['ct_shandong/xxx@192.168.64.13/XXXdb',3,'SHANDONG'],
        '192.168.64.hubei' => ['ct_hubei/xxx@192.168.64.13/XXXdb',3,'HUBEI'],
        '192.168.64.shan3xi' => ['ct_shan3xi/xxx@192.168.64.13/XXXdb',3,'SHAN3XI'],
        '192.168.64.shanghai' => ['ct_shanghai/xxx@192.168.64.13/XXXdb',3,'SHANGHAI'],
'192.168.52.beijing' => ['ct_beijing/xxx@192.168.52.13/XXXdb',3,'BEIJING'],
        '192.168.52.TIANJIN' => ['ct_tianjin/xxx@192.168.52.13/XXXdb',3,'tianjin'],
        '192.168.52.liaoning' => ['ct_liaoning/xxx@192.168.52.13/XXXdb',3,'LIAONING'],
        '192.168.52.henan' => ['ct_henan/xxx@192.168.52.13/XXXdb',3,'HENAN'],
        '192.168.52.hebei' => ['ct_hebei/xxx@192.168.52.13/XXXdb',3,'HEBEI'],
        '192.168.52.sichuan' => ['ct_sichuan/xxx@192.168.52.13/XXXdb',3,'SICHUAN'],
       '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
       '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.13/XXXdb',3,'GUANGZHOU'],
       '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
       '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.13/XXXdb',3,'FUZHOU'],
        '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
        '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.13/XXXdb',3,'HAINAN'],
        '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
        '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.13/XXXdb',3,'JIANGXI'],
       '192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
       '192.168.55.yunnan' => ['ct_yunnan/xxx@192.168.55.13/XXXdb',3,'YUNNAM'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
# '192.168.55.guangzhou' => ['ct_guangzhou/xxx@192.168.55.14/XXXdb',3,'GUANGZHOU'],
#       '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
#       '192.168.55.fuzhou' => ['ct_fuzhou/xxx@192.168.55.14/XXXdb',3,'FUZHOU'],
#        '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
#        '192.168.55.hainan' => ['ct_hainan/xxx@192.168.55.14/XXXdb',3,'HAINAN'],
#        '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
#        '192.168.55.jiangxi' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'JIANGXI'],
#   '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
#   '192.168.55.yunnan' => ['ct_jiangxi/xxx@192.168.55.14/XXXdb',3,'YUNNAM'],
        }
);
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/ct_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:<\/b><\/font>
\n";
    foreach $ip (sort keys %{$station{$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;
@ARGV=("/home/oracle/admin/dailycheck/ct_delete_partition$date_id.log");
$^I =".bak";
while (<>)
{
s/^([A-Z]*\n)/$1<\/b><\/font>
\n/;
s/P\d+/$&<\/b><\/font>/g;
s/\n/
\n/;
print ;
}
`cat /home/oracle/admin/dailycheck/ct_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:'µçÐÅ¸÷¹¤×÷Õ¾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 $ip,$connstr,$table,$days,$station_id,"\n\n";

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++)
{
#É¾³ýÏÂÃæÒ»ÐÐ
#print $partitionlist[$i],"\n";

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";}

####################end

}
return;
}

ITPub博客

ct_deletepartition.pl 删除电信分区

最新文章