Oracle automatically generates AWR reports every day

Experienced old staff want to be able to generate 1 Awr reports per day for the database, so that the performance changes in the post-analysis database, manual generation too cumbersome, check the information, found that can be automatically generated, the process is as follows.

Database environment: 11gR2 RAC (Dual node)

AWR report: Because it is a RAC database, you want to generate reports and global reports for each node, from 0 points on the first day to 0 points on the second day.

(1) Create a path on the Oracle server

Server2$[/home/oracle]mkdir awrreport

(2) Create directory and authorize (execute with SYS account)

Create or ReplaceDirectory Dir_awrreport as '/home/oracle/awrreport';Grant Read, write onDirectory Dir_awrreport toMYDBA;--Mydba as a user with DBA authorityGrant Select  onv_$Database  tomydba;Grant Select  onDba_hist_snapshot tomydba;Grant Execute  onSys.dbms_workload_repository toMYDBA;

(3) scripting (Mydba execution with monitoring account)

CREATE OR REPLACE PROCEDUREAuto_awrreport as/*Purpose: Automatically generate AWR report creator: Gegeman Date created: 2017-12-08*/Start_snap Number; End_snap Number; Rpt_interval Number:=  -;--report Interval (hours)Start_timevarchar2( -); End_timevarchar2( -); Awr_file utl_file.file_type;v_dbid Number; V_dbnamevarchar2( -);begin--determine the database name and IDSelectDbid,name intoV_dbid,v_dbname fromv$Database;---the snapshot ID of the start of the querySelect Max(snap_id) intoEnd_snap fromDba_hist_snapshot;start_snap:=End_snap-Rpt_interval;---Format Snapshot timeSelectTo_char (end_interval_time-Rpt_interval/ -,'yyyymmddhh24'), To_char (End_interval_time,'yyyymmddhh24') intoStart_time,end_time fromDba_hist_snapshotwheresnap_id=End_snap andInstance_number= 1;--Example 1 reportAwr_file:=Utl_file.fopen ('Dir_awrreport',Lower(V_dbname)||'_1_'||Start_time||'_'||End_time||'. html','a',32767); forAwr_infoinch(SelectOutput from Table(Dbms_workload_repository.awr_report_html (V_dbid,1, Start_snap,end_snap,0)) Looputl_file.put_line (awr_file,awr_info.output);EndLoop;utl_file.fclose (awr_file);--Example 2 ReportAwr_file:=Utl_file.fopen ('Dir_awrreport',Lower(V_dbname)||'_2_'||Start_time||'_'||End_time||'. html','a',32767); forAwr_infoinch(SelectOutput from Table(Dbms_workload_repository.awr_report_html (V_dbid,2, Start_snap,end_snap,0)) Looputl_file.put_line (awr_file,awr_info.output);EndLoop;utl_file.fclose (awr_file);--Global ReportingAwr_file:=Utl_file.fopen ('Dir_awrreport',Lower(V_dbname)||'_global_'||Start_time||'_'||End_time||'. html','a',32767); forAwr_infoinch(SelectOutput from Table(Dbms_workload_repository.awr_global_report_html (l_dbid=V_dbid,l_inst_num= "', L_bid=Start_snap,l_eid=End_snap,l_options= 0)) Looputl_file.put_line (awr_file,awr_info.output);EndLoop;utl_file.fclose (awr_file);EndAuto_awrreport;

(4) Grant the monitoring user the ability to perform procedure (using the SYS account)

Grant Execute  on  to Mydba;

(5) Create job, execute every night 12:30 (monitor account)

DeclareJob1 Number;beginsys.dbms_job.submit (Job=Job1,what= 'Auto_awrreport;', Next_date=Sysdate,interval= 'TRUNC (sysdate+1) + 30/(24*60)', Instance= 2); End;

Oracle automatically generates AWR reports every day