#!/usr/bin/perl
use strict;
use Time::localtime;
my @conf_mandatory =('SQLPLUS_CMD','SQL_FILES','SCRIPT_LOG_PATH','SCRIPT_LOG_RETENTION_POLICY');
if ( $#ARGV < 0) {
&print_usage;
exit(1);
}
my $conf = &get_conf($ARGV[0]);
&rotate($conf->{SCRIPT_LOG_PATH}, $conf->{SCRIPT_LOG_RETENTION_POLICY});
open (OUT, "> $conf->{SCRIPT_LOG_PATH}")
or print("Failed to create file: $conf->{SCRIPT_LOG_PATH}.") and exit(1);
&write_log('INFO', "$0 started.");
&exec_sqls;
&write_log('INFO', "$0 finished.");
close (OUT);
exit(0);
sub print_usage {
print "$0: missing argument\n";
print "Usage : perl $0 configuration_file\n";
print "Example: perl $0 sample.conf\n";
}
sub get_conf {
my $conf;
my $filename = $_[0];
my $str = &read_file($filename);
eval ('$conf = { dummy=>1,' . $str . '};1;')
or print("Error in conf file. $@.") and exit(1);
foreach my $item (@conf_mandatory) {
if (!exists($conf->{$item})) {
print("$item does not exist in $filename.") and exit(1);
} elsif ($conf->{$item} eq '') {
print("$item is mandatory in $filename.") and exit(1);
}
}
return $conf;
}
sub rotate {
my($filename,$max) = @_;
my $cur;
return if ($max < 0);
if (-f "$filename.$max") {
unlink ("$filename.$max")
or &error_exit("Failed to delete file: $filename.$max.");
}
for ($cur = $max; $cur > 1; $cur--) {
my $prev = $cur - 1;
if (-f "$filename.$prev") {
rename("$filename.$prev","$filename.$cur")
or &error_exit("Failed to rotate files: $filename.$prev --> $filename.$cur.");
}
}
if (-f "$filename") {
rename("$filename","$filename.1")
or &error_exit("Failed to rotate files: $filename --> $filename.1.");
}
}
sub read_file {
my $filename = $_[0];
my $lines = '';
if (-f $filename) {
open (IN, "< $filename") or &error_exit("Failed to open file: $filename.");
while (my $line = <IN>) { $lines .= $line; }
close (IN);
} else {
&error_exit("File not found: $filename.");
}
return $lines;
}
sub exec_sqls {
my $sqlplus_cmd = $conf->{SQLPLUS_CMD};
my @sql_files = @{$conf->{SQL_FILES}};
foreach my $sql_file (@sql_files) {
&error_exit("File not found: $sql_file.") if (! -f $sql_file);
my $cmd = $sqlplus_cmd . ' @' . $sql_file;
my @stdout = `$cmd`;
if ($?== 0) {
foreach my $line (@stdout) { chomp($line); &write_log('INFO', $line); }
&write_log('INFO', "Sql script successfully completed: $sql_file.");
} else {
foreach my $line (@stdout) { chomp($line); &write_log('ERROR', $line); }
&error_exit("Failed to execute sql script: $sql_file.");
}
}
}
sub write_log {
my $tm = localtime;
my $dt = sprintf("%04d-%02d-%02d %02d:%02d:%02d",
$tm->year+1900, $tm->mon+1, $tm->mday, $tm->hour, $tm->min, $tm->sec);
print OUT "[$dt] [$_[0]] $_[1]\n";
}
sub error_exit {
write_log('ERROR', $_[0]) if ($#_ >= 0);
exit(1);
}
SQLPLUS_CMD => 'sqlplus / as sysdba',
SQL_FILES => ['/usr/local/mis/bin/exec_sql/gather_stats.sql'],
SCRIPT_LOG_PATH => '/usr/local/mis/bin/exec_sql/log/gather_stats.log',
SCRIPT_LOG_RETENTION_POLICY => 7,
if [ "$1" != 'exec' ]; then
echo "usage: $0 exec"
exit 1
fi
BASE_DIR=$(cd $(dirname $0);pwd)
BASE_NAME=$(basename $0)
STEM_NAME=${BASE_NAME%%.*}
cd ${BASE_DIR}
source ../env.conf || { echo "Failed to read env.conf" ; exit 1 ; }
source ../common.conf || { echo "Failed to read common.conf" ; exit 1 ; }
perl exec_sql.pl gather_stats.conf
if [ $? -eq 0 ]
then
print_msg 'INFO' 'Collect statistics successfully.'
else
print_msg 'ERR' 'Failed to collect statistics.'
exit 1
fi
exit 0
WHENEVER SQLERROR EXIT 1
DECLARE
TYPE SCHEMA_ARRAY IS TABLE OF VARCHAR2(40);
SCHEMAS SCHEMA_ARRAY := SCHEMA_ARRAY('SCOTT','SMITH');
BEGIN
FOR I IN 1..SCHEMAS.COUNT LOOP
dbms_output.put_line(SCHEMAS(I));
DBMS_STATS.GATHER_SCHEMA_STATS (
OWNNAME => SCHEMAS(I)
,OPTIONS => 'GATHER AUTO'
,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
);
END LOOP;
END;
/
ALTER SYSTEM FLUSH SHARED_POOL;
EXIT 0
export ORACLE_SID=orcl
export LANG=C
export NLS_LANG=American_America.JA16SJISTILDE
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=.:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
function print_msg() {
ERROR_LEVEL=$1
MSG=$2
LOG_DATE=`date '+%Y-%m-%d'`
LOG_TIME=`date '+%H:%M:%S'`
printf "[%-10s %-8s] [%s] %-50s\n" "${LOG_DATE}" "${LOG_TIME}" "${ERROR_LEVEL}" "${MSG}"
}
function delete_expired_files() {
DIR=$1
PREFIX=$2
EXPIRE_DATE=`date -d "$3 days" '+%Y%m%d'`
RTN_CD=0
FILES=(`find "${DIR}" -maxdepth 1 -name "${PREFIX}*"`) || { echo 1; exit; }
for FILE in "${FILES[@]}"
do
FILE_DATE=`basename ${FILE}|perl -ne 'if(($y,$m,$d)=$_=~/^[a-zA-Z0-9\-\_]+([0-9]{4})-([0-9]{2})-([0-9]{2})-[0-9]{6}\.[a-zA-Z0-9\.]+$/){print "$y$m$d"}else{print 0}'`
if [[ "${DIR}" && "$FILE_DATE" -ne 0 && "$FILE_DATE" -lt "$EXPIRE_DATE" ]]; then
rm -f "${FILE}" || RTN_CD=1
fi
done
echo ${RTN_CD}
}
