Generate AWR reports in batches in Oracle
In daily work, we need to monitor AWR reports every day to eliminate potential risks. The following is an automatic O & M script. The ADDM of Oracle also provides some optimization suggestions. For the script to automatically generate the ADDM report, see batch generate the ADDM report in Oracle (SEE ).
Usage: Create an AWR directory under the drive E and put the three files into the directory awr. cmd, awr. SQL, myawr. SQL. create a scheduled task to trigger awr through the job plan of window. cmd.
The connected user must be granted two permissions.
Grant select any dictionary to user; ----- the user is the account of the production database.
Grant execute on dbms_workload_repository to user; ------- the user is the production database account.
Generate and view Oracle AWR reports
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian
Steps for generating an Oracle AWR report
1. File Name: awr. cmd
Content: cmd.exe/c sqlplus username/password @ servername @ awr. SQL
2. File Name: awr. SQL
Content:
@ E: \ AWR \ myawr. SQL 0 8
@ E: \ AWR \ myawr. SQL 8 12
@ E: \ AWR \ myawr. SQL 12 14
@ E: \ AWR \ myawr. SQL 14 18
@ E: \ AWR \ myawr. SQL 18 24
Exit
3. File Name: myawr. SQL
Content:
Set echo off;
Set veri off;
Set feedback off;
Set termout on;
Set heading off;
Set linesize 1500;
Variable dbid number;
Variable inst_num number;
Variable bid number;
Variable eid number;
Variable bhour number;
Variable ehour number;
Begin
: Bhour: = & 1;
: Ehour: = & 2;
End;
/
Begin
Select min (t. snap_id) +: bhour-1 into: bid from sys. dba_hist_snapshot t where to_char (t. begin_interval_time, 'yyyy-mm-dd') = to_char (sysdate-1, 'yyyy-mm-dd ');
Select min (t. snap_id) +: ehour-1 into: eid from sys. dba_hist_snapshot t where to_char (t. begin_interval_time, 'yyyy-mm-dd') = to_char (sysdate-1, 'yyyy-mm-dd ');
Select dbid into: dbid from v $ database;
Select instance_number into: inst_num from v $ instance;
End;
/
Set termout off;
Column report_name new_value report_name noprint;
Select 'awrrpt _ '| to_char (sysdate-1, 'yyyymmdd') |' _ '|: bhour |'-'|: ehour | '.html 'report_name from dual;
Set termout on;
Spool & report_name;
Select output from table (dbms_workload_repository.awr_report_html (: dbid,: inst_num,: bid,: eid, 0 ));
Spool off;
Set termout on;
Clear columns SQL;
Ttitle off;
Btitle off;
Repfooter off;
Undefine report_name