Friday, July 4, 2014

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.

No comments:

Post a Comment

Convert snapshot standby database to Physical standby database: Dataguard 11gR2

Step 1 SQL> shutdown immediate; Step 2 SQL> startup nomount Step 3 SQL> alter database mount; Step 4 SQL>  ALTER DATABASE CONV...