Quick and Dirty Monitoring

I was getting CPU alerts in the middle of the night from AWS. Cloudwatch wasn't showing anything with their graphs and SAR showed 95% idle.

Save the following into a file and run in the background

nohup ./foo.sh &

CPU/Load Average/Top 5 processes via TOP and PS

!/bin/bash

typeset -i looper=1
spoolFile="/tmp/tmpSnooper.log"

ORACLE_BASE="/opt/oracle"
ORACLE_HOME="${ORACLE_BASE}/product/12.1.0/client_1"
TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin
export PATH ORACLE_BASE ORACLE_HOME TNS_ADMIN

PLUS="sqlplus -s xxxxx/xxxxx@xxxx"

while (( $looper ))
do
   echo "-------------------------------------------------" >> ${spoolFile}
   date >> ${spoolFile}
   echo "-------------------------------------------------" >> ${spoolFile}

   top -b | head -n 12 | tail -n 6 >> ${spoolFile}
   uptime >> ${spoolFile}
   ps -Ao user,uid,comm,pid,pcpu,tty --sort=-pcpu | head -n 6 >> ${spoolFile}

   ${PLUS} << EOF >> ${spoolFile}
   set pagesize 100
   set linesize 300
   set echo off
   set feedback off
   column sql_text format a120
   column username format a15
   column osuser format a10
   column os_pid format a10
   column machine format a10
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.osuser,
    s.machine,
    s.program,
    p.spid              AS os_pid,
    s.sql_id,
    sa.executions,
    sa.disk_reads,
    sa.buffer_gets,
    sa.rows_processed,
    ROUND(sa.elapsed_time/1e6,2) AS elapsed_sec,
    ROUND(sa.cpu_time/1e6,2)     AS cpu_sec,
    sa.sql_text
FROM v\$session s
JOIN v\$process p ON s.paddr = p.addr
LEFT JOIN v\$sqlarea sa
     ON s.sql_id = sa.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
  AND s.type = 'USER'
ORDER BY sa.elapsed_time DESC NULLS LAST;
exit

EOF

  sleep 60
done