General
===========================================
Code Example for bash, perl, sqlplus
Have a crontab task running every 5 minutes.
crontab task is executing a main bash script.
main bash script. is calling another bash script.
bash script is preparing a list of top memory consuming processes.
main bash script is calling perl code.
perl script is calling the database to get Database SYSDATE and SNAP_ID.
perl script is preparing a INSERT statements sql file.
main bash script is calling another bash script that is running the sql file.
In main bash script there is a logic, to keep log files under control.
===========================================
File list
===========================================
crontab
.set_env
.set_profile
main_get_process_data.sh
list_processes.shload_to_db.pl
load_data_to_db.sh
===========================================
crontab
===========================================
0-59/5 * * * * /software/oracle/oracle/scripts/main_get_process_data.sh
.set_env
===========================================
export LD_LIBRARY_PATH=/software/oracle/101/lib:/usr/lib
export PATH=/usr/kerberos/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/local/cmcluster/bin:/usr/sbin:/sbin:/software/oracle/101/bin
===========================================
.set_profile
===========================================
export ORACLE_SID=orainst
export ORACLE_BASE=/software/oracle
export ORACLE_HOME=/software/oracle/101
export PATH=$PATH:/software/oracle/101/bin
===========================================
main_get_process_data.sh
===========================================
#!/bin/bash
export WORKING_DIR=/software/oracle/oracle/scripts
export OUTPUT_FILE=$WORKING_DIR/os_processes.txt
export OUTPUT_SQL_FILE=$WORKING_DIR/sh_hist_os_processes.sql
export LOG_FILE=$WORKING_DIR/main.log
export LOG_FILE_PERL=$WORKING_DIR/load_data.log
export DB_SCHEMA=dba_user/dba_pass@DWHP_NEW
. $WORKING_DIR/.export_all_env
. $WORKING_DIR/.set_profile
#export SYSDATE_SQL=$WORKING_DIR/sh_hist_os_sysdate.sql
#export SYSDATE_FILE=$WORKING_DIR/sh_hist_os_sysdate.lst
#$WORKING_DIR/call_sql_main.sh $SYSDATE_SQL
#export SNAP_ID_SQL=$WORKING_DIR/sh_hist_os_snap_id.sql
#export SNAP_ID_FILE=$WORKING_DIR/sh_hist_os_snap_id.lst
#$WORKING_DIR/call_sql_main.sh $SNAP_ID_SQL
rm -f $OUTPUT_FILE
touch $OUTPUT_FILE
rm -f $OUTPUT_SQL_FILE
touch $OUTPUT_SQL_FILE
touch $LOG_FILE
touch $LOG_FILE_PERL
echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
echo Starting Running perl script | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
$WORKING_DIR/list_processes.sh $OUTPUT_FILE
echo ============================================ | tee >> $LOG_FILE
echo Before Running perl script | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
date >> $LOG_FILE
ls -l $OUTPUT_FILE >> $LOG_FILE
ls -l $OUTPUT_SQL_FILE >> $LOG_FILE
perl $WORKING_DIR/load_to_db.pl $OUTPUT_FILE $OUTPUT_SQL_FILE $LOG_FILE_PERL
echo ============================================ | tee >> $LOG_FILE
echo Finished Running perl script | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
date >> $LOG_FILE
ls -l $OUTPUT_FILE >> $LOG_FILE
ls -l $OUTPUT_SQL_FILE >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
echo Running $WORKING_DIR/load_data_to_db.sh | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
date >> $LOG_FILE
ls -l $OUTPUT_SQL_FILE >> $LOG_FILE
$WORKING_DIR/load_data_to_db.sh $DB_SCHEMA $OUTPUT_SQL_FILE
echo ============================================ | tee >> $LOG_FILE
echo Finished Loading Data from $OUTPUT_SQL_FILE | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
date >> $LOG_FILE
ls -l $OUTPUT_SQL_FILE >> $LOG_FILE
echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
echo Keep the log files under control | tee >> $LOG_FILE
echo ============================================ | tee >> $LOG_FILE
let limit_size=1024
check_file=$LOG_FILE
let size=`du -hk $check_file | awk '{print $1}'`
if [ $size -gt $limit_size ]; then
mv ${check_file} ${check_file}_bak
fi
check_file=$LOG_FILE_PERL
let size=`du -hk $check_file | awk '{print $1}'`
if [ $size -gt $limit_size ]; then
mv ${check_file} ${check_file}_bak
fi
exit 0
===========================================
list_processes.sh===========================================
#! /bin/bash
OUTPUT_FILE=$1
ps -eo pid,ppid,rss,size,vsize,pcpu,pmem,cmd -ww --sort=vsize | tail -50 | awk '{print "INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (SNAP_ID,SYSDATE,"$1","$2","$3","$4,","$5","$6","$7",'\''"$8"'\'',PARTITION_ID);" }' > $OUTPUT_FILE
exit 0
===========================================
load_to_db.pl
===========================================
#! /usr/bin/perl
use DBI;
use Net::FTP;
use Time::gmtime;
use File::Copy;
use strict;
use warnings;
#===========================================================================
# Global Variables
#===========================================================================
my $dbh;
############################################################################
# subroutines
############################################################################
sub getlocalDate
{
use Time::gmtime;
my $tm=gmtime; my $day; my $month; my $year;
($day,$month,$year) = (gmtime) [3,4,5];
my $localDate=sprintf("%04d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday);
return $localDate;
}
sub getSysdate
{
my $sth;
#my $sql=qq(SELECT TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL);
my $sql=qq(SELECT 'TO_DATE('''||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss')||''',''YYYYMMDD hh24:mi:ss'')' FROM DUAL);
my $res_sysdate;
$sth=$dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef,\$res_sysdate);
$sth->fetch();
$sth->finish();
return ($res_sysdate);
}
sub getSnapId{
my $sth;
my $sql=qq(SELECT MAX (snap_id) FROM DBA_HIST_SNAPSHOT);
my $snap_id;
$sth=$dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef,\$snap_id);
$sth->fetch();
$sth->finish();
return ($snap_id);
}
sub getPartitionId{
my $sth;
my $sql=qq(SELECT MOD(TO_NUMBER(TO_CHAR(SYSDATE,'WW')),10) FROM DUAL);
my $part_id;
$sth=$dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef,\$part_id);
$sth->fetch();
$sth->finish();
return ($part_id);
}
#============================
# main ()
#============================
#---------------------------------------
# variables
#---------------------------------------
my $input_file='';
my $output_file='';
my $logFile='';
$input_file=shift;
$output_file=shift;
$logFile=shift;
my $db_user='system';
my $db_pass='xen86pga';
my $db_inst='igt';
my $sysdate;
my $snap_id;
my $partition_id;
my $runDate;
my $RetCode;
my @mailArg;
my $my_sysdate;
$runDate=getlocalDate();
#---------------------------------------
# Open Logfile
#---------------------------------------
#unlink $logFile;
open MyLog,">>",$logFile or die $!;
print MyLog "----I---- ---------------------------------"."\n";
print MyLog "----I---- Starting OS counters collection "."\n";
print MyLog "----I---- ---------------------------------"."\n";
print MyLog "----I---- Input File: ".$input_file."\n";
print MyLog "----I---- Output File: ".$output_file."\n";
#---------------------------------------
# Open Database Connection
#---------------------------------------
print MyLog "----I---- Opening Database Connection ".$db_inst."@".$db_user."\n";
my $db_driver="dbi:Oracle:".$db_inst;
$dbh=DBI->connect($db_driver,$db_user,$db_pass,{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";
#---------------------------------------
# Fetch Database Paramaters
#---------------------------------------
print MyLog "----I---- Database Connection Opened"."\n";
$my_sysdate='';
$my_sysdate=getSysdate();
print MyLog "----I---- SYSDATE=".$my_sysdate."\n";
$snap_id=getSnapId();
print MyLog "----I---- snap ID=".$snap_id."\n";
$partition_id=getPartitionId();
print MyLog "----I---- snap ID=".$partition_id."\n";
#---------------------------------------
# read generated temp file and create sh_hist_os_processes.sql
#---------------------------------------
# INSERT INTO SH_HIST_OS_PROCESSES (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd) VALUES (SYSDATE,SNAP_ID,8608,1,10540,1536 ,1003824,0.0,0.5,'ora_qmnc_igt');
# INSERT INTO SH_HIST_OS_PROCESSES (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd) VALUES (SYSDATE,SNAP_ID,8608,1,10540,1536 ,1003824,0.0,0.5,'ora_qmnc_igt');
my $line;
my $input_line;
my $output_line;
my $line_counter;
#unlink $output_file;
open IN_FILE,$input_file or die $!;
open OUT_FILE,">>",$output_file or die $!;
print MyLog sprintf("----I---- After opening file ".$output_file."\n");
print OUT_FILE sprintf("SET TERMOUT OFF;\n");
print OUT_FILE sprintf("SET FEEDBACK OFF;\n");
print OUT_FILE sprintf("SET SHOW OFF;\n");
print OUT_FILE sprintf("SET VERIFY OFF;\n");
$line_counter=0;
while ($line = <IN_FILE>){
$line_counter+=1;
$input_line=$line;
#print MyLog "----I---- Reading Line ".$input_line."\n";
#print 'replacing SYSDATE with '.$my_sysdate."\n";
$line=~s/SYSDATE/$my_sysdate/ ;
#print 'replacing SNAP_ID with '.$snap_id."\n";
$line=~s/SNAP_ID/$snap_id/ ;
#print 'replacing PARTITION_ID with '.$snap_id."\n";
$line=~s/PARTITION_ID/$partition_id/ ;
print OUT_FILE sprintf("%s\n",$line);
}
print OUT_FILE sprintf("COMMIT;\n");
print OUT_FILE sprintf("EXIT;\n");
close IN_FILE or die $!;
close OUT_FILE or die $!;
print MyLog sprintf("----I---- ---------------------------------"."\n");
print MyLog sprintf("----I---- Finished OS counters collection "."\n");
print MyLog sprintf("----I---- ---------------------------------"."\n");
print MyLog sprintf("\n");
close MyLog or die $!;
#---------------------------------------
exit 0
load_data_to_db.sh
===========================================
#!/bin/bash
sqlplus -s $1 @$2
===========================================
Result
===========================================
The result is a SQL file, in the format below.
SET TERMOUT OFF;
SET FEEDBACK OFF;
SET SHOW OFF;
SET VERIFY OFF;
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),31243,1,12248,1568 ,1003852,0.3,0.5,'ora_p002_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),31245,1,12276,1568 ,1003852,0.3,0.5,'ora_p003_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),12694,1,23484,1596 ,1003880,0.0,1.1,'ora_q000_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),6615,1,21236,1600 ,1003884,0.0,1.0,'ora_j014_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),8612,1,110336,1680 ,1003968,0.0,5.3,'ora_mmnl_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),8559,1,39132,1700 ,1003988,0.0,1.8,'ora_ckpt_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),31262,1,14936,1708 ,1003992,0.5,0.7,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),31260,1,15388,1716 ,1004000,1.5,0.7,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),8563,1,269960,1716 ,1004004,0.0,13.0,'ora_reco_orainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),11459,1,81184,1724 ,1004008,0.0,3.9,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),1041,1,23372,1724 ,1004008,0.0,1.1,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),3724,1,20740,1724 ,1004008,0.0,0.9,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),25043,1,21176,1728 ,1004012,0.0,1.0,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),31264,1,22524,1728 ,1004012,6.5,1.0,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),28382,1,20828,1736 ,1004020,0.0,1.0,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),21345,1,25044,1740 ,1004024,0.0,1.2,'oracleorainst',4);
INSERT INTO SH_HIST_OS_PROCESS (snap_id, ts_date, pid, ppid, rss, mem_size, mem_vsize, pcpu, pmem, cmd, partition_id) VALUES (79003,TO_DATE('20160128 15:00:02','YYYYMMDD hh24:mi:ss'),15590,1,25816,1856 ,1004140,0.0,1.2,'oracleorainst',4);
COMMIT;
EXIT;
No comments:
Post a Comment