=====Oracle AWR Reports 10g/11g/12c/19c als Datenbank User ohne DBA Rechte erstellen=====
** Anlegt 2015/10 **

Aufgabe: Als normaler Entwickler auf einer Datenbank AWR Berichte/Auswertungen erstellen


**Voraussetzung: Oracle Diagnostic Pack Lizenz steht zur Verfügung!**


Als "normaler User" fehlen Rechte um AWR Reports erzeugen zu können. 

Diese Rechte sollte als Role gebündelt werden und dann wird diese Rolle dem entsprechenden Anwender zu geordnet.


==== Notwendige Rolle anlegen ====


<code sql create_awr_user_role.sql>

--==============================================================================
-- create the role for the usage of the AWR repository for none DBA user
-- run as sys
--==============================================================================
set echo on

create role call_awr_reports;

grant select on sys.gv_$database to call_awr_reports;
grant select on sys.gv_$instance to call_awr_reports;

grant select on sys.v_$database to call_awr_reports;
grant select on sys.v_$instance to call_awr_reports;

grant execute on sys.dbms_workload_repository to call_awr_reports;
grant select on sys.dba_hist_database_instance to call_awr_reports;
grant select on sys.dba_hist_snapshot to call_awr_reports;

set echo off

</code>

Rolle mit "grant call_awr_reports to <user_anme;" zuordnen.
 


----


==== AWR Berichtsaufruf ====


Snapshot ID's ermitteln und Bericht aufrufen:
<code sql awr_call_report.sql>

--==============================================================================
-- GPI -  Gunther Pippèrr
-- Desc:  create AWR report from sql*Plus
-- Date:  10.2015
--==============================================================================
prompt
prompt !!!!You need the Tuning Pack for this feature!!!!
prompt

column end_interval_time format a18 heading "End Interval|Time"
break on dbid

ttitle left  "Overview over the snapshots in the last days" skip 2

select dbid
	 , instance_number
	 , snap_id
	 , to_char(end_interval_time,'hh24:mi dd.mm.yyyy') as end_interval_time
  from dba_hist_snapshot
 where end_interval_time > trunc(sysdate-1)
order by snap_id, instance_number
/

clear break
ttitle off


set feedback off
set heading off
set termout off

column spool_name_col new_val spool_name
column instance_number new_val inst_nr
column aktdbid new_val databaseid
 
SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_awr_report.html','\','_') 
--' resolve syntax highlight bug FROM my editer .-(
   AS SPOOL_NAME_COL
  ,SYS_CONTEXT('USERENV','INSTANCE') as instance_number
 FROM dual
/

select dbid as aktdbid 
  from v$database
/

set feedback on
set heading on
set termout on

set verify off
SET linesize 250 pagesize 2000 recsep off
set long 64000

accept snapshot_id_begin number prompt 'Enter Frist Snapshot Begin ID    : '
accept snapshot_id_end   number prompt 'Enter Snapshot End Id to compare : '

spool &&SPOOL_NAME

select * from table(sys.dbms_workload_repository.awr_report_html(&&databaseid,&&inst_nr,&&snapshot_id_begin,&&snapshot_id_end));

spool off

prompt ... check the created report  &&SPOOL_NAME

host &&SPOOL_NAME
</code> 

Optional kann dbms_workload_repository.awr_report_html noch ein Parameter mehr übergeben werden , siehe Doku unter http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69120


----


==== ASH Report aufrufen ====

Mit der gleichen Methodik kann einfach auch ein ASH Bericht aufgerufen werden, dazu Funktion ASH_REPORT_HTML aufrufen.

siehe http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS72974 für die notwendigen Parametern, diesmal direkt das Datum und nicht mehr die Snap ID's!


<code sql awr_call_ash_report.sql>

prompt
prompt !!!!You need the Tuning Pack for this feature!!!!
prompt


-- ===== 
-- get the spoolfile name and instance_number + DB ID
-- =====
set feedback off
set heading off
set termout off

column spool_name_col new_val spool_name
column instance_number new_val inst_nr
column aktdbid new_val databaseid
 
SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_ash_report.html','\','_') 
--' resolve syntax highlight bug FROM my editer .-(
   AS SPOOL_NAME_COL
  ,SYS_CONTEXT('USERENV','INSTANCE') as instance_number
 FROM dual
/

select dbid as aktdbid 
  from v$database
/

set feedback on
set heading on
set termout on

-- ===== 
-- ask for the Start and endtime of the report
-- =====
set verify off

SET linesize 120 pagesize 300 recsep off

define TIME_FORMAT='dd.mm.yyyy hh24:mi'  

column min_start_time format a18 heading "Early Start|Date"
column max_start_time format a18 heading "Latest Start|Date"

ttitle left  "Overview over the possible timeframe to get an ash report " skip 2

select to_char(min(s.sample_time),'&&TIME_FORMAT') as min_start_time
    ,  to_char(max(s.sample_time),'&&TIME_FORMAT') as max_start_time
  from dba_hist_active_sess_history s
 where  dbid = &&databaseid
   and  instance_number = &&inst_nr
/   

--
-- fix  and  snap_id in (... ) like seelect  min(snap_id), max(snap_id) from dba_hist_snapshot where  dbid = s.dbid  and  instance_number = s.inst_num )
--
ttitle off
 
accept l_btime date prompt 'Enter start time (format &&TIME_FORMAT): '
accept l_etime date prompt 'Enter end time   (format &&TIME_FORMAT): '


-- ===== 
-- create the ASH Report
-- =====	


SET linesize 500 pagesize 9000 recsep off
set long 64000
set feedback off
set heading off

spool &&SPOOL_NAME

select * 
  from table(sys.dbms_workload_repository.ash_report_html( &&databaseid
                                                         , &&inst_nr
														 , to_date('&&l_btime','&&TIME_FORMAT')
														 , to_date('&&l_etime','&&TIME_FORMAT')
														 )
			)
/			

spool off
set heading on
set feedback on

prompt ... check the created report  &&SPOOL_NAME

host &&SPOOL_NAME

set linesize 130 pagesize 300 recsep off

</code>


----


==== Quellen ====


Oracle Blogs:

  * http://marcel.vandewaters.nl/oracle/database-oracle/privileges-for-awr-reporting
  * https://oraclefunda.wordpress.com/2009/10/29/how-to-create-awr-report-manually/



Übersicht ASH

  * http://www.oracle.com/technetwork/database/manageability/ppt-active-session-history-129612.pdf