email alert configure in Linux/UNIX/Solaris/Windows server for Database health monitoring

The following process we configure for email alert to check Database Health

Step 1. Configure SMTP client from where we will send mail

[oracle@said dbalert]$ cat /etc/mail.rc
# This is the configuration file for Heirloom mailx (formerly
# known under the name "nail".
# See mailx(1) for further options.
# This file is not overwritten when 'make install' is run in
# the mailx build process again.

# Sccsid @(#)nail.rc 2.10 (gritter) 3/4/06

# Do not forward to mbox by default since this is likely to be
# irritating for most users today.
set hold

set smtp=smtp://10.11.1.204          ----smtp server information
set from="db.alert@saidrase.info"  ----email account from where mail send

Step 2. configure a shell script into smtp client server from where mail send to destination mail address


#!/bin/bash
NOW=$(date +"%M-%H-%m-%d-%Y")
MAIL_TO="saidrase@yahoo.com","rasel.said@gmail.com"
ssh oracle@10.88.1.112 /export/home/oracle/dbalert/dbinfo_mis.sh
scp oracle@10.88.1.112:/export/home/oracle/dbalert/MIS_Database_Info.html /u01/dbalert/
ssh oracle@10.88.1.112 rm -f /export/home/oracle/dbalert/MIS_Database_Info.html
##ssh oracle@10.88.1.112 df -h >>/u01/dbalert/Database_Info.html
##ssh oracle@10.88.1.112 tail -30 /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/alert_bacdb.log >>/u01/dbalert/Database_Info.html
echo "

File System Information    

"  >> /u01/dbalert/MIS_Database_Info.html
echo ""  >> /u01/dbalert/MIS_Database_Info.html</p> <p> ssh oracle@10.88.1.112 df -h >>/u01/dbalert/mis_fs.txt</p> <p> cat /u01/dbalert/mis_fs.txt >> /u01/dbalert/MIS_Database_Info.html</p> <p> echo "" >> /u01/dbalert/Database_Info.html
echo "

Alert Log Information    

"  >> /u01/dbalert/MIS_Database_Info.html
echo ""  >> /u01/dbalert/MIS_Database_Info.html</p> <p> ssh oracle@10.88.1.112 tail -30 tail -f /u01/app/oracle/diag/rdbms/orcl/cdb1/trace/alert_cdb1.log >>/u01/dbalert/alert_mis.txt</p> <p> cat /u01/dbalert/alert_mis.txt >> /u01/dbalert/MIS_Database_Info.html</p> <p> echo "" >> /u01/dbalert/MIS_Database_Info.html
echo "10.88.1.112 Server Health Check Information. Please Download the attached file" | mail -v  -a /u01/dbalert/MIS_Database_Info.html -s "MIS Database Server Health Check" $MAIL_TO
rm /u01/dbalert/alert_mis.txt /u01/dbalert/mis_fs.txt
mv /u01/dbalert/MIS_Database_Info.html /u01/dbalert/MIS_Database_Info_$NOW.html
exit


Step 3. Prepare a shell script to connect Database into monitoring Database server

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
PATH=$ORACLE_HOME/bin:$HOME/bin:/usr/bin/X11:/usr/bin/X11R6:/usr/local/sbin:/sbin:/bin:/emc/EMCpower/reloc/EMCpower/bin/sparcv9:/etc/emc/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/openwin/bin:.; export PATH
ORACLE_PATH=$ORACLE_HOME/bin:.; export ORACLE_PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
LD_LIBRARY_PATH_64=$ORACLE_HOME/lib; export LD_LIBRARY_PATH_64
CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH
ORACLE_SID=orcl; export ORACLE_SID
sqlplus  -s "/ as sysdba" << EOF
set trimspool on
SET LINE 1000
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET HEADING OFF
SET FEEDBACK OFF
SET wrap off
SET MARKUP HTML ON
column TABLESPACE_NAME for a30
spool on
spool /home/oracle/dbalert/MIS_Database_Info.html
@/home/oracle/dbalert/version_MIS.sql
spool off
exit;
EOF
exit;

Step 4. Prepare a sql script to collect data from Database server 

SET MARKUP HTML ON

SET MARKUP HTML OFF

conn c##monitor/""@dbname

select '

' || 'MIS Database Server Healthcheck Information (10.88.1.112)' || '' || '' || '

' from dual;

select  '

' || 'Tablespace Information used more than 85% in MISDB PDB' || '' || '' || '' || '

' from dual;
conn c##monitor/""@dbname

SET MARKUP HTML ON
SET PAGES 800
SET HEADING ON

select a.TABLESPACE_NAME,a.d "No of DataFile",round(a.BYTES) "TOTAL_SIZE_(M)",a.BYTES-b.BYTES "USED (M)",round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "%USED"
from
(
  select TABLESPACE_NAME,count(file_name) D,sum(BYTES)/1048576 BYTES
  from dba_data_files
  group by TABLESPACE_NAME
) a,
(
  select TABLESPACE_NAME,sum(BYTES)/1048576 BYTES, max(BYTES)/1048576 largest
  from dba_free_space
  group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.TABLESPACE_NAME not like '%UNDO%'
and round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) > 85
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

set heading on timing off

SET HEADING OFF
SET MARKUP HTML OFF

select  '

' || 'Log Switch Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col day for a11
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
,sum(1) "TOTAL_IN_DAY"
from v$log_history where first_time > sysdate-5
GROUP by to_char(first_time,'YYYY-MON-DD') order by 1 asc;

SET MARKUP HTML OFF
SET HEADING OFF

SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database User Login Since Last 6 Hours' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col Logon_Time for a22
col machine for a25

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') Logon_Time,osuser,status,USERNAME,schemaname,machine
from v$session
where type !='BACKGROUND'
AND osuser not in ('root','oracle','grid','WIN-MQ01PK54063$','BSAPP','OracleHomeUser')
and trunc(logon_time)=trunc(sysdate-1/4);

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database Invalid Object Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col owner for a22


SELECT owner,count(*) Total_Invalid_Objects FROM dba_objects where status='INVALID' group by owner;

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database FRA Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col file_type for a22

SELECT file_type,percent_space_used FROM v$flash_recovery_area_usage;

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database UpTime Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col InsName for a22

SELECT INSTANCE_NAME InsName,to_char(STARTUP_TIME,'DD-MON-YY hh24:mi:ss')  StartupTime FROM  v$instance ;

SET MARKUP HTML OFF
SET HEADING OFF