========================
Files List
========================
main.bat
main_from_perl.bat
report_main.bat
run_sql.bat
db_list.ini
format_db_report.pl
report_main.pl
tablespace_sql.sql
========================
Code
========================
main.bat
ECHO OFF
setlocal
cls
SET inifile=db_list.ini
SET REFRESH_LOG=report.log
SET TEMP_REFRESH_LOG=temp_report.log
ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO handling %%i
call run_sql.bat %TEMP_REFRESH_LOG% %%i
TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
main_from_perl.bat
ECHO OFF
SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt
ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%REFRESH_LOG%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO handling %%i
ECHO. 2>%TEMP_REFRESH_LOG%
call run_sql.bat %TEMP_REFRESH_LOG% %%i
TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
report_main.bat
perl report_main.pl
run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO tablespace_sql %db_name%
sqlplus -l -s %db_name% @tablespace_sql %spool_to_file% %db_name%
db_list.ini
user_a/pass_a@conn_str_a
user_b/pass_b@conn_str_b
user_c/pass_c@conn_str_c
user_d/pass_d@conn_str_d
user_e/pass_e@conn_str_e
user_f/pass_f@conn_str_f
user_g/pass_g@conn_str_g
user_h/pass_h@conn_str_h
report_main.pl
#! /usr/bin/perl
#Main perl program.
#called from report_main.bat
#calling BAT_CMD from report.ini
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='report.ini';
my $LOG_FILE_BASE_NAME='Report_Log';
my $display_delimiter="========================================\n";
my %hashTable;
my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';
my $HEADER_FILE='';
my $FOOTER_FILE='';
my $FORMAT_STYLE='';
my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';
my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $headerFile='';
my $footerFile='';
my $lastReportFile;
my $run_date='';
my $result='';
my $runDate='';
#==================================
# subs
#==================================
sub setRundate{
my $dateTime=DateTime->now;
my $month=$dateTime->month;
my $year=$dateTime->year;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
my $hh=$dateTime->hour;
if ($hh < 10){ $hh="0".$hh ; }
my $mm=$dateTime->minute;
if ($mm < 10){ $mm="0".$mm ; }
my $ss=$dateTime->second;
if ($ss < 10){ $ss="0".$ss ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub getRunDate{
my $run_date='';
my $dateTime=DateTime->now;
my $year=$dateTime->year;
my $month=$dateTime->month;
my $day=$dateTime->day;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
$run_date=$year."-".$month."-".$day;
$run_date;
}
sub fileFullPathNames{
#$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
$headerFile=$MAIN_HOME_WIN."\\".$HEADER_FILE;
$footerFile=$MAIN_HOME_WIN."\\".$FOOTER_FILE;
}
sub setLogFileName{
$logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
my $status=0;
#print "Will try to create now new folder: ".$LOG_FILE_PATH;
if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
}
if ($status == 0 ){
print "\n";
print $display_delimiter;
print "\n";
print "Log File: ".$logFile."\n";
print "\n";
print $display_delimiter;
print "\n";
}
$status;
}
sub readIniFile{
my $i_ini_file=shift;
my $line='';
my $key='';
my $value='';
my $match_str = "CUSTOMER_[0-9][0-9]";
#read from ini file
open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
my $count=0;
foreach $line (<FILE_HANDLER>){
chomp($line);
if ($line =~ /^$/) {
#nothing. Ignore empty lines
}elsif($line =~ /^#/) {
#nothing. Ignore # comment lines lines
}else{
($key, $value)=split(/=/,$line);
$hashTable{$key} = $value;
}
}
close (FILE_HANDLER);
}
sub sendMail{
my @mailArg = "";
my $sendMailInd=0;
print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
@mailArg=($mailCmd,$reportFile);
system(@mailArg);
}
sub printMultipath{
my $str = shift;
my $log_handler = shift;
print $log_handler ($str);
print ($str);
}
sub setGeneralParams{
#from ini file
$MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"};
$LOG_FILE_PATH = $hashTable{"LOG_FILE_PATH"};
$REPORT_FILE_PATH = $hashTable{"REPORT_FILE_PATH"};
$MAIL_PATH = $hashTable{"MAIL_PATH"};
$INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
$BAT_CMD = $hashTable{"BAT_CMD"};
$DB_LOG=$hashTable{"DB_LOG"};
$REPORT_FILE=$hashTable{"REPORT_FILE"};
$MAIL_CMD = $hashTable{"MAIL_CMD"};
$MAIL_HEADER = $hashTable{"MAIL_HEADER"};
$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
$LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
$HEADER_FILE = $hashTable{"HEADER_FILE"};
$FOOTER_FILE = $hashTable{"FOOTER_FILE"};
$FORMAT_STYLE = $hashTable{"FORMAT_STYLE"};
#concatinations
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;
#report
print $display_delimiter;
print "Running With General Parameters:"."\n";
print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
print "BAT_CMD: ".$BAT_CMD."\n";
print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
#print "MAIL_CMD: ".$MAIL_CMD."\n";
print "DB_LOG: ".$DB_LOG."\n";
print "Report: ".$REPORT_FILE."\n";
print $display_delimiter;
}
sub runOsCommand{
my $log_handler = shift;
my $cmd = shift;
my $ini_file = shift;
print "Running Command: ".$cmd."\n";
#$result=system($^X,$cmd);
my @SendArg=($cmd,$ini_file, $dbLogFile);
$result=system(@SendArg);
#print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
$result;
}
sub updateLastDbReport{
my $lastReportFile=shift;
my $reportFile=shift;
open OUT_FILE,">",$lastReportFile or die $!;
print OUT_FILE $reportFile."\n";
close OUT_FILE or die $!;
}
sub formatReportFile{
my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $formatStyle = shift;
my $header=shift;
my $footer=shift;
my $runDate=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $formatStyle, $header,$footer,$runDate);
$result;
}
sub reportGeneralParameters{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Input Parameters: \n",$log_handler);
printMultipath ("Run Date:".$run_date."\n",$log_handler);
printMultipath ("Log File: ".$logFile."\n",$log_handler);
printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);
printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);
printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportStartOfProcess{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportEndOfProcess{
my $log_handler = shift;
my $status = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
if ($status == 0){
printMultipath ("Process has Finished Successfuly"."\n",$log_handler);
}else{
printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
}
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
$status;
}
sub handleStatus{
my $status = shift;
my $method = shift;
my $log_handler = shift;
if ($status != 0){
if (defined $log_handler){
printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
}else{
print ("Error in Routine: ".$method." Status: ".$status."\n");
}
return $status;
}
$status;
}
#==================================
# The code starts here
#==================================
$result=main();
exit $result;
#==================================
# sub main
#==================================
sub main{
no warnings 'uninitialized';
setRundate();
$runDate=getRunDate();
readIniFile("$INI_FILE");
setGeneralParams();
$result=setLogFileName();
handleStatus($result, "setLogFileName",undef);
open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile);
fileFullPathNames();
reportStartOfProcess($LOG_HANDLER);
reportGeneralParameters($LOG_HANDLER);
my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
$result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
handleStatus($result, $cmd,undef);
#$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
if ( $FORMAT_STYLE eq "text" ){
$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$FORMAT_STYLE,"NULL","NULL",$runDate);
}elsif($FORMAT_STYLE eq "html" ){
$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$FORMAT_STYLE,$headerFile,$footerFile,$runDate);
}else{
print "Unknown format value in formatStyle: ".$FORMAT_STYLE."\n";
}
handleStatus($result, "formatReportFile",undef);
updateLastDbReport($lastReportFile,$reportFile);
reportEndOfProcess($LOG_HANDLER, $result);
close $LOG_HANDLER;
#sendMail(); - mail is send from a server with access to mail server
}
format_db_report.pl
#====================================================
# Declaration
#====================================================
my $pl_script_name='';
my $sourceFile='';
my $targetFile='';
my $formatStyle='';
my $headerFile = '';
my $footerFile = '';
my $runDate = '';
my $status='0';
my $header_dbName='Database Name';
my $header_tablespace='Tablespace';
my $header_freeSize='Free Size Mb';
my $header_usedSize='Used Size Mb';
my $header_totalSize='Max Size Mb';
my $header_freePct='Free %';
my $header_needMoreSpace='Add More Space';
my $header_underline="--------------------------------------------------";
#====================================================
# Sub routines Start Here
#====================================================
sub reportInputParams{
print "Input Parameters:"."\n";
print "sourceFile = ".$sourceFile."\n";
print "targetFile = ".$targetFile."\n";
print "formatStyle = ".$formatStyle."\n";
print "headerFile = ".$headerFile."\n";
print "footerFile = ".$footerFile."\n";
print "runDate = ".$runDate."\n";
}
sub formatStyleText{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $runDate = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $limitForAlert=5;
#=================================
#Add Header
#=================================
open OUT_FILE,">",$targetFile or die $!;
close OUT_FILE or die $!;
if ($headerFile ne "NULL"){
open IN_FILE,$headerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
#=================================
#Add Contents
#=================================
open IN_FILE,$sourceFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_dbName,0,50), (substr $header_tablespace,0,30),(substr $header_freeSize,0,12), (substr $header_usedSize,0,12), (substr $header_totalSize,0,12),(substr $header_freePct,0,8), (substr $header_needMoreSpace,0,14));
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_underline,0,50),(substr $header_underline,0,30),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,8),(substr $header_underline,0,14));
while ($i_line = <IN_FILE>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y"){
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $db_name,0,50),(substr $tablespace,0,30),(substr $freeSize,0,12),(substr $usedSize,0,12),(substr $totalSize,0,12),(substr $freePct,0,8),(substr $needMoreSpace,0,14));
}
}
}
close IN_FILE or die $!;
close OUT_FILE or die $!;
#=================================
#Add Footer
#=================================
if ($footerFile ne "NULL"){
open IN_FILE,$footerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
$status;
}
sub formatStyleHtml{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $colorGreen='#00FF00';
my $colorYellow='#FFFF66';
my $colorRed='#00FF00';
my $bgColor='';
my $htmlLine='';
#=================================
#Add Header
#=================================
open OUT_FILE,">",$targetFile or die $!;
close OUT_FILE or die $!;
if ($headerFile ne "NULL"){
open IN_FILE,$headerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
$i_line =~ s/YYYY-MM-DD/$runDate/;
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
#=================================
#Add Contents
#=================================
open IN_FILE,$sourceFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
#print OUT_FILE $header."\n";
#print OUT_FILE $hliner."\n";
while ($i_line = <IN_FILE>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y" ){
$line_counter+=1;
if ($line_counter%2 ==0){
$bgColor=$colorGreen;
}else{
$bgColor=$colorYellow;
}
if ($freePct <= $limitForAlert){
$bgColor=$colorRed;
}
$htmlLine='';
$htmlLine='<TR BGCOLOR="'.$bgColor.'">';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$db_name.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$tablespace.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freeSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$usedSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$totalSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freePct.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$needMoreSpace.'</TD>';
$htmlLine=$htmlLine.'</TR>';
print OUT_FILE $htmlLine;
}
#$line_counter=0;
}
}
close IN_FILE or die $!;
close OUT_FILE or die $!;
#=================================
#Add Footer
#=================================
if ($footerFile ne "NULL"){
open IN_FILE,$footerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
$status;
}
sub readAndWrite{
my $sourceFile = shift;
my $targetFile = shift;
my $formatStyle = shift;
my $headerFile = shift;
my $footerFile = shift;
$status='0';
print "old name".$targetFile."\n";
$targetFile = substr($targetFile,0,index($targetFile,".rep")).".".$formatStyle;
print "new name".$targetFile."\n";
if ($formatStyle eq "text"){
$status=formatStyleText($sourceFile,$targetFile,$headerFile,$footerFile);
}
if ($formatStyle eq "html"){
$status=formatStyleHtml($sourceFile,$targetFile,$headerFile,$footerFile);
}
}
#====================================================
# Code Start Here
#====================================================
$pl_script_name="format_db_report.pl";
$sourceFile = shift;
$targetFile = shift;
$formatStyle = shift;
$headerFile = shift;
$footerFile = shift;
$runDate = shift;
#print "Inside ".$pl_script_name."\n";
reportInputParams();
$status=readAndWrite($sourceFile, $targetFile, $formatStyle, $headerFile, $footerFile);
exit $status;
#====================================================
# Code End Here
#====================================================
tablespace_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS GATE_NAME,
TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND(SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END/1024/1024)) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
SPOOL OFF
EXIT;
/
No comments:
Post a Comment