#!/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;
$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;
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`;
{
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";
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";
{
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
";}
{
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";}
else
{print LOGFILE "The record is not equal PLZ check*****\n";}
}
return;
}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-754618/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-754618/