Send mail using Linux mailx function

Script to send mail and query output through mailx function

Step 1: Make a folder save the following three files:

1. Check.sh
2. Query.sql
3. message-body.txt

Step 2: Save below in check.sh

##############################################
# oracle oratab file location
cd /u01/app/oracle/script/ROLFSCHEDULEDJOB
ORATAB=/etc/oratab
if [ ! -f $ORATAB ]
then
exit 1
fi
# Loop for every entry in ORATAB file
cat $ORATAB|grep -v '^$' | while read LINE
do        case $LINE in
\#*)    # Comment-Line in ORATAB
;;        *)
# Setup ORACLE_SID and ORACLE_HOME
# if third field in ORATAB is 'Y'
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ]
then
ORACLE_SID=""
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
#echo ORACLE_SID=$ORACLE_SID
#echo ORACLE_HOME=$ORACLE_HOME
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
#export PATH=$PATH:$ORACLE_HOME/bin
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exit|$ORACLE_HOME/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" / as sysdba @query.sql > $ORACLE_SID.html
esac # End case $LINE
done
# End Loop read ORATAB

( cat body-message.txt; uuencode NRESST.html NRESST.html; uuencode NRESST11.html NRESST11.html;uuencode NRESSQ.html NRESSQ.html)
| mail -s "OBIWAN Server Reports" mail@mail.com -c mail@mail.com

Step 3: Save query in query.sh folder

set feedback off
set serverout on
set wrap off
set pages 300
set lines 150
col segment_name for a50
PROMPT ===============================================================
PROMPT DATABASE NAME AND HOST NAME
PROMPT ===============================================================
select INSTANCE_NAME,HOST_NAME,VERSION,STATUS from v$instance;
PROMPT ===============================================================
PROMPT SCHEMA SIZE OF DATABASE
PROMPT ===============================================================
select OWNER,sum(bytes)/1024/1024 "SIZE MB"  from dba_segments group by owner order by 1;
PROMPT ===============================================================
PROMPT TOP 20 LARGE INDEX SIZE
PROMPT ===============================================================
select * from
(select OWNER,segment_name "Index Name",sum(bytes)/1024/1024 "Size of Index(MB)"
from DBA_SEGMENTS where segment_type='INDEX' group by owner,segment_name order by 3 desc)where rownum =sysdate-2 and operation = 'BACKUP' order by start_time desc;

Step 4: Save file message body:

Hi,

Please find the attached reports.

It's auto-generated email, kindly do not reply to this mail.

Regards,
Oracle DBA Team

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s