Use shell to automatically send Oracle AWR reports

1 Requirement

Send the important database AWR to the DBA mail on a regular basis every day. After receiving the AWR mail, the DBA simply checks whether the database has abnormal waiting tasks and abnormal SQL statements, try to avoid analyzing related SQL statements when the user finds that the database is slow.

2 related environments

Operating System

This script is run under Red Hat and has not been tested on other operating systems. Because dbms_workload_repository is used to generate reports, you can analyze the databases in any operating system, that is to say, it has nothing to do with the database operating system.

Database Version

This script can analyze any sub-version database of Oracle 10g and 11g.

3. Script details

Scripts to store directories and files
 
[Oracle @ svr7-71 awrrpt] $ pwd
 
/Home/oracle/awrrpt
 
[Oracle @ svr7-71 awrrpt] $ ls-lrt
 
-Rwxrwx --- 1 oracle oinstall 1968 May 24 awrrpt. sh
 
Drwxr-xr-x 2 oracle oinstall 4096 May 24 reports
 
-Rwxrwx --- 1 oracle oinstall 360 May 24 run. sh
 
 
 
Awrrpt. sh is the script for generating AWR, and reports is the folder for storing the generated AWR report.
 
, Run. sh can be configured with multiple databases for the entrance to generate AWR
 
 
 
[Oracle @ svr7-71 awrrpt] $ more awrrpt. sh

#! /Bin/bash
 
#$1 startTime
 
#$2 endTime
 
#$3 TNS user/passpassword @ sid
 
# Creater: eymit. wu
 
# Last modified: 2012-05-23
 
.~ /. Bash_profile
 
PROJECT_HOME = '/home/oracle/awrrpt'
 
REPORT_HOME = '/home/oracle/awrrpt/report'
 
TODAY = 'date + % Y % m % d'
 
 
 
Cd $ {PROJECT_HOME}
INSTANCE_NAME = 'sqlplus-s "$3" <EOF
Set feed off
Set term off
Set echo off
Set pages 0
Set heading off
Select instance_name from v \ $ instance;
Exit;
EOF'

FILE_NAME = 'sqlplus-s "$3" <EOF
Set feed off
Set term off
Set echo off
Set pages 0
Set heading off
Select '$ {REPORT_HOME}/awrrpt _' | to_char (sysdate, 'yyyymmdd ') | '_' | '$ INSTANCE_NAME' | '_' | to_char (min (snap_id) | '_' |
To_char (max (snap_id) | '.html 'file_name
From dba_hist_snapshot t
Where t. end_interval_time between trunc (sysdate) + $1/24 and
Trunc (sysdate) + ($2 + 1)/24;
Exit;
EOF'

Echo "instance_name $ INSTANCE_NAME"
Echo "FILE_NAME $ FILE_NAME"

Sqlplus "$3" <EOF

Column dbid new_value dbid;
Select dbid from v \ $ database;

Column instance_number new_value instance_number;
Select instance_number from v \ $ instance;

Column max_id new_value end_snap;
Column min_id new_value begin_snap;
Select to_char (max (snap_id) max_id,
To_char (min (snap_id) min_id
From dba_hist_snapshot t
Where t. end_interval_time between trunc (sysdate) + $1/24 and
Trunc (sysdate) + ($2 + 1)/24;

Set echo off;
Set veri off;
Set feedback off;
Set termout on;
Set heading off;
Set linesize 1500;
Set trimspool on;

Spool $ FILE_NAME;
Select output from table (dbms_workload_repository.AWR_REPORT_HTML (& dbid, & instance_number, & begin_snap, & end_snap, 0 ));
Spool off;
EOF
 
 
 
Echo "Report file name is:" $ FILE_NAME
 
Echo "AWR report of $ INSTANCE_NAME" | mutt-s "[info] AWR report of $ INSTANCE_NAME $ {TODAY}"-a "$ FILE_NAME" linux@hotmail.com
 
Do not forget to modify the sent emails and themes. run. sh will be introduced below.
 
4 usage
 
We can see that three parameters need to be passed to call the awrrpt Script: start time, end time, user name and password for database connection, and tns. Run below. sh configures two AWR reports from to, and the database only needs to modify run. sh.
 
 
 
[Oracle @ svr7-71 awrrpt] $ more run. sh

./Awrrpt. sh 9 11 test1/test1 @ ora71
 
./Awrrpt. sh 9 11 test1/test1 @ ora72
 
 
Finally, do not forget to configure tnsname for the database to be generated to tnsnames. ora, and finally configure run. sh to crontab.