How to recording “vmstat” Output in Oracle Table?

San
Posts: 24
Joined: Sun Oct 09, 2016 2:53 pm

How to recording “vmstat” Output in Oracle Table?

Postby San » Sun Oct 09, 2016 3:15 pm

How to recording “vmstat” Output in Oracle Table?

sprasad
Posts: 24
Joined: Sun Oct 09, 2016 2:52 pm

Re: How to recording “vmstat” Output in Oracle Table?

Postby sprasad » Sun Oct 09, 2016 3:17 pm

To record vital CPU statistics given by the ‘vmstat’ command into Oracle Table.

The Same can be used to analyze CPU load over any given time interval.

HP–UX 11.23 Environment

Code for the shell script file “vminora.sh”: -

### start of shell script file ###


#### variable which holds oracle username/password and the connect string ####

loginstr=perfstat/perfstat@nsdldwh1

#### end of variable ####


#### oracle environment variables (variables will be different on different servers) ####

ORACLE_HOME=/orabinary/db; export ORACLE_HOME
ORACLE_SID=nsdldwh1;export ORACLE_SID
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr:/usr/bin:/etc:/usr/ccs/bin:$PATH;export PATH
EDITOR=vi;export EDITOR
TNS_ADMIN=$ORACLE_HOME/network/admin;export TNS_ADMIN
ORA_NLS10=$ORACLE_HOME/ocommon/nls/admin/data;export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/bin:/usr/ccs/lib:$ORACLE_HOME/lib;export LD_LIBRARY_PATH

#### end of oracle environment variables ####


SERVER_NAME=`uname -a|awk '{print $2}'`
### running uname -a and picking up the server name (2nd field) from awk command

typeset -u SERVER_NAME
export SERVER_NAME
### exporting the server name
vmstat 1 2 > /tmp/msg$$
### running vmstat command and directing its output to temporary file msg$$


# run vmstat and direct the output into the Oracle table . . .


### editing the output of vmstat stored in temp file msg$$ to get the required fields
### and linking the required fields to variables like runque, page_in etc

cat /tmp/msg$$|sed 1,3d |\ .
awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, $16, $17, $18) }' |\
read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU

## deleting lines 1 thru 3 with sed command
## picking up fields 1,8,9,16 etc via awk command
## assigning them to respective variables.


### end of editing output of vmstat ###

### Procedure to insert the above edited output into Oracle Table ###

runvmcollect() { ### name of procedure
cd /orabinary/db/bin ### navigating to oracle binary directory
sqlplus -S $loginstr << endl ### logging into oracle via sqlplus and passing username/passwd via variable loginstr
set echo off ### sqlplus environment setting
set feedback off ### sqlplus environment setting
set heading off ### sqlplus environment setting
set timing off ### sqlplus environment setting
set pages 0 ### sqlplus environment setting
set lines 90 ### sqlplus environment setting
set trimspool on ### sqlplus environment setting
set serveroutput on size 1000000 ### sqlplus environment setting
set serveroutput on format wrap ### sqlplus environment setting
insert into perfstat.stats_vmstat values (sysdate,'$SERVER_NAME',$RUNQUE,$PAGE_IN,$PAGE_OUT,$USER_CPU,$SYSTEM_CPU,$IDLE_CPU,0);
### above line inserts into the oracle table the current sysdate and the vmstat values from ### the variables $page_out etc
commit;
exit
endl
}
### End of Procedure to insert the above edited output into Oracle Table ###
runvmcollect ### calling the above defined procedure
rm /tmp/msg$$ ### removing vmstat output from temp file.

### End of shell script file ###



## HP-UX CRONTAB ENTRY TO RUN THE ABOVE CODE ##

TO MAKE ENTRY IN THE CRONTAB OF ORACLE OR ROOT USER RUN THE LINES BELOW

$ crontab -e (e for edit)

copy paste the line below and exit like you exit in vi editor

5,10,15,20,25,30,35,40,45,50,55 0-23 1-31 1-12 1-6 sh /local/sarstats/vminora.sh

The above lines runs the shell script after every 5 Minutes (starting from 5th min of hour to 55th min of hour)
for every hour (0-23) for every day (1-31) for every month (1-12) for the days Monday (1) to Saturday (6).

################ end of HP-UX crontab explanation #########



################ Oracle Table Structure to record vmstat output ##


CREATE TABLE STATS_VMSTAT
(
START_DATE TIMESTAMP(6),
SERVER_NAME VARCHAR2(20 BYTE),
RUNQUE_WAITS NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER,
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
IDLE_CPU NUMBER,
WAIT_CPU NUMBER
)
TABLESPACE PERFSTAT
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;


################ End of Oracle Table Structure #####


Return to “Scripting”

Who is online

Users browsing this forum: No registered users and 1 guest