Linux/Unix shell automatically sends AWR Report (2)

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 provides a complete set of functions for Linux/Unix shell to automatically send AWR reports.

 

1. shell script

robin@SZDB:~/dba_scripts/custom/awr> more autoawr_by_time.sh#!/bin/bash# --------------------------------------------------------------------------+#                 Generate AWR report and send mail automatically           |#   Filename: autoawr_by_time.sh                                            |#   Desc:                                                                   |#       The script use to generate awr report by time period.               |#       Three parameter for it.                                             |#           para1: <ORACLE_SID>   mandatory parameter                       |#           para2: [begin time]   optional parameter                        |  #           para3: [end time  ]   optional parameter                        |#       Deploy it by crontab as requirement                                 |  #   Usage:                                                                  |#       ./autoawr_by_time.sh <instance_name> [begin time] [end time]        |  #   Example:                                                                |#       ./autoawr_by_time.sh TESTDB                                         |   #            --default,time period is from last midnight to today midnight  |#       ./autoawr_by_time.sh TESTDB 2013031009                              |#            --time period is from 2013031009 to now                        |#       ./autoawr_by_time.sh TESTDB 2013031009 2013031012                   |#            --time period by speicifed                                     | #   Author : Robinson                                                       | #   Blog   : http://blog.csdn.net/robinson_0612                             |# --------------------------------------------------------------------------+## -------------------------------#  Set environment here # ------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefi# ------------------------------------------------------------#  Check the parameter, if no specify,then use default value# ------------------------------------------------------------if [ -z "${1}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> [begin_date] [end_date]"fiif [ -z "${3}" ] && [ -z "${2}" ];then    begin_date=`date -d yesterday +%Y%m%d`'00'    end_date=`date +%Y%m%d`'00'elif [ -z "${3}" ]; then    begin_date=${2}    end_date=`date +%Y%m%d%H`else    begin_date=${2}    end_date=${3}fiORACLE_SID=${1}export ORACLE_SID begin_date end_date export MACHINE=`hostname`export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56export MAIL_LIST='Robinson.chen@12306.com'export AWR_CMD=/users/robin/dba_scripts/custom/awrexport AWR_DIR=/users/robin/dba_scripts/custom/awr/report/${ORACLE_SID}export MAIL_FM='oracle@szdb.com'RETENTION=31echo $ORACLE_SID echo $begin_dateecho $end_date# --------------------------------------------------------------------#  Check the directory for store awr report,if not exist, create it# --------------------------------------------------------------------if [ ! -d "${AWR_DIR}" ]; then    mkdir -p ${AWR_DIR}fi# ----------------------------------------------# 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 the awr report# ---------------------------------------------sqlplus -S "/ as sysdba" @${AWR_CMD}/autoawr_by_time.sql $begin_date $end_date 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    exitfi# ------------------------------------------------# Send email with AWR report# ------------------------------------------------filename=`ls ${AWR_DIR}/${ORACLE_SID}_awrrpt_?_${begin_date}_${end_date}*`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`.Time period: $begin_date,$end_date. "    $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_by_time.sqlSET ECHO OFF;SET VERI OFF;SET FEEDBACK OFF;SET TERMOUT ON;SET HEADING OFF;SET TRIMSPOOL ON;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 snap_id        INTO :bid        FROM dba_hist_snapshot       WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&1';      SELECT snap_id        INTO :eid        FROM dba_hist_snapshot       WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&2';   SELECT dbid INTO :dbid FROM v$database;   SELECT instance_number INTO :inst_num FROM v$instance;END;/--print dbid;--print bid;--print eid;--print inst_num;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 || '_' ||'&&1'||'_'||'&&2'|| '.' || '&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;--SPOOL &report_nameSELECT 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;SET TRIMSPOOL OFF;UNDEFINE report_nameUNDEFINE report_typeUNDEFINE fn_nameUNDEFINE lnszUNDEFINE no_optionsexit; 

3. Additional instructions
A. The script automatically generates an AWR report for different instances in different time periods.
B. Usage:./autoawr_by_time.sh <instance_name> [begin time] [end time], which can be used to generate AWR report anytime, anywhere
C. If [begin time] [End Time] is omitted, the AWR report generated from the early morning of yesterday to the early morning of this morning is automatically generated.
D. When only [End Time] is omitted, an AWR report is generated from [begin time] to the current maximum snap_id.
E. When [begin time] [End Time] is specified, the AWR Report of the specified time period is generated.
F. Send an AWR report by calling sendemail. For details, refer to the indispensable sendemail.

 

4. Deployment reference

# If you only need a whole day of AWR report, simply deploy it to crontab. # If you need an AWR report for a whole day or for different time periods, you can deploy it as follows and merge it into a shell file Robin @ szdb :~ /Dba_scripts/custom/AWR> More AWR. Sh #! /Bin/bashdt = 'date + % Y % m % d 'start _ date = $ DT '05 'end _ date = $ DT '09'/users/Robin/dba_scripts/ custom/AWR/autoawr_by_time.sh cnmmbo # Get AWR report/users/Robin/dba_scripts/custom/AWR/autoawr_by_time.sh cnmmbo $ start_date # obtain AWR report/users/from the specified start time to the present/ robin/dba_scripts/custom/AWR/autoawr_by_time.sh cnmmbo $ start_date $ end_date # obtain the AWR reportexit Robin @ szdb for the specified time period: ~ /Dba_scripts/custom/AWR> crontab-L # Do not edit this file-edit the master and reinstall.45 11 ***/users/Robin/dba_scripts/custom/AWR. Sh

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)