Generate AWR reports in batches in Oracle

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.