Linux/Unix shell automatically sends AWR report

The Linux/Unix shell automatically sends an AWR report to observe the Oracle database performance. The awr feature provided by Oracle provides us with a nearly perfect solution, with the awr feature, 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. Www.2cto.com 1. shell script [python] 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 | # If you want to change the snap inter Val, please change autoawr. SQL | # and crontab configuration | # Usage: | #. /autoawr. sh $ ORACLE_SID | #|# Author: Robinson |######------------------------ # Check SID # -------------------------- if [-z "$ {1}"]; then echo "Usage:" echo "'basename $0 'oracle_sid" exit 1 fi # ------------------------------- # Se T 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 runn Ing, 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 < 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 # mailbox # Send email with AWR report # interval 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 # users # Removing files older $ RETENTION parameter #----------------------------- ------------------- Find $ {AWR_DIR}-name "* awrrpt *"-mtime + $ RETENTION-exec rm {}\; exit 2. SQL script for generating awr report [SQL] robin @ SZDB :~ /Dba_scripts/custom/awr> more autoawr. SQL SET ECHO OFF; SET VERI OFF; SET FEEDBACK OFF; SET TERMOUT ON; SET HEADING OFF; VARIABLE rpt_options NUMBER; DEFINE no_options = 0; define ENABLE_ADDM = 8; REM according to your needs, the value can be 'text' or 'html' DEFINE report_type = 'html'; BEGIN: rpt_options: = & no_options; END;/VARIABLE dbid NUMBER; VARIABLE inst_num NUMBER; VARIABLE bid NUMBER; VARIABLE eid NUMBER; begin select min (snap_id) INTO: bid FROM dba_hist_snapshot WHERE TO_CHAR (end_interval_time, 'yyyymmdd ') = TO_CHAR (SYSDATE, 'yyyymmdd'); select max (snap_id) INTO: eid FROM dba_hist_snapshot; SELECT dbid INTO: dbid FROM v $ database; SELECT instance_number INTO: inst_num FROM v $ instance; END; /COLUMN ext NEW_VALUE ext noprint column fn_name NEW_VALUE fn_name NOPRINT; COLUMN lnsz NEW_VALUE lnsz NOPRINT; SELECT 'txt 'ext from dual where lower (' & report_type ') = 'text '; SELECT 'html' ext from dual where lower ('& report_type') = 'html'; SELECT 'awr _ report_text 'fn_name from dual where lower (' & report_type ') = 'text'; SELECT 'awr _ report_html 'fn_name from dual where lower (' & report_type ') = 'html '; SELECT '80' lnsz from dual where lower ('& report_type') = 'text'; SELECT '000000' lnsz from dual where lower ('& report_type') = 'html '; set linesize & lnsz; COLUMN report_name NEW_VALUE report_name NOPRINT; SELECT instance_name | '_ awrrpt _' | instance_number | '_' | B. timestamp | '. '|' & ext 'report_name FROM v $ instance a, (SELECT TO_CHAR (begin_interval_time, 'yyyymmdd') timestamp FROM dba_hist_snapshot WHERE snap_id =: bid) B; SET TERMOUT OFF; SPOOL $ AWR_DIR/& report_name; SELECT output from table (DBMS_WORKLOAD_REPOSITORY. & fn_name (: dbid,: inst_num,: bid,: eid,: rpt_options); spool off; set termout on; clear columns SQL; TTITLE OFF; BTITLE OFF; REPFOOTER OFF; UNDEFINE report_name UNDEFINE report_type UNDEFINE fn_name UNDEFINE lnsz UNDEFINE no_options 3. In addition, a and shell scripts first determine whether the specified instance is in available. If not, Exit B and call autoawr. after the SQL script generates awr reportc and generates an awr report, if the file exists, the system automatically sends emails d and autoawr. the main part of the SQL script that generates an awr report is to call DBMS_WORKLOAD_REPOSITORY. & fn_name process e. This script generates an awr report for the whole day, that is, from the zero point of the day to the zero point of the next day f. The parameters of the SQL script need to be determined by dbid, instance number, and the start and end id of the snap. rpt_options is used to determine whether the report contains the addm item g. You can customize the start and end IDs of the snap as needed. You need to modify the SQL statement to obtain the correct snap id, generate the required report h. Modify fn_name as needed to generate awr reports of the txt or html type, report_name indicates that the DB cannot be restarted between two snap instances reported by the final file name I and AWR; otherwise, an error may occur (not tested) j. This script supports Oracle 10g/11g. For more information about the awr report generation script, see awrrpt. SQL, awrrpti. SQL