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 00: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 1fi# -------------------------------#  Set environment here # ------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiexport ORACLE_SID=$1export MACHINE=`hostname`export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56export MAIL_LIST='Robinson.cheng@12306.com'export AWR_CMD=/users/robin/dba_scripts/custom/awrexport AWR_DIR=/users/robin/dba_scripts/custom/awr/reportexport 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 1fi;# ----------------------------------------------# Generate awr report# ----------------------------------------------$ORACLE_HOME/bin/sqlplus /nolog<<EOFconnect / as sysdba;@${AWR_CMD}/autoawr.sql;exit;EOFstatus=$?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    exitfi# ------------------------------------------------# 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    

2. SQL script for generating AWR report

robin@SZDB:~/dba_scripts/custom/awr> more autoawr.sqlSET 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-1, 'yyyymmdd');   SELECT MAX (snap_id) INTO :eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time,'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd');   SELECT dbid INTO :dbid FROM v$database;SELECT instance_number INTO :inst_num FROM v$instance;END;/COLUMN ext NEW_VALUE ext NOPRINTCOLUMN 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 '1500' 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 = :eid) 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_nameUNDEFINE report_typeUNDEFINE fn_nameUNDEFINE lnszUNDEFINE no_options 

3. Additional instructions
A. in the shell script, first determine whether the specified instance is in available. If not, exit.
B. Call the autoawr. SQL script to generate an AWR report.
C. After an AWR report is generated, an email is automatically sent if the file exists.
D. In the autoawr. SQL script, AWR report is generated mainly because dbms_workload_repository. & fn_name is called.
E. The script generates an AWR report for the whole day, that is, from of the current day to of the next day.
F. The dbid, instance number, and start and end ID of the snap must be determined for the parameters of the SQL script. rpt_options is used to determine whether the report contains ADDM items.
G. You can customize the start and end IDs of the desired snap. You need to modify the SQL statement to obtain the correct snap ID to generate the required report.
H. Modify fn_name as needed to generate the awr report of the txt or HTML type, and report_name to determine the final file name.
I. the DB restart operation cannot be performed between two snap instances reported by 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 and awrrpti. SQL.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)