Linux/Unix shell automatically sends AWR report

Observe the Oracle database performance. The awr feature provided by Oracle provides us with a nearly perfect solution. With awr features, we can extract awr reports from the database at any time. However, we need to provide some interactive information when executing the awrrpt. SQL script, so we can integrate it into the shell script to automatically generate awr reports for a specified period of time and send them to relevant personnel. This article describes how to implement the linux shell script.

1. shell script

Robin @ SZDB :~ /Dba_scripts/custom/awr> more autoawr. sh
#! /Bin/bash
# -------------------------------------------------------------------------- +
# Check alert log file |
# Filename: autoawr. sh |
# Desc: |
# The script use to generate AWR report and send mail automatic. |
# The SQL script autoawr. SQL call by this shell script. |
# Default, the whole day AWR report will be gathered. |
# Deploy it to crontab at 23: 30 |
# If you want to change the snap interval, please change autoawr. SQL |
# And crontab configuration |
# Usage: |
#./Autoawr. sh $ ORACLE_SID |
# |
# Author: Robinson |
# Blog: http://blog.csdn.net/robinson-0612 |
# -------------------------------------------------------------------------- +
#
#--------------------------
# Check SID
#--------------------------

If [-z "$ {1}"]; then
Echo "Usage :"
Echo "'basename $0 'oracle_sid"
Exit 1
Fi

#-------------------------------
# Set environment here
#------------------------------

If [-f ~ /. Bash_profile]; then
.~ /. Bash_profile
Fi

Export ORACLE_SID = $1
Export MACHINE = 'hostname'
Export MAIL_DIR =/users/robin/dba_scripts/sendEmail-v1.56
Export MAIL_LIST = 'robinson. cheng@12306.com'
Export AWR_CMD =/users/robin/dba_scripts/custom/awr
Export AWR_DIR =/users/robin/dba_scripts/custom/awr/report
Export MAIL_FM = 'oracle @ szdb.com'
RETENTION = 31

#----------------------------------------------
# Check if the database is running, if not exit
#----------------------------------------------

Db_stat = 'ps-ef | grep pmon _ $ ORACLE_SID | grep-v grep | cut-f3-d _'
If [-z "$ db_stat"]; then
# Date>/tmp/db _ $ {ORACLE_SID} _ stauts. log
Echo "$ ORACLE_SID is not available on $ {MACHINE }!!! "#>/Tmp/db _ $ {ORACLE_SID} _ stauts. log
MAIL_SUB = "$ ORACLE_SID is not available on $ {MACHINE }!!! "
MAIL_BODY = "$ ORACLE_SID is not available on $ {MACHINE} at 'date' when try to generate AWR ."
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-m $ MAIL_BODY
Exit 1
Fi;

#----------------------------------------------
# Generate awr report
#----------------------------------------------
$ ORACLE_HOME/bin/sqlplus/nolog <EOF
Connect/as sysdba;
@ $ {AWR_CMD}/autoawr. SQL;
Exit;
EOF

Status = $?
If [$ status! = 0]; then
Echo "$ ORACLE_SID is not available on $ {MACHINE }!!! "#>/Tmp/db _ $ {ORACLE_SID} _ stauts. log
MAIL_SUB = "Occurred error while generate AWR for $ {ORACLE_SID }!!! "
MAIL_BODY = "Some exceptions encountered during generate AWR report for $ ORACLE_SID on 'hostname '."
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-m $ MAIL_BODY
Exit
Fi

#------------------------------------------------
# Send email with AWR report
#------------------------------------------------
Dt = 'date-d yesterday + % Y % m % d'
Filename = 'ls $ {AWR_DIR}/$ {ORACLE_SID} _ awrrpt _? _ $ {Dt }*'
If [-e "$ {filename}"]; then
MAIL_SUB = "AWR report from $ {ORACLE_SID} on 'hostname '."
MAIL_BODY = "This is an AWR report from $ {ORACLE_SID} on 'hostname '."
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-m $ MAIL_BODY-a $ {filename}
Echo $ {filename}
Fi

#------------------------------------------------
# Removing files older than $ RETENTION parameter
#------------------------------------------------

Find $ {AWR_DIR}-name "* awrrpt *"-mtime + $ RETENTION-exec rm {}\;
Exit

1 2 3 4 5 Next Page