Pages

Thursday, January 28, 2016

Linux monitoring by Example. Code Example for bash, perl, sqlplus.

===========================================
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.sh
load_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;  
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)