Database Health checkup script

Database Health checkup script

This script need to run with INSTANCE_NAME. without this it will errored out where script is connecting to db.
Script Name :   db_healthcheck.sh

run like ./db_healthcheck.sh <Instance Name>

Script is below

#/bin/ksh

#Create by Amaresh

#Created Date 03/12/2009

#Modified Date 03/07/2014

# This script is created for doing quick health check.

export logfile=/tmp/healthcheck.log

export ORACLE_SID=$1

#System uptime

echo -e "\n\n============================System Uptime================================"

echo hostname

hostname

echo

uptime

echo    "==============================End System Uptime=============================="

echo -e "\n\n===========================Sar Output===================================="

sar 2 10

echo    "==============================End Sar Output================================="

#echo -e "\n\n==========================Memory Free Stats=============================="

#free -m

#echo    "=============================End Memory Status==============================="

echo -e "\n\n============================Disk Status=================================="

df -h

echo    "==============================End Disk Status================================"

echo -e "\n\n================Top 20 process Stats 3 times from Top command============"

echo First Time

echo

top -c -n 1|head -30

echo

echo 2nd Time

top -c -n 1|head -30

echo

echo

echo 3rd Time

top -c -n 1|head -30

echo

echo  " =================================End of Top Command==========================="

echo -e "\n\n=======================Listener Status==================================="

echo

#ps -ef |grep tns

ps -ef |grep tns|grep inherit

lsn=`ps -ef |grep tns|grep -v grep| awk '{print $9}'`

echo $lsn

lsnrctl status $lsn

echo

echo    "=========================End Listener Status================================="

echo -e "\n\n================Oracle Enterprise Manager================================"

ps -ef |grep -v grep|grep -i "/bin/emagent"

agent=`ps -ef |grep -i "/bin/emagent"|grep -v grep| awk '{print $8}'|rev| cut -c 8- | rev`

echo $agent

cd $agent

./emctl status agent

echo    "=========================End Enterprise Manager=============================="

echo -e "\n\n========================================================================="

echo -e "\n\n=======================Checkign Backup logs====================================="

echo -e "\n\n========Archive Log Backup checks ==========="

ls -ltr /orabkup/$ORACLE_SID/rman_arch*.log

echo -e "\n\n========RMAN Full Backup checks ============="

ls -ltr /orabkup/$ORACLE_SID/rman_$ORACLE_SID*.log

echo -e "\n\n========Export Backup checks ================"

ls -ltr /orabkup/$ORACLE_SID/exp*.log

echo -e "\n\n========Backup checksthrough RMAN ==========="

sqlplus /nolog <<!

conn /as sysdba

set lines 120

set pages 1000

col RMAN_Status FORMAT A20 heading "Status"

col INPUT_TYPE  FORMAT A15 heading "Backup Type"

col Hrs         FORMAT 999.99 heading "Backup Time"

col Start_Time  FORMAT A20 heading "Backup Start Time"

col End_Time  FORMAT A20 heading "Backup End Time"

SELECT INPUT_TYPE,

       TO_CHAR(START_TIME,'DY mm/dd hh24:mi') Start_Time,

       TO_CHAR(END_TIME,'DY mm/dd hh24:mi')   End_Time,

       ELAPSED_SECONDS/3600                  Hrs,

       STATUS   RMAN_Status

FROM V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate -15

ORDER BY SESSION_KEY desc;

!

echo    "=========================End Backup Checks==================================="

echo -e "\n\n========================================================================="

echo -e "\n============================Database statistics============================"

echo -e "\n==========================================================================="

# check startup time

export blk_size=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter db_block_size

exit;

END`

block_size=`echo $blk_size| awk '{print $3}'`

#echo $block_size

echo -e "\n=============Checks for AUTO EXTEND Data file  and TS Utilization======================"

sqlplus /nolog <<!

conn /as sysdba

select instance_name, inst_id,to_char(startup_time,'dd/mm/yyyy hh24:mi:ss') startuptime  from gv\$instance;

set line 150

set pages 1000

define block_size =$block_size;

col TABLESPACE_NAME for a30

select a.tablespace_name, b.no_of_data_files , b.max_size_M,a.BLOCK_SIZE,a.ALLOCATION_TYPE,a.SEGMENT_SPACE_MANAGEMENT,a.EXTENT_MANAGEMENT,b.AUTOEXTENSIBLE from dba_tablespaces a  , (select tablespace_name, AUTOEXTENSIBLE, count(*) as no_of_data_files , sum(maxblocks*&block_size)/1024/1024 as max_size_M from dba_data_files group by tablespace_name,AUTOEXTENSIBLE) b where a.tablespace_name=b.tablespace_name;

set line 199

set pages 1000

col TABLESPACE_NAME for a30

col file_name for a50

show parameter db_block_size

select tablespace_name, file_name, AUTOEXTENSIBLE, sum(maxblocks*&block_size)/1024/1024 as max_size_M  from dba_data_files group by tablespace_name, file_name, AUTOEXTENSIBLE  order by 1;

!echo checking file ststus

select distinct status from dba_data_files;

select count(*) from v\$recover_file;

!echo Tablespace Utiliazation Report

column tablespace_name  heading "TableSpace" format a30

column total_space      heading "TotalSpace" format 9999999

column free_space       heading "FreeSpace"  format 9999999

column used             heading "UsedSpace"  format 9999999

column pct              heading "Used %"     format 999.99

select a.tablespace_name,total_space,free_space,

(total_space-free_space) used,

((total_space-free_space)/total_space)*100 pct

from

(select tablespace_name,sum(bytes)/(1024*1024) total_space

 from   dba_data_files

 group by tablespace_name) a,

(select tablespace_name,sum(Bytes)/(1024*1024) free_space

 from  dba_free_space

 group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

order by 5 desc;

!echo  checking blocking locks

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

         id1, id2, lmode, request, type

    FROM V\$LOCK

   WHERE (id1, id2, type) IN

             (SELECT id1, id2, type FROM V\$LOCK WHERE request>0)

   ORDER BY id1, request;

!echo Session holding Library cache pin locks

select /*+ ordered */ w1.sid  waiting_session,

h1.sid  holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,

decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v\$session w1, v\$session h1

where (((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and (((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and  w.kgllktype         =  h.kgllktype

and  w.kgllkhdl  =  h.kgllkhdl

and  w.kgllkuse     =   w1.saddr

and  h.kgllkuse     =   h1.saddr;

!

echo -e "\n\n==================================END of Database status report========="

echo -e "\n\n========================== DR checks ==================================="

sqlplus /nolog <<!

conn /as sysdba

clear column

!echo "================  Checks for DR parameters ================"

!echo "==================  Checks for DR parameters ================"

show parameter log_archive_dest_state_2

show parameter log_archive_dest_2

set line 150

set pages 1000

select switchover_status from v\$database;

!echo  "=============================== Checks DR Log Applied and Log Archived =============="

!echo -e "\n \n"

select rtrim(ltrim(max(al.sequence#))) as LOG_APPLIED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.applied='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select rtrim(ltrim(max(al.sequence#))) as LOG_ARCHIVED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.ARCHIVED='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from V\$MANAGED_STANDBY;

!

echo -e "\n\n===================Last 50 Lines of Alertlog for DR checks=============="

VALUE=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter background_dump_dest;

exit;

END`

echo $VALUE

tmp=`echo $VALUE| awk '{print $3}'`

echo $tmp

tail -50 $tmp/alert_*.log

echo -e "\n\n\n\n\n======================End of Database alter Messaes================"

Please give me feedback if you want to anything in this script, Will try to put those as well.