Health script

 

#!/bin/ksh
###############################################################################################
## Purpose:  This script is used to check db session details
## Author :   self
## Usnage :  ./we.ksh <Instance Name>
##              Ex: ./we.ksh LPCRTAB1
###############################################################################################

if [ -z "$1" ];
then
  echo
  echo "Usage   : $0  <SID>"
  echo "Example : $0 LPCRTAB1"
  echo
exit
fi

. ~/dbs  $1

RUN_DIR=`dirname $0` ; export RUN_DIR
if [ $(echo ${RUN_DIR} | cut -c1) != "/" ]; then
   RUN_DIR=$(echo `pwd`/${RUN_DIR}) ;
echo $RUN_DIR
fi
LOG_DIR=${RUN_DIR}/log ; export LOG_DIR
LOG=${LOG_DIR}/sdba_account_cleanup_$1.log ; export LOG

$ORACLE_HOME/bin/sqlplus -S / as sysdba<< EOF
set time on timing on
spool ${LOG}
alter session set NLS_DATE_FORMAT='MM-DD-YYYY HH24:MI:SS';

SELECT USERNAME,STATUS,COUNT(*) FROM GV\$SESSION WHERE STATUS ='INACTIVE'  GROUP BY USERNAME,STATUS;
set head off
select '#########Blocking session details with SQL_ID,Holder SID and SERIAL# #############' from dual;
select c.sql_id, a.HOLDING_SESSION ,c.sql_text
from dba_blockers a ,v\$session b ,v\$sql c
where a.HOLDING_SESSION=b.sid and b.sql_hash_value=c.hash_value;
select tbs from dual;
set head off
select '############# Blocking session details ###########' from dual;
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v\$lock l1, v\$session s1, v\$lock l2, v\$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by l1.id1, l2.request;


select '#########Top Recent Wait Events######' from dual;
set head on
col EVENT format a60

select * from (select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v\$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6;
set head off
select '#############Performance status summary############' from dual;
set verify off
set feed off
set newpage 0
set array 100
set pages 0
set wrap off
set flush off
set feedback off
set echo off
set verify off
--set term off
set head off
set trim on
column DBNAME new_value c_dbname noprint
SELECT sys_context('USERENV', 'DB_NAME') DBNAME FROM DUAL;
--column  "&&c_dbname " format a60
set pages 15
--set head on
WITH nums as (
Select 'C'||Rownum  rn From dual Connect By Rownum  < 12 )
select decode
(rn,
'C1', 'Long Operations              : ' ||
    ( select count(*) from v\$session_longops where sofar <> totalwork     ),
'C2', 'Checkpoint Delay             : ' ||
    (
    select round(((sysdate - min(first_time) ) * 1440),0) || ' Minutes ' from v\$log a, v\$database b
    where a.first_change# > b.checkpoint_change#
    and status !='INACTIVE'
    and thread#=userenv('INSTANCE')
    group by status
    having count(status) > 1  ),
'C3', 'Active/TotalLogs             : ' ||
    ( select Activelogs||' / '|| totallogs from    (select count(*) ActiveLogs from v\$Log
      where status !='INACTIVE'  and thread#=userenv('INSTANCE')), ( select count(*) TotalLogs  from v\$log
      where thread#=userenv('INSTANCE'))  b ),
'C4', 'Active/TotalSessions         : ' ||
    (select Active||' / '|| Total from ( select /*+ rule */  count(*) Active from v\$session where status ='ACTIVE') ,
    (select /*+ rule */  count(*) Total from v\$session ) ) ,
'C5', 'Backup Progress              : '||
      (
        select /*+ rule */  count(*)  || ' Sessions'  from gv\$session
        where username ='SYS'
        and program like 'rman%'
        and module like 'backup%'
        and status ='ACTIVE'
      ) ,
'C6', 'Scheduler Jobs Running       : '||
       (
       select /*+ rule */  count(*)
       from dba_scheduler_running_jobs
       )  ,
'C7', 'Blocked Sessions             : '||
       (
       select /*+ rule */  count(*)
       from v\$session
       where blocking_session_status ='VALID'
       ) ,
'C8', 'Rollback Sess 100 blk        : '||
       (
        select /*+ rule */ count(*)  from v\$transaction
        where used_ublk  > 100
       ) ,
'C9', 'AWR  Last Generated          : '||
      (
      select round((sysdate - TO_DATE (TO_CHAR (max_awr_date, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') ) * 1440  ,0) || '  Minutes back'
      from ( select max(end_interval_time) max_awr_date from dba_hist_snapshot where dbid = ( select dbid from v\$database)  )
      ) ,
'C10', 'PGA  Excessive Usage ->50MB  : '||
      (
                select count(*) from v\$Process
                where pga_alloc_mem > 50*1048576
      ) ,
'C11', 'DDL  Operations              : '||
      (
                select count(*)   from gv\$session
                where command not in ( 0,2,3,6,7,47,-86)
      )
)
from nums
/
exit

EOF