Automatic acquisition of AWR reports using shell scripts

Observing Oracle Database performance, Oracle's own AWR capabilities provide us with an almost perfect solution, with the AWR feature we can extract AWR reports from the database at any time. Reports allow you to understand the overall performance of a system, and the resulting reports include multiple sections.

How do I get an AWR report?

1. Execute with Awrrpt.sql script
2. By calling Oralce's package Dbms_workload_repository.awr_report_html/dbms_workload_repository.awr_report_text

Get the AWR report is usually in HTML form; Open page is friendly and easy to view.

By means of Method 1 Awrrpt.sql script execution requires that we provide some interaction information. The operation is also relatively simple. This article implements an AWR report that automatically generates a specified period of time through a shell script. Adopt Method 2.

1. The SQL script that generated the AWR report Autoawr.sql

SETECHOOFF; SETVERIOFF; SETFEEDBACKOFF; SETTermout on; SETHEADINGOFF; VARIABLE dbid Number; VARIABLE Inst_num Number; VARIABLE Bid Number; VARIABLE Eid Number; BEGIN    SELECT MIN(snap_id) into: Bid fromDba_hist_snapshotWHERETo_char (End_interval_time,'YYYYMMDD')=To_char (sysdate-1,'YYYYMMDD'); SELECT MAX(snap_id) into: Eid fromDba_hist_snapshotWHERETo_char (Begin_interval_time,'YYYYMMDD')=To_char (sysdate-1,'YYYYMMDD'); SELECTdbid into:d bid fromv$Database; SELECTInstance_number into: Inst_num fromv$instance; END; /    SetPageSize0;SetLinesize121;COLUMNreport_name new_value report_name noprint;SELECTInstance_name|| '_awrrpt_' ||Instance_number|| '_' ||B.timestamp || '.' || 'HTML'Report_name fromv$instance A, (SELECTTo_char (Begin_interval_time,'YYYYMMDD')timestamp             fromDba_hist_snapshotWHEREsnap_id=: Eid) b; SETTermoutOFF; SPOOL $AWR _dir/&Report_name; SELECTOutput from TABLE(dbms_workload_repository.awr_report_html (:d bid,: Inst_num,  : Bid,: Eid)); SPOOLOFF; SETTermout on;  CLEAR COLUMNS SQL; TtitleOFF; BtitleOFF; RepfooterOFF; Undefine Report_name

2. The shell script that generated the AWR report autoawr.sh

#!/bin/Bashif[- F~/. Bash_profile]; Then source ~/. Bash_profile fi export Awr_cmd=/home/oracle/Awrexport Awr_dir=/home/oracle/awr/Report RETENTION=31# ----------------------------------------------  #Generate awr Report# ----------------------------------------------  $ORACLE _home/bin/sqlplus/As sysdba<<eof@${awr_cmd}/Autoawr.sql;  Exit EOF# ------------------------------------------------  #removing files older than $RETENTION parameter# ------------------------------------------------  Find${awr_dir}-name"*awrrpt*"-mtime +$RETENTION-exec rm {} \; Exit

Automatic acquisition of AWR reports using shell scripts