Oracle Database Health Check Shell Script

Database health check is one of the basic task that each and every DBA perform in their day to day activity but manually performing that task takes some time and manual effort too. So we can automate it to reduce manual effort which also saves DBA’s time, Below is the shell script that we can use to perform basic health check.

                    MAIN SHELL SCRIPT

#!/bin/ksh

#set -xv

#set -n

typeset -u DB_UNIQ NAME=$1

HLTH_CHECK=/tmp/DB_HEALTH_CHECK.log

if [ -z $1 ] then

echo  -e "\033[32mPlease Pass Correct DB_UNIQUE NAME in Format <./SCRIPT_NAME> <DB_UNIQUE_NAME>, Exiting......\033[0m"

exit 1;

else

echo  -e "\033[32mDB_UNIQUE NAME IS : $DB UNIQ NAME\033[0m\n"

echo  -e "\033[32mContinuing With DB Health Check......\033[0m\n"

fi

sqlplus -S "/ as sysdba" <<EOF >> $HLTH CHECK

SET LINES 300 COLSEP 'l'

COL NAME FOR A15

COL DATABASE_ROLE FOR A15

COL START_TIME FOR A20

COL HOST_NAME FOR A40

COL OPEN_TIME FOR A40

COL RECOVERY_STATUS FOR A30

COL LOCAL_UNDO FOR 9999999999

COL PROCESS FOR A10

COL STATUS FOR 10

COL THREAD# FOR 9999999

COL SEQUENCE# FOR 9999999999

COL DELAY_MINS FOR 999999999

COL BLOCK# FOR 99999999999

COL BLOCKS FOR 99999999999

!echo -e "\033[32mChecking CDB STATUS.....\033[0m"

select name,database_role,to_char (startup_time, 'DD-MON-YYYY HH24:MI:SS') as start_time,open_mode,host_name,instance_name,status v\$database,gv\$instance;

!echo -e "\033[32mChecking PDB STATUS....\033[0m\n"

select name, open_mode,open_time, restricted, local_undo, recovery status from gv\$pdbs order by name;

echo -e "\033[32mChecking Standby Sync Status....\033[0m\n"

select arch.thread as thread, arch.sequence as "Last Sequence Received", APPL.sequence "Last Sequence Applied",(arch.sequence# - appl.sequence#) as "Difference" from(select thread,sequence# from v\$archived_log where (thread#, first_time) in (select thread#,max (first time) from v\$archived_loggroup by thread#)) arch,(select thread#,sequence# from v\$log_history where (thread#, first_time) in (select thread#,max (first_time)from v\$log_history group by thread#)) appl where arch.thread#=appl.thread# order by 1;

echo -e "\033[32mChecking MRP Status In Case Of Phsyical Standby....\033[0m\n"

select Process,status,thread#,sequence#,delay_mins,block#,blocks from gv$managed_standby where prcoess in('LNS','RFS','MRP0') order by process;

exit;

EOF

echo -e "\033[32m Health Check Completed Successfully....\033[0m\n"

## Database Service Status

echo -e "\033[32mReteriving Database Service Info.....\033[0m" | tee -a $HLTH_CHECK

srvctl status service -d $DB_UNIQ_NAME >> $HLTH_CHECK

cat $HLTH CHECK

## Removing Database Health Check File

echo -e "\033[32mRemvoing Health Check File.....\033[0m"

rm -rf $HLTH CHECK