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.
