#!/bin/bash
SID=ORACLE_SID
USERNAME="dbuser"
PASSWORD="dbpassword"
ZABBIX_SERVER=zabbix-server.example.com
ZABBIX_HOST="Zabbix Server"
. /opt/oracle/product/11.2/oracle.env
export ORACLE_SID="${SID}"
NOW=$(date +%s)
JobSTART_TIME=$(date +%s.%N)
LAST_WEEK=$((NOW-3600*24*7))
LAST_MONTH=$((NOW-3600*24*30))
LAST_YEAR=$((NOW-3600*24*365))
TOTAL_ROWS=0
query_get ()
# Pass query body
# run query and parse
# spit out results
{
local QUERY
local START_TIME=$(date +%s.%N)
case $1 in
alerts ) QUERY="delete from alerts where clock<${LAST_YEAR}";;
history ) QUERY="delete from history where clock<${LAST_MONTH}";;
history_uint ) QUERY="delete from history_uint where clock<${LAST_MONTH}";;
history_str ) QUERY="delete from history_str where clock<${LAST_WEEK}";;
history_text ) QUERY="delete from history_text where clock<${LAST_WEEK}";;
history_log ) QUERY="delete from history_log where clock<${LAST_WEEK}";;
trends ) QUERY="delete from trends where clock<${LAST_YEAR}";;
trends_uint ) QUERY="delete from trends_uint where clock<${LAST_YEAR}";;
acknowledges ) QUERY="delete from acknowledges where eventid=(select eventid from events where clock<${LAST_YEAR})";;
events ) QUERY="delete from events where eventid=(select eventid from events where clock<${LAST_YEAR})";;
* ) echo "Fail.";exit 1;;
esac
local SPOOL=/var/tmp/query_get.$RANDOM
local getResult=$(sqlplus -S ${USERNAME}/${PASSWORD}@${ORACLE_SID} << EOF
set pagesize 100
set linesize 100
set heading off
set tab off
spool $SPOOL
${QUERY};
spool off
commit;
quit
EOF)
local END_TIME=$(date +%s.%N)
local ELAPSED=$(printf "%.3F" $(echo "$END_TIME - $START_TIME"|bc))
local ROWS=$(grep rows $SPOOL | awk '{print $1}')
TOTAL_ROWS=$((TOTAL_ROWS + ROWS))
echo "/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s \"${ZABBIX_HOST}\" -k hk.rows_deleted[${1}] -o ${ROWS}"
/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s "${ZABBIX_HOST}" -k hk.rows_deleted[${1}] -o ${ROWS}
echo "/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s \"${ZABBIX_HOST}\" -k hk.time_elapsed[${1}] -o ${ELAPSED}"
/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s "${ZABBIX_HOST}" -k hk.time_elapsed[${1}] -o ${ELAPSED}
rm $SPOOL
return
}
#echo
#echo -n "Time now: "
#echo $NOW| perl -e 'print localtime(<>) . "\n";'
#echo -n "Deleting history older than: "
#echo $LAST_MONTH| perl -e 'print localtime(<>) . "\n";'
#echo -n "Deleting trends older than: "
#echo $LAST_YEAR| perl -e 'print localtime(<>) . "\n";'
#echo
for TABLE in acknowledges alerts events history history_log history_str history_text history_uint trends trends_uint; do
query_get $TABLE
done
TOTAL_ELAPSED=$(printf "%.3F" $(echo "$(date +%s.%N) - $JobSTART_TIME"|bc))
echo "/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s \"${ZABBIX_HOST}\" -k hk.rows_deleted[TOTAL] -o ${TOTAL_ROWS}"
/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s "${ZABBIX_HOST}" -k hk.rows_deleted[TOTAL] -o ${TOTAL_ROWS}
echo "/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s \"${ZABBIX_HOST}\" -k hk.time_elapsed[TOTAL] -o ${TOTAL_ELAPSED}"
/usr/local/bin/zabbix_sender -z ${ZABBIX_SERVER} -s "${ZABBIX_HOST}" -k hk.time_elapsed[TOTAL] -o ${TOTAL_ELAPSED}
Comment